DBIx::Class schema handling differs between in-memory and on-disk SQLite

Question

Update: Solved my problem

Again I have tripped over the behaviour of make_schema_at (see the comment in the code about @INC being modified, I already filed a bug report for that).

The reason my code below (with the correction mentioned in my first comment, i.e. defining

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

did not work in the case of the in-memory database was that make_schema_at disconnects the connection with handle $dbh! One can resolve this problem by passing a clone of the db handle to make_schema_at. I consider this behaviour of make_schema_at a bug, too, but maybe it's a matter of taste. I will discuss it and maybe file a bug report. I have decided to add an updated version of the program just in case someone else has the same problem:

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' );

Original Question:

I wanted to use an in-memory SQLite database for the tests of a Perl module. The idea is to create some tables in an in-memory SQLite database provided by DBD::SQLite, dump the DBIx::Class schema to disk using DBIx::Class::Schema::Loader, load the resulting schema and do my testing against this schema. (See e.g. http://www.modernperlbooks.com/mt/2012/04/make-a-dbic-schema-from-ddl.html for the rationale of handling a DBIx::Class schema like that.) I want to use an in-memory db because I don't want to do things such as writing to disk when a user installs the module.

My problem is that it makes a difference using such an in-memory database as opposed to using an on-disk SQLite database.

I prepared a complete example showing what I mean and in which changing one line for the other makes the difference. Assume that in a directory we have:

a file schema.sql whose contents is shown below and which creates a table "T", its contents should be irrelevant
an SQLite database sqlite_db not (yet) having a table "T"
the program SQLite_test.pl

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_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';

# (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' );

Now, running the program SQLite_test.pl as shown works (when run for the first time, before the next run one obviously needs to drop the table T, I did not want to complicate the example). But if one comments the line after (1) and comments out the line after (2), one gets the following error, saying that no table "T" could be found:

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 
...

Do you have an idea what I have overlooked?

Update: The environment is Windows 7 / Strawberry Perl 5.16.2.1.

Well, where do you think table T is supposed to get created at for the in memory table? — ugexe
I do not understand your question well, but it made me rethink. One thing that seems to be wrong is that when using the in-memory SQLite db, in the line my $dbic_schema = MySchema->connect( $dsn, q{}, q{} ); I make a connection to a +new+ database. I tried to fix this replacing this line by my $dbic_schema = MySchema->connect( sub { $dbh } ); But this still gives me an error "DBI Exception: DBD::SQLite::db prepare_cached failed: attempt to prepare on inactive database handle" How can I make sure the database handle $dbh is active when creating it using DBIx::RunSQL? — JoseDS
To summarize / clarify my last comment, the main points are: (1) Table T was created in one database, but then I connect to a different one(?) (2) A solution would be to use the database handle to the original database when connecting the DBIx::Class schema $dbic_schema (3) Reusing $dbh as described in my first comment does not work ("...inactive database handle..."). — JoseDS

Alexander Hartmaier Alexander Hartmaier · Accepted Answer · 2012-12-11T23:46:49

Connect to the new, in-memory SQLite db as you do and then run

$dbic_schema->deploy;

This will generate and run all required DDL statements against the database. No need to use DBIx::RunSQL at all because DBIx::Class already includes that functionality.

Note that you need to have SQL::Translator installed.

DBIx::Class schema handling differs between in-memory and on-disk SQLite

1 Answers