PHP / SQLite - Copying a table from disk to memory

Question

I have a sqlite3 database on my harddrive (file.db) with 5 tables. I'd like to copy 3 of these tables to an in-memory database (:memory:).

Is there a simple way to do so using PHP5's PDO format?

Answer 1

Not a pdo-specific solution that may or may not be sufficient in your case:

• create a :memory: database
• Attach the existing database file
• CREATE TABLE ... AS SELECT * FROM ...
• Detach the database file

edit: an example
First an example database stored in mydb.sq3

<?php
$pdo = new PDO('sqlite:mydb.sq3');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE foo(x INTEGER PRIMARY KEY ASC, y, z)');

$stmt = $pdo->prepare("INSERT INTO foo (x,y,z) VALUES (:x,:y,:z)");
$stmt->bindParam(':x', $x);
$stmt->bindParam(':y', $y);
$stmt->bindParam(':z', $z);

for($x=0; $x<100; $x++) {
    $y = $x*2;
    $z = $x*2+1;
    $stmt->execute();
}

Now we have a :memory: database and want to transfer the table foo

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('ATTACH "mydb.sq3" as filedb');
$pdo->exec('CREATE TABLE bar AS SELECT * FROM filedb.foo');
$pdo->exec('DETACH filedb');

Done. But let's take a look at the sqlite_master table

foreach($pdo->query('SELECT sql FROM sqlite_master') as $row) {
    echo $row['sql'];
}

this prints

CREATE TABLE bar(x INT,y,z)

The INTEGER PRIMARY KEY ASC declaration is lost. Might be sufficient though....

Answer 2

If that's what you need to do, then VolkerK's answer is the one I'd provide, but I feel that I have to point out that you're going to read the contents of those tables into memory each time you run that code (every time that page loads?), so it might be better just to query the data files from disk.

Answer 3

Note that one could always use some kind of shared memory mechanism (e.g. APC, memcache, etc..) to keep sqlite's in-memory databases persistent across connections.

Answer 4

You can dump the database at the end of the connection, save it as apc variable and then load and run again from apc at the beginning of the next execution.

Answer 5

Using the method outlined by VolkerK roughly doubled the performance of my code when using a ~150Mb sqlite database.

Loading the database into an in-memory sqlite db didn't require any other changes to my existing code.

My use case was batch processing data so I didn't have to deal with the problems Wez Furlong highlights.

Reading the data into memory was surprisingly fast. The whole 150Mb was loaded into memory from SSD in less than two seconds. It seemed too good to be true so I checked and rechecked the data.

Many thanks to VolkerK for a magic solution!

I also found that when I indexed the in-memory tables my queries executed three times faster

//adapting VolkerK's example...
//loop through tables from the local sqlite db 
$tables = array('companies', 'directors',  'previous_names');
foreach($tables as $table){
    //load each table into memory
    $pdo->exec("CREATE TABLE $table AS SELECT * FROM filedb.$table");

    //index each table on the relevant columns
    $pdo->exec("CREATE INDEX IF NOT EXISTS `".$table."_company_number` 
                ON $table (`company_number`);");
}

Answer 6

If you need a small database for tests, you may export your databse to an SQL file and then execute it as a single query in PHP:

class EphemeralPDO extends \PDO {
  public function __construct() {
    parent::__construct('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    $queries = file_get_contents(__DIR__ . '/database.export.sql');
    $this->exec($queries);
  }
}