使用 PHP/PDO 将 SQL 脚本运行到 PostgreSQL [英] Running SQL scripts to PostgreSQL using PHP/PDO

问题描述

我需要对我的数据库运行一些 SQL 脚本，以更新架构和数据(某种迁移).

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

因为在运行每个脚本之前需要检查一些逻辑，所以我正在编写一个小的 PHP 工具来执行脚本，但是我有一个简单的问题:我可以加载并执行一个简单"的 SQL 脚本(包括表操作)吗?, 触发器和存储过程更新)直接，还是应该在脚本中添加标记(标记每个句子的结束位置)，然后逐句运行脚本?

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?

对于数据库访问，我使用的是 PDO.

For the database access I'm using the PDO.

推荐答案

我今天遇到了类似的情况.

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

这篇关于使用 PHP/PDO 将 SQL 脚本运行到 PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！