Reply To: Inteligent playlists

[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"