diff options
Diffstat (limited to 'examples/schema.sql')
| -rw-r--r-- | examples/schema.sql | 164 |
1 files changed, 164 insertions, 0 deletions
diff --git a/examples/schema.sql b/examples/schema.sql new file mode 100644 index 0000000..9770ffa --- /dev/null +++ b/examples/schema.sql @@ -0,0 +1,164 @@ +/* + * SCROTT Copyright (C) 2016 Malf Furious + * + * Scrott is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published + * by the Free Software Foundation, either version 3 of the License, + * or (at your option) any later version. + * + * Scrott is distributed in the hope that it will be useful, but + * WITHOUT ANY WARRANTY; even the implied warranty of MERCHANTABILITY + * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public + * License for more details. + */ + +CREATE DATABASE IF NOT EXISTS `db_scrott`; +USE `db_scrott`; + +/* + * Description of SCROTT object permissions system + * ----------------------------------------------- + * Every object has an owner, zero or more additional members, and then there is the general + * public. + * + * OWNER = user OR group + * MEMBER = user + * OWNER MEMBERS OTHERS + * Permissions are: ------------------------------------------------ + * 1. Access object GRANTED GRANTED MB #6 + * 2. Modify object GRANTED MB #0 DENIED + * 3. Modify members GRANTED MB #1 DENIED + * 4. Modify permissions GRANTED DENIED DENIED + * 5. Access sub-objects GRANTED MB #2 MB #7 + * 6. Create sub-objects GRANTED MB #3 MB #8 + * 7. Modify sub-objects GRANTED MB #4 DENIED + * 8. Modify sub-objects' members GRANTED MB #5 DENIED + * 9. Modify sub-objects' permissions GRANTED DENIED DENIED + * + * 012345678 + * EG: 001111000 = 120 --implies--> that members may access,create,and + * fully modify sub-objects. All other + * options are DENIED. This mask value + * of 120 is a recommended starting point. + * + * Permissions can be overridden down-stream, but permissions will cascade otherwise. + */ + +/* System-wide admin settings */ +DROP TABLE IF EXISTS `setting`; +CREATE TABLE `setting` ( + `key` varchar(50) NOT NULL, + `value` varchar(50) DEFAULT NULL, + PRIMARY KEY (`key`) +); + +/* Common object structure attributes */ +DROP TABLE IF EXISTS `object`; +CREATE TABLE `object` ( + `guid` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL DEFAULT 0, + `owner` varchar(10) DEFAULT NULL, + `parent` varchar(10) DEFAULT NULL, + `name` varchar(50) NOT NULL, + `timeCreated` datetime NOT NULL, + `timeUpdated` datetime NOT NULL, + `type` enum( + 'user', + 'extern-user', + 'group', + 'pad', + 'stage', + 'issue', + 'message', + 'log' + ) NOT NULL, + PRIMARY KEY (`guid`) +); + +/* Scheme for associating objects with members */ +DROP TABLE IF EXISTS `obj_member`; +CREATE TABLE `obj_member` ( + `guid` varchar(10) NOT NULL, + `member` varchar(10) NOT NULL, + PRIMARY KEY (`guid`,`member`) +); + +/* Scheme for logging if a message has been seen by a user on a wide scale */ +DROP TABLE IF EXISTS `msg_read`; +CREATE TABLE `msg_read` ( + `guid` varchar(10) NOT NULL, + `user` varchar(10) NOT NULL, + PRIMARY KEY (`guid`,`user`) +); + +/* User objects - special attributes */ +/* Notice: 'key' field is the user's hashed and salted password -- SHA256 */ +/* Notice: 'salt' is a random SHA256 output, used as salt for user's password */ +DROP TABLE IF EXISTS `user`; +CREATE TABLE `user` ( + `guid` varchar(10) NOT NULL, + `key` varchar(64) NOT NULL, + `salt` varchar(64) NOT NULL, + `alias` varchar(50) DEFAULT NULL, + `admin` int(10) unsigned NOT NULL DEFAULT 0, + `email` varchar(50) DEFAULT NULL, + `emailConf` int(10) unsigned NOT NULL DEFAULT 0, + `emailConfKey` varchar(64) NOT NULL, + PRIMARY KEY (`guid`) +); + +/* Extern-user objects - special attributes */ +/* Notice: these objects are contructred on portal pages or if a scrott user gives ownership of an object to an external user. */ +/* Notice: This table should be searched for addresses while creating a new normal user object. If in the event the users email address exists here, it should be promoted to a full user account */ +DROP TABLE IF EXISTS `extern-user`; +CREATE TABLE `extern-user` ( + `guid` varchar(10) NOT NULL, + `email` varchar(50) NOT NULL, + `emailConfKey` varchar(64) NOT NULL, + PRIMARY KEY (`guid`) +); + +/* object type 'group' has no unique attributes */ + +/* Pad objects - special attributes */ +DROP TABLE IF EXISTS `pad`; +CREATE TABLE `pad` ( + `guid` varchar(10) NOT NULL, + `stage` varchar(10) DEFAULT NULL, + `nextIssueNumber` int(10) NOT NULL DEFAULT 0, + PRIMARY KEY (`guid`) +); + +/* Stage objects - special attributes */ +DROP TABLE IF EXISTS `stage`; +CREATE TABLE `stage` ( + `guid` varchar(10) NOT NULL, + `stage` varchar(10) DEFAULT NULL, + PRIMARY KEY (`guid`) +); + +/* Issue objects - special attributes */ +/* Notice: 'closed' issues should be denoted by being a child of the pad directly, and not the child of a pad's stages */ +/* Notice: an issue is 'unread' if the assignee has yet to review the issue. Self assigned issues, by definition, cannot be in unread state. */ +DROP TABLE IF EXISTS `issue`; +CREATE TABLE `issue` ( + `guid` varchar(10) NOT NULL, + `number` int(10) unsigned NOT NULL, + `assignee` varchar(10) DEFAULT NULL, + `unread` int(10) unsigned NOT NULL DEFAULT 1, + `desc` text DEFAULT NULL, + `due` datetime DEFAULT NULL, + `tags` text DEFAULT NULL, + PRIMARY KEY (`guid`) +); + +/* Message objects - special attributes */ +/* Notice: These are discussion messages, comments on issues, and also private messages sent directly to users */ +/* Notice: System log enteries use this struct to store data as well */ +DROP TABLE IF EXISTS `message`; +CREATE TABLE `message` ( + `guid` varchar(10) NOT NULL, + `author` varchar(10) NOT NULL, + `message` text DEFAULT NULL, + PRIMARY KEY (`guid`) +); |