Inteligent playlists

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.

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

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"