http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11390 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #2 from David Cook <dcook@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/...). 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/...) 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.