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. More...

Collaboration diagram for myDataBases.MySQLbase:

[legend]

List of all members.

Public Member Functions
	MySQLbase (String adr, String login, String passwd, List< String > fieldsHardData, List< String > fieldsType)
	Creates an instance of the MySQLbase class and sets all of its attributes. <../..>
boolean	alreadyDone (int idMedia1, int idMedia2, 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. <../..>
Boolean	comparisonResult (int idMedia1, int idMedia2, String axis)
	Returns the result of the comparison between the two media as stored in the SQL database. <../..>
void	insertHardDataInSQL (List< String[]> entries)
	Inserts the content of `entries` in the `hit` table of the MySQL database. <../..>
void	insertResultsInSQL (List< String[]> entries, MediaBase dbNames)
	Inserts the content of `entries` in the `results` table of the MySQL database. <../..>
void	generateDump ()
	Creates a dump of the database. <../..>
Static Public Member Functions
static Boolean	testConnection (LogWriter log, String testPasswd)
	Tests if the connection with the database works. <../..>
Private Attributes
String	adr
	The url where the database used is. <../..>
String	login
	A login enabling reading an writing access to the DBcps database. <../..>
String	passwd
	The password matching the login used. <../..>
Connection	conSQL
	The connection to the MySQL database. <../..>
String[]	columnsHardDataSQL
	The labels of the columns in the `hit` table of the MySQL database. <../..>
Boolean[]	isStringHardData
	Contains `true` if the content of the corresponding label in the `hit` table is a string and therefore requires quotation marks. <../..>
String	queryHardDataStub
	The stub common to all queries inserting new hard data in the `hit` table of the SQL database. <../..>
String[]	columnsResultsSQL = { "axis", "idMedia1", "idMedia2", "greater", "nameMedia1", "nameMedia2" }
	The labels of the columns in the `results` table of the MySQL database. <../..>
Boolean[]	isStringResults = { true, false, false, false, true, true}
	The same as `isStringHardData`, but for the `results` table. <../..>
String	queryResultsStub
	The stub common to all queries inserting results in the `results` table of the SQL database. <../..>

Detailed Description

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 crowdUser.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)

Definition at line 33 of file MySQLbase.java.

Constructor & Destructor Documentation

