通过DataMapper从SQLite内存数据库中没有此类表错误 [英] No Such Table error from SQLite memory DB via DataMapper

问题描述

我有一个Ruby程序，该程序使用DataMapper作为ORM与内存中的SQLite DB进行通信。这一直很好，但是我最近才添加了一个新的DM类和相应的表。令我惊讶的是，现在在auto_migrate期间一切都爆了！

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!

这是DataMapper生成的SQL：

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.

正在使用的软件：

• 通过RVM的Ruby 1.9.2p289


• SQLite3 @ 3.7.7.1通过MacPorts


• DataMapper gem v 1.1.0

有人知道为什么会这样吗？

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

推荐答案

我怀疑问题是由于线程池由DataMapper（或更准确地说，DataObjects，DataMapper使用的数据库驱动程序）自动完成的。线程之间不共享数据库连接。对于诸如postgresql或mysql甚至sqlite3这样的文件支持数据库，这是很好的（甚至是有益的）。如果sqlite3位于内存存储中，则连接是。因此，其他线程将因此失败。另外，在一段时间不活动（〜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.

如果是这样，我就是不确定是否有容易解决的方法。您可能可以修改do_sqlite3以避免这种情况。基本上应该同样快的另一种替代方法是在ramdrive上使用文件支持的sqlite3 DB。

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.

这篇关于通过DataMapper从SQLite内存数据库中没有此类表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！