#!/usr/bin/python3
import os, time
from xml.sax import saxutils
import psycopg2

# From http://pypgsql.sourceforge.net/pypgsql-faq.html
#pyPgSQL.fetchReturnsList = 1
#pyPgSQL.noPostgresCursor = 1

defaultDbName = "iTunesTracks"
defaultTableName = "tracks"
defaultTopArtistName = "topartist"
defaultPlaylistsNamesTableName = "playlistsnames"
defaultPlaylistsTableName = "playlists"
defaultPlaylistDataName = "playlistdata"
defaultOutputFileName = "analysis.html"

def makeSummize(output, ratingInfo, width=150):
    total = 0
    for i in range(6):
        if (i not in ratingInfo):
            ratingInfo[i] = 0
        else:
            total += ratingInfo[i]
    output.append('<span class="sum">')
    cumWidth = 0
    for i in range(6):
        if (ratingInfo[i] > 0):
            newWidth = (ratingInfo[i] * width)/total
            songWord = "songs"
            if (ratingInfo[i] == 1):
                songWord = "song"
            output.append('<span class="r%d" title="%d %s of rating %d" style="width:%dpx;left:%dpx;"></span>' % (i, ratingInfo[i], songWord, i, newWidth, cumWidth))
            cumWidth += newWidth
    output.append('</span>\n')

curIndex = 0
def chartCallback(connection, index, tableName, column):
    def tempCallback(output, item, whereInTable):
        global curIndex
        if (whereInTable == 0):
            if (index == curIndex):
                if (item == None or item == 'None'):
                    whereString = "%s IS NULL" % column
                else:
                    whereString = "%s='%s'" % (column, str(item).replace("'", "''"))
                sqlQuery = "SELECT rating/20 AS rating, COUNT(*) AS numTracks FROM %s WHERE %s GROUP BY rating ORDER BY rating;" % (tableName, whereString)
                tempCursor = connection.cursor()
                tempCursor.execute(sqlQuery)
                ratingInfo = {}
                for record in tempCursor:
                    ratingInfo[int(record[0])] = int(record[1])
                output.append('<td style="width: 150px;">')
                makeSummize(output, ratingInfo)
                output.append('</td>')
            curIndex += 1
        else:
            curIndex = 0
    return tempCallback

curIndex = 0
def playlistCallback(connection, index, tableName, playlistsNamesTableName, playlistsTableName):
    def tempCallback(output, item, whereInTable):
        global curIndex
        if (whereInTable == 0):
            if (index == curIndex):
                if (item == None):
                    item = ''
                #  SELECT rating/20 AS rating, COUNT(*) AS num FROM ((SELECT playlistid FROM playlistsnames WHERE playlistname='Guitar Hero') AS baz INNER JOIN (SELECT trackid FROM playlists) AS foo USING (playlistid)) INNER JOIN (SELECT * from tracks) AS bar USING (trackid) GROUP BY rating;
                #sqlQuery = "SELECT rating/20 AS rating, COUNT(*) AS numTracks FROM (SELECT trackid from %s WHERE playlistid='%s') AS foo INNER JOIN (SELECT * from %s) AS bar USING (trackid) GROUP BY rating;" % (tableName, column, str(item).replace("'", "''"))
                # TODO
                sqlQuery = "SELECT rating/20 AS rating, COUNT(*) FROM %s, %s, %s WHERE %s.playlistname='%s' AND %s.playlistid=%s.playlistid AND %s.trackid=%s.trackid GROUP BY rating;" % (tableName, playlistsNamesTableName, playlistsTableName, playlistsNamesTableName, str(item).replace("'", "''"), playlistsNamesTableName, playlistsTableName, tableName, playlistsTableName)
                tempCursor = connection.cursor()
                tempCursor.execute(sqlQuery)
                ratingInfo = {}
                for record in tempCursor:
                    ratingInfo[int(record[0])] = int(record[1])
                output.append('<td style="width: 150px;">')
                makeSummize(output, ratingInfo)
                output.append('</td>')
            curIndex += 1
        else:
            curIndex = 0
    return tempCallback

