Skip to main content

From Amarok Wiki

(Redirected from Postgresql HowTo)

Deutsch | English


Warning: PostgreSQL will 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.

Contents

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 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 "http://amarok.kde.org/wiki/PostgreSQL_HowTo"