1DROP VIEW IF EXISTS duplicated_songs;
2
3DROP INDEX IF EXISTS idx_url;
4
5DROP INDEX IF EXISTS idx_comp_artist;
6
7DROP INDEX IF EXISTS idx_albumartist;
8
9DROP INDEX IF EXISTS idx_artist;
10
11DROP INDEX IF EXISTS idx_album;
12
13DROP INDEX IF EXISTS idx_title;
14
15ALTER TABLE songs RENAME TO songs_old;
16
17ALTER TABLE subsonic_songs RENAME TO subsonic_songs_old;
18
19ALTER TABLE playlist_items RENAME TO playlist_items_old;
20
21CREATE TABLE songs (
22
23  title TEXT,
24  album TEXT,
25  artist TEXT,
26  albumartist TEXT,
27  track INTEGER NOT NULL DEFAULT -1,
28  disc INTEGER NOT NULL DEFAULT -1,
29  year INTEGER NOT NULL DEFAULT -1,
30  originalyear INTEGER NOT NULL DEFAULT 0,
31  genre TEXT,
32  compilation INTEGER NOT NULL DEFAULT 0,
33  composer TEXT,
34  performer TEXT,
35  grouping TEXT,
36  comment TEXT,
37  lyrics TEXT,
38
39  artist_id TEXT,
40  album_id TEXT,
41  song_id TEXT,
42
43  beginning INTEGER NOT NULL DEFAULT 0,
44  length INTEGER NOT NULL DEFAULT 0,
45
46  bitrate INTEGER NOT NULL DEFAULT -1,
47  samplerate INTEGER NOT NULL DEFAULT -1,
48  bitdepth INTEGER NOT NULL DEFAULT -1,
49
50  source INTEGER NOT NULL DEFAULT 0,
51  directory_id INTEGER NOT NULL DEFAULT -1,
52  url TEXT NOT NULL,
53  filetype INTEGER NOT NULL DEFAULT 0,
54  filesize INTEGER NOT NULL DEFAULT -1,
55  mtime INTEGER NOT NULL DEFAULT -1,
56  ctime INTEGER NOT NULL DEFAULT -1,
57  unavailable INTEGER DEFAULT 0,
58
59  playcount INTEGER NOT NULL DEFAULT 0,
60  skipcount INTEGER NOT NULL DEFAULT 0,
61  lastplayed INTEGER NOT NULL DEFAULT -1,
62
63  compilation_detected INTEGER DEFAULT 0,
64  compilation_on INTEGER NOT NULL DEFAULT 0,
65  compilation_off INTEGER NOT NULL DEFAULT 0,
66  compilation_effective INTEGER NOT NULL DEFAULT 0,
67
68  art_automatic TEXT,
69  art_manual TEXT,
70
71  effective_albumartist TEXT,
72  effective_originalyear INTEGER NOT NULL DEFAULT 0,
73
74  cue_path TEXT
75
76);
77
78CREATE INDEX IF NOT EXISTS idx_url ON songs (url);
79
80CREATE INDEX IF NOT EXISTS idx_comp_artist ON songs (compilation_effective, artist);
81
82CREATE INDEX IF NOT EXISTS idx_albumartist ON songs (albumartist);
83
84CREATE INDEX IF NOT EXISTS idx_artist ON songs (artist);
85
86CREATE INDEX IF NOT EXISTS idx_album ON songs (album);
87
88CREATE INDEX IF NOT EXISTS idx_title ON songs (title);
89
90CREATE VIEW duplicated_songs as select artist dup_artist, album dup_album, title dup_title from songs as inner_songs where artist != '' and album != '' and title != '' and unavailable = 0 group by artist, album , title having count(*) > 1;
91
92CREATE TABLE subsonic_songs (
93
94  title TEXT,
95  album TEXT,
96  artist TEXT,
97  albumartist TEXT,
98  track INTEGER NOT NULL DEFAULT -1,
99  disc INTEGER NOT NULL DEFAULT -1,
100  year INTEGER NOT NULL DEFAULT -1,
101  originalyear INTEGER NOT NULL DEFAULT 0,
102  genre TEXT,
103  compilation INTEGER NOT NULL DEFAULT 0,
104  composer TEXT,
105  performer TEXT,
106  grouping TEXT,
107  comment TEXT,
108  lyrics TEXT,
109
110  artist_id TEXT,
111  album_id TEXT,
112  song_id TEXT,
113
114  beginning INTEGER NOT NULL DEFAULT 0,
115  length INTEGER NOT NULL DEFAULT 0,
116
117  bitrate INTEGER NOT NULL DEFAULT -1,
118  samplerate INTEGER NOT NULL DEFAULT -1,
119  bitdepth INTEGER NOT NULL DEFAULT -1,
120
121  source INTEGER NOT NULL DEFAULT 0,
122  directory_id INTEGER NOT NULL DEFAULT -1,
123  url TEXT NOT NULL,
124  filetype INTEGER NOT NULL DEFAULT 0,
125  filesize INTEGER NOT NULL DEFAULT -1,
126  mtime INTEGER NOT NULL DEFAULT -1,
127  ctime INTEGER NOT NULL DEFAULT -1,
128  unavailable INTEGER DEFAULT 0,
129
130  playcount INTEGER NOT NULL DEFAULT 0,
131  skipcount INTEGER NOT NULL DEFAULT 0,
132  lastplayed INTEGER NOT NULL DEFAULT -1,
133
134  compilation_detected INTEGER DEFAULT 0,
135  compilation_on INTEGER NOT NULL DEFAULT 0,
136  compilation_off INTEGER NOT NULL DEFAULT 0,
137  compilation_effective INTEGER NOT NULL DEFAULT 0,
138
139  art_automatic TEXT,
140  art_manual TEXT,
141
142  effective_albumartist TEXT,
143  effective_originalyear INTEGER NOT NULL DEFAULT 0,
144
145  cue_path TEXT
146
147);
148
149INSERT INTO songs (ROWID, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, artist_id, album_id, song_id, beginning, length, bitrate, samplerate, bitdepth, source, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path)
150SELECT ROWID, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, artist_id, album_id, song_id, beginning, length, bitrate, samplerate, bitdepth, source, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path
151FROM songs_old;
152
153DROP TABLE songs_old;
154
155DELETE FROM songs_fts;
156
157INSERT INTO songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment)
158SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment
159FROM songs;
160
161INSERT INTO subsonic_songs (ROWID, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, artist_id, album_id, song_id, beginning, length, bitrate, samplerate, bitdepth, source, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path)
162SELECT ROWID, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, artist_id, album_id, song_id, beginning, length, bitrate, samplerate, bitdepth, source, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path
163FROM subsonic_songs_old;
164
165DROP TABLE subsonic_songs_old;
166
167DELETE FROM subsonic_songs_fts;
168
169INSERT INTO subsonic_songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment)
170SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment
171FROM subsonic_songs;
172
173CREATE TABLE playlist_items (
174
175  playlist INTEGER NOT NULL,
176  type INTEGER NOT NULL DEFAULT 0,
177  collection_id INTEGER,
178  playlist_url TEXT,
179
180  title TEXT,
181  album TEXT,
182  artist TEXT,
183  albumartist TEXT,
184  track INTEGER,
185  disc INTEGER,
186  year INTEGER,
187  originalyear INTEGER,
188  genre TEXT,
189  compilation INTEGER DEFAULT 0,
190  composer TEXT,
191  performer TEXT,
192  grouping TEXT,
193  comment TEXT,
194  lyrics TEXT,
195
196  artist_id TEXT,
197  album_id TEXT,
198  song_id TEXT,
199
200  beginning INTEGER,
201  length INTEGER,
202
203  bitrate INTEGER,
204  samplerate INTEGER,
205  bitdepth INTEGER,
206
207  source INTEGER,
208  directory_id INTEGER,
209  url TEXT,
210  filetype INTEGER,
211  filesize INTEGER,
212  mtime INTEGER,
213  ctime INTEGER,
214  unavailable INTEGER DEFAULT 0,
215
216  playcount INTEGER DEFAULT 0,
217  skipcount INTEGER DEFAULT 0,
218  lastplayed INTEGER DEFAULT 0,
219
220  compilation_detected INTEGER DEFAULT 0,
221  compilation_on INTEGER DEFAULT 0,
222  compilation_off INTEGER DEFAULT 0,
223  compilation_effective INTEGER DEFAULT 0,
224
225  art_automatic TEXT,
226  art_manual TEXT,
227
228  effective_albumartist TEXT,
229  effective_originalyear INTEGER,
230
231  cue_path TEXT
232
233);
234
235INSERT INTO playlist_items (ROWID, playlist, type, collection_id, playlist_url, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, artist_id, album_id, song_id, beginning, length, bitrate, samplerate, bitdepth, source, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path)
236SELECT ROWID, playlist, type, collection_id, playlist_url, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, artist_id, album_id, song_id, beginning, length, bitrate, samplerate, bitdepth, source, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path
237FROM playlist_items_old;
238
239DROP TABLE playlist_items_old;
240
241DELETE FROM playlist_items_fts_;
242
243INSERT INTO playlist_items_fts_ (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment)
244SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment
245FROM playlist_items;
246
247UPDATE schema_version SET version=11;
248