Differences

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

--- linux:ubuntu:spotifymusictodatabase [2021/12/29 00:49] – lunetikk
+++ linux:ubuntu:spotifymusictodatabase [2023/01/18 16:01] (current) – lunetikk
@@ Line 4: / Line 4: @@
 ===== Database =====
-{{:linux:ubuntu:pasted:20211229-004224.png?500}}
+{{:linux:ubuntu:pasted:20230118-151359.png?800}}
 <code mysql spotifymusic.sql>
@@ Line 10: / Line 10: @@
 CREATE TABLE `album` (
   `artist_id` smallint(5) NOT NULL DEFAULT '0',
-  `album_id` smallint(5) NOT NULL AUTO_INCREMENT,
+  `album_id` smallint(5) NOT NULL,
   `album_name` char(128) DEFAULT NULL,
   `album_cover` varchar(1000) DEFAULT NULL,
@@ Line 16: / Line 16: @@
   `album_releasedate` date DEFAULT NULL,
   `album_tracknumber` smallint(5) DEFAULT NULL,
-  `album_spotifyurl` varchar(128) DEFAULT NULL,
+  `album_spotifyurl` varchar(128) DEFAULT NULL
-  PRIMARY KEY (album_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 CREATE TABLE `artist` (
-  `artist_id` smallint(5) NOT NULL AUTO_INCREMENT,
+  `artist_id` smallint(5) NOT NULL,
   `artist_name` char(128) DEFAULT NULL,
-  `artist_spotifyurl` varchar(128) DEFAULT NULL,
+  `artist_spotifyurl` varchar(128) DEFAULT NULL
-  PRIMARY KEY (artist_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE `connectalbumtrack` (
+  `album_id` smallint(10) NOT NULL,
+  `track_id` smallint(10) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE `connectartisttrack` (
+  `artist_id` smallint(10) NOT NULL,
+  `track_id` smallint(10) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ Line 35: / Line 43: @@
 CREATE TABLE `track` (
-  `track_id` smallint(5) NOT NULL AUTO_INCREMENT,
   `artist_id` smallint(5) NOT NULL DEFAULT '0',
   `album_id` smallint(5) NOT NULL DEFAULT '0',
+  `track_id` smallint(5) NOT NULL,
   `track_name` char(255) DEFAULT NULL,
   `track_popularity` int(20) DEFAULT NULL,
   `track_tracknumber` smallint(5) DEFAULT NULL,
   `track_previewurl` varchar(128) DEFAULT NULL,
-  `track_spotifyurl` varchar(128) DEFAULT NULL,
+  `track_spotifyurl` varchar(128) DEFAULT NULL
-  PRIMARY KEY (track_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ALTER TABLE `album`
+  ADD PRIMARY KEY (`album_id`),
+  ADD KEY `album_id` (`album_id`),
+  ADD KEY `artist_id` (`artist_id`);
+ALTER TABLE `artist`
+  ADD PRIMARY KEY (`artist_id`),
+  ADD UNIQUE KEY `artist_id_2` (`artist_id`),
+  ADD KEY `artist_id` (`artist_id`),
+  ADD KEY `artist_id_3` (`artist_id`);
+ALTER TABLE `connectalbumtrack`
+  ADD KEY `album_id` (`album_id`),
+  ADD KEY `track_id` (`track_id`);
+ALTER TABLE `connectartisttrack`
+  ADD KEY `artist_id` (`artist_id`),
+  ADD KEY `track_id` (`track_id`);
+ALTER TABLE `played`
+  ADD KEY `artist_id` (`artist_id`),
+  ADD KEY `album_id` (`album_id`),
+  ADD KEY `track_id` (`track_id`);
+ALTER TABLE `track`
+  ADD PRIMARY KEY (`track_id`),
+  ADD KEY `track_id` (`track_id`),
+  ADD KEY `artist_id` (`artist_id`),
+  ADD KEY `album_id` (`album_id`);
+ALTER TABLE `album`
+  MODIFY `album_id` smallint(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;
+ALTER TABLE `artist`
+  MODIFY `artist_id` smallint(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=71;
+ALTER TABLE `track`
+  MODIFY `track_id` smallint(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=69;
+ALTER TABLE `album`
+  ADD CONSTRAINT `album_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`);
+ALTER TABLE `connectalbumtrack`
+  ADD CONSTRAINT `connectalbumtrack_ibfk_1` FOREIGN KEY (`track_id`) REFERENCES `track` (`track_id`),
+  ADD CONSTRAINT `connectalbumtrack_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`);
+ALTER TABLE `connectartisttrack`
+  ADD CONSTRAINT `connectartisttrack_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`),
+  ADD CONSTRAINT `connectartisttrack_ibfk_2` FOREIGN KEY (`track_id`) REFERENCES `track` (`track_id`);
+ALTER TABLE `played`
+  ADD CONSTRAINT `played_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`),
+  ADD CONSTRAINT `played_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`),
+  ADD CONSTRAINT `played_ibfk_3` FOREIGN KEY (`track_id`) REFERENCES `track` (`track_id`);
+ALTER TABLE `track`
+  ADD CONSTRAINT `track_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`),
+  ADD CONSTRAINT `track_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`);
+</code>
+===== Script =====
+==== Bashscript ====
+<code bash copySpotifyinSQL.sh>
+#!/bin/bash
+#----------VARS----------#
+#Path to logfile
+LOGPATH=/var/log/mysqlinsert_spotify.log
+#Login for MYSQL
+LOGIN="local"
+#Database
+DB=spotifymusic
+TMPFILE=/tmp/spotifyaudio.json
+WEBFILE=/var/www/mysite/spotifyaudio.php
+WEBFILE2=/var/www/mysite/spotifyaudio2.php
+ACCESSTOKENFILE="/spotify/token.json"
+ACCESSTOKEN=`jq -r ".access_token" $ACCESSTOKENFILE`
+BASICAUTH="yourAUTHkey"
+REFRESH_TOKEN="yourREFRESHtoken"
+#----------GET_CURRENT_TRACK_INFO----------#
+GET_CURRENT_TRACK_INFO () {
+OFFLINE=`cat $TMPFILE | jq -r '.is_playing'`
+#define array length
+ARTISTNUMBER=`cat $TMPFILE | jq '.item.artists | length'`
+#artists to array
+if [ -z "$ARTISTNUMBER" ]
+then
+      ARTISTNUMBER="0"
+fi
+if [ "$ARTISTNUMBER" -gt "1" ];then
+        declare -ga ARTISTARRAY=()
+        for ((i=1; i<=$ARTISTNUMBER; i+=1)); do
+                j=$(($i-1))
+                ARTISTS=`cat $TMPFILE | jq -r ".item.artists[$j].name"`
+                ARTISTARRAY+=("$ARTISTS")
+        done
+        echo ${ARTISTARRAY[0]}
+        echo ${ARTISTARRAY[1]}
+        printf -v ARTISTARRAYOUTPUT '%s,' "${ARTISTARRAY[@]}"; echo "${ARTISTARRAYOUTPUT%,}" | sed 's/, */, /g' > /tmp/artists.txt
+else
+        ARTIST=`cat $TMPFILE | jq -r '.item.artists[0].name'`
+fi
+#spotifyinfo to vars
+export ARTISTSPOTIFYURL=`cat $TMPFILE | jq -r '.item.artists[0].external_urls.spotify'`
+export TITLE=`cat $TMPFILE | jq -r '.item.name'`
+export TITLENUMBER=`cat $TMPFILE | jq -r '.item.track_number'`
+export TITLEPOPULARITY=`cat $TMPFILE | jq -r '.item.popularity'`
+export TITLESPOTIFYURL=`cat $TMPFILE | jq -r '.item.external_urls.spotify'`
+export TITLESPOTIFYPREVIEWURL=`cat $TMPFILE | jq -r '.item.preview_url'`
+export ALBUM=`cat $TMPFILE | jq -r '.item.album.name'`
+export ALBUMTYPE=`cat $TMPFILE | jq -r '.item.album.album_type'`
+export ALBUMTRACKS=`cat $TMPFILE | jq -r '.item.album.total_tracks'`
+export ALBUMSPOTIFYURL=`cat $TMPFILE | jq -r '.item.album.external_urls.spotify'`
+export ALBUMRELEASEDATE=`cat $TMPFILE | jq -r '.item.album.release_date'`
+export ALBUMIMG=`cat $TMPFILE | jq -r '.item.album.images[0].url'`
+}
+#----------GET_SPOTIFY_TRACK----------#
+GET_SPOTIFY_TRACK () {
+  curl -X "GET" "https://api.spotify.com/v1/me/player/currently-playing" -H "Accept: application/json" -H "Content-Type: application/json" -H "Authorization: Bearer "$ACCESSTOKEN"" > $TMPFILE
+}
+#----------CHECK_TOKEN----------#
+CHECK_TOKEN () {
+  grep -i '"status": 401,\|"status": 400,' $TMPFILE && return 0 || return 1
+}
+#----------REFRESH_TOKEN----------#
+REFRESH_TOKEN () {
+  curl -H "Authorization: Basic $BASICAUTH" -d grant_type="refresh_token" -d refresh_token="$REFRESH_TOKEN" -d redirect_uri="http%3A%2F%2Flocalhost:4815%2Fcallback%2F" https://accounts.spotify.com/api/token > $ACCESSTOKENFILE
+  ACCESSTOKEN=`jq -r ".access_token" $ACCESSTOKENFILE`
+}
+#----------WRITE_TO_WEBSITE----------#
+WRITE_TO_WEBSITE () {
+   echo "" > $WEBFILE
+#sometimes $ARTISTS was empty, this is the workaround, might be fixed in the meantime
+WHYUNOWORKIN=$(cat /tmp/artists.txt)
+if [ -z "$OFFLINE" ]
+then
+      OFFLINE="false"
+fi
+if [ "$OFFLINE" != "false" ]; then
+   echo "<link rel='stylesheet' href='./styles.css' type='text/css' media='screen' />" >> $WEBFILE
+   echo "<div id='iframe'>" >> $WEBFILE
+   echo "<table style='width:100%'><col style='width:12%'><col style='width:73%'><col style='width:15%'>" >> $WEBFILE
+   echo "<tr>" >> $WEBFILE
+   echo "<td>State: </td>" >> $WEBFILE
+   echo "<td>PLAYING</td>" >> $WEBFILE
+   echo "<td rowspan='4'><img src='"$ALBUMIMG"' alt='albumcover' style='width:64px;height:64px;'></td>" >> $WEBFILE
+   echo "</tr>" >> $WEBFILE
+   echo "<tr>" >> $WEBFILE
+   echo "<td>Artist: </td>" >> $WEBFILE
+   if [ -z "$ARTIST" ]; then
+       echo "<td>"$WHYUNOWORKIN"</td>" >> $WEBFILE
+   else
+       echo "<td>"$ARTIST"</td>" >> $WEBFILE
+   fi
+   echo "<td></td>" >> $WEBFILE
+   echo "</tr>" >> $WEBFILE
+   echo "<tr>" >> $WEBFILE
+   echo "<td>Title: </td>" >> $WEBFILE
+   echo "<td>"$TITLE"</td>" >> $WEBFILE
+   echo "</tr>" >> $WEBFILE
+   echo "<tr>" >> $WEBFILE
+   echo "<td>Album: </td>" >> $WEBFILE
+   echo "<td>"$ALBUM"</td>" >> $WEBFILE
+   echo "</tr>" >> $WEBFILE
+   echo "</table>" >> $WEBFILE
+   echo "</div>" >> $WEBFILE
+else
+   echo "<link rel='stylesheet' href='./styles.css' type='text/css' media='screen' />" >> $WEBFILE
+   echo "<div id='iframe'>" >> $WEBFILE
+   echo "Iam not listening to music right now..." >> $WEBFILE
+   echo "</div>" >> $WEBFILE
+fi
+}
+#----------WRITE_TO_MYSQL----------#
+WRITE_TO_MYSQL () {
+#wanted to use the comparison below but it didnt work. maybe someone knows how to fix it?
+#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.
+HASHONE=$(md5sum $WEBFILE | cut -d " " -f1)
+HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1)
+if [ "$HASHONE" != "$HASHTWO" ]
+then  # if status is equal to 1, then execute code
+DBTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT track_id FROM track WHERE track_name = '$TITLE'")
+DBALBUM=$(mysql --login-path=$LOGIN -D $DB -se "SELECT album_id FROM album WHERE album_name = '$ALBUM'")
+if [ "$ARTISTNUMBER" -gt "1" ];then
+        for ((i = 0; i < ${#ARTISTARRAY[*]}; i++))
+        do
+            DBARTIST=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '${ARTISTARRAY[$i]}'")
+                if [ -z "$DBARTIST" ]
+                then
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO artist (artist_name,artist_spotifyurl)
+VALUES ('${ARTISTARRAY[$i]}','$ARTISTSPOTIFYURL');
+EOFMYSQL
+                fi
+        done
+else
+        echo "artist"
+        echo $ARTIST
+        DBARTIST=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'")
+        if [ -z "$DBARTIST" ]
+        then
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO artist (artist_name,artist_spotifyurl)
+VALUES ('$ARTIST','$ARTISTSPOTIFYURL');
+EOFMYSQL
+        fi
+fi
+if [ "$ARTISTNUMBER" -gt "1" ];then
+        DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '${ARTISTARRAY[0]}'")
+else
+        DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'")
+fi
+if [ -z "$DBALBUM" ]
+then
+        if [ "$ARTISTNUMBER" -gt "1" ];then
+mysql --login-path=$LOGIN -D $DB  << EOFMYSQL
+INSERT INTO album (album_name,album_cover,artist_id,album_type,album_releasedate,album_tracknumber,album_spotifyurl)
+VALUES ('$ALBUM','$ALBUMIMG','$DBARTISTNEW','$ALBUMTYPE','$ALBUMRELEASEDATE','$ALBUMTRACKS','$ALBUMSPOTIFYURL');
+EOFMYSQL
+        else
+mysql --login-path=$LOGIN -D $DB  << EOFMYSQL
+INSERT INTO album (album_name,album_cover,artist_id,album_type,album_releasedate,album_tracknumber,album_spotifyurl)
+VALUES ('$ALBUM','$ALBUMIMG','$DBARTISTNEW','$ALBUMTYPE','$ALBUMRELEASEDATE','$ALBUMTRACKS','$ALBUMSPOTIFYURL');
+EOFMYSQL
+        fi
+fi
+DBALBUMNEW=$(mysql --login-path=$LOGIN -D $DB -se "SELECT album_id FROM album WHERE album_name = '$ALBUM'")
+if [ -z "$DBTRACK" ]
+then
+        if [ "$ARTISTNUMBER" -gt "1" ];then
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO track (track_name,album_id,artist_id,track_tracknumber,track_popularity,track_previewurl,track_spotifyurl)
+VALUES ('$TITLE','$DBALBUMNEW','$DBARTISTNEW','$TITLENUMBER','$TITLEPOPULARITY','$TITLESPOTIFYPREVIEWURL','$TITLESPOTIFYURL');
+EOFMYSQL
+        else
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO track (track_name,album_id,artist_id,track_tracknumber,track_popularity,track_previewurl,track_spotifyurl)
+VALUES ('$TITLE',$DBALBUMNEW,'$DBARTISTNEW','$TITLENUMBER','$TITLEPOPULARITY','$TITLESPOTIFYPREVIEWURL','$TITLESPOTIFYURL');
+EOFMYSQL
+        fi
+fi
+DBTRACKNEW=$(mysql --login-path=$LOGIN -D $DB -se "SELECT track_id FROM track WHERE track_name = '$TITLE'")
+DBALBUMTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT a.album_name, c.track_name FROM album a, connectalbumtrack b, track c WHERE b.album_id = '$DBALBUMNEW' AND b.track_id = '$DBTRACKNEW'")
+#connectartisttrack
+if [ "$ARTISTNUMBER" -gt "1" ]
+then
+        for ((i = 0; i < ${#ARTISTARRAY[*]}; i++))
+        do
+                DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '${ARTISTARRAY[$i]}'")
+                DBARTISTTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT a.artist_name, c.track_name FROM artist a, connectartisttrack b, track c WHERE b.artist_id = '$DBARTISTNEW' AND b.track_id = '$DBTRACKNEW'")
+                if [ -z "$DBARTISTTRACK" ]
+                then
+mysql --login-path=$LOGIN -D $DB  << EOFMYSQL
+INSERT INTO connectartisttrack (artist_id,track_id)
+VALUES ('$DBARTISTNEW','$DBTRACKNEW');
+EOFMYSQL
+                fi
+        done
+else
+        DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se "SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'")
+        DBARTISTTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT a.artist_name, c.track_name FROM artist a, connectartisttrack b, track c WHERE b.artist_id = '$DBARTISTNEW' AND b.track_id = '$DBTRACKNEW'")
+        if [ -z "$DBARTISTTRACK" ]
+        then
+mysql --login-path=$LOGIN -D $DB  << EOFMYSQL
+INSERT INTO connectartisttrack (artist_id,track_id)
+VALUES ('$DBARTISTNEW','$DBTRACKNEW');
+EOFMYSQL
+        fi
+fi
+#connectalbumtrack
+if [ -z "$DBALBUMTRACK" ]
+then
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO connectalbumtrack (album_id,track_id)
+VALUES ('$DBALBUMNEW','$DBTRACKNEW');
+EOFMYSQL
+fi
+#played is not yet fixed for multiple artists and just uses the first one
+if [ "$ARTISTNUMBER" -gt "1" ];then
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO played (artist_id,track_id,album_id)
+VALUES ('$DBARTISTNEW','$DBTRACKNEW','$DBALBUMNEW');
+EOFMYSQL
+else
+mysql --login-path=$LOGIN -D $DB << EOFMYSQL
+INSERT INTO played (artist_id,track_id,album_id)
+VALUES ('$DBARTISTNEW','$DBTRACKNEW','$DBALBUMNEW');
+EOFMYSQL
+fi
+#copy webfile to webfile2 for comparison in the next run
+cp -a $WEBFILE $WEBFILE2
+fi
+}
+#----------MAIN----------#
+GET_SPOTIFY_TRACK
+CHECK_TOKEN
+LASTSTATUS=`echo $?`
+if [ "$LASTSTATUS" -eq 0 ];then
+        REFRESH_TOKEN
+        GET_SPOTIFY_TRACK
+fi
+GET_CURRENT_TRACK_INFO
+WRITE_TO_WEBSITE
+WRITE_TO_MYSQL
+</code>
+==== Automation ====
+The bashscript can be automated via crontab (example runs every 2 minutes).
 <code>
+*/2 * * * * /spotify/copySpotifyinSQL.sh >/dev/null 2>&1
+</code>

Lunetikk's IT Wiki

User Tools

Site Tools

Differences

Page Tools