[Dbix-class] $schema->deploy() to SQLite not creating with foreign keys.

[Todd Hunter]

Im trying to create a replica of a database that has multiple schemas with
SQLite (so i can build a test environment).  I build the Result objects and
when i deploy i get an error with the foreign keys.  The SQLite CREATE TABLE
instruction does not allow database names in the foreign key clause (if you
remove it, the table creates fine).

Is there a way to work around this issue?  Im not overly concerned if the
sqlite database doesn't contain the foreign key relationships.

Thanks
Todd


A full sample of the code.

package MyApp::Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_namespaces();
1;

package MyApp::Schema::Result::A;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('other_db.A');
__PACKAGE__->add_columns(qw/ a1 a2 /);
__PACKAGE__->set_primary_key('a1');
__PACKAGE__->has_many(bs =3D> 'MyApp::Schema::Result::B', 'b1');
1;

package MyApp::Schema::Result::B;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('other_db.B');
__PACKAGE__->add_columns(qw/ b1 b2 /);
__PACKAGE__->set_primary_key('b1');
__PACKAGE__->belongs_to(a =3D> 'MyApp::Schema::Result::A', 'b1');
1;

The main script:

use MyApp::Schema;

my $schema =3D MyApp::Schema->connect('dbi:SQLite:dbname=3Dtest.db','','',{=
});

my $res =3D $schema->storage->dbh_do(
     sub {
        my ($storage, $dbh) =3D @_;
        $dbh->do("attach database 'other.db' as other_db");
    }
);

$schema->deploy();

The error given is:

    DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI
Exception: DBD::SQLite::db do failed: near ".": syntax error [for Statement
"CREATE TABLE other_db.B (
      b1  NOT NULL,
      b2  NOT NULL,
      PRIMARY KEY (b1),
      FOREIGN KEY(b1) REFERENCES other_db.A(a1)
    )"] at dbi.pl line 17
     (running "CREATE TABLE other_db.B (
      b1  NOT NULL,
      b2  NOT NULL,
      PRIMARY KEY (b1),
      FOREIGN KEY(b1) REFERENCES other_db.A(a1)
    )") at dbi.pl line 17
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110716/5cc=
276b2/attachment.htm