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

5 Comments

Semicolon may also be in the string content (for ex. if you also fill database with data). I usually split on \n\n.

Maybe you should try DBIx::MultiStatementDo.

When I have to do this with MySQL, I just forego DBI and open a pipe to the mysql command line client, and dump it all in. Seems easier that way.

SQL::SplitStatement (and hence DBIx::MultiStatementDo) sometimes trips over scripts with stored procedures because the SQL end of statement marker is used inside procedures - e.g. RT #57971.

However, the author seems keen to fix this, so it may be the solution going fowards

Leave a comment

About Raster Burn

user-pic I blog about Perl.