[Dbix-class] Versioned Cluelessness

[Christopher Laco]

Christopher Laco wrote:
> I thought this would be easy. Apparently I'm stupid.
> 
> I have a schema, which has a $VERSION. I've loaded the Versioned 
> component and created two scripts. The first script simply calls 
> $schema->create_ddl_dir with the current/prev versions and directory. 
> The second script simply calls $schema->upgrade
> 
> I've had no issues creating the versioned sql files themselves; both for 
> each version (Schema-1.sql, Schema-2.sql) and for the diffs between 
> versions (Schema-1-2.sql). Those files contain nothing extraordinary. I 
> just added column.
> 
> What has me stumnped is the initial deploy. If I simply do:
> 
>   $schema->connect('dbi:SQLite:database.db')->upgrade;
> 
> I get warnings about the db not being versioned, and then the versioned 
> table is created. However, no other schema tables are created. I thought 
> that update() by itself ran all statements (run_upgrade sets ||= qr//;)
> 
> I know Versioned is finding the right upgrade_directory because if I 
> upgrade to a non-existing .sql file version, if bitches about not 
> finding the file (but it does find 1-2*.sql from above).
> 
> Yes, I'm using SQLite, but the upgrade does a simple alter. No indexes 
> or any other odd magic are involved it would seem.
> 
> What am I missing?
> 
> -=Chris

It appears the you really need to either deploy initially, or start with 
  a version 0 first.

When I get past that, running an $schema->upgrade() from version 1 to 
version 2 croaks:

> claco at mbp ~/mvc-marathon/catalyst/BurningPlate $ script/*upgrade.pl dbi:SQLite:burning_plate.db
> Versions out of sync. This is 2, your database contains version 1, please call upgrade on your Schema.
> Reg: (?i-xsm:create)
> Statements: CREATE TEMPORARY TABLE restaurants_temp_alter (  id INTEGER PRIMARY KEY NOT NULL,  name VARCHAR(100) NOT NULL,  foo VARCHAR(5))
> CREATE TABLE restaurants (  id INTEGER PRIMARY KEY NOT NULL,  name VARCHAR(100) NOT NULL,  foo VARCHAR(5))
> DBIx::Class::Storage::DBI::__ANON__(): DBI Exception: DBD::SQLite::db do failed: table restaurants already exists(1) at dbdimp.c line 271 [for Statement "CREATE TABLE restaurants (  id INTEGER PRIMARY KEY NOT NULL,  name VARCHAR(100) NOT NULL,  foo VARCHAR(5))"] at /Users/claco/mvc-marathon/catalyst/BurningPlate/script/../lib/BurningPlate/Schema.pm line 31


upgrade() doesn't appear to run the statements in the same order as they 
appear in the file:

> CREATE TEMPORARY TABLE restaurants_temp_alter (
>   id INTEGER PRIMARY KEY NOT NULL,
>   name VARCHAR(100) NOT NULL,
>   foo VARCHAR(5)
> );
> INSERT INTO restaurants_temp_alter SELECT id, name, foo FROM restaurants;
> DROP TABLE restaurants;
> CREATE TABLE restaurants (
>   id INTEGER PRIMARY KEY NOT NULL,
>   name VARCHAR(100) NOT NULL,
>   foo VARCHAR(5)
> );
> INSERT INTO restaurants SELECT id, name, foo FROM restaurants_temp_alter;
> DROP TABLE restaurants_temp_alter;



despite the advice of:

> NOTE: Since SQL::Translator 0.09000 it is better to just run all statmets in the order given, since the SQL produced is of better quality.