Some 3.1.2 Beta database tools

[Owyn]

Here are 3 tools which have been in private beta for a while but will not be released to general Addons until 3.1.2 is released.

3.1.2 includes a lot of SQL Trigger cleanups which were complete as of build 1276.

The links provide the latest released MM3 MMIP installers for the tools. There are no known problems with them at this time.

DB_Audit
Prepare an audit report of a MM3 Sqlite database. The new triggers clean-up for new deletes but don't clean up old orphaned rows.
Use Tools->Scripts->MM3 Database Audit Report, then, select folder for report.

DB_Clean
Cleans most of the errors reported by DB_Audit. Should only need to be run once. Re-running DB_Audit should produce a clean report.
Use Tools->Scripts->MM3 Database Cleanup, then, select folder for report.

DB_Schema
Prepare a sorted, formatted Database Schema dump. This format is suitable for use by file difference utilities such as ExamDiff.
It is also useful documentation while working on scripts.
Use Tools->Scripts->MM3 Database Schema Report, then, select folder for report.

[Owyn]

For reference this is a DB_Schema report for 3.1.1.1261 (i.e. the starting point for 3.1.2 changes).

Code: Select all

DB_Schema.vbs(1.0.4):Create Database Schema Report for MM3
Report File: C:\Users\Ian\Documents\MM3 Scripts\DB_Schema Reports\DB_Schema_Report(3.1.1.66797).txt
Database: C:\Users\Ian\AppData\Local\MediaMonkey\MM.DB
DB Info:     3 1 20 1690673015
MediaMonkey: 3.1.1.1261
SQLite:      3.5.4
Run: 19/10/2009

CREATE TABLE Albums (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,Artist TEXT COLLATE IUNICODE
    ,Album TEXT COLLATE IUNICODE
    ,Year INTEGER
    ,Comment TEXT COLLATE IUNICODE
    ,Tracks INTEGER DEFAULT 0)
CREATE TRIGGER delete_albums DELETE ON Albums
BEGIN
  DELETE FROM ArtistsAlbums WHERE ArtistsAlbums.IDAlbum=old.ID;
END
CREATE INDEX idxAlbumsAlbum ON Albums (Album)

CREATE TABLE Artists (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,Artist TEXT COLLATE IUNICODE
    ,Comment TEXT COLLATE IUNICODE
    ,Tracks INTEGER DEFAULT 0
    ,Albums INTEGER DEFAULT 0
    ,Authors INTEGER DEFAULT 0
    ,Conducts INTEGER DEFAULT 0
    ,Lyrics INTEGER DEFAULT 0)
CREATE INDEX idxArtistsArtist ON Artists (Artist)
CREATE INDEX idxArtistsAuthors ON Artists (Authors)
CREATE INDEX idxArtistsConducts ON Artists (Conducts)
CREATE INDEX idxArtistsLyrics ON Artists (Lyrics)
CREATE INDEX idxArtistsTracks ON Artists (Tracks)

CREATE TABLE ArtistsAlbums (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDArtist INTEGER
    ,IDAlbum INTEGER)
CREATE TRIGGER delete_artistsalbums DELETE ON ArtistsAlbums
BEGIN
  UPDATE Artists SET Albums=Albums-1 WHERE Artists.ID=old.IDArtist;
END
CREATE INDEX idxArtistsAlbumsAlbum ON ArtistsAlbums (IDAlbum)
CREATE INDEX idxArtistsAlbumsArtist ON ArtistsAlbums (IDArtist)
CREATE TRIGGER insert_artistsalbums INSERT ON ArtistsAlbums
BEGIN
  UPDATE Artists SET Albums=ifnull(Albums,0)+1 WHERE Artists.ID=new.IDArtist;
END
CREATE TRIGGER update_artistsalbums UPDATE ON ArtistsAlbums
BEGIN
  UPDATE Artists SET Albums=ifnull(Albums,0)+1 WHERE Artists.ID=new.IDArtist;
  UPDATE Artists SET Albums=Albums-1 WHERE Artists.ID=old.IDArtist;
END

CREATE TABLE ArtistsSongs (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,PersonType INTEGER
    ,IDArtist INTEGER
    ,IDSong INTEGER)
CREATE TRIGGER delete_artistssongs DELETE ON ArtistsSongs WHEN old.PersonType=1
BEGIN
  UPDATE Artists SET Tracks=Tracks-1 WHERE Artists.ID=old.IDArtist;
END
CREATE TRIGGER delete_artistssongs_authors DELETE ON ArtistsSongs WHEN old.PersonType=3
BEGIN
  UPDATE Artists SET Authors=Authors-1 WHERE Artists.ID=old.IDArtist;
