Check-in [689e787a2b]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Perltidy
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:689e787a2bd17231db6978edfe60703663c70baa
User & Date: cfuhrman 2015-08-18 19:34:21
Context
2015-08-18
20:09
perltidy with 100 max chars p/line check-in: 4279f5771d user: cfuhrman tags: trunk
19:34
Perltidy check-in: 689e787a2b user: cfuhrman tags: trunk
2015-01-09
18:25
Import jmglov toolkit Import Josh Glover's toolkit from the subversion vendor branch check-in: 79707b28c0 user: cfuhrman tags: trunk
Changes

Changes to db/Topological.pm.

179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
...
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
                $self->{_list}->{$root} = $list;

        }

        # iterate through each node, visiting it as we go
        foreach my $node (keys %{ $self->{_list} }) {
                $self->_dfsVisit($self->{_list}->{$node})
                        if $self->{_list}->{$node}->{color} eq "white";
        }

        # victory
        return $self;

}          # _dfs()

................................................................................
sub _dfsVisit
{
        my $self = shift;
        my $node = shift;

        # validate row
        croak "_dfsVisit(): First argument must be hash ref\n"
                unless $node
                and ref $node eq "HASH";

        # update time and color the node
        $self->{_time}++;

        $node->{color}    = "gray";
        $node->{discover} = $self->{_time};

        # iterate through adjacent edges in our graph
        foreach my $child (@{ $node->{children} }) {
                $self->_dfsVisit($self->{_list}->{$child})
                        if $self->{_list}->{$child}->{color} eq "white";
        }

        # color node black now that it has been processed
        $self->{_time}++;

        $node->{color}  = "black";
        $node->{finish} = $self->{_time};







|







 







|
|










|







179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
...
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
                $self->{_list}->{$root} = $list;

        }

        # iterate through each node, visiting it as we go
        foreach my $node (keys %{ $self->{_list} }) {
                $self->_dfsVisit($self->{_list}->{$node})
                    if $self->{_list}->{$node}->{color} eq "white";
        }

        # victory
        return $self;

}          # _dfs()

................................................................................
sub _dfsVisit
{
        my $self = shift;
        my $node = shift;

        # validate row
        croak "_dfsVisit(): First argument must be hash ref\n"
            unless $node
            and ref $node eq "HASH";

        # update time and color the node
        $self->{_time}++;

        $node->{color}    = "gray";
        $node->{discover} = $self->{_time};

        # iterate through adjacent edges in our graph
        foreach my $child (@{ $node->{children} }) {
                $self->_dfsVisit($self->{_list}->{$child})
                    if $self->{_list}->{$child}->{color} eq "white";
        }

        # color node black now that it has been processed
        $self->{_time}++;

        $node->{color}  = "black";
        $node->{finish} = $self->{_time};

Changes to db/ora2mysql.pl.

98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
...
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
...
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
...
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
...
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
...
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
...
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
...
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453

                my $sqlstr = "CREATE TABLE " . uc($table) . "\n(\n";

                foreach my $field (@{ $orahash->{$key}->{fields} }) {

                        $_ = $field->{type};

                TYPE:
                        {

                                /^z|Z|f|d|D|V/ && do {

                                        $sqlstr .=
                                                uc(
"$field->{field_name}  DATETIME ");
                                        last TYPE;

                                };          # Date type

                                (/^i/ || $field->{outformat} =~ /\%d/) && do {

                                        $sqlstr .= uc(
                                                 "$field->{field_name}   INT ");
                                        last TYPE;

                                };          # Integer

                                /.*/ && do {

                                        if ($field->{field_name} =~ /DATABASE/i)
                                        {
                                                $sqlstr .=
                                                        uc(
"DBASE VARCHAR($field->{vlength}) ");
                                        } else {
                                                $sqlstr .=
                                                        uc(
"$field->{field_name} VARCHAR($field->{vlength}) ");
                                        }

                                        last TYPE;

                                };          # anything else

                        }          # TYPE

                        $sqlstr .= " NOT NULL"
                                if ($field->{field_name} =~ /unit|line|xdate/i);

                        $sqlstr .= ",\n";

                }          # iterate through fields

                $sqlstr .=
                        " PRIMARY KEY (XDATE, UNIT, LINE)) TYPE = "
                        . TABLE_TYPE . ";\n\n";

                print "Creating $table ... \n";

                my $sth = $dbh->prepare($sqlstr);

                return undef unless $sth;

................................................................................
                my $ofields = [];
                my $mfields = [];
                my $binders = [];
                foreach my $field (@{ $orahash->{$class}->{fields} }) {

                        if ($field->{type} =~ /^z|Z|f|d|D|V/) {

                                push( @{$ofields},
                                      sprintf(
"TO_CHAR( %s, 'YYYY-MM-DD HH24:MI:SS' )",
                                              uc($field->{field_name})));

                        }          # this is datetime field
                        else {

                                push(@{$ofields}, uc($field->{field_name}));

                        }          # everything else
................................................................................
                        }

                        push(@{$binders}, "?");

                }          # iterate through fields

                # Generate Oracle SQL
                my $oracle =
                        sprintf("select %s from %s where xdate >= sysdate - "
                                        . $days,
                                join(", ", @{$ofields}),
                                $orahash->{$class}->{cdrtable});

                my $orasth = $dbh_ora->prepare($oracle);

                unless ($orasth) {
                        warn "$DBI::errstr \n";
                        warn $orahash->{$class}->{cdrtable}
                                . " will be skipped!\n";
                        next;
                }

                # Generate and Bind MySQL SQL
                my @a = split(/\./, $orahash->{$class}->{cdrtable});
                my $mysql = sprintf("insert into %s ( %s ) values ( %s )",
                                    uc($a[ scalar(@a) - 1 ]),
                                    join(", ", @{$mfields}),
                                    join(", ", @{$binders}));

                my $mysth = $dbh_mys->prepare($mysql);

                die "$DBI::errstr"
                        if ($mysth->err);

                $orasth->execute();

                my $counter   = 0;
                my $errct     = 0;
                my $starttime = [gettimeofday];

