From dae3964e7682dcd0d64075dfc28a23c12ef6c52e Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Sat, 14 Jan 2017 02:26:28 -0500 Subject: Reset working directory for clean Scrott implementation --- schema.sql | 203 ------------------------------------------------------------- 1 file changed, 203 deletions(-) delete mode 100644 schema.sql (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql deleted file mode 100644 index e6826cf..0000000 --- a/schema.sql +++ /dev/null @@ -1,203 +0,0 @@ -/* - * 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 - * - * 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) -); - -/* - * 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? */ - description 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) -); -- cgit v1.2.3