Substitutions Within Substitutions

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

I need to write a script to search through a SQL file and add "NOT NULL" to all the table creation elements that don't otherwise specify. I'm a little bogged down on the mechanics, though. Here's a concrete example:

create table foo (
   somevar1 sometype,
   somevar2 sometype null,
   somevar3 sometype not null,
   somevar4 sometype(20),
   somevar5 sometype(20) null,
   somevar6 sometype(10))
[download]

In this case, I'd want to add the string "NOT NULL" to the end of variables 1, 4, and 6, and leave the others alone. I've entered the example in a nicely indented fashion, but in practice, I have no control over the formatting. There might be any amount or type of whitespace wherever SQL allows it. Also, there might be any number of "create table" blocks in a given file.

I've got a pattern that gets me most of the way there, but it's only hitting the last element in each create table list. How do I do this correctly?

$str =~ s{
   (create\s+table\s+\w+\s*\()   # "create table foo (", save as $1
   (                             # Grab this pattern (each of the crea
+te table variables), save as $2
      (                          # Save as $3
         \s*                     # Any spaces
         \w+                     # Variable name
         \s+                     # At least one space
         \w+                     # Variable type
         (                       # Optional (nnn) for character string
+ types, save as $4
            \(                   # Literal open paren
            \d+                  # Any number of digits
            \)                   # Literal close paren
         )?                      # The ? makes it optional
         \s*                     # Any spaces
      )                          # "varname vartype " (not the null/no
+t null part)
      (                          # Save as $5
         [\w\s]*                 # Any words and/or spaces ("null" or 
+"not null", specifically)
      ) 
      (                          # Save as $6
         ,?                      # Optional comma (optional as it may 
+be the last element)
         \s*                     # Any spaces
      )
   )+                            # Match this whole section at least o
+nce
   (\s*\)\s*)                    # Closing paren for create table bloc
+k with surrounding whitespace, save as $7
}
{
      print $1;
      print $3;
      if ($5 =~ /null/i) {
         print $5;
      } else {
         print " NOT NULL";
      }
      print $6;
      print $7;
}gex;

# Output: create table foo (somevar6 sometype(10) NOT NULL)
[download]

---
A fair fight is a sign of poor planning.

Comment on Substitutions Within Substitutions Select or Download Code

Replies are listed 'Best First'.
Re: Substitutions Within Substitutions by BrowserUk (Patriarch) on May 02, 2006 at 22:43 UTC
This* seems to work: #! perl -slw use strict; local $/ = ''; # paragraph mode while( my $clause = <DATA> ) { $clause =~ s[ ( create \s+ table \s+ \S+ \s+ $ ) ( .+ ) ( $ \s* )$ ]{ my( $x, $y, $z ) = ( $1, $2, $3 ); $y =~ s[(?<!null)(,\|\Z)][ not null$1 ]g; "$x$y$z"; }esx; print $clause; } __DATA__ create table foo ( somevar1 sometype, somevar2 sometype null, somevar3 sometype not null, somevar4 sometype(20), somevar5 sometype(20) null, somevar6 sometype(10)) create table foo ( somevar1 sometype , somevar2 sometype null , somevar3 sometype not null, somevar4 sometype (20), somevar5 sometype(20) null, somevar6 sometype(10 )) create table foo ( somevar1 sometype, somevar2 sometype null, somevar3 sometype not null, somevar4 sometype(20), somevar5 sometype(20) null, somevar6 sometype(10)) [download] Produces C:\test>junk3 create table foo ( somevar1 sometype not null, somevar2 sometype null, somevar3 sometype not null, somevar4 sometype(20) not null, somevar5 sometype(20) null, somevar6 sometype(10) not null ) create table foo ( somevar1 sometype not null, somevar2 sometype null not null, somevar3 sometype not null, somevar4 sometype (20) not null, somevar5 sometype(20) null, somevar6 sometype(10 ) not null ) create table foo ( somevar1 sometype not null, somevar2 sometype null +, somevar3 sometype not null, somevar4 sometype(20) not null, somevar5 sometype( +20) null, somevar6 sometype(10) not null ) [download] *Note: I've use a simplified regex to demostrate the nested substitution technique. You will probably need to improve it match SQl syntax. Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal? "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice.	[reply] [d/l] [select]
Re: Substitutions Within Substitutions by Anonymous Monk on May 02, 2006 at 22:48 UTC
This is slightly easier on (my) eyes... my $text = <<END; create table foo ( somevar1 sometype, somevar2 sometype null, somevar3 sometype not null, somevar4 sometype(20), somevar5 sometype(20) null, somevar6 sometype(10)) END my $header = qr/create\s+table\s+\w+\s$\s/; my $name = qr/\w+\s/; my $type = qr/\w+\s/; my $op_char = qr/(?:\(\d+$\s)?/; my $words = qr/(?:\w+\s)/; my $decl = qr/$name $type $op_char $words/x; my $comma = qr/,\s/; my $rest = qr/\)\s/; $text =~ s{($header) ((?: $decl $comma) $decl) ($rest)} { $1 . (join ",", map {$_ . (/null\s*$/ ? "" : " NOT NULL")} split(/,/, $2)) . $3 }gex; print $text; [download]	[reply] [d/l]
Re: Substitutions Within Substitutions by davidrw (Prior) on May 02, 2006 at 22:00 UTC
what about cheating a little and taking this approach (either way, i think negative look-ahead is going to help; see perlre) `$_ = do {local $/=undef; <DATA>}; s/ (sometype$\d+$\|sometype)(?!.?(not )?null\b.?)/ $1 NOT NULL/mgi; print; __DATA__ create table foo ( somevar1 sometype, somevar2 sometype null, somevar3 sometype not null, somevar4 sometype(20), somevar5 sometype(20) null, somevar6 sometype(10)) ;` [download]	[reply] [d/l]
Re: Substitutions Within Substitutions by Sprad (Hermit) on May 02, 2006 at 22:18 UTC
I think I figured it out: $sql =~ s{ (create\s+table\s+\w+\s$) # "create table foo (", save as $1 ( # Save all the elements as $2 ( # Grab this pattern (each of the c +reate table variables), save as $3 \s # Any spaces \w+ # Variable name \s+ # At least one space \w+ # Variable type ( # Optional (nnn) for character str +ing types, save as $4 \( # Literal open paren \d+ # Any number of digits $ # Literal close paren )? # The ? makes it optional \s* # Any spaces [\w\s]* # Any words and/or spaces ("null" +or "not null", specifically) ,? # Optional comma (optional as it m +ay be the last element) \s* # Any spaces )+ # Match this whole section at leas +t once ) (\s\)\s) # Closing paren for create table b +lock with surrounding whitespace, save as $5 } { @elements = split(/,/, $2); foreach $elem (@elements) { if ($elem =~ /null\s*$/i) { next; } $elem .= " NOT NULL"; } $1 . (join ",", @elements) . $5 }gex; [download] I changed the regex to grab the whole block of table elements (getting rid of some unnecessary parens in the process), then just did an easy split and join on them inside the right hand block. Then I switched the prints to a concatenation so it would actually do a substitution. --- A fair fight is a sign of poor planning.	[reply] [d/l]
Re: Substitutions Within Substitutions by Fletch (Bishop) on May 03, 2006 at 02:02 UTC
And somewhat sidestepping the original problem, you might try and use something like SQL::Statement to parse your data (specifically probably the perldoc for "SQL::Statement::Structure" is of interest; see link in the aforementioned module's docs) and then manipulate that form and re-output the modified SQL after you've twiddled it accordingly. Update: Grrr, link to ...::Structure wasn't working. Reworded.	[reply]