| Key | Value |
| DropTrackArtist | DROP TABLE IF EXISTS track_artist; |
| CreateTrackArtist | CREATE TABLE track_artist (pid INTEGER NOT NULL, name TEXT, name_order INTEGER, sort_name TEXT, has_songs INTEGER DEFAULT 0, has_music_videos INTEGER DEFAULT 0, has_non_compilation_tracks INTEGER DEFAULT 0, PRIMARY KEY (pid)); |
| PopulateTrackArtist | INSERT INTO track_artist ( name, sort_name, name_order ) SELECT DISTINCT artist, sort_artist, artist_order FROM item GROUP BY artist_order; |
| AddItemTrackArtist | ALTER TABLE item ADD COLUMN track_artist_pid INTEGER DEFAULT 0; |
| LinkItemTrackArtist | UPDATE item SET track_artist_pid = (SELECT pid FROM track_artist WHERE item.artist_order = track_artist.name_order AND item.artist IS NOT NULL); |
| CreateIndexForItemTrackArtistPid | CREATE INDEX idx_item_track_artist_pid ON item (track_artist_pid); |
| CreateTempIndexForItemArtistPid | CREATE INDEX idx_temp_item_artist_pid ON item (artist_pid); |
| AddTrackArtistIsUnknown | ALTER TABLE track_artist ADD COLUMN is_unknown INTEGER DEFAULT 0; |
| CreateUnknownTrackArtist | INSERT INTO track_artist (name, name_order, sort_name, is_unknown) VALUES ('Unknown Artist', 4294967295, 'Unknown Artist', 1); |
| FixUnknownTrackArtists | UPDATE item SET track_artist_pid = (SELECT pid FROM track_artist WHERE track_artist.pid == item.track_artist_pid AND track_artist.name IS NOT NULL); |
| LinkUnknownTrackArtist | UPDATE item SET track_artist_pid = (SELECT pid FROM track_artist WHERE is_unknown == 1) WHERE track_artist_pid IS NULL; |
| UpdateArtistHasNonCompilationTracks | UPDATE track_artist SET has_non_compilation_tracks = (SELECT item.is_compilation FROM item WHERE item.track_artist_pid == track_artist.pid AND item.is_compilation = 0 LIMIT 1) IS NOT NULL; |
| AddArtistIsUnknown | ALTER TABLE artist ADD COLUMN is_unknown INTEGER DEFAULT 0; |
| CreateUnknownArtist | INSERT INTO artist (kind, artwork_status, artwork_album_pid, name, name_order, sort_name, is_unknown) VALUES (2, 0, 0, 'Unknown Artist', 4294967295, 'Unknown Artist', 1); |
| FixUnknownArtists | UPDATE item SET artist_pid = (SELECT artist_pid FROM artist WHERE artist.pid == item.artist_pid AND artist.name IS NOT NULL); |
| LinkUnknownArtist | UPDATE item SET artist_pid = (SELECT pid FROM artist WHERE is_unknown == 1) WHERE artist_pid IS NULL; |
| AddAlbumIsUnknown | ALTER TABLE album ADD COLUMN is_unknown INTEGER DEFAULT 0; |
| CreateUnknownAlbum | INSERT INTO album (name, name_order, kind, artist_pid, artwork_status, artwork_item_pid, user_rating, all_compilations, season_number, is_unknown) VALUES ('Unknown Album', 4294967295, 2, (SELECT pid FROM artist WHERE is_unknown==1), 0, 0, 0, 0, 0, 1); |
| FixUnknownAlbums | UPDATE item SET album_pid = (SELECT album_pid FROM album WHERE album.pid == item.album_pid AND album.name IS NOT NULL); |
| LinkUnknownAlbum | UPDATE item SET album_pid = (SELECT pid FROM album WHERE is_unknown == 1) WHERE album_pid IS NULL; |
| CreateIndexForItemAlbumPid | CREATE INDEX idx_item_album_pid ON item (album_pid); |
| AddGenreIsUnknown | ALTER TABLE genre_map ADD COLUMN is_unknown INTEGER DEFAULT 0; |
| CreateUnknownGenre | INSERT INTO genre_map (genre, genre_order, is_unknown) VALUES ('Unknown Genre', 4294967295, 1); |
| LinkUnknownGenre | UPDATE item SET genre_id = (SELECT id FROM genre_map WHERE is_unknown == 1) WHERE genre_id == 0; |
| AddComposerIsUnknown | ALTER TABLE composer ADD COLUMN is_unknown INTEGER DEFAULT 0; |
| CreateUnknownComposer | INSERT INTO composer (name, name_order, sort_name, is_unknown) VALUES ('Unknown Composer', 4294967295, 'Unknown Composer', 1); |
| LinkUnknownComposer | UPDATE item SET composer_pid = (SELECT pid FROM composer WHERE is_unknown == 1) WHERE composer_pid == 0 ; |
| AddAlbumArtistHasSongs | ALTER TABLE artist ADD COLUMN has_songs INTEGER DEFAULT 0; |
| UpdateAlbumArtistHasSongs | UPDATE artist SET has_songs = (SELECT item.is_song FROM item WHERE item.artist_pid == artist.pid AND item.is_song = 1 LIMIT 1) IS NOT NULL; |
| UpdateArtistHasSongs | UPDATE track_artist SET has_songs = (SELECT item.is_song FROM item WHERE item.track_artist_pid == track_artist.pid AND item.is_song = 1 LIMIT 1) IS NOT NULL; |
| AddAlbumArtistHasMusicVideos | ALTER TABLE artist ADD COLUMN has_music_videos INTEGER DEFAULT 0; |
| UpdateAlbumArtistHasMusicVideos | UPDATE artist SET has_music_videos = (SELECT item.is_music_video FROM item WHERE item.artist_pid == artist.pid AND item.is_music_video = 1 LIMIT 1) IS NOT NULL; |
| UpdateArtistHasMusicVideos | UPDATE track_artist SET has_music_videos = (SELECT item.is_music_video FROM item WHERE item.track_artist_pid == track_artist.pid AND item.is_music_video = 1 LIMIT 1) IS NOT NULL; |
| AddAlbumHasSongs | ALTER TABLE album ADD COLUMN has_songs INTEGER DEFAULT 0; |
| InsertAlbumHasSongs | UPDATE album SET has_songs = (SELECT item.is_song FROM item WHERE item.album_pid == album.pid AND item.is_song == 1 LIMIT 1) IS NOT NULL; |
| AddAlbumHasMusicVideos | ALTER TABLE album ADD COLUMN has_music_videos INTEGER DEFAULT 0; |
| InsertAlbumHasMusicVideos | UPDATE album SET has_music_videos = (SELECT item.is_music_video FROM item WHERE item.album_pid == album.pid AND item.is_music_video == 1 LIMIT 1) IS NOT NULL; |
| AddAlbumSortAlbum | ALTER TABLE album ADD COLUMN sort_order INTEGER DEFAULT 0; |
| InsertAlbumSortAlbum | UPDATE album SET sort_order = (SELECT item.album_order FROM item WHERE item.album_pid == album.pid LIMIT 1); |
| AddAlbumArtistOrder | ALTER TABLE album ADD COLUMN artist_order INTEGER DEFAULT 0; |
| InsertAlbumArtistOrder | UPDATE album SET artist_order = (SELECT name_order FROM artist WHERE album.artist_pid = artist.pid LIMIT 1); |
| AddAlbumAnyCompilation | ALTER TABLE album ADD COLUMN has_any_compilations INTEGER DEFAULT 0; |
| UpdateAlbumAnyCompilation | UPDATE album SET has_any_compilations = (SELECT item.is_compilation FROM item WHERE item.album_pid == album.pid AND item.is_compilation == 1 LIMIT 1) IS NOT NULL; |
| AddGenreHasMusic | ALTER TABLE genre_map ADD COLUMN has_music INTEGER DEFAULT 0; |
| InsertGenreHasMusic | UPDATE genre_map SET has_music = (SELECT is_song FROM item WHERE item.genre_id = genre_map.id AND item.media_kind & 33 LIMIT 1) IS NOT NULL; |
| CreateItemToContainerTempIndex | CREATE INDEX item_to_container_temp_idx ON item_to_container (item_pid, container_pid); |
| AddFallbackSortOrderForItem | ALTER TABLE item ADD COLUMN physical_order INTEGER; |
| PopulateFallbackSortOrderForItem | UPDATE item SET physical_order = (SELECT physical_order FROM item_to_container WHERE container_pid == (SELECT primary_container_pid FROM db_info LIMIT 1) AND item_pid = item.pid); |
| DropItemToContainerTempIndex | DROP INDEX item_to_container_temp_idx; |
| AddTrackArtistAlbumCount | ALTER TABLE track_artist ADD COLUMN album_count INTEGER DEFAULT 0; |
| PopulateTrackArtistAlbumCount | UPDATE track_artist SET album_count = (select COUNT(DISTINCT(album_pid)) FROM item WHERE item.track_artist_pid == track_artist.pid); |
| CreateRentalExpiredColumn | ALTER TABLE item_stats ADD COLUMN rental_expired INTEGER DEFAULT 0; |
| DropTempIndexForItemArtistPid | DROP INDEX idx_temp_item_artist_pid; |
| CreateArtistSortOrderIndex | CREATE INDEX item_artist_sort_order_idx ON item (artist_order,album_order,disc_number,track_number,sort_title,physical_order); |
| CreateItemToContainerPhysicalOrderIndex | CREATE INDEX item_to_container_physical_order_idx ON item_to_container (physical_order); |
| CreateItemToContainerContainerPIDIndex | CREATE INDEX item_to_container_container_pid_idx ON item_to_container (container_pid); |
| CreateItemTitleOrderIndex | CREATE INDEX item_title_order_idx ON item (title_order); |
| CreateItemAlbumOrderIndex | CREATE INDEX item_album_order_idx ON item (album_order,disc_number,track_number,artist_order,sort_title,physical_order); |
| CreateItemGenreIdIndex | CREATE INDEX item_genre_id_idx ON item (genre_id); |
| AddComposerHasMusic | ALTER TABLE composer ADD COLUMN has_music INTEGER DEFAULT 0; |
| InsertComposerHasMusic | UPDATE composer SET has_music = (SELECT is_song FROM item WHERE item.composer_pid = composer.pid AND item.media_kind & 33 LIMIT 1) IS NOT NULL; |
| CreateAlbumSortName | ALTER TABLE album ADD COLUMN sort_name TEXT; |
| UpdateAlbumSortName | UPDATE album SET sort_name = (SELECT DISTINCT sort_album from item WHERE album.pid = item.album_pid LIMIT 1); |
| AddItemHasLyrics | ALTER TABLE item ADD COLUMN has_lyrics INTEGER DEFAULT 0; |
| UpdateItemHasLyrics | UPDATE item SET has_lyrics = (SELECT item_pid FROM lyrics WHERE item.pid = lyrics.item_pid) IS NOT NULL; |
| AddItemDateReleased | ALTER TABLE item ADD COLUMN date_released INTEGER DEFAULT 0; |
| UpdateItemDateReleased | UPDATE item SET date_released = (SELECT COALESCE(store_info.date_released, podcast_info.date_released, 0) FROM item AS item2 LEFT OUTER JOIN store_info ON item.pid = store_info.item_pid LEFT OUTER JOIN podcast_info ON item.pid = podcast_info.item_pid WHERE item2.pid = item.pid); |
| FixUnknownTVSeries | UPDATE video_info SET series_name = (SELECT item.title FROM item WHERE video_info.item_pid = item.pid) WHERE video_info.series_name IS NULL; |
| CreateArtistsForTVSeries | INSERT INTO track_artist(name, sort_name) SELECT DISTINCT video_info.series_name, video_info.sort_series_name FROM video_info, item WHERE item.pid == video_info.item_pid AND item.is_tv_show; |
| UpdateArtistForTVSeries | UPDATE item SET artist = (SELECT series_name FROM video_info WHERE item.pid = video_info.item_pid) WHERE item.is_tv_show = 1; |
| UpdateSortArtistForTVSeries | UPDATE item SET sort_artist = (SELECT sort_series_name FROM video_info WHERE item.pid = video_info.item_pid) WHERE item.is_tv_show = 1; |
| UpdateTrackArtistPidForTVSeries | UPDATE item SET track_artist_pid = (SELECT pid FROM track_artist WHERE item.artist = track_artist.name AND track_artist.name_order IS NULL) WHERE item.is_tv_show = 1; |
| CreatePlayCountUserOriginal | ALTER TABLE item_stats ADD COLUMN play_count_user_original INTEGER DEFAULT 0; |
| CreateSkipCountUserOriginal | ALTER TABLE item_stats ADD COLUMN skip_count_user_original INTEGER DEFAULT 0; |
| UpdatePlayCountUserOriginal | UPDATE item_stats SET play_count_user_original = play_count_user; |
| UpdateSkipCountUserOriginal | UPDATE item_stats SET skip_count_user_original = skip_count_user; |
| DropTrackSize | DROP TABLE IF EXISTS track_size_calc; |
| CreateTrackSize | CREATE TABLE track_size_calc (pid INTEGER NOT NULL, kind TEXT NOT NULL, size INTEGER NOT NULL, PRIMARY KEY (pid), UNIQUE (kind)); |
| PopulateTrackSizeAudio | INSERT INTO track_size_calc ( kind, size ) SELECT 'audio' AS kind, COALESCE(SUM(location.file_size), 0) FROM location, item WHERE item.pid = location.item_pid AND item.media_kind & 3146029; |
| PopulateTrackSizeVideo | INSERT INTO track_size_calc ( kind, size ) SELECT 'video' AS kind, COALESCE(SUM(location.file_size), 0) FROM location, item WHERE item.pid = location.item_pid AND item.media_kind & 3682; |
| PopulateTrackSizeMusicVideo | INSERT INTO track_size_calc ( kind, size ) SELECT 'music_video' AS kind, COALESCE(SUM(location.file_size), 0) FROM location, item WHERE item.pid = location.item_pid AND item.media_kind & 32; |
| AddAlbumArtistCount | ALTER TABLE album ADD COLUMN artist_count_calc INTEGER DEFAULT 0 NOT NULL; |
| UpdateAlbumArtistCount | UPDATE album SET artist_count_calc = COALESCE ((SELECT COUNT(DISTINCT item.artist_pid) FROM item WHERE item.album_pid = album.pid GROUP BY item.album_pid), 0); |
| AddGenreMapArtistCount | ALTER TABLE genre_map ADD COLUMN artist_count_calc INTEGER DEFAULT 0 NOT NULL; |
| AddGenreMapAlbumCount | ALTER TABLE genre_map ADD COLUMN album_count_calc INTEGER DEFAULT 0 NOT NULL; |
| UpdateGenreMapArtistCount | UPDATE genre_map SET artist_count_calc = COALESCE((SELECT COUNT(DISTINCT item.track_artist_pid) FROM item WHERE item.media_kind & 33 AND item.genre_id = genre_map.id GROUP BY item.genre_id), 0); |
| UpdateGenreMapAlbumCount | UPDATE genre_map SET album_count_calc = COALESCE((SELECT COUNT(DISTINCT item.album_pid) FROM item WHERE item.media_kind & 33 AND item.genre_id = genre_map.id GROUP BY item.genre_id), 0); |
| UpdateDeviceUpdateLevelTo1100 | UPDATE version_info SET device_update_level = 1100; |
