curtisb has asked for the wisdom of the Perl Monks concerning the following question:

First, I would like to thank everyone who helped me with the first question on this matter.
Now I have a new question, I can do the matching and changing of the datatypes need. However, I want to restrict this to only CREATE TABLE statements in a file. I have provided the script that works great; just needs to be refined.
I was thinking about encapulating these if-elsif statements in a if statement like so: 
if (<some variable> eq "CREATE TABLE")
{
              Do IF-ELSE statements;
}

[download]
Then close the while loop.
I was woundering if I'm on the right track, if not someone please put be back on.
Code that works
#!/usr/bin/perl -w

$gocount=$charcount=0;
$numcount=$floatcount=$realcount=0;
$datecount=$timestampcount=0;
$clobcount=$blobcount=0;
$longrawcount=$total=0;

print "\nWhat file would you like to open: ";
$file = <STDIN>;
$file =~ s/[\r\n]+//g;

print "\nAttempting to open $file\n";

open(FILE, "< $file") || die "FILE ERROR: Unable to open file: $!\n";
open(OUT, "> new-files/$file") || die"NEW FILE ERROR: Unable to create
+ new file: $!\n";

while (<FILE>)
{
    if (s/^\s*go\s*$/\/\n/gi)
    {
        $gocount++;
        print OUT "TABLESPACE ARSAMS\n";
        #print "$file:  found a match\n";

    }elsif (s/\bchar\b/VARCHAR2/gi || s/\bvarchar\b/VARCHAR2/gi)
        {
            $charcount++;
        }elsif(s/\bdecimal\b/NUMBER/gi || s/\binteger\b/NUMBER/gi || s
+/\bbigint\b/NUMBER/gi || s/\bbit\b/NUMBER/gi || s/\btinyint\b/NUMBER/
+gi || s/\bsmallint\b/NUMBER/gi || s/\bnumeric\b/NUMBER/gi || s/\bmone
+y\b/NUMBER/gi)
            {
                $numcount++;
            }elsif(s/\bfloat\b/FLOAT/gi)
                {
                    $floatcount++;
                }elsif(s/\breal\b/REAL/gi)
                    {
                        $realcount++;
                    }elsif(s/\bdate\b/DATE/gi || s/\btime\b/DATE/gi ||
+ s/\bsmalldatetime\b/DATE/gi || s/\bdatetime\b/DATE/gi)
                        {
                            $datecount++;
                        }elsif(s/\btimestamp\b/TIMESTAMP/gi)
                            {
                                $timestampcount++;
                            }elsif(s/\blongchar\b/CLOB/gi)
                                {
                                    $clobcount++;
                                }elsif(s/\bbinary\b/BLOB/gi || s/\bvar
+binary\b/BLOB/gi)
                                    {
                                        $blobcount++;
                                    }elsif(s/\blongbinary\b/LONG RAW/g
+i)
                                        {
                                            $longrawcount++;
                                        }
                

    $count++;
    print OUT;
    
}

#$gocount+$charcount+$numcount+$floatcount+$realcount+$datecount+$time
+stampcount+$clobcount+$blobcount+$longrawcount=$total;

print "\nThere are $count LINES in the file\n";
print "Changed $gocount GO in this file\n";
print "Changed $charcount CHAR in this file\n";
print "Changed $numcount NUMBER in this file\n";
print "Changed $floatcount FLOATS in this file\n";
print "Changed $realcount REAL in this file\n";
print "Changed $datecount DATE in this file\n";
print "Changed $timestampcount TIMESTAMP in this file\n";
print "Changed $clobcount CLOB in this file\n";
print "Changed $blobcount BLOB in this file\n";
print "Changed $longrawcount LONG RAW in this file\n";
#print "\nTotal number of changes: $total\n";

close(OUT) || die "Unable to close OUT file: $!\n";
close(FILE) || die "Unable to close file: $!\n";

[download]
Someone please help.
Thanks,
Bobby

Replies are listed 'Best First'.
Re: Matching Question #2
by Aristotle (Chancellor) on Sep 23, 2002 at 17:57 UTC
    First off, any time you have something like $gocount, $numcount, $charcount etc it's better to put them in a hash as in $count{go}, $count{num} etc. You can put your various alternatives in a hash as well and can then match on an autogenerated alternation. 
    #!/usr/bin/perl -w
use strict;

my %translation = (
    char          => 'VARCHAR2',
    varchar       => 'VARCHAR2',
    decimal       => 'NUMBER',
    integer       => 'NUMBER',
    bigint        => 'NUMBER',
    bit           => 'NUMBER',
    tinyint       => 'NUMBER',
    smallint      => 'NUMBER',
    numeric       => 'NUMBER',
    money         => 'NUMBER',
    float         => 'FLOAT',
    real          => 'REAL',
    date          => 'DATE',
    time          => 'DATE',
    smalldatetime => 'DATE',
    datetime      => 'DATE',
    timestamp     => 'TIMESTAMP',
    longchar      => 'CLOB',
    binary        => 'BLOB',
    varbinary     => 'BLOB',
    longbinary    => 'LONG RAW',
);

my %count;

my $rx = qr/@{[join '|', map quotemeta, keys %translation ]}/;

while (<>) {
    if(s/^\s*go\s*$/\/\n/gi) {
        $count{GO}++;
        print "TABLESPACE ARSAMS\n";
    }

    s/\b($rx)\b/$count{$translation{$1}}++; $translation{$1}/egi;

    $count{total}++;
    print OUT;
}

print "$_: $count{$_} changes in this file" for sort keys %count;

    [download]

    This is untested code, but should work modulo typos. See Mark-Jason Dominus' excellent Program Repair Shop and Red Flags article series on Perl.com for introductory pointers on how to write better code.

    Note that rather than try and open files itself, this script uses @ARGV for its input (so that Perl does error checking for us) and outputs to STDOUT which can be redirected (error checking by the shell). This is both lazy and flexible. Now you can say script file1 file2 file3 > outfile and get output just as requested.

    Makeshifts last the longest.

[reply]
[d/l]

Back to Seekers of Perl Wisdom