myDataBases.MySQLbase.MySQLbase	(	String	adr,
		String	login,
		String	passwd,
		List< String >	fieldsHardData,
		List< 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.

Definition at line 104 of file MySQLbase.java.

  {
        this.adr = adr;
        this.login = login;
        this.passwd = passwd;
        this.columnsHardDataSQL = fieldsHardData.toArray(new String[0]) ;
        
        // Creation of the isStringHarData array
        List<Boolean> isFieldAString = new ArrayList <Boolean>() ; 
        for (String type : fieldsType)
        {
              if (type.equals("INT") || type.equals("FLOAT(23)") || type.equals("BIGINT") )
                    isFieldAString.add(false);
              else
                    isFieldAString.add(true);
              this.isStringHardData = isFieldAString.toArray(new Boolean[0]);
        }
        
        // Creation of the Hard Data query stub
        this.queryHardDataStub = "INSERT INTO hit (";
        for (int i=0; i<this.columnsHardDataSQL.length-1; i++)
              this.queryHardDataStub += this.columnsHardDataSQL[i]+", ";
        this.queryHardDataStub += this.columnsHardDataSQL [this.columnsHardDataSQL.length - 1] + ") VALUES (";
        
        // Creation of the Results query stub
        this.queryResultsStub = "INSERT INTO results (";
        for (int i=0; i<this.columnsResultsSQL.length-1; i++)
              this.queryResultsStub += this.columnsResultsSQL[i]+", ";
        this.queryResultsStub += this.columnsResultsSQL [this.columnsResultsSQL.length - 1] + ") VALUES (";
        
        // connection to the MySQL base
        try
        {
            Class.forName("com.mysql.jdbc.Driver");   //Register the JDBC driver for MySQL.
            this.conSQL = DriverManager.getConnection(this.adr,this.login, this.passwd);
        }
        catch(Exception e)  { e.printStackTrace(); }
  }

Member Function Documentation

boolean myDataBases.MySQLbase.alreadyDone	(	int	idMedia1,
		int	idMedia2,
		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.

Definition at line 201 of file MySQLbase.java.

  {
        String query1 = "SELECT greater FROM results WHERE axis='"+axis;
        String query2 = "SELECT greater FROM results WHERE axis='"+axis;
        boolean result = false;
        
        query1 += "' AND idMedia1="+idMedia1+" AND idMedia2="+idMedia2+" ;";
        query2 += "' AND idMedia1="+idMedia2+" AND idMedia2="+idMedia1+" ;";
        
        try
        {
              Statement stmt = this.conSQL.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
              ResultSet rs;
              rs = stmt.executeQuery(query1);
              if (rs.next())
                    result = true;
              else
              {
                    rs = stmt.executeQuery(query2);
                    if (rs.next())
                          result = true;
              }
              rs.close();
              stmt.close();
        }
        catch(Exception e)  { e.printStackTrace(); }
        return result;
  }

Boolean myDataBases.MySQLbase.comparisonResult	(	int	idMedia1,
		int	idMedia2,
		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.

Definition at line 243 of file MySQLbase.java.

  {
        String query1 = "SELECT greater FROM results WHERE axis='"+axis;
        String query2 = "SELECT greater FROM results WHERE axis='"+axis;
        boolean result = true;
        
        query1 += "' AND idMedia1="+idMedia1+" AND idMedia2="+idMedia2+" ;";
        query2 += "' AND idMedia1="+idMedia2+" AND idMedia2="+idMedia1+" ;";
        
        try
        {
              Statement stmt = this.conSQL.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
              ResultSet rs;
              rs = stmt.executeQuery(query1);
              if (rs.next())
                    result = (rs.getInt("greater") == 1 );
              else
              {
                    rs = stmt.executeQuery(query2);
                    if (rs.next())
                          result = (rs.getInt("greater") == 2 );
              }
              rs.close();
              stmt.close();
        }
        catch(Exception e)  { e.printStackTrace(); }
        return result;
  }

void myDataBases.MySQLbase.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.

Definition at line 376 of file MySQLbase.java.

  {
        try
        {
              Calendar cal = Calendar.getInstance();
              String dumpName = "DBcps_" + cal.get(Calendar.DATE) + "-" + cal.get(Calendar.MONTH)
                                          + "-" + cal.get(Calendar.YEAR) + ".sql";
              String [] cmd = { "../data/mysql/dump.sh",this.login,this.passwd, dumpName};
              Runtime.getRuntime().exec(cmd);
        }
        catch(Exception e)  { e.printStackTrace(); }
  }

void myDataBases.MySQLbase.insertHardDataInSQL ( List< 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.

Definition at line 287 of file MySQLbase.java.

  {  
        for (int i=0; i< entries.size(); i++)
        {
              String[] entry = entries.get(i);
              String query = this.queryHardDataStub;
              Integer endLoop;
              
              // Determining the number of fields to write in the SQL request
              if (entry.length < this.columnsHardDataSQL.length )
                    endLoop = entry.length;
              else
                    endLoop = this.columnsHardDataSQL.length;
              
            // Writing each field but the last one
              for (int j=0; j<endLoop; j++)
              {
                    if (this.isStringHardData[j])     // if it is a string, we print it between "'"
                          query += "'" + entry[j] + "'";
                    else      // if it is a number
                          if (entry[j].length() == 0) // if it is null, we write 0
                                query += "0";
                          else      // else we write it
                                query += entry[j];
                    if (j == endLoop-1)   // are we at the end of the request ?
                          query += ") ;\n";     // if yes, we close it with a parenthesis and a semicolon
                    else
                          query += ", ";  // otherwise, we just a put a comma
              }
              try
              {
                    Statement stmt = this.conSQL.createStatement();
                    stmt.executeUpdate(query);
              }
              catch(Exception e)  { e.printStackTrace(); }
        }
  }

void myDataBases.MySQLbase.insertResultsInSQL	(	List< 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.

Definition at line 339 of file MySQLbase.java.

  {  
        for (int i=0; i<entries.size(); i++)
        {
              String[] entry = entries.get(i);
              String query = this.queryResultsStub;
              // adding the content of the entry in the query
              for (int j=0; j<entry.length-1; j++)
                    if (this.isStringResults[j])
                          query += "'" + entry[j] + "', ";
                    else
                          query += entry[j] + ", ";
              // adding the "greater" entry
              query += entry[3] + ", " ;
              // adding the names of the media considered
              query += "'" + dbNames.getMedia( Integer.parseInt(entry[1]) ) + "', '"
                              + dbNames.getMedia( Integer.parseInt(entry[2]) )  + "') ;" ;
              try
              {
                    //System.out.println(query);
                    Statement stmt = this.conSQL.createStatement();
                    stmt.executeUpdate(query);
              } catch(Exception e)  { e.printStackTrace(); }
        }
  }

static Boolean myDataBases.MySQLbase.testConnection	(	LogWriter	log,
		String	testPasswd
	)		`[static]`

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 myShell.LogWriter instance.

If the connection to the database doesn't work, you might try replacing "jdbc:mysql://localhost:3306/" by "jdbc:mysql://127.0.0.1:3306/"

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.

Definition at line 162 of file MySQLbase.java.

  {
        Boolean connected = false;
        Map<String,String> config = Interpreter.getConfig();
        try
        {
            Class.forName("com.mysql.jdbc.Driver");   //Register the JDBC driver for MySQL.
            DriverManager.getConnection( "jdbc:mysql://localhost:3306/" + config.get("sqlBase") ,
                    config.get("sqlUser"), testPasswd);
            connected = true;
        }
        catch(Exception e)
        {
              e.printStackTrace();
              connected = false;
        }
        if (connected)
                  log.append("connection with the database works.\n");
        else
                  log.append("ERROR : A problem was encountered while connecting with the database.\n" +
                  " Interrupting.");
        return connected;
  }