[Dbix-class] User error? SQLite problem? with DBIx::Class::Fixtures and populate

[luke saunders]

On Wed, Oct 8, 2008 at 12:56 AM, luke saunders <luke.saunders at gmail.com>wro=
te:

> On Tue, Oct 7, 2008 at 6:22 PM, Ashley <apv at sedition.com> wrote:
>
>> I'm messing around with writing some tests with DBIx::Class::Fixtures and
>> getting stuck. Trying to see if it's something obvious I'm doing wrong or
>> there is currently an sqlite problem. The code below runs fine except the
>> final step: $fixtures->populate. This is the error it gives-
>>
>> SQL was:
>>  DROP TABLE account
>> DBIx::Class::Fixtures::populate(): DBI Exception: DBD::SQLite::db do
>> failed: no such table: account(1) at dbdimp.c line 271 [for Statement "D=
ROP
>> TABLE account"] at ./fixture-test.pl line 40
>> 1..2
>>
>> There is a table "account" and it's in the MyApp-Test-Schema-1-SQLite.sql
>> as well as the dumped $schema object. My DBIC modules are all current.
>>
>> Any ideas?
>>
>
> That's pretty weird. Could you please send me your
> MyApp-Test-Schema-1-SQLite.sql file? Or better still tar up the whole test
> case if it's self-contained (you might have to send it off list). Hopeful=
ly
> then I'll be able to replicate it.
>

(Ashley sent me the files offlist)

Your call to create_ddl_dir is producing an SQL file with this:

 ...
 DROP TABLE account;
 CREATE TABLE account (
 ...

But that falls over if 'account' doesn't exist. SQLite allows DROP TABLE IF
EXISTS but only after version 3.3. I have knocked up a patch to the
SQL::Translator producer which would allow you to specify an SQLite version,
which, if greater than 3.3 would add the IF EXISTS and all would be well
(patch appended to email and also sent to Jess).

That's no good to you now though, so your best option is to change this:
$schema->create_ddl_dir(['SQLite'],1);

to this:
$schema->create_ddl_dir(['SQLite'],1,undef,undef,{ add_drop_table =3D> 0 });

Which would leave out the DROP TABLE part altogether. If you're just using
your DDL for DBIx::Class::Fixtures then that's fine, since it clears the
existing tables anyway before populating.

Cheers,
Luke.



=3D=3D=3D lib/SQL/Translator/Producer/SQLite.pm
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/SQL/Translator/Producer/SQLite.pm       (revision 1795)
+++ lib/SQL/Translator/Producer/SQLite.pm       (local)
@@ -60,6 +60,8 @@
     my $no_comments    =3D $translator->no_comments;
     my $add_drop_table =3D $translator->add_drop_table;
     my $schema         =3D $translator->schema;
+    my $producer_args  =3D $translator->producer_args;
+    my $sqlite_version  =3D $producer_args->{sqlite_version} || 0;

     debug("PKG: Beginning production\n");

@@ -70,6 +72,7 @@
     my @table_defs =3D ();
     for my $table ( $schema->get_tables ) {
         my @defs =3D create_table($table, { no_comments =3D> $no_comments,
+                                          sqlite_version =3D> $sqlite_vers=
ion,
                                           add_drop_table =3D> $add_drop_ta=
ble,});
         my $create =3D shift @defs;
         $create .=3D ";\n";
@@ -124,6 +127,7 @@
     my $table_name =3D $table->name;
     my $no_comments =3D $options->{no_comments};
     my $add_drop_table =3D $options->{add_drop_table};
+    my $sqlite_version =3D $options->{sqlite_version};

     debug("PKG: Looking at table '$table_name'\n");

@@ -135,8 +139,9 @@
     # Header.
     #
     my $create =3D '';
-    $create .=3D "--\n-- Table: $table_name\n--\n" unless $no_comments;
-    $create .=3D qq[DROP TABLE $table_name;\n] if $add_drop_table;
+    my $exists =3D ($sqlite_version >=3D 3.3) ? ' IF EXISTS' : '';
+    $create .=3D "--\n-- Table: $table_name\n--\n" unless $no_comments;
+    $create .=3D "DROP TABLE$exists $table_name;\n" if $add_drop_table;
     $create .=3D "CREATE ${temp}TABLE $table_name (\n";

     #
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20081008/fe9=
4e10c/attachment.htm