SQL scripts? Just do it.

The problem is that I have various .sql files containing database creation statements like:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo ...
...
...;

It would be wonderful to execute these sql files using DBI’s do method, but do can only execute one statement at a time. Ok, simple enough, just split the file on semicolon, right? Well, often you have stored routines that change the delimiter, mid-statement.

DELIMITER $$
CREATE PROCEDURE `ugh`..
BEGIN
  STMT;
  STMT;
END $$
DELIMITER ;

Never fear! A few lines of perl, and you have a bare bones SQL parser:

while ($file_text) {
  $file_text =~ s/^\s+?//s && do { next };
  $file_text =~ s/^DELIMITER\s+(\S+)//s && do {
    # Changing delimiter to $1
    $delimiter = $1;
    next;
  };
  $file_text =~ s/^(.+?)\Q$delimiter\E//s && do {
    # $1 is our sql statement
    push @sql, $1;
  };
}

Has anybody else solved this differently? system('msyql') is cheating ;-D

About Raster Burn

user-pic I blog about Perl.