1

I need to run some SQL scripts to my database, to update the schema and data (some kind of migration).

Because there is some logic to check before running each script, I'm writting a small PHP tool to execute the scripts, but I have a simple problem: Can I load and execute a "simple" SQL script (including table manipulation, triggers & stored procedures updates) directly, or should I add markers to the script (to mark where each sentence ends), and run the script sentence by sentence?

For the database access I'm using the PDO.

mHouses
  • 875
  • 1
  • 16
  • 36

1 Answers1

2

I had a similar situation today.

My solution is extremely simple, but is just smart enough to allow for comments and statements that span multiple lines.

// open script file

$scriptfile = fopen($script_path, "r");
if (!$scriptfile) { die("ERROR: Couldn't open {$scriptfile}.\n"); }

// grab each line of file, skipping comments and blank lines

$script = '';
while (($line = fgets($scriptfile)) !== false) {
    $line = trim($line);
    if(preg_match("/^#|^--|^$/", $line)){ continue; } 
    $script .= $line;
}

// explode script by semicolon and run each statement

$statements = explode(';', $script);
foreach($statements as $sql){
    if($sql === '') { continue; }
    $query = $pdo->prepare($sql);
    $query->execute();
    if($query->errorCode() !== '00000'){ die("ERROR: SQL error code: ".$query->errorCode()."\n"); }
}
DaveGauer
  • 1,255
  • 14
  • 25
  • That's interesting, but there is a problem: What if there are triggers or stored procedures, where you can change the delimiter and use another character instead of the semicolon? And what if there is a semicolon inside an update or insert statement? I'll have a look at Joomla's source code, to see how they manage sql scripts when installing components... – mHouses Jun 30 '15 at 06:01
  • Well, Joomla uses something like you: https://github.com/joomla/joomla-cms/blob/c41fcf023012741e230acac6acbe956f5c275c41/libraries/joomla/database/driver.php#L338. – mHouses Jun 30 '15 at 06:40
  • 1
    Yeah, my script definitely assumes that you have written the SQL statements yourself and can prevent edge cases. Otherwise you'll be doing *real* parsing and a simple script like this will *not* be sufficient. I always prefer the simple solution when it'll do the job. – DaveGauer Jun 30 '15 at 17:37