ModSQL
Class Select

java.lang.Object
  |
  +--ModSQL.Select

All Implemented Interfaces:

Function, FunctionProvider, Query, RowConstructor, Table

final class Select

extends Object

implements RowConstructor, Table, FunctionProvider, Query

Class to parse and execute an SQL SELECT statement. This class implements Table, RowConstructor and Query so that it can behave as any one of these interfaces dictates, although some queries may not be compatable with some of these behaviours.

In the case of Query, the constuctor does the parsing, and execute will call optimize and may actually execute the query too.

In the case of RowConstructor or Table, the constructor still does the parsing, but the user is expected to explicitly call optimize before attempting to evaluate rows.

Author:

chris.studholme@utoronto.ca

Inner Class Summary

(package private) class	Select.GroupByList Simple list of ColumnValue's in a SELECT query's GROUP BY list.
(package private) class	Select.OrderByComparator Comparator for sorting rows when evaluating ORDER BY.

Field Summary

private boolean	aggregate Are we grouping or aggregating?
private String[]	column_names Names of columns.
private Object[]	column_values Column values for current row.
private Function[]	columns Array of columns to select.
private int	current_row Current row in cached rows.
private boolean	distinct True if SELECT DISTINCT.
private HashSet	distinct_set HashSet used to detect and remove duplicate rows.
private Function[]	group_by Functions to GROUP BY.
private String[]	group_by_names Names of columns to GROUP BY.
private Function	having HAVING clause used to select groups of interest.
private DatabaseManager	manager Table manager used to open tables.
private Object[]	null_row Array of null references to return as a null row.
private boolean	optimized Has optimize been called?
private Function[]	order_by List of functions to ORDER BY.
private boolean[]	order_by_dir ORDER BY order: true for ascending, false for decending.
private boolean[]	order_by_dup Flag indicating that order_by entry is dup of columns entry.
private int[]	order_by_indices Indices into columns array.
private TableReader	reader Reader to get database rows from.
private List	rows Cached rows.
private int	start_group Index in columns of start of GROUP BY.
private int	start_having Index in columns of start of HAVING.
private int	start_order Index in columns of start of ORDER BY.
private int	start_where Index in columns of start of WHERE.
private String[]	table_aliases Alternate names for tables (aliases).
private DatabaseTable[]	table_list List of tables from which rows are read.
static Boolean	TRUE_VALUE Constant true value as Boolean object.
private Function	where WHERE clause used to select rows of interest.

Fields inherited from interface ModSQL.Function

MATCH_BEGINS, MATCH_EQU, MATCH_GT, MATCH_GTE, MATCH_LT, MATCH_LTE, MATCH_NE

Constructor Summary

protected

Select(StreamTokenizer tokenizer, DatabaseManager manager) Contructor to parse query.

Method Summary

