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

1. This variable is used by DBIx::Class::Schema::schema_version() and DBIx::Class::Schema::Versioned for determining the current schema version
2. Add "our $version" to Koha::Schema

• SOLUTION AVAILABLE: DBIC currrently contains extra foreign keys

1. For the following tables:`issues`, `items`, `biblio`
2. 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

1. For the following tables:`closure`,`closure_rrule`
2. 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().

1. 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")

1. 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

1. I'm not sure of the ramifications of this but it affects the following tables: `creator_layouts`, `marc_subfield_structure`
2. 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

1. Change `message_queue.message_id` to a primary key. (solved `message_queue` problem)
2. 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

1. For the following tables: `pending_offline_operations`
2. 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...
   1. http://search.cpan.org/~ribasushi/DBIx-Class-0.082810/lib/DBIx/Class/ResultSource.pm#source_info
3. 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...
   1. 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...)

1. DBIx::Class::Schema::Loader::DBI::mysql::_columns_info_for() translates "double" into "double precision"...
2. 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...)

1. DBIx::Class::Schema::Loader::DBI::mysql::_extra_column_info translates `COLUMN_DEFAULT` "CURRENT_TIME" to lowercase "current_time"...
2. See SQL::Translator::* issues for the solution/workaround to this one...

• SOLUTION/WORKAROUND AVAILABLE: 'ON UPDATE CURRENT_TIMESTAMP' is missing from many timestamps

1. 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`).
   1. 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...
2. 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'.
3. 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

1. This means that they all default to int(11), tinyint(4), and smallint(6) when the schema is installed via deploy()
2. 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.
3. 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)

1. This causes `items_search_fields` and `message_queue` to not be created when running deploy()
2. `message_queue` can be mitigated by adding a primary key
3. `items_search_fields` requires `authorised_values`.`category` to be a key in order to have a foreign key reference to it
4. 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.

1. 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.
2. dcook isn't sure whether this is really a bug in the Loader or the Parser...but worth investigating
3. 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

1. my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0; (from SQL::Translator::Producer::MySQL)
2. "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.

1. 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)
   1. For more information about how this parser works, consult: http://search.cpan.org/~jtbraun/Parse-RecDescent-1.967009/lib/Parse/RecDescent.pm
2. SQL::Translator::Parser::DBIx::Class does NOT UPPERCASE default values (this parser is run over the current DBIC schema)
3. Unfortunately, DBIx::Class::Schema::Loader::DBI::mysql appears to LOWERCASE default values of 'CURRENT_TIMESTAMP' to 'current_timestamp'
4. This difference in parsing causes spurious alter statements when running SQL::Translator::Diff
5. dcook thinks this must be a bug in DBIx::Class::Schema::Loader::DBI::mysql... as there really is no reason to lowercase 'CURRENT_TIMESTAMP'
6. 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.

1. 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.
2. dcook doesn't know in which module this bug originates but it might be worth investigating...
3. 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

1. 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
2. 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.
3. 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.

1. 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.
2. 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