................................................................................
                if ($timediff != 0) {
                        $avg = $counter / $timediff;
                }          # if ($timediff != 0) {
                else {
                        $avg = "NA";
                }          # else (if ($timediff != 0))

                printf(" ... %5d record(s) processed @ %0.2f p/sec\n",
                        $counter, $avg);

                $stats->{ uc($a[ scalar(@a) - 1 ]) } = {
                                                         count => $counter,
                                                         avg   => $avg
                        }
                        unless ($counter == 0);

                $orasth->finish;
                $mysth->finish;

        }          # iterate through classes

}          # loadData()
................................................................................

sub oraStruc
{

        my $dbh_ora = shift;

        # Classes
        my $sth_class =
                $dbh_ora->prepare(
"select cdr_code, layout, cdrtable from cdr_class order by cdrtable, layout");

        die $DBI::errstr unless $sth_class;

        # Fields
        my $sth_fields = $dbh_ora->prepare(
                "select field_name, vstart,
    vlength, type, outformat from cdr_fields where layout=? order by vstart");
................................................................................
        die $DBI::errstr unless $sth_fields;

        $sth_class->execute();

        my $classes = $sth_class->fetchall_arrayref;

        die "$DBI::errstr"
                if ($sth_class->err);

        my $orahash = {};

        foreach my $class (@{$classes}) {

                $sth_fields->execute($class->[1]);

                die "$DBI::errstr"
                        if ($sth_fields->err);

                my $fields = $sth_fields->fetchall_arrayref;

                next if (scalar(@{$fields}) == 0);

                my $key = sprintf("%s:%s:%s",
                                  $class->[0],          # cdr_code
                                  $class->[1],          # layout
                                  $class->[2]
                );                            # cdrtable

                $orahash->{$key} = {
                                     cdr_code => $class->[0],
                                     layout   => $class->[1],
                                     cdrtable => $class->[2],
                                     fields   => [] };

................................................................................
        my $stats = shift;
        my $file  = shift;

        open(REPORT, "> $file");

        foreach my $table (sort keys %{$stats}) {

                @report = ($table,
                           $stats->{$table}->{count},
                           sprintf("%5.2f", $stats->{$table}->{avg}));

                $mydate = strftime("%c", localtime(time));

                write REPORT;

        }

................................................................................

        GetOptions("gen-tables" => \$gentables,
                   "report-dir" => \$report_file,
                   "days"       => \$days,
        );

        my $dbh_ora = DBI->connect("dbi:Oracle:bill", "doc", "doc");
        my $dbh_mys = DBI->connect("dbi:mysql:database=stargate;host=mudslide",
                                   "tfc_user", "tfc_user");

        die $DBI::errstr unless ($dbh_ora && $dbh_mys);

        # disable auto-commit
        $dbh_mys->{'AutoCommit'} = 0;

        my $orahash = oraStruc($dbh_ora);
................................................................................

        genTables({
                    dbh_mysql => $dbh_mys,
                    orahash   => $orahash
                  }) if $gentables;

        loadData({
                   oracle  => $dbh_ora,
                   mysql   => $dbh_mys,
                   orahash => $orahash,
                   stats   => \%stats
        });

        $dbh_ora->disconnect;
        $dbh_mys->disconnect;

        genReports(\%stats, $report_file);








|




<
<
|






<
|






|
<
<
<
|

<
<
|









|





<
|
<







 







<
<
|
<







 







<
|
<
<
|





|
<













|







 







|
<




|
|







 







<
<
|







 







|








|








<
|







 







<
<
|







 







|
<







 







|
|
|
|







98
99
100
101
102
103
104
105
106
107
108
109


110
111
112
113
114
115
116

117
118
119
120
121
122
123
124



125
126


127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

143

144
145
146
147
148
149
150
...
193
194
195
196
197
198
199


200

201
202
203
204
205
206
207
...
213
214
215
216
217
218
219

220


221
222
223
224
225
226
227

228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
...
266
267
268
269
270
271
272
273

274
275
276
277
278
279
280
281
282
283
284
285
286
...
290
291
292
293
294
295
296


297
298
299
300
301
302
303
304
...
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330

331
332
333
334
335
336
337
338
...
369
370
371
372
373
374
375


376
377
378
379
380
381
382
383
...
397
398
399
400
401
402
403
404

405
406
407
408
409
410
411
...
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429

                my $sqlstr = "CREATE TABLE " . uc($table) . "\n(\n";

                foreach my $field (@{ $orahash->{$key}->{fields} }) {

                        $_ = $field->{type};

                    TYPE:
                        {

                                /^z|Z|f|d|D|V/ && do {



                                        $sqlstr .= uc("$field->{field_name}  DATETIME ");
                                        last TYPE;

                                };          # Date type

                                (/^i/ || $field->{outformat} =~ /\%d/) && do {


                                        $sqlstr .= uc("$field->{field_name}   INT ");
                                        last TYPE;

                                };          # Integer

                                /.*/ && do {

                                        if ($field->{field_name} =~ /DATABASE/i) {



                                                $sqlstr .= uc("DBASE VARCHAR($field->{vlength}) ");
                                        } else {


                                                $sqlstr .= uc("$field->{field_name} VARCHAR($field->{vlength}) ");
                                        }

                                        last TYPE;

                                };          # anything else

                        }          # TYPE

                        $sqlstr .= " NOT NULL"
                            if ($field->{field_name} =~ /unit|line|xdate/i);

                        $sqlstr .= ",\n";

                }          # iterate through fields


                $sqlstr .= " PRIMARY KEY (XDATE, UNIT, LINE)) TYPE = " . TABLE_TYPE . ";\n\n";


                print "Creating $table ... \n";

                my $sth = $dbh->prepare($sqlstr);

                return undef unless $sth;

................................................................................
                my $ofields = [];
                my $mfields = [];
                my $binders = [];
                foreach my $field (@{ $orahash->{$class}->{fields} }) {

                        if ($field->{type} =~ /^z|Z|f|d|D|V/) {



                                push(@{$ofields}, sprintf("TO_CHAR( %s, 'YYYY-MM-DD HH24:MI:SS' )", uc($field->{field_name})));


                        }          # this is datetime field
                        else {

                                push(@{$ofields}, uc($field->{field_name}));

                        }          # everything else
................................................................................
                        }

                        push(@{$binders}, "?");

                }          # iterate through fields

                # Generate Oracle SQL

                my $oracle = sprintf("select %s from %s where xdate >= sysdate - " . $days,


                                     join(", ", @{$ofields}), $orahash->{$class}->{cdrtable});

                my $orasth = $dbh_ora->prepare($oracle);

                unless ($orasth) {
                        warn "$DBI::errstr \n";
                        warn $orahash->{$class}->{cdrtable} . " will be skipped!\n";

                        next;
                }

                # Generate and Bind MySQL SQL
                my @a = split(/\./, $orahash->{$class}->{cdrtable});
                my $mysql = sprintf("insert into %s ( %s ) values ( %s )",
                                    uc($a[ scalar(@a) - 1 ]),
                                    join(", ", @{$mfields}),
                                    join(", ", @{$binders}));

                my $mysth = $dbh_mys->prepare($mysql);

                die "$DBI::errstr"
                    if ($mysth->err);

                $orasth->execute();

                my $counter   = 0;
                my $errct     = 0;
                my $starttime = [gettimeofday];

................................................................................
                if ($timediff != 0) {
                        $avg = $counter / $timediff;
                }          # if ($timediff != 0) {
                else {
                        $avg = "NA";
                }          # else (if ($timediff != 0))

                printf(" ... %5d record(s) processed @ %0.2f p/sec\n", $counter, $avg);


                $stats->{ uc($a[ scalar(@a) - 1 ]) } = {
                                                         count => $counter,
                                                         avg   => $avg
                    }
                    unless ($counter == 0);

                $orasth->finish;
                $mysth->finish;

        }          # iterate through classes

}          # loadData()
................................................................................

