Regexp for replacing quotes in database insert statements

Question

I'm converting a sqlite3 database to mysql.

I have a nice command file for sed that changes AUTOINCREMEMT and the other things needed, but I'm stuck on the last one: double quotes.

sqlite3 dump format:

CREATE TABLE "products" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  "name" varchar(255), 
  "desc" varchar(255) );

INSERT INTO "products" VALUES(1,'Flux Capacitor',
  'This is the "real" thing.\nPromise!')

For the first statement, I can replace all double quotes with backticks and mysql will be happy.

However, my product information has double quotes in the data. How can I exclude these from being replaced? I was trying to replace only those double quotes with a placeholder, then I could replace all the other double quotes, then I could change the placeholder back, but my regex-fu isn't up to par.

This was as far as I got:

/"[^"]*"/s

... to match the double quoted texts, but I couldn't figure out how to qualify that only double quotes inside single quotes should be matched.

Answer 1

I would change MySQL to accept double-quotes as identifier delimiters. This is standard SQL behavior, and you can make MySQL behave that way with a SQL mode:

mysql> SET SQL_MODE = ANSI;

Or more specifically:

mysql> SET SQL_MODE = ANSI_QUOTES;

Then MySQL should understand your data dump.

See "Server SQL Modes" for more information.

Answer 2

Well I know how to easily solve it in PHP with preg_replace_callback():

<?php

    $sql = file_get_contents('sqlite3 dump.txt');
    function callback($match) { return str_replace('"', '`', $match[0]); }
    $sql = preg_replace_callback('/CREATE TABLE .*?;/s', callback, $sql);
    echo preg_replace_callback('/INSERT INTO .*? VALUES/s', callback, $sql);

?>

Unless you can "SET SQL_MODE = ANSI_QUOTES" as Bill Karwin said.

Answer 3

I can replace all double quotes with backticks and mysql will be happy.

Happy for now, but it wouldn't have solved the whole problem, so could easily fall over in the future. Apostrophe and backslash also work differently in MySQL.

my product information has double quotes in the data. How can I exclude these from being replaced?

You can't reliably. SQL syntax is actually quite complex, and cannot in the general case be parsed by regex hacking.

Bill's suggestion with changing SQL_MODE to fit the existing syntax is a much better approach. I run MySQL in ANSI mode all the time, as I dislike having to tailor my apps to one particular database's foibles.