[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

David Cook <dcook at prosentient.com.au> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |dcook at prosentient.com.au

--- Comment #2 from David Cook <dcook at prosentient.com.au> ---
I've been playing around with DBIx::Class::Schema... and looking at
DBIx::Class::Schema::Versioned.

0) We probably want to add "our $VERSION" to Koha::Schema. The
DBIx::Class::Schema::schema_version() method uses this variable when figuring
out the "current version" for the database as represented by the DBIC classes.
While you won't necessarily get any warnings, it can be used in
Koha::Database::create_ddl_dir()
(http://cpansearch.perl.org/src/RIBASUSHI/DBIx-Class-0.082810/lib/DBIx/Class/Storage/DBI.pm).
Plus, I believe DBIx::Class::Schema::Versioned counts on this $VERSION variable
to know the current version as well:

A table called I<dbix_class_schema_versions> is created and maintained by the
module. This is used to determine which version your database is currently at.
Similarly the $VERSION in your DBIC schema class is used to determine the
current DBIC schema version.
(http://cpansearch.perl.org/src/RIBASUSHI/DBIx-Class-0.082810/lib/DBIx/Class/Schema/Versioned.pm)


1) Itemtypes and Statistics both use the "double" data type. It looks like the
Schema::Loader recorded these as "double precision", which is fine if you're
just doing a dump via Koha::Schema->create_ddl_dir($databases,
$current_version, $directory).

However, if you're trying to do a diff via
Koha::Database->create_ddl_dir($databases, $current_version, $directory,
$previous_version), you'll get fatal errors like so:

    ERROR (line 2890): Invalid statement: Was expecting comment, or use, or
                       set, or drop, or create, or alter, or insert, or
                       delimiter, or empty statement
DBIx::Class::Storage::DBI::create_ddl_dir(): translate: Error with parser
'SQL::Translator::Parser::MySQL':  no results at dbic_controller.pl line 6

If we can't generate diffs, then we can't use
DBIx::Class::Schema::Versioned::upgrade().


2) I'm running into a strange issue when I am finally able to run a SQL diff. 

So I tried dumping two different versions of the DBIC schema using
"Koha::Database->create_ddl_dir($databases, $current_version, $directory)". I
then used the CLI tool "sqlt-diff", and it worked perfectly. It only detected
the actual difference between the two.

However, if I tried "Koha::Database->create_ddl_dir($databases,
$current_version, $directory, $previous_version", I got weird results. The
reason was that I wasn't comparing the two dumps. Rather, create_ddl_dir() was
using a previous dump and the current version as it is stored in DBIC. 

(I confirmed this when I emulated create_ddl_dir() but removed the possibility
of using the current schema in favour of a current dump.)

For some reason, SQL::Translator::Producer::MySQL acts quite differently when
using the method SQL::Translator::Producer::MySQL->can('preprocess_schema')
returns true. I haven't dug deep enough to figure out what is causing the
problem... whether it's the SQL::Translator::Parser::MySQL or
SQL::Translator::Parser::DBIx::Class or SQL::Translator::Producer::MySQL or...
I don't know what.

In any case, I get the following set of SQL statements:

BEGIN;

ALTER TABLE accountlines CHANGE COLUMN timestamp timestamp timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE accountoffsets CHANGE COLUMN timestamp timestamp timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE action_logs CHANGE COLUMN timestamp timestamp timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE aqbudgets CHANGE COLUMN timestamp timestamp timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE aqorders CHANGE COLUMN timestamp timestamp timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE aqorders_items CHANGE COLUMN timestamp timestamp timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE aqorders_transfers CHANGE COLUMN timestamp timestamp timestamp NOT
NULL DEFAULT current_timestamp;

ALTER TABLE biblio DROP INDEX biblionumber,
                   CHANGE COLUMN timestamp timestamp timestamp NOT NULL DEFAULT
current_timestamp;

ALTER TABLE marc_subfield_structure CHANGE COLUMN seealso seealso text NULL;

ALTER TABLE message_queue CHANGE COLUMN time_queued time_queued timestamp NOT
NULL DEFAULT current_timestamp;

ALTER TABLE messages CHANGE COLUMN message_date message_date timestamp NOT NULL
DEFAULT current_timestamp;

ALTER TABLE misc_files CHANGE COLUMN date_uploaded date_uploaded timestamp NOT
NULL DEFAULT current_timestamp;

ALTER TABLE patronimage DROP INDEX borrowernumber;

[...there was more but you get the idea]

--

I have a theory about why "marc_subfield_structure" is there. I think it's
because it ACTUALLY has a VARCHAR datatype and a size of 1100 characters in the
DBIC schema. According to MySQL's documentation "Values in VARCHAR columns are
variable-length strings. The length can be specified as a value from 0 to 255
before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions."

If you go to the source for SQL::Translator::Producer::MySQL, you can find the
following lines:

    elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
        unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
            $data_type = 'text';
            @size      = ();
        }
    }

We can see that the data_type has been changed to 'text' from 'varchar', so
it's clear that $mysql_version isn't being passed in (correctly).

--

Unfortunately, I didn't notice anything in SQL::Translator::Producer::MySQL to
signify why all those "timestamp" columns are being changed. 

--

I think I've sort of figured out the dropped indexes as well. The dropped index
for every column happens where the index is for a field that is A) the primary
key, and B) also a foreign key (in the DBIC schema). It looks like "X Module"
automatically creates an index for every foreign key, but it runs into a
problem when the foreign key is also the primary key.

The SQL dump will still create some SQL for it like so:

"INDEX (`borrowernumber`),"

which is in stark contrast to how DBIC creates indexes normally:

"INDEX `patroncards_idx_borrowernumber` (`borrowernumber`),"

I think technically the `patroncards_idx_borrowernumber` name is optional in
MySQL, but I think this is what's causing the strange result in the SQL diff.


3) Since the SQL being dumped from DBIC isn't the same SQL that we currently
have in kohastructure.sql, I think that we'll want to be careful before using
Koha::Database::deploy();

4) I've tried doing SQL diffs between DBIC dumps and kohastructure.sql.
However, it looks like there are characters in kohastructure.sql that are
causing problems for SQL::Translator::Diff. I think it croaked whenver it got
to "CREATE TABLE `borrowers`". I think I was able to do a trace and it thought
it was written as "\nCREATE TABLE `borrowers`", which... I couldn't see in my
text editor... but that will probably be a problem.

5) I'm to do some more poking around, but these are some of the things I've
encountered over the past 24 hours.

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