/* * 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) );