Amarok/Archives/Amarok 1.4/User Guide/PostgreSQLHowTo

From KDE Community Wiki

Warning: PostgreSQL is be deprecated in Amarok 2.


Amarok supports a PostgreSQL database backend in addition to the built-in SQLite database engine. To get PostgreSQL support compiled-in, you need to specify "--enable-postgres" as a configure parameter and re-run "make install" as root. Your configure line probably will look something like this: 

$ ./configure --enable-postgresql

When you have the postgres deamon running, you need to set up an user to work with, if you haven't already. For example, to create the user "dude": 

su - postgres
createuser -P dude
  • Note - the -P option is for creating a user with a password. For more options use:
createuser --help

If everything went ok you should be able to create a new database as dude. 

exit
createdb amarokcollection

EDIT: Better: 

createdb -O dude amarokcollection

Please, pay attention on line above: it's -O (capital letter "o"), not zero.


You can access the database by 

psql amarokcollection

You may get an error regarding the database 'root' not existing if you do not specify the database name to connect to on the command line. root is the default database loaded if nothing is specified on the command line.

If for any reason you want to delete the database, 

dropdb amarokcollection

In amarok, go to Configure Amarok and then to Collection. Choose PostgreSQL. You will have to specify the host (probably localhost), port (probably 5432), and the name of the db that you have created for it. Additionally, the username and password of a user who has write access to the given database needs to be specified.

"surfsup" is the password I chose for user "dude" above.

PostgreSQL Remote Server Gotcha: Most PostgreSQL installs do not listen for remote tcp_ip sockets by default and the postmaster daemon listens only to localhost by default.

So if you get errors about not being able to connect to the server or database, (_not_ password related errors) then you will have to edit your startup script to use the -i parameter for the postmaster.

On SuSE 9 & 10 systems, edit /etc/sysconfig/postgresql and add "-i" to the POSTGRES_OPTIONS variable.

For other systems, edit /var/lib/pgsql/data/postgresql.conf and change the "listen_addresses" option to include all the IP addresses that the postmaster should listen on.

Then restart the PostgreSQL daemon. You should then update your firewall accordingly.

SQLite -> PostgreSQL

The easiest way to convert your SQLite-DB seems to be the following:

  • backup your collection.db
  • switch to PostgreSQL and rebuild your collection
  • quit amarok
  • get your statistics back w/ the following commands:
cd ~/.kde/share/apps/amarok && \
sqlite3 collection.db .dump | grep 'INSERT INTO "statistics"' |\
sed -e 's/,0)/,FALSE)/' -e 's/,1)/,TRUE)/' |\
psql -U <amarok-user> <amarok-db>

Of course, replace <amarok-user> and <amarok-db> accordingly. We need sed because otherwise psql complains we're trying to insert an integer into a boolean field.

PostgreSQl -> SQLite (Amarok 1.4.10 vs. PostgreSQL 8.3.1)

As postgres won't be supported with Amarok 2 anyway, moving back to sqlite might eventually make it possible to keep the old collection db. This is based on the "MySQL > SQLite" howto:

Backup the old SQLite database and export the postgres database (with some conversions). Because of the "-D" switch to pg_dump, sqlite takes forever (at least with a more than 1.5y old database of 16000+ titles). There might be a more elegant way to do the true/false conversion which avoids strange false positives. 

cd ~/.kde/share/apps/amarok && \
mv collection.db collection.db.old && \
pg_dump -D --data-only -U postgres amarok | grep -v magnatune_ | sed -e "s/\\\n/\n/g" -e "s/\\\'/\'\'/g" -e "s/\\\\\"/\"\"/g" -e "s/false)/0)/" -e "s/true)/1)/" -e "s/, false,/, 0,/g" -e "s/, true,/, 1,/g" > amarok.sql && \
sqlite3 collection.db

Paste the following SQL statements at the prompt to create the tables and indices: 

