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