com.cutunes.server
Class DatabaseController

java.lang.Object
  extended bycom.cutunes.server.DatabaseController

public class DatabaseController
extends java.lang.Object

Contains all logic for connecting/querying/updating the top40db database. Written as a Java singleton so only a single database connection can be opened by an application at any given time.

Methods mainly called through the RequestHandler in response to XML-RPC messages. A couple methods, updateTop40Caches() and updatePlayCountWeek() are called by cron jobs on a regular basis.

Author:
blake, hal2001

Method Summary
 void bulkLoadHasSongData(int userID, java.lang.String hasSongsFile)
          Uses MySQL's load data function to bulk load a file full of information into the has_songs table.
 void bulkLoadSongData(int userID, java.lang.String songsFile)
          Uses MySQL's load data function to bulk load a file full of information into the songs table.
 void clearTable(java.lang.String table)
          Clear a table
 int countForArtist(java.lang.String query)
           
 java.util.Vector getArtistsLike(java.lang.String artist)
          Gets artists that are similar to another artist
 java.util.Vector getArtistsOfUser(int userID, int myUserID, int limit)
          Get the top artists of a user that another user has
static DatabaseController getInstance()
          Java singleton getInstance() method.
 java.sql.Timestamp getLastSync(int userID, java.lang.String version)
          Queries database to get the last time a user synced.
 java.util.Vector getSongsForArtist(java.lang.String artist, int userID, int numItems)
          Gets songs of a given artist that a user has
 int getUserID(java.lang.String user, java.lang.String password)
          Queries database with username and password to find the unique ID for a given user.
 java.util.Vector getUserIDFor(java.lang.String query)
          Get a the name and ID of a user given a query.
 void loadDataIntoTable(java.lang.String file, java.lang.String table, boolean isLocal)
          Load data from a file into a table
 void logMessage(boolean error, java.lang.String function, java.lang.String message)
          Logs messages into the message_logs table of the database.
static void main(java.lang.String[] args)
          Simple main method used for testing the database controller.
 void setLastSync(int userID, java.lang.String version)
          Sets the last sync time for a user to the current time in UTC, and logs the sync in the sync_logs table.
 void updateForFirstSync(int userID, java.lang.String version)
          Adds 1 to the this week play count of every song that was played in the last week
 void updateHasSong(int userID, java.util.Date lastSyncDate, java.util.Vector songGroup)
          Method updates the playcount/last_played information in the has_song table for a given user.
 void updatePlayCountWeek()
          Automatically 'decays' the play_count_week field for all rows in the has_songs table.
 void updateTop40Caches()
          Caches the Top40 artists, albums, songs (both week and all-time views) in extra table in the database (called top_songs, top_artists, etc).
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Method Detail

getInstance

public static DatabaseController getInstance()
Java singleton getInstance() method.

Returns:
Returns a reference to the singleton object

logMessage

public void logMessage(boolean error,
                       java.lang.String function,
                       java.lang.String message)
Logs messages into the message_logs table of the database. Messages are logged with current time, a boolean describing whether the message is recording an error or not, a string describing the method logging the error, and a message.

Parameters:
error - Boolean error is true is message is an error, false if just a friendly message
function - String describing method/function issuing message
message - String with message to be logged

getUserID

public int getUserID(java.lang.String user,
                     java.lang.String password)
Queries database with username and password to find the unique ID for a given user.

Parameters:
user - String username
password - String password
Returns:
Unique ID identifying a given user, or -1 if no user can be found for username/password pair

getLastSync

public java.sql.Timestamp getLastSync(int userID,
                                      java.lang.String version)
Queries database to get the last time a user synced. Note that an hour is subtracted from the last sync time that is returned in order to trick the client into sending a user's most recent music (from the past hour). This is very useful for debugging/testing. Time returned is in UTC even though the time outputed to System.out is in EST.

Parameters:
userID - Unique ID identifying user
version - String version of client used
Returns:
Timestamp of last sync minus an hour UTC, or null if no user is found

setLastSync

public void setLastSync(int userID,
                        java.lang.String version)
Sets the last sync time for a user to the current time in UTC, and logs the sync in the sync_logs table.

