View Full Version : DB integration branch

2004-08-13, 01:08
Ack...originally sent to the wrong list...

As I mentioned earlier this week on the list, I've started the process
of moving SlimServer over to using a more reasonable backend database.
I'm ready to start checking my changes into a branch (details of the
branch to follow), but wanted to set expectations before doing so.

So far I've done the following:

* Created a fairly flat schema that has tables for songs, genres,
artists, albums and playlists. The schema allowed me to maintain
the current Info.pm API while internally starting the process of
moving away from non-unique string identifiers for objects in the
* Maintained the existing Info.pm API, while internally creating the
notion of a DataSource. There is currently a singleton
LocalDataSource. The eventual goal is to be able to support
mutiple DataSources, some of which may be remote.
* Implemented the LocalDataSource using Class::DBI and the Perl DBI
SQLite driver. Class::DBI is used to create a class abstraction on
top of SQL tables. While it doesn't completely hide SQL, it does
allow you to contain it to a small set of packages, rather than
pepper it all around your code.
* Lightly tested browsing and searching with a small library. It's
limping along, but does have bugs, missing functionality and
performance issues.

The parts that I haven't dealt with yet and would definitely appreciate
help with are:

* Switching over from the string identifier API currently supported
by Info.pm to one that uses either objects and/or unique
identifiers, similar to the ones being discussed in the thread
started by Pat Farell.
* Creating a more "relational" database design, similar to the one
suggested by Pat. I've got some comments on Pat's strawman that
I'll put in a separate message, but I think it's a great start.
Thanks for starting the discussion, Pat!
* Figuring out a robust and efficient scheme for doing database
commits. The current branch code uses a similar scheme to the old
in-memory Perl cache - a commit is done at the end of a scan and
then at regular intervals on a timer. It's efficient, but not very
robust...especially when we start treating the database as more
than just a cache and starting putting in data that can't be
* Figuring out a reasonable story for versioning. The current branch
code simply checks a version number in the database and, if it
doesn't find a match, drops all tables and starts from scratch.
* Fixing missing or broken functionality and performance problems.
I'll try to get together and maintain a list of known problems
with the branch code.

As I said, it's a start. There's much work to be done and some of the
right discussions have already started. Take a look at the code, give it
a whirl, and let's start improving it.


2004-08-13, 01:24
I've checked in my DB integration changes to a branch of the CVS tree.
To get them, update or checkout with the command line option -r
BRANCH_SQL_DB. There are a few new files. I checked them into the trunk
before branching to make it easier to land them back later. They
shouldn't harm the trunk build.

I have not yet checked in the CPAN modules for DBI, DBD::SQLite, and
Class::DBI. For now, you can add them to your own Perl installation
using the CPAN shell or, with ActiveState Perl, ppm. The CPAN shell
installation command is:

perl -MCPAN -e 'install <packagename>'
perl -MCPAN -e 'shell'

The new files are:

SQL/SQLite/*.sql - Files for creating and dropping database tables. With
SQLite, all columns are variable length, so I haven't specified a max
Slim/Music/DataSource.pm - Abstract data source class. Since Perl
doesn't really support interfaces, this one's a little ugly.
Slim/Music/LocalDataSource.pm - Implementation of a the local filesystem
datasource that's based on Class::DBI. This is where some of the Info.pm
Slim/Music/DBI.pm - Class::DBI derived packages that wrap the SQL
tables. All of the in-line SQL is in this file.
Slim/Music/Info.pm has changed to use the DataSource interface for
persistent data.

Everything else is pretty much unchanged for now. More when I'm
coherent. Enjoy!