Lunetikk's IT Wiki

User Tools

Site Tools

You are here: start » linux » ubuntu » alexamusictodatabase
Trace:
linux:ubuntu:alexamusictodatabase

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Next revisionBoth sides next revision
linux:ubuntu:alexamusictodatabase [2019/11/24 22:43] – lunetikklinux:ubuntu:alexamusictodatabase [2021/01/07 14:33] – lunetikk
Line 50: Line 50:
 DB=amazonmusic DB=amazonmusic
  
 +#Temp- and Webfile
 TMPFILE=/tmp/audio.txt TMPFILE=/tmp/audio.txt
 WEBFILE=/var/www/mysite/audio.php WEBFILE=/var/www/mysite/audio.php
 WEBFILE2=/var/www/mysite/audio2.php WEBFILE2=/var/www/mysite/audio2.php
  
 +#Execute Alexa Remote Control to get the current data and write into $TMPFILE
 /alexa/alexa-remote-control/alexa_remote_control.sh -q > $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}'` OFFLINE=`grep -m1 'message' $TMPFILE | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'`
 STATE=`grep -m1 'state' $TMPFILE | cut -d '"' -f 4` STATE=`grep -m1 'state' $TMPFILE | cut -d '"' -f 4`
Line 65: Line 67:
 IMG=`grep -m1 'url' $TMPFILE | cut -d '"' -f 4` IMG=`grep -m1 'url' $TMPFILE | cut -d '"' -f 4`
  
- +#If $WEBFILE exists, make a copy of it ($WEBFILE2) and empty $WEBFILE, else create an empty $WEBFILE
-#echo "" > $WEBFILE +
 if [ -f $WEBFILE ]; then if [ -f $WEBFILE ]; then
    cp -a $WEBFILE $WEBFILE2    cp -a $WEBFILE $WEBFILE2
Line 75: Line 75:
 fi fi
  
 +#If $OFFLINE isnt null and $STATECUT isnt null, write all the data to $WEBFILE, else you are not listening to music
 if [ "$OFFLINE" != "null" ] && [ "$STATECUT" != "null" ]; then if [ "$OFFLINE" != "null" ] && [ "$STATECUT" != "null" ]; then
    echo "<table style='width:100%'><col style='width:90%'><col style='width:10%'><tr><td>" >> $WEBFILE    echo "<table style='width:100%'><col style='width:90%'><col style='width:10%'><tr><td>" >> $WEBFILE
Line 88: Line 89:
 fi fi
  
-#HASH="$(cmp --silent $WEBFILE $WEBFILE2; echo $?)"  # "$?" gives exit status for each comparison+#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. #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... #cmp showed 2 (error) all the time, even in cli, feel free to use if it works for you...
Line 100: Line 104:
 then   then  
  
-DBTRACK=$(mysql -D $DB --login-path=$LOGIN -se "SELECT track_id FROM track WHERE track_name = '$TITLE'") +#Look for the current track, artist, album, if not in database, insert, else dont insert 
-DBARTIST=$(mysql -D $DB --login-path=$LOGIN -se "SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'") +DBTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT track_id FROM track WHERE track_name = '$TITLE'") 
-DBALBUM=$(mysql -D $DB --login-path=$LOGIN -se "SELECT album_id FROM album WHERE album_name = '$ALBUM'")+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 "$DBTITLE" ]+if [ -z "$DBTRACK" ]
 then then
         if [ -z "$DBALBUM" ]         if [ -z "$DBALBUM" ]
Line 110: Line 115:
                 if [ -z "$DBARTIST" ]                 if [ -z "$DBARTIST" ]
                 then                 then
-mysql -D $DB --login-path=$LOGIN  << EOFMYSQL+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
 INSERT INTO artist (artist_name) INSERT INTO artist (artist_name)
 VALUES ('$ARTIST'); VALUES ('$ARTIST');
 EOFMYSQL EOFMYSQL
                 fi                 fi
-mysql -D $DB --login-path=$LOGIN  << EOFMYSQL+mysql --login-path=$LOGIN -D $DB  << EOFMYSQL
 INSERT INTO album (album_name,album_cover,artist_id) INSERT INTO album (album_name,album_cover,artist_id)
 VALUES ('$ALBUM','$IMG',(SELECT artist_id FROM artist WHERE artist_name = '$ARTIST')); VALUES ('$ALBUM','$IMG',(SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'));
 EOFMYSQL EOFMYSQL
         fi         fi
-mysql -D $DB --login-path=$LOGIN  << EOFMYSQL+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
 INSERT INTO track (track_name,album_id,artist_id) 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')); VALUES ('$TITLE',(SELECT album_id FROM album WHERE album_name = '$ALBUM'),(SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'));
Line 126: Line 131:
 fi fi
  
-mysql -D $DB --login-path=$LOGIN  << EOFMYSQL+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
 INSERT INTO played (artist_id,track_id,album_id) 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')); 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 EOFMYSQL
 +
  
 fi fi
linux/ubuntu/alexamusictodatabase.txt · Last modified: 2021/12/29 00:15 by lunetikk

Page Tools

Impressum Datenschutz
Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki