[Koha-cvs] koha/updater updatedatabase
paul poulain
paul at koha-fr.org
Wed Jan 25 16:16:07 CET 2006
CVSROOT: /cvsroot/koha
Module name: koha
Branch:
Changes by: paul poulain <tipaul at savannah.gnu.org> 06/01/25 15:16:07
Modified files:
updater : updatedatabase
Log message:
updating DB :
* removing useless tables
* adding useful indexes
* altering some columns definitions
* The goal being to have updater working fine for foreign keys.
For me it's done, let me know if it works for you. You can see an updated schema of the DB (with constraints) on the wiki
CVSWeb URLs:
http://cvs.savannah.gnu.org/viewcvs/koha/koha/updater/updatedatabase.diff?tr1=1.127&tr2=1.128&r1=text&r2=text
Patches:
Index: koha/updater/updatedatabase
diff -u koha/updater/updatedatabase:1.127 koha/updater/updatedatabase:1.128
--- koha/updater/updatedatabase:1.127 Tue Jan 24 17:57:17 2006
+++ koha/updater/updatedatabase Wed Jan 25 15:16:06 2006
@@ -1,6 +1,6 @@
#!/usr/bin/perl
-# $Id: updatedatabase,v 1.127 2006/01/24 17:57:17 tipaul Exp $
+# $Id: updatedatabase,v 1.128 2006/01/25 15:16:06 tipaul Exp $
# Database Updater
# This script checks for required updates to the database.
@@ -103,6 +103,12 @@
);
my %dropable_table = (
+ sessionqueries => 'sessionqueries',
+ marcrecorddone => 'marcrecorddone',
+ users => 'users',
+ itemsprices => 'itemsprices',
+ biblioanalysis => 'biblioanalysis',
+ borexp => 'borexp',
# tablename => 'tablename',
);
@@ -320,6 +326,42 @@
extra => '',
},
],
+ aqbooksellers => [
+ {
+ field => 'listprice',
+ type => 'varchar(10)',
+ null => 'NULL',
+ key => '',
+ default => '',
+ extra => '',
+ },
+ {
+ field => 'invoiceprice',
+ type => 'varchar(10)',
+ null => 'NULL',
+ key => '',
+ default => '',
+ extra => '',
+ },
+ ],
+ issues => [
+ {
+ field => 'borrowernumber',
+ type => 'int(11)',
+ null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
+ key => '',
+ default => '',
+ extra => '',
+ },
+ {
+ field => 'itemnumber',
+ type => 'int(11)',
+ null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
+ key => '',
+ default => '',
+ extra => '',
+ },
+ ],
);
my %indexes = (
@@ -368,6 +410,15 @@
{ indexname => 'ordernumber',
content => 'ordernumber',
},
+ { indexname => 'bookfundid',
+ content => 'bookfundid',
+ },
+ ],
+ currency => [
+ { indexname => 'PRIMARY',
+ content => 'currency',
+ type => 'PRIMARY',
+ }
],
);
@@ -394,6 +445,8 @@
onDelete => 'CASCADE',
},
],
+ # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
+ # easily deleted, but branches/itemtype not too easy to empty...
biblioitems => [
{ key => 'biblionumber',
foreigntable => 'biblio',
@@ -401,6 +454,12 @@
onUpdate => 'CASCADE',
onDelete => 'CASCADE',
},
+ { key => 'itemtype',
+ foreigntable => 'itemtypes',
+ foreignkey => 'itemtype',
+ onUpdate => 'CASCADE',
+ onDelete => 'RESTRICT',
+ },
],
items => [
{ key => 'biblioitemnumber',
@@ -412,13 +471,13 @@
{ key => 'homebranch',
foreigntable => 'branches',
foreignkey => 'branchcode',
- onUpdate => 'RESTRICT',
+ onUpdate => 'CASCADE',
onDelete => 'RESTRICT',
},
{ key => 'holdingbranch',
foreigntable => 'branches',
foreignkey => 'branchcode',
- onUpdate => 'RESTRICT',
+ onUpdate => 'CASCADE',
onDelete => 'RESTRICT',
},
],
@@ -445,12 +504,6 @@
onUpdate => 'CASCADE',
onDelete => 'RESTRICT',
},
-# { key => 'booksellerid',
-# foreigntable => 'aqbooksellers',
-# foreignkey => 'id',
-# onUpdate => 'CASCADE',
-# onDelete => 'RESTRICT',
-# },
],
aqorders => [
{ key => 'basketno',
@@ -459,6 +512,26 @@
onUpdate => 'CASCADE',
onDelete => 'CASCADE',
},
+ { key => 'biblionumber',
+ foreigntable => 'biblio',
+ foreignkey => 'biblionumber',
+ onUpdate => 'SET NULL',
+ onDelete => 'SET NULL',
+ },
+ ],
+ aqbooksellers => [
+ { key => 'listprice',
+ foreigntable => 'currency',
+ foreignkey => 'currency',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'invoiceprice',
+ foreigntable => 'currency',
+ foreignkey => 'currency',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
],
aqorderbreakdown => [
{ key => 'ordernumber',
@@ -467,7 +540,126 @@
onUpdate => 'CASCADE',
onDelete => 'CASCADE',
},
+ { key => 'bookfundid',
+ foreigntable => 'aqbookfund',
+ foreignkey => 'bookfundid',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ ],
+ branchtransfers => [
+ { key => 'frombranch',
+ foreigntable => 'branches',
+ foreignkey => 'branchcode',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'tobranch',
+ foreigntable => 'branches',
+ foreignkey => 'branchcode',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'itemnumber',
+ foreigntable => 'items',
+ foreignkey => 'itemnumber',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ ],
+ issuingrules => [
+ { key => 'categorycode',
+ foreigntable => 'categories',
+ foreignkey => 'categorycode',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'itemtype',
+ foreigntable => 'itemtypes',
+ foreignkey => 'itemtype',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ ],
+ issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
+ # for stat purposes
+ { key => 'borrowernumber',
+ foreigntable => 'borrowers',
+ foreignkey => 'borrowernumber',
+ onUpdate => 'SET NULL',
+ onDelete => 'SET NULL',
+ },
+ { key => 'itemnumber',
+ foreigntable => 'items',
+ foreignkey => 'itemnumber',
+ onUpdate => 'SET NULL',
+ onDelete => 'SET NULL',
+ },
+ ],
+ reserves => [
+ { key => 'borrowernumber',
+ foreigntable => 'borrowers',
+ foreignkey => 'borrowernumber',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'biblionumber',
+ foreigntable => 'biblio',
+ foreignkey => 'biblionumber',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'itemnumber',
+ foreigntable => 'items',
+ foreignkey => 'itemnumber',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'branchcode',
+ foreigntable => 'branches',
+ foreignkey => 'branchcode',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ ],
+ borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
+ # but prevent deleting a branch as soon as it has 1 borrower !
+ { key => 'categorycode',
+ foreigntable => 'categories',
+ foreignkey => 'categorycode',
+ onUpdate => 'RESTRICT',
+ onDelete => 'RESTRICT',
+ },
+ { key => 'branchcode',
+ foreigntable => 'branches',
+ foreignkey => 'branchcode',
+ onUpdate => 'RESTRICT',
+ onDelete => 'RESTRICT',
+ },
],
+ accountlines => [
+ { key => 'borrowernumber',
+ foreigntable => 'borrowers',
+ foreignkey => 'borrowernumber',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ { key => 'itemnumber',
+ foreigntable => 'items',
+ foreignkey => 'itemnumber',
+ onUpdate => 'SET NULL',
+ onDelete => 'SET NULL',
+ },
+ ],
+ auth_tag_structure => [
+ { key => 'authtypecode',
+ foreigntable => 'auth_types',
+ foreignkey => 'authtypecode',
+ onUpdate => 'CASCADE',
+ onDelete => 'CASCADE',
+ },
+ ],
+ # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
);
#-------------------
@@ -742,14 +934,25 @@
} else {
print "Creating $foreign_table in $table\n";
# first, drop any orphan value in child table
- my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
- $dbh->do($sql);
- print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
- $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
+ if ($row->{onDelete} ne "RESTRICT") {
+ my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
+ $dbh->do($sql);
+ print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
+ }
+ my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
$sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
$sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
$dbh->do($sql);
- print "SQL ERROR: $sql : $dbh->errstr \n" if $dbh->err;
+ if ($dbh->err) {
+ print "====================
+An error occured during :
+\t$sql
+It probably means there is something wrong in your DB : a row ($table.$row->{key}) refers to a value in $row->{foreigntable}.$row->{foreignkey} that does not exist. solve the problem and run updater again (or just the previous SQL statement).
+You can find those values with select
+\t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
+====================\n
+";
+ }
}
}
}
@@ -1026,6 +1229,15 @@
exit;
# $Log: updatedatabase,v $
+# Revision 1.128 2006/01/25 15:16:06 tipaul
+# updating DB :
+# * removing useless tables
+# * adding useful indexes
+# * altering some columns definitions
+# * The goal being to have updater working fine for foreign keys.
+#
+# For me it's done, let me know if it works for you. You can see an updated schema of the DB (with constraints) on the wiki
+#
# Revision 1.127 2006/01/24 17:57:17 tipaul
# DB improvements : adding foreign keys on some tables. partial stuff done.
#
More information about the Koha-cvs
mailing list