Migrating Paradox Data to PostgreSQL in Ubuntu

Submitted by k4tz on Tue, 12/15/2009 - 14:17

We use Paradox database on Borland Delphi. Paradox classified as file database, not server database. Each table store in .db extension file. If you like to migrate from Paradox to PostgreSQL, then follow this tutorial.

I am using Ubuntu 9.10 distro.

# apt-get install php5-dev php-pear pxlib1-dev
# pecl channel-update pear.php.net
# pecl install paradox

Create /etc/php5/conf.d/paradox.ini file that contain:

extension=paradox.so

Save this scripts in paradox2sql.php:

<?php
/*
Paradox to SQL Converter
(c)2007-2009 RAB
*/
 
function ascii_only($s) {
  $r = "";
  for ($i=0; $i<=strlen($s); $i++) {
    $ch = $s[$i];
    $ascii = ord($ch);
    if ($ascii > 126 or $ascii < 32) {
      $ch = " ";
    }
    $r .= $ch;
  }
  return $r;
}
 
function fixfieldname($s) {
  $s = strtolower($s);
  $s = str_replace(' ','_', $s);
  $s = str_replace('#','',$s);
  if ($s == 'desc') {
      $s = 'description';
  }
  return $s;
}
 
 
$filename = $_SERVER['argv'][1];
$fp = fopen($filename, "r");
$pxdoc = new paradox_db();
if(!$pxdoc->open_fp($fp)) {
    die('Error opening ' . $filename);
}
 
 
/* Field structure */
 
$fieldCount = $pxdoc->numfields();
$fields = array();
$fieldtype = array();
$fieldnames = array();
for ($i=0; $i<$fieldCount; $i++) {
  $f = $pxdoc->get_field($i);
  if ($f['type'] == PX_FIELD_ALPHA) {
    $type = "varchar(". $f['size'] . ")";
  } elseif ($f['type'] == PX_FIELD_DATE) {
    $type = "date";
  } elseif ($f['type'] == PX_FIELD_SHORT) {
    $type = "smallint";
  } elseif ($f['type'] == PX_FIELD_LONG) {
    $type = "integer";
  } elseif ($f['type'] == PX_FIELD_NUMBER) {
    $type = "float";
  } elseif ($f['type'] == PX_FIELD_LOGICAL) {
    $type = "boolean";
  } elseif ($f['type'] == PX_FIELD_TIMESTAMP) {
    $type = "timestamp";
  } elseif ($f['type'] == PX_FIELD_TIME) {
    $type = "time";
  } elseif ($f['type'] == PX_FIELD_AUTOINC) {
    $type = "serial";
  } else {
    print $filename . " unknown type\n";
    print_r($f);
    exit;
  }
  $fieldname = fixfieldname($f['name']);
  array_push($fieldnames, $fieldname);
  $field = $fieldname . " " . $type;
  array_push($fields, $field);
  $fieldtype[$fieldname] = $type;
}
 
 
/* Table structure */
 
$t = explode('/',$filename);
$t = explode('.', $t[count($t)-1]);
$tablename = strtolower($t[0]);
$sql = "CREATE TABLE " . $tablename . "(\n" .
  implode($fields,",\n") . "\n);";
 
 
/* Primary key */
 
$info = pathinfo($filename);
if ($info['extension'] == 'DB') {
    $px = 'PX';
} else {
    $px = 'px';
}
$filekey = substr($filename, 0, strlen($filename)-strlen($info['extension'])) . $px;
$keys = array();
if (file_exists($filekey)) {
    $fk = fopen($filekey, "r");
    $pxkey = new paradox_db();
    if(!$pxkey->open_fp($fk)) {
        die('Error opening ' . $filekey);
    }
    $keycount = $pxkey->numfields();
    for ($i=0; $i<$keycount; $i++) {
        array_push($keys, $fieldnames[$i]);
    }
    fclose($fk);
}
 
 
/* Records */
 
$sql .= "\nCOPY $tablename FROM stdin;";
$recordCount = $pxdoc->numrecords();
$px = px_new();
for ($i=0; $i<$recordCount; $i++) {
  $r = $pxdoc->retrieve_record($i);
  $fields = array();
  foreach($r as $fieldname=>$value) {
    $fieldname = fixfieldname($fieldname);
    if ($fieldtype[$fieldname] == 'date') {
        if ($value == '') {
            $value = "\\N";
        } else {
            $value = px_date2string($px, $value, "Y/n/d");
            preg_match('/([\d]*)\/([\d]*)\/([\d]*)/', $value, $match, PREG_OFFSET_CAPTURE);
            if ($match[0][0] != $value) {
                $value = "\\N";
            }
        }
    } elseif ($fieldtype[$fieldname] == 'timestamp') {
        if ($value <= 0) {
            $value = "\\N";
        } else {
            $v = px_timestamp2string($px, $value, "Y/n/d H:i:s");
            preg_match('/([\d]*)\/([\d]*)\/([\d]*) ([\d]*):([\d]*):([\d]*)/', $value, $match, PREG_OFFSET_CAPTURE);
            if ($match[0][0] != $value) {
                $value = "\\N";
            }
        }
    } else {
        $value = trim(ascii_only($value . ""));
        if ($value == '') {
            if (! in_array($fieldname, $keys)) {
                $value = "\\N";
            }
        }
    }
    array_push($fields, $value);
  }
  $sql .= "\n" . implode($fields, "\t");
}
 
$pxdoc->close();
fclose($fp);
 
$sql .= "\n\\.\n";
 
 
/* Primary key */
 
$info = pathinfo($filename);
if ($info['extension'] == 'DB') {
    $px = 'PX';
} else {
    $px = 'px';
}
$filekey = substr($filename, 0, strlen($filename)-strlen($info['extension'])) . $px;
if (! file_exists($filekey)) {
    print $sql;
    exit;
}
$fk = fopen($filekey, "r");
$pxkey = new paradox_db();
if(!$pxkey->open_fp($fk)) {
    die('Error opening ' . $filekey);
}
$keycount = $pxkey->numfields();
$keys = array();
for ($i=0; $i<$keycount; $i++) {
    array_push($keys, $fieldnames[$i]);
}
fclose($fk);
$sql .= "\nALTER TABLE $tablename ADD PRIMARY KEY(" . implode($keys,",") . ");";
 
print $sql;
?>

Then:

$ php paradox2sql.php mytable.db > mytable.sql

Paradox files locate in a directory. Use paradox2sql.py script down here to migrate that directory:

"""
Paradox directory to SQL
(c)2009 RAB
"""
import glob
import os
 
def run(s):
    print s
    os.system(s)
 
dirname = os.sys.argv[1]
files = glob.glob('%s/*.db' % dirname)
files += glob.glob('%s/*.DB' % dirname)
 
for source in files:
    target = source.lower().split('/')[-1][:-2] + 'sql'
    if os.path.exists(target):
        continue
    run('php db2sql.php %s > %s' % (source, target))

example:

$ python paradox2sql.py mydata

where mydata are the directory where Paradox *.db file stored.

To restore all sql file in current directory, use this sql2db.sh script:

for sql in `ls *.sql`; do
    echo $sql
    psql $1 -f $sql
done

Create database first before you execute:

$ sudo su
# su postgres
$ createuser ilham
$ createdb -O ilham mydb
$ exit
# exit

Still as user ilham:

$ sh sql2db.sh mydb

Source: http://jabber.rab.co.id/os/migrasi-data-paradox-ke-postgresql