DB schema bugs

= Koha DB schema inconsistencies =
The following page tries to reference all strange things in the Koha database. They'll have to be fixed in a future version, or they are strange but assumed and are in Koha for a good reason. 
Short reading = the topics that are debatable are in italic. Unless we (joubu & paul_p) made a mistake when investigating all other lines are wrong database design and should be fixed. If you have time, you're welcomed reading everything and adding your thoughts. We may have missed some things, so you're also welcomed to add whatever you think it's worth adding 
== Table names ==
*Table names are sometimes singular (biblio), sometimes plural (items). Rough numbers: 85 are plural, 50 are singular 
*Table name sometimes include a _ (marc_tag_structure), sometime they don't (biblioitems). 70 have a _ and 64 dont (some of them don't need one anyway, like items or borrowers) 
*A few tables are prefixed with aq (acquisition), others aren't 
*opac_news table is not only for opac news, but also for staff interface. Should have another name 
*should we call 'users of the libraries' members, patrons, borrowers or users ? Choose one term & stick with it. At the moment we use different terms in different places: borrowers as table name C4/Members.pm as package, patron on interface, user for user_permission 
*z3950servers table should be rename in "available_search_servers"
*Some tables don't have any primary key (maybe for some of them it's OK):
accountlines			=> Done/Stéphane Delaune bugz:7671/Need Signoff
* No PK 
accountoffsets			=> 
* No PK
aqorderdelivery			=>
* No PK
borrower_attributes		=>
* No PK
branches			=>
* No PK. (possible = branchcode)
branchtransfers
* No PK
browser				=>
* No PK
deletedborrowers		=>
* No PK
fundmapping			=> does not exist in kohastructure
* No PK
	
