Home
Home Blogs k4tz's blog
    • Blog
    • Clinic
    • Contact
    • Download
    • Video
    • Login

Poll

Favourite console text editor in Ubuntu:

LXer -- Linux and Open Source News

  • Equinox Introduces 2 Other Brilliant Themes, Installation Made Easy via PPA
  • First Alpha of uTorrent Server for Linux Released
  • A Linux Demo For Amnesia: The Dark Descent
  • Fedoraproject.org redesign looking really, really great
  • How To Set Up A USB-Over-IP Server And Client With Ubuntu 10.04
more

Linux Today

  • Editor's Note: Holiday Tech Fun
  • Mint 9: Minty fresh Linux
  • Amnesia: The Dark Descent Demo Released!
  • Android: the return of the Unix wars?
  • Songbird 1.8.0 adds support for more devices
more

Linux Insider

  • Tablet Skirmish Heats Up With Toshiba Entry
  • Samsung's Galaxy Whirls Into the Tablet Universe
  • Sony's Shocking 'Other OS' Win and Suspect Distro Popularity Trends
  • 2 Smart Backup Apps Show You the Way to Go Home
  • Cloud Computing Calms Open Source Warfare
more

Migrating Paradox Data to PostgreSQL in Ubuntu

Submitted by k4tz on Tue, 12/15/2009 - 14:17
  • Blog
  • PostgreSQL
  • Ubuntu

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

  • k4tz's blog
  • Add new comment

Recent blog posts

  • How to install PHP-GTK in Ubuntu 10.04 Lucid Lynx
  • Download Linux Mint 9 "Xfce"
  • ISO Booting with Grub 2 in Ubuntu
  • Howto Fix Rhythmbox Iphone + Ipod Sync Issues in Ubuntu
  • How to install Ubuntu 10.04 on Thinkpad x201
  • Fix for Audio Problem in Dell Studio 1450 in Ubuntu 10.04 (lucid lynx)
  • Howto optimize Flash in Firefox on Ubuntu
  • HP Laserjet P1006 Printer not Working on Ubuntu
  • How to get video recording working with Acer Crystal Eye Webcam on Ubuntu
  • Reset your Windows password and edit the Windows Registry from Ubuntu
more

Linux World

  • Net Neutrality: what does the Google Verizon proposal mean for GNU Linux?
  • Why can't free software lead to hardware innovation?
Archive Syndicate content

Recent comments

  • Re
    34 weeks 4 days ago
  • Re
    34 weeks 4 days ago
  • Re
    35 weeks 4 days ago
  • Re
    36 weeks 21 hours ago
  • Re
    36 weeks 5 days ago
  • Re
    36 weeks 5 days ago
  • Re
    37 weeks 1 hour ago
  • iwl3945
    38 weeks 4 days ago
  • HomeBank
    50 weeks 5 days ago
  • KMyMoney and direct connect to banks
    1 year 2 weeks ago
All contents copyright © 2008, Dhuha Net. All rights reserved
Ubuntudoctor® is a member of the Dhuha Network. Privacy Policy
RoopleTheme