MergingOfTables

From Koha Wiki

Jump to: navigation, search

Contents

 Glourious plan to merge tables

Why?

In Koha's DB, instead of having a "soft" deletion of entries by flagging the deleted one as so, the rows in the db is actually deleted, but only after a new row in a different table is inserted.

Koha's DB is relational, so there is a cascade of links between each object: a biblio is referred by many items, and each item is referred by issues, holds, messages.

E.g.: when a row in the item table is moved from "items" to "deleted_items", then all the references from holds, transfers and so on will be broken.

The suggested more orthodox approach to this would be to add a "deleted_at" column, which is normally null unless the row is deleted, then it set to the timestamp of when it was deletion.

This means that the row is still there and will still be referred by other tables, but it just won't be shown in the relevant lists.

This would improve the maintenance of Koha in many ways (e.g.: the auto_increment id on those tables caused lots of issues when you add an item and delete it immediately after. the next new item will have the same itemnumber as the previously deleted one, clashing with it in many ways, see https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix ).


Previous discussions

Relevant discussions on mailing lists:

Which tables?

  • deletedborrowers => borrowers (CONSIDER: maybe postpone this?)
  • deletedbiblio => biblio
  • deletedbiblioitems => biblioitems
  • deletedbiblio_metadata => biblio_metadata
  • deleteditems => items
  • old_issues => issues
  • old_reserves => reserves

How

Add a deleted column to the tables, and merge old_/deleted-tables into them. Column could be a booelan (tinyint in MySQL) or maybe better deleted_at with a timestamp denoting when the borrower/item/reserve etc was deleted/obsoleted.

Bugzilla bugs

bug #20271

a initial patch for items, biblio (and corollary) pushed to bz

Other tasks

  • Update SQL reports library

Bugs and Issues

  • barcode in items table is UNIQUE, so deleting an item won't let you reuse its barcode again after.
  • maybe some view in koha should shows all the items (with the deleted ones grayed out) and allow un-delete (this should be a follow up task IMHO)
  • deletedborrowers table isn't used anywhere. It's fairly easy to merge them but any unique indexes (email?) would cause several problems. I think this need more planning.