[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 #8 from David Cook <dcook at prosentient.com.au> ---
So I finally am running some diffs between clean installs from the "web
installer" and the "dbic deploy".

There are a lot of differences... ( DBRev 3.17.00.055 && a couple tweaks to
DBIC files)

1) 'ON UPDATE CURRENT_TIMESTAMP' is missing from many timestamps 

I think this can be provided as an "extra" in a DBIC Result class though.

AFFECTED:
`accountlines`
`action_logs`
`aqbudgets`
`aqorders`
`aqorders_items`
`biblio`
`biblioitems`
`borrower_debarments`
`borrower_modifications`
`course_items`
`course_reserves`
`courses`
`creator_batches`
`currency`
`deletedbiblio`
`deletedbiblioitems`
`deleteditems`
`issues`
`items`
`old_issues`
`old_reserves`
`patroncards`
`reserveconstraints`
`reserves`
`suggestions`
`virtualshelfcontents`
`virtualshelves`

---

2) All int(egers) default to int(11), all tinyint to tinyint(4), all smallint
to smallint(6).

DBIx::Class::Schema::Loader doesn't appear to load the "width" or "range" for
integers. So while kohastructure.sql and the web installer dump say
`notify_level` int(2), a mysql dump of a DBIC deploy says `notify_level`
int(11).

This doesn't seem solvable... except by changing all exciting integers to have
a width of 11 and enforcing width/size limits through code.

http://cpansearch.perl.org/src/ILMARI/SQL-Translator-0.11020/lib/SQL/Translator/Parser/MySQL.pm
sub normalize_field()


AFFECTED:
`accountlines` int(11)
`aqbasketgroups` tinyint(4)
`aqbudgetperiods` tinyint(4) x2
`aqbudgets` int(11)
`aqbudgets_planning` tinyint(4)
`aqcontacts` tinyint(4) x4
`aqorders` tinyint(4)
`auth_subfield_structure` tiny(4) x4
`biblio` tinyint(4)
`biblioitems` int(11)
`borrower_attribute_types` tinyint(4) x6
`borrower_message_preferences` tinyint(4)
`borrower_modifications` tinyint(4) x2
`borrower_sync` tinyint(4)
`borrowers` tinyint(4) x2
`branch_borrower_circ_rules` int(11)
`branch_item_rules` tinyint(4)
`branch_transfer_limits` int(11)
`branchcategories` tinyint(4)
`categories` tinyint(4) x5
`columns_settings` int(11) x2
`creator_batches` int(11)
`creator_images` int(11)
`creator_layouts` int(11) x5
`creator_templates` int(11) x4
`currency` tinyint(4)
`default_borrower_circ_rules` int(11)
`default_branch_circ_rules` int(11), tinyint(4)
`default_branch_item_rules` tinyint(4)
`default_circ_rules` int(11)
`deletedbiblio` tinyint(4)
`deletedbiblioitems` int(11)
`deletedborrowers` tinyint(4) x2
`deleteditems` tinyint(4) x6
`import_biblios` tinyint(4)
`issues` tinyint(4), int(11)
`issuingrules` tinyint(4) x2, int(11) x4
`items` tinyint(4) x6
`letter` tinyint(4)
`marc_modification_template_actions` int(11), tinyint(4)
`marc_subfield_structure` tinyint(4) x3, int(11)
`matchpoint_components` int(11) x2
`message_attributes` tinyint(4)
`message_transports` tinyint(4)
`notifys` int(11)
`old_issues` int(11), tinyint(4)
`old_reserves` tinyint(4) x2
`overduerules`int(11) x4
`overduerules_transport_types` int(11)
`printers_profile` int(11) x2
`ratings` tinyint(4)
`reserves` int(4) x2
`saved_sql` tinyint(4)
`special_holidays` smallint(6)
`subscription` tinyint(4) x3, int(11)
`suggestions` int(11), smallint(6)
`tags_all` int(11)
`tags_approval` int(11) x2
`tags_index` int(11)
`transport_cost` tinyint(4)
`virtualshelves` tinyint(4) x3

---

3) Indexes are missing

For `accountlines`, the indexes for `borrowernumber` and `itemnumber` appear,
but only because they are also foreign keys. There is no index for `timestamp`
when using DBIC, but there is when using the Web Installer.

AFFECTED:
`accountlines`
`action_logs`
`alert`
`aqorders`
`auth_header`
`auth_subfield_structure`
`authorised_values`
`biblio`
`biblioitems`
`borrower_attribute_types`
`borrower_modifications`
`borrowers`
`branchcategories`
`categories`
`class_sort_rules`
`class_sources`
`creator_templates`
`deletedbiblio`
`deletedbiblioitems`
`deletedborrowers`
`deleteditems`
`import_auths`
`import_batches`
`import_biblios`
`import_items`
`import_records`
`issues`
`issuingrules`
`item_circulation_alert_preferences` (although the web installer index looks
really weird anyway)
`items`
`itemtypes`
`language_descriptions`
`language_rfc4646_to_iso639`
`language_script_bidi`
`language_script_mapping`
`language_subtag_registry`
`linktracker`
`marc_matchers`
`marc_subfield_structure`
`misc_files`
`old_issues`
`old_reserves`
`reports_dictionary`
`reserves`
`saved_sql`
`search_history`
`statistics`
`subscriptionhistory`
`suggestions`
`zebraqueue`


