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

[Jess Robinson]

On Wed, 07 Jan 2009 21:06:27 -0000, Jason Gottshall  
<jgottshall at capwiz.com> wrote:

> 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

No, thanks, since those actually have a meaning, eg \'CURRENT_TIMESTAMP'

> 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

c) create a component that is only loaded when $schema->storage->sqlt_type  
eq 'SQLite' which overrides insert and does.. whatever.

> 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],
>              },
>          },
>      );

It'd be more sensible to let SQL::Translator do this bit, since that's  
it's purpose. So you write whichever of those as the default value, and  
SQLT translates into something standard on the SQLT::Schema::Field, and  
back into whichever producer you use on deploy().

> Thoughts, anyone?
Several ;)

Jess



-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/