Backup PlayHistory Table Script

[nynaevelan]

Hi:

In the last few days I have had to rebuild my database due to a malformed disk error, I still do not know what I did to cause it but I thought it best to rebuild my db from scratch instead of trying to find the error(s). This process was pretty smooth thanks to all the great scripts that are available and some copy/pasteing from SQLite. However there was one area which I was not able to recover, and which is the part that hurts the most (and you have no idea how much it hurts). Can someone tell me if it is possible, and if so if someone is willing, to create a backup script which would essentially backup the playhistory table as well as the Playcount and Last Played fields from the Songs table? Ideally this script would also have the possibility to restore and overwrite the playcounts/table once the backup is used to restore data. Is this out of the realm of possibility because I sure would not like to lose my playdata again. I know your first thought would be to use Trixmoto's Backup script but if there is problems in the db, then they will exist in all the backups, as was what happened in my case and I do not know of a way to revert back through the backups to find a uncorrupted copy. And there are scripts available to recover all the data from a previous database except the playhistory info. I would be eternally grateful if someone wanted to accept this challenge because I would like to get my playhistory back if at all possible.

Nyn

[Eyal]

I know your first thought would be to use Trixmoto's Backup script but if there is problems in the db, then they will exist in all the backups, as was what happened in my case and I do not know of a way to revert back through the backups to find a uncorrupted copy.

Could it be only wrong MM version? Don't forget that database format has changed from 3.0 to 3.1.

[nynaevelan]

If only it was that simple, no I have been on 3.1 through all the betas, and no this problem did not start with a change in updates. I created the problem, although I do not know how and what to do to fix it. Unfortunately this was bound to happen, I have been having intermittent problems with my db for quite some time but this latest catastrophy is killing it. Personally, I think the only thing it is good for is to recover data from, I cannot continue to use it reliably.

Nyn

[ZvezdanD]

I know your first thought would be to use Trixmoto's Backup script but if there is problems in the db, then they will exist in all the backups, as was what happened in my case and I do not know of a way to revert back through the backups to find a uncorrupted copy.

I think that it would be the best if Trixmoto add such thing to his Backup script. Did you ask him for it?

I don't understand how you know that all your backups are corrupted. Did you try to restore backups one by one to find out which one is the last correct?

By the way, if you just want to restore Played table from some old backup, it could be done with just few SQL commands in some SQLite program or Bex's SQL Viewer:

Code: Select all

ATTACH DATABASE c:\temp\MM.DB AS BackupDB
DELETE FROM Played
/* use the previous line only if you want to remove all existing played data */
INSERT OR REPLACE INTO Played SELECT * FROM BackupDB.Played
DETACH DATABASE BackupDB

[Teknojnky]

That would not work for restoring playhistory into a newly scanned database, would it?

I mean, wouldn't all the track/album/etc ID's be all different?

[nynaevelan]

I was hoping the filename would be able to be used instead, or some other tag field that is unique to each file. Or some other indicator to select the right tracks.

Nyn

[ZvezdanD]

Yeah, you are right. She should add some kind of matching test, something like WHERE Artist = BackupDB.Songs.Artist AND Title = BackupDB.Songs.Title AND ...

Code: Select all

INSERT OR REPLACE INTO Played SELECT BackupDB.Played.IDPlayed, ID, BackupDB.Played.PlayDate FROM BackupDB.Played LEFT JOIN BackupDB.Songs ON BackupDB.Played.IDPlayed = BackupDB.Songs.ID LEFT JOIN Songs ON Songs.Artist = BackupDB.Songs.Artist  AND Songs.Title = BackupDB.Songs.Title

[ZvezdanD]

Well, if you have same paths, then instead Artist AND Title matching you could use Path = BackupDB.Songs.Path.

[Teknojnky]

The played table consists of only,

IDPlayed, IDSong, PlayDate

so it would not be possible to simply match up an exported playhistory table to a newly scanned DB.

It may or may not be possible if the songs table was included, then somehow cross-referenced based on path, but that would seem to be alot more complex than simply copying the whole DB itself to a backup folder/drive.

Honestly, my playlists/autoplaylists are far more important than play history.. I mean really, did you really keep play history of all songs you listened to from the time you were born, or less exaggerated, from before you used MM?

Don't get me wrong, my database is 700+ megs and dates back to 2005 and mm 2.4/2.5.

But if I lost my DB (and I have in the past had to go back to an older db and try to fix/update it to current tracks), the only thing that would hurt is losing my collection of playlists, which for the most part is just a matter of re-doing specialized auto-playlists and re-importing playlists I've burned to mp3 or cdaudio discs.

Anyway!

[nynaevelan]

I guess that is where we differ, the majority of my playlists are built based on the plays, and ironically all my playlists are saved with the use of Trixmoto's Backup Playlists and ZvezdanD's Export M3Us scripts. But while I was not 100% anal about the plays, they were approximately 80% accurate. I had a set of playlists which are/were being used just based on the number of plays and when they were played. And I have been running some stats to try to see my listening habits, so yes they are important to me. This is the one part of my db that I could not save, everything else was able to be backed up and restored or restored with the use of copy/paste. I would reallllllllly hate to lose 2 1/2 years of data, but if I have to then I guess I have to.

Also, copying the database is not helpful if it is corrupted, which is why I needed to start a new database. Strangely enough it looks like Helium has something better than MM because it has a feature to export and import play data.

The only other way I could think of is some kind of script/utility that could check the integrity of the db and give the user some kind of warning. Or maybe a tool to scan a backup for any corruption or problems.

Nyn

[Teknojnky]

malformed database is typically from physical disk errors, either power outage or disconnected drive while data was being written.

if you had previously working db's backed up, they should be safe from the malformed image (unless of course the whole drive itself crashed), albeit this does not address any internal database consistencies but at least you would have a working and nearly current db.

I agree that MM could have better data export/import facilities, in particular it should be trivial for a user to import/export auto-playlists, but that of course would be own personal pet peeve.

maintain database should fix most logistical errors, however it does not (and probably should not) address errors at the data level.

[Benn]

Nyn, I know it doesn't help now, but for the future: have you made your DB more robust? (Assuming Teknojnky was right about a physical error) http://www.mediamonkey.com/support/inde ... ticleid=75

[Benn]

Also, I'm with Nyn on the playhistory issue: MM's playhistory table (plus of course Bex's amazing PlayHistory script) is my favourite thing about the program; I love to know exactly what I've been listening to and to easily make playlists of my best tracks. So any further development of this aspect would be amazing..

[nynaevelan]

Nyn, I know it doesn't help now, but for the future: have you made your DB more robust? (Assuming Teknojnky was right about a physical error) http://www.mediamonkey.com/support/inde ... ticleid=75

Yes Benn I do have those tweaks on my machine, unfortunately with Vista that doesn't always prevent those shutdown errors.

Nyn

[nynaevelan]

malformed database is typically from physical disk errors, either power outage or disconnected drive while data was being written.

I do not think my problems started with a disk failure but more from Vista errors. I tried going back through my backups but I couldn't find one that did not give me the error when I tried to run the Complete Maintain Database. It worked fine for a quick maintain but I thought it best to create a new db rather than lose more integrity. So far for August alone I have 142 backups and yet I am still crippled. Also, I have backups on a secondary drive and they too get the same errors so this cannot be disk errors.