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

[Jason Gottshall]

Jess Robinson wrote:
> 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'

True, though the producer currently checks plain scalars for valid forms 
of those types of things and uses them appropriately. We could probably 
regex the scalar refs and only ignore the ones that contain expressions 
other than the few (3?) like your example that are actually valid in 
SQLite.

>> 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.

Is that "c" meant to replace my "b"? (my "b" was a second necessary 
step, not an alternative to "a".) It does makes sense, tho. I just don't 
know the internals well enough yet to know the right place to put that 
kind of stuff without digging.

>> 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().

I'd love to leave all this to SQL::Translator. The question then 
becomes: what is that "something standard"? It's the lack of a standard 
that's giving me headaches in the first place. And looking at the 
differences among various implementations, we may need a whole separate 
date expression parser for every flavor in order to get this right. My 
flavor-specific hash idea was intentionally avoiding the whole 
convoluted parse-and-reformat issue; this way you really only have to 
worry about figuring out the syntax for the ones you intend to use...

>> Thoughts, anyone?
> Several ;)
Thanks for thinking about this, Jess!

Jason

-- 
Jason Gottshall
jgottshall at capwiz.com