Moving Databases between machines - a script
Here's a little perl script I wrote that allows me to create an SQL
unload script and a dbload command script for moving my database from
one machine to another. Very useful in the development stage when the
number of fields in a table changes from time to time. Hope somebody
finds it to be of use.
Regards
Neil S. Briscoe Telephone: +44 252 376737
System Administrator Fax: +44 252 376644
Bartec Medical Systems Ltd., Email: nbriscoe@cix.compulink.co.uk
Impression House
Invincible Road "!##@!** Listen who swears
Farnborough Christopher Robin has fallen downstairs"
Hants GU14 7NP -- The Goodies Book of Criminal Records
England
------------Script Starts here-------------
#! /home/neil/bin/perl
# This script writes the sql unload and dbload command files to move the database
# from here to any other machine.
# Either dbscema or system outputs the wrong exit code, hence && not ||
# We run this to generate the current schema
system("dbschema -q -d ndb fred") && die "Couldn't run dbschema: $!\\n";
open(SCHEMA, "fred") || die "Couldn't read current schema file: $!\\n";
open(LOAD, "> dbload.com") || die "Couldn't open load file for writing: $!\\n";
open(DUMP, "> dump.sql") || die "Couldn't open dump file for writing: $!\\n";
$dumpdir='/usr/ndb/dbdump'; # Where we're making our ASCII files.
$intable = 0; # Currently not in table
while(<SCHEMA>) { # Read from the schema file
if (!$intable) { # If we're not in a table yet
if (/create table/) { # Found create table
s/(create table )([a-zA-Z0-9_]*)(\\s+)/$2/;
$table = $_; # Record table name
$intable = 1; # Now we're in the table
$count = 0; # No fields yet
}
}
else { # We're in the table
next if /^\\s+\\(\\s+$/; # Skip left bracket line
if (/\\);/) { # It's a right bracket line - do the stuff
printf LOAD "\\"%s/%s\\" DELIMITER \\"|\\" %d;\\n", $dumpdir, $table, $count;
printf LOAD "INSERT INTO %s;\\n", $table;
printf DUMP "unload to \\"%s/%s\\" select * from %s;\\n", $dumpdir, $table, $table;
$intable = 0; # Out of table again
}
else { # Just a field in the table
$count++; # So we count it
}
}
}
close(SCHEMA);
close(LOAD);
close(DUMP);
unlink("fred");
----------------End of Script