[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Koha-cvs] koha/updater updatedatabase
|
From: |
paul poulain |
|
Subject: |
[Koha-cvs] koha/updater updatedatabase |
|
Date: |
Wed, 25 Jan 2006 15:16:07 +0000 |
CVSROOT: /cvsroot/koha
Module name: koha
Branch:
Changes by: paul poulain <address@hidden> 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.
#