Database Tools

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Database Tools

Post by Owyn » Thu Nov 19, 2009 7:48 am

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.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies

hintergrundrauschen
Posts: 180
Joined: Sat Mar 29, 2008 6:20 pm

Re: Database Tools

Post by hintergrundrauschen » Thu Nov 19, 2009 12:02 pm

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)?
Claude

Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: Database Tools

Post by Owyn » Thu Nov 19, 2009 12:28 pm

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.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies

hintergrundrauschen
Posts: 180
Joined: Sat Mar 29, 2008 6:20 pm

Re: Database Tools

Post by hintergrundrauschen » Thu Nov 19, 2009 1:00 pm

OK, thanks!
Claude

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: Database Tools

Post by nynaevelan » Sun Dec 06, 2009 9:07 am

Owyn:

Is there a way to have the report open when AuditDB is run??
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: Database Tools

Post by Owyn » Sun Dec 06, 2009 9:26 am

That would require a change to the script. Will take a look around for an appropriate widget to browse the report file.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies

Onweerwolf
Posts: 660
Joined: Tue Dec 12, 2006 5:32 pm
Location: The Netherlands

Re: Database Tools

Post by Onweerwolf » Fri Nov 26, 2010 10:49 am

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)----
Image

Lowlander
Posts: 47578
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Re: Database Tools

Post by Lowlander » Fri Nov 26, 2010 10:51 am

Did you run DB_Clean?
Lowlander (MediaMonkey user since 2003)

Onweerwolf
Posts: 660
Joined: Tue Dec 12, 2006 5:32 pm
Location: The Netherlands

Re: Database Tools

Post by Onweerwolf » Fri Nov 26, 2010 11:20 am

Yes! :)
Image

Onweerwolf
Posts: 660
Joined: Tue Dec 12, 2006 5:32 pm
Location: The Netherlands

Re: Database Tools

Post by Onweerwolf » Tue Nov 30, 2010 6:56 am

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

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: Database Tools

Post by nynaevelan » Tue Nov 30, 2010 8:34 am

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

Code: Select all

SELECT IDPlaylist, PlaylistName FROM Playlists WHERE IDPlaylist = '67'
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

Onweerwolf
Posts: 660
Joined: Tue Dec 12, 2006 5:32 pm
Location: The Netherlands

Re: Database Tools

Post by Onweerwolf » Tue Nov 30, 2010 8:35 am

Thank you, I will try that.
Image

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: Database Tools

Post by nynaevelan » Tue Nov 30, 2010 8:39 am

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.
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

Onweerwolf
Posts: 660
Joined: Tue Dec 12, 2006 5:32 pm
Location: The Netherlands

Re: Database Tools

Post by Onweerwolf » Tue Nov 30, 2010 11:42 am

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?
Image

Onweerwolf
Posts: 660
Joined: Tue Dec 12, 2006 5:32 pm
Location: The Netherlands

Re: Database Tools

Post by Onweerwolf » Tue Nov 30, 2010 2:39 pm

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):

Image

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

Post Reply