lightize/schema.sql at master · bencahill/lightize · 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
CREATE TABLE image(
	id INTEGER PRIMARY KEY NOT NULL,
	name TEXT NOT NULL,
	directoryId INTEGER NOT NULL,
	hiddenInDirectory BOOLEAN NOT NULL,
	eventId INTEGER NOT NULL,
	date TEXT,
	info TEXT,
	rating INTEGER NOT NULL,
	edits TEXT,
	UNIQUE (name, directoryId),
	FOREIGN KEY(directoryId) REFERENCES directory(id) ON DELETE CASCADE
);

CREATE TABLE directory(
	id INTEGER PRIMARY KEY NOT NULL,
	name TEXT NOT NULL,
	date INTEGER NOT NULL
);

CREATE TABLE event(
	id INTEGER PRIMARY KEY NOT NULL,
	name TEXT NOT NULL,
	directoryId INTEGER NOT NULL,
	date INTEGER NOT NULL,
	UNIQUE (name, directoryId),
	FOREIGN KEY(directoryId) REFERENCES directory(id) ON DELETE CASCADE
);

CREATE TABLE album(
	id INTEGER PRIMARY KEY NOT NULL,
	name TEXT UNIQUE NOT NULL,
	date INTEGER NOT NULL
);

CREATE TABLE imageAlbum(
	imageId INTEGER NOT NULL,
	albumId INTEGER NOT NULL,
	hiddenInAlbum BOOLEAN NOT NULL,
	FOREIGN KEY(imageId) REFERENCES image(id) ON DELETE CASCADE,
	FOREIGN KEY(albumId) REFERENCES album(id) ON DELETE CASCADE,
	PRIMARY KEY(imageId, albumId)
);