Limit smart playlists ?

Viewing 10 posts - 1 through 10 (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:

    foo@bar:~$ 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]

Viewing 10 posts - 1 through 10 (of 19 total)
  • The forum ‘General Discussion’ is closed to new topics and replies.