Running SQL scripts to PostgreSQL using PHP/PDO

Question

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.

openai_sucks · Accepted Answer · 2015-06-30 00:54:10Z

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"); }
}

answered Jun 30, 2015 at 0:54

openai_sucks

1,2831 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mHouses Over a year ago

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 Over a year ago

Well, Joomla uses something like you: github.com/joomla/joomla-cms/blob/….

openai_sucks Over a year ago

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.

Collectives™ on Stack Overflow

Running SQL scripts to PostgreSQL using PHP/PDO

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related