standup.sql
Class SQLUtils

java.lang.Object
  extended by standup.sql.SQLUtils

public class SQLUtils
extends Object

This is the class that handles the connection to the PostgreSQL database. It provides a range of static methods for posting queries and retrieving results using the JDBC API. Note that no knowledge of the JDBC API is required to use these methods.

Author:
Ruli Manurung

Field Summary
private static PreparedStatement cpsSelectIdFromLexiconWhereOrtho
           
private static PreparedStatement cpsSelectIdFromLexiconWhereWord
           
private static PreparedStatement cpsSelectIdFromWordformWhereOrtho
           
private static Connection db
          Connection object to the Postgres database.
private static boolean initialized
          Boolean value indicating whether or not a connection to the database has been initialized.
private static String masterSQLParamsFile
           
 
Constructor Summary
SQLUtils()
           
 
Method Summary
(package private) static boolean callBooleanFunction(CallableStatement cs, String arg)
           
(package private) static float callFloatFunction(CallableStatement cs, String arg)
           
(package private) static int callIntFunction(CallableStatement cs, String arg)
           
(package private) static long callLongFunction(CallableStatement cs, String arg)
           
(package private) static ResultSet callResultSetFunction(CallableStatement cs, String arg)
           
(package private) static String callStringFunction(CallableStatement cs, String arg)
           
private static Set<standup.authoring.dbbuild.ProtoLexeme> convertLexemeIDStringListToSetProtoLexeme(Collection<String> lexemeIDs)
           
private static Set<standup.authoring.dbbuild.ProtoWordForm> convertWordFormIDStringListToSetProtoWordForm(Collection<String> wordFormIDs)
           
static String createTemporaryLexemeIDSetTable(LexemeSet ls)
          Creates a temporary table in the SQL database, containing a single 'id' column, and populated by all the Lexeme IDs found in the given LexemeSet.
static void deleteTable(String tableName)
          This function deletes the SQL table with the given table name.
private static List<String> doCompoundSpell(List<WordString> ws)
           
private static List<String> doCompoundSpell(List<WordString> ws, String prefix)
           
static int executeArbitraryStatement(String stmtString)
           
static int executeArbitraryStatementQuietly(String stmtString)
           
