0.8
Sorting media using crowdsourcing.   
Doxygen
LIRIS
Public Member Functions | Static Public Member Functions | Private Attributes

myDataBases.MySQLbase Class Reference

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:

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:
adrThe address of the MySQL database. It is the value to give to the attribute adr.
loginThe login of the database. It is the value to give to the attribute login.
passwdThe password of the database. It is the value to give to the attribute psswd.
fieldsHardDataThe fields of the hit table.
fieldsTypeThe 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:
idMedia1The identifier of the first media being compared.
idMedia2The identifier of the second media to be compared.
axisThe 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:
idMedia1The identifier of the first media being compared.
idMedia2The identifier of the second media to be compared.
axisThe 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:
entriesA 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:
entriesA list containing the results after their treatment.
dbNamesThe 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.

Parameters:
logThe LogWriter instance used to inform the user.
testPasswdThe 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;
  }

Member Data Documentation

String myDataBases.MySQLbase.adr [private]

The url where the database used is.

Definition at line 43 of file MySQLbase.java.

The labels of the columns in the hit table of the MySQL database.

Definition at line 61 of file MySQLbase.java.

String [] myDataBases.MySQLbase.columnsResultsSQL = { "axis", "idMedia1", "idMedia2", "greater", "nameMedia1", "nameMedia2" } [private]

The labels of the columns in the results table of the MySQL database.

Definition at line 76 of file MySQLbase.java.

Connection myDataBases.MySQLbase.conSQL [private]

The connection to the MySQL database.

Definition at line 55 of file MySQLbase.java.

Contains true if the content of the corresponding label in the hit table is a string and therefore requires quotation marks.

Definition at line 66 of file MySQLbase.java.

Boolean [] myDataBases.MySQLbase.isStringResults = { true, false, false, false, true, true} [private]

The same as isStringHardData, but for the results table.

Definition at line 80 of file MySQLbase.java.

String myDataBases.MySQLbase.login [private]

A login enabling reading an writing access to the DBcps database.

Definition at line 47 of file MySQLbase.java.

String myDataBases.MySQLbase.passwd [private]

The password matching the login used.

Definition at line 51 of file MySQLbase.java.

The stub common to all queries inserting new hard data in the hit table of the SQL database.

Definition at line 70 of file MySQLbase.java.

The stub common to all queries inserting results in the results table of the SQL database.

Definition at line 84 of file MySQLbase.java.


The documentation for this class was generated from the following file:
 All Classes Namespaces Files Functions Variables