This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
linux:ubuntu:alexamusictodatabase [2019/11/24 22:12] – lunetikk | linux:ubuntu:alexamusictodatabase [2021/12/29 00:15] (current) – lunetikk | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Add played music to database | ====== Add played music to database | ||
- | This script is an updated version of my script to [[linux: | + | This script is an updated version of my script to [[linux: |
- | {{: | + | **Update 2021-12-29:** Amazon redesigned the output via alexa.amazon.de, |
- | ===== Script | + | ===== Database |
- | ==== Webcode ==== | + | {{: |
- | The script will create a php file, add this as iframe whereever you want your music to be shown | + | < |
- | < | + | CREATE TABLE `album` ( |
- | <iframe id=" | + | `artist_id` smallint(5) NOT NULL DEFAULT ' |
+ | `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 ' | ||
+ | `album_id` smallint(5) NOT NULL DEFAULT ' | ||
+ | `track_id` smallint(5) NOT NULL DEFAULT ' | ||
+ | `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 ' | ||
+ | `album_id` smallint(5) NOT NULL DEFAULT ' | ||
+ | PRIMARY KEY (track_id) | ||
+ | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | ||
</ | </ | ||
+ | |||
+ | ===== Script ===== | ||
==== Bashscript ==== | ==== Bashscript ==== | ||
- | The script will execute the the "Alexa Remote Control" | + | <code bash copyTracksinSQL.sh> |
- | + | ||
- | <code bash copyTracks.sh> | + | |
#!/bin/bash | #!/bin/bash | ||
+ | #Login MySQL | ||
+ | LOGIN=" | ||
+ | |||
+ | #Database MySQL | ||
+ | DB=amazonmusic | ||
+ | |||
+ | #Temp- and Webfile | ||
TMPFILE=/ | TMPFILE=/ | ||
WEBFILE=/ | WEBFILE=/ | ||
+ | WEBFILE2=/ | ||
- | / | + | #Execute Alexa Remote Control to get the current data and write into $TMPFILE |
+ | /alexa/ | ||
- | #OFFLINE=`grep -m1 ' | + | #Get the necessary data from $TMPFILE |
OFFLINE=`grep -m1 ' | OFFLINE=`grep -m1 ' | ||
STATE=`grep -m1 ' | STATE=`grep -m1 ' | ||
- | STATENULL=`grep -m1 ' | + | STATECUT=`grep -m1 ' |
ARTIST=`grep -m1 ' | ARTIST=`grep -m1 ' | ||
TITLE=`grep -m1 ' | TITLE=`grep -m1 ' | ||
Line 34: | Line 69: | ||
IMG=`grep -m1 ' | IMG=`grep -m1 ' | ||
- | echo "" | + | #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 "" | ||
+ | else | ||
+ | echo "" | ||
+ | fi | ||
- | if [ " | + | #If $OFFLINE isnt null and $STATECUT isnt null, write all the data to $WEBFILE, else you are not listening to music |
- | echo "< | + | if [ " |
- | echo " | + | |
- | echo " | + | |
- | echo " | + | |
- | echo " | + | |
- | echo "</ | + | |
- | echo "< | + | |
- | echo "</ | + | |
+ | | ||
else | else | ||
- | echo "Iam not listening to music right now..." | + | echo "Iam not listening to music right now..." |
+ | 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 | ||
+ | |||
+ | # | ||
+ | #If a FILE is ' | ||
+ | #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 [ " | ||
+ | if [ " | ||
+ | then | ||
+ | |||
+ | #Look for the current track, artist, album, if not in database, insert, else dont insert | ||
+ | DBTRACK=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | DBARTIST=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | DBALBUM=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | |||
+ | if [ -z " | ||
+ | then | ||
+ | if [ -z " | ||
+ | then | ||
+ | if [ -z " | ||
+ | then | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO artist (artist_name) | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO album (album_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO track (track_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | |||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO played (artist_id, | ||
+ | VALUES ((SELECT artist_id FROM artist WHERE artist_name = ' | ||
+ | EOFMYSQL | ||
+ | |||
fi | fi | ||
</ | </ | ||
Line 55: | Line 147: | ||
< | < | ||
- | */2 * * * * / | + | */2 * * * * / |
</ | </ | ||