From 947afdef3c333cbc0c61614b42fd28a86056e636 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 3 Mar 2015 00:03:34 -0800 Subject: [PATCH] Bug 6427 [QA Followup] - Drop old accounts tables --- installer/data/mysql/updatedatabase.pl | 238 ++++++++++++++++---------------- 1 file changed, 121 insertions(+), 117 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 22f6243..ee8e0fc 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9677,6 +9677,124 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.19.00.007"; +if ( CheckVersion($DBversion) ) { + my $orphan_budgets = $dbh->selectall_arrayref(q| + SELECT budget_id, budget_name, budget_code + FROM aqbudgets + WHERE budget_parent_id IS NOT NULL + AND budget_parent_id NOT IN ( + SELECT DISTINCT budget_id FROM aqbudgets + ) + |, { Slice => {} } ); + + if ( @$orphan_budgets ) { + for my $b ( @$orphan_budgets ) { + print "Fund $b->{budget_name} (code:$b->{budget_code}, id:$b->{budget_id}) does not have a parent, it may cause problem\n"; + } + print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: FAIL)\n"; + } else { + print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: OK)\n"; + } + SetVersion($DBversion); +} + +$DBversion = "3.19.00.008"; +if ( CheckVersion($DBversion) ) { + my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q| + SELECT COUNT(*) + FROM aqorders o + WHERE NOT EXISTS ( + SELECT NULL + FROM aqbudgets b + WHERE b.budget_id = o.budget_id + ); + |); + + if ( $number_of_orders_not_linked->[0] > 0 ) { + $dbh->do(q| + INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0) + |); + my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef ); + $dbh->do(qq| + INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id ); + |); + my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef ); + $dbh->do(qq| + UPDATE aqorders o + SET budget_id = $budget_id + WHERE NOT EXISTS ( + SELECT NULL + FROM aqbudgets b + WHERE b.budget_id = o.budget_id + ) + |); + } + + $dbh->do(q| + ALTER TABLE aqorders + ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) ON DELETE CASCADE ON UPDATE CASCADE + |); + + print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 ) ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n"; + SetVersion($DBversion); +} + +$DBversion = "3.19.00.009"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q| + UPDATE suggestions s SET s.budgetid = NULL + WHERE NOT EXISTS ( + SELECT NULL + FROM aqbudgets b + WHERE b.budget_id = s.budgetid + ); + |); + + $dbh->do(q| + ALTER TABLE suggestions + ADD CONSTRAINT suggestions_budget_id_fk FOREIGN KEY (budgetid) REFERENCES aqbudgets(budget_id) ON DELETE SET NULL ON UPDATE CASCADE + |); + + print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n"; + SetVersion($DBversion); +} + +$DBversion = "3.19.00.010"; +if ( CheckVersion($DBversion) ) { + $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('SessionRestrictionByIP','1','Check for Change in Remote IP address for Session Security. Disable when remote ip address changes frequently.','','YesNo')"); + print "Upgrade to $DBversion done (Bug 5511 - SessionRestrictionByIP)\n"; + SetVersion ($DBversion); +} + +$DBversion = "3.19.00.011"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q| + INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES + (20, 'lists', 'Lists', 0) + |); + $dbh->do(q| + INSERT INTO permissions (module_bit, code, description) VALUES + (20, 'delete_public_lists', 'Delete public lists') + |); + print "Upgrade to $DBversion done (Bug 13417: Add permission to delete public lists)\n"; + SetVersion ($DBversion); +} + +$DBversion = "3.19.00.012"; +if(CheckVersion($DBversion)) { + $dbh->do(q{ + ALTER TABLE biblioitems MODIFY COLUMN marcxml longtext + }); + + $dbh->do(q{ + ALTER TABLE deletedbiblioitems MODIFY COLUMN marcxml longtext + }); + + print "Upgrade to $DBversion done (Bug 13523 - Remove NOT NULL restriction on field marcxml due to mysql STRICT_TRANS_TABLES)\n"; + SetVersion($DBversion); +} + $DBversion = "3.19.00.XXX"; if ( CheckVersion($DBversion) ) { $dbh->do(q{ @@ -9859,127 +9977,13 @@ if ( CheckVersion($DBversion) ) { } } - print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n"; - SetVersion ($DBversion); -} - -$DBversion = "3.19.00.007"; -if ( CheckVersion($DBversion) ) { - my $orphan_budgets = $dbh->selectall_arrayref(q| - SELECT budget_id, budget_name, budget_code - FROM aqbudgets - WHERE budget_parent_id IS NOT NULL - AND budget_parent_id NOT IN ( - SELECT DISTINCT budget_id FROM aqbudgets - ) - |, { Slice => {} } ); - - if ( @$orphan_budgets ) { - for my $b ( @$orphan_budgets ) { - print "Fund $b->{budget_name} (code:$b->{budget_code}, id:$b->{budget_id}) does not have a parent, it may cause problem\n"; - } - print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: FAIL)\n"; - } else { - print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: OK)\n"; - } - SetVersion($DBversion); -} - -$DBversion = "3.19.00.008"; -if ( CheckVersion($DBversion) ) { - my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q| - SELECT COUNT(*) - FROM aqorders o - WHERE NOT EXISTS ( - SELECT NULL - FROM aqbudgets b - WHERE b.budget_id = o.budget_id - ); - |); - - if ( $number_of_orders_not_linked->[0] > 0 ) { - $dbh->do(q| - INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0) - |); - my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef ); - $dbh->do(qq| - INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id ); - |); - my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef ); - $dbh->do(qq| - UPDATE aqorders o - SET budget_id = $budget_id - WHERE NOT EXISTS ( - SELECT NULL - FROM aqbudgets b - WHERE b.budget_id = o.budget_id - ) - |); - } - - $dbh->do(q| - ALTER TABLE aqorders - ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) ON DELETE CASCADE ON UPDATE CASCADE - |); - - print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 ) ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n"; - SetVersion($DBversion); -} - -$DBversion = "3.19.00.009"; -if ( CheckVersion($DBversion) ) { - $dbh->do(q| - UPDATE suggestions s SET s.budgetid = NULL - WHERE NOT EXISTS ( - SELECT NULL - FROM aqbudgets b - WHERE b.budget_id = s.budgetid - ); - |); - - $dbh->do(q| - ALTER TABLE suggestions - ADD CONSTRAINT suggestions_budget_id_fk FOREIGN KEY (budgetid) REFERENCES aqbudgets(budget_id) ON DELETE SET NULL ON UPDATE CASCADE - |); + $dbh->do("DROP TABLE accountoffsets"); + $dbh->do("DROP TABLE accountlines"); - print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n"; - SetVersion($DBversion); -} - -$DBversion = "3.19.00.010"; -if ( CheckVersion($DBversion) ) { - $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('SessionRestrictionByIP','1','Check for Change in Remote IP address for Session Security. Disable when remote ip address changes frequently.','','YesNo')"); - print "Upgrade to $DBversion done (Bug 5511 - SessionRestrictionByIP)\n"; - SetVersion ($DBversion); -} - -$DBversion = "3.19.00.011"; -if ( CheckVersion($DBversion) ) { - $dbh->do(q| - INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES - (20, 'lists', 'Lists', 0) - |); - $dbh->do(q| - INSERT INTO permissions (module_bit, code, description) VALUES - (20, 'delete_public_lists', 'Delete public lists') - |); - print "Upgrade to $DBversion done (Bug 13417: Add permission to delete public lists)\n"; + print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n"; SetVersion ($DBversion); } -$DBversion = "3.19.00.012"; -if(CheckVersion($DBversion)) { - $dbh->do(q{ - ALTER TABLE biblioitems MODIFY COLUMN marcxml longtext - }); - - $dbh->do(q{ - ALTER TABLE deletedbiblioitems MODIFY COLUMN marcxml longtext - }); - - print "Upgrade to $DBversion done (Bug 13523 - Remove NOT NULL restriction on field marcxml due to mysql STRICT_TRANS_TABLES)\n"; - SetVersion($DBversion); -} =head1 FUNCTIONS -- 1.7.10.4