[Koha-bugs] [Bug 11390] DBIx::Class schema deployment script

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11390

--- Comment #6 from David Cook <dcook at prosentient.com.au> ---
Muahaha. I've solved the "timestamp" issue.

I ran the following SQL:

SELECT * FROM information_schema.`COLUMNS` where TABLE_SCHEMA = 'kohadev' and
DATA_TYPE = 'timestamp';

I got back 43 columns, but I could only count 41 in the SQL diff. So I isolated
the two that didn't appear...

1) BorrowerDebarment->updated
2) Accountline->timestamp

The reason? BorrowerDebarment->updated didn't have a default_value. I had a
feeling yesterday that it was going to be a problem with the default_value of
\"current_timestamp". 

Well, I looked at Accountline->timestamp, and it did have a default_value... of
"\CURRENT_TIMESTAMP".

Sure enough, when I changed the default_value on a few other Result classes,
they no longer showed up as spurious diffs in the SQL diff! 

I haven't isolated which module is responsible for this case sensitivity issue
(I would say bug), but the workaround for us is just to use uppercase SQL in
the default_value.

--

I've investigated DBIx::Class a bit and SQL::Abstract, and while SQL::Abstract
does do some things with case sensitivity... I don't think it's an issue in
this case.

I think the DBIC schema isn't having anything done to its case. This is
reinforced by the fact that the spurious diffs use lowercase
"current_timestamp".

I'm thinking... when it's parsing the past DBIC dump (rather than the current
DBIC schema) that it's uppercasing "current_timestamp" to "CURRENT_TIMESTAMP".

Of course, the time is now 5:17pm on a Friday, so I'm not going to investigate
this any further until next week.

OK, I lied. Check out
http://cpansearch.perl.org/src/ILMARI/SQL-Translator-0.11020/lib/SQL/Translator/Parser/MySQL.pm
and search the page a bit for all instances of "current_timestamp".

As I suspected, the MySQL Parser is uppercasing current_timestamp and now() to
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP when used as a default_value.

Ergo... the only thing we can do is uppercase our literal SQL...

It appears that SQL::Translator::Diff takes a "case_insensitive" option. That
it passes it to the SQL::Translator::Schema::* objects...

SQL::Translator::Schema::Table->get_field($field, "case_insensitive"); However,
that appears to only treat field names as case_insensitive... which probably
makes sense.

So yeah... guess we'll be uppercasing our default_value if it's literal SQL, if
we want to create SQL diffs using DBIx::Class::Schema.

-- 
You are receiving this mail because:
You are watching all bug changes.