Revision: 2607
https://sourceforge.net/p/mrbs/code/2607/
Author: cimorrison
Date: 2012-12-27 08:23:53 +0000 (Thu, 27 Dec 2012)
Log Message:
-----------
Set up new tables
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/dbsys.inc
Added Paths:
-----------
mrbs/branches/linked_bookings/web/upgrade/36/
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 2012-12-19 16:36:00 UTC
(rev 2606)
+++ mrbs/branches/linked_bookings/tables.my.pre41.sql 2012-12-27 08:23:53 UTC
(rev 2607)
@@ -102,6 +102,22 @@
KEY idxEndTime (end_time)
);
+CREATE TABLE mrbs_room_entry
+(
+ id int NOT NULL auto_increment,
+ room_id int DEFAULT NULL,
+ entry_id int DEFAULT 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)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
CREATE TABLE mrbs_repeat
(
id int NOT NULL auto_increment,
@@ -130,6 +146,22 @@
PRIMARY KEY (id)
);
+CREATE TABLE IF NOT EXISTS mrbs_room_repeat
+(
+ id int NOT NULL auto_increment,
+ room_id int DEFAULT NULL,
+ repeat_id int DEFAULT 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)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
CREATE TABLE mrbs_variables
(
id int NOT NULL auto_increment,
@@ -163,6 +195,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '35');
+ VALUES ( 'db_version', '36');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/branches/linked_bookings/tables.my.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.my.sql 2012-12-19 16:36:00 UTC (rev
2606)
+++ mrbs/branches/linked_bookings/tables.my.sql 2012-12-27 08:23:53 UTC (rev
2607)
@@ -100,6 +100,22 @@
PRIMARY KEY (id),
KEY idxStartTime (start_time),
KEY idxEndTime (end_time)
+) ENGINE=InnoDB;
+
+CREATE TABLE mrbs_room_entry
+(
+ id int NOT NULL auto_increment,
+ room_id int DEFAULT NULL,
+ entry_id int DEFAULT 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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_repeat
@@ -130,6 +146,22 @@
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE IF NOT EXISTS mrbs_room_repeat
+(
+ id int NOT NULL auto_increment,
+ room_id int DEFAULT NULL,
+ repeat_id int DEFAULT 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)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
CREATE TABLE mrbs_variables
(
id int NOT NULL auto_increment,
@@ -163,6 +195,6 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '35');
+ VALUES ( 'db_version', '36');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/branches/linked_bookings/tables.pg.pre73.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.pg.pre73.sql 2012-12-19 16:36:00 UTC
(rev 2606)
+++ mrbs/branches/linked_bookings/tables.pg.pre73.sql 2012-12-27 08:23:53 UTC
(rev 2607)
@@ -99,6 +99,19 @@
create index mrbs_idxStartTime on mrbs_entry(start_time);
create index mrbs_idxEndTime on mrbs_entry(end_time);
+CREATE TABLE mrbs_room_entry
+(
+ id serial primary key,
+ room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE,
+ entry_id int DEFAULT NULL REFERENCES mrbs_entry(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE
+);
+create index mrbs_idxRoomEntryRoom on mrbs_room_entry(room_id);
+create index mrbs_idxRoomEntryEntry on mrbs_room_entry(entry_id);
+
CREATE TABLE mrbs_repeat
(
id serial primary key,
@@ -125,6 +138,19 @@
ical_sequence smallint DEFAULT 0 NOT NULL
);
+CREATE TABLE mrbs_room_repeat
+(
+ id serial primary key,
+ room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE,
+ repeat_id int DEFAULT NULL REFERENCES mrbs_repeat(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE
+);
+create index mrbs_idxRoomRepeatRoom on mrbs_room_repeat(room_id);
+create index mrbs_idxRoomRepeatEntry on mrbs_room_repeat(repeat_id);
+
CREATE TABLE mrbs_variables
(
id serial primary key,
@@ -152,6 +178,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '35');
+ VALUES ('db_version', '36');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/branches/linked_bookings/tables.pg.sql
===================================================================
--- mrbs/branches/linked_bookings/tables.pg.sql 2012-12-19 16:36:00 UTC (rev
2606)
+++ mrbs/branches/linked_bookings/tables.pg.sql 2012-12-27 08:23:53 UTC (rev
2607)
@@ -112,6 +112,19 @@
create index mrbs_idxStartTime on mrbs_entry(start_time);
create index mrbs_idxEndTime on mrbs_entry(end_time);
+CREATE TABLE mrbs_room_entry
+(
+ id serial primary key,
+ room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE,
+ entry_id int DEFAULT NULL REFERENCES mrbs_entry(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE
+);
+create index mrbs_idxRoomEntryRoom on mrbs_room_entry(room_id);
+create index mrbs_idxRoomEntryEntry on mrbs_room_entry(entry_id);
+
CREATE TABLE mrbs_repeat
(
id serial primary key,
@@ -138,6 +151,19 @@
ical_sequence smallint DEFAULT 0 NOT NULL
);
+CREATE TABLE mrbs_room_repeat
+(
+ id serial primary key,
+ room_id int DEFAULT NULL REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE,
+ repeat_id int DEFAULT NULL REFERENCES mrbs_repeat(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE
+);
+create index mrbs_idxRoomRepeatRoom on mrbs_room_repeat(room_id);
+create index mrbs_idxRoomRepeatEntry on mrbs_room_repeat(repeat_id);
+
CREATE TABLE mrbs_variables
(
id serial primary key,
@@ -165,6 +191,6 @@
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '35');
+ VALUES ('db_version', '36');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/branches/linked_bookings/web/dbsys.inc
===================================================================
--- mrbs/branches/linked_bookings/web/dbsys.inc 2012-12-19 16:36:00 UTC (rev
2606)
+++ mrbs/branches/linked_bookings/web/dbsys.inc 2012-12-27 08:23:53 UTC (rev
2607)
@@ -7,16 +7,18 @@
// These variables specify the names of the tables in the database
global $db_tbl_prefix;
-$tbl_area = $db_tbl_prefix . "area";
-$tbl_entry = $db_tbl_prefix . "entry";
-$tbl_repeat = $db_tbl_prefix . "repeat";
-$tbl_room = $db_tbl_prefix . "room";
-$tbl_users = $db_tbl_prefix . "users";
-$tbl_variables = $db_tbl_prefix . "variables";
-$tbl_zoneinfo = $db_tbl_prefix . "zoneinfo";
+$tbl_area = $db_tbl_prefix . "area";
+$tbl_entry = $db_tbl_prefix . "entry";
+$tbl_repeat = $db_tbl_prefix . "repeat";
+$tbl_room = $db_tbl_prefix . "room";
+$tbl_room_entry = $db_tbl_prefix . "room_entry";
+$tbl_room_repeat = $db_tbl_prefix . "room_repeat";
+$tbl_users = $db_tbl_prefix . "users";
+$tbl_variables = $db_tbl_prefix . "variables";
+$tbl_zoneinfo = $db_tbl_prefix . "zoneinfo";
-$db_schema_version = 35;
+$db_schema_version = 36;
$local_db_schema_version = 1;
Added: mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql
===================================================================
--- mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql
(rev 0)
+++ mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql 2012-12-27
08:23:53 UTC (rev 2607)
@@ -0,0 +1,51 @@
+# $Id$
+
+# Create the room junction tables. First we have to make sure that
+# the room, entry and repeat tables use the InnoDB engine. Ordering them
+# first by the primary key column speeds up the operation.
+
+ALTER TABLE %DB_TBL_PREFIX%entry ORDER BY id;
+ALTER TABLE %DB_TBL_PREFIX%entry ENGINE = INNODB;
+ALTER TABLE %DB_TBL_PREFIX%repeat ORDER BY id;
+ALTER TABLE %DB_TBL_PREFIX%repeat ENGINE = INNODB;
+ALTER TABLE %DB_TBL_PREFIX%room ORDER BY id;
+ALTER TABLE %DB_TBL_PREFIX%room ENGINE = INNODB;
+
+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,
+ 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)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO %DB_TBL_PREFIX%room_entry (room_id, entry_id)
+ SELECT room_id, id FROM %DB_TBL_PREFIX%entry;
+
+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,
+ 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)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO %DB_TBL_PREFIX%room_repeat (room_id, repeat_id)
+ SELECT room_id, id FROM %DB_TBL_PREFIX%repeat;
+
Property changes on: mrbs/branches/linked_bookings/web/upgrade/36/mysql.sql
___________________________________________________________________
Added: svn:keywords
## -0,0 +1 ##
+Id
\ No newline at end of property
Added: svn:eol-style
## -0,0 +1 ##
+native
\ No newline at end of property
Added: mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql
===================================================================
--- mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql
(rev 0)
+++ mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql 2012-12-27
08:23:53 UTC (rev 2607)
@@ -0,0 +1,36 @@
+-- $Id$
+
+-- Create the room junction tables.
+
+CREATE TABLE %DB_TBL_PREFIX%room_entry
+(
+ id serial primary key,
+ room_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%room(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE,
+ entry_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%entry(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE
+);
+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)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE,
+ repeat_id int DEFAULT NULL REFERENCES %DB_TBL_PREFIX%repeat(id)
+ ON UPDATE CASCADE
+ ON DELETE CASCADE
+);
+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;
+
Property changes on: mrbs/branches/linked_bookings/web/upgrade/36/pgsql.sql
___________________________________________________________________
Added: svn:keywords
## -0,0 +1 ##
+Id
\ No newline at end of property
Added: svn:eol-style
## -0,0 +1 ##
+native
\ No newline at end of property------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
