File indexing completed on 2023-03-17 11:15:09
0001
0002
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
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
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
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
0072 if ($last_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
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 !~ ;
0108 $line =~ s/ +$//;
0109 if (length($line) > 80) {
0110 chomp $line;
0111 }
0112 if ($line =~ ) {
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 =~ ) {
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 =~ ) {
0145 @tmp = split / +/, $line;
0146 $ic = 1;
0147
0148 $c = nextChar($tmp[2], $ic);
0149
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
0155 $const_name =~ s/^./$c/;
0156 $constraints .= " ALTER TABLE " . $table_name . " RENAME CONSTRAINT " .
0157 $const_name . " TO " . $old_const_name . ";\n";
0158
0159
0160
0161
0162 if (($line =~ ) || ($line =~ )) {
0163 $constraints .= " ALTER INDEX " . $const_name . " RENAME TO " . $old_const_name . ";\n";
0164
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
0194 $sql2 .= $index{$table_name};
0195 $sql2 .= "\n";
0196
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
0213
0214
0215
0216
0217
0218
0219
0220
0221
0222
0223
0224
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
0259 $loop = 1;
0260 while ($loop != 0) {
0261 @remKeys = keys %deferred;
0262 foreach $k (@remKeys) {
0263 @array = split/\
0264 $lar = @array;
0265 $sql = $array[$lar - 2];
0266 $sql2 = $array[$lar - 1];
0267
0268
0269
0270
0271
0272
0273
0274
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 }