Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Regular Expression Problem

by curtisb (Monk)
on Nov 26, 2002 at 19:47 UTC ( [id://215913]=perlquestion: print w/replies, xml ) Need Help??

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

I'm having a bit of a problem. I know that I need to read the file which this is in. I'm able to read the file, but I just cannot see how to make this switch in the expression. I have this create table SQL statment. I'm adding a primary key to it. I need to add the phrase
CONSTRAINT PK_<table_name>
before the statment PRIMARY KEY. Does anyone know a quick way I could do thin inside a while loop?


Here is the SQL statment before the change.

CREATE TABLE "SO"."EL_HAND_RECEIPT" ( EL_ID NUMBER(20) NOT NULL, UIC VARCHAR2(6) NULL, ISSUE_RECEIPT_CD VARCHAR2(1) NOT NULL, PRIMARY KEY (EL_ID) ) TABLESPACE DATA /

Now here is what I need it to look like.

CREATE TABLE "SO"."EL_HAND_RECEIPT" ( EL_ID NUMBER(20) NOT NULL, UIC VARCHAR2(6) NULL, ISSUE_RECEIPT_CD VARCHAR2(1) NOT NULL, CONSTRAINT PK_EL_HAND_RECEIPT PRIMARY KEY (EL_ID) ) TABLESPACE DATA /

I have some what of a clue on how to do this but I'm not sure. could someone give a hand on this. It would be greatly appreciated.


Thanks,

Bobby Curtis

Replies are listed 'Best First'.
Re: Regular Expression Problem
by Chmrr (Vicar) on Nov 26, 2002 at 20:19 UTC

    To rephrase your problem in a way that may make the answer more obvious, you need to:

    1. Edit the file in-place
    2. Keep track of the most recent CREATE TABLE line
    3. Extract the table name out of it if it exists, and use it to keep up-to-date what table we're in
    4. Otherwise, replace every occurrance of the string "PRIMARY KEY" with the string "CONTRAINT PK_(table name) PRIMARY KEY"

    Armed with this knowledge, we can go about writing something that does this. I don't know enough about your problem to do #3 above reliably, so you'll probably have to change that regex in the following code:

    #!/usr/bin/perl -pi # The -p wraps the following code in a loop for each line, # and the -i tells perl we're editing the file in-place. # Give this program the filename(s) to change as arguments # on the command line. # Extract and update the table name if this is a CREATE TABLE line $table = $1 if (/CREATE TABLE "SO"."([^"]+)"/); # Change the "PRIMARY KEY" bit if we find it. s/(PRIMARY KEY)/CONTRAINT PK_$table $1/;

    I hope that makes some amount of sense.

    perl -pe '"I lo*`+$^X$\"$]!$/"=~m%(.*)%s;$_=$1;y^`+*^e v^#$&V"+@( NO CARRIER'

Re: Regular Expression Problem
by DamnDirtyApe (Curate) on Nov 26, 2002 at 20:14 UTC
    my $sql = <<'END_OF_SQL' ; CREATE TABLE "SO"."EL_HAND_RECEIPT" ( EL_ID NUMBER(20) NOT NULL, UIC VARCHAR2(6) NULL, ISSUE_RECEIPT_CD VARCHAR2(1) NOT NULL, PRIMARY KEY (EL_ID) ) TABLESPACE DATA / END_OF_SQL my $table = 'EL_HAND_RECEIPT' ; $sql =~ s/PRIMARY KEY/CONSTRAINT PK_$table PRIMARY KEY/; print $sql ;

    _______________
    DamnDirtyApe
    Those who know that they are profound strive for clarity. Those who
    would like to seem profound to the crowd strive for obscurity.
                --Friedrich Nietzsche
Re: Regular Expression Problem
by Wonko the sane (Deacon) on Nov 26, 2002 at 20:29 UTC
    Not really sure what you mean by inside a while loop.
    Unless you have several of these type of entries that this needs to be done to?

    Maybe something like this would do the trick?

    #!/usr/local/bin/perl use strict; my $data = join( '', <DATA> ); $data =~ s/(CREATE TABLE "SO"\.")([^"]+)(.+)(PRIMARY KEY)/$1$2$3CONSTR +AINT PK_$2 $4/ms; print $data; __DATA__ CREATE TABLE "SO"."EL_HAND_RECEIPT" ( EL_ID NUMBER(20) NOT NULL, UIC VARCHAR2(6) NULL, ISSUE_RECEIPT_CD VARCHAR2(1) NOT NULL, PRIMARY KEY (EL_ID) ) TABLESPACE DATA

    Outputs...

    :!test.pl CREATE TABLE "SO"."EL_HAND_RECEIPT" ( EL_ID NUMBER(20) NOT NULL, UIC VARCHAR2(6) NULL, ISSUE_RECEIPT_CD VARCHAR2(1) NOT NULL, CONSTRAINT PK_EL_HAND_RECEIPT PRIMARY KEY (EL_ID) ) TABLESPACE DATA
    If that doesnt help, post some code.

    Wonko

      Here is what I consturcted from your example. However, it only returns the first line where there is a creat table statment. I get nothing like what you got. Could you please take a look and let me know where I went wrong.
      Thanks, Bobby

      #!/usr/bin/perl -w use strict; my $file = "go.txt"; print "\nOpening $file\n"; open(FILE, "< $file") || die "OLD FILE ERROR: Unable to open file: $!\ +n"; open(OUT, "> $file.new") || die"NEW FILE ERROR: Unable to create new f +ile: $!\n"; while (<FILE>) { my $data = join('', <FILE>); $data =~ s/(CREATE TABLE "SO"\.")([^"]+)(.+)(PRIMARY KEY)/$1 $2 $3 + CONSTRAINT PK_$2 $4/ms; print OUT; } print "Closing $file\n"; close(OUT) || die "Unable to close OUT file: $!\n"; close(FILE) || die "Unable to close OLD file: $!\n";

        First, a note: the join operator is already reading the whole file into memory (the <FILE> is evaluated in list context, so it returns all the lines from the file), so there is no need for the while loop. You should just say:

        #!/usr/bin/perl -w use strict; my $file = "go.txt"; print "\nOpening $file\n"; open(FILE, "< $file") || die "OLD FILE ERROR: Unable to open file: $!\ +n"; open(OUT, "> $file.new") || die"NEW FILE ERROR: Unable to create new f +ile: $!\n"; my $data=join('',<FILE>);

        Anyway, there is an error in the regexp, and one in the modifiers. The line should be:

        $data =~ s/(CREATE TABLE "SO"\.")([^"]+)(.+?)(PRIMARY KEY)/$1$2$3CONSTRAINT PK_$2 $4/msg;

        I changed two things:

        • I changed from (.+) to (.+?), so that it matches the shortest string. Otherwise, when there are more than one "CREATE TABLE" in the file, it would match the first "CREATE" with the last "PRIMARY KEY".
        • I added the g modifier, so that every match will be changed.

        And, of course, then you have to print the result:

        print OUT $data; print "Closing $file\n"; close(OUT) || die "Unable to close OUT file: $!\n"; close(FILE) || die "Unable to close OLD file: $!\n";
        -- 
                dakkar - Mobilis in mobile
        
Re: Regular Expression Problem
by rdfield (Priest) on Nov 27, 2002 at 10:17 UTC
    This looks a bit like Oracle DDL...if it is you might want to consider altering your replacement text to something like:
    ...PRIMARY KEY (EL_ID) USING INDEX TABLESPACE INDEXES STORAGE (INITIAL ... NEXT ... MAXEXTENTS UNLIMITED PCTINCEASE 0)
    (replacing the ...'s with appropriate values), so that you have your indexes in a seperate tablespace to your data.

    rdfield

      OK, I have this working for the most part. However, I have one question how do I get USING INDEX TABLESPACE INDEXES added after the closing )?

      s/(PRIMARY KEY)/CONSTRAINT PK_$table $1/;
      can anyone help.

      Thanks,

      Bobby

        s/(PRIMARY KEY\([^)]\))/CONSTRAINT PK_$table $1 USING INDEX TABLESPACE + INDEXES/;
        as long as you have the primary key columns defined within brackets after "PRIMARY KEY".

        rdfield

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://215913]
Approved by tadman
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (5)
As of 2024-12-06 17:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Which IDE have you been most impressed by?













    Results (44 votes). Check out past polls.