myDataBases
Class MySQLbase

java.lang.Object
  extended by myDataBases.MySQLbase

public class MySQLbase
extends java.lang.Object

An instance of this class is used to avoid submitting unnecessary comparisons by saving former results as well as to allow a fast retrieval of these comparison results.

This class is used to store the HITs hard results (i.e, not refined) as they were submitted by the turkers in the hit table of the database. Comparisons stored in this table can be false since they are not treated in any way by this program yet. However, a lot of information is available, for instance the country turkers live in, etc. For each comparison between two media, several entries are put in the hit table corresponding to the several answers that were made by different turkers.

Such hard data is made of several fields ; these fields must be given when an instance of this object is build.

MySQLbase is also used to store refined results, i.e results obtained using an instance of Refiner. These results are stored in the results table of the database. Each comparison is present only one time : results being refined, they are no longer duplicated.

At last, this class can also generate gold data for you to upload at CrowdFLower in order to increase the quality of your results.

Author:
Leo Perrin (perrin.leo@gmail.com)

Field Summary
private  java.lang.String adr
          The url where the database used is.
private  java.lang.String[] columnsHardDataSQL
          The labels of the columns in the hit table of the MySQL database
private  java.lang.String[] columnsResultsSQL
          The labels of the columns in the results table of the MySQL database
private  java.sql.Connection conSQL
          The connection to the MySQL database
private  java.lang.Boolean[] isStringHardData
          Contains true if the content of the corresponding label in the hit table is a string and therefore requires quotation marks.
private  java.lang.Boolean[] isStringResults
          The same as isStringHardData, but for the results table.
private  java.lang.String login
          A login enabling reading an writing access to the DBcps database.
private  java.lang.String passwd
          The password matching the login used.
private  java.lang.String queryHardDataStub
          The stub common to all queries inserting new hard data in the hit table of the SQL database
private  java.lang.String queryResultsStub
          The stub common to all queries inserting results in the results table of the SQL database
 
Constructor Summary
MySQLbase(java.lang.String adr, java.lang.String login, java.lang.String passwd, java.util.List<java.lang.String> fieldsHardData, java.util.List<java.lang.String> fieldsType)
          Creates an instance of the MySQLbase class and sets all of its attributes.
 
Method Summary
 boolean alreadyDone(int idMedia1, int idMedia2, java.lang.String axis)
          Returns true if the result of the comparison between the media whose IDs are idMedia1 and idMedia2 along the axis axis is already in the database, false otherwise.
 java.lang.Boolean comparisonResult(int idMedia1, int idMedia2, java.lang.String axis)
          Returns the result of the comparison between the two media as stored in the SQL database.
 void generateDump()
          Creates a dump of the database.
 void insertHardDataInSQL(java.util.List<java.lang.String[]> entries)
          Inserts the content of entries in the hit table of the MySQL database.
 void insertResultsInSQL(java.util.List<java.lang.String[]> entries, MediaBase dbNames)
          Inserts the content of entries in the results table of the MySQL database.
static java.lang.Boolean testConnection(LogWriter log, java.lang.String testPasswd)
          Tests if the connection with the database works.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

adr

private java.lang.String adr
The url where the database used is.


login

private java.lang.String login
A login enabling reading an writing access to the DBcps database.


passwd

private java.lang.String passwd
The password matching the login used.


conSQL

private java.sql.Connection conSQL
The connection to the MySQL database


columnsHardDataSQL

private java.lang.String[] columnsHardDataSQL
The labels of the columns in the hit table of the MySQL database


isStringHardData

private java.lang.Boolean[] isStringHardData
Contains true if the content of the corresponding label in the hit table is a string and therefore requires quotation marks.


queryHardDataStub

private java.lang.String queryHardDataStub
The stub common to all queries inserting new hard data in the hit table of the SQL database


columnsResultsSQL

private java.lang.String[] columnsResultsSQL
The labels of the columns in the results table of the MySQL database


isStringResults

private java.lang.Boolean[] isStringResults
The same as isStringHardData, but for the results table.


queryResultsStub

private java.lang.String queryResultsStub
The stub common to all queries inserting results in the results table of the SQL database

