Lunetikk's IT Wiki

User Tools

Site Tools

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

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
linux:ubuntu:spotifymusictodatabase [2023/01/18 15:17] – lunetikklinux:ubuntu:spotifymusictodatabase [2023/01/18 16:01] (current) – lunetikk
Line 4: Line 4:
 ===== Database ===== ===== Database =====
  
-{{:linux:ubuntu:pasted:20230118-151359.png?500}}+{{:linux:ubuntu:pasted:20230118-151359.png?800}}
  
 <code mysql spotifymusic.sql> <code mysql spotifymusic.sql>
Line 112: Line 112:
   ADD CONSTRAINT `track_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_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> </code>
linux/ubuntu/spotifymusictodatabase.1674051439.txt.gz · Last modified: 2023/01/18 15:17 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