Limit smart playlists ?

[rpedde]

@riro wrote:

Is it hard to implement some way where you could add the option to create a sql-query in the playlists-table ?

No, in fact they used to be raw sql. But that limits my database backend to being sql. I need to be able to support arbitrary backend databases, particularly to be able to use the server as a daap server for a separate music collection (webjuke, xmms2, etc). Or even to be able to use it with a slimmer db backend (gdb), or a non-db backend (Topfield PVR).

In short, it just makes sense to abstract the backend. ‘Course, I could just write a sql parser and interpret the sql command against an abstract backend, but it’s easier to use a tiny query languge and convert that to sql rather than the other way around.

[rpedde]

@fizze wrote:

wow, that DB code looks weird.
is idx an auto-increment field? it should be a unique key anyhow. in oracle you do that with a sequence. anywho….

No, it’s not. The key is “id”, and that is an auto increment. idx is part of a composite key, along with file path. So for example, an iso image might have a composite key of someiso.iso,1 someiso.iso,2 being first and second track in the iso image. That way you have have one physical file (or script) with multiple song entries. It should default to 0 though. :/ Guess I should look at my table definition again.

the statement

 insert into playlistitems select NULL,154,id from songs order by time_added desc limit 100; 

[/code]
looks weirdish too.

sqlite doesn’t have a full parser, and stuff that would be illegal on other sql implementations is cheerfully accepted. 🙂 So it doesn’t *require* the parenthesis around the subselect, although sql99 clearly does. My bad.

 INSERT INTO playlistitems

(SELECT NULL, 154, id

FROM songs

ORDER BY time_added DESC limit 100); 

that doesnt even look like proper SQL to me. you probably meant:

(SELECT id from SONGS ORDER by time_added DESC limit 100);

in that sub-select.

that deffo does a full table scan, since there are no indices.
I guess the last 100 indices of songs would suffice, since its a auto-increment field, or sequence of some-sort, right?

Nope, that’s sql99. Just selecting a 3-tuple into playlistitems where two of the columns are constant and the other is the result of the query.

As far as a table scan, yeah. Sure does. But if it’s run as part of a nightly cron script or something, that wouldn’t be so bad.

Last 100 id’s isn’t quite right either — they would get reset after a full rescan. The date_added is based on ctime, so is probably a better indicator than id. At least after a full rescan.

— Ron

[fizze]

hm ok, Im a spoilt Oracle kid 😉

well, for the indices (idx) – if its just an auto-increment, that doesnt mean its bound to be complete? as in – without gaps?
its does not need to be, anyhow.

Then, for a fresh scan (not full) the new items would have the highest idx, wouldnt they?

A full rescan kinda kills most of those since the date_added gets reset, doesnt it ? 😉

[riro]

@rpedde wrote:

In short, it just makes sense to abstract the backend.

So… an easy way is to add some sort of limit-option (as in iTunes) to the “query” used today…?

[rpedde]

@fizze wrote:

well, for the indices (idx) – if its just an auto-increment, that doesnt mean its bound to be complete? as in – without gaps?
its does not need to be, anyhow.
[/quote[

It’s id that’s the oid, not idx.

But yeah, it’s complete, but it’s in the order it gets scanned from disk, not the order in which they were ripped into the collection.

A full rescan kinda kills most of those since the date_added gets reset, doesnt it ? 😉

Mostly, but you figure the date added is the date the file was created, and that is either the date in the db, or the ctime, whichever is older. So yeah, if you modified the file after it was added and did a full rescan, then you lose the ctime data, and the date_added becomes the date it was modified. But it’s better than nothing, I guess.

[rpedde]

@riro wrote:

@rpedde wrote:

In short, it just makes sense to abstract the backend.

So… an easy way is to add some sort of limit-option (as in iTunes) to the “query” used today…?

/me nods.

I’m thinking something close to sqlish like:

limit 100
order date_added asc
title != “”

Kind of contrived on the “all songs” query. But something like that.

— Ron

[fizze]

well if you sort by date_added ASCENDING, you will get the oldest ones first 😉

so this query would actually show the 100 oldest songs.
But well, I’m splitting hairs now.

[riro]

@rpedde wrote:

limit 100
order date_added asc
title != “”

If you add the option to order on the fields that are available in the webinterface and limit on nof songs you can do a lot more with the smart playlists.

Might be nice to have the option to limit on length… …well I dont know why but what a heck, when you are at it.. why not 🙂

Why not a random order, kinda hard with standard sql-queries…

[rpedde]

@riro wrote:

Why not a random order, kinda hard with standard sql-queries…

A fair number of them implement it (order by random), otherwise it could be done outside the sql for it. After all, not everything has to be translated into sql — if I’m using some other database backend, it might not be able to order by column, so I’ll be implementing a generic ordering scheme for those that can’t do ordering on the db side. So I can do the same thing with random.

But yeah, random is another one that’s been asked for a lot.

— Ron

[Author]

Posts