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