overallRatingInfo = {}
def overallRatingCallback(output, row, whereInTable):
    if (row != None):
        overallRatingInfo[row[0]] = int(row[1])
    else:
        if (whereInTable == 2):
            output.append('<div>')
            makeSummize(output, overallRatingInfo)
            output.append('</div>')
        #maxNum = 0
        #for i in range(6):
        #    if (i not in ratingInfo):
        #        ratingInfo[i] = 0
        #    else:
        #        maxNum = max(ratingInfo[i], maxNum)
        ## Figure out how to make the graph.
        #mainOutput.append('<ul id="ratingGraph">\n')
        #for i in range(6):
        #    mainOutput.append('<li class="qtr" id="q%d">%d<ul>\n' % (i+1, i))
        #    mainOutput.append('<li class="sent bar" style="height: %dpx"><p>%d</p></li></ul></li>\n' % (int(300 * (float(ratingInfo[i])/float(maxNum * 1.1))), ratingInfo[i]))
        #mainOutput.append('<li id="ticks">\n')
        #mainOutput.append('<div class="tick" style="height: 59px;"><p>%d</p></div>\n' % int(maxNum * 1.1))
        #mainOutput.append('<div class="tick" style="height: 59px;"><p>%d</p></div>\n' % int(maxNum * 1.1 * .8))
        #mainOutput.append('<div class="tick" style="height: 59px;"><p>%d</p></div>\n' % int(maxNum * 1.1 * .6))
        #mainOutput.append('<div class="tick" style="height: 59px;"><p>%d</p></div>\n' % int(maxNum * 1.1 * .4))
        #mainOutput.append('<div class="tick" style="height: 59px;"><p>%d</p></div>\n' % int(maxNum * 1.1 * .2))
        #mainOutput.append('</li>\n')
        #mainOutput.append('</ul>\n')
    #print "got row: %s" % row

linksOutput = []
mainOutput = []
def main(dbName, tableName, topArtistName, playlistsNamesTableName, playlistsTableName, playlistDataName, outputFileName, session=None, statusShelf=None):
    if (os.path.exists(outputFileName)):
        os.remove(outputFileName)
    outputFile = open(outputFileName, 'a')
    if (statusShelf != None):
        statusShelf['data']['status'] = 'Performing analysis on data...'
        statusShelf.sync()
    connection = psycopg2.connect('dbname=%s' % dbName)
    connection.set_client_encoding('UNICODE')
    cursor = connection.cursor()
    outputFile.write('<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">\n')
    outputFile.write('<html>\n<head><title>iTunes Rating Analysis</title>\n')
    outputFile.write('<META http-equiv="Content-Type" content="text/html; charset=utf-8">\n')
    outputFile.write('<META http-equiv="Content-Style-Type" content="text/css">\n')
    outputFile.write('<style type="text/css">\n')
    outputFile.write('body,td,a,div,p { font-family: arial, sans-serif }\n')
    outputFile.write('th { text-align: left; }\n')
    outputFile.write('.even { background-color: #BFFFBD; }\n')
    outputFile.write('.odd { background-color: #BDE0FF; }\n')
    outputFile.write('table.sortable th {background-color: #aaa; color: black; }\n')
    outputFile.write('table caption {background-color: #75BCFF; white-space: nowrap;}\n')
    outputFile.write('body { background-color: #E5E5E5; }\n')
    outputFile.write('/* www.summize.com style bars */\n')
    outputFile.write('.sum{ height: 16px; display: block; overflow: hidden; position: relative; width: 150px; }\n')
    outputFile.write('.sum span { height: 16px; overflow: hidden; position: absolute; }\n')
    outputFile.write('.r0 { background-color: #000000; }\n')
    outputFile.write('.r1 { background-color: #f05233; }\n')
    outputFile.write('.r2 { background-color: #e8993a; }\n')
    outputFile.write('.r3 { background-color: #e0d541; }\n')
    outputFile.write('.r4 { background-color: #abd846; }\n')
    outputFile.write('.r5 { background-color: #5dcc4e; }\n')
    # TODO - it would be nice to automate that somehow
    outputFile.write('/* Technique borrowed from http://meyerweb.com/eric/css/edge/bargraph/demo-ie.html */\n')
    outputFile.write('#ratingGraph {position: relative; width: 480px; height: 300px; margin: 1.1em 0em 3.5em; padding: 0px; background: #DDD; border: 2px solid gray; list-style: none; font: 9px Helvetica, Geneva, sans-serif;}\n')
    outputFile.write('#ratingGraph ul {margin: 0px; padding: 0px; list-style: none;}\n')
    outputFile.write('#ratingGraph li {position: absolute; bottom: 0px; width: 80px; z-index: 2; margin: 0px; padding: 0px; text-align: center; list-style: none;}\n')
    outputFile.write('#ratingGraph li.qtr {height: 298px; padding-top: 2px; border-right: 1px dotted #C4C4C4;}\n')
    outputFile.write('#ratingGraph li.bar {width: 60px; border: 1px solid; border-bottom: none; color: #000;}\n')
    outputFile.write('#ratingGraph li.bar p {margin: 5px 0px 0px; padding: 0px;}\n')
    outputFile.write('#ratingGraph li.sent {left: 5px; background: #DCA; border-color: #EDC #BA9 #000 #EDC;}\n')
    outputFile.write('#ratingGraph #q1 {left: 0px;}\n')
    outputFile.write('#ratingGraph #q2 {left: 80px;}\n')
    outputFile.write('#ratingGraph #q3 {left: 160px;}\n')
    outputFile.write('#ratingGraph #q4 {left: 240px;}\n')
    outputFile.write('#ratingGraph #q5 {left: 320px;}\n')
    outputFile.write('#ratingGraph #q6 {left: 400px; border-right: none}\n')
    outputFile.write('#ratingGraph #ticks {width: 480px; height: 300px; z-index: 1;}\n')
    outputFile.write('#ratingGraph #ticks .tick {position: relative; border-bottom: 1px solid #BBB; width: 480px;}\n')
    outputFile.write('#ratingGraph #ticks .tick p {position: absolute; left: 100%; top: -0.67em; margin: 0em 0em 0em 0.5em;}\n')
    outputFile.write('</style>\n')
    outputFile.write('<script type="text/javascript">\n')
    outputFile.write('<!--\n')
    sortTableFile = open('sorttable.js', 'r')
    for line in sortTableFile.readlines():
        # In inline scripts, end tags cause validation to fail.
        # So, escape them.
        outputFile.write(line.replace('</', '<\/'))
    outputFile.write('// -->\n')
    outputFile.write('</script>\n')
    outputFile.write('</head>\n')
    outputFile.write('<body><p>This analysis done by <a href="http://gregstoll.dyndns.org/iTunesAnalysis/">Greg\'s iTunes Rating Analysis</a> on %s</p>\n' % time.asctime())
    findFavoriteGroups(cursor, tableName, topArtistName, outputFile)
    outputFile.write('<p>Click on the headers to sort by column.  The little charts are based on <a href="http://www.summize.com">summize.com</a> - each color represents a different rating (black is 0, red is 1, etc.).  You can mouse over the colors to see the exact number of songs that had that rating.</p>\n')
    linksOutput.append('<ul>')
    executeCommand(cursor, "SELECT foo.averagerating, foo.stdDev, foo.numRatedTracks, bar.totalTracks, baz.trackstorate FROM (SELECT AVG(rating/20.0) AS averagerating, STDDEV(rating/20.0) AS stdDev, COUNT(*) AS numRatedTracks FROM %s WHERE rating > 20) AS foo, (SELECT COUNT(*) AS totalTracks FROM %s) AS bar, (SELECT COUNT(*) AS trackstorate FROM %s WHERE rating = 0 AND (genre IS NULL OR genre<>'Books & Spoken')) AS baz;" % (tableName, tableName, tableName), "Average rating of all tracks", ['Average rating', 'Standard deviation', 'Rated tracks', 'Total tracks', 'Tracks to rate'], 'avgRating')
    executeCommand(cursor, "SELECT rating/20 AS rating, COUNT(*) AS numTracks FROM %s GROUP BY rating ORDER BY rating;" % (tableName), "Number of rated tracks", ['Rating', 'Number of tracks'], 'ratingTracks', overallRatingCallback, False)
    executeCommand(cursor, "SELECT SUM(playcount*length)/(3600*24) AS days, (SUM(playcount*length)-((3600*24)*(SUM(playcount*length)/(3600*24))))/3600 AS hours, (SUM(playcount*length)-(3600*(SUM(playcount*length)/3600)))/60 AS minutes, (SUM(playcount*length)-(60*(SUM(playcount*length)/60))) AS seconds FROM %s;" % (tableName) , "Total time listened to music", ['Days', 'Hours', 'Minutes', 'Seconds'], 'timeListened')
    executeCommand(cursor, "SELECT SUM(length)/(3600*24) AS days, (SUM(length)-((3600*24)*(SUM(length)/(3600*24))))/3600 AS hours, (SUM(length)-(3600*(SUM(length)/3600)))/60 AS minutes, (SUM(length)-(60*(SUM(length)/60))) AS seconds FROM %s;" % (tableName), "Total time of music in library", ['Days', 'Hours', 'Minutes', 'Seconds'], 'totalTime')
    executeCommand(cursor, "SELECT SUM(length)/(3600*24) AS days, (SUM(length)-((3600*24)*(SUM(length)/(3600*24))))/3600 AS hours, (SUM(length)-(3600*(SUM(length)/3600)))/60 AS minutes, (SUM(length)-(60*(SUM(length)/60))) AS seconds FROM %s WHERE rating = 0 AND (genre IS NULL OR genre <> 'Books & Spoken');" % (tableName), "Total time of unrated music", ['Days', 'Hours', 'Minutes', 'Seconds'], 'unratedTime')
    executeCommand(cursor, "SELECT * FROM (SELECT artist, COUNT(*) AS numTracks, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev FROM %s WHERE rating > 20 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM %s GROUP BY artist) AS bar USING (artist) WHERE numTracks > 4 ORDER BY foo.averageRating DESC, foo.numTracks DESC;" % (tableName, tableName), "Highest average rated artists with more than four tracks rated", ['Artist', 'Chart', 'Rated tracks', 'Average rating', 'Standard deviation', 'Total tracks'], 'bestArtists1', chartCallback(connection, 0, tableName, 'artist'))
    executeCommand(cursor, "SELECT * FROM (SELECT artist, COUNT(*) AS numTracks, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev FROM %s WHERE rating > 20 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM %s GROUP BY artist) AS bar USING (artist) WHERE numTracks > 1 AND numTracks < 5 ORDER BY foo.averageRating DESC, foo.numTracks DESC;" % (tableName, tableName), "Highest average rated artists with two to four tracks rated", ['Artist', 'Chart', 'Tracks', 'Average rating', 'Standard deviation', 'Total tracks'], 'bestArtists2', chartCallback(connection, 0, tableName, 'artist'))
    executeCommand(cursor, "SELECT * FROM (SELECT album, COUNT(*) AS numTracks, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev FROM %s WHERE rating > 20 GROUP BY album) AS foo INNER JOIN (SELECT album, COUNT(*) AS totalTracks, %s(album) AS mainartist FROM %s GROUP BY album) AS bar USING (album) WHERE numTracks > 1 ORDER BY foo.averageRating DESC, foo.numTracks DESC;" % (tableName, topArtistName, tableName), "Highest average rated albums with at least one track rated", ['Album', 'Chart', 'Rated tracks', 'Average rating', 'Standard deviation', 'Total tracks', 'Main artist'], 'bestAlbums', chartCallback(connection, 0, tableName, 'album'))
    # SELECT COUNT(*), ROUND(AVG(rating/20.0),5) FROM (SELECT trackid FROM playlists WHERE playlistid=38072) AS foo INNER JOIN (SELECT * from tracks) AS bar USING (trackid) WHERE rating > 0;  -- gets for that playlist
    # CREATE FUNCTION playlistdata (integer) RETURNS playlistresults AS 'SELECT * FROM (SELECT playlistname FROM playlistsnames WHERE playlistid=$1) AS tempname, (SELECT COUNT(*) FROM playlists WHERE playlistid=$1) AS tempblah, (SELECT COUNT(*), ROUND(AVG(rating/20.0),5), ROUND(STDDEV(rating/20.0),5) FROM (SELECT trackid FROM playlists WHERE playlistid=$1) AS foo INNER JOIN (SELECT * from tracks) AS bar USING (trackid) WHERE rating > 0) AS baz;' LANGUAGE SQL;
    # CREATE TABLE playlistresults (playlistname VARCHAR(200), totalcount bigint, ratedcount bigint, avgRating NUMERIC, stdDev NUMERIC);

    #  SELECT avgrating(a) FROM (SELECT playlistdata(playlistid) AS a FROM playlists GROUP BY playlistid) AS foo;
    if (playlistsNamesTableName):
        executeCommand(cursor, 'SELECT playlistname(a), avgrating(a) AS avgrating, stddev(a), ratedcount(a), totalcount(a)  FROM (SELECT %s(playlistid) AS a FROM %s GROUP BY playlistid) AS foo ORDER BY avgrating DESC;' % (playlistDataName, playlistsTableName) , "Highest rated playlists", ['Playlist name', 'Chart', 'Average rating', 'Standard deviation', 'Rated tracks', 'Total tracks'], 'bestPlaylists', playlistCallback(connection, 0, tableName, playlistsNamesTableName, playlistsTableName))
    executeCommand(cursor, "SELECT bar.genre, foo.averageRating, foo.stdDev, foo.numRatedTracks, bar.totalTracks FROM (SELECT genre, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev, COUNT(*) AS numRatedTracks FROM %s WHERE rating > 20 GROUP BY genre) AS foo RIGHT JOIN (SELECT genre, COUNT(*) AS totalTracks FROM %s GROUP BY genre) AS bar ON (foo.genre=bar.genre OR (foo.genre IS NULL AND bar.genre IS NULL)) ORDER BY averageRating DESC;" % (tableName, tableName), "Genres in rating order", ['Genre', 'Chart', 'Average rating', 'Standard deviation', 'Rated tracks', 'Total tracks'], 'bestGenres', chartCallback(connection, 0, tableName, 'genre'))
    executeCommand(cursor, "SELECT * FROM (SELECT artist, COUNT(*) AS num4and5StarTracks FROM %s WHERE rating >= 80 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS ratedTracks FROM %s WHERE rating > 0 GROUP BY artist) AS baz USING (artist) INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM %s GROUP BY artist) AS bar USING (artist) ORDER BY num4and5StarTracks DESC, totalTracks;" % (tableName, tableName, tableName), "Artists with most 4 and 5 star tracks", ['Artist', 'Chart', '4 & 5 star tracks', 'Rated tracks', 'Total tracks'], 'fourAndFiveStarArtists', chartCallback(connection, 0, tableName, 'artist'))
    executeCommand(cursor, "SELECT * FROM (SELECT artist, COUNT(*) AS num5StarTracks FROM %s WHERE rating = 100 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS ratedTracks FROM %s WHERE rating > 0 GROUP BY artist) AS baz USING (artist) INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM %s GROUP BY artist) AS bar USING (artist) ORDER BY num5StarTracks DESC, totalTracks;" % (tableName, tableName, tableName), "Artists with most 5 star tracks", ['Artist', 'Chart', '5 star tracks', 'Rated tracks', 'Total tracks'], 'fiveStarArtists', chartCallback(connection, 0, tableName, 'artist'))
    executeCommand(cursor, "SELECT * FROM (SELECT album, COUNT(*) AS num5StarTracks FROM %s WHERE rating = 100 GROUP BY album) AS foo INNER JOIN (SELECT album, COUNT(*) AS totalTracks, %s(album) AS mainartist FROM %s GROUP BY album) AS bar USING (album) ORDER BY num5StarTracks DESC, totalTracks;" % (tableName, topArtistName, tableName), "Albums with most 5 star tracks", ['Album', 'Chart', '5 star tracks', 'Total tracks', 'Main artist'], 'fiveStarAlbums', chartCallback(connection, 0, tableName, 'album'))
    executeCommand(cursor, "SELECT bar.comments, foo.averageRating, foo.stdDev, foo.numRatedTracks, bar.totalTracks FROM (SELECT comments, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev, COUNT(*) AS numRatedTracks FROM %s WHERE rating > 20 GROUP BY comments) AS foo RIGHT JOIN (SELECT comments, COUNT(*) AS totalTracks FROM %s GROUP BY comments) AS bar ON (foo.comments=bar.comments OR (foo.comments IS NULL AND bar.comments IS NULL)) WHERE foo.numRatedTracks > 1 ORDER BY averageRating DESC;" % (tableName, tableName), "Comments in rating order", ['Comments', 'Average rating', 'Standard deviation', 'Rated tracks', 'Total tracks'], 'commentsRating')
    executeCommand(cursor, "SELECT * FROM (SELECT artist, COUNT(*) AS numTracks FROM %s GROUP BY artist) AS foo WHERE numTracks > 1 ORDER BY numTracks DESC;" % (tableName), "Artists with more than one song", ['Artist', 'Total tracks'], 'totalArtists')
    executeCommand(cursor, "SELECT SUM(playcount) FROM %s;" % (tableName), "Total number of tracks played", ['Tracks'], 'tracksPlayed')
    linksOutput.append('</ul>')
    for line in linksOutput:
        outputFile.write(line)
    for line in mainOutput:
        outputFile.write(line)
    outputFile.write('<p><a href="https://validator.w3.org/check?uri=referer"><img src="https://www.w3.org/Icons/valid-html401" alt="Valid HTML 4.01 Strict" height="31" width="88"></a>\n')
    outputFile.write('<a href="https://jigsaw.w3.org/css-validator/check/referer"><img style="border:0;width:88px;height:31px" src="https://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!"></a></p>\n')
    if (outputFileName == defaultOutputFileName):
        # Add tracking, etc. to local one
        outputFile.write('<!--#include virtual="endOfBody.html"-->')
    outputFile.write('</body></html>\n')
    outputFile.close()