import_biblios			=>
* No PK
import_record_matches		=>
* No PK
language_script_bidi		=>
* No PK
language_script_mapping		=>
* No PK
matcher_matchpoints		=>
* No PK
matchpoint_component_norms	=>
* No PK
message_queue			=>
* No PK. (possible = message_id)
notifys				=>
* No PK. (we have a field named notify_id but it's a normal field, no unique and no autoincrement)
nozebra				=> this table is deprecated, it's OK
* No PK
old_issues			=>
* No PK
old_reserves			=>
* No PK
reserveconstraints		=>
* No PK
reserves			=>
* No PK
search_history			=>
* No PK
serialitems			=>
* No PK. (idea: delete this table and create either in items.serialid or in serial.itemnumber)
sessions			=> this table has a UNIQUE KEY `id`, we should move to PRIMARY KEY
* No PK (possible = id)
statistics			=>
* No PK
stopwords			=>
* No PK
tmp_holdsqueue			=>
* No PK
user_permissions		=>
* No PK
virtualshelfcontents		=> 
* No PK
 
*the table need_merge_authorities could be removed and replaced by a column in authority table 
*the tables biblio and biblioitems should be merged (question : is it a good idea, they could be usefull for FRBR in the future...) 
*the word "category" is used in many places (shelveds, borrower_attribute_type, branchcategories,...) and we have a table categories related to borrower category. We should rename this table to borrower_category 
*the tables default_borrower_circ_rules, default_branch_circ_rules, default_branch_item_rules, default_circ_rules are not always related to what their name says 
**default_borrower_circ_rule => related to borrower category code (should be named default_borrower_category_circ_rule ? there is a 1-1 relation with categories, so this table should be completely removed in favor of a new column in categories) 
**default_branch_circ_rules => related to branch circ rules. There is a 1-1 relation with branches, so this table should be completly removed in favor of 2 new column in branches) 
**default_branch_item_rules => related to itemtype rules. At the very least, this table should be renamed to default_item_circ_rules. In fact, the table should be completly removed in favor of a new column in branches table 
**default_circ_rules => this table is completly useless, as it just can contain 1 line (the primary key is a column that can only have "singleton" value !). Should be removed in favor of a systempreference 
*the tables language_ are constants, they are just filled at the setup of Koha. We should remove them in favor of a YAML or any kind of static file. 
*message_transport_types contains only 1 column, that is a FK for message_transports. We should remove it in favor of SELECT DISTINCT message_transport_type in message_transport (that's what we already do with authorised_values.category) 
*the table patroncard seem to be unused and could be removed 
*the table subscriptionhistory should be merged with subscription, as there is a 1-1 relation between them
Strange things that are are not bugs or inconsistencies: 
*Some tables have a old_ prefix, some have a deleted_ prefix. The old_ prefix means the information is kept here and used in Koha. For example, issues once the item has been returned are moved to old_issues table. The deleted_ prefix means we store here a record that has been deleted, and is not used anywhere by Koha.
== Column names ==
*primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => We have decided to use column_id as primary key identifier 
*Column names sometimes have a _ (biblioitems.cn_source), sometimes they don't (items.notforloan) 
*Column names are usually lowercase, some have a capital (exhaustive list = deletedborrowers.B_*, borrowers.B_* fields, old_reserves.lowestPriority, reserves.lowestPriority, suggestions.STATUS) => we should switch the Uppercase letters to lowercase 
*Some column names are prefixed with almost the table name (aqbudgetperiod.budget_period_*, aqcontract.contract*, aqbudgets.budget*, messages.message_*) => we should remove those prefixes
== Column to change ==
*aqbudgetperiods.sort1_authcat (and sort2_authcat) is unused & should be removed 
*aqorders.sort1_authcat (and sort2_authcat) is unused and should be removed
(aqbudgets.sort1_authcat is OK. It contains the authorised_values category to display to the user, that is used to fill aqorders.sort1) 
*borrowers.sex is a varchar(1) and should be ENUM ('M','F') NULL DEFAULT NULL 
*most (all ?) tinyint(1) fields are used as boolean and could be switched to boolean type 
*collections.colBranchcode is varchar(4) => should be varchar(10) with a foreign key on branches.branchcode 
*debatable= letters table has a PK based on 2 fields. It could be switched to a id (for example: overduerules contains letter1, letter2, and letter3, that is only a part of the primary key. We know that overduerules are related to 'CIRC' but it's not obvious in the database => poor design [the author of this comment is also the author of this design ;-) ] ) 
*reviews.borrowernumber and reviews.biblionumber should be NOT NULL (instead of DEFAULT NULL atm) 
*saved_reports.report_id => isn't this the saved_sql.id ? if yes, then we should: 
**rename the column to saved_reports.saved_sql_id 
**add a FK (ON DELETE CASCADE) 
*serial.biblionumber must be changed to INT(11) (instead of varchar(100) at the moment) 
*serial.subscriptionid must be changed to INT(11) (instead of varchar(100) at the moment) 
*search_history.userid should be renamed borrowernumber 
*auth_subfield_structure has a frameworkcode field that is unused and can be removed 
*matchpoint_component_norms.sequence and matchpoint_components.sequence should be renamed to avoid name collisions 
*opac_news.new should be renamed to avoid name collisions 
*patronimage has a constraint against a nullable field (borrower.cardnumber) - surely this should be against borrowernumber (and/or cardnumber in patronimage should not allow null)
*z3950servers.db sould be renamed dbname
*z3950servers.name sould be renamed label
== Missing foreign keys ==
*accountlines.accountno should be a foreign key for accountoffsets.accountno (note PP: to be confirmed, i'm not sure)
*borrowers.borrowernumber should be a foreign key for 
**alert.borrowernumber (with ON DELETE CASCADE) 
**statistics.borrowernumber (with ON DELETE SET NULL) 
**messages.borrowernumber (with ON DELETE SET NULL) 
**reserveconstraints.borrowernumber (with ON DELETE CASCADE) 
**reviews.borrowernumber (with ON DELETE CASCADE) 
**saved_sql.borrowernumber (with ON DELETE SET NULL) 
**search_history.borrowernumber (with ON DELETE CASCADE) -the borrowernumber field is called userid atm, see below- 
**suggestions.suggestedby, suggestion.managedby, acceptedby, rejectedby (with ON DELETE SET NULL)
*aqbudgetperiods.budget_period_id should be a FK for: 
**aqbudgets_planning.budget_period_id (ON DELETE CASCADE)
*biblio.biblionumber should be a FK for: 
**reserveconstraints.biblionumber (with ON DELETE CASCADE) 
**reviews.biblionumber (with ON DELETE CASCADE) 
**serial.biblionumber (with ON DELETE CASCADE) 
**suggestions.biblionumber (with ON DELETE SET NULL) --note the biblionumber is NULL at the beginning of the suggestion process, so we must check the FK will work well with biblionumber=NULL
*biblio_framework.frameworkcode should be a FK for: 
**biblio.frameworkcode (with ON DELETE SET NULL) 
**marc_tag_structure.frameworkcode (with ON DELETE CASCADE) 
**marc_subfield_structure.frameworkcode (with ON DELETE CASCADE) 
**fieldmapping.frameworkcode (with ON DELETE CASCADE)
*branches.branchcode should be a FK for 
**branch_transfer_limits.toBranch and fromBranch (ON DELETE CASCADE) 
**deletedborrowers.branchcode (ON DELETE SET NULL) 
**import_batches.branchcode (ON DELETE CASCADE) 
**import_records.branchcode (ON DELETE CASCADE) 
**import_items.branchcode (ON DELETE CASCADE) 
**issues.branchcode (ON DELETE CASCADE) 
**issuingrules.branchcode (ON DELETE CASCADE) 
**old_issues.branchcode (ON DELETE SET NULL) 
**old_reserves.branchcode (ON DELETE CASCADE) 
**overduerules.branchcode (ON DELETE CASCADE) 
**repeatable_holidays.branchcode (ON DELETE CASCADE 
**special_holidays.branchcode (ON DELETE CASCADE) 
**item_circulation_alert_preferences.branchcode (ON DELETE CASCADE) 
**messages.branchcode (ON DELETE SET NULL)
**items.homebranch (ON DELETE CASCADE, ON UPDATE CASCADE) . is this correct?
**items.holdingbranch (ON DELETE CASCADE, ON UPDATE CASCADE) . is this correct?
*categories.categorycode should be a FK for 
**item_circulation_alert_preferences.categorycode (ON DELETE CASCADE) 
**overduerules.categorycode (ON DELETE CASCADE)
*itemtypes.itemtype should be a FK for 
**items.itype (ON DELETE SET NULL)
*subscription.subscriptionid should be a FK for 
**serial.subscriptionid
*sessions.sessionid should be a FK for 
**search_history.sessionid (ON DELETE SET NULL) -- it's used to distinguish current session & previous search history
Strange things thare are not bugs or inconsistencies: 
*could/should cities table be a foreign key of borrowers.city ? no, because it's just a pre-filled list of common cities for this library. The borrower city can also be entered manually. 
*suggestions.branchcode and subscription.branchcode don't have a FK to branches table because they can (or are) be empty sometimes, breaking the FK. Question: would it be possible/working to have this field NULL and a foreign key placed ? to be tested... 
*aqorders.branchcode should have a FK on branches if the answer to the previous question is positive
== No longer used tables and table columns ==
Probably unused tables: 
*ethnicity 
*notifys 
*printers
Probably unused columns: 
*borrowers.ethnotes / deletedborrowers.ethnotes 
*borrowers.ethnicity / deletedborrowers.ethnicity
 
== resume from kohastructure.sql ==
Table names 	First primary key 	Second primary key 	Third primary key	possible existing unique key
accountlines 	accountlinesid 	 	 	
accountoffsets 	 	 	 	
action_logs 	action_id 	 	 	
alert 	alertid 	 	 	
aqbasket 	basketno 	 	 	
aqbasketgroups 	id 	 	 	
aqbooksellers 	id 	 	 	
aqbudgetperiods 	budget_period_id 	 	 	
aqbudgets 	budget_id 	 	 	
aqbudgets_planning 	plan_id 	 	 	
aqcontract 	contractnumber 	 	 	
aqorderdelivery 	 	 	 	
aqorders 	ordernumber 	 	 	
aqorders_items 	itemnumber 	 	 	
auth_header 	authid 	 	 	
auth_subfield_structure 	authtypecode 	tagfield 	tagsubfield 	
auth_tag_structure 	authtypecode 	tagfield 	 	
auth_types 	authtypecode 	 	 	
authorised_values 	id 	 	 	
biblio 	biblionumber 	 	 	
biblio_framework 	frameworkcode 	 	 	
bibliocoverimage 	imagenumber 	 	 	
biblioitems 	biblioitemnumber 	 	 	
borrower_attribute_types 	code 	 	 	
borrower_attributes 	 	 	 	
borrower_message_preferences 	borrower_message_preference_id	 	 	
borrower_message_transport_preferences	borrower_message_preference_id	message_transport_type	 	
borrowers 	borrowernumber 	 	 	
branch_borrower_circ_rules 	categorycode 	branchcode 	 	
branch_item_rules 	itemtype 	branchcode 	 	
branch_transfer_limits 	limitId 	 	 	
branchcategories 	categorycode 	 	 	
branches 	 	 	 	branchcode
branchrelations 	branchcode 	categorycode 	 	
branchtransfers 	 	 	 	
browser 	 	 	 	
categories 	categorycode 	 	 	
cities 	cityid 	 	 	
class_sort_rules 	class_sort_rule 	 	 	
class_sources 	cn_source 	 	 	
collections 	colId 	 	 	
collections_tracking 	ctId 	 	 	
creator_batches 	label_id 	 	 	
creator_images 	image_id 	 	 	
creator_layouts 	layout_id 	 	 	
creator_templates 	template_id 	 	 	
currency 	currency 	 	 	
default_borrower_circ_rules 	categorycode 	 	 	
default_branch_circ_rules 	branchcode 	 	 	
default_branch_item_rules 	itemtype 	 	 	
default_circ_rules 	singleton 	 	 	
deletedbiblio 	biblionumber 	 	 	
deletedbiblioitems 	biblioitemnumber 	 	 	
deletedborrowers 	 	 	 	
deleteditems 	itemnumber 	 	 	
ethnicity 	code 	 	 	
export_format 	export_format_id 	 	 	
fieldmapping 	id 	 	 	
hold_fill_targets 	itemnumber 	 	 	
import_batches 	import_batch_id 	 	 	
import_biblios 	 	 	 	
import_items 	import_items_id 	 	 	
import_record_matches 	 	 	 	
import_records 	import_record_id 	 	 	
issues 	 	 	 	
issuingrules 	branchcode 	categorycode 	itemtype 	
item_circulation_alert_preferences 	id 	 	 	
items 	itemnumber 	 	 	
itemtypes 	itemtype 	 	 	
language_descriptions 	id 	 	 	
language_rfc4646_to_iso639 	id 	 	 	
language_script_bidi 	 	 	 	
language_script_mapping 	 	 	 	
language_subtag_registry 	id 	 	 	
letter 	module 	code 	 	
marc_matchers 	matcher_id 	 	 	
marc_subfield_structure 	frameworkcode 	tagfield 	tagsubfield 	
marc_tag_structure 	frameworkcode 	tagfield 	 	
matchchecks 	matchcheck_id 	 	 	
matcher_matchpoints 	 	 	 	
matchpoint_component_norms 	 	 	 	
matchpoint_components 	matchpoint_component_id 	 	 	
matchpoints 	matchpoint_id 	 	 	
message_attributes 	message_attribute_id 	 	 	
message_queue 	 	 	 	message_id
message_transport_types 	message_transport_type 	 	 	
message_transports 	message_attribute_id 	message_transport_type	is_digest 	
messages 	message_id 	 	 	
need_merge_authorities 	id 	 	 	
notifys 	 	 	 	
nozebra 	 	 	 	
old_issues 	 	 	 	
old_reserves 	 	 	 	
opac_news 	idnew 	 	 	
overduerules 	branchcode 	categorycode 	 	
patroncards 	cardid 	 	 	
patronimage 	cardnumber 	 	 	
pending_offline_operations 	operationid 	 	 	
permissions 	module_bit 	code 	 	
printers 	printername 	 	 	
printers_profile 	profile_id 	 	 	
repeatable_holidays 	id 	 	 	
reports_dictionary 	id 	 	 	
reserveconstraints 	 	 	 	
reserves 	 	 	 	
reviews 	reviewid 	 	 	
roadtype 	roadtypeid 	 	 	
saved_reports 	id 	 	 	
saved_sql 	id 	 	 	
search_history 	 	 	 	
serial 	serialid 	 	 	
serialitems 	 	 	 	
services_throttle 	service_type 	 	 	
sessions 	 	 	 	id
special_holidays 	id 	 	 	
statistics 	 	 	 	
stopwords 	 	 	 	
subscription 	subscriptionid 	 	 	
subscriptionhistory 	subscriptionid 	 	 	
subscriptionroutinglist 	routingid 	 	 	
suggestions 	suggestionid 	 	 	
systempreferences 	variable 	 	 	
tags 	entry 	 	 	
tags_all 	tag_id 	 	 	
tags_approval 	term 	 	 	
tags_index 	term 	biblionumber 	 	
tmp_holdsqueue 	 	 	 	
user_permissions 	 	 	 	
userflags 	bit 	 	 	
virtualshelfcontents 	 	 	 	
virtualshelves 	shelfnumber 	 	 	
z3950servers 	id 	 	 	
zebraqueue 	id 	 	 	
=Koha DBIx::Class Problems=
For more information, see http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11390
Problems below current as of DBRev 3.17.00.060 (commit ec6a50b2fe502f14738aef82dc30564a335a224b)
==Problems with DBIx::Class==
===Solvable issues===
*SOLUTION AVAILABLE: Koha::Schema should have a version number
#This variable is used by DBIx::Class::Schema::schema_version() and DBIx::Class::Schema::Versioned for determining the current schema version
#Add "our $version" to Koha::Schema
*SOLUTION AVAILABLE: DBIC currrently contains extra foreign keys
#For the following tables:`issues`, `items`, `biblio`
#This doesn't really matter... so long as we remember to add these foreign keys when updating existing schemas...(except biblio.biblionumber... that causes problems with SQL diffs - see below)
===Unsolved issues===
*DBIC currently contains extra tables
#For the following tables:`closure`,`closure_rrule`
#dcook is going to look into how we got extra tables in DBIC... as this doesn't seem like a good thing
==Problems with DBIx::Class::Schema->deploy()==
===Solvable issues===
*SOLUTION AVAILABLE: Index names and foreign key constraint names aren't loaded by DBIx::Class::Schema::Loader and thus they're different when using DBIx::Class::Schema->deploy().
#This isn't necessarily a problem so long as we remember to update existing installs ahead of time
*SOLUTION AVAILABLE: DBIx::Class::Schema automatically adds indexes for all foreign keys when deploying() (ie making "extra indexes")
#This isn't necessarily a problem... so long as we also remember to set up these indexes for existing databases when we transition to just using DBIC
*SOLUTION AVAILABLE: (UTF8) CHARACTER SET and COLLATE do not appear in the deployed SQL
#I'm not sure of the ramifications of this but it affects the following tables: `creator_layouts`, `marc_subfield_structure`
#We can use DBIx::Class::Schema::Loader::make_schema_at() and "custom_col_info" to add these...
===Unsolved issues===
*PARTIAL SOLUTION AVAILABLE: DBIC cannot currently create `items_search_fields` and `message_queue` because of key issues due to DBIC::Schema::Loader
#Change `message_queue.message_id` to a primary key. (solved `message_queue` problem)
#dcook doesn't see a solution for `items_search_fields` yet though... we'd need to index `authorised_values`.`category` and there's no way to do that via the Loader...
*Table type incorrectly set to InnoDB when it should actually be MyISAM
#For the following tables: `pending_offline_operations`
#We might be able to manually add the source_info to the Result class...but dcook doesn't see a way of doing that via the Loader...
##http://search.cpan.org/~ribasushi/DBIx-Class-0.082810/lib/DBIx/Class/ResultSource.pm#source_info
#SQL::Translator::Producer::MYSQL can use MyISAM instead of InnoDB if an extra attribute is added when producing... but dcook doesn't see a way of currently doing that via the Loader...
##http://search.cpan.org/~ilmari/SQL-Translator-0.11020/lib/SQL/Translator/Producer/MySQL.pm#Extra_attributes.
==Problems with DBIx::Class::Schema::Loader==
===Solvable issues===
*SOLUTION/WORKAROUND AVAILABLE: `itemtypes` and `statistics` both include columns with the "double/double precision" data type, which cause problems with SQL diffs. (see later post...)
#DBIx::Class::Schema::Loader::DBI::mysql::_columns_info_for() translates "double" into "double precision"...
#See SQL::Translator::* issues for the solution/workaround to this one...
*SOLUTION/WORKAROUND AVAILABLE: Default values of "CURRENT_TIMESTAMP" are being recorded as lowercase "current_timestamp" which causes spurious entries in SQL diffs. (see later post...)
#DBIx::Class::Schema::Loader::DBI::mysql::_extra_column_info translates `COLUMN_DEFAULT` "CURRENT_TIME" to lowercase "current_time"...
#See SQL::Translator::* issues for the solution/workaround to this one...
*SOLUTION/WORKAROUND AVAILABLE: 'ON UPDATE CURRENT_TIMESTAMP' is missing from many timestamps
#Unfortunately, DBIx::Class::Schema::Loader::DBI::mysql doesn't appear to have a mechanism to detect and add 'on update CURRENT_TIMESTAMP' (which is stored in `information_schema`.`COLUMNS`.`EXTRA`).
##Currently not sure if the info about the `EXTRA` is available at all via the DBI handler... if it is, perhaps a patch could be sent upstream to fix this MySQL loader issue...
#Fortunately, if a column has an "extra" attribute hashref with an 'on update' key and \"CURRENT_TIMESTAMP\" value, it will add it during a DBIC::Schema::deploy(). This is thanks to SQL::Translator::Producer::MySQL::create_field(), which also looks at extras for 'character set' and 'collate'.
#Fortunately, DBIx::Class::Schema::Loader::make_schema_at() takes an option of "custom_column_info", which is a "Hook for adding extra attributes to the column_info for a column", so we can add this "extra" selectively. While this is finicky, it means that we can generate more accurate DBIC classes in the short-term.
Examples:
 make_schema_at(
 "Koha::Schema",
 { debug => 1, dump_directory => $path, preserve_case => 1,
 custom_column_info => sub {
 my ($table, $column_name, $column_info) = @_;
 if ($table eq 'accountlines' && $column_name eq 'timestamp'){
 return { extra => { "on update" => \"CURRENT_TIMESTAMP" } };
 }
 },
 },
 ["DBI:$db_driver:dbname=$db_name;host=$db_host;port=$db_port",$db_user, $db_passwd ]
 );
 "timestamp",
 {
 data_type => "timestamp",
 datetime_undef_if_invalid => 1,
 default_value => \"current_timestamp",
 is_nullable => 0,
 extra => { 
 "on update" => \"CURRENT_TIMESTAMP",
 },
 },
STILL NEED TO INVESTIGATE HOW THIS IMPACTS SQL DIFFs, especially in regards to case! (N.B. regardless of the case in the DBIC class, it winds up uppercase in MySQL)
===Unsolved issues===
*The width/range/size of int(eger), tinyint, and smallint is not recorded by DBIC
#This means that they all default to int(11), tinyint(4), and smallint(6) when the schema is installed via deploy()
#It's worth noting that the numeric_precision of these columns is stored as 10, 3, and 5 (ie their maximums since the '-' character needs to be accounted for as well) in `information_schema`.`columns`. So... it looks like MySQL must be using another table or parsing the names directly to get the real width/range/size.... I don't think there's any way to solve this one using DBIC::Schema::Loader. However, it's easy to solve by hand in the DBIC classes themselves.
#While probably not the end of the world, it is certainly sub-optimal...
*DBIC::Schema::Loader doesn't appear to have picked up any indexes except primary keys (ie there are missing keys/indexes)
#This causes `items_search_fields` and `message_queue` to not be created when running deploy()
#`message_queue` can be mitigated by adding a primary key
#`items_search_fields` requires `authorised_values`.`category` to be a key in order to have a foreign key reference to it
#This is also just plain suboptimal in general... I'll have to investigate more, but I'm not sure there is a solution to this using DBIC::Schema::Loader...
==Problems with SQL::Translator::* via DBIx::Class::Schema==
===Solvable issues===
*SOLUTION/WORKAROUND AVAILABLE: `itemtypes` and `statistics` have columns with "double" data types, which is stored as "double precision" in DBIC, which cause problems with SQL diffs.
#DBIx::Class::Schema::Loader::DBI::mysql translates "double" into "double precision", which creates fatal errors when using SQL::Translator::Parser::MySQL via SQL::Translator::Diff via DBIx::Class::Schema to create SQL diffs between different schema versions. 
#dcook isn't sure whether this is really a bug in the Loader or the Parser...but worth investigating
#In any case, an easy solution would be to use regex via the "filter_genderated_code" option in DBIx::Class::Schema::Loader::make_schema_at():
Example:
 filter_generated_code => sub {
 my ($type, $class, $text) = @_;
 $text =~ s/"double precision"/"double"/g;
 return $text;
 }
*SOLUTION/WORKAROUND AVAILABLE: "varchar" fields over 255 characters get translated into "text" fields UNLESS the correct "producer_args" are passed to SQL::Translator::Producer::MySQL via DBIx::Class::Schema
#my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0; (from SQL::Translator::Producer::MySQL)
# "producer_args => {mysql_version => '5.5.33'}" as part of the hashref "$sqltargs" (from DBIC deploy() $sqltargs)
*SOLUTION/WORKAROUND AVAILABLE: Default values of "CURRENT_TIMESTAMP" are being recorded in DBIC as lowercase "current_timestamp" which causes spurious entries in SQL diffs.
#SQL::Translator::Parser::MySQL UPPERCASES default values of "current_timestamp" into "CURRENT_TIMESTAMP" (when doing a diff, this parser is run over a past schema dump)
##For more information about how this parser works, consult: http://search.cpan.org/~jtbraun/Parse-RecDescent-1.967009/lib/Parse/RecDescent.pm
#SQL::Translator::Parser::DBIx::Class does NOT UPPERCASE default values (this parser is run over the current DBIC schema)
#Unfortunately, DBIx::Class::Schema::Loader::DBI::mysql appears to LOWERCASE default values of 'CURRENT_TIMESTAMP' to 'current_timestamp'
#This difference in parsing causes spurious alter statements when running SQL::Translator::Diff
#dcook thinks this must be a bug in DBIx::Class::Schema::Loader::DBI::mysql... as there really is no reason to lowercase 'CURRENT_TIMESTAMP'
#However, an easy solution would be to use regex via the "filter_genderated_code" option in DBIx::Class::Schema::Loader::make_schema_at():
Example:
 filter_generated_code => sub {
 my ($type, $class, $text) = @_;
 $text =~ s/(\\"current_timestamp"|\scurrent_timestamp\s)/\U$1\E/g;
 return $text;
 }
===Unsolved issues===
*When a primary key is also a foreign key (with an auto generated index), it creates a spurious undesirable "DROP INDEX" statement in the SQL diff.
#I think this might be because the generated SQL tries to create an unnamed index for the primary key/foreign key, but I'm not sure.
#dcook doesn't know in which module this bug originates but it might be worth investigating...
#In any case, an easy solution be to add primary keys to all tables where the primary key is also a foreign key. However, we currently are treating biblio.biblionumber as a primary key and a foreign key, so that workaround wouldn't work in that situation...as it really is the primary key (not a poorly chosen primary key like other tables where the primary key is really just a foreign key to another table's primary key)
*Problems doing SQL diffs when dropping an indexed foreign key
#It creates statements to drop the index and the foreign key, but it also appends another "ALTER TABLE" statement to the end of the current statement creating invalid SQL
#This can be mitigated by using the "no_batch_alters" option, as it'll separate out the three statements. The first two will succeed and the third will just be ignored as it doesn't actually ALTER anything. Still, this seems sub-optimal.
#Another option is to hand-edit SQL after a DIFF and before an UPGRADE
*Dropping a foreign key that is also a primary key, you'll get SQL which will generate errors.
#Dropping the primary key will drop the index and then trying to drop the foreign key index will fail because it was the primary index.
#This can be mitigated by never having a primary key that is also a foreign key, I suppose, or by hand-editing SQL after a DIFF and before an UPGRADE
Category:Development
Category:Database_RFCs