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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
|
/*
* 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)
);
/*
* Scrott issues may have multiple assignees. This table is used to
* co-relate assignees and issues along with additional meta-data.
*/
DROP TABLE IF EXISTS assignees;
CREATE TABLE assignees (
guid varchar(8) NOT NULL, /* guid of issue */
assignee varchar(8) NOT NULL, /* user */
assigner varchar(8) NOT NULL, /* user */
assigned datetime NOT NULL, /* timestamp */
dismisser varchar(8) NOT NULL DEFAULT '', /* user */
dismissed varchar(64) NOT NULL DEFAULT '', /* timestamp */
signedoff varchar(64) NOT NULL DEFAULT '', /* timestamp */
PRIMARY KEY (guid, assignee)
);
/*
* 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,
mesg varchar(8) NOT NULL DEFAULT '',
closer varchar(8) NOT NULL DEFAULT '', /* user */
closed varchar(64) NOT NULL DEFAULT '', /* timestamp */
due varchar(64) NOT NULL DEFAULT '', /* timestamp */
tags text DEFAULT 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 DEFAULT NULL,
attachment varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (guid)
);
|
