I have a webapp on my phone what uses SQLite, and I have no sqlite3 tool that I can call from CLI, so I needed a pure-PHP solution to dumping my database.

Nothing on the internet seems to be created for this purpose, or at least nothing that can be found in the first page of google.

So well, if this page won’t end up on the first page of google I’m just wasting my time.

Dammit.

So anyway:

<?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);

Or just find, edit, comment the code on GitHub.

This is also for self-reference.

Data types

MySQL

There’s a shitload of data types, really, just go look at the official documentation and bask in the glory of MySQL redundancy (j/k).

SQLite

Very few, reassuring and simple data types. Basically text, numbers, and “whatever” (blob). Again, go look at the official documentation.

Database connection

MySQL

$GLOBALS["dbcon"]=@mysqli_connect($dbhost, $dbuser, $dbpass);
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");
@mysqli_select_db($GLOBALS["dbcon"],$dbname);
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");
@mysqli_set_charset($GLOBALS["dbcon"],'utf8');
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");

SQLite

$db = new SQLite3(dirname(__FILE__)."/DB/db.sqlite");
$db->busyTimeout(5000);
// WAL mode has better control over concurrency.
// Source: https://www.sqlite.org/wal.html
$db->exec('PRAGMA journal_mode = wal;');
$db->exec('PRAGMA synchronous=NORMAL;');

(last couple of rows are only useful if you plan to have some -little- write-concurrency, otherwise don’t use them)

Very important thing to know: if you are writing code for a local-running application, SQLite connections will not “time out” as there’s no server to wait for your input, just a file on the disk (or memory, even!)

Queries

MySQL

$results=mysqli_query($GLOBALS["dbcon"],$query);

SQLite

$db->exec($query);

when you don’t expect results, so for INSERT, UPDATE or DELETE

$results=$db->query($query);

when you expect multiple results, or several fields in a row

$value=$db->querySingle($query);

when you want returned a single-value result, for example when the query is something like SELECT timestamp FROM records WHERE id=$number LIMIT 1 (for this, with MySQL, you should parse the results with mysqli_fetch_array or similar, and then select the first value with [0])

Fetch results

MySQL

$row=mysqli_fetch_array($results);

when you want both associative and indexed arrays,

$row=mysqli_fetch_assoc($results);

when you only need associative arrays, and

$row=mysqli_fetch_row($results);

if you want only indexed arrays.

SQLite

In parallel with above:

$row=$results->fetchArray();

$row=$results->fetchArray(SQLITE3_ASSOC);

$row=$results->fetchArray(SQLITE3_NUM);

Speed considerations

If you don’t need associative arrays, you should always go for indexed arrays, since both in MySQL and SQLite they are fetched significantly faster; also, even if by very little, fetching only associative arrays is still faster then having both associative and indexed fetched together (and you’re not going to need those both anyway).

Escaping

MySQL

mysqli_real_escape_string($GLOBALS["dbcon"],$string);

SQLite

SQLite3::escapeString($string);

this function is not binary safe though at the time of writing (hasn’t been for a while from what I understand…)

Database functions

Just converting the PHP functions won’t be sufficient for most.

Think about time functions for examples, or DEFAULT values, or NULLing a NOT NULL timestamp column to have it automatically assigned to CURRENT_TIMESTAMP, these things are not present in SQLite.

MySQL

DEFAULT CURRENT_TIMESTAMP

NOW(), YEAR(), TIMEDIFF(), and another shitload of functions.

SQLite

DEFAULT (Datetime('now','localtime'))

Several variations on the strftime() functions, of which the Datetime() above is an example.

Again, go look at the official documentation, as what you see above is my own translation for my own purposes, and you will find both in official sources and in the vast world of StackOverflow a plethora of readings and interpretations.