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.