/src/libtomahawk/database/Schema.sql

  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.
  4
  5CREATE 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,
  8    guid TEXT NOT NULL,
  9    command TEXT NOT NULL,
 10    singleton BOOLEAN NOT NULL,
 11    compressed BOOLEAN NOT NULL,
 12    json TEXT NOT NULL
 13);
 14CREATE UNIQUE INDEX oplog_guid ON oplog(guid);
 15CREATE INDEX oplog_source ON oplog(source);
 16
 17
 18
 19-- the basic 3 catalogue tables:
 20
 21CREATE TABLE IF NOT EXISTS artist (
 22    id INTEGER PRIMARY KEY AUTOINCREMENT,
 23    name TEXT NOT NULL,
 24    sortname TEXT NOT NULL
 25);
 26CREATE UNIQUE INDEX artist_sortname ON artist(sortname);
 27CREATE INDEX artist_name ON artist(name);
 28
 29CREATE TABLE IF NOT EXISTS track (
 30    id INTEGER PRIMARY KEY AUTOINCREMENT,
 31    artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 32    name TEXT NOT NULL,
 33    sortname TEXT NOT NULL
 34);
 35CREATE UNIQUE INDEX track_artist_sortname ON track(artist,sortname);
 36CREATE INDEX track_name ON track(name);
 37
 38CREATE TABLE IF NOT EXISTS album (
 39    id INTEGER PRIMARY KEY AUTOINCREMENT,
 40    artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 41    name TEXT NOT NULL,
 42    sortname TEXT NOT NULL
 43);
 44CREATE UNIQUE INDEX album_artist_sortname ON album(artist,sortname);
 45
 46
 47
 48-- Source, typically a remote peer.
 49
 50CREATE TABLE IF NOT EXISTS source (
 51    id INTEGER PRIMARY KEY AUTOINCREMENT,
 52    name TEXT NOT NULL,
 53    friendlyname TEXT,
 54    lastop TEXT NOT NULL DEFAULT "",       -- guid of last op we've successfully applied
 55    isonline BOOLEAN NOT NULL DEFAULT false
 56);
 57CREATE UNIQUE INDEX source_name ON source(name);
 58
 59
 60
 61-- playlists
 62
 63CREATE TABLE IF NOT EXISTS playlist (
 64    guid TEXT PRIMARY KEY,
 65    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- owner
 66    shared BOOLEAN DEFAULT false,
 67    title TEXT,
 68    info TEXT,
 69    creator TEXT,
 70    lastmodified INTEGER NOT NULL DEFAULT 0,
 71    currentrevision TEXT REFERENCES playlist_revision(guid) DEFERRABLE INITIALLY DEFERRED,
 72    dynplaylist BOOLEAN DEFAULT false,
 73    createdOn INTEGER NOT NULL DEFAULT 0
 74);
 75
 76--INSERT INTO playlist(guid, title, info, currentrevision, dynplaylist)
 77--VALUES('dynamic_playlist-guid-1','Test Dynamic Playlist Dynamic','this playlist automatically created and used for testing','revisionguid-1', 1);
 78
 79--INSERT INTO playlist(guid, title, info, currentrevision, dynplaylist)
 80--VALUES('dynamic_playlist-guid-2','Test Dynamic Playlist Static','this playlist automatically created and used for testing','revisionguid-11', 1);
 81
 82CREATE TABLE IF NOT EXISTS playlist_item (
 83    guid TEXT PRIMARY KEY,
 84    playlist TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 85    trackname  TEXT NOT NULL,
 86    artistname TEXT NOT NULL,
 87    albumname  TEXT,
 88    annotation TEXT,
 89    duration INTEGER,       -- in seconds, even tho xspf uses milliseconds
 90    addedon INTEGER NOT NULL DEFAULT 0,   -- date added to playlist
 91    addedby INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- who added this to the playlist
 92    result_hint TEXT        -- hint as to a result, to avoid using the resolver
 93);
 94CREATE INDEX playlist_item_playlist ON playlist_item(playlist);
 95CREATE INDEX playlist_item_trackname ON playlist_item(trackname);
 96CREATE INDEX playlist_item_artistname ON playlist_item(artistname);
 97
 98CREATE TABLE IF NOT EXISTS playlist_revision (
 99    guid TEXT PRIMARY KEY,
100    playlist TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
101    entries TEXT, -- qlist( guid, guid... )
102    author INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
103    timestamp INTEGER NOT NULL DEFAULT 0,
104    previous_revision TEXT REFERENCES playlist_revision(guid) DEFERRABLE INITIALLY DEFERRED
105);
106
107--INSERT INTO playlist_revision(guid, playlist, entries)
108--      VALUES('revisionguid-1', 'dynamic_playlist-guid-1', '[]');
109--INSERT INTO playlist_revision(guid, playlist, entries)
110--      VALUES('revisionguid-11', 'dynamic_playlist-guid-2', '[]');
111
112CREATE TABLE IF NOT EXISTS dynamic_playlist (
113    guid TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
114    pltype TEXT, -- the generator type
115    plmode INTEGER, -- the mode of this playlist
116    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
117);
118
119--INSERT INTO dynamic_playlist(guid, pltype, plmode)
120--      VALUES('dynamic_playlist-guid-1', 'echonest', 0);
121--INSERT INTO dynamic_playlist(guid, pltype, plmode)
122--      VALUES('dynamic_playlist-guid-2', 'echonest', 1);
123
124-- list of controls in each playlist. each control saves a selectedType, a match, and an input
125CREATE TABLE IF NOT EXISTS dynamic_playlist_controls (
126    id TEXT PRIMARY KEY,
127    playlist TEXT NOT NULL REFERENCES playlist(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
128    selectedType TEXT,
129    match TEXT,
130    input TEXT
131);
132
133--INSERT INTO dynamic_playlist_controls(id, playlist, selectedType, match, input)
134--      VALUES('controlid-1', 'dynamic_playlist-guid-1', "artist", 0, "FooArtist" );
135--INSERT INTO dynamic_playlist_controls(id, playlist, selectedType, match, input)
136--      VALUES('controlid-2', 'dynamic_playlist-guid-11', "artist", 0, "FooArtist" );
137
138
139
140CREATE TABLE IF NOT EXISTS dynamic_playlist_revision (
141    guid TEXT PRIMARY KEY NOT NULL REFERENCES playlist_revision(guid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
142    controls TEXT, -- qlist( id, id, id )
143    plmode INTEGER,
144    pltype TEXT
145);
146
147--INSERT INTO dynamic_playlist_revision(guid, controls, plmode, pltype)
148--      VALUES('revisionguid-1', '["controlid-1"]', 0, "echonest");
149--INSERT INTO dynamic_playlist_revision(guid, controls, plmode, pltype)
150--      VALUES('revisionguid-11', '["controlid-2"]', 1, "echonest");
151
152
153-- files on disk and joinage with catalogue. physical properties of files only:
154
155-- if source=null, file is local to this machine
156CREATE TABLE IF NOT EXISTS file (
157    id INTEGER PRIMARY KEY AUTOINCREMENT,
158    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
159    url TEXT NOT NULL,                   -- file:///music/foo/bar.mp3, <guid or hash?>
160    size INTEGER NOT NULL,               -- in bytes
161    mtime INTEGER NOT NULL,              -- file mtime, so we know to rescan
162    md5 TEXT,                            -- useful when comparing stuff p2p
163    mimetype TEXT,                       -- "audio/mpeg"
164    duration INTEGER NOT NULL DEFAULT 0, -- seconds
165    bitrate INTEGER NOT NULL DEFAULT 0   -- kbps (or equiv)
166);
167CREATE UNIQUE INDEX file_url_src_uniq ON file(source, url);
168CREATE INDEX file_source ON file(source);
169CREATE INDEX file_mtime ON file(mtime);
170
171-- mtime of dir when last scanned.
172-- load into memory when rescanning, skip stuff that's unchanged
173CREATE TABLE IF NOT EXISTS dirs_scanned (
174    name TEXT PRIMARY KEY,
175    mtime INTEGER NOT NULL
176);
177
178CREATE TABLE IF NOT EXISTS file_join (
179    file INTEGER PRIMARY KEY REFERENCES file(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
180    artist INTEGER NOT NULL REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
181    track INTEGER NOT NULL REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
182    album INTEGER REFERENCES album(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
183    albumpos INTEGER,
184    composer INTEGER REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
185    discnumber INTEGER
186);
187CREATE INDEX file_join_track  ON file_join(track);
188CREATE INDEX file_join_artist ON file_join(artist);
189CREATE INDEX file_join_album  ON file_join(album);
190
191
192
193-- tags, weighted and by source (rock, jazz etc)
194
195-- weight is always 1.0 if tag provided by our user.
196-- may be less from aggregate sources like lastfm global tags
197CREATE TABLE IF NOT EXISTS track_tags (
198    id INTEGER PRIMARY KEY,   -- track id
199    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
200    tag TEXT NOT NULL,        -- always store as lowercase
201    ns TEXT,                  -- ie 'last.fm', 'echonest'
202    weight float DEFAULT 1.0  -- range 0-1
203);
204CREATE INDEX track_tags_tag ON track_tags(tag);
205
206CREATE TABLE IF NOT EXISTS album_tags (
207    id INTEGER PRIMARY KEY,   -- album id
208    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
209    tag TEXT NOT NULL,        -- always store as lowercase
210    ns TEXT,                  -- ie 'last.fm', 'echonest'
211    weight float DEFAULT 1.0  -- range 0-1
212);
213CREATE INDEX album_tags_tag ON album_tags(tag);
214
215CREATE TABLE IF NOT EXISTS artist_tags (
216    id INTEGER PRIMARY KEY,   -- artist id
217    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
218    tag TEXT NOT NULL,        -- always store as lowercase
219    ns TEXT,                  -- ie 'last.fm', 'echonest'
220    weight float DEFAULT 1.0  -- range 0-1
221);
222CREATE INDEX artist_tags_tag ON artist_tags(tag);
223
224-- all other attributes.
225-- like tags that have a value, eg:
226--  BPM=120, releaseyear=1980, key=Dminor, composer=Someone
227-- NB: since all values are text, numeric values should be zero-padded to a set amount
228--     so that we can always do range queries.
229
230CREATE TABLE IF NOT EXISTS track_attributes (
231    id INTEGER REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,   -- track id
232    k TEXT NOT NULL,
233    v TEXT NOT NULL
234);
235CREATE INDEX track_attrib_id ON track_attributes(id);
236CREATE INDEX track_attrib_k  ON track_attributes(k);
237
238-- Collection attributes, tied to a source. An example might be an echonest song catalog
239
240CREATE TABLE IF NOT EXISTS collection_attributes (
241    id INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, -- source id, null for local source
242    k TEXT NOT NULL,
243    v TEXT NOT NULL
244);
245
246-- social attributes connected to the track.
247-- like love, hate, comments, recommendations
248--  love=[comment], hate=[comment], comment=Some text
249-- NB: since all values are text, numeric values should be zero-padded to a set amount
250--     so that we can always do range queries.
251
252CREATE TABLE IF NOT EXISTS social_attributes (
253    id INTEGER REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,   -- track id
254    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE, -- DEFERRABLE INITIALLY DEFERRED,
255    k TEXT NOT NULL,
256    v TEXT NOT NULL,
257    timestamp INTEGER NOT NULL DEFAULT 0
258);
259CREATE INDEX social_attrib_id        ON social_attributes(id);
260CREATE INDEX social_attrib_source    ON social_attributes(source);
261CREATE INDEX social_attrib_k         ON social_attributes(k);
262CREATE INDEX social_attrib_timestamp ON social_attributes(timestamp);
263
264
265
266-- playback history
267
268-- if source=null, file is local to this machine
269CREATE TABLE IF NOT EXISTS playback_log (
270    id INTEGER PRIMARY KEY AUTOINCREMENT,
271    source INTEGER REFERENCES source(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
272    track INTEGER REFERENCES track(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
273    playtime INTEGER NOT NULL,              -- when playback finished (timestamp)
274    secs_played INTEGER NOT NULL
275);
276
277CREATE INDEX playback_log_source ON playback_log(source);
278CREATE INDEX playback_log_track ON playback_log(track);
279CREATE INDEX playback_log_playtime ON playback_log(playtime);
280
281
282
283-- auth information for http clients
284
285CREATE TABLE IF NOT EXISTS http_client_auth (
286    token TEXT NOT NULL PRIMARY KEY,
287    website TEXT NOT NULL,
288    name TEXT NOT NULL,
289    ua TEXT,
290    mtime INTEGER,
291    permissions TEXT NOT NULL
292);
293
294
295
296-- Schema version, and misc tomahawk settings relating to the collection db
297
298CREATE TABLE IF NOT EXISTS settings (
299    k TEXT NOT NULL PRIMARY KEY,
300    v TEXT NOT NULL DEFAULT ''
301);
302
303INSERT INTO settings(k,v) VALUES('schema_version', '31');