END
CREATE TRIGGER delete_artistssongs_conducts DELETE ON ArtistsSongs WHEN old.PersonType=4
BEGIN
  UPDATE Artists SET Conducts=Conducts-1 WHERE Artists.ID=old.IDArtist;
END
CREATE TRIGGER delete_artistssongs_lyrics DELETE ON ArtistsSongs WHEN old.PersonType=5
BEGIN
  UPDATE Artists SET Lyrics=Lyrics-1 WHERE Artists.ID=old.IDArtist;
END
CREATE INDEX idxArtistsSongsArtist ON ArtistsSongs (IDArtist,PersonType)
CREATE INDEX idxArtistsSongsPersonTypeSong ON ArtistsSongs (PersonType,IDSong)
CREATE INDEX idxArtistsSongsSong ON ArtistsSongs (IDSong)
CREATE TRIGGER insert_artistssongs_authors INSERT ON ArtistsSongs WHEN new.PersonType=3
BEGIN
  UPDATE Artists SET Authors=ifnull(Authors,0)+1 WHERE Artists.ID=new.IDArtist;
END
CREATE TRIGGER insert_artistssongs_conducts INSERT ON ArtistsSongs WHEN new.PersonType=4
BEGIN
  UPDATE Artists SET Conducts=ifnull(Conducts,0)+1 WHERE Artists.ID=new.IDArtist;
END
CREATE TRIGGER insert_artistssongs_lyrics INSERT ON ArtistsSongs WHEN new.PersonType=5
BEGIN
  UPDATE Artists SET Lyrics=ifnull(Lyrics,0)+1 WHERE Artists.ID=new.IDArtist;
END
CREATE TRIGGER insert_artistssongs_tracks INSERT ON ArtistsSongs WHEN new.PersonType=1
BEGIN
  UPDATE Artists SET Tracks=ifnull(Tracks,0)+1 WHERE Artists.ID=new.IDArtist;
END

CREATE TABLE Covers (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDSong INTEGER
    ,CoverOrder INTEGER
    ,CoverPath TEXT COLLATE IUNICODE
    ,CoverStorage INTEGER
    ,CoverDescription TEXT COLLATE IUNICODE
    ,CoverType INTEGER
    ,PictureType TEXT COLLATE IUNICODE)
CREATE INDEX idxCoverSong ON Covers (IdSong)

CREATE TABLE DBInfo (IDInfo INTEGER
    ,InfoValue INTEGER
    ,InfoString TEXT)

CREATE TABLE DeviceFilters (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,FilterID INTEGER
    ,SynchAllMusic INTEGER
    ,SynchAllPlaylists INTEGER
    ,SynchAllPodcasts INTEGER
    ,SynchAllAudiobooks INTEGER)

CREATE TABLE DeviceTracks (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,IDTrack INTEGER
    ,DevicePath TEXT COLLATE IUNICODE
    ,Rating INTEGER
    ,PlayCount INTEGER
    ,UploadTime REAL
    ,Status INTEGER
    ,Converted INTEGER
    ,PreModified INTEGER)
CREATE UNIQUE INDEX idxDeviceTracksDeviceTrack ON DeviceTracks (IDDevice, IDTrack)
CREATE INDEX idxDeviceTracksTrack ON DeviceTracks (IDTrack)

CREATE TABLE Devices (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,PluginName TEXT COLLATE IUNICODE
    ,SynchAll INTEGER
    ,DeleteUnsynch INTEGER
    ,AdvancedSynch INTEGER
    ,TargetMask TEXT COLLATE IUNICODE
    ,SaveAAToFolder INTEGER
    ,SaveAAToTag INTEGER
    ,RemoveAAFromTag INTEGER
    ,RemoveAAByteRate INTEGER
    ,SyncAAMask TEXT COLLATE IUNICODE
    ,CopyM3U INTEGER
    ,M3UFolder TEXT COLLATE IUNICODE
    ,M3UCopyLocations INTEGER
    ,M3UCopyArtists INTEGER
    ,M3UCopyAlbums INTEGER
    ,M3UCopyPlaylists INTEGER
    ,M3UOrganize INTEGER
    ,EpisodesCount INTEGER
    ,SynchUnplayedEpis INTEGER
    ,DeleteConfirm INTEGER
    ,BiDirSync INTEGER
    ,BiDirConfirm INTEGER
    ,SynchOnConnect INTEGER
    ,DeviceID TEXT COLLATE IUNICODE
    ,DeviceCaption TEXT COLLATE IUNICODE
    ,AutoUnmount INTEGER
    ,RandomSelection INTEGER
    ,PreferStars INTEGER
    ,DontDelete TEXT COLLATE IUNICODE
    ,AutoConversions TEXT COLLATE IUNICODE
    ,Invisible INTEGER
    ,LastAutoSynch INTEGER
    ,FirstGenre INTEGER
    ,FirstArtist INTEGER)
