Substitutions Within Substitutions (Sprad)

Substitutions Within Substitutions

Sprad

created: 2006-05-02 17:36:06

perlmonks

node
author

prlmnks

node
author

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))

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 create 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/not 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 once
   (\s*\)\s*)                    # Closing paren for create table block 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)

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

Re: Substitutions Within Substitutions

davidrw

created: 2006-05-02 18:00:48

perlmonks

prlmnks

what about cheating a little and taking this approach (either way, i think negative look-ahead is going to help; see [perldoc://perlre])

$_ = do {local $/=undef; };
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))
;

Re: Substitutions Within Substitutions

Sprad

created: 2006-05-02 18:18:46

perlmonks

prlmnks

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 create table variables), 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
            [\w\s]*                 # Any words and/or spaces ("null" or "not null", specifically)
            ,?                      # Optional comma (optional as it may be the last element)
            \s*                     # Any spaces
      )+                            # Match this whole section at least once
   )
(\s*\)\s*)                          # Closing paren for create table block 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;

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.

Re: Substitutions Within Substitutions

BrowserUk

created: 2006-05-02 18:43:15

perlmonks

prlmnks

This* seems to work:

#! perl -slw
use strict;

local $/ = ''; # paragraph mode

while( my $clause =  ) {
    $clause =~ s[
        ( create \s+ table \s+ \S+ \s+ \( )
        ( .+ )
        ( \) \s* )$
    ]{
        my( $x, $y, $z ) = ( $1, $2, $3 );
        $y  =~ s[(?

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 )




*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.

Re: Substitutions Within Substitutions

Anonymous Monk

created: 2006-05-02 18:48:12

perlmonks

prlmnks

This is slightly easier on (my) eyes...

my $text = <

Re: Substitutions Within Substitutions

Fletch

created: 2006-05-02 22:02:29

perlmonks

prlmnks

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.