This is an old revision of the document!
This script is an updated version of my script to display playing music title on website. It will write all the artists, tracks and albums to a MySQL database.
CREATE TABLE `album` ( `artist_id` smallint(5) NOT NULL DEFAULT '0', `album_id` smallint(5) NOT NULL AUTO_INCREMENT, `album_name` char(128) DEFAULT NULL, `album_cover` varchar(1000) DEFAULT NULL, PRIMARY KEY (album_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `artist` ( `artist_id` smallint(5) NOT NULL AUTO_INCREMENT, `artist_name` char(128) DEFAULT NULL, PRIMARY KEY (artist_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `played` ( `artist_id` smallint(5) NOT NULL DEFAULT '0', `album_id` smallint(5) NOT NULL DEFAULT '0', `track_id` smallint(5) NOT NULL DEFAULT '0', `played` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `track` ( `track_id` smallint(5) NOT NULL AUTO_INCREMENT, `track_name` char(255) DEFAULT NULL, `artist_id` smallint(5) NOT NULL DEFAULT '0', `album_id` smallint(5) NOT NULL DEFAULT '0', PRIMARY KEY (track_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#!/bin/bash #Login for MYSQL LOGIN="local" #Database.Table DB=amazonmusic TMPFILE=/tmp/audio.txt WEBFILE=/var/www/lunetikkde/audio.php WEBFILE2=/var/www/lunetikkde/audio2.php /alexa/alexa-remote-control/alexa_remote_control.sh -q > $TMPFILE #OFFLINE=`grep -m1 'message' $TMPFILE | cut -d '"' -f 3 | cut -d ' ' -f 2` OFFLINE=`grep -m1 'message' $TMPFILE | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'` STATE=`grep -m1 'state' $TMPFILE | cut -d '"' -f 4` STATECUT=`grep -m1 'state' /tmp/audio.txt | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'` ARTIST=`grep -m1 'subText1' $TMPFILE | cut -d '"' -f 4` TITLE=`grep -m1 'title' $TMPFILE | cut -d '"' -f 4` ALBUM=`grep -m1 'subText2' $TMPFILE | cut -d '"' -f 4` IMG=`grep -m1 'url' $TMPFILE | cut -d '"' -f 4` #echo "" > $WEBFILE if [ -f $WEBFILE ]; then cp -a $WEBFILE $WEBFILE2 echo "" > $WEBFILE else echo "" > $WEBFILE fi if [ "$OFFLINE" != "null" ] && [ "$STATECUT" != "null" ]; then echo "<table style='width:100%'><col style='width:90%'><col style='width:10%'><tr><td>" >> $WEBFILE echo "State: $STATE </br>" >> $WEBFILE echo "Artist: $ARTIST </br>" >> $WEBFILE echo "Title: $TITLE </br>" >> $WEBFILE echo "Album: $ALBUM" >> $WEBFILE echo "</td><td>" >> $WEBFILE echo "<img src='$IMG' alt='albumcover' style='width:64px;height:64px;'>" >> $WEBFILE echo "</td></tr></table>" >> $WEBFILE else echo "Iam not listening to music right now..." >> $WEBFILE fi #HASH="$(cmp --silent $WEBFILE $WEBFILE2; echo $?)" # "$?" gives exit status for each comparison #If a FILE is '-' or missing, read standard input. Exit status is 0 if inputs are the same, 1 if different, 2 if trouble. #cmp showed 2 (error) all the time, even in cli, feel free to use if it works for you... HASHONE=$(md5sum $WEBFILE | cut -d " " -f1) HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1) #if [ "$HASH" -eq 1 ] #use this if you use cmp if [ "$HASHONE" != "$HASHTWO" ] then # if status is equal to 1, then execute code DBTRACK=$(mysql -D $DB --login-path=$LOGIN -se "SELECT track_id FROM track WHERE track_name = '$TITLE'") DBARTIST=$(mysql -D $DB --login-path=$LOGIN -se "SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'") DBALBUM=$(mysql -D $DB --login-path=$LOGIN -se "SELECT album_id FROM album WHERE album_name = '$ALBUM'") if [ -z "$DBTITLE" ] then if [ -z "$DBALBUM" ] then if [ -z "$DBARTIST" ] then mysql -D $DB --login-path=$LOGIN << EOFMYSQL INSERT INTO artist (artist_name) VALUES ('$ARTIST'); EOFMYSQL fi mysql -D $DB --login-path=$LOGIN << EOFMYSQL INSERT INTO album (album_name,album_cover,artist_id) VALUES ('$ALBUM','$IMG',(SELECT artist_id FROM artist WHERE artist_name = '$ARTIST')); EOFMYSQL fi mysql -D $DB --login-path=$LOGIN << EOFMYSQL INSERT INTO track (track_name,album_id,artist_id) VALUES ('$TITLE',(SELECT album_id FROM album WHERE album_name = '$ALBUM'),(SELECT artist_id FROM artist WHERE artist_name = '$ARTIST')); EOFMYSQL fi mysql -D $DB --login-path=$LOGIN << EOFMYSQL INSERT INTO played (artist_id,track_id,album_id) VALUES ((SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'),(SELECT track_id FROM track WHERE track_name = '$TITLE'),(SELECT album_id FROM album WHERE album_name = '$ALBUM')); EOFMYSQL fi
The bashscript can be automated via crontab (example runs every 2 minutes).
*/2 * * * * /alexa-remote-control/copyTrackinSQL.sh >/dev/null 2>&1