def findFavoriteGroups(cursor, tableName, topArtistName, outputFile):
    # Find top 5 artists by number of 4 and 5 rated songs and by average rating
    # (with more than 4 songs rated)
    artists = set()
    cursor.execute("SELECT artist, count(*) as numtracks FROM %s WHERE rating >= 80 GROUP BY artist ORDER BY numtracks DESC LIMIT 5;" % tableName)
    for record in cursor:
        artists.update([record[0]])
    cursor.execute("SELECT artist, count(*) as numtracks FROM %s WHERE rating = 100 GROUP BY artist ORDER BY numtracks DESC LIMIT 5;" % tableName)
    for record in cursor:
        artists.update([record[0]])
    cursor.execute("SELECT * FROM (SELECT artist, COUNT(*) AS numTracks, AVG(rating) AS avgRating FROM %s WHERE rating > 20 GROUP BY artist) AS foo WHERE foo.numTracks > 4 ORDER BY foo.avgRating DESC LIMIT 5;" % tableName)
    for record in cursor:
        artists.update([record[0]])
    artistsList = list(artists)
    artistsList.sort()
    outputFile.write('<p><b>Favorite artists</b>: %s</p>' % (', '.join(artistsList)))
    # TODO - topartist() function is really slow here
    cursor.execute("SELECT foo.album, %s(foo.album) FROM (SELECT album, COUNT(*) AS numTracks, AVG(rating) AS avgRating FROM %s WHERE rating > 20 GROUP BY album) AS foo WHERE foo.numTracks > 4 ORDER BY foo.avgRating DESC LIMIT 5;" % (topArtistName, tableName))
    albumsList = [] 
    for record in cursor:
        albumsList.append((record[0], record[1]))
    def artistString(artistTuple):
        if (artistTuple[1] != '' and artistTuple[1] != 'None'):
            return "%s (by %s)" % artistTuple
        else:
            return "%s" % artistTuple[0]
    outputFile.write('<p><b>Favorite albums</b>: %s</p>' % (', '.join([artistString(x) for x in albumsList])))