sub oraStruc
{

        my $dbh_ora = shift;

        # Classes


        my $sth_class = $dbh_ora->prepare("select cdr_code, layout, cdrtable from cdr_class order by cdrtable, layout");

        die $DBI::errstr unless $sth_class;

        # Fields
        my $sth_fields = $dbh_ora->prepare(
                "select field_name, vstart,
    vlength, type, outformat from cdr_fields where layout=? order by vstart");
................................................................................
        die $DBI::errstr unless $sth_fields;

        $sth_class->execute();

        my $classes = $sth_class->fetchall_arrayref;

        die "$DBI::errstr"
            if ($sth_class->err);

        my $orahash = {};

        foreach my $class (@{$classes}) {

                $sth_fields->execute($class->[1]);

                die "$DBI::errstr"
                    if ($sth_fields->err);

                my $fields = $sth_fields->fetchall_arrayref;

                next if (scalar(@{$fields}) == 0);

                my $key = sprintf("%s:%s:%s",
                                  $class->[0],          # cdr_code
                                  $class->[1],          # layout

                                  $class->[2]);                            # cdrtable

                $orahash->{$key} = {
                                     cdr_code => $class->[0],
                                     layout   => $class->[1],
                                     cdrtable => $class->[2],
                                     fields   => [] };

................................................................................
        my $stats = shift;
        my $file  = shift;

        open(REPORT, "> $file");

        foreach my $table (sort keys %{$stats}) {



                @report = ($table, $stats->{$table}->{count}, sprintf("%5.2f", $stats->{$table}->{avg}));

                $mydate = strftime("%c", localtime(time));

                write REPORT;

        }

................................................................................

        GetOptions("gen-tables" => \$gentables,
                   "report-dir" => \$report_file,
                   "days"       => \$days,
        );

        my $dbh_ora = DBI->connect("dbi:Oracle:bill", "doc", "doc");
        my $dbh_mys = DBI->connect("dbi:mysql:database=stargate;host=mudslide", "tfc_user", "tfc_user");


        die $DBI::errstr unless ($dbh_ora && $dbh_mys);

        # disable auto-commit
        $dbh_mys->{'AutoCommit'} = 0;

        my $orahash = oraStruc($dbh_ora);
................................................................................

        genTables({
                    dbh_mysql => $dbh_mys,
                    orahash   => $orahash
                  }) if $gentables;

        loadData({
          oracle  => $dbh_ora,
          mysql   => $dbh_mys,
          orahash => $orahash,
          stats   => \%stats
        });

        $dbh_ora->disconnect;
        $dbh_mys->disconnect;

        genReports(\%stats, $report_file);

Changes to db/ora2pg.pl.

110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
...
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
...
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
...
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
...
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
...
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
...
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
...
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
...
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
{
        my $dbh  = shift;
        my $tabs = shift;

        foreach my $tab (@{ $tabs->{_sorted} }) {
                print STDERR "Loading $tab\n";

               # print header ... note we use the Postgres COPY cmd to load data
                print "COPY $tab FROM STDIN;\n";

                # build and execute sql
                my $sth = $dbh->prepare(sqlBuild($tabs->{$tab}))
			or die "Could not prepare SQL : " . $DBI::errstr . "\n";

                $sth->execute();

                # iterate through results
                while (my @a = $sth->fetchrow()) {

                        # normalize @a
................................................................................
                                        $a[$i] = '\N';
                                        next;
                                }

                                # is this a blank date/time?
                                if (     exists $a[$i]
                                     and not $a[$i]
                                     and $tabs->{$tab}->{cols}->[$i]->{type} eq
                                     "DATE") {
                                        $a[$i] = '\N';
                                        next;
                                }

                                # does this data contain a newline?
                                elsif ($a[$i]) {
                                        $a[$i] =~ s/\r\n/\\r/g;
                                        $a[$i] =~ s/\r/\\r/g;
                                        $a[$i] =~ s/\n/\\r/g;
                                        $a[$i] =~ s/\t/\\t/g;
                                }

                                # is this a binary object?
                                elsif ($tabs->{$tab}->{cols}->[$i]->{type} =~
                                        /[BC]LOG/) {
                                        $a[$i] = pqSerialize($a[$i]);
                                }

                          # catch the rare circumstance where the field is equal
                          # to 0
                                $a[$i] = 0
                                        if (exists $a[$i] and not $a[$i]);

                                # rare case
                                $a[$i] = " " if $a[$i] eq "";

                        }

                        printf("%s\n", join("\t", @a));
................................................................................
                        # increment counter
                        $count++;

                        # if this is a numeric column with a precision, set that
                        # as appropriate
                        if (     $_->{type} =~ /number/i
                             and $_->{prec}) {
                                printf("    %-20s NUMERIC(%d)",
                                        $_->{name}, $_->{prec});
                        } else {
                                printf("    %-20s %20s",
                                        $_->{name}, $map->{ $_->{type} });
                        }

                        # if this is a varchar column, print out it's length
                        printf("(%d) ", $_->{len}) if $_->{type} =~ /varchar2/i;

                        # is the column nullable?
                        print(" not null ") unless $_->{null} eq "Y";
................................................................................
                             and scalar @{ $tabs->{$tab}->{keys} } == 0) {
                                print "\n";
                        } else {
                                print ",\n";
                        }
                }

            # if there are no primary keys for this table, then skip primary key
                unless (scalar @{ $tabs->{$tab}->{keys} }) {
                        printf(");\n\n");
                        next;
                }

                # print primary keys
                printf("    PRIMARY KEY ( %s )",
                        join(", ", @{ $tabs->{$tab}->{keys} }));

                # print foreign keys, if any
                foreach my $fk (keys %{ $tabs->{$tab}->{foreign} }) {
                        printf( "\n    , FOREIGN KEY ( %s ) REFERENCES %s",
                                join( ", ",
                                      @{
                                              $tabs->{$tab}->{foreign}->{$fk} }
                                ),
                                $fk
                        );
                }

                # close things up
                printf("\n);\n\n");
        }

}          # tableCreate()
................................................................................
{
        my $dbh  = shift;
        my $ora  = shift;
        my $tabs = {};

        # create the appropriate statement handle
        my $sth1 =
                $dbh->prepare(  "SELECT T.TABLE_NAME, TC.COLUMN_NAME, "
                              . "TC.DATA_TYPE, TC.DATA_LENGTH, TC.NULLABLE, "
                              . "TC.DATA_PRECISION "
                              . "FROM  ALL_TABLES T, ALL_TAB_COLUMNS TC "
                              . "WHERE T.TABLE_NAME = TC.TABLE_NAME "
                              . "  AND T.OWNER      = TC.OWNER "
                              . "  AND T.OWNER = ? "
                              . "ORDER BY T.TABLE_NAME")
                or die $DBI::errstr . "\n";

        # execute
        $sth1->execute(uc $ora->{schema});

        # extract table information
        my $cache = {};
        while (my @a = $sth1->fetchrow()) {

                # this is for debuggery
                # next unless $a[0] =~ /billing_rpts/i;

                # create initial array if it has not already been created
                $tabs->{ $a[0] }->{cols} = []
                        unless (exists $tabs->{ $a[0] }->{cols}
                                and ref $tabs->{ $a[0] }->{cols} eq "ARRAY");

                # create data object
                $tabs->{ $a[0] }->{name} = $a[0];

                push( @{ $tabs->{ $a[0] }->{cols} },
                      {
                         name => $a[1],
................................................................................
                         prec => (exists $a[5]) ? $a[5] : "",
                      });

        }

        # get primary key information for each table
        my $sth2 =
                $dbh->prepare(  "SELECT CC.COLUMN_NAME "
                              . "FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS CC "
                              . "WHERE C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME "
                              . "  AND C.TABLE_NAME = CC.TABLE_NAME "
                              . "  AND C.OWNER = CC.OWNER "
                              . "  AND C.CONSTRAINT_TYPE = 'P' "
                              . "  AND C.OWNER = ? "
                              . "  AND C.TABLE_NAME = ? ")
                or die $DBI::errstr . "\n";

        # iterate through each table, grabbing primary key information
        foreach my $table (sort keys %{$tabs}) {

                # grab key info
                $sth2->execute(uc $ora->{schema}, $table);

................................................................................

                # grab key info
                $sth3->execute(uc $ora->{schema}, $table);

                $tabs->{$table}->{foreign} = {};
                while (my @b = $sth3->fetchrow()) {
                        $tabs->{$table}->{foreign}->{ $b[4] } = []
                                unless ($tabs->{$table}->{foreign}->{ $b[4] }
                                and ref $tabs->{$table}->{foreign}->{ $b[4] } eq
                                "ARRAY");

                     # [4] references the foreign key'd table and [5] the column
                        push(@{ $tabs->{$table}->{foreign}->{ $b[4] } }, $b[5]);

                        # add this table to the root table's children list
                        push(@{ $tabs->{ $b[4] }->{children} }, $table);

                }

................................................................................
        # grab configuration
        my $gen = $cfg->param(-block => 'general');
        my $ora = $cfg->param(-block => 'oracle');

        # build list of fields to extract
        foreach my $fld (@{ $tab->{cols} }) {

                push( @{$flds},
                      ($fld->{type} eq "DATE")
                      ? sprintf("TO_CHAR( %s, '%s' )",
                                $fld->{name}, ORA_DATE_FORMAT)
                      : $fld->{name});

                next if $df;

                # if this is a prime date field, store it
                foreach my $prm (@{ $gen->{timefields} }) {
                        if (uc $fld->{name} eq uc $prm) {
................................................................................
                        }
                }

        }

        # build the where clause if we need to
        my $where = ($df) ? "WHERE $df >= SYSDATE - " . $gen->{datarange} : ""
                if $gen->{datarange};

        # Finally, return the newly built SQL string
        return
                sprintf("SELECT %s from %s.%s %s",
                        join(", ", @{$flds}),
                        $ora->{schema}, $tab->{name}, $where);

}          # sqlBuild()

# ----------------------------------------------------------------------

main:
{
................................................................................
        );

        # -n implies -t
        $create = 1 if $noload;

        # does the configuration file exist?
        die "No such file : $file\n"
                unless $file and -e $file;

        # read in configuration file
        $cfg = Config::Simple->new($file);

        # get oracle environment settings
        my $ora = $cfg->param(-block => 'oracle');

        # connect to oracle instance
        my $dbh = DBI->connect($ora->{source}, $ora->{user}, $ora->{pass})
                or die "Could not connect to oracle : $DBI::errstr\n";

        # get tables
        my $tables = tableGet($dbh, $ora);

        # determine topological sorting order
        $tables->{_sorted} = tableSort($tables);

        # create them
        tableCreate($tables)
                if $create;

        dataLoad($dbh, $tables)
                unless $noload;

        # print Dumper $tables;

        # disconnect
        $dbh->disconnect();

}          # main()