static int[] executeArbitraryStatements(List<String> stmtStrings)
          Execute a batch of SQL statements (e.g.
static ResultSet executeQuery(String query)
           
private static PreparedStatement getCPSSelectIdFromLexiconWhereOrtho()
           
private static PreparedStatement getCPSSelectIdFromLexiconWhereWord()
           
private static PreparedStatement getCPSSelectIdFromWordformWhereOrtho()
           
static Statement getCursorBasedStatement()
          This returns the offset-th row in the SQL table with name tableName, which must be a table of schema instantiations whose columns are those specified by schemaVars.
static Statement getCursorBasedStatement(int fetchSize)
          Returns a Statement that will 'fetch' rows using a cursor with given fetchSize.
static List<List<Keyword>> getListNKeywordListFromQuery(int n, String query)
          This executes the SQL query provided as a parameter, and assumes that the result of the query is an n-column table of Strings, which is returned as a List of List of Keywords.
static List<List<String>> getListNStringListFromQuery(int n, String query)
          This executes the SQL query provided as a parameter, and assumes that the result of the query is an n-column table of Strings, which is returned as a List of List of Strings.
static PreparedStatement getPreparedStatement(String query)
          This method returns a PreparedStatement given the query.
private static List<String> getPreparedStatementResult(PreparedStatement cps, String value)
          Executes the given PreparedStatement using the given parameter value and returns the query results as a List<String>, or null if an error occurred.
private static Set<String> getPreparedStatementResultUnion(PreparedStatement cps, List<String> values)
          Executes the given PreparedStatement using the different parameters given in values and returns the UNION of all the query results as a Set<String>.
static LexemeSet getSpelledCompiledLexemes(WordForm w)
          Returns all Lexemes spelt with the given WordForm.
static LexemeSet getSpelledCompiledLexemes(WordSequence w)
          Returns all Lexemes with the given spelling.
static Set<WordForm> getSpelledCompiledWordForms(WordSequence w)
           
static Set<standup.authoring.dbbuild.ProtoLexeme> getSpelledProtoLexemes(standup.authoring.dbbuild.ProtoWordForm w)
           
static Set<standup.authoring.dbbuild.ProtoLexeme> getSpelledProtoLexemes(WordSequence w)
           
static Set<standup.authoring.dbbuild.ProtoWordForm> getSpelledProtoWordForms(WordSequence w)
           
(package private) static Statement getStatement()
          Returns a Statement.
static List<String> getStringListFromQuery(String query)
          This executes the SQL query provided as a parameter, and assumes that the result of the query is a 1-column table of strings, which is returned as a List of Strings.
private static List<String> getStringListFromResultSet(ResultSet rs)
          Builds a List<String> from the given ResultSet, which is assumed to contain String values for its first column.
static int getTableCount(String tableName)
          This returns the number of rows contained in the SQL table with the given table name.
static void initialize()
          This initializes the SQL database connection using the arguments supplied by CommandLineArguments.
private static void initialize(String[] params)
          A method of initializing the database connection by providing it with an array of Strings that contains the required parameters.
static void initialize(URL configURL)
          This initializes the SQL database connection using the arguments supplied in the given URL.
static boolean isInitialized()
           
(package private) static CallableStatement prepareFunction(String funcName, int arity)
          This method calls an SQL function that takes a String argument and returns the single return value as a String.
(package private) static CallableStatement prepareFunction(String funcName, int returnType, int arity)
           
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

masterSQLParamsFile

private static String masterSQLParamsFile

db

private static Connection db
Connection object to the Postgres database. Must be established during initialization.


initialized

private static boolean initialized
Boolean value indicating whether or not a connection to the database has been initialized.


cpsSelectIdFromWordformWhereOrtho

private static PreparedStatement cpsSelectIdFromWordformWhereOrtho

cpsSelectIdFromLexiconWhereOrtho

private static PreparedStatement cpsSelectIdFromLexiconWhereOrtho

cpsSelectIdFromLexiconWhereWord

private static PreparedStatement cpsSelectIdFromLexiconWhereWord
Constructor Detail

SQLUtils

public SQLUtils()
Method Detail

initialize

public static void initialize()
                       throws STANDUPSQLException
This initializes the SQL database connection using the arguments supplied by CommandLineArguments. It takes whatever arguments it can, and uses the defaults contained in 'masterSQLParamsFile' for whatever is not supplied.

Throws:
STANDUPSQLException

initialize

public static void initialize(URL configURL)
                       throws STANDUPSQLException
This initializes the SQL database connection using the arguments supplied in the given URL.

Throws:
STANDUPSQLException

initialize

private static void initialize(String[] params)
                        throws STANDUPSQLException
A method of initializing the database connection by providing it with an array of Strings that contains the required parameters.

Parameters:
params - An array of 4 Strings
  • The JDBC driver identifier string
  • The JDBC URL for the STANDUP database
  • The Postgres user account
  • The Postgres user password
Throws:
STANDUPSQLException

isInitialized

public static boolean isInitialized()

prepareFunction

static CallableStatement prepareFunction(String funcName,
                                         int arity)
This method calls an SQL function that takes a String argument and returns the single return value as a String. Examples of such functions are the lexical accessor functions defined in Chapter 6.4 of the technical specification which have been implemented as pl/pgsql functions. Indeed, callStringFunction(String,String) is called by 5 'wrapper' functions that duplicate the SQL lexical accessor functions in the Java backend:

Parameters:
funcName - The name of the SQL (i.e. pl/pgsql) function, e.g. 'getwordformortho'
arg - The String value to be passed as an argument (most probably the id field of a Lexeme or a WordForm)
Returns:
The String value returned by the SQL function.

prepareFunction

static CallableStatement prepareFunction(String funcName,
                                         int returnType,
                                         int arity)

callResultSetFunction

static ResultSet callResultSetFunction(CallableStatement cs,
                                       String arg)

callStringFunction

static String callStringFunction(CallableStatement cs,
                                 String arg)

callIntFunction

static int callIntFunction(CallableStatement cs,
                           String arg)

callBooleanFunction

static boolean callBooleanFunction(CallableStatement cs,
                                   String arg)

callLongFunction

static long callLongFunction(CallableStatement cs,
                             String arg)

callFloatFunction

static float callFloatFunction(CallableStatement cs,
                               String arg)

getPreparedStatement

public static PreparedStatement getPreparedStatement(String query)
This method returns a PreparedStatement given the query. the calling method is responsible for using it appropriately!


getStringListFromResultSet

private static List<String> getStringListFromResultSet(ResultSet rs)
Builds a List<String> from the given ResultSet, which is assumed to contain String values for its first column. Any other columns are ignored.

Parameters:
rs -
Returns:

getStringListFromQuery

public static List<String> getStringListFromQuery(String query)
This executes the SQL query provided as a parameter, and assumes that the result of the query is a 1-column table of strings, which is returned as a List of Strings.

Parameters:
query - the SQL query to be executed
Returns:
A List of Strings containing the values returned by the query.

getListNStringListFromQuery

public static List<List<String>> getListNStringListFromQuery(int n,
                                                             String query)
This executes the SQL query provided as a parameter, and assumes that the result of the query is an n-column table of Strings, which is returned as a List of List of Strings.

Parameters:
query - the SQL query to be executed
Returns:
A List of List of Strings containing the values returned by the query.

getListNKeywordListFromQuery

public static List<List<Keyword>> getListNKeywordListFromQuery(int n,
                                                               String query)
This executes the SQL query provided as a parameter, and assumes that the result of the query is an n-column table of Strings, which is returned as a List of List of Keywords.

Parameters:
query - the SQL query to be executed
Returns:
A List of List of Keywords containing the values returned by the query.
See Also:
Keyword.createKeyword(String)

getStatement

static Statement getStatement()
                       throws SQLException
Returns a Statement.

Returns:
Throws:
SQLException

getCursorBasedStatement

public static Statement getCursorBasedStatement()
This returns the offset-th row in the SQL table with name tableName, which must be a table of schema instantiations whose columns are those specified by schemaVars. It also removes/deletes the retrieved row from the table.

Parameters:
tableName - The name of the SQL table containing the schema instantiations (probably a temporary one!). The general contract is that it contains n+m columns, where n = the number of schema variables to be instantiated and m = the number of phonetic similarity values.
schemaVars - The name of the schema variables to be instantiated. These must match the colum names in the specified SQL table.
phonSimVars - The name of the phonetic similarity variables to be extracted. These must match the colum names in the specified SQL table.
offset - The index of the row to be returned
Returns:
An array of two Lists - the first is a list of the schema instantiations, and the second is a list of the phonetic similarity values.

getCursorBasedStatement

public static Statement getCursorBasedStatement(int fetchSize)
Returns a Statement that will 'fetch' rows using a cursor with given fetchSize. Note that it sets autocommit to false, so make sure to either turn it on again!

Parameters:
fetchSize -
Returns:

executeQuery

public static ResultSet executeQuery(String query)

deleteTable

public static void deleteTable(String tableName)
                        throws STANDUPSQLException
This function deletes the SQL table with the given table name. It should be called when a created temporary table is no longer needed (NOTE: in theory, the SQL server will delete all temporary tables anyway once the connection is closed, but it doesn't hurt to do manual garbage collection...).

Parameters:
tableName -
Throws:
STANDUPSQLException

getTableCount

public static int getTableCount(String tableName)
                         throws STANDUPSQLException
This returns the number of rows contained in the SQL table with the given table name.

Parameters:
tableName -
Returns:
Throws:
STANDUPSQLException

executeArbitraryStatements

public static int[] executeArbitraryStatements(List<String> stmtStrings)
Execute a batch of SQL statements (e.g. INSERTS, UPDATES, DELETES, etc.). Note that if any of the statements fails, it may fail to execute the entire list of statements!


executeArbitraryStatement

public static int executeArbitraryStatement(String stmtString)

executeArbitraryStatementQuietly

public static int executeArbitraryStatementQuietly(String stmtString)

getSpelledCompiledWordForms

public static Set<WordForm> getSpelledCompiledWordForms(WordSequence w)

getSpelledProtoWordForms

public static Set<standup.authoring.dbbuild.ProtoWordForm> getSpelledProtoWordForms(WordSequence w)

getCPSSelectIdFromWordformWhereOrtho

private static PreparedStatement getCPSSelectIdFromWordformWhereOrtho()

getSpelledCompiledLexemes

public static LexemeSet getSpelledCompiledLexemes(WordSequence w)
Returns all Lexemes with the given spelling. Requires access to the SQL lexical database.

Parameters:
w - The required spelt form
Returns:
A LexemeSet of all CompiledLexemes with the given spelling

getSpelledProtoLexemes

public static Set<standup.authoring.dbbuild.ProtoLexeme> getSpelledProtoLexemes(WordSequence w)

getCPSSelectIdFromLexiconWhereOrtho

private static PreparedStatement getCPSSelectIdFromLexiconWhereOrtho()

getSpelledCompiledLexemes

public static LexemeSet getSpelledCompiledLexemes(WordForm w)
Returns all Lexemes spelt with the given WordForm. Requires access to the SQL lexical database.

Parameters:
w -
Returns:

getSpelledProtoLexemes

public static Set<standup.authoring.dbbuild.ProtoLexeme> getSpelledProtoLexemes(standup.authoring.dbbuild.ProtoWordForm w)

getCPSSelectIdFromLexiconWhereWord

private static PreparedStatement getCPSSelectIdFromLexiconWhereWord()

getPreparedStatementResult

private static List<String> getPreparedStatementResult(PreparedStatement cps,
                                                       String value)
Executes the given PreparedStatement using the given parameter value and returns the query results as a List<String>, or null if an error occurred. Assumes that cps takes one String argument, and the query result is a one-column table of Strings.

Parameters:
cps -
value -
Returns:

getPreparedStatementResultUnion

private static Set<String> getPreparedStatementResultUnion(PreparedStatement cps,
                                                           List<String> values)
Executes the given PreparedStatement using the different parameters given in values and returns the UNION of all the query results as a Set<String>. Assumes that cps takes one String argument, and the query result is a one-column table of Strings. Note that since it uses a set union, if any of the individual results contains duplicate values these will be deleted as well.

Parameters:
cps -
values -
Returns:

convertLexemeIDStringListToSetProtoLexeme

private static Set<standup.authoring.dbbuild.ProtoLexeme> convertLexemeIDStringListToSetProtoLexeme(Collection<String> lexemeIDs)

convertWordFormIDStringListToSetProtoWordForm

private static Set<standup.authoring.dbbuild.ProtoWordForm> convertWordFormIDStringListToSetProtoWordForm(Collection<String> wordFormIDs)

doCompoundSpell

private static List<String> doCompoundSpell(List<WordString> ws)

doCompoundSpell

private static List<String> doCompoundSpell(List<WordString> ws,
                                            String prefix)

createTemporaryLexemeIDSetTable

public static String createTemporaryLexemeIDSetTable(LexemeSet ls)
                                              throws STANDUPSQLException
Creates a temporary table in the SQL database, containing a single 'id' column, and populated by all the Lexeme IDs found in the given LexemeSet. This is required for the instantiation of a standup.joke.InstantiationConstraintLexicon. The resulting table uses the single 'id' column as its primary key.

Parameters:
ls - The LexemeSet containing the values for the temporary table.
Returns:
the name of the created temporary SQL table.
Throws:
STANDUPSQLException