This SQL stuff seems closer to what Id like to see, though itd be good to see it implemented natively and supported by the database. Having two lots of database code seems like a waste. And Im not sure how heavyweight the sqllite stuff is compared to the native database. Admittedly I havent experimented much with it, so it could be better than I think.[a href="index.php?act=findpost&pid=282847"][{POST_SNAPBACK}][/a]
I implemented a simple component to export the contents of the foobar2000 database to a SQLite 3 database. The SQLite database uses the following schema:CREATE TABLE tracks (url TEXT, subsong INTEGER, PRIMARY KEY (url, subsong));
CREATE TABLE meta (trackid INTEGER, name TEXT, value TEXT);
CREATE TABLE info (trackid INTEGER, name TEXT, value TEXT);
The trackid column in the meta and info tables holds the rowid of the associated track. While this is certainly not the nicest database schema, it is capable of storing arbitrary field names and multiple values in one field*. Needless to say, queries can get quite ugly with this, for example to get the locations of all songs by Queen you would have to use something likeSELECT * FROM tracks WHERE rowid IN (SELECT trackid FROM meta WHERE name LIKE 'artist' AND value LIKE 'queen');
I don't know how fast SQL queries on this database are compared to queries on the native foobar2000 database (using playlist generator style filters or another method). I have however measured the time to create and populate the SQL database (done in a single transaction of course); it is more than ten times slower than writing an FPL playlist containing the same information.
*: Exactly what makes the database schema so cumbersome, but also key features of foobar2000.