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