cigarbox/schema.sql at master · n8foo/cigarbox · GitHub

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
CREATE TABLE photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
privacy INTEGER DEFAULT 0,
sha1 TEXT NOT NULL,
fileType TEXT(4) NOT NULL,
dateTaken INTEGER DEFAULT NULL,
ts INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX 'photos_sha1_UNIQUE' ON 'photos' ('sha1' ASC);


CREATE TABLE photosets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT,
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX 'photosets_title_UNIQUE' ON 'photosets' ('title' ASC);

CREATE TABLE galleries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT,
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX 'galleries_title_UNIQUE' ON 'galleries' ('title' ASC);

CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tag TEXT,
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX 'tags_tag_UNIQUE' ON 'tags' ('tag' ASC);

CREATE TABLE comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
comment TEXT,
photo_id INTEGER REFERENCES photos (id),
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE import_meta (
id INTEGER PRIMARY KEY AUTOINCREMENT,
photo_id INTEGER REFERENCES photos (id),
fileDate INTEGER NOT NULL,
importPath TEXT,
importSource TEXT,
S3 integer,
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX 'import_meta_photo_id_UNIQUE' ON 'import_meta' ('photo_id' ASC);

CREATE TABLE galleries_photosets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
galleries_id INTEGER REFERENCES galleries (id),
photoset_id INTEGER REFERENCES photosets (id),
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX 'galleries_photosets_UNIQUE' ON 'galleries_photosets' ('galleries_id','photoset_id' ASC);

CREATE TABLE tags_photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tag_id INTEGER REFERENCES tags (id),
photo_id INTEGER REFERENCES photos (id),
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX 'tags_photos_UNIQUE' ON 'tags_photos' ('tag_id','photo_id' ASC);

CREATE TABLE photosets_photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
photoset_id INTEGER REFERENCES photos (id),
photo_id INTEGER REFERENCES photosets (id),
ts NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX 'photosets_photos_UNIQUE' ON 'photosets_photos' ('photoset_id','photo_id' ASC);