EcalCondDB/sql/ASTEP1.pl

0001 #!/usr/bin/env perl
0002 #!/usr/bin/perl
0003
0004 use Getopt::Long;
0005
0006 $year;
0007 GetOptions('year=n' => \$year);
0008 if ((!$year) || (length $year > 2)) {
0009     print "Usage: ASTEP1.pl --year [year]\n";
0010     print "       [year] must be two digits\n";
0011     exit 0;
0012 }
0013
0014 $cyear = $year;
0015 if ($year < 10) {
0016     $cyear = "0" . $year;
0017 }
0018
0019 # first of all get the primary key for each table
0020 open IN, "pks.data";
0021 @buffer = <IN>;
0022 close IN;
0023
0024 %pk;
0025 foreach $line (@buffer) {
0026     chomp $line;
0027     $line =~ s/ +$//;
0028     ($table, $key) = split / +/, $line;
0029     $pk{$table} = $key;
0030 }
0031
0032 print "--> READ PRIMARY KEYS\n";
0033
0034 # then get the size of the tables and the maximum IOV value
0035 open IN, "partition.data";
0036 @buffer = <IN>;
0037 close IN;
0038
0039 %iov;
0040 foreach $line (@buffer) {
0041     chomp $line;
0042     $line =~ s/ +$//;
0043     ($table, $key, $size, $iovs) = split / +/, $line;
0044     if ($iovs == '') {
0045     $iovs = 1;
0046     } else {
0047     $iovs += 1;
0048     }
0049     if ((exists $pk{$table}) && ($pk{$table} == $key)) {
0050     $iov{$table} = $iovs;
0051     }
0052 }
0053
0054 print "--> READ TABLE SIZE\n";
0055
0056 # then gets info on indexes
0057
0058 open IN, "indexes.data";
0059 @buffer = <IN>;
0060 close IN;
0061
0062 %index;
0063 $last_index_name = "";
0064 foreach $line (@buffer) {
0065     chomp $line;
0066     $line =~ s/ +$//;
0067     ($index_name, $table, $column) = split / +/, $line;
0068     if (exists $pk{$table}) {
0069     $indx = "  CREATE INDEX " . $index_name . " ON " . $table .
0070         " (" . $column .");\n";
0071 # treat index with multiple columns
0072     if ($last_index_name !~ m/^$index_name$/) {
0073         $last_index_name = $index_name;
0074     } else {
0075         $regexp = ", $column);\n";
0076         $index{$table} =~ s/..$/$regexp/;
0077         $indx = "";
0078     }
0079     if (!(exists $index{$table})) {
0080         $index{$table} = $indx;
0081     } else {
0082         $index{$table} .= $indx;
0083     }
0084     }
0085 }
0086
0087 # now create sql statements to copy tables
0088
0089 open IN, "recreate.sql";
0090 @buffer = <IN>;
0091 close IN;
0092
0093 $start = 0;
0094 $table_name = "";
0095 $sql = "";
0096 $sql2 = "";
0097 $constraints = "";
0098 $extrasql .= "  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255\n" .
0099     "  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n" .
0100     "  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)";
0101 open OUT, ">copytables.sql";
0102 @tables;
0103 @references;
0104 %deferred;
0105 $drop = "";
0106 foreach $line (@buffer) {
0107     next unless $line !~ m/^ +$/; # skip blank lines
0108     $line =~ s/ +$//;
0109     if (length($line) > 80) {
0110     chomp $line;
0111     }
0112     if ($line =~ m/CREATE TABLE/) {
0113     $table_name = $line;
0114     chomp $table_name;
0115     $table_name =~ s/\"$//;
0116     $table_name =~ s/.*\"//;
0117     if (length($table_name) > 27) {
0118         print "WARNING: TABLE $table_name has a name too long\n";
0119     }
0120     @references = ();
0121     if (exists $pk{$table_name}) {
0122         $ic = length($line) - 3;
0123         $c = nextChar($line, $ic);
0124         $line =~ s/(.).\" *$/\1$c\"/;
0125     }
0126     $start = 1;
0127     }
0128     if ($line =~ m/REFERENCES/) {
0129     $referredTable = $line;
0130     ($part1, $part2) = split / \(/, $referredTable;
0131     $refT = $part1;
0132     $refT =~ s/\"$//;
0133     $refT =~ s/.*\"//;
0134     if (exists $pk{$refT}) {
0135         $ic = length($part1) - 2;
0136         $c = nextChar($part1, $ic);
0137         $part1 =~ s/..$/$c\"/;
0138     }
0139     $referredTable = $part1;
0140     $referredTable =~ s/.*\"([^\"]+)\"/\1/;
0141     push @references, $referredTable;
0142     $line = $part1 . " (" . $part2;
0143     }
0144     if ($line =~ m/CONSTRAINT/) {
0145     @tmp = split / +/, $line;
0146     $ic = 1;
0147 #   $ic = length($tmp[2]) + length($tmp[1]) + length($tmp[0]) + 3 - 3;
0148     $c = nextChar($tmp[2], $ic);
0149 #   $line =~ s/(CONSTRAINT \"[^ ]+).\"/\1$c\"/;
0150     $line =~ s/(CONSTRAINT \")./\1$c/;
0151     $old_const_name = $tmp[2];
0152     $old_const_name =~ s/\"//g;
0153     $const_name = $old_const_name;
0154 #   $const_name =~ s/(.*).$/\1$c/;
0155     $const_name =~ s/^./$c/;
0156     $constraints .= "  ALTER TABLE " . $table_name . " RENAME CONSTRAINT " .
0157         $const_name . " TO " . $old_const_name . ";\n";
0158
0159 #
0160 # take into account automatic indexes
0161 #
0162     if (($line =~ m/PRIMARY KEY/) || ($line =~ m/UNIQUE/)) {
0163         $constraints .= "  ALTER INDEX " . $const_name . " RENAME TO " . $old_const_name . ";\n";
0164 # drop them from the list of indexes
0165         $regexp = "  CREATE INDEX $old_const_name ON $table_name [^\n]+";
0166         $index{$table_name} =~ s/$regexp/  /g;
0167     }
0168     }
0169     @try = split / +/, $line;
0170     $nTry = @try;
0171     if (($line =~ /TABLESPACE/) && ($nTry == 3) && ($start == 1)) {
0172     $sql .= $line;
0173     if (length $table_name > 27) {
0174         print "ERROR: $table_name name is too long!!!\n";
0175     }
0176     $sql .= "  PARTITION BY RANGE (\"" . $pk{$table_name} . "\")\n";
0177     $sql .= "  (PARTITION \"" . $table_name . "_" . $cyear .
0178         "\" VALUES LESS THAN (" . $iov{$table_name} . ")\n" . $extrasql . ",\n";
0179     $sql .= "  PARTITION \"" . $table_name . "_0" .
0180         "\" VALUES LESS THAN (MAXVALUE)\n" . $extrasql . "\n";
0181     $sql .= "  );\n";
0182     $newtable_name = $table_name;
0183     if (exists $pk{$table_name}) {
0184         $c = nextChar($newtable_name, length($newtable_name) - 1);
0185         $newtable_name =~ s/(.*).$/\1$c/;
0186     }
0187     $sql .= "  INSERT INTO " . $newtable_name .
0188         " (SELECT * FROM " . $table_name . ");\n";
0189     $sql .= "\n";
0190     $sql2 = "  DROP TABLE $table_name CASCADE CONSTRAINTS;\n";
0191     $sql2 .= "  RENAME " . $newtable_name . " TO " . $table_name . ";\n";
0192     $sql2 .= $constraints;
0193 # correct index creation statements substituting special characters with newlines
0194     $sql2 .= $index{$table_name};
0195     $sql2 .= "\n";
0196 # check if we need to defer this definition since it depends on other tables
0197     $toDefer = 0;
0198     if (exists $pk{$table_name}) {
0199         foreach $t (@references) {
0200         @alreadyDef = grep {/^$t$/} @tables;
0201         $n = @alreadyDef;
0202         if ($n == 0) {
0203             $toDefer = 1;
0204             if (exists $deferred{$newtable_name}) {
0205             $deferred{$newtable_name} .= "#" . $t;
0206             } else {
0207             $deferred{$newtable_name} = $t;
0208             }
0209         }
0210         }
0211     }
0212 #   if ($toDefer) {
0213 #       print "DEFERRING CREATION OF TABLE $newtable_name\n";
0214 #       $deferred{$newtable_name} .= "#" . $sql . "#" . $sql2;
0215 #   } else {
0216 #       if (exists $pk{$table_name}) {
0217 #       print OUT "  PROMPT Creating $newtable_name\n";
0218 #       print OUT $sql;
0219 #       $drop .= $sql2;
0220 #       push @tables, $newtable_name;
0221 #       print "CREATING TABLE $newtable_name\n";
0222 #       } else {
0223 #       push @tables, $table_name;
0224 #       print "KEEPING  TABLE $table_name\n";
0225 #       }
0226 #   }
0227     if (exists $pk{$table_name}) {
0228         if ($toDefer) {
0229         print "DEFERRING CREATION OF TABLE $newtable_name\n";
0230         $deferred{$newtable_name} .= "#" . $sql . "#" . $sql2;
0231         } else {
0232         print OUT "  PROMPT Creating $newtable_name\n";
0233         print OUT $sql;
0234         $drop .= $sql2;
0235         push @tables, $newtable_name;
0236         print "CREATING TABLE $newtable_name\n";
0237         }
0238     } else {
0239         push @tables, $table_name;
0240         print "KEEPING  TABLE $table_name\n";
0241     }
0242     $start = 0;
0243     $sql = "";
0244     $constraints = "";
0245     }
0246     if ($start == 1) {
0247     $sql .= $line;
0248     }
0249 }
0250
0251 print "========================================================\n";
0252 print "List of already defined table\n";
0253 foreach $t (@tables) {
0254     print "$t\n";
0255 }
0256 print "========================================================\n";
0257
0258 # print remaining statements
0259 $loop = 1;
0260 while ($loop != 0) {
0261     @remKeys = keys %deferred;
0262     foreach $k (@remKeys) {
0263     @array = split/\#/,$deferred{$k};
0264     $lar = @array;
0265     $sql = $array[$lar - 2];
0266     $sql2 = $array[$lar - 1];
0267 #   @buf = split /\n/, $sql;
0268 #   @required = ();
0269 #   foreach $line (@buf) {
0270 #       $table;
0271 #       if ($line =~ m/CREATE TABLE/) {
0272 #       $table = $line;
0273 #       $table =~ s/.$//;
0274 #       $table =~ s/.*\"//;
0275 #       }
0276 #   }
0277     pop @array;
0278     pop @array;
0279     $nRequired = @array;
0280     $nFound = 0;
0281     print "$k requires the definition of $nRequired tables\n";
0282     foreach $a (@array) {
0283         print "$k: checking $a...";
0284         @gres = grep {/^$a$/} @tables;
0285         $ngres = @gres;
0286         if ($ngres != 0) {
0287         print "Found! ";
0288         foreach $tt (@gres) {
0289             print "$tt ";
0290         }
0291         print "\n";
0292         } else {
0293         print "\n";
0294         }
0295         $nFound += $ngres;
0296     }
0297     if ($nFound == $nRequired) {
0298         print "$k: TABLE DUMPED\n";
0299         print OUT "  PROMPT Creating $k\n";
0300         print OUT $sql;
0301         $drop .= $sql2;
0302         delete $deferred{$k};
0303         push @tables, $k;
0304     }
0305     }
0306     @remKeys = keys %deferred;
0307     $loop = @remKeys;
0308     print "----------- Remaining keys $loop\n\n";
0309 }
0310
0311 close OUT;
0312
0313 open OUT, ">dropoldtables.sql";
0314 print OUT $drop;
0315 close OUT;
0316 exit 0;
0317
0318 sub nextChar() {
0319     my $line = @_[0];
0320     my $ic = @_[1];
0321     my @chars = split(//, $line);
0322     my $r = chr(ord($chars[$ic]) + 1);
0323     return $r;
0324 }
0325
0326 sub prevChar() {
0327     my $line = @_[0];
0328     my $ic = @_[1];
0329     my @chars = split(//, $line);
0330     my $r = chr(ord($chars[$ic]) - 1);
0331 }