PDA

View Full Version : Access to SqueezeCenter DB or should use API



psycik
2009-06-03, 20:19
Hi

I write a music player for a PVR. Basically have all the scanning etc functions going, but thought since squeezecenter does this as well (and much faster/better than me) why not try to reuse the information.

So my question is, is there an API/Web Service way of getting access to the Artist, Album title, track information stored? Or would I have to implement my own database calls to the SqueezeCenter DB directly?

Thanks in advance.

mherger
2009-06-03, 21:08
> So my question is, is there an API/Web Service way of getting access to

See http://yourserver:9000/html/docs/cli-api.html - it's a complete
command reference accessible through a simple socket connection or http
(through the json/rpc handler).

> the Artist, Album title, track information stored? Or would I have to
> implement my own database calls to the SqueezeCenter DB directly?

Don't do this. Schema changes can happen.

Philip Meyer
2009-06-04, 00:18
>> the Artist, Album title, track information stored? Or would I have to
>> implement my own database calls to the SqueezeCenter DB directly?
>
>Don't do this. Schema changes can happen.

I would do this, if you want the best performance, and not have to worry about changes to the API, which can happen.

Working out some SQL based on the DB schema as a lot easier than workin out how to use the DB API.

mherger
2009-06-04, 01:07
>>> the Artist, Album title, track information stored? Or would I have to
>>> implement my own database calls to the SqueezeCenter DB directly?
>>
>> Don't do this. Schema changes can happen.
>
> I would do this, if you want the best performance, and not have to worry
> about changes to the API, which can happen.

Correct. It's a performance vs. maintenance nightmare trade-off.

psycik - you better speak to developers who've actually taken this
decision before you, and ask them how they plan to support the upcoming SC
7.4. You'll find them in the forum:

Dr Lovegrove - of Moose fame
Pippin - iPeng author

Phil - we don't even know whether performance is an issue here. Google for
"premature optimization" :-).

--

Michael

Philip Meyer
2009-06-04, 07:35
>Correct. It's a performance vs. maintenance nightmare trade-off.
>
I find SQL easy to maintain. And schema doesn't change that radically too often. How long has it been since the last schema change? A few years I think...

>psycik - you better speak to developers who've actually taken this
>decision before you, and ask them how they plan to support the upcoming SC
>7.4. You'll find them in the forum:
>
I thought SC7.4 schema was going to be the same, and new schema only when it becomes 8.0?

>Dr Lovegrove - of Moose fame
>Pippin - iPeng author
>
Don't forget Erland, who has also looked into API and direct schema access.

>Phil - we don't even know whether performance is an issue here. Google for
>"premature optimization" :-).
>
We know that performance is an issue with the current API and CLI interfaces. Direct SQL access is much faster, and doesn't have the Perl dependency. And we know that SQLite is worse than MySQL.

But you are right, it is premature for SC8.0 new schema. But I'd hazard a big guess that going through multiple layers of Perl code to access DB content will continue to be slower than not doing it...

erland
2009-06-04, 12:21
Don't forget Erland, who has also looked into API and direct schema access.

It really depends on what you want to do.

I've used the database using SQL directly since 6.2 and so far there have been some small changes where some column names was changed in the 6.5 release. A bigger problem has been the change of database engine, we used SQLite in 6.2 and 6.3 and changed to MySQL in 6.5. It was fairly easy to support both, in some cases I had to have duplicate SQL statements but often it was just a matter of using rand() or random(). For me personally it's a lot worse now because the SQL statements has gotten a lot more complex compared to how they look like back in 2006.

A lot bigger change was the DBIx Slim::Schema DB API change introduced in 6.5, this caused lot more work than changing the SQL statements.

I think the CLI interface has changed at least as much as the database structure if we look at the period between 6.2 and 7.3, my guess is that it has changed more. Of course, there is reason for this change since it in 6.2 was an interface for third party applications while it now in 7.x is used as the primary communication interface between the Squeezebox Controller and player.

However, it should be noted that there is a plan to change to SQLite in 7.4 and there is a plan to optionally support both SQLite and MySQL in 7.4 or soon after. There is also a plan to completely change the database structure, probably in 8.0. This could make things more complicated if you use the database directly.

If the third party application runs on a separate machine it's a lot easier to use the CLI/JSON api since it's already network based and you don't have to mess around with drivers. If you talk with Dr Lovegrove (Moose developer) I'm sure he'll let you know the joy of offering support to someone that have never installed a database driver.

My personal guess is that if you only like have simple artists/albums/tracks lists, it's probably better to use CLI/JSON compared to using the database directly. The CLI/JSON interface is a lot more matured today than it was back in 2006 and it will probably also be the same or very similar after the database schema change planned for 8.0.

I can't use CLI/JSON in my plugins because it doesn't offer the functionality I need, so my only solution is to use SQL or the DBIx interface. The DBIx Slim::Schema interface has so far has been less stable than the database structure IMHO.

mherger
2009-06-04, 22:15
> I think the CLI interface has changed at least as much as the database
> structure if we look at the period between 6.2 and 7.3, my guess is that
> it has changed more.

That's true. But imho in a non-breaking way: a lot of new commands have
been introduced, the menu mode has been added to support SqueezePlay etc.
I had developed a remote control application back in the 6.x days, haven't
touched the code in >2 years, but it would still be working.

Great posting, thanks.

Michael