void	addParameter(Function item) Adds a value (function) to the list of parameters maintained by this function.
void	beforeFirst() Reset the table to before the first row.
void	close() Close all tables.
Object	evaluate(boolean aggregate) Evaluate parameters and compute the function.
Object	evaluate(int match_op, Object match_value) Same as evaluate(), except that a specific result is desired.
void	evaluateOrder(int index, int order) Specify the order in which the parameters should be evaluated.
Object[]	evaluateRow(boolean aggregate) Evaluate each column of the row and return the results as an array of length equal to the value returned by numColumns().
int	execute() Optimizes query in preperation for calls to next().
protected void	executeAggregate() Execute simple aggregate query.
protected void	executeAndCacheResults() Execute query and store results in rows array.
protected void	executeBasic() Execute basic queries (no GROUP BY and no aggregates).
protected void	executeGroupBy() Execute queries involving grouping (ie.
protected void	finalize() Close all tables.
int	getColumnCount() Returns the number of columns in the table.
String	getColumnName(int column) Get the name of a column.
int	getMaxResultSize() This version of getMaxResultSize() can only be used if the query returns only one column.
int	getMaxResultSize(int column) Return the maximum number of characters String values in the specified column will have.
Object	getObject(int column) Get the value of a column in the current row as a Java object.
Function	getParameter(int index) Get a particular parameter.
int	getParameterCount() Get the number pf parameters currently supplied to this function.
Object[]	getRow() Get the current row as an array of Objects.
long	getRowCount() Return number of rows if known.
int	getSQLType() This version of getSQLType() can only be used if the query returns only one column.
int	getSQLType(int column) Return the SQL type of the specified column.
boolean	isAggregate() Select queries are never aggregate.
boolean	isConstant() A query is non-constant if it is a subquery that references columns from its parent.
Function	lookupFunction(String description) Lookup description in column_names array and return matching columns entry if match is found.
boolean	next() The table is initially positioned before its first row; the first call to next makes the first row the current row; the second call makes the second row the current row, etc.
void	optimize() Optimize query and prepare for execution.
private void	parseSelect(StreamTokenizer tokenizer) Parse entire SELECT query.
void	registerWith(Object o) Most objects passed to this method will be passed on to all contained function objects.
void	reset() In the case of aggregate functions, this method resets the state of the function to the state it was in before the first call to evaluate().
void	setupTableReader() Create and setup TableReader object (with no parent).
void	setupTableReader(TableReader parent) Create and setup TableReader object.
String	toString() Returns human-readable string version of query (with surrounding brackets).
String	toString(boolean with_brackets) Returns human-readable string version of query.

Methods inherited from class java.lang.Object

, clone, equals, getClass, hashCode, notify, notifyAll, registerNatives, wait, wait, wait

Field Detail

TRUE_VALUE

public static final Boolean TRUE_VALUE

Constant true value as Boolean object.

distinct

private boolean distinct

True if SELECT DISTINCT.

distinct_set

private HashSet distinct_set

HashSet used to detect and remove duplicate rows.

columns

private Function[] columns

Array of columns to select.

column_names

private String[] column_names

Names of columns.

column_values

private Object[] column_values

Column values for current row.

table_list

private DatabaseTable[] table_list

List of tables from which rows are read.

table_aliases

private String[] table_aliases

Alternate names for tables (aliases).

where

private Function where

WHERE clause used to select rows of interest.

group_by

private Function[] group_by

Functions to GROUP BY.

group_by_names

private String[] group_by_names

Names of columns to GROUP BY.

having

private Function having

HAVING clause used to select groups of interest.

order_by

private Function[] order_by

List of functions to ORDER BY.

order_by_dir

private boolean[] order_by_dir

ORDER BY order: true for ascending, false for decending.

order_by_indices

private int[] order_by_indices

Indices into columns array.

order_by_dup

private boolean[] order_by_dup

Flag indicating that order_by entry is dup of columns entry.

start_order

private int start_order

Index in columns of start of ORDER BY.

start_having

private int start_having

Index in columns of start of HAVING.

start_group

private int start_group

Index in columns of start of GROUP BY.

start_where

private int start_where

Index in columns of start of WHERE.

manager

private transient DatabaseManager manager

Table manager used to open tables.

reader

private transient TableReader reader

Reader to get database rows from.

aggregate

private boolean aggregate

Are we grouping or aggregating?

optimized

private boolean optimized

Has optimize been called?

rows

private List rows

Cached rows.

current_row

private int current_row

Current row in cached rows.

null_row

private Object[] null_row

Array of null references to return as a null row.

Constructor Detail

Select

protected Select(StreamTokenizer tokenizer,
                 DatabaseManager manager)
          throws SQLException,
                 IOException

Contructor to parse query.

Parameters:

tokenizer - StreamTokenizer that SQL tokens should be read from

manager - manager to use when looking up tables

Throws:

SQLException - if an error occurs

IOException - if there is a problem reading the query

Method Detail

close

public void close()

Close all tables.

Specified by:

close in interface Query

Following copied from interface: ModSQL.Query

Throws:

SQLException - if an error occurs

finalize

protected void finalize()

Close all tables.

Overrides:

finalize in class Object

parseSelect

private void parseSelect(StreamTokenizer tokenizer)
                  throws SQLException,
                         IOException

Parse entire SELECT query.

Parameters:

tokenizer - StreamTokenizer that SQL tokens should be read from

Throws:

SQLException - if an error occurs

IOException - if there is a problem reading the query

getParameterCount

public int getParameterCount()

Get the number pf parameters currently supplied to this function.

Specified by:

getParameterCount in interface Function

Returns:

number of parameters

getParameter

public Function getParameter(int index)

Get a particular parameter. Always throws an exception as parameters are not supported.

Specified by:

getParameter in interface Function

Parameters:

index - index of parameter to get

Returns:

parameter (function)

Throws:

IndexOutOfBoundsException - always

addParameter

public void addParameter(Function item)
                  throws SQLException

Adds a value (function) to the list of parameters maintained by this function. Always throws an exception as parameters are not supported.

Specified by:

addParameter in interface Function

Parameters:

item - function to add

Throws:

SQLException - always

evaluateOrder

public void evaluateOrder(int index,
                          int order)

Specify the order in which the parameters should be evaluated. Always throws an exception as parameters are not supported.

Specified by:

evaluateOrder in interface Function

Parameters:

index - index of parameter

order - number indicating order in which parameter is evaluated

Throws:

IndexOutOfBoundsException - always

lookupFunction

public Function lookupFunction(String description)

Lookup description in column_names array and return matching columns entry if match is found. This method returns null if no match was found.

Specified by:

lookupFunction in interface FunctionProvider

Parameters:

description - name of SELECT column to look for

Returns:

Function object (possibly null)

registerWith

public void registerWith(Object o)
                  throws SQLException

Most objects passed to this method will be passed on to all contained function objects. Two classes of objects are treated differntly. Select objects are blocked by this method, and TableReader objects initiate setup of the TableReader needed by this object with the passed TableReader as a parent.

Specified by:

registerWith in interface Function

Parameters:

o - object to register with

Throws:

SQLException - if a database error occurs

setupTableReader

public void setupTableReader()
                      throws SQLException

Create and setup TableReader object (with no parent).

Throws:

SQLException - if an error occurs

setupTableReader

public void setupTableReader(TableReader parent)
                      throws SQLException

Create and setup TableReader object.

Parameters:

parent - parent TableReader object

Throws:

SQLException - if an error occurs

optimize

public void optimize()
              throws SQLException

Optimize query and prepare for execution.

Specified by:

optimize in interface Function

Throws:

SQLException - if an error occurs

toString

public String toString()

Returns human-readable string version of query (with surrounding brackets).

Overrides:

toString in class Object

Returns:

String representation of query

toString

public String toString(boolean with_brackets)

Returns human-readable string version of query.

Specified by:

toString in interface Table

Parameters:

with_brackets - true to include surrounding brackets

Returns:

String representation of query

isAggregate

public boolean isAggregate()

Select queries are never aggregate.

Specified by:

isAggregate in interface Function

Returns:

false

isConstant

public boolean isConstant()

A query is non-constant if it is a subquery that references columns from its parent.

Specified by:

isConstant in interface Function

Returns:

true if the query results do not depend on a parent TableReader

getColumnCount

public int getColumnCount()

Returns the number of columns in the table. This value should be greater than zero.

Specified by:

getColumnCount in interface RowConstructor

Returns:

number of columns in table

getRowCount

public long getRowCount()
                 throws SQLException

Return number of rows if known.

Specified by:

getRowCount in interface Table

Returns:

number of rows or -1 if count is not known

getColumnName

public String getColumnName(int column)

Get the name of a column. This name is its given name, or the fully qualified name of a table column is the column is simple that.

Specified by:

getColumnName in interface Table

Parameters:

column - column number (starting from zero)

Returns:

name of column (or null if column has no name)

getSQLType

public int getSQLType(int column)
               throws SQLException

Return the SQL type of the specified column.

Specified by:

getSQLType in interface RowConstructor

Parameters:

column - column number (starting from zero)

Returns:

SQL type of data to be returned

Throws:

SQLException - if an error occurs

getSQLType

public int getSQLType()
               throws SQLException

This version of getSQLType() can only be used if the query returns only one column. In this case, calling this method is equivalent to calling getSQLType(0).

Specified by:

getSQLType in interface Function

Returns:

SQL type of data to be returned

Throws:

SQLException - if an error occurs

getMaxResultSize

public int getMaxResultSize(int column)
                     throws SQLException

Return the maximum number of characters String values in the specified column will have. If the column is not of String type or if the maximum length is not known, -1 should be returned.

Specified by:

getMaxResultSize in interface RowConstructor

Parameters:

column - column number (starting from zero)

Returns:

maximum size of String returned or -1 if unknown

Throws:

SQLException - if an error occurs

getMaxResultSize

public int getMaxResultSize()
                     throws SQLException

This version of getMaxResultSize() can only be used if the query returns only one column. In this case, calling this method is equivalent to calling getMaxResultSize(0).

Specified by:

getMaxResultSize in interface Function

Returns:

maximum size of String returned or -1 if unknown

Throws:

SQLException - if an error occurs

executeAggregate

protected void executeAggregate()
                         throws SQLException,
                                EndOfTable

Execute simple aggregate query. That is, SELECT columns are aggregates, there is no GROUP BY, and neither ORDER BY nor DISTINCT have an effect.

Throws:

SQLException - if an error occurs

EndOfTable - if the table is advanced beyond that last row

executeGroupBy

protected void executeGroupBy()
                       throws SQLException,
                              EndOfTable

Execute queries involving grouping (ie. have a GROUP BY clause).

Throws:

SQLException - if an error occurs

EndOfTable - if the table is advanced beyond that last row

executeBasic

protected void executeBasic()
                     throws SQLException,
                            EndOfTable

Execute basic queries (no GROUP BY and no aggregates).

Throws:

SQLException - if an error occurs

EndOfTable - if the table is advanced beyond that last row

executeAndCacheResults

protected void executeAndCacheResults()
                               throws SQLException,
                                      EndOfTable

Execute query and store results in rows array.

Throws:

SQLException - if an error occurs

EndOfTable - if the table is advanced beyond that last row

execute

public int execute()
            throws SQLException

Optimizes query in preperation for calls to next(). May actually execute query. From Query interface.

Specified by:

execute in interface Query

Returns:

-1 to indicate that a result set is needed

Throws:

SQLException - if a database-access error occurs

reset

public void reset()

In the case of aggregate functions, this method resets the state of the function to the state it was in before the first call to evaluate().

Specified by:

reset in interface Function

Following copied from interface: ModSQL.Function

Throws:

SQLException - if an error occurs

beforeFirst

public void beforeFirst()
                 throws SQLException

Reset the table to before the first row. After a call to this method, next() should advance to the first row in the table.

Specified by:

beforeFirst in interface Table

Throws:

SQLException - if a database-access error occurs

next

public boolean next()
             throws SQLException,
                    EndOfTable

The table is initially positioned before its first row; the first call to next makes the first row the current row; the second call makes the second row the current row, etc.

Specified by:

next in interface Table

Returns:

true if the new current row is valid; false if there are no more rows

Throws:

SQLException - if an error occurs

EndOfTable - if thrown by a parameter

getRow

public Object[] getRow()
                throws SQLException

Get the current row as an array of Objects.

Specified by:

getRow in interface Table

Returns:

array of objects or null if current row is not valid

Throws:

SQLException - if a database-access error occurs

getObject

public Object getObject(int column)
                 throws SQLException

Get the value of a column in the current row as a Java object. This method will throw an exception if the current row is not valid.

Specified by:

getObject in interface Table

Parameters:

column - column number (starting from zero)

Returns:

an Object holding the column value

Throws:

SQLException - if an error occurs

evaluateRow

public Object[] evaluateRow(boolean aggregate)
                     throws SQLException,
                            EndOfTable

Evaluate each column of the row and return the results as an array of length equal to the value returned by numColumns(). If the row is the result of a subquery with no rows, the result will be an array of NULL values.

Specified by:

evaluateRow in interface RowConstructor

Parameters:

aggregate - final evaluation of aggregates

Returns:

array of objects

Throws:

SQLException - if an error occurs

EndOfTable - if thrown by a parameter

evaluate

public Object evaluate(boolean aggregate)
                throws SQLException,
                       EndOfTable

Evaluate parameters and compute the function. The parameters should be evaluated in the order indicated with evaluateOrder() for optimal query efficiency.

If the function is an aggregate, this method may return null to indicate that the aggregate is not yet available. The method will be called later with the aggregate parameter set to true to indicate that the final aggregate value is required. If the function is non-constant and non-aggregate, it may throw an exception if the aggregate parameter is true. New rows should not be read from the database if aggregate is set to true.

Specified by:

evaluate in interface Function

Parameters:

aggregate - true to return final aggregate value

Returns:

result object

Throws:

SQLException - if an error occurs

EndOfTable - if thrown by a parameter

evaluate

public Object evaluate(int match_op,
                       Object match_value)
                throws SQLException,
                       EndOfTable

Same as evaluate(), except that a specific result is desired. If the desired result can only be obtained when a particular parameter evaluates to a particular value, the evaulate() method for that parameter should be passed that particular value in the hopes that it will be returned. Otherwise, if knowledge of a desired result does not sufficiently constrain the parameters, this method can simply call the evaluate() method above and return what it returns.

The match_op parameter will be one of the MATCH_* constants. The match condition can be considered a relation of the form: desired_value match_op match_value. For example, desired_value MATCH_LT match_value implies desired_value < match_value.

Specified by:

evaluate in interface Function

Parameters:

match_op - how desired value is matched

match_value - value to match to

Returns:

result object

Throws:

SQLException - if an error occurs

EndOfTable - if thrown by a parameter