CREATE TABLE tags (url VARCHAR(1024),dir VARCHAR(1024),createdate INTEGER,modifydate INTEGER,album INTEGER,artist INTEGER,composer INTEGER,genre INTEGER,title VARCHAR(255),year INTEGER,comment TEXT,track NUMERIC(4),discnumber INTEGER,bitrate INTEGER,length INTEGER,samplerate INTEGER,filesize INTEGER,filetype INTEGER,sampler BOOL,bpm FLOAT,deviceid INTEGER);
CREATE TABLE album (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE artist (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE composer (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE genre (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE year (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE images (path VARCHAR(1024),deviceid INTEGER,artist VARCHAR(255),album VARCHAR(255));
CREATE TABLE embed (url VARCHAR(1024),deviceid INTEGER,hash VARCHAR(1024),description VARCHAR(255));
CREATE TABLE directories (dir VARCHAR(1024),deviceid INTEGER,changedate INTEGER);
CREATE TABLE uniqueid (url VARCHAR(1024),deviceid INTEGER,uniqueid VARCHAR(32) UNIQUE,dir VARCHAR(1024));
CREATE TABLE admin (noption VARCHAR(255), value VARCHAR(255));
CREATE TABLE related_artists (artist VARCHAR(255),suggestion VARCHAR(255),changedate INTEGER );
CREATE TABLE amazon ( asin VARCHAR(20), locale VARCHAR(2), filename VARCHAR(33), refetchdate INTEGER );
CREATE TABLE lyrics (url VARCHAR(1024), deviceid INTEGER,lyrics TEXT, uniqueid VARCHAR(32));
CREATE TABLE playlists (playlist VARCHAR(255), url VARCHAR(1024), tracknum INTEGER );
CREATE TABLE labels (id INTEGER PRIMARY KEY , name VARCHAR(255), type INTEGER);
CREATE TABLE tags_labels (deviceid INTEGER,url VARCHAR(1024), uniqueid VARCHAR(32), labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );
CREATE TABLE podcastchannels (url VARCHAR(1024) UNIQUE,title VARCHAR(255),weblink VARCHAR(1024),image VARCHAR(1024),comment TEXT,copyright VARCHAR(255),parent INTEGER,directory VARCHAR(255),autoscan BOOL, fetchtype INTEGER, autotransfer BOOL, haspurge BOOL, purgecount INTEGER );
CREATE TABLE podcastepisodes (id INTEGER PRIMARY KEY , url VARCHAR(1024) UNIQUE,localurl VARCHAR(1024),parent VARCHAR(1024),guid VARCHAR(1024),title VARCHAR(255),subtitle VARCHAR(255),composer VARCHAR(255),comment TEXT,filetype VARCHAR(255),createdate VARCHAR(255),length INTEGER,size INTEGER,isNew BOOL );
CREATE TABLE podcastfolders (id INTEGER PRIMARY KEY , name VARCHAR(255),parent INTEGER, isOpen BOOL );
CREATE TABLE statistics (url VARCHAR(1024),deviceid INTEGER,createdate INTEGER,accessdate INTEGER,percentage FLOAT,rating INTEGER DEFAULT 0,playcounter INTEGER,uniqueid VARCHAR(32) UNIQUE,deleted BOOL DEFAULT 0,PRIMARY KEY(url, deviceid) );
CREATE TABLE devices (id INTEGER PRIMARY KEY ,type VARCHAR(255),label VARCHAR(255),lastmountpoint VARCHAR(255),uuid VARCHAR(255),servername VARCHAR(255),sharename VARCHAR(255));
CREATE INDEX album_idx ON album( name );
CREATE INDEX artist_idx ON artist( name );
CREATE INDEX composer_idx ON composer( name );
CREATE INDEX genre_idx ON genre( name );
CREATE INDEX year_idx ON year( name );
CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );
CREATE INDEX album_tag ON tags( album );
CREATE INDEX artist_tag ON tags( artist );
CREATE INDEX composer_tag ON tags( composer );
CREATE INDEX genre_tag ON tags( genre );
CREATE INDEX year_tag ON tags( year );
CREATE INDEX sampler_tag ON tags( sampler );
CREATE INDEX images_album ON images( album );
CREATE INDEX images_artist ON images( artist );
CREATE INDEX images_url ON images( path, deviceid );
CREATE UNIQUE INDEX embed_url ON embed( url, deviceid );
CREATE INDEX embed_hash ON embed( hash );
CREATE UNIQUE INDEX directories_dir ON directories( dir, deviceid );
CREATE INDEX uniqueid_uniqueid ON uniqueid( uniqueid );
CREATE INDEX uniqueid_url ON uniqueid( url, deviceid );
CREATE INDEX tags_artist_index ON tags( artist );
CREATE INDEX tags_album_index ON tags( album );
CREATE INDEX tags_deviceid_index ON tags( deviceid );
CREATE INDEX tags_url_index ON tags( url );
CREATE INDEX embed_deviceid_index ON embed( deviceid );
CREATE INDEX embed_url_index ON embed( url );
CREATE INDEX related_artists_artist ON related_artists( artist );
CREATE INDEX url_podchannel ON podcastchannels( url );
CREATE INDEX url_podepisode ON podcastepisodes( url );
CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );
CREATE INDEX url_podfolder ON podcastfolders( id );
CREATE INDEX devices_type ON devices( type );
CREATE INDEX devices_uuid ON devices( uuid );
CREATE INDEX devices_rshare ON devices( servername, sharename );
CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );
CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );
CREATE INDEX playlist_playlists ON playlists( playlist );
CREATE INDEX url_playlists ON playlists( url );
CREATE UNIQUE INDEX labels_name ON labels( name, type );
CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );
CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );
CREATE INDEX tags_labels_labelid ON tags_labels( labelid );
CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );
CREATE INDEX percentage_stats ON statistics( percentage );
CREATE INDEX rating_stats ON statistics( rating );
CREATE INDEX playcounter_stats ON statistics( playcounter );
CREATE INDEX uniqueid_stats ON statistics( uniqueid );

