You are here: Home » Topic » Inteligent playlists

Inteligent playlists

This topic contains 12 replies, has 3 voices, and was last updated by  fizze 10 years, 8 months ago.

Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Posts
  • #825

    riro
    Participant

    In WMP you have the option to create a playlist on “Afternoon total” and so on…

    My first thought was to create a script that kept a log of whats playing and when… and in step 2 create a static playlist based on the criteria of when its played.

    But… when thinking about how complex that will be… and my skills in perl/bash. That might not be a project that I will take to the finish-line πŸ™‚

    Anyway… is there a possibility to get firefly to record a log of songs (id & when) played as a sqlite-table?
    With that in place it opens up for a bunch of queries to the database that can result in a playlist.

    Why did I think of this…? Well I was at a Bose showroom where they presented a bunch of cool stuff with playlists…

    A bit further down the road, think about this:
    You select a playlist “My morning music” and firefly starts streaming from the library. If/when you press skip on any song firefly remembers that and keeps that in mind next time you start that smart(er) playlist.

    #7567

    rpedde
    Participant

    @riro wrote:

    Anyway… is there a possibility to get firefly to record a log of songs (id & when) played as a sqlite-table?
    With that in place it opens up for a bunch of queries to the database that can result in a playlist.

    The last play date/time is in the database. Seems like you could do that will log trawling as well. It doesn’t separate time from date, though, so it would be hard to get just a time rather than date/time.

    I might look at that though, it might be interesting to play with.

    A bit further down the road, think about this:
    You select a playlist “My morning music” and firefly starts streaming from the library. If/when you press skip on any song firefly remembers that and keeps that in mind next time you start that smart(er) playlist.

    Actually, what I really want to do is add the ability to run user scripts on events. I’m looking at doing that with s-lang. That way it would be cross-platform.

    Then maybe building a user-built repository of script to do interesting things like the stuff you mentioned.

    — Ron

    #7568

    riro
    Participant

    @rpedde wrote:

    @riro wrote:

    Anyway… is there a possibility to get firefly to record a log of songs (id & when) played as a sqlite-table?
    With that in place it opens up for a bunch of queries to the database that can result in a playlist.

    The last play date/time is in the database. Seems like you could do that will log trawling as well. It doesn’t separate time from date, though, so it would be hard to get just a time rather than date/time.

    The last play, yes. But if you play song X at 08:00 (breakfast) and 18:00 (dinner)… It isn’t that easy to do a breakfast-playlist.

    Just a small log-table with time, date, songid and if you have the time add a boolean for skipped and maybe a column identifying the client that played the song.

    With that in place… a shell-script generating playlists shouldn’t be that hard to create.

    #7569

    fizze
    Participant

    Well, all you’d need is a seperate table.

    songID, timestamp as primary keys
    client, skipped, flags should do the trick.

    better map it through a view since this might likely be changed πŸ˜‰

    all you’d need is a job that grabs the “now playing” info likewise frmo the mt-daap’s XML file, and populates the DB.
    the only obvious limitation I’d seee with that approach is stuff like ‘skipped’.

    Checking the status of the stream wouldnt even suffice, since you dont know wether $device just buffers it, or has actually played it. And even when you press ‘next’ with the file at 99.5%, what should it count as?

    I like the idea however. πŸ™‚

    With a DB like that you could even map seasonal and/or weather info. as in “sunny sunday morning tunes” or “rainy monday tunes” πŸ˜€

    #7570

    riro
    Participant

    @fizze wrote:

    all you’d need is a job that grabs the “now playing” info likewise frmo the mt-daap’s XML file, and populates the DB.

    I found a script (on this forum?) that reads the last played info and populates a rockscrobbler-logfile, and then I use rockscrobbler to Audioscrobbel (is that a word? πŸ˜• )my tunes.

    A small clip from the bash-script:


    $SQLITE $DATABASE β€˜SELECT artist,album,title,track,song_length,time_played FROM songs where time_played > β€˜$LASTRUNβ€˜ ORDER BY time_played ASC;’ | awk -F β€˜|’ β€˜{ printf "%st%st%st%st%dtLt%sn",$1,$2,$3,$4,$5/600,$6 }’ >> $OUTLOG

    echo "LASTRUN="`date +%s` > $LASTFILE

    Not that hard to convert it to first do a select and on the second line a insert into another table.
    Just need to figure out an easy way to split time_played into separate date and time-columns.

    //riro

    #7571

    riro
    Participant

    OK… a few steps on the way…

    This code will create a table, populate it with last played-info from the songs-table. And then create a view that has a formated date and time-column.


    CREATE TABLE playlistlog (
    id INTEGER NOT NULL,
    time_played INTEGER
    );

    INSERT INTO playlistlog SELECT id, time_played FROM songs where time_played > 0;

    CREATE VIEW formatedlog AS
    SELECT songs.path AS path, time(playlistlog.time_played, 'unixepoch') AS play_time, date(playlistlog.time_played, 'unixepoch') AS play_date
    FROM playlistlog, songs
    WHERE songs.id = playlistlog.id;

    With that in place you can create a query like:


    SELECT path
    FROM formatedlog
    WHERE play_time > '18:00:00' AND play_time < '18:30:00';

    And you will get a list (with filename) of songs played between 18:00 and 18:30.

    Next step… create a view with songs played more than one time in that time-frame.

    #7572

    fizze
    Participant

    well, I’d rather use to_char functions, than date() and time().

    to_char(‘dd.mm.yyyy’,$datetime) returns the date in that format.
    where
    to_char(‘hh24:mi’,$datetime) returns the time in 24h:minute format.

    you can use advanced formatting for those.

    Im coming from oracle though, so I dont know if thats available in sqlite. But i think its sql standard.

    well, the VIEW wouldnt be like:

    SELECT count(path), path FROM formatedlog
    WHERE play_time BETWEEN '18:00:00' AND '18:30:00'
    GROUP BY path
    HAVING count(path) > 1;

    however you’d like to format your date/time fields. the having is the key though. that should even order it by the count(path).

    #7573

    riro
    Participant

    A few minutes later…


    SELECT path
    FROM formatedlog
    WHERE play_time > '13:00:00' AND play_time < '17:00:00'
    GROUP BY path
    ORDER BY count(*) DESC, play_date DESC, play_time DESC
    LIMIT 10;

    Will give you a top-ten list of played songs in the time frame stated.

    Now I only have to take the shell-script a few posts earlier, modify it so that it will insert into the log-table.
    Then cron it every xx minutes.

    Then… once a day create a few playlists with queries like this, rescan server and Im good to go.

    “I only have to…” πŸ™‚

    #7574

    fizze
    Participant

    well, just use the select frmo the script, and in line 2 in the script use the variables for the log-table.

    be careful with the create statement though. indices, and keys etc.

    the primary key should be path & date/time. You should also use indices for the path and date/time, otherwise its gonna be very slow.

    Id also setup a job that eliminates date from that log DB which is older than, say, a few weeks.

    #7575

    riro
    Participant

    Ok… this is how I did it.

    Start up sqlite against your music-database,

    sqlite3 /opt/var/mt-daapd/songs3.db

    Create the table:

    CREATE TABLE playlistlog (
    id INTEGER NOT NULL,
    time_played INTEGER PRIMARY KEY NOT NULL
    );

    Then populate the table, will take last time played of every song that is played and add it to the playlistlog-table.

    INSERT INTO playlistlog SELECT id, time_played FROM songs where time_played > 0;

    Then, create a view to make it easy to read the log.

    CREATE VIEW formatedlog AS
    SELECT songs.path AS path, time(playlistlog.time_played, 'unixepoch') AS play_time, date(playlistlog.time_played, 'unixepoch') AS play_date
    FROM playlistlog, songs
    WHERE songs.id = playlistlog.id;

    When this is done… exit sqlite3 with “.quit”

    Run this script on a regular basis (Once every hour?). It will update the log-table with recently played songs.

    #!/bin/bash
    SQLITE="sqlite3"
    DATABASE="/opt/var/mt-daapd/songs3.db"
    LASTFILE=/path/to/somewhere/playlist.date

    if [ -e $LASTFILE ]
    then
    . $LASTFILE
    else
    LASTRUN=0
    fi

    $SQLITE $DATABASE 'INSERT INTO playlistlog SELECT id, time_played FROM songs where time_played > '$LASTRUN';'

    echo "LASTRUN="`date +%s` > $LASTFILE

    Then you need this script to create playlists

    #!/bin/bash

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

    echo "# --- Auto generated" >$PLSNAME
    echo "# Songs between "$FROMTIME" and "$TOTIME >>$PLSNAME
    echo "# Generated `date` " >>$PLSNAME
    $SQLITE $DATABASE 'SELECT path FROM formatedlog WHERE play_time BETWEEN "'$FROMTIME'" AND "'$TOTIME'" GROUP BY path ORDER BY count(*) DESC, play_date DESC, play_time DESC LIMIT '$PLSLIMIT';'>>$PLSNAME

    Then… schedule this script to run once a day to regenerate your playlists and start a scan.


    #!/bin/bash

    /path/to/script/above 13:00:00 17:00:00 50 DayTime-Top_50.m3u

    wget -q "http://mt-daapd:[email protected]:3689/config-update.html?action=rescan"
    #7576

    fizze
    Participant

    very nice. πŸ™‚

    one problem though:
    if a song is NOT played since the last update, it is inserted into the log-db with the same time. This wont give an error since the song ID isnt a primary key. (ouch).

    both fields should be primary keys. Also, you maybe dont want to use the ID of firefly since that can change with DB versions and more or less songs in the library. Depending on how ‘static’ youd library is however, this might not be a problem.

    To insert, you have to use a cursor and check the latest playtime, and if its different, insert the new entry.
    otherwise you’d get lots and lots of bogus data from songs that were not played.

    I donno if sqlite is capable of triggers and ‘smart’ indices, but triggering would be a lot more sophisticated & elegant. πŸ˜‰

    #7577

    riro
    Participant

    @fizze wrote:

    if a song is NOT played since the last update, it is inserted into the log-db with the same time. This wont give an error since the song ID isnt a primary key. (ouch).

    Since the unix timestamp is created every time the script runs “echo LASTRUN…” I dont see how this could happen.

    I run the insert-script once every 12 minutes… I use it to audio-scrobble. And I haven’t seen any problems yet.

    #7578

    fizze
    Participant

    ah, got it. this is what you use LASTFILE for.
    nice. πŸ™‚

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic.