Split table syntax and take out all constraints...
Hi All,
I have a dbschema output for a large database, and I want to create
all tables as RAW tables first in order to load data and then change
the type to STANDARD.
My problem is that I can't create a RAW table if the table syntax
contains constraints such as
create table mytable
(
my_key serial not null ,
my_name varchar(64) not null ,
desc1 varchar(32) not null ,
desc2 varchar(64) not null ,
primary key (my_key) constraint pk_mytable
);
Is there a way to split the above syntax into create and alters, so
the alters will be the constraints such as
create table mytable
(
my_key serial not null ,
my_name varchar(64) not null ,
desc1 varchar(32) not null ,
desc2 varchar(64) not null
);
alter table mytable add constraint primary key (my_key) constraintpk_mytable;
Any other ideas?
Thanks,
Mahdi
On Jul 14, 4:24 am, Mahdi Sbeih <mahdi.sb...@gmail.com> wrote:
> Hi All,
>
> I have a dbschema output for a large database, and I want to create
> all tables as RAW tables first in order to load data and then change
> the type to STANDARD.
>
> My problem is that I can't create a RAW table if the table syntax
> contains constraints such as
> create table mytable
> (
> my_key serial not null ,
> my_name varchar(64) not null ,
> desc1 varchar(32) not null ,
> desc2 varchar(64) not null ,
> primary key (my_key) constraint pk_mytable
> );>
> Is there a way to split the above syntax into create and alters, so
> the alters will be the constraints such as
>
> create table mytable
> (
> my_key serial not null ,
> my_name varchar(64) not null ,
> desc1 varchar(32) not null ,
> desc2 varchar(64) not null
> );
> alter table mytable add constraint primary key (my_key) constraint> pk_mytable;
>
> Any other ideas?
>
> Thanks,
> Mahdi
We had to do the same thing recently because we changed platforms,
except that we used HPL which I recommend instead of using raw tables.
We still split things up so we could build our indexes in parallel. We
used myschema then I wrote a quick and dirty perl script. I can't
guarantee it but you are welcome to it. Caveat emptor as they say. One
other thing is that it renames constraints and indexes in the schema
to the way I like them. If you know perl then it would be
straightforward to remove this.
It splits the file into 4 files.
indexes
pks
fks
rest
You run rest first. It creates all of the tables, stored procedures,
triggers (which if you are loading raw you may want to create later),
privs, etc.
Next you run the index file to create the indexes. This may put all of
the indexes for a table on the same line so you can use a simple
script to run them in parallel, if not then I had another script that
did that. Next, run the PK file and then finally the FK file. It is
messy and the script isn't commented because it was a quick and dirty
thing. I just looked at the output again it doesn't compress the
lines. I split that functionality out to another script.
It also may run into an unforeseen issue that didn't occur in our data
set. I'll be leaving for France in a few hours so I won't be much help
for a week.
Here it is:
==== First line below =====
#!/usr/bin/perl -w
use strict ;
$::DEBUG=0;
unless ( (scalar @ARGV) == 1) {
die "Need to pass in one file name." ;
}
print "$ARGV[0]\\n" ;
my $BASE_NAME=$ARGV[0] ;
$BASE_NAME =~ s/\\.[^.]*$//g ;
#split out indexes
unless ( open IDXS, "> ${BASE_NAME}_indexes.split.sql" ) {
die "Couldn't create index file.\\n" ;
}
unless ( open FKS, "> ${BASE_NAME}_constraints.split.sql" ) {
die "Couldn't get Schema\\n" ;
}
unless ( open REST, "> ${BASE_NAME}_rest.split.sql" ) {
die "Couldn't create grant file.\\n" ;
}
unless ( open PKS, "> ${BASE_NAME}_pks.split.sql" ) {
die "Couldn't create grant file.\\n" ;
}
my %htExists = () ;
my $index=0;
my $constraint=0;
my $create_procedure=0;
my $end_procedure=0;
my $print_constraint="";
my $print_index="";
my %fks = () ;
my %fk_names = () ;
my %indexes = () ;
my %pks = () ;
my $tab="";
my $line;
while (<>) {
$line=$_;
$line =~ s/cluster\\s+index/index/sim ;
# index creation inside procedures goes with the procedures;
if ($end_procedure) {
}
if ($create_procedure) {
}
elsif ( $line =~ /create\\s+(unique\\s+)?index/i ) {
# found index
$index=1;
$line =~ /on ("informix"\\.)?(\\w+)\\s+\\(/i ;
$tab=$2 ;
}
elsif ( $line =~ /alter\\s+table\\s+(\\w+)/i ) {
$constraint=1;
$tab=$1;
}
elsif ( $line =~ /create procedure/i ) {
$create_procedure=1;
}
if ($create_procedure) {
if ($end_procedure and $line =~ /;/) {
$create_procedure=0 ;
$end_procedure=0 ;
}
elsif ( $line =~ /end procedure ;/i ) {
$create_procedure=0 ;
}
elsif ( $line =~ /end procedure/i ) {
$end_procedure=1 ;
}
print REST $line ;
}
elsif ($index) {
$print_index.= $line ;
if ( /;/ ) {
print "DEBUG :$print_index:\\n" if $::DEBUG ;
#Get rid of all carriage returns.
$print_index =~ s/\\s+/ /sgm ;
#take last CR that is now space and remove it
$print_index =~ s/; /;/sgm ;
print "DEBUG :$print_index:\\n" if $::DEBUG ;
push( @{$indexes{$tab}}, "$print_index") ;
$htExists{$tab}=1;
$index=0 ;
$print_index = "" ;
}
}
elsif ($constraint) {
$print_constraint .= $line ;
if ( /;/ ) {
print "DEBUG :$print_constraint:\\n" if $::DEBUG ;
#Get rid of all carriage returns.
$print_constraint =~ s/\\s+/ /sgm ;
#take last CR that is now space and remove it
$print_constraint =~ s/; /;/sgm ;
print "DEBUG :$print_constraint:\\n" if $::DEBUG ;
#Convert unique to primary when id .
my $changed_to_pk=0 ;
if ($print_constraint =~ /unique\\s*\\(\\s*(${tab}_)?id\\s*\\)/i) {
$print_constraint =~ s/unique/PRIMARY KEY/i ;
$changed_to_pk=1 ;
}
if ($print_constraint =~ /primary key/i) {
my $currpkname ;
if ( $print_constraint =~ /constraint (\\w+);/i ) {
$currpkname=$1 ;
}
else {
$currpkname="No Name";
}
if ( ! ( $currpkname =~ /${tab}_pk/ ) ) {
my $newpkname=${tab} . "_pk" ;
$print_constraint =~ s/(\\sCONSTRAINT \\w+)?;/ CONSTRAINT
$newpkname;/ ;
$print_constraint .= " { renamed primary key $currpkname to
$newpkname }" ;
}
if ( $changed_to_pk ) {
$print_constraint .= " { changed unique constraint to
primary key }" ;
}
push( @{$pks{$tab}}, "$print_constraint") ;
$htExists{$tab}=1;
}
else {
$print_constraint =~ /ADD CONSTRAINT ((UNIQUE)|(FOREIGN KEY)) \\
(/ ;
my $cons_type = ( $1 eq "FOREIGN KEY" ? "fk" : "un" ) ;
my $currfkname ;
if ( $print_constraint =~ /constraint (\\w+);/i ) {
$currfkname=$1 ;
}
else {
$currfkname="No Name";
}
if ( ( ! ( $currfkname =~ /${tab}_${cons_type}\\d+/ ) ) or
exists($fk_names{$currfkname}) ) {
my $fknum=1;
my $newfkname=${tab} . "_" . ${cons_type} . $fknum ;
while ( exists($fk_names{$newfkname}) ) {
++$fknum ;
$newfkname=${tab} . "_" . ${con