No Such Table error from SQLite memory DB via DataMapper

Question

I have a Ruby program that uses DataMapper as an ORM to talk to an in-memory SQLite DB. This has been working fine, however I just recently added a new DM class, and corresponding table. To my surprise, things now blow up during an auto_migrate!

here is the SQL generated by DataMapper:

~ (0.000390) PRAGMA table_info("sensationd_channels")
~ (0.000010) PRAGMA table_info("sensationd_commands")
~ (0.000009) PRAGMA table_info("sensationd_configurations")
~ (0.000052) PRAGMA table_info("sensationd_measurements")
~ (0.000028) SELECT sqlite_version(*)
~ (0.000035) DROP TABLE IF EXISTS "sensationd_channels"
~ (0.000009) PRAGMA table_info("sensationd_channels")
~ (0.000423) CREATE TABLE "sensationd_channels" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "channel" INTEGER NOT NULL, "name" VARCHAR(50), "precision" INTEGER DEFAULT 11, "gain" INTEGER DEFAULT 1, "differential" BOOLEAN DEFAULT 'f', "configuration_id" INTEGER NOT NULL)
~ (0.000191) CREATE INDEX "index_sensationd_channels_configuration" ON "sensationd_channels" ("configuration_id")
~ (0.000015) DROP TABLE IF EXISTS "sensationd_commands"
~ (0.000009) PRAGMA table_info("sensationd_commands")
~ (0.000153) CREATE TABLE "sensationd_commands" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "action" INTEGER DEFAULT 1, "complete" BOOLEAN DEFAULT 'f', "issued_at" TIMESTAMP, "completed_at" TIMESTAMP)
~ (0.000015) DROP TABLE IF EXISTS "sensationd_configurations"
~ (0.000009) PRAGMA table_info("sensationd_configurations")
~ (0.000155) CREATE TABLE "sensationd_configurations" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "created_on" TIMESTAMP, "modified_on" TIMESTAMP, "name" VARCHAR(50) NOT NULL, "active" BOOLEAN)
~ (0.000015) DROP TABLE IF EXISTS "sensationd_measurements"
~ (0.000009) PRAGMA table_info("sensationd_measurements")
~ (0.000152) CREATE TABLE "sensationd_measurements" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "timestamp" TIMESTAMP, "measurement" VARCHAR(65535) NOT NULL, "channel_id" INTEGER NOT NULL, "channel_configuration_id" INTEGER NOT NULL)
~ (0.000175) CREATE INDEX "index_sensationd_measurements_channel" ON "sensationd_measurements" ("channel_id", "channel_configuration_id")
~ (0.000083) SELECT "id", "created_on", "modified_on", "name", "active" FROM "sensationd_configurations" WHERE "active" = 't' ORDER BY "id" LIMIT 1
~ (0.000073) INSERT INTO "sensationd_configurations" ("created_on", "modified_on", "name", "active") VALUES ('2011-08-01T12:36:18-07:00', '2011-08-01T12:36:18-07:00', 'Test U6-Pro Configuration, differential.', 't')
~ (0.000109) SELECT "id", "action", "complete", "issued_at", "completed_at" FROM "sensationd_commands" ORDER BY "issued_at" DESC LIMIT 1
~ (0.000086) INSERT INTO "sensationd_channels" ("channel", "name", "precision", "gain", "differential", "configuration_id") VALUES (0, '0', 11, 0, 't', 1)
~ no such table: sensationd_commands (code: 1, sql state: , query: SELECT "id", "action", "complete", "issued_at", "completed_at" FROM "sensationd_commands" ORDER BY "issued_at" DESC LIMIT 1, uri: sqlite3::memory:?scheme=sqlite&user=&password=&host=&port=&query=&fragment=&adapter=sqlite3&path=:memory:)

It looks like its creating the table goes fine, but then it can't be found just a few lines later. I would think I had mis-configured the DB connection, except that the other tables are found and work just fine.

Software at play:

• Ruby 1.9.2p289 via RVM
• SQLite3 @3.7.7.1 via MacPorts
• DataMapper gem v 1.1.0

Does anyone know why this is gimping out, and what I can do about it?

Answer 1

The problem is, I suspect, due to the thread pooling which DataMapper (or more accurately, DataObjects, the database driver DataMapper uses) does automatically. The database connection isn't shared between threads. This is fine (and beneficial, even) for something like postgresql or mysql or even sqlite3 as a 'file-backed' database. In the case of sqlite3's in memory store, the connection is the database. So additional threads will fail for that reason. Also, after a period of inactivity (~1 min?), the thread will get scavenged and the database will go away too.

If it is this, I'm not sure there's an easy work around. You might be able to modify do_sqlite3 to avoid this. Another alternative that should be basically as fast, is to use a file-backed sqlite3 DB on a ramdrive.

Answer 2

I copy my comment as answer by suggestion of @Lukas_Skywalker

Too late I know but maybe it can help someone. You can use memory SQLite shared cache using the special filename file::memory:?cache=shared. This allows separate database connections to share the same in-memory database. sqlite.org/inmemorydb.html