CREATE TRIGGER delete_devices DELETE ON Devices
BEGIN
  DELETE FROM DeviceTracks WHERE DeviceTracks.IDDevice=old.ID;
END

CREATE TABLE Filters (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,Pos INTEGER
    ,Name TEXT COLLATE IUNICODE
    ,Cond TEXT COLLATE IUNICODE
    ,View TEXT COLLATE IUNICODE
    ,Player INTEGER
    ,ContentType INTEGER)

CREATE TABLE Folders (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDMedia INTEGER
    ,IDParentFolder INTEGER
    ,Folder TEXT COLLATE IUNICODE
    ,TrackCount INTEGER)
CREATE TRIGGER delete_folders DELETE ON Folders
BEGIN
  DELETE FROM FoldersHier WHERE IDChildFolder=old.ID;
END
CREATE INDEX indFoldersIDParentFolder ON Folders (IDMedia, IDParentFolder)
CREATE TRIGGER insert_folders AFTER INSERT ON Folders
BEGIN
  INSERT INTO FoldersHier (IDFolder,IDChildFolder) SELECT IDFolder,new.ID FROM FoldersHier WHERE IDChildFolder=new.IDParentFolder;
  INSERT INTO FoldersHier (IDFolder,IDChildFolder) VALUES (new.ID,new.ID);
END

CREATE TABLE FoldersHier (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDFolder INTEGER
    ,IDChildFolder INTEGER)
CREATE INDEX indFoldersHierIDChildFolder ON FoldersHier (IDChildFolder)
CREATE INDEX indFoldersHierIDFolder ON FoldersHier (IDFolder)

CREATE TABLE Genres (IDGenre INTEGER PRIMARY KEY AUTOINCREMENT
    ,GenreName TEXT COLLATE IUNICODE
    ,Description TEXT
    ,UsageCount INTEGER DEFAULT 0)
CREATE INDEX idxGenresGenreName ON Genres (GenreName)

CREATE TABLE GenresSongs (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDGenre INTEGER
    ,IDSong INTEGER)
CREATE TRIGGER delete_GenresSongs DELETE ON GenresSongs
BEGIN
  UPDATE Genres SET UsageCount=UsageCount-1 WHERE Genres.IDGenre=old.IDGenre;
  DELETE FROM  Genres WHERE Genres.IDGenre=old.IDGenre AND Genres.UsageCount=0;
END
CREATE INDEX idxGenresSongsGenre ON GenresSongs (IDGenre)
CREATE INDEX idxGenresSongsSong ON GenresSongs (IDSong)
CREATE TRIGGER insert_GenresSongs INSERT ON GenresSongs
BEGIN
  UPDATE Genres SET UsageCount=ifnull(UsageCount,0)+1 WHERE Genres.IDGenre=new.IDGenre;
END

CREATE TABLE Lists (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDListType INTEGER
    ,TextData TEXT COLLATE IUNICODE
    ,SortOrder INTEGER)
CREATE INDEX idxListsType ON Lists (IDListType, SortOrder)

CREATE TABLE ListsSongs (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDSong INTEGER
    ,IDListType INTEGER
    ,IDList INTEGER)
CREATE INDEX idxListsSongsIDList ON ListsSongs (IDList)
CREATE INDEX idxListsSongsIDSong ON ListsSongs (IDSong)

CREATE TABLE Medias (IDMedia INTEGER PRIMARY KEY AUTOINCREMENT
    ,SerialNumber INTEGER
    ,DriveType INTEGER
    ,Label TEXT COLLATE IUNICODE
    ,ShowLabel TEXT COLLATE IUNICODE
    ,Comment TEXT COLLATE IUNICODE
    ,IsAudioCD INTEGER
    ,Location TEXT  COLLATE IUNICODE
    ,CDDBId INTEGER
    ,TOCData TEXT
    ,CDDBQueryState INTEGER
    ,CDDBQueryResult TEXT
    ,TurnedSN INTEGER
    ,DriveLetter INTEGER)
CREATE TRIGGER delete_medias DELETE ON Medias
BEGIN
  DELETE FROM Songs WHERE Songs.IDMedia=old.IDMedia;
END