|




|







 







|
<













|
<



|
|

|







 







|
<

<
|







 







|






<
|



|
<
<
<
<
<
<







 







|
|
|
|
|
|
|
|
|













|
|







 







|
|
|
|
|
|
|
|
|







 







|
|
<

|







 







<
|
|
<







 







|


<
<
<
|







 







|









|









|


|







110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
134
135
136
137
138
139
140
141

142
143
144
145
146
147
148
149
150
151
152
153
154
155

156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
255
256
257
258
259
260
261
262

263

264
265
266
267
268
269
270
271
...
275
276
277
278
279
280
281
282
283
284
285
286
287
288

289
290
291
292
293






294
295
296
297
298
299
300
...
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
...
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
...
423
424
425
426
427
428
429
430
431

432
433
434
435
436
437
438
439
440
...
503
504
505
506
507
508
509

510
511

512
513
514
515
516
517
518
...
521
522
523
524
525
526
527
528
529
530



531
532
533
534
535
536
537
538
...
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
{
        my $dbh  = shift;
        my $tabs = shift;

        foreach my $tab (@{ $tabs->{_sorted} }) {
                print STDERR "Loading $tab\n";

                # print header ... note we use the Postgres COPY cmd to load data
                print "COPY $tab FROM STDIN;\n";

                # build and execute sql
                my $sth = $dbh->prepare(sqlBuild($tabs->{$tab}))
                    or die "Could not prepare SQL : " . $DBI::errstr . "\n";

                $sth->execute();

                # iterate through results
                while (my @a = $sth->fetchrow()) {

                        # normalize @a
................................................................................
                                        $a[$i] = '\N';
                                        next;
                                }

                                # is this a blank date/time?
                                if (     exists $a[$i]
                                     and not $a[$i]
                                     and $tabs->{$tab}->{cols}->[$i]->{type} eq "DATE") {

                                        $a[$i] = '\N';
                                        next;
                                }

                                # does this data contain a newline?
                                elsif ($a[$i]) {
                                        $a[$i] =~ s/\r\n/\\r/g;
                                        $a[$i] =~ s/\r/\\r/g;
                                        $a[$i] =~ s/\n/\\r/g;
                                        $a[$i] =~ s/\t/\\t/g;
                                }

                                # is this a binary object?
                                elsif ($tabs->{$tab}->{cols}->[$i]->{type} =~ /[BC]LOG/) {

                                        $a[$i] = pqSerialize($a[$i]);
                                }

                                # catch the rare circumstance where the field is equal
                                # to 0
                                $a[$i] = 0
                                    if (exists $a[$i] and not $a[$i]);

                                # rare case
                                $a[$i] = " " if $a[$i] eq "";

                        }

                        printf("%s\n", join("\t", @a));
................................................................................
                        # increment counter
                        $count++;

                        # if this is a numeric column with a precision, set that
                        # as appropriate
                        if (     $_->{type} =~ /number/i
                             and $_->{prec}) {
                                printf("    %-20s NUMERIC(%d)", $_->{name}, $_->{prec});

                        } else {

                                printf("    %-20s %20s", $_->{name}, $map->{ $_->{type} });
                        }

                        # if this is a varchar column, print out it's length
                        printf("(%d) ", $_->{len}) if $_->{type} =~ /varchar2/i;

                        # is the column nullable?
                        print(" not null ") unless $_->{null} eq "Y";
................................................................................
                             and scalar @{ $tabs->{$tab}->{keys} } == 0) {
                                print "\n";
                        } else {
                                print ",\n";
                        }
                }

                # if there are no primary keys for this table, then skip primary key
                unless (scalar @{ $tabs->{$tab}->{keys} }) {
                        printf(");\n\n");
                        next;
                }

                # print primary keys

                printf("    PRIMARY KEY ( %s )", join(", ", @{ $tabs->{$tab}->{keys} }));

                # print foreign keys, if any
                foreach my $fk (keys %{ $tabs->{$tab}->{foreign} }) {
                        printf("\n    , FOREIGN KEY ( %s ) REFERENCES %s", join(", ", @{ $tabs->{$tab}->{foreign}->{$fk} }), $fk);






                }

                # close things up
                printf("\n);\n\n");
        }

}          # tableCreate()
................................................................................
{
        my $dbh  = shift;
        my $ora  = shift;
        my $tabs = {};

        # create the appropriate statement handle
        my $sth1 =
            $dbh->prepare(  "SELECT T.TABLE_NAME, TC.COLUMN_NAME, "
                          . "TC.DATA_TYPE, TC.DATA_LENGTH, TC.NULLABLE, "
                          . "TC.DATA_PRECISION "
                          . "FROM  ALL_TABLES T, ALL_TAB_COLUMNS TC "
                          . "WHERE T.TABLE_NAME = TC.TABLE_NAME "
                          . "  AND T.OWNER      = TC.OWNER "
                          . "  AND T.OWNER = ? "
                          . "ORDER BY T.TABLE_NAME")
            or die $DBI::errstr . "\n";

        # execute
        $sth1->execute(uc $ora->{schema});

        # extract table information
        my $cache = {};
        while (my @a = $sth1->fetchrow()) {

                # this is for debuggery
                # next unless $a[0] =~ /billing_rpts/i;

                # create initial array if it has not already been created
                $tabs->{ $a[0] }->{cols} = []
                    unless (exists $tabs->{ $a[0] }->{cols}
                            and ref $tabs->{ $a[0] }->{cols} eq "ARRAY");

                # create data object
                $tabs->{ $a[0] }->{name} = $a[0];

                push( @{ $tabs->{ $a[0] }->{cols} },
                      {
                         name => $a[1],
................................................................................
                         prec => (exists $a[5]) ? $a[5] : "",
                      });

        }

        # get primary key information for each table
        my $sth2 =
            $dbh->prepare(  "SELECT CC.COLUMN_NAME "
                          . "FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS CC "
                          . "WHERE C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME "
                          . "  AND C.TABLE_NAME = CC.TABLE_NAME "
                          . "  AND C.OWNER = CC.OWNER "
                          . "  AND C.CONSTRAINT_TYPE = 'P' "
                          . "  AND C.OWNER = ? "
                          . "  AND C.TABLE_NAME = ? ")
            or die $DBI::errstr . "\n";

        # iterate through each table, grabbing primary key information
        foreach my $table (sort keys %{$tabs}) {

                # grab key info
                $sth2->execute(uc $ora->{schema}, $table);

................................................................................

                # grab key info
                $sth3->execute(uc $ora->{schema}, $table);

                $tabs->{$table}->{foreign} = {};
                while (my @b = $sth3->fetchrow()) {
                        $tabs->{$table}->{foreign}->{ $b[4] } = []
                            unless ($tabs->{$table}->{foreign}->{ $b[4] }
                                    and ref $tabs->{$table}->{foreign}->{ $b[4] } eq "ARRAY");


                        # [4] references the foreign key'd table and [5] the column
                        push(@{ $tabs->{$table}->{foreign}->{ $b[4] } }, $b[5]);

                        # add this table to the root table's children list
                        push(@{ $tabs->{ $b[4] }->{children} }, $table);

                }

................................................................................
        # grab configuration
        my $gen = $cfg->param(-block => 'general');
        my $ora = $cfg->param(-block => 'oracle');

        # build list of fields to extract
        foreach my $fld (@{ $tab->{cols} }) {


                push( @{$flds}, ($fld->{type} eq "DATE")
                      ? sprintf("TO_CHAR( %s, '%s' )", $fld->{name}, ORA_DATE_FORMAT)

                      : $fld->{name});

                next if $df;

                # if this is a prime date field, store it
                foreach my $prm (@{ $gen->{timefields} }) {
                        if (uc $fld->{name} eq uc $prm) {
................................................................................
                        }
                }

        }

        # build the where clause if we need to
        my $where = ($df) ? "WHERE $df >= SYSDATE - " . $gen->{datarange} : ""
            if $gen->{datarange};

        # Finally, return the newly built SQL string



        return sprintf("SELECT %s from %s.%s %s", join(", ", @{$flds}), $ora->{schema}, $tab->{name}, $where);

}          # sqlBuild()

# ----------------------------------------------------------------------

main:
{
................................................................................
        );

        # -n implies -t
        $create = 1 if $noload;

        # does the configuration file exist?
        die "No such file : $file\n"
            unless $file and -e $file;

        # read in configuration file
        $cfg = Config::Simple->new($file);

        # get oracle environment settings
        my $ora = $cfg->param(-block => 'oracle');

        # connect to oracle instance
        my $dbh = DBI->connect($ora->{source}, $ora->{user}, $ora->{pass})
            or die "Could not connect to oracle : $DBI::errstr\n";

        # get tables
        my $tables = tableGet($dbh, $ora);

        # determine topological sorting order
        $tables->{_sorted} = tableSort($tables);

        # create them
        tableCreate($tables)
            if $create;

        dataLoad($dbh, $tables)
            unless $noload;

        # print Dumper $tables;

        # disconnect
        $dbh->disconnect();

}          # main()

