1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
/* 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.
*/
/* 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`)
);
|