CREATE TABLE OrganizeRules (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,Pos INTEGER
    ,Path TEXT COLLATE IUNICODE
    ,Mask TEXT COLLATE IUNICODE
    ,Filters TEXT COLLATE IUNICODE
    ,Genres TEXT COLLATE IUNICODE
    ,Playlists TEXT COLLATE IUNICODE)

CREATE TABLE PathProcessing (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDMedia INTEGER
    ,Path TEXT COLLATE IUNICODE
    ,Action INTEGER
    ,IDSong INTEGER)

CREATE TABLE Played (IDPlayed INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDSong INTEGER
    ,PlayDate REAL)
CREATE INDEX idxPlayedSong ON Played (IDSong)

CREATE TABLE PlaylistSongs (IDPlaylistSong INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDPlaylist INTEGER
    ,IDSong INTEGER
    ,SongOrder INTEGER)
CREATE INDEX idxPlaylistSongsPlaylist ON PlaylistSongs (IDPlaylist, SongOrder)
CREATE INDEX idxPlaylistSongsSong ON PlaylistSongs (IDSong)

CREATE TABLE Playlists (IDPlaylist INTEGER PRIMARY KEY AUTOINCREMENT
    ,PlaylistName TEXT COLLATE IUNICODE
    ,ParentPlaylist INTEGER
    ,Comment TEXT COLLATE IUNICODE
    ,IsAutoPlaylist INTEGER
    ,QueryData TEXT COLLATE IUNICODE
    ,srcMedia INTEGER
    ,srcPath TEXT COLLATE IUNICODE
    ,Persistent INTEGER
    ,Synchronize INTEGER)
CREATE TRIGGER delete_playlists DELETE ON Playlists
BEGIN
  DELETE FROM PlaylistSongs WHERE PlaylistSongs.IDPlaylist=old.IDPlaylist;
END
CREATE INDEX idxPlaylistsParents ON Playlists (ParentPlaylist)

CREATE TABLE PodcastDirs  (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,DirName TEXT COLLATE IUNICODE
    ,Type INTEGER
    ,DirUrl TEXT COLLATE IUNICODE
    ,CachePath TEXT COLLATE IUNICODE)

CREATE TABLE PodcastEpisodes   (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDPodcast INTEGER
    ,IDTrack INTEGER
    ,ViewStatus INTEGER
    ,Downloaded INTEGER
    ,title TEXT COLLATE IUNICODE
    ,autor TEXT COLLATE IUNICODE
    ,link TEXT COLLATE IUNICODE
    ,description TEXT COLLATE IUNICODE
    ,subtitle TEXT COLLATE IUNICODE
    ,summary TEXT COLLATE IUNICODE
    ,PubDate REAL
    ,NetSource TEXT COLLATE IUNICODE
    ,GUID TEXT COLLATE IUNICODE
    ,keywords TEXT COLLATE IUNICODE
    ,category TEXT COLLATE IUNICODE)
CREATE TRIGGER delete_podcastepisodes DELETE ON PodcastEpisodes
BEGIN
  UPDATE Songs SET IDEpisode = -1 WHERE Songs.ID = old.IDTrack;
END
CREATE INDEX indURL ON PodcastEpisodes (NetSource)
CREATE TRIGGER insert_podcastepisodes AFTER INSERT ON PodcastEpisodes WHEN new.IDTrack > -1
BEGIN
  UPDATE Songs SET TrackType = gettracktype( SongPath, Genre, new.ID), IDEpisode = new.ID WHERE Songs.ID = new.IDTrack;
END
CREATE TRIGGER update_idepisode UPDATE OF IDTrack ON PodcastEpisodes WHEN new.IDTrack<>old.IDTrack
BEGIN
  UPDATE Songs SET IDEpisode = -1 WHERE ID = old.IDTrack;
  UPDATE Songs SET IDEpisode = new.ID WHERE ID = new.IDTrack;
END

CREATE TABLE Podcasts  (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,PodcastName TEXT COLLATE IUNICODE
    ,PodcastUrl TEXT COLLATE IUNICODE
    ,Generator TEXT COLLATE IUNICODE
    ,Login TEXT COLLATE IUNICODE
    ,Password TEXT COLLATE IUNICODE
    ,CustomizeRules INTEGER
    ,DelEpisodes INTEGER
    ,DelOnlyListened INTEGER
    ,DelRating INTEGER
    ,DelRatingValue INTEGER
    ,RetainEpisodes INTEGER
    ,RetainNumber INTEGER
    ,EpisodeAge INTEGER
    ,DownloadType INTEGER
    ,OverwriteTags INTEGER
    ,ShowRemovedEpisodes INTEGER
    ,Description TEXT COLLATE IUNICODE)

CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,Artist TEXT COLLATE IUNICODE
    ,IDAlbum INTEGER
    ,Album TEXT COLLATE IUNICODE
    ,AlbumArtist TEXT COLLATE IUNICODE
    ,DiscNumber TEXT COLLATE IUNICODE
    ,TrackNumber TEXT COLLATE IUNICODE
    ,SongTitle TEXT COLLATE IUNICODE
    ,SongPath TEXT COLLATE IUNICODE
    ,Extension TEXT(5)
    ,Year INTEGER
    ,Genre TEXT COLLATE IUNICODE
    ,FileLength INTEGER
    ,SongLength INTEGER
    ,IDMedia INTEGER
    ,CacheStatus INTEGER
    ,CacheName TEXT COLLATE IUNICODE
    ,Rating INTEGER
    ,Bitrate INTEGER
    ,Seekable INTEGER
    ,Broadcast INTEGER
    ,PreviewState INTEGER
    ,PreviewName TEXT COLLATE IUNICODE
    ,PreviewStartTime INTEGER
    ,PreviewLength INTEGER
    ,Author TEXT COLLATE IUNICODE
    ,SamplingFrequency INTEGER
    ,Stereo INTEGER
    ,VBR INTEGER
    ,BPM INTEGER
    ,SignType INTEGER
    ,SignPart1 INTEGER
    ,SignPart2 INTEGER
    ,SignPart3 INTEGER
    ,SignPart4 INTEGER
    ,PlayCounter INTEGER
    ,LastTimePlayed REAL
    ,AudioCDTrack INTEGER
    ,FileModified REAL
    ,TrackModified REAL
    ,MaxSample REAL
    ,NormalizeTrack REAL
    ,NormalizeAlbum REAL
    ,Custom1 TEXT COLLATE IUNICODE
    ,Custom2 TEXT COLLATE IUNICODE
    ,Custom3 TEXT COLLATE IUNICODE
    ,Custom4 TEXT COLLATE IUNICODE
    ,Custom5 TEXT COLLATE IUNICODE
    ,DateAdded REAL
    ,OrigFileLength INTEGER
    ,PreGap INTEGER
    ,PostGap INTEGER
    ,TotalSamples INTEGER
    ,PlaybackPos INTEGER
    ,GaplessBytes INTEGER
    ,IDFolder INTEGER
    ,IDEpisode INTEGER
    ,TrackType INTEGER
    ,Copyright TEXT COLLATE IUNICODE
    ,Publisher TEXT COLLATE IUNICODE
    ,Encoder TEXT COLLATE IUNICODE
    ,Lyricist TEXT COLLATE IUNICODE
    ,Conductor TEXT COLLATE IUNICODE
    ,Remixer TEXT COLLATE IUNICODE
    ,InvolvedPeople TEXT COLLATE IUNICODE
    ,OrigTitle TEXT COLLATE IUNICODE
    ,OrigArtist TEXT COLLATE IUNICODE
    ,OrigLyricist TEXT COLLATE IUNICODE
    ,GroupDesc TEXT COLLATE IUNICODE
    ,SubTitle TEXT COLLATE IUNICODE
    ,ISRC TEXT COLLATE IUNICODE
    ,InitialKey TEXT COLLATE IUNICODE
    ,Language TEXT COLLATE IUNICODE
    ,WebCommercial TEXT COLLATE IUNICODE
    ,WebCopyright TEXT COLLATE IUNICODE
    ,WebFilepage TEXT COLLATE IUNICODE
    ,WebArtist TEXT COLLATE IUNICODE
    ,WebSource TEXT COLLATE IUNICODE
    ,WebRadio TEXT COLLATE IUNICODE
    ,WebPayment TEXT COLLATE IUNICODE
    ,WebPublisher TEXT COLLATE IUNICODE
    ,WebUser TEXT COLLATE IUNICODE
    ,OrigYear INTEGER
    ,Tempo TEXT COLLATE IUNICODE
    ,Mood TEXT COLLATE IUNICODE
    ,Occasion TEXT COLLATE IUNICODE
    ,Quality TEXT COLLATE IUNICODE
    ,Lyrics TEXT COLLATE IUNICODE
    ,Comment TEXT COLLATE IUNICODE)