Parameters:
userID - Unique ID identifying user
version - String version of client software

updateHasSong

public void updateHasSong(int userID,
                          java.util.Date lastSyncDate,
                          java.util.Vector songGroup)
Method updates the playcount/last_played information in the has_song table for a given user. This method will be used for all syncs besides the user's first sync (when this information will be bulk loaded using bulkLoadHasSongData()).

Three steps are involved in updated this information:

  • The user's current playcount for the song is retrieved.
  • If the song is found (ie the user has then specified song in the has_songs table already), the playcount is adjusted by the number of days that have passed since the last sync and is updated with the new information.
  • If the song is not found the information is inserted in the has_song table.

    We have attempted to optimize this method so it runs as fast as possible, and prepared statements are used to keep things quick

    Parameters:
    userID - Unique ID identifying user
    lastSyncDate - Timestamp of user's last sync
    songGroup - Vector of songs to update has_song information for

  • bulkLoadSongData

    public void bulkLoadSongData(int userID,
                                 java.lang.String songsFile)
    Uses MySQL's load data function to bulk load a file full of information into the songs table. This is used for every update (even though most updates probably won't include new songs). Therefore a lot of duplicate information will be loaded, but this information is simply ignored (because the primary keys conflict).

    Parameters:
    userID - Unique ID identifying user
    songsFile - String with path to file to bulk load

    bulkLoadHasSongData

    public void bulkLoadHasSongData(int userID,
                                    java.lang.String hasSongsFile)
    Uses MySQL's load data function to bulk load a file full of information into the has_songs table. This is typically ONLY used for a user's first sync, since every other sync requires the weekly play count field to be updated according to the algorithm in updateHasSong().

    Parameters:
    userID - Unique ID identifying user
    hasSongsFile - String with path to file to bulk load

    updateTop40Caches

    public void updateTop40Caches()
    Caches the Top40 artists, albums, songs (both week and all-time views) in extra table in the database (called top_songs, top_artists, etc). This is to save time on the the community tab of the website, where these queries take considerable time to run in real time.


    updatePlayCountWeek

    public void updatePlayCountWeek()
    Automatically 'decays' the play_count_week field for all rows in the has_songs table. This method is run my cron on a regular basis (like every night).


    updateForFirstSync

    public void updateForFirstSync(int userID,
                                   java.lang.String version)
    Adds 1 to the this week play count of every song that was played in the last week


    loadDataIntoTable

    public void loadDataIntoTable(java.lang.String file,
                                  java.lang.String table,
                                  boolean isLocal)
    Load data from a file into a table

    Parameters:
    file -
    table -

    clearTable

    public void clearTable(java.lang.String table)
    Clear a table

    Parameters:
    table -

    getArtistsLike

    public java.util.Vector getArtistsLike(java.lang.String artist)
    Gets artists that are similar to another artist

    Parameters:
    artist -
    Returns:
    A vector containing the following items: A vector of artists A vector of scores corresponding to the artist

    getSongsForArtist

    public java.util.Vector getSongsForArtist(java.lang.String artist,
                                              int userID,
                                              int numItems)
    Gets songs of a given artist that a user has

    Parameters:
    artist -
    userID -
    numItems -
    Returns:
    A vector containing the following items: A vector of song names A vector of albums A vecor of artists A vector of scores

    getArtistsOfUser

    public java.util.Vector getArtistsOfUser(int userID,
                                             int myUserID,
                                             int limit)
    Get the top artists of a user that another user has

    Parameters:
    userID -
    myUserID -
    limit -
    Returns:
    A vector containing the following items: A vector of artists A vector of scores corresponding to the artist

    getUserIDFor

    public java.util.Vector getUserIDFor(java.lang.String query)
    Get a the name and ID of a user given a query. The query can be a fullname, firstname, lastname, or username

    Parameters:
    query -
    Returns:
    A vector containing the ID and fullname

    countForArtist

    public int countForArtist(java.lang.String query)
    Parameters:
    query -
    Returns:

    main

    public static void main(java.lang.String[] args)
    Simple main method used for testing the database controller.

    Parameters:
    args - No command line args are needed.