Merge branch 'datastructures' into dev

1 files changed, 156 insertions, 0 deletions

diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..add1472
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,156 @@
+/* SCROTT - Core System Data Structures
+ * ------------------------------------
+ *   SQL Database Schema for backend
+ */
+
+CREATE DATABASE IF NOT EXISTS `db_scrott`;
+USE `db_scrott`;
+
+/* Description of SCROTT object permissions system
+ * -----------------------------------------------
+ * Every object has an owner, zero or more additional members, and then there is the general
+ * public.
+ *
+ * OWNER  = user OR group
+ * MEMBER = user
+ *                                                 OWNER        MEMBERS           OTHERS
+ * Permissions are:                            ------------------------------------------------
+ *  1.  Access object                             GRANTED       GRANTED           MB #6
+ *  2.  Modify object                             GRANTED        MB #0           DENIED
+ *  3.  Modify members                            GRANTED        MB #1           DENIED
+ *  4.  Modify permissions                        GRANTED       DENIED           DENIED
+ *  5.  Access sub-objects                        GRANTED        MB #2            MB #7
+ *  6.  Create sub-objects                        GRANTED        MB #3            MB #8
+ *  7.  Modify sub-objects                        GRANTED        MB #4           DENIED
+ *  8.  Modify sub-objects' members               GRANTED        MB #5           DENIED
+ *  9.  Modify sub-objects' permissions           GRANTED       DENIED           DENIED
+ *
+ *                                012345678
+ *                            EG: 001111000 = 120 --implies--> that members may access,create,and
+ *                                                             fully modify sub-objects. All other
+ *                                                             options are DENIED.  This mask value
+ *                                                             of 120 is a recommended starting point.
+ *
+ * Permissions can be overridden down-stream, but permissions will cascade otherwise.
+ */
+
+/* System-wide admin settings */
+DROP TABLE IF EXISTS `setting`;
+CREATE TABLE `setting` (
+    `key`    varchar(50)          NOT NULL,
+    `value`  varchar(50)      DEFAULT NULL,
+    PRIMARY KEY (`key`)
+);
+
+/* Common object structure attributes */
+DROP TABLE IF EXISTS `object`;
+CREATE TABLE `object` (
+    `guid`   varchar(10)          NOT NULL,
+    `perms`  int(10)     unsigned NOT NULL DEFAULT 0,
+    `owner`  varchar(10)          NOT NULL,
+    `parent` varchar(10)      DEFAULT NULL,
+    `name`   varchar(50)          NOT NULL,
+    `timeCreated` datetime        NOT NULL,
+    `timeUpdated` datetime        NOT NULL,
+    `type`   enum(
+        'user',
+        'extern-user',
+        'group',
+        'pad',
+        'stage',
+        'issue',
+        'message',
+        'log-user',
+        'log-group',
+        'log-admin',
+        'log-pad',
+        'log-issue'
+    )                             NOT NULL,
+    PRIMARY KEY (`guid`)
+);
+
+/* Scheme for associating objects with members */
+DROP TABLE IF EXISTS `obj_member`;
+CREATE TABLE `obj_member` (
+    `guid`   varchar(10)          NOT NULL,
+    `member` varchar(10)          NOT NULL,
+    PRIMARY KEY (`guid`,`member`)
+);
+
+/* Scheme for logging if a message has been seen by a user on a wide scale */
+DROP TABLE IF EXISTS `msg_read`;
+CREATE TABLE `msg_read` (
+    `guid`   varchar(10)          NOT NULL,
+    `user`   varchar(10)          NOT NULL,
+    PRIMARY KEY (`guid`,`user`)
+);
+
+/* User objects - special attributes */
+/* Notice: 'timeUpdated' field should be used to store the time of the start of the user's current session */
+/* Notice: 'key' field is the user's hashed and salted password -- SHA256 */
+/* Notice: 'salt' is a random SHA256 output, used as salt for user's password */
+DROP TABLE IF EXISTS `user`;
+CREATE TABLE `user` (
+    `guid`   varchar(10)          NOT NULL,
+    `key`    varchar(64)          NOT NULL,
+    `salt`   varchar(64)          NOT NULL,
+    `alias`  varchar(50)      DEFAULT NULL,
+    `email`  varchar(50)      DEFAULT NULL,
+    `emailConf` int(10) unsigned  NOT NULL DEFAULT 0,
+    `emailConfKey` varchar(64)    NOT NULL,
+    PRIMARY KEY (`guid`)
+);
+
+/* Extern-user objects - special attributes */
+/* Notice: these objects are contructred on portal pages or if a scrott user gives ownership of an object to an external user. */
+/* Notice: This table should be searched for addresses while creating a new normal user object.  If in the event the users email address exists here, it should be promoted to a full user account */
+DROP TABLE IF EXISTS `extern-user`;
+CREATE TABLE `extern-user` (
+    `guid`   varchar(10)          NOT NULL,
+    `email`  varchar(50)          NOT NULL,
+    `emailConfKey` varchar(64)    NOT NULL,
+    PRIMARY KEY (`guid`)
+);
+
+/* object type 'group' has no unique attributes */
+
+/* Pad objects - special attributes */
+DROP TABLE IF EXISTS `pad`;
+CREATE TABLE `pad` (
+    `guid`   varchar(10)          NOT NULL,
+    `stage`  varchar(10)      DEFAULT NULL,
+    `nextIssueNumber` int(10)     NOT NULL DEFAULT 0,
+    PRIMARY KEY (`guid`)
+);
+
+/* Stage objects - special attributes */
+DROP TABLE IF EXISTS `stage`;
+CREATE TABLE `stage` (
+    `guid`   varchar(10)          NOT NULL,
+    `stage`  varchar(10)      DEFAULT NULL,
+    PRIMARY KEY (`guid`)
+);
+
+/* Issue objects - special attributes */
+/* Notice: 'closed' issues should be denoted by being a child of the pad directly, and not the child of a pad's stages */
+/* Notice: an issue is 'unread' if the assignee has yet to review the issue.  Self assigned issues, by definition, cannot be in unread state. */
+DROP TABLE IF EXISTS `issue`;
+CREATE TABLE `issue` (
+    `guid`     varchar(10)        NOT NULL,
+    `number`   int(10) unsigned   NOT NULL,
+    `assignee` varchar(10)    DEFAULT NULL,
+    `unread`   int(10) unsigned   NOT NULL DEFAULT 1,
+    `desc`     text           DEFAULT NULL,
+    PRIMARY KEY (`guid`)
+);
+
+/* Message objects - special attributes */
+/* Notice: These are discussion messages, comments on issues, and also private messages sent directly to users */
+/* Notice: System log enteries use this struct to store data as well */
+DROP TABLE IF EXISTS `message`;
+CREATE TABLE `message` (
+    `guid`   varchar(10)          NOT NULL,
+    `author` varchar(10)          NOT NULL,
+    `message` text            DEFAULT NULL,
+    PRIMARY KEY (`guid`)
+);