CREATE TRIGGER delete_songs DELETE ON Songs
BEGIN
  DELETE FROM ArtistsSongs WHERE ArtistsSongs.IDSong=old.ID;
  DELETE FROM PlaylistSongs WHERE PlaylistSongs.IDSong=old.ID;
  DELETE FROM Covers WHERE Covers.IDSong=old.ID;
  DELETE FROM DeviceTracks WHERE DeviceTracks.IDTrack=old.ID;
  DELETE FROM GenresSongs WHERE GenresSongs.IDSong=old.ID;
  DELETE FROM ListsSongs WHERE ListsSongs.IDSong=old.ID;
  DELETE FROM PathProcessing WHERE PathProcessing.IDSong=old.ID;
  UPDATE Folders SET TrackCount=TrackCount-1 WHERE Folders.ID IN (SELECT IDFolder FROM FoldersHier WHERE FoldersHier.IDChildFolder=old.IDFolder);
  DELETE FROM Folders WHERE Folders.ID IN (SELECT IDFolder FROM FoldersHier WHERE FoldersHier.IDChildFolder=old.IDFolder) AND Folders.TrackCount<=0;
  UPDATE Albums SET Tracks=Tracks-1 WHERE Albums.ID=old.IDAlbum;
  DELETE FROM SongsText WHERE rowid=old.ID;
  UPDATE PodcastEpisodes SET idTrack = -1, ViewStatus = 1 WHERE PodcastEpisodes.idTrack=old.ID;
END
CREATE INDEX idxSongsBPM ON Songs (BPM)
CREATE INDEX idxSongsExtension ON Songs (Extension)
CREATE INDEX idxSongsFileLength ON Songs (FileLength)
CREATE INDEX idxSongsFolder ON Songs (IDFolder)
CREATE INDEX idxSongsIDAlbum ON Songs (IDAlbum)
CREATE INDEX idxSongsMediaPath ON Songs (IDMedia, SongPath)
CREATE INDEX idxSongsNormalizeAlbum ON Songs (NormalizeAlbum)
CREATE INDEX idxSongsNormalizeTrack ON Songs (NormalizeTrack)
CREATE INDEX idxSongsSignature ON Songs (SignPart1)
CREATE INDEX idxSongsYear ON Songs (Year)
CREATE INDEX idxTrackType ON Songs (TrackType)
CREATE TRIGGER insert_songs AFTER INSERT ON Songs
BEGIN
  UPDATE Albums SET Tracks=ifnull(Tracks,0)+1 WHERE Albums.ID=new.IDAlbum;
  INSERT INTO PathProcessing (IDMedia,Path,Action,IDSong) VALUES (new.IDMedia,new.SongPath,1,new.ID);
  INSERT INTO SongsText (rowid,artist,album,albumartist,songtitle,genre,songpath,author,lyricist,conductor,groupdesc,subtitle,lyrics,comment,custom1,custom2,custom3,custom4,custom5,origartist,origtitle,origlyricist,publisher,encoder,copyright,mood,tempo,occasion,quality,involvedpeople) VALUES (new.id,new.Artist,new.Album,new.AlbumArtist,new.SongTitle,new.Genre,new.SongPath,new.Author,new.Lyricist,new.Conductor,new.GroupDesc,new.SubTitle,new.Lyrics,new.Comment,new.custom1,new.custom2,new.custom3,new.custom4,new.custom5,new.origartist,new.origtitle,new.origlyricist,new.publisher,new.encoder,new.copyright,new.mood,new.tempo,new.occasion,new.quality,new.involvedpeople);
  UPDATE Songs SET Extension = getextension( new.SongPath) WHERE ID = new.ID;
  UPDATE Songs SET IDEpisode = -1 WHERE ID = new.ID;
  UPDATE Songs SET TrackType = gettracktype( new.SongPath, new.Genre, -1) WHERE ID = new.ID;
END
CREATE TRIGGER update_songs UPDATE ON Songs
BEGIN
  UPDATE Albums SET Tracks=Tracks-1 WHERE Albums.ID=old.IDAlbum;
  UPDATE Albums SET Tracks=ifnull(Tracks,0)+1 WHERE Albums.ID=new.IDAlbum;
END
CREATE TRIGGER update_songs_album UPDATE OF album ON Songs WHEN new.album<>old.album
BEGIN
  UPDATE SongsText SET album=new.album WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_albumartist UPDATE OF albumartist ON Songs WHEN new.albumartist<>old.albumartist
BEGIN
  UPDATE SongsText SET albumartist=new.albumartist WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_artist UPDATE OF artist ON Songs WHEN new.artist<>old.artist
BEGIN
  UPDATE SongsText SET artist=new.artist WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_author UPDATE OF author ON Songs WHEN new.author<>old.author
BEGIN
  UPDATE SongsText SET author=new.author WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_comment UPDATE OF comment ON Songs WHEN new.comment<>old.comment
BEGIN
  UPDATE SongsText SET comment=new.comment WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_conductor UPDATE OF conductor ON Songs WHEN new.conductor<>old.conductor
