Database Tools

[Owyn]

Here are 3 tools which have been in beta for a while but have been held from general release until 3.2.0.1294 was released.

3.2.0 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.

[hintergrundrauschen]

Thanks for those. I had some problems possibly related to DB integrity (here, as well as songs missing or wrong song count in static playlists according to properties/classification dialogue of corresponding song), so I was interested in DB Audit.

Code: Select all

Played.IDSong -> Songs.ID                            18141   18141    339 **Error**


Does that mean there are song statistics (# played) for songs I deleted afterwards?

Code: Select all

PlaylistSongs.IDPlayList -> Playlists.IDPlaylist      9609    9609   2709 **Error**
...
SynchPlaylist.IDPlaylist -> Playlists.ID                 3       3      1 **Error**


Does that mean there are songs in playlists that I deleted (i.e. I deleted playlists afterwards)?

[Owyn]

What it saying is that:
1) MM did not delete playhistory at the same time you removed a track.
2) There is a reference to a deleted playlist in a sync playlist

DB_Clean will remove these orpahned rows.

Re-run DB_Audit after running DB_Clean to see if there are any other problems.

[hintergrundrauschen]

OK, thanks!

[nynaevelan]

Owyn:

Is there a way to have the report open when AuditDB is run??

[Owyn]

That would require a change to the script. Will take a look around for an appropriate widget to browse the report file.

[Onweerwolf]

Could someone help me troubleshoot these errors? I do not know how to make them go away.

Code: Select all

DB_Audit.vbs(1.0.2):Audit MediaMonkey3 Database Consistency
Report File: M:\DB_Audit_Report_20101126_1642.txt
Database:    C:\Documents and Settings\Snaak\Local Settings\Application Data\MediaMonkey\MM.DB
DB Info:     3 1 24 754235825
MediaMonkey: 3.2.2.1300
SQLite:      3.5.4
Run:         26-11-2010
Max Details: 20
Max Width:   40
 
Database Statistics
Table                              Seq   Count   MaxID
------------------------------ ------- ------- -------
Albums                           10540    2965   10540
Artists                           8779    6192    8779
ArtistsAlbums                    10386    2969   10386
ArtistsSongs                    538041   36623  538041
Covers                          114604   34831  114604
DeviceFilters                      281       2     281
DeviceTracks                     82570     276   82570
Devices                             47      10      47
Filters                              7       1       7
Folders                           6381    6145    6381
FoldersHier                      25340   24574   25340
Genres                            1130     286    1130
GenresSongs                     502259   36586  502259
Lists                               56      56      56
ListsSongs                        7017    1958    7017
Medias                              32      10      32
OrganizeRules                        1       1       1
PathProcessing                  189129       0         
Played                           60104   59720   60104
PlaylistSongs                  1849062  167816 1849062
Playlists                         2987    2365    2987
PodcastDirs                          4       2       3
Songs                            38369   36586   38369
SynchPlaylist                      839      22     837
 
 
----Checking Links---
Relation                                             Count  Linked  Error
-------------------------------------------------- ------- ------- ------
ArtistsAlbums.IDArtist -> Artists.ID                  2969    2969      0
ArtistsAlbums.IDAlbum -> Albums.ID                    2969    2969      0
ArtistsSongs.IDArtist -> Artists.ID                  36623   36623      0
ArtistsSongs.IDSong -> Songs.ID                      36623   36623      0
Covers.IDSong -> Songs.ID                            34831   34831      0
DeviceFilters.IDDevice -> Devices.ID                     2       2      0
DeviceTracks.IDDevice -> Devices.ID                    276     276      0
DeviceTracks.IDTrack -> Songs.ID                       276     276      0
Folders.IDMedia -> Medias.ID                          6145    6145      0
Folders.IDParentFolder -> Folders.ID                  6145    6145      0
FoldersHier.IDFolder -> Folders.ID                   24574   24574      0
FoldersHier.IDChildFolder -> Folders.ID              24574   24574      0
GenresSongs.IDGenre -> Genres.IDGenre                36586   36586      0
GenresSongs.IDSong -> Songs.ID                       36586   36586      0
ListsSongs.IDList -> Lists.ID                         1958    1958      0
ListsSongs.IDSong -> Songs.ID                         1958    1958      0
PathProcessing.IDMedia -> Medias.ID                      0       0      0
PathProcessing.IDSong -> Songs.ID                        0       0      0
Played.IDSong -> Songs.ID                            59720   59720      0
PlaylistSongs.IDPlayList -> Playlists.IDPlaylist    167816  167816     82 **Error**
Link not in linked table                      **ERROR**
---------------------Details-----------------------------
SELECT *
    FROM PlaylistSongs
    WHERE IDPlayList<>-1
    AND IDPlayList NOT IN (SELECT IDPlaylist
    FROM Playlists)
[IDPLAYLISTSONG] [IDPLAYLIST] [IDSONG] [SONGORDER]
[156199] [67] [20349] []
[156200] [67] [18580] []
[156201] [67] [12728] []
[156202] [67] [12084] []
[156203] [67] [18377] []
[156204] [67] [22167] []
[156205] [67] [18030] []
[156206] [67] [12900] []
[156207] [67] [12132] []
[156208] [67] [11780] []
[156209] [67] [9527] []
[156210] [67] [7992] []
[156211] [67] [37678] []
[156212] [67] [19331] []
[156213] [67] [18721] []
[156214] [67] [18127] []
[156215] [67] [10390] []
[156216] [67] [10155] []
[156217] [67] [8965] []
[156218] [67] [23295] []
---------------- 20 OF 82 Errors Listed------------
 
