Reset working directory for clean Scrott implementation

1 files changed, 174 insertions, 135 deletions

diff --git a/examples/schema.sql b/examples/schema.sql
index 9770ffa..e6826cf 100644
--- a/examples/schema.sql
+++ b/examples/schema.sql
@@ -1,164 +1,203 @@
 /*
- * SCROTT Copyright (C) 2016 Malf Furious
+ * SCROTT IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
+ * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+ * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
+ * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+ * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
+ * OTHER DEALINGS IN THE SOFTWARE.
  *
- * Scrott is free software: you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published
- * by the Free Software Foundation, either version 3 of the License,
- * or (at your option) any later version.
+ * For more information, please refer to UNLICENSE
+ */
+
+/*
+ * Execute this SQL script file to create or recreate a relational
+ * database schema for use with Scrott.  This file targets MySQL (MariaDB),
+ * however may be applicable to other DBMSs as well.  If necessary,
+ * alternative versions of this file may be tracked to support those
+ * systems.
  *
- * Scrott is distributed in the hope that it will be useful, but
- * WITHOUT ANY WARRANTY; even the implied warranty of MERCHANTABILITY
- * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public
- * License for more details.
+ * Scrott's data model emulates object-oriented structures in the database
+ * schema and features a generic "object" table containing fields pertinent
+ * all object types.  Additional tables extend this for each class of
+ * objects, referencing a row in the object table by GUID.  All objects are
+ * uniquely identifiable by a GUID which is, in our case, an 8-digit
+ * hexadecimal string.
  */
 
-CREATE DATABASE IF NOT EXISTS `db_scrott`;
-USE `db_scrott`;
+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.
+ * This table describes a dictionary used for recording system settings
+ * and configuration.  These are application-wide and only writable by
+ * administrators.  This table will only hold values which have been
+ * changed and the app will use built-in defaults for settings *not*
+ * defined by this table.
  */
