Populating and serving a temporary SQLite database file and serving.

Recently I had the requirement in PHP to populate a SQLite file with data from a MySQL database and make it available for download. I was using PDO objects for MySQL, and found I could do the same for SQLite, so the simple answer became to create a temporary SQLite file with PDO, populate it from the existing MySQL PDO object (by querying the required data and inserting it into the SQLite file-no shortcut there unfortunately) allow the user to download it and then delete it. The sample code below excludes the selection from the existing database (which is comparitively trivial) but includes the rest.
One major thing worth noting (and not illustrated here) is that if you're going to be inserting more than a few dozen records, it's worth wrapping everything in a transaction. This may seem overkill for a temporary file that will fail anyway and hit the try-catch if there's a probelm, but it results in massive performance improvements due to PHP doing all the SQL work in memory and writing the transaction to file in one operation. Just don't go doing it with hundreds of megabytes of data ;)

<?php function

outputDownloadHeaders

($path, $filename, $binary = false) { header("Pragma: public"); header("Expires: 0"); header('Cache-Control: no-store, no-cache, must-revalidate'); header('Cache-Control: pre-check=0, post-check=0, max-age=0', false); header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT'); // Browser specific headers $browser = $_SERVER['HTTP_USER_AGENT']; if(preg_match('/MSIE 5.5/', $browser) || preg_match('/MSIE 6.0/', $browser)) { header('Pragma: private'); // C in control must be lowercase header('Cache-control: private, must-revalidate'); // Must be a number for IE header("Content-Length: ".filesize($path)); } else { header("Content-Length: ".(string)(filesize($path))); } header('Content-Type: application/x-download'); header('Content-Disposition: attachment; filename="'.$filename.'"'); // Tell the client file is binary if ($binary) { header('Content-Transfer-Encoding: binary'); } } function populateSqlite($sqlitefilepath) { // Open the file-creates new file if necessary $pdo = new PDO("sqlite:".$sqlitefilepath); // Create table in the db if needed $pdo->query("CREATE TABLE IF NOT EXISTS test_table ". "(id INT NOT NULL, text VARCHAR(100) NOT NULL, PRIMARY KEY (id));"); // Populate with some data $pdo->query("INSERT INTO test_table (id, text) values (0, 'Hello');"); $pdo->query("INSERT INTO test_table (id, text) values (1, 'World');"); $pdo->query("INSERT INTO test_table (id, text) values (2, 'from');"); $pdo->query("INSERT INTO test_table (id, text) values (3, 'PHP');"); $pdo->query("INSERT INTO test_table (id, text) values (4, 'and');"); $pdo->query("INSERT INTO test_table (id, text) values (5, 'SQLite'); "); // Connection closed by scope return true; } // The filename to present to the client $dbfilename = 'sqlite.sq'; // Temporary file to hold the sqlite db on the host $dbfilepath = sys_get_temp_dir() . uniqid('sqlite') . '.sq'; try { // Populate new file with some data if (populateSqlite($dbfilepath)) { // Prompt the user to save the file locally outputDownloadHeaders($dbfilepath, $dbfilename, true); // Stream the file to the client $file = fopen($dbfilepath, 'rb'); if ($file) { while(!feof($file) and (connection_status()==0)) { print(fread($file, filesize($dbfilepath))); flush(); } fclose($file); } } } catch (Exception $ex) { echo "Oops, something went wrong: $ex->getMessage()"; } // Clean up the file unlink($dbfilepath); ?>

Posted via email from Matt's thoughts