Revision: 2962
https://sourceforge.net/p/mrbs/code/2962/
Author: cimorrison
Date: 2015-01-19 12:31:21 +0000 (Mon, 19 Jan 2015)
Log Message:
-----------
Added SQL to tidy up the database tables before the foreign keys are added,
getting rid of any rows which do not refer to a valid row in the parent table.
(Note that these rows are not visible anyway to MRBS users or admins and have
probably been created by rows being deleted using a database admin tool rather
than MRBS).
Modified Paths:
--------------
mrbs/trunk/web/upgrade/40/mysql.sql
mrbs/trunk/web/upgrade/40/pgsql.sql
mrbs/trunk/web/upgrade/41/mysql.sql
mrbs/trunk/web/upgrade/41/pgsql.sql
Modified: mrbs/trunk/web/upgrade/40/mysql.sql
===================================================================
--- mrbs/trunk/web/upgrade/40/mysql.sql 2015-01-18 16:57:30 UTC (rev 2961)
+++ mrbs/trunk/web/upgrade/40/mysql.sql 2015-01-19 12:31:21 UTC (rev 2962)
@@ -2,6 +2,17 @@
# Add some foreign keys
+# First of all tidy up the database getting rid of any zombie rows which would
prevent
+# the foreign keys being created. Note that these rows will not be visible
to users
+# and admins through MRBS. They have most likely been created when a row has
been
+# deleted from a table using a database admin tool, rather than through MRBS.
Of course,
+# foreign keys will stop this happening in the future.
+
+DELETE FROM %DB_TBL_PREFIX%room WHERE area_id NOT IN (SELECT id FROM
%DB_TBL_PREFIX%area);
+DELETE FROM %DB_TBL_PREFIX%repeat WHERE room_id NOT IN (SELECT id FROM
%DB_TBL_PREFIX%room);
+DELETE FROM %DB_TBL_PREFIX%entry WHERE room_id NOT IN (SELECT id FROM
%DB_TBL_PREFIX%room);
+
+
ALTER TABLE %DB_TBL_PREFIX%room
ADD FOREIGN KEY (area_id)
REFERENCES %DB_TBL_PREFIX%area(id)
Modified: mrbs/trunk/web/upgrade/40/pgsql.sql
===================================================================
--- mrbs/trunk/web/upgrade/40/pgsql.sql 2015-01-18 16:57:30 UTC (rev 2961)
+++ mrbs/trunk/web/upgrade/40/pgsql.sql 2015-01-19 12:31:21 UTC (rev 2962)
@@ -2,6 +2,17 @@
-- Add some foreign keys
+-- First of all tidy up the database getting rid of any zombie rows which
would prevent
+-- the foreign keys being created. Note that these rows will not be visible
to users
+-- and admins through MRBS. They have most likely been created when a row
has been
+-- deleted from a table using a database admin tool, rather than through MRBS.
Of course,
+-- foreign keys will stop this happening in the future.
+
+DELETE FROM %DB_TBL_PREFIX%room WHERE area_id NOT IN (SELECT id FROM
%DB_TBL_PREFIX%area);
+DELETE FROM %DB_TBL_PREFIX%repeat WHERE room_id NOT IN (SELECT id FROM
%DB_TBL_PREFIX%room);
+DELETE FROM %DB_TBL_PREFIX%entry WHERE room_id NOT IN (SELECT id FROM
%DB_TBL_PREFIX%room);
+
+
ALTER TABLE %DB_TBL_PREFIX%room
ADD FOREIGN KEY (area_id)
REFERENCES %DB_TBL_PREFIX%area(id)
Modified: mrbs/trunk/web/upgrade/41/mysql.sql
===================================================================
--- mrbs/trunk/web/upgrade/41/mysql.sql 2015-01-18 16:57:30 UTC (rev 2961)
+++ mrbs/trunk/web/upgrade/41/mysql.sql 2015-01-19 12:31:21 UTC (rev 2962)
@@ -1,6 +1,7 @@
# $Id$
# Make mrbs_entry.repeat_id a foreign key
+
# Previously repeat_id was set to zero if there was no repeat.
# However this breaks our foreign key constraint so we have to modify
# the repeat_id column before we can create the foreign key.
@@ -11,6 +12,20 @@
UPDATE %DB_TBL_PREFIX%entry
SET repeat_id=NULL WHERE repeat_id=0;
+# Tidy up the database getting rid of any zombie rows which would prevent
+# the foreign key being created. Note that these rows will not be visible to
users
+# and admins through MRBS. They have most likely been created when a row has
been
+# deleted from a table using a database admin tool, rather than through MRBS.
Of course,
+# foreign keys will stop this happening in the future.
+
+DELETE FROM %DB_TBL_PREFIX%entry
+ WHERE repeat_id IS NOT NULL
+ AND repeat_id NOT IN (SELECT id FROM %DB_TBL_PREFIX%repeat);
+
+DELETE FROM %DB_TBL_PREFIX%repeat
+ WHERE id NOT IN (SELECT repeat_id FROM %DB_TBL_PREFIX%entry WHERE repeat_id
IS NOT NULL);
+
+
ALTER TABLE %DB_TBL_PREFIX%entry
ADD FOREIGN KEY (repeat_id)
REFERENCES %DB_TBL_PREFIX%repeat(id)
Modified: mrbs/trunk/web/upgrade/41/pgsql.sql
===================================================================
--- mrbs/trunk/web/upgrade/41/pgsql.sql 2015-01-18 16:57:30 UTC (rev 2961)
+++ mrbs/trunk/web/upgrade/41/pgsql.sql 2015-01-19 12:31:21 UTC (rev 2962)
@@ -12,7 +12,23 @@
UPDATE %DB_TBL_PREFIX%entry
SET repeat_id=NULL WHERE repeat_id=0;
+
+-- Tidy up the database getting rid of any zombie rows which would prevent
+-- the foreign key being created. Note that these rows will not be visible
to users
+-- and admins through MRBS. They have most likely been created when a row
has been
+-- deleted from a table using a database admin tool, rather than through MRBS.
Of course,
+-- foreign keys will stop this happening in the future.
+DELETE FROM %DB_TBL_PREFIX%entry
+ WHERE repeat_id IS NOT NULL
+ AND repeat_id NOT IN (SELECT id FROM %DB_TBL_PREFIX%repeat);
+
+-- Also, while we're at it, get rid of any redundant entries in the repeat
table, ie rows
+-- that are not referenced by a row in the entry table.
+DELETE FROM %DB_TBL_PREFIX%repeat
+ WHERE id NOT IN (SELECT repeat_id FROM %DB_TBL_PREFIX%entry WHERE repeat_id
IS NOT NULL);
+
+
ALTER TABLE %DB_TBL_PREFIX%entry
ADD FOREIGN KEY (repeat_id)
REFERENCES %DB_TBL_PREFIX%repeat(id)
------------------------------------------------------------------------------
New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
GigeNET is offering a free month of service with a new server in Ashburn.
Choose from 2 high performing configs, both with 100TB of bandwidth.
Higher redundancy.Lower latency.Increased capacity.Completely compliant.
http://p.sf.net/sfu/gigenet
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