BEGIN
  UPDATE SongsText SET conductor=new.conductor WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_copyright UPDATE OF copyright ON Songs WHEN new.copyright<>old.copyright
BEGIN
  UPDATE SongsText SET copyright=new.copyright WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_custom1 UPDATE OF custom1 ON Songs WHEN new.custom1<>old.custom1
BEGIN
  UPDATE SongsText SET custom1=new.custom1 WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_custom2 UPDATE OF custom2 ON Songs WHEN new.custom2<>old.custom2
BEGIN
  UPDATE SongsText SET custom2=new.custom2 WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_custom3 UPDATE OF custom3 ON Songs WHEN new.custom3<>old.custom3
BEGIN
  UPDATE SongsText SET custom3=new.custom3 WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_custom4 UPDATE OF custom4 ON Songs WHEN new.custom4<>old.custom4
BEGIN
  UPDATE SongsText SET custom4=new.custom4 WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_custom5 UPDATE OF custom5 ON Songs WHEN new.custom5<>old.custom5
BEGIN
  UPDATE SongsText SET custom5=new.custom5 WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_encoder UPDATE OF encoder ON Songs WHEN new.encoder<>old.encoder
BEGIN
  UPDATE SongsText SET encoder=new.encoder WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_genre UPDATE OF genre ON Songs WHEN new.genre<>old.genre
BEGIN
  UPDATE SongsText SET genre=new.genre WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_groupdesc UPDATE OF groupdesc ON Songs WHEN new.groupdesc<>old.groupdesc
BEGIN
  UPDATE SongsText SET groupdesc=new.groupdesc WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_involvedpeople UPDATE OF involvedpeople ON Songs WHEN new.involvedpeople<>old.involvedpeople
BEGIN
  UPDATE SongsText SET involvedpeople=new.involvedpeople WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_lyricist UPDATE OF lyricist ON Songs WHEN new.lyricist<>old.lyricist
BEGIN
  UPDATE SongsText SET lyricist=new.lyricist WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_lyrics UPDATE OF lyrics ON Songs WHEN new.lyrics<>old.lyrics
BEGIN
  UPDATE SongsText SET lyrics=new.lyrics WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_mood UPDATE OF mood ON Songs WHEN new.mood<>old.mood
BEGIN
  UPDATE SongsText SET mood=new.mood WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_occasion UPDATE OF occasion ON Songs WHEN new.occasion<>old.occasion
BEGIN
  UPDATE SongsText SET occasion=new.occasion WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_origartist UPDATE OF origartist ON Songs WHEN new.origartist<>old.origartist
BEGIN
  UPDATE SongsText SET origartist=new.origartist WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_origlyricist UPDATE OF origlyricist ON Songs WHEN new.origlyricist<>old.origlyricist
BEGIN
  UPDATE SongsText SET origlyricist=new.origlyricist WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_origtitle UPDATE OF origtitle ON Songs WHEN new.origtitle<>old.origtitle
BEGIN
  UPDATE SongsText SET origtitle=new.origtitle WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_publisher UPDATE OF publisher ON Songs WHEN new.publisher<>old.publisher
BEGIN
  UPDATE SongsText SET publisher=new.publisher WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_quality UPDATE OF quality ON Songs WHEN new.quality<>old.quality
BEGIN
  UPDATE SongsText SET quality=new.quality WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_songpath UPDATE OF IDMedia, SongPath ON Songs WHEN new.SongPath<>old.SongPath OR new.IDMedia<>old.IDMedia
BEGIN
  INSERT INTO PathProcessing (IDMedia,Path,Action,IDSong) VALUES (old.IDMedia,old.SongPath,2,new.ID);
  INSERT INTO PathProcessing (IDMedia,Path,Action,IDSong) VALUES (new.IDMedia,new.SongPath,1,new.ID);
  UPDATE SongsText SET songpath=new.SongPath WHERE rowid=new.id;
  UPDATE Songs SET Extension = getextension( new.SongPath) WHERE ID = new.ID;
END
CREATE TRIGGER update_songs_songtitle UPDATE OF songtitle ON Songs WHEN new.songtitle<>old.songtitle
BEGIN
  UPDATE SongsText SET songtitle=new.songtitle WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_subtitle UPDATE OF subtitle ON Songs WHEN new.subtitle<>old.subtitle
BEGIN
  UPDATE SongsText SET subtitle=new.subtitle WHERE rowid=new.id;
END
CREATE TRIGGER update_songs_tempo UPDATE OF tempo ON Songs WHEN new.tempo<>old.tempo
BEGIN
  UPDATE SongsText SET tempo=new.tempo WHERE rowid=new.id;
