[Dbix-class] column default values, SQLite and Oracle

[Jason Gottshall]

I'm attempting to set things up so my existing Oracle-based schema can
be deployed to SQLite via $schema->deploy so we can do some localized
testing. One incompatibility that is hitting us hard is that SQLite
doesn't accept dynamic expressions as default values for date-typed
columns, while Oracle does, and we have a number of existing tables that
take advantage of this feature. So the relevant columns' default values
are currently configured in the Result classes with scalar refs
containing the expressions. Of course SQLite chokes when deploy()
attempts to create the corresponding tables. But I have an idea...

a) Patch SQLT::Producer::SQLite so that it ignores scalar ref (i.e. SQL
expression) default values on date columns
b) Patch DBIC::Storage::DBI::SQLite so that insert() looks in the column
metadata for scalar ref default values for date columns and drops them
into the query as literal sql expressions

One additional issue here is that the accepted syntax for date
expressions is different between SQLite and Oracle (and every other
rdbms, for that matter), so we might have to go one step further and
allow the default_value in the column metadata to be a hashref where the
keys are storage types and the values are the corresponding syntax, for
example:

     __PACKAGE__->add_columns(
         some_date => {
             data_type => 'DATE',
             default_value => {
                 Oracle => \q[sysdate + 1],
                 SQLite => \q[date('now', '+1 day')],
                 Pg     => \q[now + interval '1 day'],
                 mysql  => \q[now() + interval 1 day],
             },
         },
     );

Thoughts, anyone?

-- 
Jason Gottshall
jgottshall at capwiz.com