Regular Expression Problem

[curtisb]

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

[Chmrr]

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'

[DamnDirtyApe]

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

[Wonko the sane]

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

[curtisb]

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";

[dakkar]

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

[rdfield]

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

[curtisb]

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

[rdfield]

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