Rework database schema

Biggest changes include:
  better comments
  merged user and extern-user tables
  separate fields for different permissions (not a bitmask)
  added fields to mesgs table for handling attachments
  renamed some fields to avoid using SQL reserved words

1 files changed, 188 insertions, 0 deletions

diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..412c346
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,188 @@
+/*
+ * 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.
+ *
+ * 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'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;
+
+/*
+ * 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,
+
+    PRIMARY KEY (name)
+);
+
+/*
+ * 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)
+);
+
+/*
+ * 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)
+);
+
+/*
+ * Base table for Scrott objects
+ *
+ * TODO -- explain permissions and set defaults below...
+ */
+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 0,      /* members can access sub-objects */
+    membCres    int(1)              NOT NULL    DEFAULT 0,      /* members can create sub-objects */
+    membModifys int(1)              NOT NULL    DEFAULT 0,      /* members can modify sub-objects */
+    membMembs   int(1)              NOT NULL    DEFAULT 0,      /* 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)
+);
+
+/*
+ * 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)
+);
+
+/*
+ * Groups -- extends objects
+ */
+DROP TABLE IF EXISTS groups;
+CREATE TABLE groups (
+    guid        varchar(8)          NOT NULL,
+
+    PRIMARY KEY (guid)
+);
+
+/*
+ * 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,
+
+    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)
+);
+
+/*
+ * 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? */
+    desc        text            DEFAULT NULL,
+    due         datetime        DEFAULT NULL,
+    tags        varchar(64)     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)
+);