Inteligent playlists

[riro]

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.

[rpedde]

@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

[riro]

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

[fizze]

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” 😀

[riro]

@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

[riro]

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.

[fizze]

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

[riro]

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…” 🙂

[fizze]

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.

[riro]

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"

[Author]

Posts