Jess Robinson wrote:
On Wed, 07 Jan 2009 21:06:27 -0000, Jason Gottshall
<[email protected]> 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
[email protected]
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
