This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
linux:ubuntu:spotifymusictodatabase [2021/12/29 00:42] – created lunetikk | linux:ubuntu:spotifymusictodatabase [2023/01/18 16:01] (current) – lunetikk | ||
---|---|---|---|
Line 4: | Line 4: | ||
===== Database ===== | ===== Database ===== | ||
- | {{: | + | {{: |
+ | |||
+ | <code mysql spotifymusic.sql> | ||
+ | |||
+ | CREATE TABLE `album` ( | ||
+ | `artist_id` smallint(5) NOT NULL DEFAULT ' | ||
+ | `album_id` smallint(5) NOT NULL, | ||
+ | `album_name` char(128) DEFAULT NULL, | ||
+ | `album_cover` varchar(1000) DEFAULT NULL, | ||
+ | `album_type` varchar(128) DEFAULT NULL, | ||
+ | `album_releasedate` date DEFAULT NULL, | ||
+ | `album_tracknumber` smallint(5) DEFAULT NULL, | ||
+ | `album_spotifyurl` varchar(128) DEFAULT NULL | ||
+ | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | ||
+ | |||
+ | CREATE TABLE `artist` ( | ||
+ | `artist_id` smallint(5) NOT NULL, | ||
+ | `artist_name` char(128) DEFAULT NULL, | ||
+ | `artist_spotifyurl` varchar(128) DEFAULT NULL | ||
+ | ) 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; | ||
+ | |||
+ | 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` ( | ||
+ | `artist_id` smallint(5) NOT NULL DEFAULT ' | ||
+ | `album_id` smallint(5) NOT NULL DEFAULT ' | ||
+ | `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 | ||
+ | ) 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, | ||
+ | |||
+ | ALTER TABLE `artist` | ||
+ | MODIFY `artist_id` smallint(5) NOT NULL AUTO_INCREMENT, | ||
+ | |||
+ | ALTER TABLE `track` | ||
+ | MODIFY `track_id` smallint(5) NOT NULL AUTO_INCREMENT, | ||
+ | |||
+ | 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`); | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== Script ===== | ||
+ | |||
+ | ==== Bashscript ==== | ||
+ | |||
+ | <code bash copySpotifyinSQL.sh> | ||
+ | # | ||
+ | |||
+ | # | ||
+ | |||
+ | #Path to logfile | ||
+ | LOGPATH=/ | ||
+ | |||
+ | #Login for MYSQL | ||
+ | LOGIN=" | ||
+ | |||
+ | #Database | ||
+ | DB=spotifymusic | ||
+ | |||
+ | TMPFILE=/ | ||
+ | WEBFILE=/ | ||
+ | WEBFILE2=/ | ||
+ | |||
+ | ACCESSTOKENFILE="/ | ||
+ | ACCESSTOKEN=`jq -r " | ||
+ | BASICAUTH=" | ||
+ | REFRESH_TOKEN=" | ||
+ | |||
+ | # | ||
+ | |||
+ | GET_CURRENT_TRACK_INFO () { | ||
+ | |||
+ | OFFLINE=`cat $TMPFILE | jq -r ' | ||
+ | |||
+ | #define array length | ||
+ | ARTISTNUMBER=`cat $TMPFILE | jq ' | ||
+ | |||
+ | #artists to array | ||
+ | if [ -z " | ||
+ | then | ||
+ | ARTISTNUMBER=" | ||
+ | fi | ||
+ | |||
+ | if [ " | ||
+ | declare -ga ARTISTARRAY=() | ||
+ | for ((i=1; i< | ||
+ | j=$(($i-1)) | ||
+ | ARTISTS=`cat $TMPFILE | jq -r " | ||
+ | ARTISTARRAY+=(" | ||
+ | done | ||
+ | echo ${ARTISTARRAY[0]} | ||
+ | echo ${ARTISTARRAY[1]} | ||
+ | printf -v ARTISTARRAYOUTPUT ' | ||
+ | else | ||
+ | ARTIST=`cat $TMPFILE | jq -r ' | ||
+ | fi | ||
+ | |||
+ | # | ||
+ | export ARTISTSPOTIFYURL=`cat $TMPFILE | jq -r ' | ||
+ | |||
+ | export TITLE=`cat $TMPFILE | jq -r ' | ||
+ | export TITLENUMBER=`cat $TMPFILE | jq -r ' | ||
+ | export TITLEPOPULARITY=`cat $TMPFILE | jq -r ' | ||
+ | export TITLESPOTIFYURL=`cat $TMPFILE | jq -r ' | ||
+ | export TITLESPOTIFYPREVIEWURL=`cat $TMPFILE | jq -r ' | ||
+ | |||
+ | export ALBUM=`cat $TMPFILE | jq -r ' | ||
+ | export ALBUMTYPE=`cat $TMPFILE | jq -r ' | ||
+ | export ALBUMTRACKS=`cat $TMPFILE | jq -r ' | ||
+ | export ALBUMSPOTIFYURL=`cat $TMPFILE | jq -r ' | ||
+ | export ALBUMRELEASEDATE=`cat $TMPFILE | jq -r ' | ||
+ | export ALBUMIMG=`cat $TMPFILE | jq -r ' | ||
+ | |||
+ | } | ||
+ | |||
+ | # | ||
+ | |||
+ | GET_SPOTIFY_TRACK () { | ||
+ | curl -X " | ||
+ | } | ||
+ | |||
+ | # | ||
+ | |||
+ | CHECK_TOKEN () { | ||
+ | grep -i '" | ||
+ | } | ||
+ | |||
+ | # | ||
+ | |||
+ | REFRESH_TOKEN () { | ||
+ | curl -H " | ||
+ | ACCESSTOKEN=`jq -r " | ||
+ | } | ||
+ | |||
+ | # | ||
+ | |||
+ | WRITE_TO_WEBSITE () { | ||
+ | |||
+ | echo "" | ||
+ | |||
+ | #sometimes $ARTISTS was empty, this is the workaround, might be fixed in the meantime | ||
+ | WHYUNOWORKIN=$(cat / | ||
+ | |||
+ | if [ -z " | ||
+ | then | ||
+ | OFFLINE=" | ||
+ | fi | ||
+ | |||
+ | if [ " | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "</ | ||
+ | echo "< | ||
+ | echo "< | ||
+ | if [ -z " | ||
+ | echo "< | ||
+ | | ||
+ | echo "< | ||
+ | fi | ||
+ | echo "< | ||
+ | echo "</ | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "</ | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "</ | ||
+ | echo "</ | ||
+ | echo "</ | ||
+ | else | ||
+ | echo "< | ||
+ | echo "< | ||
+ | echo "Iam not listening to music right now..." | ||
+ | echo "</ | ||
+ | fi | ||
+ | } | ||
+ | |||
+ | # | ||
+ | |||
+ | WRITE_TO_MYSQL () { | ||
+ | |||
+ | #wanted to use the comparison below but it didnt work. maybe someone knows how to fix it? | ||
+ | # | ||
+ | #If a FILE is ' | ||
+ | |||
+ | HASHONE=$(md5sum $WEBFILE | cut -d " " -f1) | ||
+ | HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1) | ||
+ | |||
+ | if [ " | ||
+ | then # if status is equal to 1, then execute code | ||
+ | |||
+ | DBTRACK=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | DBALBUM=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | |||
+ | if [ " | ||
+ | for ((i = 0; i < ${# | ||
+ | do | ||
+ | DBARTIST=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | if [ -z " | ||
+ | then | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO artist (artist_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | done | ||
+ | else | ||
+ | echo " | ||
+ | echo $ARTIST | ||
+ | DBARTIST=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | if [ -z " | ||
+ | then | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO artist (artist_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | fi | ||
+ | |||
+ | if [ " | ||
+ | DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | else | ||
+ | DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | fi | ||
+ | |||
+ | if [ -z " | ||
+ | then | ||
+ | if [ " | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO album (album_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | else | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO album (album_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | fi | ||
+ | |||
+ | DBALBUMNEW=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | |||
+ | if [ -z " | ||
+ | then | ||
+ | if [ " | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO track (track_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | else | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO track (track_name, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | fi | ||
+ | |||
+ | DBTRACKNEW=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | DBALBUMTRACK=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | |||
+ | # | ||
+ | if [ " | ||
+ | then | ||
+ | for ((i = 0; i < ${# | ||
+ | do | ||
+ | DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | DBARTISTTRACK=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | if [ -z " | ||
+ | then | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO connectartisttrack (artist_id, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | done | ||
+ | else | ||
+ | DBARTISTNEW=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | DBARTISTTRACK=$(mysql --login-path=$LOGIN -D $DB -se " | ||
+ | if [ -z " | ||
+ | then | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO connectartisttrack (artist_id, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | fi | ||
+ | |||
+ | # | ||
+ | if [ -z " | ||
+ | then | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO connectalbumtrack (album_id, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | |||
+ | #played is not yet fixed for multiple artists and just uses the first one | ||
+ | if [ " | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO played (artist_id, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | else | ||
+ | mysql --login-path=$LOGIN -D $DB << EOFMYSQL | ||
+ | INSERT INTO played (artist_id, | ||
+ | VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | |||
+ | #copy webfile to webfile2 for comparison in the next run | ||
+ | cp -a $WEBFILE $WEBFILE2 | ||
+ | |||
+ | fi | ||
+ | } | ||
+ | |||
+ | # | ||
+ | |||
+ | GET_SPOTIFY_TRACK | ||
+ | CHECK_TOKEN | ||
+ | |||
+ | LASTSTATUS=`echo $?` | ||
+ | |||
+ | if [ " | ||
+ | REFRESH_TOKEN | ||
+ | GET_SPOTIFY_TRACK | ||
+ | fi | ||
+ | |||
+ | GET_CURRENT_TRACK_INFO | ||
+ | WRITE_TO_WEBSITE | ||
+ | WRITE_TO_MYSQL | ||
+ | </ | ||
+ | |||
+ | ==== Automation ==== | ||
+ | |||
+ | The bashscript can be automated via crontab (example runs every 2 minutes). | ||
+ | |||
+ | < | ||
+ | */2 * * * * / | ||
+ | </ |