com.cutunes.server
Class DatabaseController

java.lang.Object
  com.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.