---

4) Extra indexes appear in DBIC

Because foreign keys are always indexed by default, there are sometimes extra
indexes when using DBIC.

AFFECTED:
`aqbasketusers`
`aqbudgetborrowers`
`aqorders_transfers`
`auth_tag_structure`
`borrower_message_transport_preferences`
`branch_borrower_circ_rules`
`branch_item_rules`
`course_instructors`
`course_items`
`default_borrower_circ_rules`
`default_branch_circ_rules`
`default_branch_item_rules`
`hold_fill_targets`
`oai_sets_biblios`
`patronimage`
`ratings`
`serialitems`
`subscriptionroutinglist`
`tags_index`
`transport_cost`



---

5) Index names && foreign key names are different

_Indices_
Web installer: `acctsborridx`
DBIC: `accountlines_idx_borrowernumber`

_Foreign keys_
Web Installer: `accountlines_ibfk_1`
DBIC: `accoutlines_fk_borrowernumber`

Honestly, I prefer the DBIC style, but I think this will cause problems.

Not sure if this is solvable...

AFFECTED:
`accountlines`
`aqbasket`
`aqbasketgroups`
`aqbasketusers`
`aqbooksellers`
`aqbudgetborrowers`
`aqbudgets_planning`
`aqcontacts`
`aqcontract`
`aqinvoices`
`aqorders`
`aqorders_items`
`aqorders_transfers`
`auth_tag_structure`
`authorised_values_branches`
`biblioimages`
`biblioitems`
`borrower_attribute_types_branches`
`borrower_attributes`
`borrower_debarments`
`borrower_files`
`borrower_message_preferences`
`borrower_message_transport_preferences`
`borrower_sync`
`borrowers`
`branch_borrower_circ_rules`
`branch_item_rules`
`branchrelations`
`branchtransfers`
`categories_branches`
`class_sources`
`collections`
`course_instructors`
`course_items`
`course_reserves`
`creator_batches`
`default_borrower_circ_rules`
`default_branch_circ_rules`
`default_branch_item_rules`
`hold_fill_targets`
`import_auths`
`import_biblios`
`import_items`
`import_record_matches`
`import_records`
`issues`
`letter`
`marc_modification_template_actions`
`matchchecks`
`matcher_matchpoints`
`matchpoint_component_norms`
`matchpoint_components`
`matchpoints`
`message_transports`
`oai_sets_biblios`
`oai_sets_descriptions`
`oai_sets_mappings`
`old_issues`
`old_reserves`
`opac_news`
`overduerules_transport_types`
`patron_list_patrons`
`patron_lists`
`patroncards`
`patronimage`
`permissions`
`ratings`
`reserves`
`reviews`
`serialitems`
`subscription`
`subscriptionroutinglist`
`tags_all`
`tags_approval`
`tags_index`
`transport_cost`
`user_permissions`
`virtualshelfcontents`
`virtualshelfshares`
`virtualshelves`


---

6) DBIC doesn't include COMMENTs

Honestly, I don't see this mattering at all...

AFFECTED:
`collections_tracking`
`export_format`
`search_history`


---

7) (UTF8) CHARACTER SET and COLLATE do not appear in DBIC

AFFECTED:
`creator_layouts`
`marc_subfield_structure`

---

8) DBIC has extra foreign keys

AFFECTED:
`issues`
`items`
`biblio`

---

9) DBIC has extra tables that aren't in kohastructure.sql...

AFFECTED:

_DBIC_
`closure`
`closure_rrule`

---

10) DBIC wasn't able to create tables

DBD::mysql::db do failed: Can't create table 'kohadev38.items_search_fields'
(errno: 150) at /usr/lib/perl5/site_perl/5.16.2/DBIx/Class/Storage/DBI.pm line
3053.
DBD::mysql::db do failed: Incorrect table definition; there can be only one
auto column and it must be defined as a key at
/usr/lib/perl5/site_perl/5.16.2/DBIx/Class/Storage/DBI.pm line 3053

AFFECTED:
`items_search_fields`
`message_queue`

---

11) Default values...

Missing a Default statement in DBIC...

AFFECTED:
`itemtypes`

---

12) Table type incorrect in DBIC

AFFECTED:
`pending_offline_operations` (should be MyISAM not InnoDB)

------

**) Double Precision

Of course, I had already tampered with the cases of 'double' being written as
'double precision' in DBIC... since it wouldn't even parse 'double precision',
but I'll mention the affected tables as we'll need to generate/write these
correctly the first time...

AFFECTED:
`itemtypes`
`statistics`

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