enjoy the script
Mats Ahlgren
mats_a at MIT.EDU
Wed Feb 7 20:07:00 UTC 2007
Enjoy.
Please take extra care to make sure Amarok doesn't corrupt its database* and that backup/recovery is easy...
--Mats
*(e.g. all versions of Amarok <1.4.4 had file names sporadically truncated to 20chars; not sure if that's still going on...)
--------------------------- Useful Script ----------------------------
#!/usr/bin/python
# Restore ratings from collection.db backup in sqlite3 format
# Script by Mats Ahlgren
#
# This script restores your old ratings from a backup onto your current collection, matching old songs to new songs by shared filename. Modify the path variables below in the script to match yours before using. This script has the added benefit of working if you moved your music library to a subdirectory or changed usernames, etc.
#
# Warning: Make sure to backup your current collection before restoring old ratings; this script is provided as-is
import sqlite3
backup_url = '/absolute/path/to/backup/collection.db'
target_url = '/absolute/path/to/target/collection.db' # this is usually ...myhome/.kde/share/apps/amarok/collection.db
backup = sqlite3.connect(backup_url)
c = backup.cursor()
c.execute('select * from statistics')
results = c.fetchall()
print len(results)
target = sqlite3.connect(target_url)
for line in results:
url = line[0]
filename = url.split('/')[-1]
rating = line[-1]
#print rating, filename
c = target.cursor()
cmd = 'update statistics set rating='+str(rating)+' where url like "%'+filename+'";'
print cmd
c.execute(cmd)
target.commit()
On Wednesday 07 February 2007 10:11:59 Mats Ahlgren wrote:
> Please ignore; I can figure this out on my own...
>
> Amarok really should make a database backup or journal changes, and add a recover feature; I've found it's quite prone to this sort of stuff.
>
> --Mats
>
> ---------- Forwarded Message ----------
>
> Subject: Re: database corruption -- how to fix?
> Date: Wednesday 07 February 2007
> From: Mats Ahlgren <mats_a at mit.edu>
> To: Amarok Mailing List <amarok at kde.org>
>
> Might anyone please know how to do the following in sqlite3:
>
> I have a very old backup of Amarok's collection.db.
> I'd like to transfer ratings from all songs in the old DB to the new DB.
>
> Additionally, it would be great if this could be done by changing the root directory of all files...
>
> Thank you,
> Mats
>
> On Saturday 03 February 2007 11:17:42 Mats Ahlgren wrote:
> > So a quick recap:
> > My database got corrupted and I'm trying to recover my ratings, which
> > I've spent whole days compiling.
> >
> > After looking at the database more closely with
> > > echo .dump | mysqlite3 [database] | less
> > It seems that apparently the database was recreated on top of the
> > good one -- ratings are all at 0 and scores at 75, etc.
> >
> > Is there any sort of automatic backup from which I can recover song
> > ratings?
> >
> > Thanks,
> > Mats
> >
> > On Jan 22, 2007, at 12:18 PM, Mats Ahlgren wrote:
> >
> > > On Jan 22, 2007, at 9:18 AM, Andrew Turner wrote:
> > >
> > >> Ok, well the basic approach is to try to work out if kded/hald are
> > >> working by running:
> > >> dcop kded mediamanager fullList
> > >>
> > >> That should give you a list of devices. Check the one with your
> > >> collection is listed.
> > >>
> > >> Next, using the SQLite binary (from www.sqlite.org),
> > >> SELECT id, lastmountpoint FROM devices;
> > >
> > > 1| /media/windows
> > > 2| / <--- chose this one
> > > 3| /media/windows
> > > 4| /
> > >
> > >> Identify the one that should have your collection on. check how many
> > >> songs amarok thinks there are on that device (eg for id = 5):
> > >> SELECT count(url) FROM tags WHERE deviceid = 5;
> > >
> > > 3522
> > >
> > >> It's also worth looking in ~/.kde/share/config/amarokrc for the
> > >> [Collection Folders] section (note this is _not_ the now deprecated
> > >> Collection Folders field in the [Collection] section).
> > >> The format is something like:
> > >> 5=./music
> > >> where 5 refers to the device as above, and your collection folder has
> > >> an absolute path last given by: lastmountpoint_for_device5 + ./music
> > >
> > > 2=./home/mats/Music/Library
> > >
> > >> Note: deviceid = -1 means that for some reason Amarok couldn't figure
> > >> out which device your collection folder is on. The path is specified
> > >> relative to / .
> > >>
> > >> If there are weird dupes in that section of amarokrc, or the
> > >> deviceids
> > >> are negative, but you are running kded/hald, or the deviceids are
> > >> positive but you aren't running kde/hald, problems may well happen.
> > >
> > > I have a process called kded running.
> > >
> > > What is all this telling me?
> > >
> > > Also, I tried overwriting my database.db file with an old backup;
> > > nothing happened (no statistics on any of the songs).
> > >
> > > I recently had file system corruption with ext3 (how on Earth? is
> > > this common...) and so a few random files got put in lost+found...
> > > maybe that is the culprit? If you think it is; how would I go about
> > > transferring the old DB (with everything such as ratings) to a new
> > > installation of Amarok on a fresh system?
> > >
> > > Thanks,
> > > Mats
> > >
> > >> Andrew
> > >>
> > >>
> > >> On 22/01/07, Mats Ahlgren <mats_a at mit.edu> wrote:
> > >>> No problem; thanks for replying.
> > >>>
> > >>> Unfortunately this is Amarok 1.4.3 and I'm not viewing my collection
> > >>> over NFS.
> > >>>
> > >>> Thanks,
> > >>> Mats
> > >>>
> > >>> On Jan 22, 2007, at 8:43 AM, Guðlaugur Jóhannesson wrote:
> > >>>
> > >>>> Sorry for coming in so late, but could it be that the new dynamic
> > >>>> collection
> > >>>> is giving you a hard time. This could happen if you have recently
> > >>>> upgraded
> > >>>> amarok and mount your music collection over NFS or other removable
> > >>>> media. To
> > >>>> turn it off set
> > >>>> DynamicCollection=false
> > >>>> in the [collection] section in amarokrc.
> > >>>>
> > >>>> --
> > >>>> Guðlaugur Jóhannesson
> > >>>> Tel: +354 849 8405
> > >>>> http://www.raunvis.hi.is/~gudlaugu/
> > >>>> _______________________________________________
> > >>>> Amarok mailing list
> > >>>> Amarok at kde.org
> > >>>> https://mail.kde.org/mailman/listinfo/amarok
> > >>>
> > >>>
> > >
> > > _______________________________________________
> > > Amarok mailing list
> > > Amarok at kde.org
> > > https://mail.kde.org/mailman/listinfo/amarok
> >
> > _______________________________________________
> > Amarok mailing list
> > Amarok at kde.org
> > https://mail.kde.org/mailman/listinfo/amarok
> >
>
>
> _______________________________________________
> Amarok mailing list
> Amarok at kde.org
> https://mail.kde.org/mailman/listinfo/amarok
>
> -------------------------------------------------------
> _______________________________________________
> Amarok mailing list
> Amarok at kde.org
> https://mail.kde.org/mailman/listinfo/amarok
>
More information about the Amarok
mailing list