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
|
CREATE TABLE file (
file_id BIGSERIAL NOT NULL,
file_name TEXT NOT NULL,
file_latest BIGINT NOT NULL,
file_type SMALLINT NOT NULL,
file_deleted SMALLINT NOT NULL,
PRIMARY KEY(file_id)
);
CREATE UNIQUE INDEX file_name ON file (file_name);
CREATE INDEX file_latest ON file (file_latest);
CREATE TABLE filerevision (
fr_id BIGSERIAL NOT NULL,
fr_file BIGINT NOT NULL,
fr_size BIGINT DEFAULT 0 NOT NULL,
fr_width INT DEFAULT 0 NOT NULL,
fr_height INT DEFAULT 0 NOT NULL,
fr_metadata TEXT NOT NULL,
fr_bits INT DEFAULT 0 NOT NULL,
fr_description_id BIGINT NOT NULL,
fr_actor BIGINT NOT NULL,
fr_timestamp TIMESTAMPTZ NOT NULL,
fr_sha1 TEXT DEFAULT '' NOT NULL,
fr_archive_name TEXT DEFAULT '' NOT NULL,
fr_deleted SMALLINT NOT NULL,
PRIMARY KEY(fr_id)
);
CREATE INDEX fr_actor_timestamp ON filerevision (fr_actor, fr_timestamp);
CREATE INDEX fr_size ON filerevision (fr_size);
CREATE INDEX fr_timestamp ON filerevision (fr_timestamp);
CREATE INDEX fr_sha1 ON filerevision (fr_sha1);
CREATE INDEX fr_file ON filerevision (fr_file);
CREATE TABLE filetypes (
ft_id SMALLSERIAL NOT NULL,
ft_media_type TEXT NOT NULL,
ft_major_mime TEXT NOT NULL,
ft_minor_mime TEXT NOT NULL,
PRIMARY KEY(ft_id)
);
CREATE UNIQUE INDEX ft_media_mime ON filetypes (
ft_media_type, ft_major_mime, ft_minor_mime
);
|
