mysql: Move schema file into srvs/

This places all (both) of the service configuration files together and
cleans up the top-level directory.  The file is renamed to 'mysql.sql'
to indicate the particular service it corresponds to.

1 files changed, 202 insertions, 0 deletions

diff --git a/srvs/mysql.sql b/srvs/mysql.sql
new file mode 100644
index 0000000..13db8c7
--- /dev/null
+++ b/srvs/mysql.sql
@@ -0,0 +1,202 @@
+/*
+ * 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)         NOT NULL    DEFAULT '',
+
+    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
+ *
+ * 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)          NOT NULL    DEFAULT '',
+    parent      varchar(8)          NOT NULL    DEFAULT '',
+    name        varchar(64)         NOT NULL    DEFAULT '',
+    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',
+                    'mesg',
+                    '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)         NOT NULL    DEFAULT '',
+    email       varchar(64)         NOT NULL    DEFAULT '',
+    emailVer    varchar(8)          NOT NULL    DEFAULT '',
+    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)          NOT NULL    DEFAULT '',
+    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)          NOT NULL    DEFAULT '',
+
+    PRIMARY KEY (guid)
+);
+
+/*
+ * Issues -- extends objects
+ */
+DROP TABLE IF EXISTS issues;
+CREATE TABLE issues (
+    guid        varchar(8)          NOT NULL,
+    numb        int(32)             NOT NULL    DEFAULT 0,
+    assignee    varchar(8)          NOT NULL    DEFAULT '',
+    seen        int(1)              NOT NULL    DEFAULT 0,      /* has the assignee seen this yet? */
+    description text                NOT NULL,
+    due         varchar(64)         NOT NULL    DEFAULT '',
+    tags        text                NOT 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    DEFAULT '',
+    mesg        text                NOT NULL,
+    attachment  varchar(64)         NOT NULL    DEFAULT '',
+
+    PRIMARY KEY (guid)
+);