User Tools

Site Tools


linux:ubuntu:alexamusictodatabase

Add played music to database

This script is an updated version of my script to display playing music title on website. It will write all the artists, tracks and albums to a MySQL database.

Database

amazonmusic.sql
CREATE TABLE `album` (
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `album_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `album_name` char(128) DEFAULT NULL,
  `album_cover` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (album_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `artist` (
  `artist_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `artist_name` char(128) DEFAULT NULL,
  PRIMARY KEY (artist_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `played` (
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `album_id` smallint(5) NOT NULL DEFAULT '0',
  `track_id` smallint(5) NOT NULL DEFAULT '0',
  `played` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `track` (
  `track_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `track_name` char(255) DEFAULT NULL,
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `album_id` smallint(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (track_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Script

Bashscript

copyTracksinSQL.sh
#!/bin/bash
 
#Login MySQL
LOGIN="local"
 
#Database MySQL
DB=amazonmusic
 
TMPFILE=/tmp/audio.txt
WEBFILE=/var/www/mysite/audio.php
WEBFILE2=/var/www/mysite/audio2.php
 
/alexa/alexa-remote-control/alexa_remote_control.sh -q > $TMPFILE
 
#OFFLINE=`grep -m1 'message' $TMPFILE | cut -d '"' -f 3 | cut -d ' ' -f 2`
OFFLINE=`grep -m1 'message' $TMPFILE | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'`
STATE=`grep -m1 'state' $TMPFILE | cut -d '"' -f 4`
STATECUT=`grep -m1 'state' /tmp/audio.txt | awk 'BEGIN{FS=" "}{gsub(",",""); print $2}'`
ARTIST=`grep -m1 'subText1' $TMPFILE | cut -d '"' -f 4`
TITLE=`grep -m1 'title' $TMPFILE | cut -d '"' -f 4`
ALBUM=`grep -m1 'subText2' $TMPFILE | cut -d '"' -f 4`
IMG=`grep -m1 'url' $TMPFILE | cut -d '"' -f 4`
 
 
#echo "" > $WEBFILE
 
if [ -f $WEBFILE ]; then
   cp -a $WEBFILE $WEBFILE2
   echo "" > $WEBFILE
else
   echo "" > $WEBFILE
fi
 
if [ "$OFFLINE" != "null" ] && [ "$STATECUT" != "null" ]; then
   echo "<table style='width:100%'><col style='width:90%'><col style='width:10%'><tr><td>" >> $WEBFILE
   echo "State: $STATE </br>" >> $WEBFILE
   echo "Artist: $ARTIST </br>" >> $WEBFILE
   echo "Title: $TITLE </br>" >> $WEBFILE
   echo "Album: $ALBUM" >> $WEBFILE
   echo "</td><td>" >> $WEBFILE
   echo "<img src='$IMG' alt='albumcover' style='width:64px;height:64px;'>" >> $WEBFILE
   echo "</td></tr></table>" >> $WEBFILE
else
   echo "Iam not listening to music right now..." >> $WEBFILE
fi
 
#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.
#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)
HASHTWO=$(md5sum $WEBFILE2 | cut -d " " -f1)
 
#if [ "$HASH" -eq 1 ] #use this if you use cmp, if status is equal to 1 (different), then execute code
if [ "$HASHONE" != "$HASHTWO" ] #compare md5 hashes, if different, then execute code
then  
 
DBTRACK=$(mysql --login-path=$LOGIN -D $DB -se "SELECT track_id FROM track WHERE track_name = '$TITLE'")
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" ]
then
        if [ -z "$DBALBUM" ]
        then
                if [ -z "$DBARTIST" ]
                then
mysql --login-path=$LOGIN -D $DB << EOFMYSQL
INSERT INTO artist (artist_name)
VALUES ('$ARTIST');
EOFMYSQL
                fi
mysql --login-path=$LOGIN -D $DB  << EOFMYSQL
INSERT INTO album (album_name,album_cover,artist_id)
VALUES ('$ALBUM','$IMG',(SELECT artist_id FROM artist WHERE artist_name = '$ARTIST'));
EOFMYSQL
        fi
mysql --login-path=$LOGIN -D $DB << EOFMYSQL
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'));
EOFMYSQL
fi
 
mysql --login-path=$LOGIN -D $DB << EOFMYSQL
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'));
EOFMYSQL
 
 
fi

Automation

The bashscript can be automated via crontab (example runs every 2 minutes).

*/2 * * * * /alexa-remote-control/copyTrackinSQL.sh >/dev/null 2>&1
linux/ubuntu/alexamusictodatabase.txt · Last modified: 2019/11/24 23:31 by lunetikk