Revision: 2630
https://sourceforge.net/p/mrbs/code/2630/
Author: cimorrison
Date: 2013-01-01 13:52:22 +0000 (Tue, 01 Jan 2013)
Log Message:
-----------
Revised structure of new tables. WARNING: if you have already been using the
new table structure, ie using Rev 2607 or later from the linked_bookings
branch, then your table structure will no longer be valid, although MRBS should
still continue to work. (Apologies - it was easier just to go back and redo
Upgrade 36, rather than try and undo and then redo the tables using another
upgrade.)
Revision Links:
--------------
https://sourceforge.net/p/mrbs/code/2607/
Modified Paths:
--------------
mrbs/branches/linked_bookings/tables.my.pre41.sql
mrbs/branches/linked_bookings/tables.my.sql
mrbs/branches/linked_bookings/tables.pg.pre73.sql
mrbs/branches/linked_bookings/tables.pg.sql
mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql
mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql
Modified: mrbs/branches/linked_bookings/tables.my.pre41.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.my.pre41.sql 2013-01-01 13:44:05 UTC
(rev 2629)
+++ mrbs/branches/linked_bookings/tables.my.pre41.sql 2013-01-01 13:52:22 UTC
(rev 2630)
@@ -101,20 +101,17 @@
KEY idxEndTime (end_time)
);
-CREATE TABLE mrbs_room_entry
+CREATE TABLE IF NOT EXISTS mrbs_room_entry
(
- id int NOT NULL auto_increment,
- room_id int DEFAULT NULL,
- entry_id int DEFAULT NULL,
+ room_id int NOT NULL,
+ entry_id int NOT NULL,
FOREIGN KEY (room_id) REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (entry_id) REFERENCES mrbs_entry(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- PRIMARY KEY (id),
- KEY idxRoomEntryRoom (room_id),
- KEY idxRoomEntryEntry (entry_id)
+ PRIMARY KEY (room_id, entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_repeat
@@ -146,18 +143,15 @@
CREATE TABLE IF NOT EXISTS mrbs_room_repeat
(
- id int NOT NULL auto_increment,
- room_id int DEFAULT NULL,
- repeat_id int DEFAULT NULL,
+ room_id int NOT NULL,
+ repeat_id int NOT NULL,
FOREIGN KEY (room_id) REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (repeat_id) REFERENCES mrbs_repeat(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- PRIMARY KEY (id),
- KEY idxRoomRepeatRoom (room_id),
- KEY idxRoomRepeatRepeat (repeat_id)
+ PRIMARY KEY (room_id, repeat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_variables
Modified: mrbs/branches/linked_bookings/tables.my.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.my.sql 2013-01-01 13:44:05 UTC (rev
2629)
+++ mrbs/branches/linked_bookings/tables.my.sql 2013-01-01 13:52:22 UTC (rev
2630)
@@ -101,20 +101,17 @@
KEY idxEndTime (end_time)
) ENGINE=InnoDB;
-CREATE TABLE mrbs_room_entry
+CREATE TABLE IF NOT EXISTS mrbs_room_entry
(
- id int NOT NULL auto_increment,
- room_id int DEFAULT NULL,
- entry_id int DEFAULT NULL,
+ room_id int NOT NULL,
+ entry_id int NOT NULL,
FOREIGN KEY (room_id) REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (entry_id) REFERENCES mrbs_entry(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- PRIMARY KEY (id),
- KEY idxRoomEntryRoom (room_id),
- KEY idxRoomEntryEntry (entry_id)
+ PRIMARY KEY (room_id, entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_repeat
@@ -146,18 +143,15 @@
CREATE TABLE IF NOT EXISTS mrbs_room_repeat
(
- id int NOT NULL auto_increment,
- room_id int DEFAULT NULL,
- repeat_id int DEFAULT NULL,
+ room_id int NOT NULL,
+ repeat_id int NOT NULL,
FOREIGN KEY (room_id) REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (repeat_id) REFERENCES mrbs_repeat(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- PRIMARY KEY (id),
- KEY idxRoomRepeatRoom (room_id),
- KEY idxRoomRepeatRepeat (repeat_id)
+ PRIMARY KEY (room_id, repeat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_variables
Modified: mrbs/branches/linked_bookings/tables.pg.pre73.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.pg.pre73.sql 2013-01-01 13:44:05 UTC
(rev 2629)
+++ mrbs/branches/linked_bookings/tables.pg.pre73.sql 2013-01-01 13:52:22 UTC
(rev 2630)
@@ -100,16 +100,14 @@
CREATE TABLE mrbs_room_entry
(
- id serial primary key,
- room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ room_id int NOT NULL REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- entry_id int DEFAULT NULL REFERENCES mrbs_entry(id)
+ entry_id int NOT NULL REFERENCES mrbs_entry(id)
ON UPDATE CASCADE
- ON DELETE CASCADE
+ ON DELETE CASCADE,
+ PRIMARY KEY (room_id, entry_id)
);
-create index mrbs_idxRoomEntryRoom on mrbs_room_entry(room_id);
-create index mrbs_idxRoomEntryEntry on mrbs_room_entry(entry_id);
CREATE TABLE mrbs_repeat
(
@@ -138,16 +136,14 @@
CREATE TABLE mrbs_room_repeat
(
- id serial primary key,
- room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ room_id int NOT NULL REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- repeat_id int DEFAULT NULL REFERENCES mrbs_repeat(id)
+ repeat_id int NOT NULL REFERENCES mrbs_repeat(id)
ON UPDATE CASCADE
- ON DELETE CASCADE
+ ON DELETE CASCADE,
+ PRIMARY KEY (room_id, repeat_id)
);
-create index mrbs_idxRoomRepeatRoom on mrbs_room_repeat(room_id);
-create index mrbs_idxRoomRepeatEntry on mrbs_room_repeat(repeat_id);
CREATE TABLE mrbs_variables
(
Modified: mrbs/branches/linked_bookings/tables.pg.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.pg.sql 2013-01-01 13:44:05 UTC (rev
2629)
+++ mrbs/branches/linked_bookings/tables.pg.sql 2013-01-01 13:52:22 UTC (rev
2630)
@@ -113,16 +113,14 @@
CREATE TABLE mrbs_room_entry
(
- id serial primary key,
- room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ room_id int NOT NULL REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- entry_id int DEFAULT NULL REFERENCES mrbs_entry(id)
+ entry_id int NOT NULL REFERENCES mrbs_entry(id)
ON UPDATE CASCADE
- ON DELETE CASCADE
+ ON DELETE CASCADE,
+ PRIMARY KEY (room_id, entry_id)
);
-create index mrbs_idxRoomEntryRoom on mrbs_room_entry(room_id);
-create index mrbs_idxRoomEntryEntry on mrbs_room_entry(entry_id);
CREATE TABLE mrbs_repeat
(
@@ -151,16 +149,14 @@
CREATE TABLE mrbs_room_repeat
(
- id serial primary key,
- room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ room_id int NOT NULL REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- repeat_id int DEFAULT NULL REFERENCES mrbs_repeat(id)
+ repeat_id int NOT NULL REFERENCES mrbs_repeat(id)
ON UPDATE CASCADE
- ON DELETE CASCADE
+ ON DELETE CASCADE,
+ PRIMARY KEY (room_id, repeat_id)
);
-create index mrbs_idxRoomRepeatRoom on mrbs_room_repeat(room_id);
-create index mrbs_idxRoomRepeatEntry on mrbs_room_repeat(repeat_id);
CREATE TABLE mrbs_variables
(
Modified: mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql
===================================================================
--- mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql 2013-01-01
13:44:05 UTC (rev 2629)
+++ mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql 2013-01-01
13:52:22 UTC (rev 2630)
@@ -13,18 +13,15 @@
CREATE TABLE IF NOT EXISTS %DB_TBL_PREFIX%room_entry
(
- id int NOT NULL auto_increment,
- room_id int DEFAULT NULL,
- entry_id int DEFAULT NULL,
+ room_id int NOT NULL,
+ entry_id int NOT NULL,
FOREIGN KEY (room_id) REFERENCES %DB_TBL_PREFIX%room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (entry_id) REFERENCES %DB_TBL_PREFIX%entry(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- PRIMARY KEY (id),
- KEY idxRoomEntryRoom (room_id),
- KEY idxRoomEntryEntry (entry_id)
+ PRIMARY KEY (room_id, entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO %DB_TBL_PREFIX%room_entry (room_id, entry_id)
@@ -32,18 +29,15 @@
CREATE TABLE IF NOT EXISTS %DB_TBL_PREFIX%room_repeat
(
- id int NOT NULL auto_increment,
- room_id int DEFAULT NULL,
- repeat_id int DEFAULT NULL,
+ room_id int NOT NULL,
+ repeat_id int NOT NULL,
FOREIGN KEY (room_id) REFERENCES %DB_TBL_PREFIX%room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (repeat_id) REFERENCES %DB_TBL_PREFIX%repeat(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- PRIMARY KEY (id),
- KEY idxRoomRepeatRoom (room_id),
- KEY idxRoomRepeatRepeat (repeat_id)
+ PRIMARY KEY (room_id, repeat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO %DB_TBL_PREFIX%room_repeat (room_id, repeat_id)
Modified: mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql
===================================================================
--- mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql 2013-01-01
13:44:05 UTC (rev 2629)
+++ mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql 2013-01-01
13:52:22 UTC (rev 2630)
@@ -4,32 +4,28 @@
CREATE TABLE %DB_TBL_PREFIX%room_entry
(
- id serial primary key,
- room_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%room(id)
+ room_id int NOT NULL REFERENCES %DB_TBL_PREFIX%room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- entry_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%entry(id)
+ entry_id int NOT NULL REFERENCES %DB_TBL_PREFIX%entry(id)
ON UPDATE CASCADE
- ON DELETE CASCADE
+ ON DELETE CASCADE,
+ PRIMARY KEY (room_id, entry_id)
);
-create index %DB_TBL_PREFIX%idxRoomEntryRoom on
%DB_TBL_PREFIX%room_entry(room_id);
-create index %DB_TBL_PREFIX%idxRoomEntryEntry on
%DB_TBL_PREFIX%room_entry(entry_id);
INSERT INTO %DB_TBL_PREFIX%room_entry (room_id, entry_id)
SELECT room_id, id FROM %DB_TBL_PREFIX%entry;
CREATE TABLE %DB_TBL_PREFIX%room_repeat
(
- id serial primary key,
- room_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%room(id)
+ room_id int NOT NULL REFERENCES %DB_TBL_PREFIX%room(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
- repeat_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%repeat(id)
+ repeat_id int NOT NULL REFERENCES %DB_TBL_PREFIX%repeat(id)
ON UPDATE CASCADE
- ON DELETE CASCADE
+ ON DELETE CASCADE,
+ PRIMARY KEY (room_id, repeat_id)
);
-create index %DB_TBL_PREFIX%idxRoomRepeatRoom on
%DB_TBL_PREFIX%room_repeat(room_id);
-create index %DB_TBL_PREFIX%idxRoomRepeatEntry on
%DB_TBL_PREFIX%room_repeat(repeat_id);
INSERT INTO %DB_TBL_PREFIX%room_repeat (room_id, repeat_id)
SELECT room_id, id FROM %DB_TBL_PREFIX%repeat;
------------------------------------------------------------------------------
Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS
and more. Get SQL Server skills now (including 2012) with LearnDevNow -
200+ hours of step-by-step video tutorials by Microsoft MVPs and experts.
SALE $99.99 this month only - learn more at:
http://p.sf.net/sfu/learnmore_122512
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