Then import the actual data exported from MySQL. 

.read amarok.sql
.quit

PostgreSQL and multiple users

So you got a amarok PostgreSQL database and want multiple users to access the same database while not mixing together every users favourite music. By using schemas in PostgreSQL this can be solved, although you should have some basic knowledge about PostgreSQL before attempting this.

Note: There's one thing you have to do once, and that's to grant "public" select access to all the tables. I only know of one way to do this: 

grant select on admin, album, album_seq, amazon, artist, artist_seq, directories, embed, genre, genre_seq, images, label, lyrics, podcastchannels, podcastepisode_seq, podcastepisodes, podcastfolder_seq, podcastfolders, related_artists, statistics, tags, uniqueid, year, year_seq to public;

It ain't pretty, and is bound to change when new versions of Amarok are released. In other words, run a "grant select on <all tables> to public;" each time you upgrade Amarok. A better(?) idea would be to get some sort of poking device and use it on the Amarok developers until they add native support for multiple users =)

Quick guide

create user <user> password '<password>';
create schema authorization <user>;
create table <user>.statistics as select * from statistics where false;
create unique index "statistics_url_key" on <user>.statistics(url);
create index "percentage_stats" on <user>.statistics(percentage);
create index "playcounter_stats" on <user>.statistics(playcounter);
create index "url_stats" on <user>.statistics(url);
grant all on <user>.statistics to <user>;

A bit more detailed guide

Connect to your Amarok PostgreSQL database. 

create user <user> password '<password>';

Naturally you'll have to replace <user> and <password> with more sane values, for example "anotherdude" and "surfsout". 

create schema authorization <user>;

By creating a schema for the new user you can make postgresql look for a table in this schema before looking in the public schema. 

create table <user>.statistics as select * from statistics where false;

This will create a table with the name "statistics" (the table where data about your listening habbits is stored) in the schema you just created. 

create unique index "statistics_url_key" on <user>.statistics(url);
create index "percentage_stats" on <user>.statistics(percentage);
create index "playcounter_stats" on <user>.statistics(playcounter);
create index "url_stats" on <user>.statistics(url);

These commands simply create indexes (the first index is a unique index to prevent duplicates) to speed up queries. Nothing magical about them :) 

grant all on <user>.statistics to <user>;

This command will grant the new user with enough access to store the users preferences. Don't forget this step, it's an important one :)

A precise example

create user anotherdude password 'surfsout';
create schema authorization anotherdude;
create table anotherdude.statistics as select * from statistics where false;
create unique index "statistics_url_key" on anotherdude.statistics(url);
create index "percentage_stats" on anotherdude.statistics(percentage);
create index "playcounter_stats" on anotherdude.statistics(playcounter);
create index "url_stats" on anotherdude.statistics(url);
grant all on anotherdude.statistics to anotherdude;

This will create a user named "anotherdude" with the password "surfsout". Simply insert these values when you configure amarok to use PostgreSQL, and that should be it to make it work. Do note that you'll have to do this for every user you add.

Sharing a PostgreSQL database

Check the MySQL HowTo for tips how to share a PostgreSQL database among multiple clients.

Retrieved from "https://community.kde.org/index.php?title=Amarok/Archives/Amarok_1.4/User_Guide/PostgreSQLHowTo&oldid=28046"