From b9bdf474f838d1435dc0cba1f6ffa9e1e26520b6 Mon Sep 17 00:00:00 2001 From: Malf Furious <m@lfurio.us> Date: Thu, 20 Sep 2018 19:05:56 -0400 Subject: 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. --- srvs/mysql.sql | 202 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 202 insertions(+) create mode 100644 srvs/mysql.sql (limited to 'srvs/mysql.sql') 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) +); -- cgit v1.2.3