Re: Amarok: migrating the database from PostgreSQL to SQLite?
On Saturday 28 October 2006 12:34, Michael Schuerig wrote:
> I'm in the process moving my music collection to an external drive so
> that I can carry it around among different computers. Until now, I've
> used PostgreSQL as Amarok's storage engine, but that presumably won't
> work with my new setup. My idea is to use SQLite3 and with a symlink
> put ~/.kde/share/apps/amarok on the external drive. I don't yet know
> for sure if this will work, but what I'm unsure about is how or if at
> all to move the database from PostgreSQL to SQLite3. I can, of
> course, dump the PostgreSQL db, but is there a way to import it into
> SQLite?
To answer myself: In my case it boiled down to this
* In Configure/Collection, select SQLite as the database.
* Let Amarok re-index the collection.
* Dump the statistics table from the PostgreSQL database
$ pg_dump -a -d -t statistics amarok > /tmp/statistics.sql
* Edit /tmp/statistics.sql so that only the INSERT statements remain.
Replace false with 0.
* On the command line connect to the newly created SQLite database
$ sqlite3 /home/michael/.kde/share/apps/amarok/collection.db
* Load the statistics
sqlite> .read '/tmp/statistics.sql'
* Hope everything works well...
This did it in my case, YMMY. To be safe, before you start this process,
make backups of ~/.kde/share/config/amarokrc, ~/.kde/share/apps/amarok,
possibly even your original PostgreSQL database (pg_dump amarok >
amarok.sql).
All that said, my first impression of SQLite is that it is painfully
slow compared to PostgreSQL.
Michael
--
Michael Schuerig
mailto:michael@schuerig.de
http://www.schuerig.de/michael/
Reply to: