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

extension=paradox.so

<?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;
?>

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

"""
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))

$ python paradox2sql.py mydata

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

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

$ sh sql2db.sh mydb