def executeCommand(cursor, sqlQuery, description, headers, hrefName, rowCallback=None, callAfterEachItem=True):
    cursor.execute(sqlQuery)
    linksOutput.append('<li><a href="#%s">%s</a></li>' % (hrefName, description))
    classStr = ''
    # Make the table sortable if there's more than one result.
    if (cursor.rowcount > 1):
        classStr = 'class="sortable" '
    mainOutput.append('<table %sid="%s"><caption>%s</caption>\n' % (classStr, hrefName, saxutils.escape(description)))
    mainOutput.append("<tr>")
    for header in headers:
        mainOutput.append("<th>%s</th>" % saxutils.escape(header))
    mainOutput.append("</tr>\n")
    for record in cursor:
        mainOutput.append("<tr>")
        if (rowCallback != None):
            row = []
        # TODO - make constants for whereInTable
        for item in record:
            if (rowCallback != None):
                row.append(item)
            mainOutput.append("<td>%s</td>" % saxutils.escape(str(item)))
            if (callAfterEachItem and rowCallback):
                rowCallback(mainOutput, item, 0)
        if (not callAfterEachItem and rowCallback):
            rowCallback(mainOutput, row, 1)
        mainOutput.append("</tr>\n")
        if (callAfterEachItem and rowCallback):
            rowCallback(mainOutput, None, 1)
    mainOutput.append("</table>\n")
    if (rowCallback):
        rowCallback(mainOutput, None, 2)
    #commandLine = "psql -d %s -c \"%s\" >> %s" % (dbName, sqlQuery, outputFileName)
    #os.system(commandLine)


if (__name__ == '__main__'):
    main(defaultDbName, defaultTableName, defaultTopArtistName, defaultPlaylistsNamesTableName, defaultPlaylistsTableName, defaultPlaylistDataName, defaultOutputFileName)