You are here: Home » Topic » Limit smart playlists ?

Limit smart playlists ?

This topic contains 18 replies, has 3 voices, and was last updated by  rpedde 10 years, 11 months ago.

Viewing 15 posts - 1 through 15 (of 19 total)
  • Author
    Posts
  • #892

    riro
    Participant

    Is there any way to limit the smart playlists?

    I want to create a playlist with my 100 last additions to the library…

    #7913

    rpedde
    Participant

    @riro wrote:

    Is there any way to limit the smart playlists?

    I want to create a playlist with my 100 last additions to the library…

    Not currently. I’m finishing up the localization and backporting some of the fixes for the most annoying bugs into a 1.1 release for Roku, then I’ll be working on modularizing the database. That will come from that work.

    But yeah, I want to do limits and custom sort orders for playlists.

    #7914

    riro
    Participant

    Well.. another script that I cron every night works…

    SQLITE="sqlite3"
    DATABASE="/opt/var/mt-daapd/songs3.db"
    PLSLIMIT=$1
    PLSNAME=$2

    echo "# --- Auto generated" >$PLSNAME
    echo "# Generated `date` " >>$PLSNAME
    $SQLITE $DATABASE 'SELECT path FROM songs ORDER BY time_added DESC LIMIT '$PLSLIMIT';'>>$PLSNAME
    #7915

    fizze
    Participant

    Sweet idea πŸ™‚

    Hm nice to see how sqlite works different from ‘real’ SQL here.
    I really have to dig up all those handy cron jobs, and add some polish to my slug somewhen πŸ™‚

    #7916

    rpedde
    Participant

    @riro wrote:

    Well.. another script that I cron every night works…

    SQLITE="sqlite3"
    DATABASE="/opt/var/mt-daapd/songs3.db"
    PLSLIMIT=$1
    PLSNAME=$2

    echo "# --- Auto generated" >$PLSNAME
    echo "# Generated `date` " >>$PLSNAME
    $SQLITE $DATABASE 'SELECT path FROM songs ORDER BY time_added DESC LIMIT '$PLSLIMIT';'>>$PLSNAME

    Oh, yeah… like that it’s doable. In fact, there’s no reason you could dummy up static playlists right in the database itself using sql queries (those are playlist type 0).

    But yeah. There’s another example of why sqlite is a nicer backend than gdbm, just because you can get stuff from it.

    Nice example, btw.

    – Ron

    #7917

    riro
    Participant

    @rpedde wrote:

    Oh, yeah… like that it’s doable. In fact, there’s no reason you could dummy up static playlists right in the database itself using sql queries (those are playlist type 0).

    Oh… it’s that easy.

    Then I know what Im doing next time Im home. πŸ™‚

    I have a few playlists that I generate every night…

    Will get back (for reference) with a small sqlite-script.

    #7918

    riro
    Participant

    @rpedde wrote:

    Oh, yeah… like that it’s doable. In fact, there’s no reason you could dummy up static playlists right in the database itself using sql queries (those are playlist type 0).

    After a “few” days on the road Im back…

    Did a few attempts to figure it out, but no luck.

    What columns (any specific name/order) do you need to return from the query?

    #7919

    rpedde
    Participant

    @riro wrote:

    @rpedde wrote:

    Oh, yeah… like that it’s doable. In fact, there’s no reason you could dummy up static playlists right in the database itself using sql queries (those are playlist type 0).

    After a “few” days on the road Im back…

    Did a few attempts to figure it out, but no luck.

    What columns (any specific name/order) do you need to return from the query?

    First, make your playlist:

    [email protected]:~$ sqlite /path/to/songs.db
    sqlite> insert into playlists (title,type,items,db_timestamp,idx) values (‘Recent Adds’,0,100,0,0);
    sqlite> select last_insert_rowid();
    154
    sqlite> insert into playlistitems select NULL,154,id from songs order by time_added desc limit 100;

    Note that once you have the id (154), you can just do the insert line. Or maybe your nightly cron job would do


    delete from playlist items where playlistid=154; insert into playlistitems ...

    Ooh. Except I just noticed a full rescan deletes those. I’ll fix that for next nightly.

    — Ron

    #7920

    riro
    Participant

    @rpedde wrote:

    Or maybe your nightly cron job would do


    delete from playlist items where playlistid=154; insert into playlistitems ...

    Ooh. Except I just noticed a full rescan deletes those. I’ll fix that for next nightly.

    Ok, so it’s a semi-static playlist… then it’s no big difference than making a m3u-list…

    What I was wishing for was a non-cron fully “automated” way to create sql queries.

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

    Something like


    sqlite> insert into playlists (title,type,items,db_timestamp,idx,query) values ('Recent Adds',99,100,0,0, 'select xyz from abc');
    #7921

    fizze
    Participant

    well πŸ˜‰

    sqlite> insert into playlists (title,type,items,db_timestamp,idx) values ('Recent Adds',0,100,0,0);
    sqlite> select last_insert_rowid();
    154
    sqlite> insert into playlistitems select NULL,154,id from songs order by time_added desc limit 100;

    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….

    the statement

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

    [/code]
    looks weirdish too.

     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?

    [/code]

    #7922

    rpedde
    Participant

    @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.

    #7923

    rpedde
    Participant

    @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

    #7924

    fizze
    Participant

    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 ? πŸ˜‰

    #7925

    riro
    Participant

    @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…?

    #7926

    rpedde
    Participant

    @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.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic.