Revision: 3001
https://sourceforge.net/p/mrbs/code/3001/
Author: cimorrison
Date: 2015-02-03 15:41:40 +0000 (Tue, 03 Feb 2015)
Log Message:
-----------
Added some UNIQUE constraints to the tables
Modified Paths:
--------------
mrbs/trunk/tables.my.sql
mrbs/trunk/tables.pg.sql
mrbs/trunk/web/dbsys.inc
Added Paths:
-----------
mrbs/trunk/web/upgrade/44/
mrbs/trunk/web/upgrade/44/mysql.sql
mrbs/trunk/web/upgrade/44/pgsql.sql
Modified: mrbs/trunk/tables.my.sql
===================================================================
--- mrbs/trunk/tables.my.sql 2015-01-29 18:05:06 UTC (rev 3000)
+++ mrbs/trunk/tables.my.sql 2015-02-03 15:41:40 UTC (rev 3001)
@@ -63,7 +63,8 @@
confirmation_enabled tinyint(1),
confirmed_default tinyint(1),
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE KEY uq_area_name (area_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_room
@@ -83,6 +84,7 @@
REFERENCES mrbs_area(id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
+ UNIQUE KEY uq_room_name (area_id, room_name),
KEY idxSortKey (sort_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -161,7 +163,8 @@
variable_name varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci,
variable_content text CHARACTER SET utf8 COLLATE utf8_general_ci,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE KEY uq_variable_name (variable_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_zoneinfo
@@ -172,7 +175,8 @@
vtimezone text CHARACTER SET utf8 COLLATE utf8_general_ci,
last_updated int NOT NULL DEFAULT 0,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE KEY uq_timezone (timezone, outlook_compatible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_users
@@ -184,10 +188,11 @@
password varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci,
email varchar(75) CHARACTER SET utf8 COLLATE utf8_general_ci,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE KEY uq_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ( 'db_version', '43');
+ VALUES ( 'db_version', '44');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');
Modified: mrbs/trunk/tables.pg.sql
===================================================================
--- mrbs/trunk/tables.pg.sql 2015-01-29 18:05:06 UTC (rev 3000)
+++ mrbs/trunk/tables.pg.sql 2015-02-03 15:41:40 UTC (rev 3001)
@@ -65,7 +65,9 @@
reminders_enabled smallint,
enable_periods smallint,
confirmation_enabled smallint,
- confirmed_default smallint
+ confirmed_default smallint,
+
+ CONSTRAINT mrbs_uq_area_name UNIQUE (area_name)
);
CREATE TABLE mrbs_room
@@ -81,7 +83,9 @@
description varchar(60),
capacity int DEFAULT 0 NOT NULL,
room_admin_email text,
- custom_html text
+ custom_html text,
+
+ CONSTRAINT mrbs_uq_room_name UNIQUE (area_id, room_name)
);
create index mrbs_idxSortKey on mrbs_room(sort_key);
@@ -151,7 +155,9 @@
(
id serial primary key,
variable_name varchar(80),
- variable_content text
+ variable_content text,
+
+ CONSTRAINT mrbs_uq_variable_name UNIQUE (variable_name)
);
CREATE TABLE mrbs_zoneinfo
@@ -160,7 +166,9 @@
timezone varchar(255) DEFAULT '' NOT NULL,
outlook_compatible smallint NOT NULL DEFAULT 0,
vtimezone text,
- last_updated int NOT NULL DEFAULT 0
+ last_updated int NOT NULL DEFAULT 0,
+
+ CONSTRAINT mrbs_uq_timezone UNIQUE (timezone, outlook_compatible)
);
CREATE TABLE mrbs_users
@@ -170,10 +178,12 @@
level smallint DEFAULT '0' NOT NULL, /* play safe and give no rights */
name varchar(30),
password varchar(40),
- email varchar(75)
+ email varchar(75),
+
+ CONSTRAINT mrbs_uq_name UNIQUE (name)
);
INSERT INTO mrbs_variables (variable_name, variable_content)
- VALUES ('db_version', '42');
+ VALUES ('db_version', '44');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ('local_db_version', '1');
Modified: mrbs/trunk/web/dbsys.inc
===================================================================
--- mrbs/trunk/web/dbsys.inc 2015-01-29 18:05:06 UTC (rev 3000)
+++ mrbs/trunk/web/dbsys.inc 2015-02-03 15:41:40 UTC (rev 3001)
@@ -24,7 +24,7 @@
}
-$db_schema_version = 43;
+$db_schema_version = 44;
$local_db_schema_version = 1;
// Include the abstraction configured to be used for the default MRBS
Added: mrbs/trunk/web/upgrade/44/mysql.sql
===================================================================
--- mrbs/trunk/web/upgrade/44/mysql.sql (rev 0)
+++ mrbs/trunk/web/upgrade/44/mysql.sql 2015-02-03 15:41:40 UTC (rev 3001)
@@ -0,0 +1,18 @@
+-- $Id$
+
+-- Add some UNIQUE constraints
+
+ALTER TABLE %DB_TBL_PREFIX%room
+ ADD UNIQUE INDEX uq_room_name (area_id, room_name);
+
+ALTER TABLE %DB_TBL_PREFIX%area
+ ADD UNIQUE INDEX uq_area_name (area_name);
+
+ALTER TABLE %DB_TBL_PREFIX%users
+ ADD UNIQUE INDEX uq_name (name);
+
+ALTER TABLE %DB_TBL_PREFIX%variables
+ ADD UNIQUE INDEX uq_variable_name (variable_name);
+
+ALTER TABLE %DB_TBL_PREFIX%zoneinfo
+ ADD UNIQUE INDEX uq_timezone (timezone, outlook_compatible);
Property changes on: mrbs/trunk/web/upgrade/44/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/44/pgsql.sql
===================================================================
--- mrbs/trunk/web/upgrade/44/pgsql.sql (rev 0)
+++ mrbs/trunk/web/upgrade/44/pgsql.sql 2015-02-03 15:41:40 UTC (rev 3001)
@@ -0,0 +1,18 @@
+-- $Id$
+
+-- Add some UNIQUE constraints
+
+ALTER TABLE %DB_TBL_PREFIX%room
+ ADD CONSTRAINT %DB_TBL_PREFIX%uq_room_name UNIQUE (area_id, room_name);
+
+ALTER TABLE %DB_TBL_PREFIX%area
+ ADD CONSTRAINT %DB_TBL_PREFIX%uq_area_name UNIQUE (area_name);
+
+ALTER TABLE %DB_TBL_PREFIX%users
+ ADD CONSTRAINT %DB_TBL_PREFIX%uq_name UNIQUE (name);
+
+ALTER TABLE %DB_TBL_PREFIX%variables
+ ADD CONSTRAINT %DB_TBL_PREFIX%uq_variable_name UNIQUE (variable_name);
+
+ALTER TABLE %DB_TBL_PREFIX%zoneinfo
+ ADD CONSTRAINT %DB_TBL_PREFIX%uq_timezone UNIQUE (timezone,
outlook_compatible);
Property changes on: mrbs/trunk/web/upgrade/44/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------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
