1
-- Mutates to the database are entered into the transaction log
2
-- so they can be sent to peers to replay against a cache of your DB.
3
-- This allows peers to get diffs/sync your collection easily.
5
CREATE TABLE IF NOT EXISTS oplog (
6
id INTEGER PRIMARY KEY AUTOINCREMENT,
7
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE, -- DEFERRABLE INITIALLY DEFERRED,
10
singleton BOOLEAN NOT NULL,
11
compressed BOOLEAN NOT NULL,
14
CREATE UNIQUE INDEX oplog_guid ON oplog(guid);
15
CREATE INDEX oplog_source ON oplog(source);
19
-- the basic 3 catalogue tables:
21
CREATE TABLE IF NOT EXISTS artist (
22
id INTEGER PRIMARY KEY AUTOINCREMENT,
24
sortname TEXT NOT NULL
26
CREATE UNIQUE INDEX artist_sortname ON artist(sortname);
28
CREATE TABLE IF NOT EXISTS track (
29
id INTEGER PRIMARY KEY AUTOINCREMENT,
30
artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
32
sortname TEXT NOT NULL
34
CREATE UNIQUE INDEX track_artist_sortname ON track(artist,sortname);
36
CREATE TABLE IF NOT EXISTS album (
37
id INTEGER PRIMARY KEY AUTOINCREMENT,
38
artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
40
sortname TEXT NOT NULL
42
CREATE UNIQUE INDEX album_artist_sortname ON album(artist,sortname);
46
-- Source, typically a remote peer.
48
CREATE TABLE IF NOT EXISTS source (
49
id INTEGER PRIMARY KEY AUTOINCREMENT,
52
lastop TEXT NOT NULL DEFAULT "", -- guid of last op we've successfully applied
53
isonline BOOLEAN NOT NULL DEFAULT false
55
CREATE UNIQUE INDEX source_name ON source(name);
61
CREATE TABLE IF NOT EXISTS playlist (
62
guid TEXT PRIMARY KEY,
63
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- owner
64
shared BOOLEAN DEFAULT false,
68
lastmodified INTEGER NOT NULL DEFAULT 0,
69
currentrevision TEXT REFERENCES playlist_revision(guid) DEFERRABLE INITIALLY DEFERRED,
70
dynplaylist BOOLEAN DEFAULT false,
71
createdOn INTEGER NOT NULL DEFAULT 0
74
--INSERT INTO playlist(guid, title, info, currentrevision, dynplaylist)
75
--VALUES('dynamic_playlist-guid-1','Test Dynamic Playlist Dynamic','this playlist automatically created and used for testing','revisionguid-1', 1);
77
--INSERT INTO playlist(guid, title, info, currentrevision, dynplaylist)
78
--VALUES('dynamic_playlist-guid-2','Test Dynamic Playlist Static','this playlist automatically created and used for testing','revisionguid-11', 1);
80
CREATE TABLE IF NOT EXISTS playlist_item (
81
guid TEXT PRIMARY KEY,
82
playlist TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
83
trackname TEXT NOT NULL,
84
artistname TEXT NOT NULL,
87
duration INTEGER, -- in seconds, even tho xspf uses milliseconds
88
addedon INTEGER NOT NULL DEFAULT 0, -- date added to playlist
89
addedby INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- who added this to the playlist
90
result_hint TEXT -- hint as to a result, to avoid using the resolver
92
CREATE INDEX playlist_item_playlist ON playlist_item(playlist);
94
CREATE TABLE IF NOT EXISTS playlist_revision (
95
guid TEXT PRIMARY KEY,
96
playlist TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
97
entries TEXT, -- qlist( guid, guid... )
98
author INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
99
timestamp INTEGER NOT NULL DEFAULT 0,
100
previous_revision TEXT REFERENCES playlist_revision(guid) DEFERRABLE INITIALLY DEFERRED
103
--INSERT INTO playlist_revision(guid, playlist, entries)
104
-- VALUES('revisionguid-1', 'dynamic_playlist-guid-1', '[]');
105
--INSERT INTO playlist_revision(guid, playlist, entries)
106
-- VALUES('revisionguid-11', 'dynamic_playlist-guid-2', '[]');
108
CREATE TABLE IF NOT EXISTS dynamic_playlist (
109
guid TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
110
pltype TEXT, -- the generator type
111
plmode INTEGER, -- the mode of this playlist
112
autoload BOOLEAN DEFAULT true -- if this playlist should be autoloaded or not. true except for the case of special playlists we want to display elsewhere
115
--INSERT INTO dynamic_playlist(guid, pltype, plmode)
116
-- VALUES('dynamic_playlist-guid-1', 'echonest', 0);
117
--INSERT INTO dynamic_playlist(guid, pltype, plmode)
118
-- VALUES('dynamic_playlist-guid-2', 'echonest', 1);
120
-- list of controls in each playlist. each control saves a selectedType, a match, and an input
121
CREATE TABLE IF NOT EXISTS dynamic_playlist_controls (
123
playlist TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
129
--INSERT INTO dynamic_playlist_controls(id, playlist, selectedType, match, input)
130
-- VALUES('controlid-1', 'dynamic_playlist-guid-1', "artist", 0, "FooArtist" );
131
--INSERT INTO dynamic_playlist_controls(id, playlist, selectedType, match, input)
132
-- VALUES('controlid-2', 'dynamic_playlist-guid-11', "artist", 0, "FooArtist" );
136
CREATE TABLE IF NOT EXISTS dynamic_playlist_revision (
137
guid TEXT PRIMARY KEY NOT NULL REFERENCES playlist_revision(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
138
controls TEXT, -- qlist( id, id, id )
143
--INSERT INTO dynamic_playlist_revision(guid, controls, plmode, pltype)
144
-- VALUES('revisionguid-1', '["controlid-1"]', 0, "echonest");
145
--INSERT INTO dynamic_playlist_revision(guid, controls, plmode, pltype)
146
-- VALUES('revisionguid-11', '["controlid-2"]', 1, "echonest");
149
-- files on disk and joinage with catalogue. physical properties of files only:
151
-- if source=null, file is local to this machine
152
CREATE TABLE IF NOT EXISTS file (
153
id INTEGER PRIMARY KEY AUTOINCREMENT,
154
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
155
url TEXT NOT NULL, -- file:///music/foo/bar.mp3, <guid or hash?>
156
size INTEGER NOT NULL, -- in bytes
157
mtime INTEGER NOT NULL, -- file mtime, so we know to rescan
158
md5 TEXT, -- useful when comparing stuff p2p
159
mimetype TEXT, -- "audio/mpeg"
160
duration INTEGER NOT NULL DEFAULT 0, -- seconds
161
bitrate INTEGER NOT NULL DEFAULT 0 -- kbps (or equiv)
163
CREATE UNIQUE INDEX file_url_src_uniq ON file(source, url);
164
CREATE INDEX file_source ON file(source);
165
CREATE INDEX file_mtime ON file(mtime);
167
-- mtime of dir when last scanned.
168
-- load into memory when rescanning, skip stuff that's unchanged
169
CREATE TABLE IF NOT EXISTS dirs_scanned (
170
name TEXT PRIMARY KEY,
171
mtime INTEGER NOT NULL
174
CREATE TABLE IF NOT EXISTS file_join (
175
file INTEGER PRIMARY KEY REFERENCES file(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
176
artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
177
track INTEGER NOT NULL REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
178
album INTEGER REFERENCES album(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
180
composer INTEGER REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
183
CREATE INDEX file_join_track ON file_join(track);
184
CREATE INDEX file_join_artist ON file_join(artist);
185
CREATE INDEX file_join_album ON file_join(album);
189
-- tags, weighted and by source (rock, jazz etc)
191
-- weight is always 1.0 if tag provided by our user.
192
-- may be less from aggregate sources like lastfm global tags
193
CREATE TABLE IF NOT EXISTS track_tags (
194
id INTEGER PRIMARY KEY, -- track id
195
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
196
tag TEXT NOT NULL, -- always store as lowercase
197
ns TEXT, -- ie 'last.fm', 'echonest'
198
weight float DEFAULT 1.0 -- range 0-1
200
CREATE INDEX track_tags_tag ON track_tags(tag);
202
CREATE TABLE IF NOT EXISTS album_tags (
203
id INTEGER PRIMARY KEY, -- album id
204
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
205
tag TEXT NOT NULL, -- always store as lowercase
206
ns TEXT, -- ie 'last.fm', 'echonest'
207
weight float DEFAULT 1.0 -- range 0-1
209
CREATE INDEX album_tags_tag ON album_tags(tag);
211
CREATE TABLE IF NOT EXISTS artist_tags (
212
id INTEGER PRIMARY KEY, -- artist id
213
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
214
tag TEXT NOT NULL, -- always store as lowercase
215
ns TEXT, -- ie 'last.fm', 'echonest'
216
weight float DEFAULT 1.0 -- range 0-1
218
CREATE INDEX artist_tags_tag ON artist_tags(tag);
220
-- all other attributes.
221
-- like tags that have a value, eg:
222
-- BPM=120, releaseyear=1980, key=Dminor, composer=Someone
223
-- NB: since all values are text, numeric values should be zero-padded to a set amount
224
-- so that we can always do range queries.
226
CREATE TABLE IF NOT EXISTS track_attributes (
227
id INTEGER REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- track id
231
CREATE INDEX track_attrib_id ON track_attributes(id);
232
CREATE INDEX track_attrib_k ON track_attributes(k);
234
-- Collection attributes, tied to a source. An example might be an echonest song catalog
236
CREATE TABLE IF NOT EXISTS collection_attributes (
237
id INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- source id, null for local source
242
-- social attributes connected to the track.
243
-- like love, hate, comments, recommendations
244
-- love=[comment], hate=[comment], comment=Some text
245
-- NB: since all values are text, numeric values should be zero-padded to a set amount
246
-- so that we can always do range queries.
248
CREATE TABLE IF NOT EXISTS social_attributes (
249
id INTEGER REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- track id
250
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE, -- DEFERRABLE INITIALLY DEFERRED,
253
timestamp INTEGER NOT NULL DEFAULT 0
255
CREATE INDEX social_attrib_id ON social_attributes(id);
256
CREATE INDEX social_attrib_source ON social_attributes(source);
257
CREATE INDEX social_attrib_k ON social_attributes(k);
258
CREATE INDEX social_attrib_timestamp ON social_attributes(timestamp);
264
-- if source=null, file is local to this machine
265
CREATE TABLE IF NOT EXISTS playback_log (
266
id INTEGER PRIMARY KEY AUTOINCREMENT,
267
source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
268
track INTEGER REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
269
playtime INTEGER NOT NULL, -- when playback finished (timestamp)
270
secs_played INTEGER NOT NULL
273
CREATE INDEX playback_log_source ON playback_log(source);
274
CREATE INDEX playback_log_track ON playback_log(track);
278
-- auth information for http clients
280
CREATE TABLE IF NOT EXISTS http_client_auth (
281
token TEXT NOT NULL PRIMARY KEY,
282
website TEXT NOT NULL,
286
permissions TEXT NOT NULL
291
-- Schema version, and misc tomahawk settings relating to the collection db
293
CREATE TABLE IF NOT EXISTS settings (
294
k TEXT NOT NULL PRIMARY KEY,
295
v TEXT NOT NULL DEFAULT ''
298
INSERT INTO settings(k,v) VALUES('schema_version', '29');
added
removed
src/libtomahawk/database/Schema.sql