PlaylistSongs.IDSong -> Songs.ID                    167816  167816      0
PodcastEpisodes.IDPodcast -> Podcasts.ID                 0       0      0
PodcastEpisodes.IDTrack -> Songs.ID                      0       0      0
Songs.IDAlbum -> Albums.ID                           36586   36586      0
Songs.IDEpisode -> PodcastEpisodes.ID                36586       0      0
Songs.IDMedia -> Medias.ID                           36586   36586      0
Songs.IDFolder -> Folders.ID                         36586   36586      0
SongsText.RowID -> Songs.ID                          36586   36586      0
SynchAlbum.IDDevice -> Devices.ID                        0       0      0
SynchAlbum.IDAlbum -> Albums.ID                          0       0      0
SynchArtist.IDDevice -> Devices.ID                       0       0      0
SynchArtist.IDArtist -> Artists.ID                       0       0      0
SynchLocation.IDDevice -> Devices.ID                     0       0      0
SynchLocation.IDMedia -> Medias.ID                       0       0      0
SynchPlaylist.IDDevice -> Devices.ID                    22      22      0
SynchPlaylist.IDPlaylist -> Playlists.ID                22      22      0
SynchPodcast.IDDevice -> Devices.ID                      0       0      0
SynchPodcast.IDPodcast -> Podcasts.ID                    0       0      0
 
 
----Checking Fast Lookup (Join) Tables---
ArtistSongs(Artist) Check Counts              OK
ArtistsSongs(Artist) Check Values             OK
ArtistsSongs(Composer) Check Counts           OK
ArtistsSongs(Composer) Check Values           OK
ArtistsSongs(Conductor) Check Counts          OK
ArtistsSongs(Conductor) Check Values          OK
ArtistSongs(Lyricist) Check Counts            OK
ArtistsSongs(Lyricist) Check Values           OK
GenresSongs(Genre) Check Count                OK
GenresSongs(Genre) Check Values               OK
ArtistsAlbums(Album) Check Count              OK
ArtistsAlbums(Album) Check EQUAL Values       OK
ArtistsAlbums(Album) Check LIKE Values        **ERROR**
---------------------Details-----------------------------
SELECT *
    FROM (SELECT Artists.ID AS IDArtist, Artists.Artist AS Name, Albums.ID As IDAlbum, Albums.Artist AS AlbumArtist
    FROM Artists, ArtistsAlbums, Albums
    WHERE ArtistsAlbums.IDArtist=Artists.ID
    AND ArtistsAlbums.IDAlbum=Albums.ID)
    WHERE AlbumArtist NOT LIKE '%'||REPLACE(REPLACE(Name,'@','@@'),';',';;')||'%'
[IDARTIST] [NAME] [IDALBUM] [ALBUMARTIST]
[6253] [Æthenor] [8222] [Aethenor]
---------------- 1 OF 1 Errors Listed------------
 
Songs->Albums: Check Values                   OK
 
 
----Checking Podcast Tables---
Podcast(Songs->PodcastEpisodes)               OK
Podcast(PodcastEpisodes->Songs)               OK
Podcast(Genres->GenresSongs->Songs)           OK
 
 

----End of Report(Error Count: 2)----


[Lowlander]

Did you run DB_Clean?

[Onweerwolf]

Yes!

[Onweerwolf]

Anyone? Is there a way to check what playlist has ID 67 that seems to give all those errors?

[nynaevelan]

You can run the following sql query using Bex's SQL Viewer script.

Code: Select all

SELECT IDPlaylist, PlaylistName FROM Playlists WHERE IDPlaylist = '67'

[Onweerwolf]

Thank you, I will try that.

[nynaevelan]

Also, Bex's Playlist in Main Window script has an option to clean up playlist missing links. I think if you do a search in the beta forum he also has some sql queries out there to fix them using the SQL Viewer script. I remember him helping me on many occasions with my playlists and I think this was one of the types of things he helped me fix.

[Onweerwolf]

Ok, so I ran that query but get a blank result. Running a query for a playlist with a different ID does get me a result so I know i'm doing it right.

Is it possible that this is caused by an autoplaylist that has a non-existing playlist as a criterion?

[Onweerwolf]

Okay i used Bex's playlist in main window script and fixed a number of those errors. However after that DB_Audit keeps reporting one error like this:

Code: Select all

PlaylistSongs.IDPlayList -> Playlists.IDPlaylist    167444  167444      1 **Error**
Link not in linked table                      **ERROR**
---------------------Details-----------------------------
SELECT *
    FROM PlaylistSongs
    WHERE IDPlayList<>-1
    AND IDPlayList NOT IN (SELECT IDPlaylist
    FROM Playlists)
[IDPLAYLISTSONG] [IDPLAYLIST] [IDSONG] [SONGORDER]
[25421] [Genesis] [6873] [Trespass]
---------------- 1 OF 1 Errors Listed------------

Now what I did the first time is find the track with the error and remove it from the library (using the copy/paste metadata script to move all info from the old entry to a new entry which was a copy of the original mp3 file) then the next time I run DB_Audit it list a different song as having an error. This song was added directly after the one I had just removed (SongId +1), I also removed that one and the same thing keeps happening. It started with songs added after the 26th of February 2008. These songs are not in the same playlist either.

I'm confused.

edit: additionally the reason why I started to use these database tools is because if this error I always receive when maintaining the library and using 'optimize database (complete):



I was hoping I could fix that error by using the cleaning tool. Maybe this helps?