Connecting mpg123-0.59r to MySQL



Credits

Many thanks to Dominik Brettnacher <domi@saargate.de>, who wrote the first patch in this direction. You can find it at his site.

[Top]

Overview

Some time ago, I had a thought about mpg123 fetching its MP3 data (the whole file) from a database. As I am from Linux World, I wanted to use MySQL. Then I saw a patch for mpg123 written by Dominik Brettnacher <domi@saargate.de> at Freshmeat which was mentioned to do some nice things. About two weeks later (about christmas) I got some time to test it. This patch allows to get file names from a MySQL database server. However, it was a bit inflexible since a running MySQL server is required for program startup. Besides that the patch didn't allow to mix database query and file names given at command line.

At the moment, mpg123-mysql will be capable of querying file names from a MySQL database server. Nowadays it also allows to automagically add files given at command lines to the table and to remove those which are no longer available... However, this is done on a per-user basis to allow all users to use only one table (provided by the system operator). All these features require additional command line options, so mpg123-mysql will behave like the unpatched version for compatibility;) Btw, this is very abstract and can also be used as playlist support;)

[Top]

What you need

Just right now, the MySQL features are only available as replacement files and as a patch against mpg123-0.59r. So you will need this to use it:

To make use of your new MySQL capable mpg123, you should also have a MySQL server up and running...

To use the new features, you need to have a database with a table as well. Here's the SQL query that creates this table for you: CREATE TABLE mp3s (rating INT, path VARCHAR(255), user VARCHAR(100))

I'm not sure whether or not it is okay to use VARCHAR(255) for path. Your system may allow much longer path and file name. In this case you should use the "BLOB" or "LONGBLOB" field type.

[Top]

Bugs

I started to work at christmas evening at this little project, and I only work at free moments on it. So there will be a lot of bugs, but basically it seems to work. Here are critical bugs:

[Top]

Installation

First, get the source code of mpg123-0.59r. Extract the tar archive:
tar xzvf mpg123-0.59r.tar.gz

Then patch the source code to support MySQL access. You may do this by
gzip -cd mpg123-0.59r-mysql-patch-<VERSION>.gz | patch -p0
(OR) bzip2 -cd mpg123-0.59r-mysql-patch-<VERSION>.bz2 | patch -p0
(OR) cat mpg123-0.59r-mysql-patch-<VERSION> | patch -p0
according to the compression type in which you downloaded the patch or by adding/replacing mpg123's files by those you find at the versions page.

Then you can build mpg123-0.59r as usual. Please read the INSTALL file for how to do that. That's all...

[Top]

Configuration

Just right now there are four stages of configuration. Each one has more "power" than the proceeding one. These steps are:

In the following list you'll always find three values:

You may use any of them (at the apropriate place, of course;), but think of their different power! If you encounter problems connecting to the database server or during updates to the table, please recompile with MYSQL_DEBUG set. Then you will see the progress of stepping through the configuration files and you'll get the final configuration used by mpg123-mysql.

You basically need to set:

Important: Don't forget that the config file parser is (at the moment) not capable of handling values with spaces in them. In this case you need to use command line options, environment variables or compile-time options.

[Top]

Usage

With "-A" or "--all" mpg123-mysql will query for all available file names for that particular user.

With the "--auto-update" option you tell mpg123-mysql to automatically update the actually played song's rating value. A completely played song gets some points added while an aborted song gets some subtracted. If you play songs whose path was queried througt the database (at least -Q/--query or --all was given at command line) they will be played in order of their rating value.

The "--auto-insert" option will cause mpg123-mysql to automagically insert new file names (given at command line) into the database's table. "--auto-delete" will remove files which are no longer accessible.

As it is gererally a nice idea to use --all, --auto-update, --auto-insert and --auto-delete together if you simply want to listen to some music, you may give the single option --mysql to activate them all;)

The "-Q string" or "--query string" option allow you to search case-insensitively for string through the list of file names. This is not very comfortable (because your MP3 files need to have very useful file names), but I'm thinking about implementing a search through the ID3 tags as well.

All other options are related to configuration and described there.

[Top]

Versions and Downloads

Please look at the versions page.

[Top]

Contact and Bug Report

There will be a lot of bugs in my patch. Please report them to me. If you have any suggestions, comments or feature request, please contact me at <jbglaw@lug-owl.de>. If you really like it, you may send a postcard to Jan-Benedict Glaw, Hamsterweg 3, 33335 Gütersloh, Germany.

[Top]


© December 31, 1999 by Jan-Benedict Glaw <jbglaw@lug-owl.de>
Last edited: