使用 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屋!