DBIx:：类架构处理在内存中和磁盘上SQLite之间有所不同

Question

更新：解决了我的问题

我再次被make_schema_at的行为绊倒（请参阅代码中关于@INC被修改的注释，我已经为此提交了一份错误报告）。

下面是我编写代码的原因（以及我在第一条评论中提到的更正，即定义

my $dbic_schema = MySchema->connect( sub { $dbh } ); 

在内存数据库的情况下，make_schema_at会断开与handle$dbh的连接，这不起作用！可以通过将db句柄的克隆传递给make_schema_at来解决这个问题。我也认为MaxyStudiaAg的这种行为也是一种错误，但也许这是一个味觉的问题。我将对此进行讨论，并可能提交一份错误报告。我决定添加程序的更新版本，以防其他人也有同样的问题：

SQLite_test.pl

use strict;
use warnings;

use Test::More;

use DBI;
use DBIx::RunSQL;
use Class::Load qw (load_class);
use DBIx::Class::Schema::Loader qw/ make_schema_at /;
plan tests => 1;

my $table = 'T';

my $dsn   = 'dbi:SQLite:dbname=:memory:';

#Create our test table in the target database
my $dbh = DBIx::RunSQL->create(
    dsn     => $dsn,
    sql     => 'schema.sql',
    force   => 1,
    verbose => 1,
);

#Dump the DBIx::Class Schema in the current directory
my $attrs = {
    debug          => 1,
    dump_directory => '.',
};

#pass a clone of the database handle to make_schema_at since in the current
#version it will disconnect!
my $tmp_dbh = $dbh->clone();
make_schema_at( 'MySchema', $attrs, [ sub { $tmp_dbh }, {} ] );

#Import the resulting Schema

#In the current version, make_schema_at removes '.' from @INC,
#therefore we add it:
push @INC, '.';
eval {
    require MySchema;
    MySchema->import();
    1;
} or do {
    my $error = $@;
    croak $error;
};

#Connect to the Schema and use it to count the rows in table T (just as an example)
my $dbic_schema = MySchema->connect( sub { $dbh } );

my $result_source = $dbic_schema->source('T');
my $cls           = $result_source->result_class;

my $num_records = $dbic_schema->resultset($cls)->count;
is( $num_records, 5, 'check number of records' );

原始问题：

我想使用内存中的SQLite数据库来测试Perl模块。其想法是在DBD:：SQLite提供的内存中SQLite数据库中创建一些表，使用DBIx:：Class:：schema:：Loader将DBIx:：Class模式转储到磁盘，加载生成的模式，并针对该模式进行测试。（参见。http://www.modernperlbooks.com/mt/2012/04/make-a-dbic-schema-from-ddl.html这是处理DBIx:：类模式的基本原理。）我想使用内存中的数据库，因为我不想在用户安装模块时写入磁盘。

我的问题是，使用这样的内存数据库与使用磁盘上的SQLite数据库不同。

我准备了一个完整的例子来说明我的意思，其中一行换另一行会产生不同。假设目录中有：

• 文件模式。其内容如下所示并创建表“T”的sql，其内容应不相关

schema.sql：

CREATE TABLE T (
  id  INTEGER PRIMARY KEY,
  refid INTEGER,
  ud TEXT,
  dt TEXT,
  UNIQUE (ud,dt),
  CONSTRAINT fkey FOREIGN KEY (refid) REFERENCES T (id)
);

INSERT INTO T (id, refid, ud, dt) VALUES (1,1,'A','12.04.2011');
INSERT INTO T (id, refid, ud, dt) VALUES (2,1,'B1','12.04.2011');
INSERT INTO T (id, refid, ud, dt) VALUES (3,1,'BB','13.04.2011');
INSERT INTO T (id, refid, ud, dt) VALUES (4,4,'CCC','15.04.2011');
INSERT INTO T (id, refid, ud, dt) VALUES (5,4,'X','11.04.2011');

SQLite_测试。pl:

use strict;
use warnings;

use Test::More;

use DBI;
use DBIx::RunSQL;
use Class::Load qw (load_class);
use DBIx::Class::Schema::Loader qw/ make_schema_at /;
plan tests => 1;

my $table = 'T';

# (1) Using a database on disk works:
my $dsn = 'dbi:SQLite:dbname=sqlite_db';

# (2) Using an in-memory SQLite database not:
# my $dsn   = 'dbi:SQLite:dbname=:memory:';

#Create our test table in the target database
my $dbh = DBIx::RunSQL->create(
    dsn     => $dsn,
    sql     => 'schema.sql',
    force   => 0,
    verbose => 1,
);

#Dump the DBIx::Class Schema in the current directory
my $attrs = {
    debug          => 1,
    dump_directory => '.',
};

make_schema_at( 'MySchema', $attrs, [ sub { $dbh }, {} ] );

#Import the resulting Schema

#Note: in the current version, make_schema_at removes '.' from @INC,
#therefore we add it:
push @INC, '.';
eval {
    require MySchema;
    MySchema->import();
    1;
} or do {
    my $error = $@;
    croak $error;
};

#Connect to the Schema and use it to count the rows in table T
my $dbic_schema = MySchema->connect( $dsn, q{}, q{} );

my $result_source = $dbic_schema->source('T');
my $cls           = $result_source->result_class;

my $num_records = $dbic_schema->resultset($cls)->count;
is( $num_records, 5, 'check number of records' );

现在，运行SQLite_测试程序。如图所示的pl是有效的（当第一次运行时，在下一次运行之前，显然需要删除表T，我不想使示例复杂化）。但是，如果在（1）之后注释该行，并在（2）之后注释掉该行，则会出现以下错误，即无法找到表“T”：

DBI Exception: DBD::SQLite::db prepare_cached failed: no such table: T [for Statement "SELECT COUNT( * ) FROM T me"] at
C:/strawberry/perl/site/lib/DBIx/Class/Schema.pm line 1101.
        DBIx::Class::Schema::throw_exception('MySchema=HASH(0x1e49df4)', 'DBI Exception: DBD::SQLite::db prepare_cached
failed: no such...') called at C:/strawberry/perl/site/lib/DBIx/Class/Storage.pm line 112
        DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::SQLite=HASH(0x342c64c)', 'DBI Exception: DBD::
SQLite::db prepare_cached failed: no such...') called at C:/strawberry/perl/site/lib/DBIx/Class/Storage/DBI.pm line 1427

        DBIx::Class::Storage::DBI::__ANON__('DBD::SQLite::db prepare_cached failed: no such table: T [for ...', 'DBI::db
=HASH(0x3311c7c)', undef) called at C:/strawberry/perl/site/lib/DBIx/Class/Storage/DBI.pm line 2418 
...

你知道我忽略了什么吗？

更新：环境是Windows 7/Perl 5.16.2.1。

Answer 1

连接到新的内存中SQLite db，然后运行

$dbic_schema->deploy;

这将针对数据库生成并运行所有必需的DDL语句。根本不需要使用DBIx:：RunSQL，因为DBIx:：Class已经包含了该功能。

请注意，您需要安装SQL:：Translator。