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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
|
/*
* $Id: $
* Commands to update database to new version
*
*/
#include <stdio.h>
#include "daapd.h"
char *db_sqlite_updates[] = {
/* version 0 -> version 1 -- initial update */
"CREATE TABLE songs (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" path VARCHAR(4096) UNIQUE NOT NULL,\n"
" fname VARCHAR(255) NOT NULL,\n"
" title VARCHAR(1024) DEFAULT NULL,\n"
" artist VARCHAR(1024) DEFAULT NULL,\n"
" album VARCHAR(1024) DEFAULT NULL,\n"
" genre VARCHAR(255) DEFAULT NULL,\n"
" comment VARCHAR(4096) DEFAULT NULL,\n"
" type VARCHAR(255) DEFAULT NULL,\n"
" composer VARCHAR(1024) DEFAULT NULL,\n"
" orchestra VARCHAR(1024) DEFAULT NULL,\n"
" conductor VARCHAR(1024) DEFAULT NULL,\n"
" grouping VARCHAR(1024) DEFAULT NULL,\n"
" url VARCHAR(1024) DEFAULT NULL,\n"
" bitrate INTEGER DEFAULT 0,\n"
" samplerate INTEGER DEFAULT 0,\n"
" song_length INTEGER DEFAULT 0,\n"
" file_size INTEGER DEFAULT 0,\n"
" year INTEGER DEFAULT 0,\n"
" track INTEGER DEFAULT 0,\n"
" total_tracks INTEGER DEFAULT 0,\n"
" disc INTEGER DEFAULT 0,\n"
" total_discs INTEGER DEFAULT 0,\n"
" bpm INTEGER DEFAULT 0,\n"
" compilation INTEGER DEFAULT 0,\n"
" rating INTEGER DEFAULT 0,\n"
" play_count INTEGER DEFAULT 0,\n"
" data_kind INTEGER DEFAULT 0,\n"
" item_kind INTEGER DEFAULT 0,\n"
" description INTEGER DEFAULT 0,\n"
" time_added INTEGER DEFAULT 0,\n"
" time_modified INTEGER DEFAULT 0,\n"
" time_played INTEGER DEFAULT 0,\n"
" db_timestamp INTEGER DEFAULT 0,\n"
" disabled INTEGER DEFAULT 0,\n"
" sample_count INTEGER DEFAULT 0,\n"
" force_update INTEGER DEFAULT 0\n"
");\n"
"CREATE INDEX idx_path ON songs(path);\n"
"CREATE TABLE config (\n"
" term VARCHAR(255) NOT NULL,\n"
" subterm VARCHAR(255) DEFAULT NULL,\n"
" value VARCHAR(1024) NOT NULL\n"
");\n"
"CREATE TABLE playlists (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" title VARCHAR(255) NOT NULL,\n"
" smart INTEGER NOT NULL,\n"
" items INTEGER NOT NULL,\n"
" query VARCHAR(1024)\n"
");\n"
"CREATE TABLE playlistitems (\n"
" id INTEGER NOT NULL,\n"
" songid INTEGER NOT NULL\n"
");\n"
"INSERT INTO config VALUES ('version','','1');\n"
"INSERT INTO playlists VALUES (1,'Library',1,0,'1');\n",
/* version 1 -> version 2 */
/* force rescan for invalid utf-8 data */
"REPLACE INTO config VALUES('rescan',NULL,1);\n"
"UPDATE config SET value=2 WHERE term='version';\n",
/* version 2 -> version 3 */
/* add daap.songcodectype, normalize daap.songformat and daap.songdescription */
"drop index idx_path;\n"
"create temp table tempsongs as select * from songs;\n"
"drop table songs;\n"
"CREATE TABLE songs (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" path VARCHAR(4096) UNIQUE NOT NULL,\n"
" fname VARCHAR(255) NOT NULL,\n"
" title VARCHAR(1024) DEFAULT NULL,\n"
" artist VARCHAR(1024) DEFAULT NULL,\n"
" album VARCHAR(1024) DEFAULT NULL,\n"
" genre VARCHAR(255) DEFAULT NULL,\n"
" comment VARCHAR(4096) DEFAULT NULL,\n"
" type VARCHAR(255) DEFAULT NULL,\n"
" composer VARCHAR(1024) DEFAULT NULL,\n"
" orchestra VARCHAR(1024) DEFAULT NULL,\n"
" conductor VARCHAR(1024) DEFAULT NULL,\n"
" grouping VARCHAR(1024) DEFAULT NULL,\n"
" url VARCHAR(1024) DEFAULT NULL,\n"
" bitrate INTEGER DEFAULT 0,\n"
" samplerate INTEGER DEFAULT 0,\n"
" song_length INTEGER DEFAULT 0,\n"
" file_size INTEGER DEFAULT 0,\n"
" year INTEGER DEFAULT 0,\n"
" track INTEGER DEFAULT 0,\n"
" total_tracks INTEGER DEFAULT 0,\n"
" disc INTEGER DEFAULT 0,\n"
" total_discs INTEGER DEFAULT 0,\n"
" bpm INTEGER DEFAULT 0,\n"
" compilation INTEGER DEFAULT 0,\n"
" rating INTEGER DEFAULT 0,\n"
" play_count INTEGER DEFAULT 0,\n"
" data_kind INTEGER DEFAULT 0,\n"
" item_kind INTEGER DEFAULT 0,\n"
" description INTEGER DEFAULT 0,\n"
" time_added INTEGER DEFAULT 0,\n"
" time_modified INTEGER DEFAULT 0,\n"
" time_played INTEGER DEFAULT 0,\n"
" db_timestamp INTEGER DEFAULT 0,\n"
" disabled INTEGER DEFAULT 0,\n"
" sample_count INTEGER DEFAULT 0,\n"
" force_update INTEGER DEFAULT 0,\n"
" codectype VARCHAR(5) DEFAULT NULL\n"
");\n"
"begin transaction;\n"
"insert into songs select *,NULL from tempsongs;\n"
"commit transaction;\n"
"update songs set type=lower(type);\n"
"update songs set type='m4a' where type='aac' or type='mp4';\n"
"update songs set type='flac' where type='fla';\n"
"update songs set type='mpc' where type='mpp';\n"
"update songs set type='mpc' where type='mp+';\n"
"update songs set description='AAC audio file' where type='m4a';\n"
"update songs set description='MPEG audio file' where type='mp3';\n"
"update songs set description='WAV audio file' where type='wav';\n"
"update songs set description='Playlist URL' where type='pls';\n"
"update songs set description='Ogg Vorbis audio file' where type='ogg';\n"
"update songs set description='FLAC audio file' where type='flac';\n"
"update songs set description='Musepack audio file' where type='mpc';\n"
"update songs set codectype='mp4a' where type='m4a' or type='m4p';\n"
"update songs set codectype='mpeg' where type='mp3';\n"
"update songs set codectype='ogg' where type='ogg';\n"
"update songs set codectype='flac' where type='flac';\n"
"update songs set codectype='mpc' where type='mpc';\n"
"update songs set force_update=1 where type='m4a';\n" /* look for alac */
"create index idx_path on songs(path);\n"
"drop table tempsongs;\n"
"update config set value=3 where term='version';\n",
/* version 3 -> version 4 */
/* add db_timestamp and path to playlist table */
"create temp table tempplaylists as select * from playlists;\n"
"drop table playlists;\n"
"CREATE TABLE playlists (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" title VARCHAR(255) NOT NULL,\n"
" type INTEGER NOT NULL,\n"
" items INTEGER NOT NULL,\n"
" query VARCHAR(1024),\n"
" db_timestamp INTEGER NOT NULL,\n"
" path VARCHAR(4096)\n"
");\n"
"insert into playlists select *,0,NULL from tempplaylists;\n"
"drop table tempplaylists;\n"
"update config set value=4 where term='version';\n",
/* version 4 -> version 5 */
/* add index to playlist table */
"create temp table tempplaylists as select * from playlists;\n"
"drop table playlists;\n"
"CREATE TABLE playlists (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" title VARCHAR(255) NOT NULL,\n"
" type INTEGER NOT NULL,\n"
" items INTEGER NOT NULL,\n"
" query VARCHAR(1024),\n"
" db_timestamp INTEGER NOT NULL,\n"
" path VARCHAR(4096),\n"
" idx INTEGER NOT NULL\n"
");\n"
"insert into playlists select *,0 from tempplaylists;\n"
"drop table tempplaylists;\n"
"update config set value=5 where term='version';\n",
/* version 5 -> version 6 */
"drop index idx_path;\n"
"create temp table tempsongs as select * from songs;\n"
"drop table songs;\n"
"CREATE TABLE songs (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" path VARCHAR(4096) UNIQUE NOT NULL,\n"
" fname VARCHAR(255) NOT NULL,\n"
" title VARCHAR(1024) DEFAULT NULL,\n"
" artist VARCHAR(1024) DEFAULT NULL,\n"
" album VARCHAR(1024) DEFAULT NULL,\n"
" genre VARCHAR(255) DEFAULT NULL,\n"
" comment VARCHAR(4096) DEFAULT NULL,\n"
" type VARCHAR(255) DEFAULT NULL,\n"
" composer VARCHAR(1024) DEFAULT NULL,\n"
" orchestra VARCHAR(1024) DEFAULT NULL,\n"
" conductor VARCHAR(1024) DEFAULT NULL,\n"
" grouping VARCHAR(1024) DEFAULT NULL,\n"
" url VARCHAR(1024) DEFAULT NULL,\n"
" bitrate INTEGER DEFAULT 0,\n"
" samplerate INTEGER DEFAULT 0,\n"
" song_length INTEGER DEFAULT 0,\n"
" file_size INTEGER DEFAULT 0,\n"
" year INTEGER DEFAULT 0,\n"
" track INTEGER DEFAULT 0,\n"
" total_tracks INTEGER DEFAULT 0,\n"
" disc INTEGER DEFAULT 0,\n"
" total_discs INTEGER DEFAULT 0,\n"
" bpm INTEGER DEFAULT 0,\n"
" compilation INTEGER DEFAULT 0,\n"
" rating INTEGER DEFAULT 0,\n"
" play_count INTEGER DEFAULT 0,\n"
" data_kind INTEGER DEFAULT 0,\n"
" item_kind INTEGER DEFAULT 0,\n"
" description INTEGER DEFAULT 0,\n"
" time_added INTEGER DEFAULT 0,\n"
" time_modified INTEGER DEFAULT 0,\n"
" time_played INTEGER DEFAULT 0,\n"
" db_timestamp INTEGER DEFAULT 0,\n"
" disabled INTEGER DEFAULT 0,\n"
" sample_count INTEGER DEFAULT 0,\n"
" force_update INTEGER DEFAULT 0,\n"
" codectype VARCHAR(5) DEFAULT NULL,\n"
" idx INTEGER NOT NULL\n"
");\n"
"begin transaction;\n"
"insert into songs select *,0 from tempsongs;\n"
"commit transaction;\n"
"create index idx_path on songs(path);\n"
"drop table tempsongs;\n"
"update config set value=6 where term='version';\n",
/* version 6 -> version 7 */
"create temp table tempitems as select * from playlistitems;\n"
"drop table playlistitems;\n"
"CREATE TABLE playlistitems (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" playlistid INTEGER NOT NULL,\n"
" songid INTEGER NOT NULL\n"
");\n"
"insert into playlistitems (playlistid, songid) select * from tempitems;\n"
"drop table tempitems;\n"
"update config set value=7 where term='version';\n",
/* version 7 -> version 8 */
"create index idx_songid on playlistitems(songid)\n"
"create index idx_playlistid on playlistitems(playlistid)\n"
"update config set value=8 where term='version';\n",
/* version 8 -> version 9 */
"drop index idx_path;\n"
"create temp table tempsongs as select * from songs;\n"
"drop table songs;\n"
"CREATE TABLE songs (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" path VARCHAR(4096) UNIQUE NOT NULL,\n"
" fname VARCHAR(255) NOT NULL,\n"
" title VARCHAR(1024) DEFAULT NULL,\n"
" artist VARCHAR(1024) DEFAULT NULL,\n"
" album VARCHAR(1024) DEFAULT NULL,\n"
" genre VARCHAR(255) DEFAULT NULL,\n"
" comment VARCHAR(4096) DEFAULT NULL,\n"
" type VARCHAR(255) DEFAULT NULL,\n"
" composer VARCHAR(1024) DEFAULT NULL,\n"
" orchestra VARCHAR(1024) DEFAULT NULL,\n"
" conductor VARCHAR(1024) DEFAULT NULL,\n"
" grouping VARCHAR(1024) DEFAULT NULL,\n"
" url VARCHAR(1024) DEFAULT NULL,\n"
" bitrate INTEGER DEFAULT 0,\n"
" samplerate INTEGER DEFAULT 0,\n"
" song_length INTEGER DEFAULT 0,\n"
" file_size INTEGER DEFAULT 0,\n"
" year INTEGER DEFAULT 0,\n"
" track INTEGER DEFAULT 0,\n"
" total_tracks INTEGER DEFAULT 0,\n"
" disc INTEGER DEFAULT 0,\n"
" total_discs INTEGER DEFAULT 0,\n"
" bpm INTEGER DEFAULT 0,\n"
" compilation INTEGER DEFAULT 0,\n"
" rating INTEGER DEFAULT 0,\n"
" play_count INTEGER DEFAULT 0,\n"
" data_kind INTEGER DEFAULT 0,\n"
" item_kind INTEGER DEFAULT 0,\n"
" description INTEGER DEFAULT 0,\n"
" time_added INTEGER DEFAULT 0,\n"
" time_modified INTEGER DEFAULT 0,\n"
" time_played INTEGER DEFAULT 0,\n"
" db_timestamp INTEGER DEFAULT 0,\n"
" disabled INTEGER DEFAULT 0,\n"
" sample_count INTEGER DEFAULT 0,\n"
" force_update INTEGER DEFAULT 0,\n"
" codectype VARCHAR(5) DEFAULT NULL,\n"
" idx INTEGER NOT NULL,\n"
" has_video INTEGER DEFAULT 0,\n"
" contentrating INTEGER DEFAULT 0\n"
");\n"
"begin transaction;\n"
"insert into songs select *,0,0 from tempsongs;\n"
"commit transaction;\n"
"update songs set has_video=1 where fname like '%.m4v';\n"
"create index idx_path on songs(path);\n"
"drop table tempsongs;\n"
"update config set value=9 where term='version';\n",
/* version 9 -> version 10 */
"drop index idx_path;\n"
"create temp table tempsongs as select * from songs;\n"
"drop table songs;\n"
"CREATE TABLE songs (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" path VARCHAR(4096) NOT NULL,\n"
" fname VARCHAR(255) NOT NULL,\n"
" title VARCHAR(1024) DEFAULT NULL,\n"
" artist VARCHAR(1024) DEFAULT NULL,\n"
" album VARCHAR(1024) DEFAULT NULL,\n"
" genre VARCHAR(255) DEFAULT NULL,\n"
" comment VARCHAR(4096) DEFAULT NULL,\n"
" type VARCHAR(255) DEFAULT NULL,\n"
" composer VARCHAR(1024) DEFAULT NULL,\n"
" orchestra VARCHAR(1024) DEFAULT NULL,\n"
" conductor VARCHAR(1024) DEFAULT NULL,\n"
" grouping VARCHAR(1024) DEFAULT NULL,\n"
" url VARCHAR(1024) DEFAULT NULL,\n"
" bitrate INTEGER DEFAULT 0,\n"
" samplerate INTEGER DEFAULT 0,\n"
" song_length INTEGER DEFAULT 0,\n"
" file_size INTEGER DEFAULT 0,\n"
" year INTEGER DEFAULT 0,\n"
" track INTEGER DEFAULT 0,\n"
" total_tracks INTEGER DEFAULT 0,\n"
" disc INTEGER DEFAULT 0,\n"
" total_discs INTEGER DEFAULT 0,\n"
" bpm INTEGER DEFAULT 0,\n"
" compilation INTEGER DEFAULT 0,\n"
" rating INTEGER DEFAULT 0,\n"
" play_count INTEGER DEFAULT 0,\n"
" data_kind INTEGER DEFAULT 0,\n"
" item_kind INTEGER DEFAULT 0,\n"
" description INTEGER DEFAULT 0,\n"
" time_added INTEGER DEFAULT 0,\n"
" time_modified INTEGER DEFAULT 0,\n"
" time_played INTEGER DEFAULT 0,\n"
" db_timestamp INTEGER DEFAULT 0,\n"
" disabled INTEGER DEFAULT 0,\n"
" sample_count INTEGER DEFAULT 0,\n"
" force_update INTEGER DEFAULT 0,\n"
" codectype VARCHAR(5) DEFAULT NULL,\n"
" idx INTEGER NOT NULL,\n"
" has_video INTEGER DEFAULT 0,\n"
" contentrating INTEGER DEFAULT 0\n"
");\n"
"begin transaction;\n"
"insert into songs select * from tempsongs;\n"
"commit transaction;\n"
"create index idx_path on songs(path,idx);\n"
"drop table tempsongs;\n"
"update config set value=10 where term='version';\n",
/* version 10 -> version 11 */
"drop index idx_playlistid;\n"
"create index idx_playlistid on playlistitems(playlistid,songid);\n"
"update config set value=11 where term='version';\n",
/* version 11 -> version 12 */
"REPLACE INTO config VALUES('rescan',NULL,1);\n"
"UPDATE config SET value=12 WHERE term='version';\n",
/* version 12 -> version 13 */
"create temp table tempsongs as select * from songs;\n"
"drop table songs;\n"
"CREATE TABLE songs (\n"
" id INTEGER PRIMARY KEY NOT NULL,\n"
" path VARCHAR(4096) NOT NULL,\n"
" fname VARCHAR(255) NOT NULL,\n"
" title VARCHAR(1024) DEFAULT NULL,\n"
" artist VARCHAR(1024) DEFAULT NULL,\n"
" album VARCHAR(1024) DEFAULT NULL,\n"
" genre VARCHAR(255) DEFAULT NULL,\n"
" comment VARCHAR(4096) DEFAULT NULL,\n"
" type VARCHAR(255) DEFAULT NULL,\n"
" composer VARCHAR(1024) DEFAULT NULL,\n"
" orchestra VARCHAR(1024) DEFAULT NULL,\n"
" conductor VARCHAR(1024) DEFAULT NULL,\n"
" grouping VARCHAR(1024) DEFAULT NULL,\n"
" url VARCHAR(1024) DEFAULT NULL,\n"
" bitrate INTEGER DEFAULT 0,\n"
" samplerate INTEGER DEFAULT 0,\n"
" song_length INTEGER DEFAULT 0,\n"
" file_size INTEGER DEFAULT 0,\n"
" year INTEGER DEFAULT 0,\n"
" track INTEGER DEFAULT 0,\n"
" total_tracks INTEGER DEFAULT 0,\n"
" disc INTEGER DEFAULT 0,\n"
" total_discs INTEGER DEFAULT 0,\n"
" bpm INTEGER DEFAULT 0,\n"
" compilation INTEGER DEFAULT 0,\n"
" rating INTEGER DEFAULT 0,\n"
" play_count INTEGER DEFAULT 0,\n"
" data_kind INTEGER DEFAULT 0,\n"
" item_kind INTEGER DEFAULT 0,\n"
" description INTEGER DEFAULT 0,\n"
" time_added INTEGER DEFAULT 0,\n"
" time_modified INTEGER DEFAULT 0,\n"
" time_played INTEGER DEFAULT 0,\n"
" db_timestamp INTEGER DEFAULT 0,\n"
" disabled INTEGER DEFAULT 0,\n"
" sample_count INTEGER DEFAULT 0,\n"
" force_update INTEGER DEFAULT 0,\n"
" codectype VARCHAR(5) DEFAULT NULL,\n"
" idx INTEGER NOT NULL,\n"
" has_video INTEGER DEFAULT 0,\n"
" contentrating INTEGER DEFAULT 0,\n"
" bits_per_sample INTEGER DEFAULT 0,\n"
" album_artist VARCHAR(1024)\n"
");\n"
"begin transaction;\n"
"insert into songs select *,0,'' from tempsongs;\n"
"commit transaction;\n"
"drop table tempsongs;\n"
"update config set value=13 where term='version';\n",
NULL /* No more versions! */
};
|
