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