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:36] – 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: | ||
===== Database ===== | ===== Database ===== | ||
- | {{: | + | {{: |
<code mysql amazonmusic.sql> | <code mysql amazonmusic.sql> | ||
Line 44: | Line 46: | ||
#!/bin/bash | #!/bin/bash | ||
- | # | + | # |
LOGIN=" | LOGIN=" | ||
- | #Database.Table | + | # |
DB=amazonmusic | DB=amazonmusic | ||
+ | #Temp- and Webfile | ||
TMPFILE=/ | TMPFILE=/ | ||
- | WEBFILE=/ | + | WEBFILE=/ |
- | WEBFILE2=/ | + | WEBFILE2=/ |
+ | #Execute Alexa Remote Control to get the current data and write into $TMPFILE | ||
/ | / | ||
- | #OFFLINE=`grep -m1 ' | + | #Get the necessary data from $TMPFILE |
OFFLINE=`grep -m1 ' | OFFLINE=`grep -m1 ' | ||
STATE=`grep -m1 ' | STATE=`grep -m1 ' | ||
Line 65: | Line 69: | ||
IMG=`grep -m1 ' | IMG=`grep -m1 ' | ||
- | + | #If $WEBFILE exists, make a copy of it ($WEBFILE2) and empty $WEBFILE, else create an empty $WEBFILE | |
- | #echo "" | + | |
if [ -f $WEBFILE ]; then | if [ -f $WEBFILE ]; then | ||
cp -a $WEBFILE $WEBFILE2 | cp -a $WEBFILE $WEBFILE2 | ||
Line 75: | Line 77: | ||
fi | fi | ||
+ | #If $OFFLINE isnt null and $STATECUT isnt null, write all the data to $WEBFILE, else you are not listening to music | ||
if [ " | if [ " | ||
echo "< | echo "< | ||
Line 88: | Line 91: | ||
fi | 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 ' | #If a FILE is ' | ||
#cmp showed 2 (error) all the time, even in cli, feel free to use if it works for you... | #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) | HASHONE=$(md5sum $WEBFILE | cut -d " " -f1) | ||
HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1) | HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1) | ||
- | #if [ " | + | #if [ " |
- | if [ " | + | if [ " |
- | then | + | then |
- | DBTRACK=$(mysql | + | #Look for the current track, artist, album, if not in database, insert, else dont insert |
- | DBARTIST=$(mysql | + | DBTRACK=$(mysql --login-path=$LOGIN |
- | DBALBUM=$(mysql | + | DBARTIST=$(mysql --login-path=$LOGIN |
+ | DBALBUM=$(mysql --login-path=$LOGIN | ||
- | if [ -z "$DBTITLE" ] | + | if [ -z "$DBTRACK" ] |
then | then | ||
if [ -z " | if [ -z " | ||
Line 109: | Line 117: | ||
if [ -z " | if [ -z " | ||
then | then | ||
- | mysql -D $DB --login-path=$LOGIN | + | mysql --login-path=$LOGIN |
INSERT INTO artist (artist_name) | INSERT INTO artist (artist_name) | ||
VALUES (' | VALUES (' | ||
EOFMYSQL | EOFMYSQL | ||
fi | fi | ||
- | mysql -D $DB --login-path=$LOGIN | + | mysql --login-path=$LOGIN |
INSERT INTO album (album_name, | INSERT INTO album (album_name, | ||
VALUES (' | VALUES (' | ||
EOFMYSQL | EOFMYSQL | ||
fi | fi | ||
- | mysql -D $DB --login-path=$LOGIN | + | mysql --login-path=$LOGIN |
INSERT INTO track (track_name, | INSERT INTO track (track_name, | ||
VALUES (' | VALUES (' | ||
Line 125: | Line 133: | ||
fi | fi | ||
- | mysql -D $DB --login-path=$LOGIN | + | mysql --login-path=$LOGIN |
INSERT INTO played (artist_id, | INSERT INTO played (artist_id, | ||
VALUES ((SELECT artist_id FROM artist WHERE artist_name = ' | VALUES ((SELECT artist_id FROM artist WHERE artist_name = ' | ||
EOFMYSQL | EOFMYSQL | ||
+ | |||
fi | fi |