+DROP TABLE IF EXISTS settings;
+CREATE TABLE settings (
+    name        varchar(64)         NOT NULL,
+    value       varchar(64)     DEFAULT NULL,
 
-/* 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`)
+    PRIMARY KEY (name)
 );
 
-/* 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)      DEFAULT 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'
-    )                             NOT NULL,
-    PRIMARY KEY (`guid`)
+/*
+ * This table tracks membership.  Users may be members of groups, pads,
+ * and issues as part of the permissions system.  This table is a many-
+ * to-many relationship between users and other objects that is
+ * representative of existing memberships.
+ */
+DROP TABLE IF EXISTS members;
+CREATE TABLE members (
+    guid        varchar(8)          NOT NULL,
+    member      varchar(8)          NOT NULL,
+
+    PRIMARY KEY (guid, member)
 );
 
-/* 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`)
+/*
+ * This table records whether messages have been seen by any user.  This
+ * table is a many-to-many relationship between users and messages.
+ */
+DROP TABLE IF EXISTS views;
+CREATE TABLE views (
+    guid        varchar(8)          NOT NULL,
+    viewer      varchar(8)          NOT NULL,
+
+    PRIMARY KEY (guid, viewer)
 );
 
-/* 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`)
+/*
+ * Base table for Scrott objects
+ *
+ * Explaination of permissions...
+ * Every object has an owner, zero or more additional members, and then there is the general
+ * public.
+ *                                              OWNER       MEMBERS         PUBLIC
+ * Permissions are:                         ------------------------------------------
+ *      Access object                           GRANTED     GRANTED         config
+ *      Modify object                           GRANTED     config          DENIED
+ *      Modify members                          GRANTED     config          DENIED
+ *      Modify permissions                      GRANTED     DENIED          DENIED
+ *      Access sub-objects                      GRANTED     config          config
+ *      Create sub-objects                      GRANTED     config          config
+ *      Modify sub-objects                      GRANTED     config          DENIED
+ *      Modify sub-objects' members             GRANTED     config          DENIED
+ *      Modify sub-objects' permissions         GRANTED     DENIED          DENIED
+ *
+ * Permissions can be overridden in sub-objects, but will cascade otherwise.
+ */
+DROP TABLE IF EXISTS objects;
+CREATE TABLE objects (
+    guid        varchar(8)          NOT NULL,
+    owner       varchar(8)      DEFAULT NULL,
+    parent      varchar(8)      DEFAULT NULL,
+    name        varchar(64)         NOT NULL,
+    created     datetime            NOT NULL,
+    updated     datetime            NOT NULL,
+
+    membModify  int(1)              NOT NULL    DEFAULT 0,      /* members can modify object */
+    membMemb    int(1)              NOT NULL    DEFAULT 0,      /* members can modify members */
+    membAccs    int(1)              NOT NULL    DEFAULT 1,      /* members can access sub-objects */
+    membCres    int(1)              NOT NULL    DEFAULT 1,      /* members can create sub-objects */
+    membModifys int(1)              NOT NULL    DEFAULT 1,      /* members can modify sub-objects */
+    membMembs   int(1)              NOT NULL    DEFAULT 1,      /* members can modify sub-obj members */
+    pubAcc      int(1)              NOT NULL    DEFAULT 0,      /* public can access object */
+    pubAccs     int(1)              NOT NULL    DEFAULT 0,      /* public can access sub-objects */
+    pubCres     int(1)              NOT NULL    DEFAULT 0,      /* public can create sub-objects */
+
+    objtype     enum (
+                    'user',
+                    'group',
+                    'pad',
+                    'stage',
+                    'issue',
+                    'message',
+                    'log'
+                )                   NOT NULL,
+
+    PRIMARY KEY (guid)
 );
 
-/* User objects - special attributes */
-/* 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,
-    `admin`     int(10) unsigned  NOT NULL DEFAULT 0,
-    `email`  varchar(50)      DEFAULT NULL,
-    `emailConf` int(10) unsigned  NOT NULL DEFAULT 0,
-    `emailConfKey` varchar(64)    NOT NULL,
-    PRIMARY KEY (`guid`)
+/*
+ * Users -- extends objects
+ */
+DROP TABLE IF EXISTS users;
+CREATE TABLE users (
+    guid        varchar(8)          NOT NULL,
+    auth        varchar(64)         NOT NULL,                   /* user's salted and hashed passwd -- SHA256 */
+    salt        varchar(64)         NOT NULL,                   /* random SHA256 output, used as salt for auth */
+    alias       varchar(64)     DEFAULT NULL,
+    email       varchar(64)     DEFAULT NULL,
+    emailVer    varchar(8)          NOT NULL,
+    admin       int(1)              NOT NULL    DEFAULT 0,
+    reg         int(1)              NOT NULL    DEFAULT 0,      /* if false, user doesn't have valid credentials */
+    emailConf   int(1)              NOT NULL    DEFAULT 0,
+
+    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`)
+/*
+ * Groups -- extends objects
+ */
+DROP TABLE IF EXISTS groups;
+CREATE TABLE groups (
+    guid        varchar(8)          NOT NULL,
+
+    PRIMARY KEY (guid)
 );
 
-/* object type 'group' has no unique attributes */
+/*
+ * Pads -- extends objects
+ */
+DROP TABLE IF EXISTS pads;
+CREATE TABLE pads (
+    guid        varchar(8)          NOT NULL,
+    stage       varchar(8)      DEFAULT NULL,
+    issueNumb   int(32)             NOT NULL    DEFAULT 0,
 
-/* 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`)
+    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`)
+/*
+ * Stages -- extends objects
+ */
+DROP TABLE IF EXISTS stages;
+CREATE TABLE stages (
+    guid        varchar(8)          NOT NULL,
+    stage       varchar(8)      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,
-    `due`      datetime       DEFAULT NULL,
-    `tags`     text           DEFAULT NULL,
-    PRIMARY KEY (`guid`)
+/*
+ * Issues -- extends objects
+ */
+DROP TABLE IF EXISTS issues;
+CREATE TABLE issues (
+    guid        varchar(8)          NOT NULL,
+    numb        int(32)             NOT NULL,
+    assignee    varchar(8)      DEFAULT NULL,
+    seen        int(1)              NOT NULL    DEFAULT 0,      /* has the assignee seen this yet? */
+    description text            DEFAULT NULL,
+    due         datetime        DEFAULT NULL,
+    tags        varchar(64)     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`)
+/*
+ * Messages / logs -- extends objects
+ */
+DROP TABLE IF EXISTS mesgs;
+CREATE TABLE mesgs (
+    guid        varchar(8)          NOT NULL,
+    author      varchar(8)          NOT NULL,
+    mesg        text            DEFAULT NULL,
+    attachName  varchar(64)     DEFAULT NULL,
+    attachMime  varchar(64)     DEFAULT NULL,
+
+    PRIMARY KEY (guid)
 );