Revision: 2948
https://sourceforge.net/p/mrbs/code/2948/
Author: cimorrison
Date: 2015-01-16 16:27:20 +0000 (Fri, 16 Jan 2015)
Log Message:
-----------
Added some foreign keys. (Haven't yet added a foreign key for the repeat_id in
the entry table because the entry table currently uses repeat_id=0 when there
is no repeat. This will have to be changed because it will raise a foreign
key constraint error, but there will probably need to be code changes made as
well).
Modified Paths:
--------------
mrbs/trunk/tables.my.sql
mrbs/trunk/tables.pg.sql
mrbs/trunk/web/dbsys.inc
Added Paths:
-----------
mrbs/trunk/web/upgrade/40/
mrbs/trunk/web/upgrade/40/mysql.sql
mrbs/trunk/web/upgrade/40/pgsql.sql
Modified: mrbs/trunk/tables.my.sql
===================================================================
--- mrbs/trunk/tables.my.sql 2015-01-16 11:33:58 UTC (rev 2947)
+++ mrbs/trunk/tables.my.sql 2015-01-16 16:27:20 UTC (rev 2948)
@@ -76,16 +76,21 @@
custom_html text CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (id),
+ FOREIGN KEY (area_id)
+ REFERENCES mrbs_area(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT,
KEY idxSortKey (sort_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-CREATE TABLE mrbs_entry
+CREATE TABLE mrbs_repeat
(
id int NOT NULL auto_increment,
start_time int DEFAULT '0' NOT NULL,
end_time int DEFAULT '0' NOT NULL,
- entry_type int DEFAULT '0' NOT NULL,
- repeat_id int DEFAULT '0' NOT NULL,
+ rep_type int DEFAULT '0' NOT NULL,
+ end_date int DEFAULT '0' NOT NULL,
+ rep_opt varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
room_id int DEFAULT '1' NOT NULL,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
create_by varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
@@ -93,6 +98,9 @@
name varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text CHARACTER SET utf8 COLLATE utf8_general_ci,
+ rep_num_weeks smallint NULL,
+ month_absolute smallint DEFAULT NULL,
+ month_relative varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT
NULL,
status tinyint unsigned NOT NULL DEFAULT 0,
reminded int,
info_time int,
@@ -100,21 +108,21 @@
info_text text CHARACTER SET utf8 COLLATE utf8_general_ci,
ical_uid varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
ical_sequence smallint DEFAULT 0 NOT NULL,
- ical_recur_id varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
-
+
PRIMARY KEY (id),
- KEY idxStartTime (start_time),
- KEY idxEndTime (end_time)
+ FOREIGN KEY (room_id)
+ REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-CREATE TABLE mrbs_repeat
+CREATE TABLE mrbs_entry
(
id int NOT NULL auto_increment,
start_time int DEFAULT '0' NOT NULL,
end_time int DEFAULT '0' NOT NULL,
- rep_type int DEFAULT '0' NOT NULL,
- end_date int DEFAULT '0' NOT NULL,
- rep_opt varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
+ entry_type int DEFAULT '0' NOT NULL,
+ repeat_id int DEFAULT '0' NOT NULL,
room_id int DEFAULT '1' NOT NULL,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
create_by varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
@@ -122,9 +130,6 @@
name varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text CHARACTER SET utf8 COLLATE utf8_general_ci,
- rep_num_weeks smallint NULL,
- month_absolute smallint DEFAULT NULL,
- month_relative varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT
NULL,
status tinyint unsigned NOT NULL DEFAULT 0,
reminded int,
info_time int,
@@ -132,8 +137,15 @@
info_text text CHARACTER SET utf8 COLLATE utf8_general_ci,
ical_uid varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
ical_sequence smallint DEFAULT 0 NOT NULL,
-
- PRIMARY KEY (id)
+ ical_recur_id varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci
DEFAULT '' NOT NULL,
+
+ PRIMARY KEY (id),
+ FOREIGN KEY (room_id)
+ REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT,
+ KEY idxStartTime (start_time),
+ KEY idxEndTime (end_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_variables
Modified: mrbs/trunk/tables.pg.sql
===================================================================
--- mrbs/trunk/tables.pg.sql 2015-01-16 11:33:58 UTC (rev 2947)
+++ mrbs/trunk/tables.pg.sql 2015-01-16 16:27:20 UTC (rev 2948)
@@ -81,7 +81,10 @@
(
id serial primary key,
disabled smallint DEFAULT 0 NOT NULL,
- area_id int DEFAULT 0 NOT NULL,
+ area_id int DEFAULT 0 NOT NULL
+ REFERENCES mrbs_area(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT,
room_name varchar(25) NOT NULL,
sort_key varchar(25) NOT NULL,
description varchar(60),
@@ -91,58 +94,64 @@
);
create index mrbs_idxSortKey on mrbs_room(sort_key);
-CREATE TABLE mrbs_entry
+CREATE TABLE mrbs_repeat
(
id serial primary key,
start_time int DEFAULT 0 NOT NULL,
end_time int DEFAULT 0 NOT NULL,
- entry_type int DEFAULT 0 NOT NULL,
- repeat_id int DEFAULT 0 NOT NULL,
- room_id int DEFAULT 1 NOT NULL,
+ rep_type int DEFAULT 0 NOT NULL,
+ end_date int DEFAULT 0 NOT NULL,
+ rep_opt varchar(32) NOT NULL,
+ room_id int DEFAULT 1 NOT NULL
+ REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT,
timestamp timestamp DEFAULT current_timestamp,
create_by varchar(80) NOT NULL,
modified_by varchar(80) NOT NULL,
name varchar(80) NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text,
+ rep_num_weeks smallint DEFAULT 0 NULL,
+ month_absolute smallint DEFAULT NULL,
+ month_relative varchar(4) DEFAULT NULL,
status smallint DEFAULT 0 NOT NULL,
reminded int,
info_time int,
info_user varchar(80),
info_text text,
ical_uid varchar(255) DEFAULT '' NOT NULL,
- ical_sequence smallint DEFAULT 0 NOT NULL,
- ical_recur_id varchar(16) DEFAULT '' NOT NULL
+ ical_sequence smallint DEFAULT 0 NOT NULL
);
-create index mrbs_idxStartTime on mrbs_entry(start_time);
-create index mrbs_idxEndTime on mrbs_entry(end_time);
-CREATE TABLE mrbs_repeat
+CREATE TABLE mrbs_entry
(
id serial primary key,
start_time int DEFAULT 0 NOT NULL,
end_time int DEFAULT 0 NOT NULL,
- rep_type int DEFAULT 0 NOT NULL,
- end_date int DEFAULT 0 NOT NULL,
- rep_opt varchar(32) NOT NULL,
- room_id int DEFAULT 1 NOT NULL,
+ entry_type int DEFAULT 0 NOT NULL,
+ repeat_id int DEFAULT 0 NOT NULL,
+ room_id int DEFAULT 1 NOT NULL
+ REFERENCES mrbs_room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT,
timestamp timestamp DEFAULT current_timestamp,
create_by varchar(80) NOT NULL,
modified_by varchar(80) NOT NULL,
name varchar(80) NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text,
- rep_num_weeks smallint DEFAULT 0 NULL,
- month_absolute smallint DEFAULT NULL,
- month_relative varchar(4) DEFAULT NULL,
status smallint DEFAULT 0 NOT NULL,
reminded int,
info_time int,
info_user varchar(80),
info_text text,
ical_uid varchar(255) DEFAULT '' NOT NULL,
- ical_sequence smallint DEFAULT 0 NOT NULL
+ ical_sequence smallint DEFAULT 0 NOT NULL,
+ ical_recur_id varchar(16) DEFAULT '' NOT NULL
);
+create index mrbs_idxStartTime on mrbs_entry(start_time);
+create index mrbs_idxEndTime on mrbs_entry(end_time);
CREATE TABLE mrbs_variables
(
Modified: mrbs/trunk/web/dbsys.inc
===================================================================
--- mrbs/trunk/web/dbsys.inc 2015-01-16 11:33:58 UTC (rev 2947)
+++ mrbs/trunk/web/dbsys.inc 2015-01-16 16:27:20 UTC (rev 2948)
@@ -24,7 +24,7 @@
}
-$db_schema_version = 39;
+$db_schema_version = 40;
$local_db_schema_version = 1;
// Include the abstraction configured to be used for the default MRBS
Added: mrbs/trunk/web/upgrade/40/mysql.sql
===================================================================
--- mrbs/trunk/web/upgrade/40/mysql.sql (rev 0)
+++ mrbs/trunk/web/upgrade/40/mysql.sql 2015-01-16 16:27:20 UTC (rev 2948)
@@ -0,0 +1,21 @@
+# $Id$
+
+# Add some foreign keys
+
+ALTER TABLE %DB_TBL_PREFIX%room
+ ADD FOREIGN KEY (area_id)
+ REFERENCES %DB_TBL_PREFIX%area(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT;
+
+ALTER TABLE %DB_TBL_PREFIX%repeat
+ ADD FOREIGN KEY (room_id)
+ REFERENCES %DB_TBL_PREFIX%room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT;
+
+ALTER TABLE %DB_TBL_PREFIX%entry
+ ADD FOREIGN KEY (room_id)
+ REFERENCES %DB_TBL_PREFIX%room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT;
Property changes on: mrbs/trunk/web/upgrade/40/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/trunk/web/upgrade/40/pgsql.sql
===================================================================
--- mrbs/trunk/web/upgrade/40/pgsql.sql (rev 0)
+++ mrbs/trunk/web/upgrade/40/pgsql.sql 2015-01-16 16:27:20 UTC (rev 2948)
@@ -0,0 +1,21 @@
+-- $Id$
+
+-- Add some foreign keys
+
+ALTER TABLE %DB_TBL_PREFIX%room
+ ADD FOREIGN KEY (area_id)
+ REFERENCES %DB_TBL_PREFIX%area(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT;
+
+ALTER TABLE %DB_TBL_PREFIX%repeat
+ ADD FOREIGN KEY (room_id)
+ REFERENCES %DB_TBL_PREFIX%room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT;
+
+ALTER TABLE %DB_TBL_PREFIX%entry
+ ADD FOREIGN KEY (room_id)
+ REFERENCES %DB_TBL_PREFIX%room(id)
+ ON UPDATE CASCADE
+ ON DELETE RESTRICT;
Property changes on: mrbs/trunk/web/upgrade/40/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------------------------------------------------------------------------------
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
