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

Ok, this might be a silly question, but I cannot seem to figure out how to do this one. I have the following SQL code:
ALTER TABLE "ARSAMS"."TCMD_HEADER"
    ADD CONSTRAINT FK_TCMD_HEA_OWNER_CONTAINE (CONTAINER_OWNER_CD) 
    REFERENCES ARSAMS.CONTAINER_OWNER_CODE (CONTAINER_OWNER_CD)
/

[download]
The FK table name is where I have the problem. I need to insert FOREIGN KEY between the table name and the (. Doest anyone have any clues on how I can do this insert.
The help is greatly appreciated and good wisdom from the community.

Thanks
Bobby Curtis

Replies are listed 'Best First'.
Re: Inserting in an unknown point
by VSarkiss (Monsignor) on Oct 04, 2002 at 15:53 UTC

    Parsing SQL is not trivial, but if these statements are generated by a program, there's a good chance they're consistently simple enough that you can use a regex to do what you want. Assuming your constraint names aren't quoted (so they will match \w), and that the entire clause never breaks over two lines, this should work: 

    $sql =~ s/
  \bADD\s+CONSTRAINT\s+  # lead-in to clause
  (\w+)\s+               # the constraint name
  \(                     # opening paren of column list
/
  ADD CONSTRAINT $1 FOREIGN KEY (
/x;

    [download]
    (I haven't tested this code, BTW.) You can adjust this if you have quotes, and so on, but the basic idea is to match around it and then re-write it to what you want.

    HTH

[reply]
[d/l]
[select]
      Thanks, that did what I needed it to. Plus, I learned something new, Thanks again.

      Bobby
[reply]
Re: Inserting in an unknown point
by tommyw (Hermit) on Oct 04, 2002 at 13:53 UTC

    $string=~s/(/FOREIGN KEY (/ will insert it before the first open bracket (which happens to be the right place in this case, but may not in others...)

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
[d/l]
Re: Inserting in an unknown point
by robartes (Priest) on Oct 04, 2002 at 14:15 UTC
    If the FOREIGN KEY statement always follows immediately behind the fk table, and the fk table only shows up once in the sql string (i.e. if your sql string always looks like the above), you can simply do: 

    use strict;
my $sql='"ALTER TABLE "ARSAMS"."TCMD_HEADER" ADD CONSTRAINT FK_TCMD_HE
+A_OWNER_CONTAINE (CONTAINER_OWNER_CD) REFERENCES ARSAMS.CONTAINER_OWN
+ER_CODE (CONTAINER_OWNER_CD)"';
my $fk_table_name="FK_TCMD_HEA_OWNER_CONTAINE";
$sql=~s/($fk_table_name)/$1 FOREIGN KEY/;
print $sql;

    [download]

    CU
    Robartes-

[reply]
[d/l]
      Great!, but only one problem. How would you handle the table name changing. There are over 340 table names.

      Thanks
      Bobby
[reply]

Back to Seekers of Perl Wisdom