Constructor Detail

MySQLbase

public MySQLbase(java.lang.String adr,
                 java.lang.String login,
                 java.lang.String passwd,
                 java.util.List<java.lang.String> fieldsHardData,
                 java.util.List<java.lang.String> fieldsType)
Creates an instance of the MySQLbase class and sets all of its attributes.

The address of the database, the login of the user able to use the SQL database and its password must be given as parameters. The fields used to store the hard results of the HIT in the hit table, as well as those used to store the refined comparisons results in the results must also be given.

Parameters:
adr - The address of the MySQL database. It is the value to give to the attribute adr.
login - The login of the database. It is the value to give to the attribute login.
passwd - The password of the database. It is the value to give to the attribute psswd.
fieldsHardData - The fields of the hit table.
fieldsType - The types of these fields.
Method Detail

testConnection

public static java.lang.Boolean testConnection(LogWriter log,
                                               java.lang.String testPasswd)
Tests if the connection with the database works.

This methods allows to test whether the connection with the MySQL database works or not by creating a DriverManager instance connected to the database. The result of this test is saved using the log LogWriter instance.

Parameters:
log - The LogWriter instance used to inform the user.
testPasswd - The password corresponding to the login.
Returns:
true if the connection works, false otherwise.

alreadyDone

public boolean alreadyDone(int idMedia1,
                           int idMedia2,
                           java.lang.String axis)
Returns true if the result of the comparison between the media whose IDs are idMedia1 and idMedia2 along the axis axis is already in the database, false otherwise.

This method checks if the comparison between idMedia1 and idMedia2 is stored in the results table of the database using SQL requests. The roles of idMedia1 and idMedia2 ARE the same here, i.e if no comparison (idMedia1>idMedia2) were found, a request is sent to see if (idMedia2>idMedia1) is present.

Parameters:
idMedia1 - The identifier of the first media being compared.
idMedia2 - The identifier of the second media to be compared.
axis - The axis along which the comparison is made.
Returns:
true if the comparison is already known, false otherwise.

comparisonResult

public java.lang.Boolean comparisonResult(int idMedia1,
                                          int idMedia2,
                                          java.lang.String axis)
Returns the result of the comparison between the two media as stored in the SQL database.

Calling this method requires alreadyPermformed(idMedia1,idMedia2,axis) to be true because such a verification is NOT performed here (due to performance considerations).

Parameters:
idMedia1 - The identifier of the first media being compared.
idMedia2 - The identifier of the second media to be compared.
axis - The axis along which the comparison is made.
Returns:
true if idMedia1 has been tagged as bigger than idMedia2, false otherwise.

insertHardDataInSQL

public void insertHardDataInSQL(java.util.List<java.lang.String[]> entries)
Inserts the content of entries in the hit table of the MySQL database.

entries' arrays' content must be in the correct order so as for each column to have the correct "name", i.e for example, if the first column of this.columnsHardData is "cfId", the first element of each array contained in entries must be the "cfId" of a HIT.

This method builds SQL request using the content of entries, but also that of columnsHardDataSQL and isStringHardData. The list of array is went through within a loop; in which another loop goes through each element of the array. For each one, it is checked whether it is string or not: if it is, quotation marks are added to the request (this is what isStringHardData is for).

Parameters:
entries - A List of array of String, each array being the result of one HIT.

insertResultsInSQL

public void insertResultsInSQL(java.util.List<java.lang.String[]> entries,
                               MediaBase dbNames)
Inserts the content of entries in the results table of the MySQL database.

entries must have the correct structure, it must fit with that of the columnsResults attribute. For instance, the first element of each array of String must be the axis along which the comparison was performed.

Actually, you should not touch this, unless you are making heavy modification.

Parameters:
entries - A list containing the results after their treatment.
dbNames - The MediaBase instance in which the names of the media are stored.

generateDump

public void generateDump()
Creates a dump of the database.

A file named DBcps_dd_mm_yy.sql is written in the data/mysql/dump folder. It contains all the information necessary to re-create a MySQL database containing all the comparisons. In case of failure of the CPS, this dump enables the process to start again where the failure occurred without requiring submitting costly HITs again.