From b9bdf474f838d1435dc0cba1f6ffa9e1e26520b6 Mon Sep 17 00:00:00 2001 From: Malf Furious 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. --- schema.sql | 202 --------------------------------------------------------- srvs/mysql.sql | 202 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 202 insertions(+), 202 deletions(-) delete mode 100644 schema.sql create mode 100644 srvs/mysql.sql diff --git a/schema.sql b/schema.sql deleted file mode 100644 index 13db8c7..0000000 --- a/schema.sql +++ /dev/null @@ -1,202 +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) 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) -); 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