Differences

This shows you the differences between two versions of the page.

--- linux:ubuntu:alexamusictodatabase [2019/11/24 22:11] – created lunetikk
+++ linux:ubuntu:alexamusictodatabase [2021/12/29 00:15] (current) – lunetikk
@@ Line 1: / Line 1: @@
 ====== Add played music to database  ======
-This script is an updated version of
+This script is an updated version of my script to [[linux:ubuntu:alexamusictitleonwebsite|display playing music title on website]]. It will write all the artists, tracks and albums to a MySQL database.
-{{:linux:ubuntu:pasted:20191005-132910.png}}
+**Update 2021-12-29:** Amazon redesigned the output via alexa.amazon.de, the string for "album" is now always empty, so can only get the state, artist and title at the moment. If you use Spotify you might want to check out my Spotify script. I also thought about a way to get an album via artist and track out of a musicdatabase (like musicbrainz) but I dont know yet if this will work.
-===== Script =====
+===== Database =====
-==== Webcode ====
+{{:linux:ubuntu:pasted:20191124-223313.png?500}}
-The script will create a php file, add this as iframe whereever you want your music to be shown
+<code mysql amazonmusic.sql>
-<code html websnipped.php>
+CREATE TABLE `album` (
-<iframe id="frame1" src="audio.php" width="650" height="100"></iframe>
+  `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;
 </code>
+===== Script =====
 ==== Bashscript ====
-The script will execute the the "Alexa Remote Control" with "-q / query queue" to get the current tracks, then cuts out the playing song with all its info and albumcover to pipe it into the php file.
+<code bash copyTracksinSQL.sh>
-<code bash copyTracks.sh>
 #!/bin/bash
+#Login MySQL
+LOGIN="local"
+#Database MySQL
+DB=amazonmusic
+#Temp- and Webfile
 TMPFILE=/tmp/audio.txt
 WEBFILE=/var/www/mysite/audio.php
+WEBFILE2=/var/www/mysite/audio2.php
-/alexa-remote-control/alexa_remote_control.sh -q > $TMPFILE
+#Execute Alexa Remote Control to get the current data and write into $TMPFILE
+/alexa/alexa-remote-control/alexa_remote_control.sh -q > $TMPFILE
-#OFFLINE=`grep -m1 'message' $TMPFILE | cut -d '"' -f 3 | cut -d ' ' -f 2`
+#Get the necessary data from $TMPFILE
 OFFLINE=`grep -m1 'message' $TMPFILE | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'`
 STATE=`grep -m1 'state' $TMPFILE | cut -d '"' -f 4`
-STATENULL=`grep -m1 'state' /tmp/audio.txt | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'`
+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`
@@ Line 34: / Line 69: @@
 IMG=`grep -m1 'url' $TMPFILE | cut -d '"' -f 4`
-echo "" > $WEBFILE
+#If $WEBFILE exists, make a copy of it ($WEBFILE2) and empty $WEBFILE, else create an empty $WEBFILE
+if [ -f $WEBFILE ]; then
+   cp -a $WEBFILE $WEBFILE2
+   echo "" > $WEBFILE
+else
+   echo "" > $WEBFILE
+fi
-if [ "$OFFLINE" != "null" ] && [ "$STATENULL" != "null" ]; then
+#If $OFFLINE isnt null and $STATECUT isnt null, write all the data to $WEBFILE, else you are not listening to music
-echo "<table style='width:100%'><col style='width:90%'><col style='width:10%'><tr><td>" >> $WEBFILE
+if [ "$OFFLINE" != "null" ] && [ "$STATECUT" != "null" ]; then
-echo "State: $STATE </br>" >> $WEBFILE
+   echo "<table style='width:100%'><col style='width:90%'><col style='width:10%'><tr><td>" >> $WEBFILE
-echo "Artist: $ARTIST </br>" >> $WEBFILE
+   echo "State: $STATE </br>" >> $WEBFILE
-echo "Title: $TITLE </br>" >> $WEBFILE
+   echo "Artist: $ARTIST </br>" >> $WEBFILE
-echo "Album: $ALBUM" >> $WEBFILE
+   echo "Title: $TITLE </br>" >> $WEBFILE
-echo "</td><td>" >> $WEBFILE
+   echo "Album: $ALBUM" >> $WEBFILE
-echo "<img src='$IMG' alt='albumcover' style='width:64px;height:64px;'>" >> $WEBFILE
+   echo "</td><td>" >> $WEBFILE
-echo "</td></tr></table>" >> $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
+   echo "Iam not listening to music right now..." >> $WEBFILE
+fi
+#The following section compares the $WEBFILEs to make sure you dont write data multiple times for the same track
+#Use cmp if it works for you or just use MD5 like I did
+#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...
+#MD5 Hash of both webfiles to compare
+HASHONE=$(md5sum $WEBFILE | cut -d " " -f1)
+HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1)
+#if [ "$HASH" -eq 1 ] #use this if you use cmp, if status is equal to 1 (different), then execute code
+if [ "$HASHONE" != "$HASHTWO" ] #compare md5 hashes, if different, then execute code
+then
+#Look for the current track, artist, album, if not in database, insert, else dont insert
+DBTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT track_id FROM track WHERE track_name = '$TITLE'")
+DBARTIST=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'")
+DBALBUM=$(mysql --login-path=$LOGIN -D $DB -se "SELECT album_id FROM album WHERE album_name = '$ALBUM'")
+if [ -z "$DBTRACK" ]
+then
+        if [ -z "$DBALBUM" ]
+        then
+                if [ -z "$DBARTIST" ]
+                then
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO artist (artist_name)
+VALUES ('$ARTIST');
+EOFMYSQL
+                fi
+mysql --login-path=$LOGIN -D $DB  << 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 --login-path=$LOGIN -D $DB << 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 --login-path=$LOGIN -D $DB << 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
 </code>
@@ Line 55: / Line 147: @@
 <code>
-*/2 * * * * /alexa-remote-control/copyTrack.sh >/dev/null 2>&1
+*/2 * * * * /alexa-remote-control/copyTrackinSQL.sh >/dev/null 2>&1
 </code>

Lunetikk's IT Wiki

User Tools

Site Tools

Differences

Page Tools