First of all thanks a lot to magnate and all the other contributors to this audioscrobbler solution. It seems to work very fine now on my NSLU2 (armeb) running unslung 6.10 with firefly svn-1586 and lastfmsubmitd-0.37 with python 2.4. So here’s my feedback on my working database query script.
It took me some time until I finally tried out another approach than the one suggested here:
@barefoot wrote:
Thanks for the help. Actually, I was successful in working around the problem yesterday, even if I don’t know the exact reason for failure. What I now do is: At the end of the script, I sleep for 5 second and then collect all files in the spool directory (if still existent) to one single file in a separate place and copy it back to the spool directory. This file is recognized reliably (and if not, it is collected next time), all the others get erased. I don’t know why it works, but it does.
So what I basically do is first writing the query result file somewhere outside the spool dir and then move it into the spool. That way lastfmsubmitd processes the file everytime (so far ;).
Additionally I made the script only query the database, if the database file has been changed since the last run. Furthermore a spool file is only written if a database query has been done, instead of always creating 0 byte files when the script runs.
I also had to fix the timezone offset by -1 h ($6-3600 seconds in the gawk statement) in the query result to report GMT+0 timestamps, as otherwise the times would be GMT+1 and thus shown in the future in my last.fm stats.
So here’s my currently running script:
#!/bin/bash
# fetch newly played songs from fireflydb and write
# into lastfmsubmitd readable format
# config
SQLITE=sqlite
DATABASE=/opt/var/mt-daapd/songs.db
LASTFILE=/opt/var/mt-daapd/lastfmsubmit.date
DBLSFILE=/opt/var/mt-daapd/lastfmsubmit.ls
# get last run time
if [ -e $LASTFILE ]
then
. $LASTFILE
else
LASTRUN=0
fi
# get last database file date
if [ -e $DBLSFILE ]
then
. $DBLSFILE
else
DBLSRUN=
fi
# exit when database file unchanged
DBLSNOW=`ls -l $DATABASE`
if [ "$DBLSRUN" == "$DBLSNOW" ]
then
exit
fi
# log file date
echo "DBLSRUN="$DBLSNOW"" > $DBLSFILE
# query database
OUTFILE=$(mktemp /tmp/mt-daapd-XXXXXXXX)
$SQLITE $DATABASE 'SELECT artist,album,title,track,song_length,time_played FROM songs where time_played > '$LASTRUN' ORDER BY time_played ASC;' | gawk -F '|' '{ printf "---nartist: "%s"nalbum: "%s"ntitle: "%s"ntrack: %snlength: %dntime: !timestamp %sn",$1,$2,$3,$4,$5/1000,strftime("%Y-%m-%d %T",$6-3600) }' > $OUTFILE
mv $OUTFILE /var/spool/lastfm
# log query date
echo "LASTRUN="`date +%s` > $LASTFILE
# make lastfmsubmitd files readable
chmod 664 /var/spool/lastfm/*
Be sure to set the paths, the sqlite executable and time offset to your needs. This script needs bash and gawk available via ipkg.
I haven’t tried yet to change the shebang line from bash to sh. Might be better to do so.