CODEWITHSUNDEEP
phpsqlite
Sosy
Sosy

Reputation: 173

How do I perform a .dump on SQLite database through PHP?

I need to get the results of the command line function .dump from an sqlite3 database, but through PHP. Specifically, I am using Zend_Db.

Is it possible to call the .dump command through the database adapter? If not, is there a work around, that is simpler than pulling each table schema, each row and write my own output?

I have tried simply running .dump as a query, but I get a general syntax error.

Upvotes: 2

Views: 2641

Answers (2)

ephestione
ephestione

Reputation: 43

I released a code snippet because I wanted to do exactly this, and noone had released a premade solution: https://www.ephestione.it/dump-sqlite-database-to-sql-with-php-alone/

<?php

$db = new SQLite3(dirname(__FILE__)."/your/db.sqlite");
$db->busyTimeout(5000);

$sql="";

$tables=$db->query("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';");

while ($table=$tables->fetchArray(SQLITE3_NUM)) {
    $sql.=$db->querySingle("SELECT sql FROM sqlite_master WHERE name = '{$table[0]}'").";\n\n";
    $rows=$db->query("SELECT * FROM {$table[0]}");
    $sql.="INSERT INTO {$table[0]} (";
    $columns=$db->query("PRAGMA table_info({$table[0]})");
    $fieldnames=array();
    while ($column=$columns->fetchArray(SQLITE3_ASSOC)) {
        $fieldnames[]=$column["name"];
    }
    $sql.=implode(",",$fieldnames).") VALUES";
    while ($row=$rows->fetchArray(SQLITE3_ASSOC)) {
        foreach ($row as $k=>$v) {
            $row[$k]="'".SQLite3::escapeString($v)."'";
        }
        $sql.="\n(".implode(",",$row)."),";
    }
    $sql=rtrim($sql,",").";\n\n";
}
file_put_contents("sqlitedump.sql",$sql);

Upvotes: 0

Noah
Noah

Reputation: 15340

The SQLite3 .dump command is part of the command shell, and not part of the database library itself.

See the section Special commands to sqlite3 on the page Command Line Shell For SQLite

The only way you can do this is via PHP exec()

Upvotes: 4

Related Questions