/
schema.sql
99 lines (80 loc) · 3.07 KB
/
schema.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
-- the basic 3 catalogue tables:
CREATE TABLE IF NOT EXISTS artist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
sortname TEXT NOT NULL
);
CREATE UNIQUE INDEX artist_sortname ON artist(sortname);
CREATE TABLE IF NOT EXISTS track (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE,
name TEXT NOT NULL,
sortname TEXT NOT NULL
);
CREATE UNIQUE INDEX track_artist_sortname ON track(artist,sortname);
CREATE TABLE IF NOT EXISTS album (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE,
name TEXT NOT NULL,
sortname TEXT NOT NULL
);
CREATE UNIQUE INDEX album_artist_sortname ON album(artist,sortname);
-- the trigram search indexes
CREATE TABLE IF NOT EXISTS artist_search_index (
ngram TEXT NOT NULL,
id INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE,
num INTEGER NOT NULL DEFAULT 1
);
CREATE UNIQUE INDEX artist_search_index_ngram_artist ON artist_search_index(ngram, id);
CREATE TABLE IF NOT EXISTS album_search_index (
ngram TEXT NOT NULL,
id INTEGER NOT NULL REFERENCES album(id) ON DELETE CASCADE ON UPDATE CASCADE,
num INTEGER NOT NULL DEFAULT 1
);
CREATE UNIQUE INDEX album_search_index_ngram_album ON album_search_index(ngram, id);
CREATE TABLE IF NOT EXISTS track_search_index (
ngram TEXT NOT NULL,
id INTEGER NOT NULL REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE,
num INTEGER NOT NULL DEFAULT 1
);
CREATE UNIQUE INDEX track_search_index_ngram_track ON track_search_index(ngram, id);
--CREATE TABLE IF NOT EXISTS album_track (
-- album INTEGER NOT NULL REFERENCES album(id) ON DELETE CASCADE ON UPDATE CASCADE,
-- track INTEGER NOT NULL REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE,
-- position INTEGER NOT NULL
--);
-- files on disk and joinage with catalogue:
CREATE TABLE IF NOT EXISTS file (
id INTEGER PRIMARY KEY AUTOINCREMENT,
path TEXT NOT NULL,
size INTEGER NOT NULL,
mtime INTEGER NOT NULL,
md5 TEXT,
mimetype TEXT,
duration INTEGER NOT NULL DEFAULT 0,
bitrate INTEGER NOT NULL DEfAULT 0
);
CREATE UNIQUE INDEX file_path_uniq ON file(path);
CREATE TABLE IF NOT EXISTS file_join (
file INTEGER NOT NULL REFERENCES file(id) ON DELETE CASCADE ON UPDATE CASCADE,
artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE,
track INTEGER NOT NULL REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE,
album INTEGER REFERENCES album(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- HTTP Authentication
CREATE TABLE IF NOT EXISTS playdar_auth (
token TEXT NOT NULL PRIMARY KEY,
website TEXT NOT NULL,
name TEXT NOT NULL,
mtime INTEGER NOT NULL,
permissions TEXT NOT NULL
);
-- Settings
CREATE TABLE IF NOT EXISTS playdar_settings (
ns TEXT,
name TEXT NOT NULL,
value TEXT,
defaultvalue TEXT NOT NULL,
description TEXT NOT NULL
);
CREATE UNIQUE INDEX playdar_settings_idx ON playdar_settings(ns,value);