From 890edc0f3f847ec2ef75bc260aff60a11f4cbeb9 Mon Sep 17 00:00:00 2001 From: M Date: Wed, 18 Nov 2015 13:15:45 -0500 Subject: + Defined schema for backend users table ! Devised an initial concept for Scrott object permissions system --- schema.sql | 63 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 63 insertions(+) create mode 100644 schema.sql diff --git a/schema.sql b/schema.sql new file mode 100644 index 0000000..d586c35 --- /dev/null +++ b/schema.sql @@ -0,0 +1,63 @@ +/* SCROTT - Core System Data Structures + * ------------------------------------ + * SQL Database Schema for backend + */ + +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. + */ + +DROP TABLE IF EXISTS `user`; +CREATE TABLE `user` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL, + `name` varchar(50) NOT NULL, + `alias` varchar(50) DEFAULT NULL, + `key` varchar(64) NOT NULL, + `salt` varchar(64) NOT NULL, + + PRIMARY KEY (`id`) +); + + + + + + +DROP TABLE IF EXISTS `group`; +CREATE TABLE `group` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `name` varchar(50) NOT NULL, + /* TODO -- wip */ + /* TODO -- remember members table */ -- cgit v1.2.3 From c94008be7dad72d4fee27aa0e39d5c286d22c292 Mon Sep 17 00:00:00 2001 From: M Date: Wed, 18 Nov 2015 21:49:48 -0500 Subject: + Defined membership association table --- schema.sql | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/schema.sql b/schema.sql index d586c35..f29948f 100644 --- a/schema.sql +++ b/schema.sql @@ -34,6 +34,14 @@ USE `db_scrott`; * Permissions can be overridden down-stream, but permissions will cascade otherwise. */ +DROP TABLE IF EXISTS `member`; +CREATE TABLE `member` ( + `object` varchar(10) NOT NULL, + `member` varchar(10) NOT NULL, + + PRIMARY KEY (`object`,`member`) +); + DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, @@ -60,4 +68,3 @@ CREATE TABLE `group` ( `owner` varchar(10) NOT NULL, `name` varchar(50) NOT NULL, /* TODO -- wip */ - /* TODO -- remember members table */ -- cgit v1.2.3 From 359c276b3cd853f0f9688dc5c3d5806be43e9b0b Mon Sep 17 00:00:00 2001 From: M Date: Wed, 18 Nov 2015 22:04:33 -0500 Subject: + Defined group table --- schema.sql | 18 ++++++++---------- 1 file changed, 8 insertions(+), 10 deletions(-) diff --git a/schema.sql b/schema.sql index f29948f..78ad375 100644 --- a/schema.sql +++ b/schema.sql @@ -56,15 +56,13 @@ CREATE TABLE `user` ( PRIMARY KEY (`id`) ); - - - - - DROP TABLE IF EXISTS `group`; CREATE TABLE `group` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `name` varchar(50) NOT NULL, - /* TODO -- wip */ + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL, + `name` varchar(50) NOT NULL, + + PRIMARY KEY (`id`) +); -- cgit v1.2.3 From 8b0f72d095fc5cb894c6ed19612d98fb03224d9f Mon Sep 17 00:00:00 2001 From: M Date: Wed, 18 Nov 2015 23:48:35 -0500 Subject: * Draft definitions for pad and stage schemas --- schema.sql | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) diff --git a/schema.sql b/schema.sql index 78ad375..26f7d8e 100644 --- a/schema.sql +++ b/schema.sql @@ -66,3 +66,27 @@ CREATE TABLE `group` ( PRIMARY KEY (`id`) ); + +DROP TABLE IF EXISTS `pad`; +CREATE TABLE `pad` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL, + `name` varchar(50) NOT NULL, + + PRIMARY KEY (`id`) +); + +DROP TABLE IF EXISTS `stage`; +CREATE TABLE `stage` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL, + `name` varchar(50) NOT NULL, + `parent` varchar(10) NOT NULL, + /* TODO -- wip */ + + PRIMARY KEY (`id`) +); -- cgit v1.2.3 From 6c660e97ebc4b3f9395c3d8489accb8fce103572 Mon Sep 17 00:00:00 2001 From: M Date: Thu, 19 Nov 2015 19:57:11 -0500 Subject: * Reworked core datamodel -- now using a common main table for holding ALL objects (and their common attributes). Additional, supplemental tables may be employed to maintain data attributes which are specific to a given object type --- schema.sql | 54 ++++++++++++++++++++++++++++++------------------------ 1 file changed, 30 insertions(+), 24 deletions(-) diff --git a/schema.sql b/schema.sql index 26f7d8e..c6a6370 100644 --- a/schema.sql +++ b/schema.sql @@ -34,38 +34,44 @@ USE `db_scrott`; * Permissions can be overridden down-stream, but permissions will cascade otherwise. */ -DROP TABLE IF EXISTS `member`; -CREATE TABLE `member` ( - `object` varchar(10) NOT NULL, - `member` varchar(10) NOT NULL, +/* 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) NOT NULL, + `parent` varchar(10) DEFAULT NULL, + `name` varchar(50) NOT NULL, + `type` enum( + 'user', + 'group' + ) NOT NULL, + PRIMARY KEY (`guid`) +); - PRIMARY KEY (`object`,`member`) +/* 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`) ); +/* User objects - special attributes */ DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `perms` int(10) unsigned NOT NULL, - `name` varchar(50) NOT NULL, - `alias` varchar(50) DEFAULT NULL, - `key` varchar(64) NOT NULL, - `salt` varchar(64) NOT NULL, - - PRIMARY KEY (`id`) + `guid` varchar(10) NOT NULL, + `key` varchar(64) NOT NULL, + `salt` varchar(64) NOT NULL, + `alias` varchar(50) DEFAULT NULL, + PRIMARY KEY (`guid`) ); -DROP TABLE IF EXISTS `group`; -CREATE TABLE `group` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `perms` int(10) unsigned NOT NULL, - `name` varchar(50) NOT NULL, +/* object type 'group' has no unique attributes */ - PRIMARY KEY (`id`) -); + + +/* TODO -- WIP FROM HERE */ DROP TABLE IF EXISTS `pad`; CREATE TABLE `pad` ( -- cgit v1.2.3 From 4cea4b5494c6660d03614d8ea59214f1ad924de3 Mon Sep 17 00:00:00 2001 From: M Date: Thu, 19 Nov 2015 20:35:32 -0500 Subject: * Refactored pad table into new core data model --- schema.sql | 22 ++++++++++------------ 1 file changed, 10 insertions(+), 12 deletions(-) diff --git a/schema.sql b/schema.sql index c6a6370..7bd2bf0 100644 --- a/schema.sql +++ b/schema.sql @@ -44,7 +44,8 @@ CREATE TABLE `object` ( `name` varchar(50) NOT NULL, `type` enum( 'user', - 'group' + 'group', + 'pad' ) NOT NULL, PRIMARY KEY (`guid`) ); @@ -69,21 +70,18 @@ CREATE TABLE `user` ( /* object type 'group' has no unique attributes */ - - -/* TODO -- WIP FROM HERE */ - +/* Pad objects - special attributes */ DROP TABLE IF EXISTS `pad`; CREATE TABLE `pad` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `perms` int(10) unsigned NOT NULL, - `name` varchar(50) NOT NULL, - - PRIMARY KEY (`id`) + `guid` varchar(10) NOT NULL, + `stage` varchar(10) NOT NULL, + PRIMARY KEY (`guid`) ); + + +/* TODO -- WIP FROM HERE */ + DROP TABLE IF EXISTS `stage`; CREATE TABLE `stage` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -- cgit v1.2.3 From 7ed08522b6b93f39c8f7ca4074e0cd66bc28d2f5 Mon Sep 17 00:00:00 2001 From: M Date: Thu, 19 Nov 2015 21:08:33 -0500 Subject: * Refactored stage table into new data model --- schema.sql | 22 +++++++--------------- 1 file changed, 7 insertions(+), 15 deletions(-) diff --git a/schema.sql b/schema.sql index 7bd2bf0..0867a75 100644 --- a/schema.sql +++ b/schema.sql @@ -45,7 +45,8 @@ CREATE TABLE `object` ( `type` enum( 'user', 'group', - 'pad' + 'pad', + 'stage' ) NOT NULL, PRIMARY KEY (`guid`) ); @@ -74,23 +75,14 @@ CREATE TABLE `user` ( DROP TABLE IF EXISTS `pad`; CREATE TABLE `pad` ( `guid` varchar(10) NOT NULL, - `stage` varchar(10) NOT NULL, + `stage` varchar(10) DEFAULT NULL, PRIMARY KEY (`guid`) ); - - -/* TODO -- WIP FROM HERE */ - +/* Stage objects - special attributes */ DROP TABLE IF EXISTS `stage`; CREATE TABLE `stage` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `perms` int(10) unsigned NOT NULL, - `name` varchar(50) NOT NULL, - `parent` varchar(10) NOT NULL, - /* TODO -- wip */ - - PRIMARY KEY (`id`) + `guid` varchar(10) NOT NULL, + `stage` varchar(10) DEFAULT NULL, + PRIMARY KEY (`guid`) ); -- cgit v1.2.3 From b1c0e57c8a1ccb9074cc5eecc64464aaece26973 Mon Sep 17 00:00:00 2001 From: M Date: Thu, 19 Nov 2015 21:54:20 -0500 Subject: + Defined initial issue object in schema --- schema.sql | 17 ++++++++++++++++- 1 file changed, 16 insertions(+), 1 deletion(-) diff --git a/schema.sql b/schema.sql index 0867a75..2863cbc 100644 --- a/schema.sql +++ b/schema.sql @@ -46,7 +46,8 @@ CREATE TABLE `object` ( 'user', 'group', 'pad', - 'stage' + 'stage', + 'issue' ) NOT NULL, PRIMARY KEY (`guid`) ); @@ -76,6 +77,7 @@ 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`) ); @@ -86,3 +88,16 @@ CREATE TABLE `stage` ( `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, + PRIMARY KEY (`guid`) +); -- cgit v1.2.3 From fbb58c515142d55de72ae61b504596bea97d0f67 Mon Sep 17 00:00:00 2001 From: M Date: Thu, 19 Nov 2015 22:33:02 -0500 Subject: * Data schema errata --- schema.sql | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/schema.sql b/schema.sql index 2863cbc..589fafb 100644 --- a/schema.sql +++ b/schema.sql @@ -42,6 +42,8 @@ CREATE TABLE `object` ( `owner` varchar(10) NOT NULL, `parent` varchar(10) DEFAULT NULL, `name` varchar(50) NOT NULL, + `timeCreated` datetime NOT NULL, + `timeUpdated` datetime NOT NULL, `type` enum( 'user', 'group', @@ -61,12 +63,17 @@ CREATE TABLE `obj_member` ( ); /* User objects - special attributes */ +/* Notice: 'timeUpdated' field should be used to store the time of the start of the user's current session */ +/* 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, + `email` varchar(50) DEFAULT NULL, + `emailConf` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`guid`) ); -- cgit v1.2.3 From dfb9f8868efab7ccc5440c537c12424e72af3a4a Mon Sep 17 00:00:00 2001 From: M Date: Fri, 20 Nov 2015 19:32:37 -0500 Subject: + Defined schema for extern-user object --- schema.sql | 13 +++++++++++++ 1 file changed, 13 insertions(+) diff --git a/schema.sql b/schema.sql index 589fafb..58cbc45 100644 --- a/schema.sql +++ b/schema.sql @@ -46,6 +46,7 @@ CREATE TABLE `object` ( `timeUpdated` datetime NOT NULL, `type` enum( 'user', + 'extern-user', 'group', 'pad', 'stage', @@ -74,6 +75,18 @@ CREATE TABLE `user` ( `alias` varchar(50) DEFAULT NULL, `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`) ); -- cgit v1.2.3 From 6819f27ab74011ff13bdd4f156a09a4372269d34 Mon Sep 17 00:00:00 2001 From: M Date: Fri, 20 Nov 2015 21:34:29 -0500 Subject: + Defined schema for message table --- schema.sql | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) diff --git a/schema.sql b/schema.sql index 58cbc45..fd37b18 100644 --- a/schema.sql +++ b/schema.sql @@ -50,7 +50,8 @@ CREATE TABLE `object` ( 'group', 'pad', 'stage', - 'issue' + 'issue', + 'message' ) NOT NULL, PRIMARY KEY (`guid`) ); @@ -121,3 +122,14 @@ CREATE TABLE `issue` ( `desc` 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 */ +DROP TABLE IF EXISTS `message`; +CREATE TABLE `message` ( + `guid` varchar(10) NOT NULL, + `author` varchar(10) NOT NULL, + `unread` int(10) unsigned NOT NULL DEFAULT 1, + `message` text DEFAULT NULL, + PRIMARY KEY (`guid`) +); -- cgit v1.2.3 From 81a46c7cf94aae9b39fd2bf3232778ea261281ff Mon Sep 17 00:00:00 2001 From: M Date: Fri, 20 Nov 2015 21:42:06 -0500 Subject: + Declaired types for system log levels. they will be encapsulated in Message-type objects --- schema.sql | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) diff --git a/schema.sql b/schema.sql index fd37b18..5dd4a10 100644 --- a/schema.sql +++ b/schema.sql @@ -51,7 +51,12 @@ CREATE TABLE `object` ( 'pad', 'stage', 'issue', - 'message' + 'message', + 'log-user', + 'log-group', + 'log-admin', + 'log-pad', + 'log-issue' ) NOT NULL, PRIMARY KEY (`guid`) ); @@ -125,6 +130,7 @@ CREATE TABLE `issue` ( /* 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, -- cgit v1.2.3 From 4f256fbdde3305177aa7e5c80ea715bfd677efee Mon Sep 17 00:00:00 2001 From: M Date: Fri, 20 Nov 2015 22:16:27 -0500 Subject: * Moved the `unread` value of messages out into a new table so that this value can now be user-specific --- schema.sql | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/schema.sql b/schema.sql index 5dd4a10..2171cde 100644 --- a/schema.sql +++ b/schema.sql @@ -69,6 +69,14 @@ CREATE TABLE `obj_member` ( 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: 'timeUpdated' field should be used to store the time of the start of the user's current session */ /* Notice: 'key' field is the user's hashed and salted password -- SHA256 */ @@ -135,7 +143,6 @@ DROP TABLE IF EXISTS `message`; CREATE TABLE `message` ( `guid` varchar(10) NOT NULL, `author` varchar(10) NOT NULL, - `unread` int(10) unsigned NOT NULL DEFAULT 1, `message` text DEFAULT NULL, PRIMARY KEY (`guid`) ); -- cgit v1.2.3 From 1bf95de120d273b96c0d89cc21f9c32f3b2e6851 Mon Sep 17 00:00:00 2001 From: M Date: Fri, 20 Nov 2015 22:57:19 -0500 Subject: + Defined setting table for system-wide administrator settings --- schema.sql | 8 ++++++++ 1 file changed, 8 insertions(+) diff --git a/schema.sql b/schema.sql index 2171cde..add1472 100644 --- a/schema.sql +++ b/schema.sql @@ -34,6 +34,14 @@ USE `db_scrott`; * 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` ( -- cgit v1.2.3