Changes to scm/add-cvs-revnum.pl.

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
             and $node->has_properties()
             and $node->has_property('svn:date')) {
                $lastrevnum  = $node->revnum();
                $lastrevdate = date $node->property('svn:date');
        }

        if (     $node->has_contents()
             and $node->content() =~
/\$Id: (.*?,v) ([1-9]+\.[0-9.]+) (\d{4}\/\d{2}\/\d{2}) (\d{2}:\d{2}:\d{2}) (\w+) Exp \$/
            ) {

                my $cvsrev = $2;
                my $date   = $3;
                my $time   = $4;
                $date =~ s/\//\-/g;
                my $nodedate = date "$date $time";

                if (defined $lastrevnum
                     and abs($lastrevdate - $nodedate) <= (3 * 60)) {

                        my $counter = 0;
                        $node->properties->add("cvs2svn:cvs-rev", $cvsrev,
                                               $counter++);
                        $node->properties->add("svn:eol-style", "native",
                                               $counter++);
                        $node->properties->add(
                                 "svn:executable",
                                 $node->property("svn:executable")
                                     || $filenames{ $node->header('Node-path') }
                                     ->{executable},
                                 $counter++
                            )
                            if ($node->has_property("svn:executable")
                                or
                                defined $filenames{ $node->header('Node-path') }
                                ->{executable});

                        $node->properties->del("svn:keywords");
                        $node->changed;

                        $filenames{ $node->header('Node-path') } = {
                                 cvsrev     => $cvsrev,
                                 executable => $node->property("svn:executable")
                                     || $filenames{ $node->header('Node-path') }
                                     ->{executable} }

                            if $node->has_header('Node-path');

                }

        } elsif (    $node->has_headers()
                 and $node->has_header('Node-path')
                 and defined $filenames{ $node->header('Node-path') }
                 and $node->has_contents()) {

                my $counter = 0;
                $node->properties->add("svn:eol-style", "native", $counter++);
                $node->properties->add(
                                 "svn:executable",
                                 $node->property("svn:executable")
                                     || $filenames{ $node->header('Node-path') }
                                     ->{executable},
                                 $counter++
                    )
                    if ($node->has_property("svn:executable")
                        or defined $filenames{ $node->header('Node-path') }
                        ->{executable});

                $node->properties->add("svn:keywords", $keywords, $counter++);
                $node->properties->del("cvs2svn:cvs-rev");
                $node->changed;

                delete $filenames{ $node->header('Node-path') };

        }

        $newdb->write_node($node);

}








