-
Notifications
You must be signed in to change notification settings - Fork 0
/
mike.sql
365 lines (294 loc) · 17.9 KB
/
mike.sql
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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
-- Mike's Schema
-- vim: set tabstop=4 expandtab autoindent smartindent:
-- author: Sylvain Rabot <[email protected]>
-- date: 03/07/2010
-- copyright: All rights reserved
-- __ _________ ______
-- / |/ / _/ //_/ __/
-- / /|_/ // // ,< / _/
-- /_/ /_/___/_/|_/___/
--
-- mike ------------------------------------------------------------------------
COMMENT ON SCHEMA mike IS 'mike, a lightweight, robust, efficient virtual file system';
-- mike.info -------------------------------------------------------------------
DROP TABLE IF EXISTS mike.info CASCADE;
CREATE TABLE mike.info (
key text NOT NULL CHECK (key ~ '^[A-Z0-9_]{2,}$'),
ctime timestamptz NOT NULL DEFAULT now(),
value text,
PRIMARY KEY (key, ctime)
);
COMMENT ON TABLE mike.info IS 'informations about database deployement';
COMMENT ON COLUMN mike.info.key IS 'information identifier';
COMMENT ON COLUMN mike.info.value IS 'information value';
-- mike.info -------------------------------------------------------------------
DROP TABLE IF EXISTS mike.conf CASCADE;
CREATE TABLE mike.conf (
key text NOT NULL PRIMARY KEY CHECK (key ~ '^[a-z0-9_]{2,}$') UNIQUE,
value text,
ctime timestamptz NOT NULL DEFAULT now(),
mtime timestamptz
);
COMMENT ON TABLE mike.conf IS 'configurations table';
COMMENT ON COLUMN mike.conf.key IS 'configuration identifier';
COMMENT ON COLUMN mike.conf.value IS 'configuration value';
-- mike.user_state -------------------------------------------------------------
DROP TABLE IF EXISTS mike.user_state CASCADE;
CREATE TABLE mike.user_state (
state smallint NOT NULL PRIMARY KEY,
label text NOT NULL CHECK (label ~ '^[A-Z0-9_]{2,}$'),
description text NOT NULL CHECK (description != '')
);
COMMENT ON TABLE mike.user_state IS 'list of user states';
COMMENT ON COLUMN mike.user_state.state IS 'state identifier';
COMMENT ON COLUMN mike.user_state.label IS 'state label';
COMMENT ON COLUMN mike.user_state.description IS 'state description';
INSERT INTO mike.user_state (state, label, description) VALUES (0, 'ALIVE', 'user alive');
INSERT INTO mike.user_state (state, label, description) VALUES (1, 'LOCKED', 'user locked');
-- mike.user -------------------------------------------------------------------
DROP TABLE IF EXISTS mike.user CASCADE;
CREATE TABLE mike.user (
id_user serial NOT NULL PRIMARY KEY,
id_sso text DEFAULT NULL,
nickname text DEFAULT NULL,
state smallint NOT NULL DEFAULT 1 REFERENCES mike.user_state (state),
#ifdef LVM_SUPPORT
lv text NOT NULL DEFAULT 'lv_1',
#endif
ctime timestamptz NOT NULL DEFAULT now(),
mtime timestamptz,
UNIQUE (id_sso)
);
COMMENT ON TABLE mike.user IS 'user informations';
COMMENT ON COLUMN mike.user.id_user IS 'user unique identifier';
COMMENT ON COLUMN mike.user.id_sso IS 'user unique external identifier';
COMMENT ON COLUMN mike.user.nickname IS 'user nickname';
COMMENT ON COLUMN mike.user.state IS 'user state';
#ifdef LVM_SUPPORT
COMMENT ON COLUMN mike.user.lv IS 'user''s logical volume name';
#endif
COMMENT ON COLUMN mike.user.ctime IS 'user creation date';
COMMENT ON COLUMN mike.user.ctime IS 'user modification date';
-- mike.group ------------------------------------------------------------------
DROP TABLE IF EXISTS mike.group CASCADE;
CREATE TABLE mike.group (
id_group serial NOT NULL PRIMARY KEY,
id_user integer NOT NULL REFERENCES mike.user (id_user),
name text NOT NULL CHECK (name != ''),
description text DEFAULT NULL
);
COMMENT ON TABLE mike.group IS 'group informations';
COMMENT ON COLUMN mike.group.id_group IS 'group unique identifier';
COMMENT ON COLUMN mike.group.id_user IS 'group owner';
COMMENT ON COLUMN mike.group.name IS 'group name';
COMMENT ON COLUMN mike.group.description IS 'group description';
-- mike.as_user_group ----------------------------------------------------------
DROP TABLE IF EXISTS mike.as_user_group CASCADE;
CREATE TABLE mike.as_user_group (
id_user integer NOT NULL REFERENCES mike.user (id_user),
id_group integer NOT NULL REFERENCES mike.group (id_group)
);
COMMENT ON TABLE mike.as_user_group IS 'associative table between mike.user and mike.group';
COMMENT ON COLUMN mike.as_user_group.id_user IS 'user identifier';
COMMENT ON COLUMN mike.as_user_group.id_group IS 'group identifier';
-- mike.inode_state ------------------------------------------------------------
DROP TABLE IF EXISTS mike.inode_state CASCADE;
CREATE TABLE mike.inode_state (
state smallint NOT NULL PRIMARY KEY,
label text NOT NULL CHECK (label ~ '^[A-Z0-9_]{2,}$'),
description text NOT NULL CHECK (description != '')
);
COMMENT ON TABLE mike.inode_state IS 'list of inode states';
COMMENT ON COLUMN mike.inode_state.state IS 'state identifier';
COMMENT ON COLUMN mike.inode_state.label IS 'state label';
COMMENT ON COLUMN mike.inode_state.description IS 'state description';
INSERT INTO mike.inode_state (state, label, description) VALUES (0, 'ALIVE', 'alive');
INSERT INTO mike.inode_state (state, label, description) VALUES (1, 'TRASHED', 'trashed');
INSERT INTO mike.inode_state (state, label, description) VALUES (2, 'WAITING_FOR_PHYSICAL_REMOVAL', 'waiting for physical removal');
INSERT INTO mike.inode_state (state, label, description) VALUES (3, 'WAITING_FOR_LOGICAL_REMOVAL', 'waiting for data removal');
-- mike.mimetype ---------------------------------------------------------------
DROP TABLE IF EXISTS mike.mimetype CASCADE;
CREATE TABLE mike.mimetype (
id_mimetype smallint NOT NULL PRIMARY KEY,
mimetype text NOT NULL CHECK (mimetype ~ E'^[a-zA-Z0-9_/ .+-]+$'),
UNIQUE (mimetype)
) WITH (fillfactor = 98);
CREATE SEQUENCE mimetype_id_mimetype_seq START 1 OWNED BY mike.mimetype.id_mimetype;
ALTER TABLE mike.mimetype ALTER COLUMN id_mimetype SET DEFAULT nextval('mimetype_id_mimetype_seq'::regclass)::smallint;
COMMENT ON TABLE mike.mimetype IS 'list of mimetypes';
COMMENT ON COLUMN mike.mimetype.id_mimetype IS 'mimetype identifier';
COMMENT ON COLUMN mike.mimetype.mimetype IS 'mimetype';
INSERT INTO mike.mimetype (id_mimetype, mimetype) VALUES (0::smallint, 'application/x-directory');
-- mike.inode ------------------------------------------------------------------
DROP TABLE IF EXISTS mike.inode CASCADE;
CREATE TABLE mike.inode (
id_inode bigserial NOT NULL PRIMARY KEY,
id_inode_parent bigint REFERENCES mike.inode (id_inode) ON DELETE CASCADE,
id_user integer NOT NULL REFERENCES mike.user (id_user),
state smallint NOT NULL DEFAULT 0::smallint REFERENCES mike.inode_state (state),
id_mimetype smallint REFERENCES mike.mimetype (id_mimetype),
name text NOT NULL CHECK (
name != '' AND
length(name) <= 255 AND
strpos(name, '/') = 0 AND
strpos(name, E'\u000d') = 0 AND -- no CR
strpos(name, E'\u000a') = 0 -- no LF
),
path text NOT NULL CHECK (substr(path, 1, 1) = '/'),
#ifdef TREE_MAX_DEPTH
treepath ltree NOT NULL CHECK (nlevel(treepath) <= TREE_MAX_DEPTH),
#else
treepath ltree NOT NULL,
#endif /* TREE_MAX_DEPTH */
ctime timestamptz NOT NULL DEFAULT now(),
mtime timestamptz,
#ifdef INODE_RAND_COLUMN
rand float NOT NULL DEFAULT random(),
#endif /* INODE_RAND_COLUMN */
size bigint NOT NULL DEFAULT 0,
versioning_size bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 90);
COMMENT ON TABLE mike.inode IS 'inodes are entities extended by all vfs items';
COMMENT ON COLUMN mike.inode.id_inode IS 'inode unique identifier';
COMMENT ON COLUMN mike.inode.id_inode_parent IS 'identifier of parent inode';
COMMENT ON COLUMN mike.inode.id_user IS 'owner of the inode';
COMMENT ON COLUMN mike.inode.state IS 'state of the inode, references mike.inode_state';
COMMENT ON COLUMN mike.inode.name IS 'name of the inode, limited to 255 characters';
COMMENT ON COLUMN mike.inode.path IS 'path of the inode';
COMMENT ON COLUMN mike.inode.treepath IS 'treepath of the inode';
COMMENT ON COLUMN mike.inode.ctime IS 'creation timestamp with timezone of the inode';
COMMENT ON COLUMN mike.inode.mtime IS 'last modification timestamp with timezone of the inode';
#ifdef INODE_RAND_COLUMN
COMMENT ON COLUMN mike.inode.rand IS 'random value used for random clustering';
#endif /* INODE_RAND_COLUMN */
COMMENT ON COLUMN mike.inode.id_mimetype IS 'mimetype of the inode';
COMMENT ON COLUMN mike.inode.size IS 'size of the inode';
COMMENT ON COLUMN mike.inode.versioning_size IS 'versioning size of the inode';
-- mike.directory --------------------------------------------------------------
DROP TABLE IF EXISTS mike.directory CASCADE;
CREATE TABLE mike.directory (
id_inode bigint NOT NULL PRIMARY KEY,
id_inode_parent bigint REFERENCES mike.directory (id_inode) ON DELETE CASCADE,
id_mimetype smallint NOT NULL REFERENCES mike.mimetype (id_mimetype) DEFAULT 0::smallint,
inner_mtime timestamptz,
inner_size bigint NOT NULL DEFAULT 0,
inner_versioning_size bigint NOT NULL DEFAULT 0,
dir_count smallint NOT NULL DEFAULT 0::smallint,
inner_dir_count integer NOT NULL DEFAULT 0,
file_count smallint NOT NULL DEFAULT 0::smallint,
inner_file_count integer NOT NULL DEFAULT 0,
UNIQUE (id_inode_parent, state, name)
) INHERITS (mike.inode) WITH (fillfactor = 90);
COMMENT ON TABLE mike.directory IS 'table containing all the directory inodes';
COMMENT ON COLUMN mike.directory.id_inode IS 'inode unique identifier';
COMMENT ON COLUMN mike.directory.id_inode_parent IS 'identifier of parent inode';
COMMENT ON COLUMN mike.directory.id_user IS 'owner of the inode';
COMMENT ON COLUMN mike.directory.id_mimetype IS 'mimetype of the inode';
COMMENT ON COLUMN mike.directory.state IS 'state of the inode, references mike.inode_state';
COMMENT ON COLUMN mike.directory.name IS 'name of the inode, limited to 255 characters';
COMMENT ON COLUMN mike.directory.path IS 'path of the inode';
COMMENT ON COLUMN mike.directory.treepath IS 'treepath of the inode';
COMMENT ON COLUMN mike.directory.ctime IS 'creation timestamp with timezone of the inode';
COMMENT ON COLUMN mike.directory.mtime IS 'last modification timestamp with timezone of the inode';
COMMENT ON COLUMN mike.directory.inner_mtime IS 'modification date of last updated child directory';
COMMENT ON COLUMN mike.directory.size IS 'size in bytes of the inode';
COMMENT ON COLUMN mike.directory.inner_size IS 'size sum of child directories';
COMMENT ON COLUMN mike.directory.versioning_size IS 'size in bytes of the inode';
COMMENT ON COLUMN mike.directory.inner_versioning_size IS 'versioning size sum of child directories';
COMMENT ON COLUMN mike.directory.dir_count IS 'number of direct child directories';
COMMENT ON COLUMN mike.directory.inner_dir_count IS 'number of child directories';
COMMENT ON COLUMN mike.directory.file_count IS 'number of direct child files';
COMMENT ON COLUMN mike.directory.inner_file_count IS 'number of child files';
-- mike.file -------------------------------------------------------------------
DROP TABLE IF EXISTS mike.file CASCADE;
CREATE TABLE mike.file (
id_inode bigint NOT NULL PRIMARY KEY,
id_inode_parent bigint NOT NULL REFERENCES mike.directory (id_inode) ON DELETE RESTRICT,
#ifndef NO_ATIME
atime timestamptz,
#endif /* NO_ATIME */
UNIQUE(id_inode_parent, state, name)
) INHERITS (mike.inode) WITH (fillfactor = 90);
COMMENT ON TABLE mike.file IS 'table containing all the file inodes';
COMMENT ON COLUMN mike.file.id_inode IS 'inode unique identifier';
COMMENT ON COLUMN mike.file.id_inode_parent IS 'identifier of parent inode';
COMMENT ON COLUMN mike.file.id_user IS 'owner of the inode';
COMMENT ON COLUMN mike.file.id_mimetype IS 'mimetype of the inode';
COMMENT ON COLUMN mike.file.state IS 'state of the inode, references mike.inode_state';
COMMENT ON COLUMN mike.file.name IS 'name of the inode, limited to 255 characters';
COMMENT ON COLUMN mike.file.path IS 'path of the inode';
COMMENT ON COLUMN mike.file.treepath IS 'treepath of the inode';
COMMENT ON COLUMN mike.file.ctime IS 'creation timestamp with timezone of the inode';
COMMENT ON COLUMN mike.file.mtime IS 'last modification timestamp with timezone of the inode';
#ifndef NO_ATIME
COMMENT ON COLUMN mike.file.atime IS 'last access timestamp with timezone of the inode';
#endif /* NO_ATIME */
COMMENT ON COLUMN mike.file.size IS 'size of the inode';
COMMENT ON COLUMN mike.file.versioning_size IS 'versioning size of the inode';
-- mike.lv ---------------------------------------------------------------------
#ifdef LVM_SUPPORT
CREATE TABLE mike.lv (
name text NOT NULL PRIMARY KEY,
ctime timestamptz NOT NULL DEFAULT now(),
mtime timestamptz NOT NULL DEFAULT now(),
users integer[] NOT NULL DEFAULT ARRAY[]::integer[]
);
#endif /* LVM_SUPPORT */
-- mike.volume_state -----------------------------------------------------------
DROP TABLE IF EXISTS mike.volume_state CASCADE;
CREATE TABLE mike.volume_state (
state integer NOT NULL PRIMARY KEY,
label text NOT NULL CHECK (label ~ '^[A-Z0-9_]{2,}$'),
description text NOT NULL CHECK (description != '')
);
COMMENT ON TABLE mike.volume_state IS 'list of volume states';
COMMENT ON COLUMN mike.volume_state.state IS 'state identifier';
COMMENT ON COLUMN mike.volume_state.label IS 'state label';
COMMENT ON COLUMN mike.volume_state.description IS 'state description';
INSERT INTO mike.volume_state (state, label, description) VALUES (0, 'UP', 'volume up');
INSERT INTO mike.volume_state (state, label, description) VALUES (1, 'DOWN', 'volume down');
-- mike.volume -----------------------------------------------------------------
DROP TABLE IF EXISTS mike.volume CASCADE;
CREATE TABLE mike.volume (
id_volume smallint NOT NULL PRIMARY KEY,
state integer NOT NULL REFERENCES mike.volume_state (state) DEFAULT 1,
path text NOT NULL CHECK (substr(path, 1, 1) = '/' AND substring(path, '.$') = '/'),
virtual_used_size bigint NOT NULL DEFAULT 0,
real_used_size bigint NOT NULL DEFAULT 0,
max_size bigint NOT NULL DEFAULT 0,
ctime timestamptz NOT NULL DEFAULT now(),
mtime timestamptz,
token text
) WITH (fillfactor = 95);
CREATE SEQUENCE volume_id_volume_seq START 1 OWNED BY mike.volume.id_volume;
ALTER TABLE mike.volume ALTER COLUMN id_volume SET DEFAULT nextval('volume_id_volume_seq'::regclass)::smallint;
COMMENT ON TABLE mike.volume IS 'volumes informations';
COMMENT ON COLUMN mike.volume.id_volume IS 'volume unique identifier';
COMMENT ON COLUMN mike.volume.state IS 'state of the volumes, references mike.volume_state';
COMMENT ON COLUMN mike.volume.path IS 'path of the volumes';
COMMENT ON COLUMN mike.volume.virtual_used_size IS 'virtual used size used on the volumes';
COMMENT ON COLUMN mike.volume.real_used_size IS 'real used size used on the volumes';
COMMENT ON COLUMN mike.volume.max_size IS 'max size available on the volumes';
COMMENT ON COLUMN mike.volume.ctime IS 'creation date off the volumes';
COMMENT ON COLUMN mike.volume.mtime IS 'last modification date of the volumes';
COMMENT ON COLUMN mike.volume.token IS 'security token for volume removal';
-- mike.xfile ------------------------------------------------------------------
DROP TABLE IF EXISTS mike.xfile CASCADE;
CREATE TABLE mike.xfile (
id_xfile bigserial NOT NULL PRIMARY KEY,
id_volume smallint NOT NULL REFERENCES mike.volume (id_volume) ON DELETE CASCADE,
id_mimetype smallint NOT NULL REFERENCES mike.mimetype (id_mimetype) ON DELETE RESTRICT,
size bigint NOT NULL,
sha1 text CHECK (length(sha1) = 40),
md5 text CHECK (length(md5) = 32)
) WITH (fillfactor = 95);
COMMENT ON TABLE mike.xfile IS 'xfile represents files on the file system';
-- mike.as_file_xfile ----------------------------------------------------------
DROP TABLE IF EXISTS mike.as_file_xfile CASCADE;
CREATE TABLE mike.as_file_xfile (
id_user integer NOT NULL REFERENCES mike.user (id_user) ON DELETE CASCADE,
id_inode bigint NOT NULL REFERENCES mike.file (id_inode) ON DELETE CASCADE,
id_xfile bigint NOT NULL REFERENCES mike.xfile (id_xfile) ON DELETE CASCADE,
ctime timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE mike.as_file_xfile IS 'associative table between mike.file and mike.xfile';