END
CREATE TRIGGER update_tracktype UPDATE OF IDEpisode, SongPath, Genre ON Songs WHEN new.SongPath<>old.SongPath OR new.IDEpisode<>old.IDEpisode OR new.Genre<>old.Genre
BEGIN
  UPDATE Songs SET TrackType = gettracktype( new.SongPath, new.Genre, new.IDEpisode) WHERE ID = new.ID;
END

CREATE VIRTUAL TABLE SongsText USING FTS3(TOKENIZE mm
    ,artist
    ,album
    ,albumartist
    ,songtitle
    ,genre
    ,songpath
    ,author
    ,lyricist
    ,conductor
    ,groupdesc
    ,subtitle
    ,lyrics
    ,comment
    ,custom1
    ,custom2
    ,custom3
    ,custom4
    ,custom5
    ,origartist
    ,origtitle
    ,origlyricist
    ,publisher
    ,encoder
    ,copyright
    ,mood
    ,tempo
    ,occasion
    ,quality
    ,involvedpeople)

CREATE TABLE SongsText_content(  docid INTEGER PRIMARY KEY
    ,c0artist
    ,c1album
    ,c2albumartist
    ,c3songtitle
    ,c4genre
    ,c5songpath
    ,c6author
    ,c7lyricist
    ,c8conductor
    ,c9groupdesc
    ,c10subtitle
    ,c11lyrics
    ,c12comment
    ,c13custom1
    ,c14custom2
    ,c15custom3
    ,c16custom4
    ,c17custom5
    ,c18origartist
    ,c19origtitle
    ,c20origlyricist
    ,c21publisher
    ,c22encoder
    ,c23copyright
    ,c24mood
    ,c25tempo
    ,c26occasion
    ,c27quality
    ,c28involvedpeople)

CREATE TABLE SongsText_segdir(  level integer
    ,idx integer
    ,start_block integer
    ,leaves_end_block integer
    ,end_block integer
    ,root blob
    ,primary key(level
    ,idx))


CREATE TABLE SongsText_segments(  blockid INTEGER PRIMARY KEY
    ,block blob)

CREATE TABLE SynchAlbum (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,IDAlbum INTEGER
    ,MaskPath TEXT COLLATE IUNICODE
    ,FilterID INTEGER)
CREATE INDEX idxSynchAlbumDevice ON SynchAlbum (IDDevice, IDAlbum)

CREATE TABLE SynchArtist (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,IDArtist INTEGER
    ,MaskPath TEXT COLLATE IUNICODE
    ,FilterID INTEGER)
CREATE INDEX idxSynchArtistDevice ON SynchArtist (IDDevice, IDArtist)

CREATE TABLE SynchLocation (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,IDMedia INTEGER
    ,Path TEXT COLLATE IUNICODE
    ,MaskPath TEXT COLLATE IUNICODE
    ,FilterID INTEGER)
CREATE INDEX idxSynchLocationDevice ON SynchLocation (IDDevice, IDMedia, Path)

CREATE TABLE SynchPlaylist (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,IDPlaylist INTEGER
    ,MaskPath TEXT COLLATE IUNICODE)
CREATE INDEX idxSynchPlaylistDevice ON SynchPlaylist (IDDevice, IDPlaylist)

CREATE TABLE SynchPodcast (ID INTEGER PRIMARY KEY AUTOINCREMENT
    ,IDDevice INTEGER
    ,IDPodcast INTEGER
    ,MaskPath TEXT COLLATE IUNICODE)
CREATE INDEX idxSynchPodcastDevice ON SynchPodcast (IDDevice, IDPodcast)

CREATE TABLE TmpSqlHist (tmpIDnr INTEGER PRIMARY KEY
    ,tmpRows INTEGER
    ,tmpTime TEXT
    ,tmpHtml TEXT
    ,tmpSql TEXT)

CREATE TABLE TmpSqlSaves (tmpIDnr INTEGER PRIMARY KEY
    ,tmpName TEXT
    ,tmpRows INTEGER
    ,tmpTime TEXT
    ,tmpHtml TEXT
    ,tmpSql TEXT)

CREATE TABLE sqlite_sequence(name
    ,seq)

----End of Report----


[Owyn]

Added small script to test if Optimize(Complete) is required due to problems identified with Mantis#6140
This script requires MediaMonkey 3.2.0.1287 or later.

DB_TestFTS
Install and run from Tools->Scripts->MM3 Database Test if Optimize(Complete) required.

[nynaevelan]

Thanks Owyn you are building some very nice tools.