<
|
<











|
<
|
<
|
<
<
|
<
<
<

<
|
<





|
|
<
<
>











|
<
<
|
<
<
<

|
<













62
63
64
65
66
67
68

69

70
71
72
73
74
75
76
77
78
79
80
81

82

83


84



85

86

87
88
89
90
91
92
93


94
95
96
97
98
99
100
101
102
103
104
105
106


107



108
109

110
111
112
113
114
115
116
117
118
119
120
121
122
             and $node->has_properties()
             and $node->has_property('svn:date')) {
                $lastrevnum  = $node->revnum();
                $lastrevdate = date $node->property('svn:date');
        }

        if (     $node->has_contents()

             and $node->content() =~ /\$Id: (.*?,v) ([1-9]+\.[0-9.]+) (\d{4}\/\d{2}\/\d{2}) (\d{2}:\d{2}:\d{2}) (\w+) Exp \$/) {


                my $cvsrev = $2;
                my $date   = $3;
                my $time   = $4;
                $date =~ s/\//\-/g;
                my $nodedate = date "$date $time";

                if (defined $lastrevnum
                     and abs($lastrevdate - $nodedate) <= (3 * 60)) {

                        my $counter = 0;
                        $node->properties->add("cvs2svn:cvs-rev", $cvsrev,  $counter++);

                        $node->properties->add("svn:eol-style",   "native", $counter++);

                        $node->properties->add("svn:executable",


                                               $node->property("svn:executable") || $filenames{ $node->header('Node-path') }->{executable}, $counter++)



                            if ($node->has_property("svn:executable")

                                or defined $filenames{ $node->header('Node-path') }->{executable});


                        $node->properties->del("svn:keywords");
                        $node->changed;

                        $filenames{ $node->header('Node-path') } = {
                                               cvsrev     => $cvsrev,
                                               executable => $node->property("svn:executable") || $filenames{ $node->header('Node-path') }->{executable}


                            }
                            if $node->has_header('Node-path');

                }

        } elsif (    $node->has_headers()
                 and $node->has_header('Node-path')
                 and defined $filenames{ $node->header('Node-path') }
                 and $node->has_contents()) {

                my $counter = 0;
                $node->properties->add("svn:eol-style", "native", $counter++);
                $node->properties->add("svn:executable",


                                       $node->property("svn:executable") || $filenames{ $node->header('Node-path') }->{executable}, $counter++)



                    if ($node->has_property("svn:executable")
                        or defined $filenames{ $node->header('Node-path') }->{executable});


                $node->properties->add("svn:keywords", $keywords, $counter++);
                $node->properties->del("cvs2svn:cvs-rev");
                $node->changed;

                delete $filenames{ $node->header('Node-path') };

        }

        $newdb->write_node($node);

}

scm/trace.sh became a regular file.