Sql.cal

* Redistribution and use in source and binary forms, with or without

* modification, are permitted provided that the following conditions are met:

* * Redistributions of source code must retain the above copyright notice,

* this list of conditions and the following disclaimer.

* * Redistributions in binary form must reproduce the above copyright

* notice, this list of conditions and the following disclaimer in the

* documentation and/or other materials provided with the distribution.

* * Neither the name of Business Objects nor the names of its contributors

* may be used to endorse or promote products derived from this software

* without specific prior written permission.

* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"

* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE

* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE

* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE

* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR

* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF

* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS

* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN

* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)

* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE

* POSSIBILITY OF SUCH DAMAGE.

* Sql.cal

* Creation date: Feb 5, 2004.

* By: Richard Webster

/**

* This module provides a combinator library for creating abstract SQL queries.

* A query is represented by the type {@link typeConstructor = Query@}.

* A new, empty query can be created by calling {@link newQuery@}.

* {@code

* qry0 = newQuery;

* @}

* To project a column or a SQL expression, use the {@link project@} function.

* There are several variations on this function, such as {@link projectColumn@} and {@link projectWithAliases@}.

* {@code

* qry1 = project qry0 [toUntypedExpr countryField, toUntypedExpr orderDateField];

* @}

* To add a restriction to the query, use the {@link restrict@} function.

* {@code

* qry2 = restrict qry1 (eqExpr countryField (stringConstant "Canada"));

* @}

* To add sorting to the query, use the {@link order@} function.

* {@code

* qry3 = order qry2 orderDateField True;

* @}

* To add a join to the query, use the {@link join@} function.

* {@code

* joinInfo = makeJoinInfo (intField custTable "Customer ID") (intField ordersTable "Customer ID") InnerJoin;

* qry4 = join qry3 joinInfo;

* @}

* An abstract {@link typeConstructor = Query@} can be converted to a concrete SQL query with the {@link queryText@} function.

* This function requires a {@link typeConstructor = SqlBuilder@} for a specific RDBMS as found in the module {@link module = "Cal.Data.SqlBuilder"@}.

* See the module {@link module = "Cal.Test.Data.Sql_Tests"@} for examples of query construction.

* There is some support for other types of SQL statements, such as ones to create/delete tables and inserting rows.

* See the {@link Statement@} type.

* @author Richard Webster

module Cal.Data.Sql;

import Cal.Core.Prelude using

typeConstructor = Byte, Int, Double, String, Boolean, Char, Integer, JObject, JList, Maybe, Ordering;

dataConstructor = False, True, LT, EQ, GT, Nothing, Just;

typeClass = Eq, Ord, Num, Inputable, Outputable;

function =

append, compare, concat, const, doubleToString, equals, error, fromJust, fst, input,

intToString, isNothing, isEmpty, max, mod, not, output, round, seq, snd, toDouble,

field1, field2, field3, upFrom, upFromTo;

typeConstructor = Long;

function = compose, fromMaybe, isJust, listToMaybe, maybeToList, longToString;

;

import Cal.Collections.List using

function = all, chop, filter, foldLeft, foldLeftStrict, foldRight, head, intersperse,

last, length, list2, map, outputList, outputListWith, reverse, subscript, sum, tail, take,

zip, zip3, zipWith;

function = any, concatMap, deleteFirsts, drop, elemIndex, isElem, mapIndexed, repeat, removeDuplicates,

removeDuplicatesBy, lookupWithDefault, find, groupBy, intersect, isSingletonList, sort;

;

import Cal.Core.String using

function = toLowerCase;

;

import Cal.Core.Debug using

typeClass = Show;

function = show;

;

import Cal.Utilities.TimeZone using

typeConstructor = TimeZone;

;

import Cal.Utilities.Time using

typeConstructor = Time;

;

import Cal.Utilities.PrettyPrinter using

typeConstructor = Document;

function = line, multilineText, nest, text;

;

import Cal.Data.SqlType using

typeConstructor = SqlType;

;

import Cal.Collections.Set using

typeConstructor = Set;

;

import Cal.Collections.Array using

typeConstructor = Array;

;

import Cal.Data.DatabaseMetadata using

typeConstructor = DatabaseReference, TableReference, TableDescription, FieldDescription,

TableConstraint;

dataConstructor = PrimaryKeyConstraint, UniqueConstraint, ForeignKeyConstraint;

function = getTableNameFromReference, makeTableReference;

;

import Cal.Utilities.UniqueIdentifier using

typeConstructor = UniqueIdentifier;

typeClass = UniquelyNamedItem;

function = makeUniqueIdentifierByName;

;

import Cal.Utilities.Range using

typeConstructor = Range;

function =

hasLeftEndpoint, hasRightEndpoint, includesLeftEndpoint,

includesRightEndpoint, leftEndpoint, rightEndpoint;

;

friend Cal.Data.SqlBuilder;

/**

* Associates a table resource with its alias for the query.

data public QueryTable =

/**

* A reference to an actual database table with a given alias for the query.

* @arg tableRef a reference to a database table by name

* @arg tableAlias the alias for the table in the query

private BaseTable

tableRef :: !TableReference

tableAlias :: !String

/**

* A nested SELECT query to be treated as a query table with a given alias.

* @arg subquery a database query to be treated as a query table

* @arg tableAlias the alias for the nested SELECT 'table' in the query

private SubQueryTable

subquery :: Query

tableAlias :: !String

/**

* A nested SELECT statement to be treated as a query table with a given alias.

* @arg subqueryText the SQL for a SELECT statement to be treated as a query table

* @arg tableAlias the alias for the nested SELECT 'table' in the query

private OpaqueSubQueryTable

subqueryText :: String

tableAlias :: !String

;

/**

* Constructs a query table using the specified table name.

* The table alias will be based on the table name.

* @arg tableName the name of the table to be used in the query

* @return a query table for the specified table using the table name as the alias

makeQueryTable :: String -> QueryTable;

public makeQueryTable !tableName = makeQueryTableWithAlias tableName "";

/**

* Constructs a query table using the specified table name and base alias name.

* If no table alias is specified, the table name will be used as the alias.

* @arg tableName the name of the table to be used in the query

* @arg tableAlias the alias to use for the table in the query;

* An empty alias name indicates that the table name should be used as the alias

* @return a query table for the specified table and alias

makeQueryTableWithAlias :: String -> String -> QueryTable;

public makeQueryTableWithAlias !tableName !tableAlias =

let

fixedAlias :: String;

fixedAlias = if (isEmpty tableAlias) then tableName else tableAlias;

BaseTable (makeTableReference tableName) fixedAlias;

/**

* Constructs a subquery table using the specified query and alias.

* This function will remove any ordering from the subquery (unless it is a {@link TopN@}

* query).

* @arg subquery a database query to be treated as a query table

* @arg tableAlias the alias to use for the table in the query;

* If an empty alias is specified, then a default one will be generated.

* @return a query table for the nested SELECT query with the given alias

makeSubQueryTable :: Query -> String -> QueryTable;

public makeSubQueryTable subquery !tableAlias =

let

fixedAlias :: String;

fixedAlias = if (isEmpty tableAlias) then "Q" else tableAlias;

SubQueryTable (fixSubqueryOrdering subquery) fixedAlias;

/**

* Constructs a subquery table using the specified query text and alias.

* The query text must be valid when used as a subquery.

* In most cases, this means that it cannot contain an ORDER BY clause.

* @arg subqueryText a SELECT statement text to be treated as a query table

* @arg tableAlias the alias to use for the table in the query;

* If an empty alias is specified, then a default one will be generated.

* @return a query table for the nested SELECT query with the given alias

makeOpaqueSubQueryTable :: String -> String -> QueryTable;

public makeOpaqueSubQueryTable subqueryText !tableAlias =

let

fixedAlias :: String;

fixedAlias = if (isEmpty tableAlias) then "Q" else tableAlias;

OpaqueSubQueryTable subqueryText fixedAlias;

/**

* Returns whether the specified table is a subquery table.

* @arg queryTable a query table

* @return True if the table is a subquery (or opaque subquery) table;

* False if the table is a base database table

isSubqueryTable :: QueryTable -> Boolean;

public isSubqueryTable !queryTable =

case queryTable of

(SubQueryTable | OpaqueSubQueryTable) {} -> True;

_ -> False;

;

/**

* Returns the name of the table (without quotes).

* An empty string is returned for the name of a subquery table.

* @arg queryTable a query table

* @return the name of the base database table,

* or an empty string if the query table is based on a subquery

getQueryTableName :: QueryTable -> String;

public getQueryTableName !queryTable =

case queryTable of

BaseTable {tableRef} -> getTableNameFromReference tableRef;

_ -> "";

;

/**

* Returns the table alias for a query table.

* This is a composition of the base alias and the table ID to give a distinct alias for the table.

* @arg queryTable a query table

* @return the table alias for the query table

getQueryTableAlias :: QueryTable -> String;

public getQueryTableAlias !queryTable =

case queryTable of

(BaseTable | SubQueryTable | OpaqueSubQueryTable) {tableAlias} -> tableAlias;

;

/**

* Query tables can be compared for equality.

* This is based only on the table aliases, not the actual table information.

instance Eq QueryTable where

equals = equalsQueryTable;

notEquals = notEqualsQueryTable;

;

equalsQueryTable :: QueryTable -> QueryTable -> Boolean;

private equalsQueryTable table1 table2 =

case table1 of

BaseTable {tableAlias} ->

case table2 of

// TODO: should this compare the tables as well? (This breaks the

// SqlParser code currently.)

BaseTable {tableAlias = tableAlias2} ->

tableAlias == tableAlias2;

// && table == table2;

_ -> False;

;

SubQueryTable {tableAlias} ->

case table2 of

// TODO: should this compare the subqueries as well? (This breaks the

// SqlParser code currently.)

SubQueryTable {tableAlias = tableAlias2} ->

tableAlias == tableAlias2;

// && subQuery == subQuery2;

_ -> False;

;

OpaqueSubQueryTable {tableAlias} ->

case table2 of

OpaqueSubQueryTable {tableAlias = tableAlias2} ->

tableAlias == tableAlias2;

// && subqueryText == subqueryText2;

_ -> False;

;

notEqualsQueryTable :: QueryTable -> QueryTable -> Boolean;

private notEqualsQueryTable table1 table2 = not (equalsQueryTable table1 table2);

/**

* Query tables can be shown.

* For subquery tables, the subquery text will not be shown since this may be too verbose.

instance Debug.Show QueryTable where

show = showQueryTable;

;

showQueryTable :: QueryTable -> String;

private showQueryTable table =

case table of

BaseTable {tableRef, tableAlias} ->

if (getTableNameFromReference tableRef == tableAlias) then tableAlias

else (show tableRef) ++ " AS " ++ tableAlias;

(SubQueryTable |

OpaqueSubQueryTable) {tableAlias} -> "<subquery> AS "++ tableAlias;

;

/**

* Options for the query.

data public QueryOption =

/**

* This option indicates that only distinct rows should be returned for the query.

public Distinct

/**

* This option indicates that the first N rows should be returned.

* @arg n the number of rows to be returned

* @arg percent if True then the number with be interpretted as a percentage of the full set of rows,

* if False then the number will be interpretted as a number of rows

* @arg withTies if True then any rows that are considered equivalent order-wise to the Nth row will also be included in the results;

* if False then only the first N rows will be returned, even if there are ties

public TopN

n :: !Int

percent :: !Boolean

withTies :: !Boolean

deriving Eq, Show

;

/**

* Returns whether the query option specifies TopN criteria.

* @arg option a query option

* @return True if the query option specifies TopN criteria

isTopNOption :: QueryOption -> Boolean;

public isTopNOption !option =

case option of

TopN {} -> True;

_ -> False;

;

/**

* Information about a join between 2 tables.

data public JoinInfo =

/**

* Information about a join between 2 tables.

* @arg leftTable one of the query tables to be joined

* @arg rightTable the other query table to be joined

* @arg fieldJoinExprs Boolean expressions joining fields from the tables

* @arg joinType the type of join being performed (inner, left outer, right outer, or full outer)

private JoinInfo

leftTable :: !QueryTable

rightTable :: !QueryTable

fieldJoinExprs :: [TypedExpr Boolean]

joinType :: !JoinType

deriving Eq

;

/**

* Creates a join info using the query field info specified.

* The expressions provided must both be {@link QueryField@}s.

* The link expressions will be created to compare each pair of fields using

* {@code '='@}.

* If other link comparison are required, the {@link typeConstructor = JoinInfo@} can be constructed with

* these explicitly.

* @arg leftField an expression for a field in the left table to be joined

* @arg rightField an expression for a field in the right table to be joined

* @arg joinType the type of join being performed (inner, left outer, right outer, or full outer)

* @return join info between the specified table fields

makeJoinInfo :: Eq a => TypedExpr a -> TypedExpr a -> JoinType -> JoinInfo;

public makeJoinInfo !leftField !rightField !joinType =

let

tableFromQueryField fieldExpr =

case (toUntypedExpr fieldExpr) of

QueryField {queryTable} -> queryTable;

_ -> error ("The join fields must be QueryFields");

;

leftTable :: QueryTable;

leftTable = tableFromQueryField leftField;

rightTable :: QueryTable;

rightTable = tableFromQueryField rightField;

fieldJoinExpr :: TypedExpr Boolean;

fieldJoinExpr = eqExpr leftField rightField;

JoinInfo leftTable rightTable [fieldJoinExpr] joinType;

/**

* Creates a join info between 2 tables using the specified linking expression.

* @arg leftTable one of the query tables to be joined

* @arg rightTable the other query table to be joined

* @arg linkingExpr a Boolean expression joining fields from the tables

* @arg joinType the type of join being performed (inner, left outer, right outer, or full outer)

makeJoinInfo2 :: QueryTable -> QueryTable -> TypedExpr Boolean -> JoinType -> JoinInfo;

public makeJoinInfo2 !leftTable !rightTable linkingExpr !joinType =

JoinInfo leftTable rightTable [linkingExpr] joinType;

/**

* The types of joins that can be performed between two tables.

data public JoinType =

/**

* The inner join option indicates that rows should be returned where

* the join condition between the table is satisfied.

public InnerJoin

/**

* The left outer join option indicates that all rows from the left table should

* be included along with values from the right table where the join condition is satisfied.

public LeftOuterJoin

/**

* The right outer join option indicates that all rows from the right table should

* be included along with values from the left table where the join condition is satisfied.

public RightOuterJoin

/**

* The full outer join option indicates that all rows satisfying the join condition should be returned

* as well as any unmatched rows from both the left and right tables.

public FullOuterJoin

deriving Eq, Show

;

/**

* A binary tree structure for representing joins.

data public JoinNode =

/**

* A node representing a single query table.

* @arg table a query table

public JoinTable

table :: QueryTable

/**

* A node specifying a join between tables in two join trees.

* @arg leftNode one of the join trees to be joined

* @arg rightNode the other join tree to be joined

* @arg linkingExpr a Boolean expression joining tables in the join trees

* @arg joinType the type of join to be performed

public JoinSubtree

leftNode :: JoinNode

rightNode :: JoinNode

linkingExpr :: (TypedExpr Boolean)

joinType :: JoinType

deriving Eq

;

/**

* Join nodes can be shown.

instance Debug.Show JoinNode where

show = showJoinNode;

;

showJoinNode :: JoinNode -> String;

private showJoinNode !node =

case node of

JoinTable joinTable -> show joinTable;

JoinSubtree {leftNode, rightNode} -> "{" ++ (showJoinNode leftNode) ++ " -> " ++ (showJoinNode rightNode) ++ "}";

;

/**

* A named set of joins.

data public JoinSet =

/**

* A named set of joins.

* @arg name the name of the join set

* @arg joins the root join of the join set

private JoinSet

name :: !String

joins :: JoinNode

deriving Show

;

/**

* Construct a join set from a list of JoinInfo values.

* @arg name the name of the join set

* @arg joins the joins to be part of the set

* @return a join set with the specified name

makeJoinSet :: String -> [JoinInfo] -> JoinSet;

public makeJoinSet !name joins =

makeJoinSet2 name (buildJoinTree joins);

/**

* Construct a join set from a JoinNode tree

* @arg name the name of the join set

* @arg joinTree the root join of the join set

* @return a join set with the specified name

makeJoinSet2 :: String -> JoinNode -> JoinSet;

public makeJoinSet2 !name joinTree =

JoinSet name joinTree;

/**

* Returns the join info from the join set.

* @arg joinSet a join set

* @return the root join node of the set

joinSetJoinTree :: JoinSet -> JoinNode;

public joinSetJoinTree !joinSet =

joinSet.JoinSet.joins;

// Implement the UniquelyNamedItem class.

instance UniquelyNamedItem JoinSet where

getDisplayName = joinSetName;

getUniqueIdentifier = joinSetUniqueIdentifier;

;

joinSetName !joinSet = joinSet.JoinSet.name;

joinSetUniqueIdentifier !joinSet = makeUniqueIdentifierByName (joinSetName joinSet);

/**

* A database expression.

data public Expr =

/**

* A reference to a field in a query table.

* @arg fieldName the name of the field

* @arg queryTable the query table

private QueryField

fieldName :: String

queryTable :: QueryTable

/**

* A literal value expression.

* @arg constantValue the constant value

private ConstExpr

constantValue :: ConstValue

/**

* A parameter placeholder expression.

* @arg parameter the database parameter

private ParameterExpr

parameter :: Parameter

/**

* A list of database expressions.

* @arg listValues the list of expressions

private ListExpr

listValues :: [Expr]

/**

* A function expression.

* @arg func an identifier for the database function

* @arg arguments the function argument expressions

private FunctionExpr

func :: DbFunction

arguments :: [Expr]

/**

* A subquery expression.

* The subquery must have a single projected column.

* @arg subquery the subquery to be treated as a database expression

private SubQueryExpr

subquery :: Query

deriving Eq, Show

;

/**

* Returns {@link True@} if the expression represents a list.

* @arg expr a database expression

* @return {@link True@} if the expression represents a list; {@link False@} otherwise

isListExpr :: Expr -> Boolean;

public isListExpr !expr =

case expr of

ListExpr {} -> True;

_ -> False;

;

/**

* Returns the value expressions from a list expression.

* An exception will be thrown if the expression is not a list expression.

* @arg expr a database expression

* @return the list of value expressions form a list expression

getListFromListExpr :: Expr -> [Expr];

public getListFromListExpr !expr =

case expr of

ListExpr {listValues} -> listValues;

_ -> error "Not a list expression";

;

/**

* Returns {@link True@} if the expression represents a constant containing a string value.

* @arg expr a database expression

* @return {@link True@} if the expression represents a string literal; {@link False@} otherwise

isStringConstExpr :: Expr -> Boolean;

public isStringConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

StringValue {} -> True;

_ -> False;

;

_ -> False;

;

/**

* Returns the string value from a constant expression.

* An exception will be thrown if the expression is not a string constant expression.

* @arg expr a database expression

* @return the string value from the constant database expression

getStringValueFromConstExpr :: Expr -> String;

public getStringValueFromConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

StringValue {strValue} -> strValue;

_ -> error "Not a string constant expression";

;

_ -> error "Not a constant expression";

;

/**

* Returns {@link True@} if the expression represents a constant containing a number value.

* @arg expr a database expression

* @return {@link True@} if the expression represents a numeric literal; {@link False@} otherwise

isNumberConstExpr :: Expr -> Boolean;

public isNumberConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

NumberValue {} -> True;

_ -> False;

;

_ -> False;

;

/**

* Returns the number value from a constant expression.

* An exception will be thrown if the expression is not a numeric constant expression.

* @arg expr a database expression

* @return the numeric value from the constant database expression

getNumberValueFromConstExpr :: Expr -> Double;

public getNumberValueFromConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

NumberValue {numValue} -> numValue;

_ -> error "Not a number constant expression";

;

_ -> error "Not a constant expression";

;

/**

* Returns {@link True@} if the expression represents a constant containing a boolean value.

* @arg expr a database expression

* @return {@link True@} if the expression represents a Boolean constant; {@link False@} otherwise

isBooleanConstExpr :: Expr -> Boolean;

public isBooleanConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

BooleanValue {} -> True;

_ -> False;

;

_ -> False;

;

/**

* Returns the boolean value from a constant expression.

* An exception will be thrown if the expression is not a constant Boolean expression.

* @arg expr a database expression

* @return the Boolean value from the constant database expression

getBooleanValueFromConstExpr :: Expr -> Boolean;

public getBooleanValueFromConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

BooleanValue {boolValue} -> boolValue;

_ -> error "Not a boolean constant expression";

;

_ -> error "Not a constant expression";

;

/**

* Returns {@link True@} if the expression represents a constant containing a time value.

* @arg expr a database expression

* @return {@link True@} if the expression represents a time constant; {@link False@} otherwise

isTimeConstExpr :: Expr -> Boolean;

public isTimeConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

TimeValue {} -> True;

_ -> False;

;

_ -> False;

;

/**

* Returns the time value from a constant expression.

* An exception will be thrown if the expression is not a time constant expression.

* @arg expr a database expression

* @return the time value from the constant database expression

getTimeValueFromConstExpr :: Expr -> Time;

public getTimeValueFromConstExpr !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

TimeValue {timeValue} -> timeValue;

_ -> error "Not a time constant expression";

;

_ -> error "Not a constant expression";

;

/**

* Returns {@link True@} if the expression represents a field in a query table.

* @arg expr a database expression

* @return {@link True@} if the expression represents a field in a query table; {@link False@} otherwise

isQueryFieldExpr :: Expr -> Boolean;

public isQueryFieldExpr !expr =

case expr of

QueryField {} -> True;

_ -> False;

;

/**

* Returns the query table from a query field expression.

* An exception will be thrown if the expression is not a query field expression.

* @arg expr a database expression

* @return the field name from the query field expression

getFieldNameFromQueryFieldExpr :: Expr -> String;

public getFieldNameFromQueryFieldExpr !expr =

case expr of

QueryField {fieldName} -> fieldName;

_ -> error "Not a query field expression";

;

/**

* Returns the field name from a query field expression.

* An exception will be thrown if the expression is not a query field expression.

* @arg expr a database expression

* @return the query table from the query field expression

getQueryTableFromQueryFieldExpr :: Expr -> QueryTable;

public getQueryTableFromQueryFieldExpr !expr =

case expr of

QueryField {queryTable} -> queryTable;

_ -> error "Not a query field expression";

;

/**

* Returns {@link True@} if the expression represents a function (or operator)

* application.

* @arg expr a database expression

* @return {@link True@} if the expression represents a database function expression; {@link False@} otherwise

isFunctionExpr :: Expr -> Boolean;

public isFunctionExpr !expr =

case expr of

FunctionExpr {} -> True;

_ -> False;

;

/**

* Returns the function type from a function expression.

* An exception will be thrown if the expression is not a function expression.

* @arg expr a database expression

* @return the function identifier from the function expression

getFunctionFromFunctionExpr :: Expr -> DbFunction;

public getFunctionFromFunctionExpr !expr =

case expr of

FunctionExpr {func} -> func;

_ -> error "Not a function expression";

;

/**

* Returns the arguments from a function expression.

* An exception will be thrown if the expression is not a function expression.

* @arg expr a database expression

* @return the argument expressions from the function expression

getArgumentsFromFunctionExpr :: Expr -> [Expr];

public getArgumentsFromFunctionExpr !expr =

case expr of

FunctionExpr {arguments} -> arguments;

_ -> error "Not a function expression";

;

/**

* Returns whether the expression represents an operator applied to one or more

* arguments.

* @arg expr a database expression

* @return {@link True@} if the expression represents an operator applied to one or

* more arguments; {@link False@} otherwise

isOperatorExpr :: Expr -> Boolean;

public isOperatorExpr !expr =

case expr of

FunctionExpr {func} -> isOperator func;

_ -> False;

;

/**

* {@code TypedExpr@} wraps an untyped {@link Expr@} and adds information about the expression

* data type.

data public TypedExpr a =

private TypedExpr

untypedExpr :: Expr

;

/**

* Returns the untyped expression from a typed expression.

* @arg typedExpr a typed database expression

* @return the untyped database expression

toUntypedExpr :: TypedExpr a -> Expr;

public toUntypedExpr !typedExpr = typedExpr.TypedExpr.untypedExpr;

/**

* Returns a typed expression from the untyped expression.

* @arg untypedExpr an untyped database expression

* @return the typed database expression

toTypedExpr :: Expr -> TypedExpr a;

public toTypedExpr = TypedExpr;

/**

* Returns the type of the values represented by this typed expression.

* @arg expr a database expression

* @return the type of the database expression

typeOfExpr :: Prelude.Typeable a => TypedExpr a -> Prelude.TypeRep;

public typeOfExpr !expr =

head (Prelude.typeArguments (Prelude.typeOf expr));

// Allow typed expressions to be compared for equality.

instance Eq (TypedExpr a) where

equals = equalsTypedExpr;

notEquals = notEqualsTypedExpr;

;

// Allow typed expressions to be shown.

instance Show (TypedExpr a) where

show = showTypedExpr;

;

showTypedExpr :: TypedExpr a -> String;

showTypedExpr !typedExpr =

show typedExpr.TypedExpr.untypedExpr;

/**

* Checks whether 2 typed expressions are equal.

private equalsTypedExpr !expr1 !expr2 =

(toUntypedExpr expr1) == (toUntypedExpr expr2);

private notEqualsTypedExpr !expr1 !expr2 =

not (equalsTypedExpr expr1 expr2);

/**

* A constant value.

data private ConstValue =

private NullValue

private StringValue

strValue::String

private NumberValue

numValue::Double

private BooleanValue

boolValue::Boolean

private TimeValue

timeValue::Time

deriving Eq, Show

;

/**

* Construct a database expression for a string literal.

* @arg strValue the string literal value

* @return the database expression for the string literal

stringConstant :: String -> TypedExpr String;

public stringConstant strValue =

TypedExpr (ConstExpr (StringValue strValue));

/**

* Construct a database expression for a numeric literal.

* @arg numValue the numeric literal value

* @return the database expression for the numeric literal

numericConstant :: Num a => a -> TypedExpr a;

public numericConstant numValue =

TypedExpr (ConstExpr (NumberValue (toDouble numValue)));

/**

* Construct a database expression for a Boolean value.

* @arg boolValue the Boolean value

* @return the database expression for the Boolean value

booleanConstant :: Boolean -> TypedExpr Boolean;

public booleanConstant !boolValue =

if boolValue then trueConstant else falseConstant;

/**

* A database expression for the Boolean value True.

* @return the database expression for the Boolean value True

trueConstant :: TypedExpr Boolean;

public trueConstant =

TypedExpr (ConstExpr (BooleanValue True));

/**

* A database expression for the Boolean value False.

* @return the database expression for the Boolean value False

falseConstant :: TypedExpr Boolean;

public falseConstant =

TypedExpr (ConstExpr (BooleanValue False));

/**

* Construct a database expression for a time literal.

* @arg timeValue the time literal value

* @return the database expression for the time literal

timeConstant :: Time -> TypedExpr Time;

public timeConstant timeValue =

TypedExpr (ConstExpr (TimeValue timeValue));

/**

* A database expression for a null value of some data type.

* @return a database expression for a null value

nullValue :: TypedExpr a;

public nullValue =

TypedExpr (ConstExpr NullValue);

/**

* Tests whether an untyped expression has the constant value of null.

* This does not check whether an expression evaluates to null -- it simply

* checks whether it has the constant value of null.

* @arg expr a database expression

* @return True if the expression has the constant value of null

isNullValue :: Expr -> Boolean;

public isNullValue !expr =

case expr of

ConstExpr {constantValue} ->

case constantValue of

NullValue -> True;

_ -> False;

;

_ -> False;

;

/**

* A database parameter that can be used in place of a value in a SQL statement.

* The parameter values can be bound in when executing the SQL statement.

* This is often used to execute the same statement multiple times with different values.

data private Parameter =

/**

* A parameter without a name.

* The values for unnamed parameters will be bound based on the position of the

* parameter in the SQL statement.

private UnnamedParameter

/**

* A parameter with an associated name.

* Note: JDBC 3.0 only supports named parameter for CallableStatement.

private NamedParameter

name :: String

deriving Eq, Show

;

/**

* Returns whether the parameter is a named one.

* @arg param a database parameter

* @return True if the parameter is named one; False if the parameter is unnamed

isUnnamedParameter :: Parameter -> Boolean;

public isUnnamedParameter !param =

case param of

UnnamedParameter -> True;

_ -> False;

;

/**

* Returns the name of a named parameter.

* An error will be thrown if the parameter is unnamed.

* @arg param a database parameter

* @return the name of the named database parameter

getParameterName :: Parameter -> String;

public getParameterName !param =

case param of

NamedParameter {name} -> name;

_ -> error "Only a named parameter has a name!";

;

/**

* Constructs an string expression for a database parameter.

* If Nothing is specified for the name, then the parameter will be an unnamed one.

* @arg maybeParamName an optional name for the parameter

* @return a string database expression for a database parameter

stringParameter :: Maybe String -> TypedExpr String;

public stringParameter !maybeParamName = TypedExpr $ untypedParameter maybeParamName;

/**

* Constructs an integer expression for a database parameter.

* If Nothing is specified for the name, then the parameter will be an unnamed one.

* @arg maybeParamName an optional name for the parameter

* @return a integer database expression for a database parameter

integerParameter :: Maybe String -> TypedExpr Int;

public integerParameter !maybeParamName = TypedExpr $ untypedParameter maybeParamName;

/**

* Constructs an double expression for a database parameter.

* If Nothing is specified for the name, then the parameter will be an unnamed one.

* @arg maybeParamName an optional name for the parameter

* @return a double database expression for a database parameter

doubleParameter :: Maybe String -> TypedExpr Double;

public doubleParameter !maybeParamName = TypedExpr $ untypedParameter maybeParamName;

/**

* Constructs an Boolean expression for a database parameter.

* If Nothing is specified for the name, then the parameter will be an unnamed one.

* @arg maybeParamName an optional name for the parameter

* @return a Boolean database expression for a database parameter

booleanParameter :: Maybe String -> TypedExpr Boolean;

public booleanParameter !maybeParamName = TypedExpr $ untypedParameter maybeParamName;

/**

* Constructs an time expression for a database parameter.

* If Nothing is specified for the name, then the parameter will be an unnamed one.

* @arg maybeParamName an optional name for the parameter

* @return a time database expression for a database parameter

timeParameter :: Maybe String -> TypedExpr Time;

public timeParameter !maybeParamName = TypedExpr $ untypedParameter maybeParamName;

/**

* Constructs an untyped expression for a database parameter.

* If Nothing is specified for the name, then the parameter will be an unnamed one.

* @arg maybeParamName an optional name for the parameter

* @return a database expression for a database parameter

untypedParameter :: Maybe String -> Expr;

private untypedParameter !maybeParamName =

case maybeParamName of

Just paramName -> ParameterExpr $ NamedParameter paramName;

Nothing -> ParameterExpr $ UnnamedParameter;

;

/**

* Constructs an untyped database expression for a list of values.

* @arg listValues a list of database expressions for the list value

* @return an untyped database expression for the list of values

untypedListExpr :: [Expr] -> Expr;

public untypedListExpr listValues = ListExpr listValues;

/**

* Constructs a typed database expression for a list of values.

* This probably doesn't need to be exposed since it can only be used with

* the IN binary operator anyway.

* @arg listValues a list of database expressions for the list value

* @return a typed database expression for the list of values

listExpr :: [TypedExpr a] -> TypedExpr [a];

private listExpr listValues = TypedExpr (ListExpr (map toUntypedExpr listValues));

/**

* Creates a database expression based on a subquery.

* Any ordering will be removed from the subquery (unless it uses {@link TopN@}).

* The subquery must have a single projected column.

* @arg subquery the subquery to be treated as a database expression

* @return a database expression based on the subquery

subQueryExpr :: Query -> Expr;

public subQueryExpr !subquery =

let

// Remove any ordering from the subquery (unless it uses TopN).

fixedSubquery = fixSubqueryOrdering subquery;

// Check that there is only 1 return column in the subquery.

// TODO: is there a way to make this typed based on the (one and only) result column in the query?

if (isSingletonList (getProjectedColumns fixedSubquery)) then SubQueryExpr fixedSubquery

else error "Only subqueries returning a single column can be used as field expressions";

/**

* Database expression functions and operators.

data public DbFunction =

// Unary operators.

public OpNot | public OpBitNot | public OpNegate

| public OpIsNull | public OpIsNotNull

| public OpExists

// Binary operators

| public OpAnd | public OpOr

| public OpLike | public OpIn

| public OpCat

| public OpBitAnd | public OpBitOr | public OpBitXor

// Other operators

| public OpBetween | public OpCase_Simple | public OpCase_Searched

// Conversion functions

| public ConvertToStringFunction

| public ConvertToIntFunction

| public ConvertToDoubleFunction

| public ConvertToTimeFunction

// Numeric functions

| public SinFunction | public SqrtFunction | public TanFunction | public TruncateFunction

// String functions

| public AsciiFunction | public CharFunction | public DifferenceFunction

| public SoundexFunction | public SpaceFunction | public SubstringFunction | public UcaseFunction

// System functions

| public DatabaseFunction | public UserFunction

// Null-handling functions

| public IfNullFunction | public NullIfFunction

// DateTime functions

| public WeekFunction | public YearFunction

| public DateTimeAddFunction timeInterval :: TimeInterval

| public DateTimeDiffFunction timeInterval :: TimeInterval

// Aggregation functions.

| public AggrDistinctCount | public AggrDistinctSum | public AggrDistinctAvg

| public AggrCountAll

| public AggrStdDev | public AggrStdDevP | public AggrVar | public AggrVarP

| public AggrOther other :: String

// A function with the specified name.

| public FunctionOther funcName :: String

deriving Eq, Show

;

/**

* Possible time interval values for working with date/time values.

data public TimeInterval =

public YearInterval

| public QuarterInterval

| public MonthInterval

| public DayInterval

| public HourInterval

| public MinuteInterval

| public SecondInterval

deriving Eq, Show

;

/**

* Indicates whether a specified function is an aggregation function or not.

* @arg fn a database function identifier

* @return True if the specified function is an aggregation function

isAggregationFunction :: DbFunction -> Boolean;

public isAggregationFunction !fn =

case fn of

(AggrCount |

AggrSum |

AggrAvg |

AggrMin |

AggrMax |

AggrDistinctCount |

AggrDistinctSum |

AggrDistinctAvg |

AggrCountAll |

AggrStdDev |

AggrStdDevP |

AggrVar |

AggrVarP |

AggrOther) {} -> True;

_ -> False;

;

/**

* Returns the default name of a database function.

defaultFunctionName :: DbFunction -> String;

public defaultFunctionName !func =

case func of

// ConvertFunction -> "CONVERT";

AbsFunction -> "ABS";

AcosFunction -> "ACOS";

AsinFucntion -> "ASIN";

AtanFunction -> "ATAN";

Atan2Function -> "ATAN2";

CeilingFunction -> "CEILING";

CosFunction -> "COS";

CotFunction -> "COT";

DegreesFunction -> "DEGREES";

ExpFunction -> "EXP";

FloorFunction -> "FLOOR";

LogFunction -> "LOG";

Log10Function -> "LOG10";

ModFunction -> "MOD";

PiFunction -> "PI";

PowerFunction -> "POWER";

RadiansFunction -> "RADIANS";

RandFunction -> "RAND";

RoundFunction -> "ROUND";

SignFunction -> "SIGN";

SinFunction -> "SIN";

SqrtFunction -> "SQRT";

TanFunction -> "TAN";

TruncateFunction -> "TRUNCATE";

AsciiFunction -> "ASCII";

CharFunction -> "CHAR";

// TODO: use the OpCat operator instead...

// ConcatFunction -> "CONCAT";

DifferenceFunction -> "DIFFERENCE";

InsertFunction -> "INSERT";

LcaseFunction -> "LCASE";

LeftFunction -> "LEFT";

LengthFunction -> "LENGTH";

LocateFunction -> "LOCATE";

LtrimFunction -> "LTRIM";

RepeatFunction -> "REPEAT";

ReplaceFunction -> "REPLACE";

RightFunction -> "RIGHT";

RtrimFunction -> "RTRIM";

SoundexFunction -> "SOUNDEX";

SpaceFunction -> "SPACE";

SubstringFunction -> "SUBSTRING";

UcaseFunction -> "UCASE";

DatabaseFunction -> "DATABASE";

IfNullFunction -> "IFNULL";

NullIfFunction -> "NULLIF";

UserFunction -> "USER";

DayNameFunction -> "DAYNAME";

DayOfWeekFunction -> "WEEKDAY";

DayOfMonthFunction -> "DAY";

DayOfYearFunction -> "DAYOFYEAR";

HourFunction -> "HOUR";

MinuteFunction -> "MINUTE";

MonthFunction -> "MONTH";

MonthNameFunction -> "MONTHNAME";

NowFunction -> "NOW";

QuarterFunction -> "QUARTER";

SecondFunction -> "SECOND";

// TimestampAddFunction -> "TIMESTAMPADD";

// TimestampDiffFunction -> "TIMESTAMPDIFF";

WeekFunction -> "WEEK";

YearFunction -> "YEAR";

DateTimeAddFunction {} -> "DATEADD";

DateTimeDiffFunction {} -> "DATEDIFF";

AggrCount -> "COUNT";

AggrSum -> "SUM";

AggrAvg -> "AVG";

AggrMin -> "MIN";

AggrMax -> "MAX";

AggrStdDev -> "STDDEV";

AggrStdDevP -> "STDDEVP";

AggrVar -> "VAR";

AggrVarP -> "VARP";

AggrOther {other} -> other;

// Note: the following block of functions are generated specially, but names are included here for

// purposes of outputing to Java.

AggrDistinctCount -> "DISTINCT COUNT";

AggrDistinctSum -> "DISTINCT SUM";

AggrDistinctAvg -> "DISTINCT AVG";

AggrCountAll -> "COUNT ALL";

FunctionOther {funcName} -> funcName;

;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// SQL Functions

/**

* Constructs an untyped database expression for a call to a database function.

* No checking will be done for the number or types of the arguments.

* Where possible the appropriate type-safe function should be called to construct the

* function expression.

* @arg func an identifier for a database function

* @arg arguments a list of database expressions for the function arguments

* @return an untyped database expression for a call to the database function

untypedFunctionExpr :: DbFunction -> [Expr] -> Expr;

public untypedFunctionExpr func arguments = FunctionExpr func arguments;

// Helper functions to construct typed function expressions.

// These should not be exposed.

functionExpr0 :: DbFunction -> TypedExpr a;

private functionExpr0 func = TypedExpr (untypedFunctionExpr func []);

functionExpr1 :: DbFunction -> TypedExpr a -> TypedExpr b;

private functionExpr1 func arg1 = TypedExpr (untypedFunctionExpr func [toUntypedExpr arg1]);

functionExpr2 :: DbFunction -> TypedExpr a -> TypedExpr b -> TypedExpr c;

private functionExpr2 func arg1 arg2 = TypedExpr (untypedFunctionExpr func [toUntypedExpr arg1, toUntypedExpr arg2]);

functionExpr3 :: DbFunction -> TypedExpr a -> TypedExpr b -> TypedExpr c -> TypedExpr d;

private functionExpr3 func arg1 arg2 arg3 = TypedExpr (untypedFunctionExpr func [toUntypedExpr arg1, toUntypedExpr arg2, toUntypedExpr arg3]);

functionExpr4 :: DbFunction -> TypedExpr a -> TypedExpr b -> TypedExpr c -> TypedExpr d -> TypedExpr e;

private functionExpr4 func arg1 arg2 arg3 arg4 = TypedExpr (untypedFunctionExpr func [toUntypedExpr arg1, toUntypedExpr arg2, toUntypedExpr arg3, toUntypedExpr arg4]);

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Unary operator expressions

/**

* Constructs an untyped database expression for the application of a unary operator.

* No checking will be done for the type of the argument.

* Where possible the appropriate type-safe function should be called to construct the

* function expression.

* @arg func an identifier for a unary operator

* @arg argument a database expression to which the unary operator will be applied

* @return an untyped database expression for the application of the unary operator

untypedUnaryExpr :: DbFunction -> Expr -> Expr;

public untypedUnaryExpr func argument = FunctionExpr func [argument];

/**

* Constructs a database expression for the application of the 'not' operator to a Boolean argument.

* @arg boolExpr a Boolean database expression

* @return a Boolean database expression for the application of the 'not' operator to the argument expression

notExpr :: TypedExpr Boolean -> TypedExpr Boolean;

public notExpr = functionExpr1 OpNot;

/**

* Constructs a database expression for the application of the bitwise 'not' operator to an integer argument.

* @arg intExpr an integer database expression

* @return an integer database expression for the application of the bitwise 'not' operator to the argument expression

bitwiseNotExpr :: TypedExpr Int -> TypedExpr Int;

public bitwiseNotExpr = functionExpr1 OpBitNot;

/**

* Constructs a database expression for the negation of the numeric argument expression.

* @arg numExpr a numeric database expression

* @return a numeric database expression for the negation of the argument expression

negateExpr :: Num a => TypedExpr a -> TypedExpr a;

public negateExpr = functionExpr1 OpNegate;

/**

* Constructs a database expression to test whether an argument expression is null.

* @arg expr a database expression

* @return a Boolean database expression to test test whether an argument expression is null

isNullExpr :: TypedExpr a -> TypedExpr Boolean;

public isNullExpr = functionExpr1 OpIsNull;

/**

* Constructs a database expression to test whether an argument expression is non-null.

* @arg expr a database expression

* @return a Boolean database expression to test test whether an argument expression is non-null

isNotNullExpr :: TypedExpr a -> TypedExpr Boolean;

public isNotNullExpr = functionExpr1 OpIsNotNull;

/**

* Constructs a database expression to test whether the argument subquery expression returns any rows.

* @arg subqueryExpr a subquery expressoin

* @return a Boolean database expression to test whether the argument subquery expression returns any rows

existsExpr :: TypedExpr [a] -> TypedExpr Boolean;

public existsExpr subqueryExpr =

// TODO: is there any way to make this only accept subqueries?

functionExpr1 OpExists subqueryExpr;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Binary operator expressions

/**

* Constructs an untyped database expression for the application of a binary operator.

* No checking will be done for the types of the arguments.

* Where possible the appropriate type-safe function should be called to construct the

* function expression.

* @arg func an identifier for a binary operator

* @arg leftArgument a database expression for the left argument to which the operator will be applied

* @arg rightArgument a database expression for the right argument to which the operator will be applied

* @return an untyped database expression for the application of the binary operator

untypedBinaryExpr :: DbFunction -> Expr -> Expr -> Expr;

public untypedBinaryExpr func leftArgument rightArgument = FunctionExpr func [leftArgument, rightArgument];

/**

* Constructs a database expression which test two operand expressions for equality.

* @arg expr1 a database expression for the first operand

* @arg expr2 a database expression for the second operand

* @return a Boolean database expression to test the operand expressions for equality

eqExpr :: Eq a => TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public eqExpr = functionExpr2 OpEq;

/**

* Constructs a database expression which tests two operand expressions for inequality.

* @arg expr1 a database expression for the first operand

* @arg expr2 a database expression for the second operand

* @return a Boolean database expression to test the operand expressions for inequality

notEqExpr :: Eq a => TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public notEqExpr = functionExpr2 OpNotEq;

/**

* Constructs a database expression to test whether the first operand is less than the second.

* @arg expr1 a database expression for the first operand

* @arg expr2 a database expression for the second operand

* @return a Boolean database expression to test whether the first operand is less than the second

ltExpr :: Ord a => TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public ltExpr = functionExpr2 OpLt;

/**

* Constructs a database expression to test whether the first operand is greater than the second.

* @arg expr1 a database expression for the first operand

* @arg expr2 a database expression for the second operand

* @return a Boolean database expression to test whether the first operand is greater than the second

gtExpr :: Ord a => TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public gtExpr = functionExpr2 OpGt;

/**

* Constructs a database expression to test whether the first operand is less than or equal to the second.

* @arg expr1 a database expression for the first operand

* @arg expr2 a database expression for the second operand

* @return a Boolean database expression to test whether the first operand is less than or equal to the second

ltEqExpr :: Ord a => TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public ltEqExpr = functionExpr2 OpLtEq;

/**

* Constructs a database expression to test whether the first operand is greater than or equal to the second.

* @arg expr1 a database expression for the first operand

* @arg expr2 a database expression for the second operand

* @return a Boolean database expression to test whether the first operand is greater than or equal to the second

gtEqExpr :: Ord a => TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public gtEqExpr = functionExpr2 OpGtEq;

/**

* Constructs a database expression to test whether both of the operand expressions are True.

* @arg b1 a database expression for the first operand

* @arg b2 a database expression for the second operand

* @return a Boolean database expression to test whether both of the operand expressions are True

andExpr :: TypedExpr Boolean -> TypedExpr Boolean -> TypedExpr Boolean;

public andExpr !b1 b2 =

if (b1 == trueConstant) then b2

else if (b2 == trueConstant) then b1

else if (b1 == falseConstant || b2 == falseConstant) then falseConstant

else functionExpr2 OpAnd b1 b2;

/**

* Constructs a database expression to test whether either of the operand expressions are True.

* @arg b1 a database expression for the first operand

* @arg b2 a database expression for the second operand

* @return a Boolean database expression to test whether either of the operand expressions are True

orExpr :: TypedExpr Boolean -> TypedExpr Boolean -> TypedExpr Boolean;

public orExpr b1 b2 =

if (b1 == falseConstant) then b2

else if (b2 == falseConstant) then b1

else if (b1 == trueConstant || b2 == trueConstant) then trueConstant

else functionExpr2 OpOr b1 b2;

/**

* Constructs a database expression to test whether the first string operand expression matches

* the pattern of the second operand expression.

* @arg strExpr a string database expression for the first operand

* @arg patternExpr a string database expression for the pattern against which the first operand will be tested

* @return a Boolean database expression to test whether first string operand expression matches

* the pattern of the second operand expression

likeExpr :: TypedExpr String -> TypedExpr String -> TypedExpr Boolean;

public likeExpr = functionExpr2 OpLike;

/**

* Constructs a database expression to test whether the value of the first operand is equal to the value

* returned by one of the list operand expressions.

* @arg leftExpr a database expression for the first operand

* @arg listValueExprs a database expression for the values against the first operand will be tested

* @return a Boolean database expression to test whether the value of the first operand is one of

* the values returned by the list operand expression

inExpr :: Eq a => TypedExpr a -> [TypedExpr a] -> TypedExpr Boolean;

public inExpr leftExpr listValueExprs = functionExpr2 OpIn leftExpr (listExpr listValueExprs);

/**

* Constructs a database expression to test whether the value of the first operand is one of the values

* returned by the list operand expression.

* @arg leftExpr a database expression for the first operand

* @arg listValuesExpr a database expression for the values against the first operand will be tested

* @return a Boolean database expression to test whether the value of the first operand is one of

* the values returned by the list operand expression

inExpr2 :: Eq a => TypedExpr a -> TypedExpr [a] -> TypedExpr Boolean;

public inExpr2 leftExpr listValuesExpr = functionExpr2 OpIn leftExpr listValuesExpr;

/**

* Constructs a database expression to concatenate two string operand expressions.

* @arg expr1 a string database expression for the first operand

* @arg expr2 a string database expression for the second operand

* @return a string database expression to concatenate the string operand expressions

concatExpr :: TypedExpr String -> TypedExpr String -> TypedExpr String;

public concatExpr = functionExpr2 OpCat;

/**

* A helper function for constructing a numeric binary operator.

* Do not expose this.

numBinaryExpr :: Num a => DbFunction -> TypedExpr a -> TypedExpr a -> TypedExpr a;

private numBinaryExpr = functionExpr2;

/**

* Constructs a database expression to add two numeric operand expressions.

* @arg expr1 a numeric database expression for the first operand

* @arg expr2 a numeric database expression for the second operand

* @return a numeric database expression to add the two numeric operand expressions

public addExpr = numBinaryExpr OpPlus;

/**

* Constructs a database expression to subtract two numeric operand expressions.

* @arg expr1 a numeric database expression for the first operand

* @arg expr2 a numeric database expression for the second operand

* @return a numeric database expression to subtract the two numeric operand expressions

public subtractExpr = numBinaryExpr OpMinus;

/**

* Constructs a database expression to multiply two numeric operand expressions.

* @arg expr1 a numeric database expression for the first operand

* @arg expr2 a numeric database expression for the second operand

* @return a numeric database expression to multiply the two numeric operand expressions

public multiplyExpr = numBinaryExpr OpMul;

/**

* Constructs a database expression to divide two numeric operand expressions.

* @arg expr1 a numeric database expression for the first operand

* @arg expr2 a numeric database expression for the second operand

* @return a numeric database expression to divide the two numeric operand expressions

public divideExpr = numBinaryExpr OpDiv;

/**

* Constructs a database expression to return the modulus of two numeric operand expressions.

* @arg expr1 a numeric database expression for the first operand

* @arg expr2 a numeric database expression for the second operand

* @return a numeric database expression to return the modulus of the two numeric operand expressions

public modulusExpr = numBinaryExpr OpMod;

/**

* A helper function for constructing an integer binary operator.

* Do not expose this.

intBinaryExpr :: DbFunction -> TypedExpr Int -> TypedExpr Int -> TypedExpr Int;

private intBinaryExpr = functionExpr2;

/**

* Constructs a database expression to perform a bitwise 'and' of two numeric operand expressions.

* @arg expr1 an integer database expression for the first operand

* @arg expr2 an integer database expression for the second operand

* @return an integer database expression to perform a bitwise 'and' of two numeric operand expressions

public bitwiseAndExpr = intBinaryExpr OpBitAnd;

/**

* Constructs a database expression to perform a bitwise 'or' of two numeric operand expressions.

* @arg expr1 an integer database expression for the first operand

* @arg expr2 an integer database expression for the second operand

* @return an integer database expression to perform a bitwise 'or' of two numeric operand expressions

public bitwiseOrExpr = intBinaryExpr OpBitOr;

/**

* Constructs a database expression to perform a bitwise 'xor' of two numeric operand expressions.

* @arg expr1 an integer database expression for the first operand

* @arg expr2 an integer database expression for the second operand

* @return an integer database expression to perform a bitwise 'xor' of two numeric operand expressions

public bitwiseXorExpr = intBinaryExpr OpBitXor;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Other operator expressions

/**

* Constructs a database expression which test whether the value of one operand expression is between

* the value of two other operand expressions.

* @arg leftExpr a database expression to be tested

* @arg lowerExpr a database expression for the lower bound of the value range

* @arg upperExpr a database expression for the upper bound of the value range

* @return a Boolean database expression to test whether the value of one operand expression is between

* the value of two other operand expressions

betweenExpr :: Ord a => TypedExpr a -> TypedExpr a -> TypedExpr a -> TypedExpr Boolean;

public betweenExpr leftExpr lowerExpr upperExpr = functionExpr3 OpBetween leftExpr lowerExpr upperExpr;

/**

* Constructs a database expression for a simple case statement.

* When this expression is evaluated, the result value of the matching WHEN clause will be returned.

* If none of the WHEN values match the case value, then the ELSE value will be returned.

* @arg caseExpr a database expression for the value to be tested

* @arg whenValueAndResults a list of pairs of comparison values and the corresponding result values

* @arg elseValue a database expression for the value to be returned if none of the WHEN clauses match

* @return a database expression for a simple case statement

simpleCaseExpr :: Eq a => TypedExpr a -> [(TypedExpr a, TypedExpr b)] -> Maybe (TypedExpr b) -> TypedExpr b;

public simpleCaseExpr caseExpr whenValueAndResults elseValue =

let

unTypedExprs = toUntypedExpr caseExpr

: concatMap (\pr -> [toUntypedExpr (fst pr), toUntypedExpr (snd pr)]) whenValueAndResults

++ map toUntypedExpr (maybeToList elseValue);

TypedExpr (untypedFunctionExpr OpCase_Simple unTypedExprs);

/**

* Construct a database expression for a searched case statement.

* Each WHEN clause will contain a Boolean expression and a result value.

* The result will be the result value for the first WHEN clause with a condition that returns True.

* If none of the WHEN clause expressions return True, then the ELSE value will be returned.

* @arg whenConditionAndResults a list of pairs of Boolean condition expressions and result values

* @arg elseValue a database expression for the value to be returned if none of the WHEN clauses evaluate to True

* @return a database expression for a searched case statement

searchedCaseExpr :: [(TypedExpr Boolean, TypedExpr b)] -> Maybe (TypedExpr b) -> TypedExpr b;

public searchedCaseExpr whenConditionAndResults elseValue =

let

unTypedExprs = concatMap (\pr -> [toUntypedExpr (fst pr), toUntypedExpr (snd pr)]) whenConditionAndResults

++ map toUntypedExpr (maybeToList elseValue);

TypedExpr (untypedFunctionExpr OpCase_Searched unTypedExprs);

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Numeric functions

absExpr :: Num a => TypedExpr a -> TypedExpr a;

public absExpr = functionExpr1 AbsFunction;

acosExpr :: TypedExpr Double -> TypedExpr Double;

public acosExpr = functionExpr1 AcosFunction;

asinExpr :: TypedExpr Double -> TypedExpr Double;

public asinExpr = functionExpr1 AsinFucntion;

atanExpr :: TypedExpr Double -> TypedExpr Double;

public atanExpr = functionExpr1 AtanFunction;

atan2Expr :: TypedExpr Double -> TypedExpr Double -> TypedExpr Double;

public atan2Expr = functionExpr2 Atan2Function;

ceilingExpr :: Num a => TypedExpr a -> TypedExpr Int;

public ceilingExpr = functionExpr1 CeilingFunction;

cosExpr :: TypedExpr Double -> TypedExpr Double;

public cosExpr = functionExpr1 CosFunction;

cotExpr :: TypedExpr Double -> TypedExpr Double;

public cotExpr = functionExpr1 CotFunction;

degreesExpr :: TypedExpr Double -> TypedExpr Double;

public degreesExpr = functionExpr1 DegreesFunction;

expExpr :: Num a => TypedExpr a -> TypedExpr Double;

public expExpr = functionExpr1 ExpFunction;

floorExpr :: Num a => TypedExpr a -> TypedExpr Int;

public floorExpr = functionExpr1 FloorFunction;

logExpr :: Num a => TypedExpr a -> TypedExpr Double;

public logExpr = functionExpr1 LogFunction;

log10Expr :: Num a => TypedExpr a -> TypedExpr Double;

public log10Expr = functionExpr1 Log10Function;

modExpr :: TypedExpr Int -> TypedExpr Int -> TypedExpr Int;

public modExpr = functionExpr2 ModFunction;

piExpr :: TypedExpr Double;

public piExpr = functionExpr0 PiFunction;

powerExpr :: (Num a, Num b) => TypedExpr a -> TypedExpr b -> TypedExpr a;

public powerExpr = functionExpr2 PowerFunction;

radiansExpr :: Num a => TypedExpr a -> TypedExpr Double;

public radiansExpr = functionExpr1 RadiansFunction;

randExpr :: TypedExpr Int -> TypedExpr Double;

public randExpr = functionExpr1 RandFunction;

roundExpr :: TypedExpr Double -> TypedExpr Int -> TypedExpr Double;

public roundExpr = functionExpr2 RoundFunction;

signExpr :: Num a => TypedExpr a -> TypedExpr Int;

public signExpr = functionExpr1 SignFunction;

sinExpr :: TypedExpr Double -> TypedExpr Double;

public sinExpr = functionExpr1 SinFunction;

sqrtExpr :: Num a => TypedExpr a -> TypedExpr Double;

public sqrtExpr = functionExpr1 SqrtFunction;

tanExpr :: TypedExpr Double -> TypedExpr Double;

public tanExpr = functionExpr1 TanFunction;

/**

* TODO: is this the correct signature?

truncateExpr :: Num a => TypedExpr a -> TypedExpr Int;

public truncateExpr = functionExpr1 TruncateFunction;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Conversion functions

/**

* Converts an integer value to a double value.

* The assumption here is that the database will do an implicit conversion

* between these 2 types, so no function will be applied in the generated SQL.

* A function could be used here if necessary.

intToDoubleExpr :: TypedExpr Int -> TypedExpr Double;

public intToDoubleExpr expr = toTypedExpr (toUntypedExpr expr);

/**

* Converts a value to a string value.

convertToStringExpr :: TypedExpr a -> TypedExpr String;

public convertToStringExpr = functionExpr1 ConvertToStringFunction;

/**

* Converts a value to a int value.

convertToIntExpr :: TypedExpr a -> TypedExpr Int;

public convertToIntExpr = functionExpr1 ConvertToIntFunction;

/**

* Converts a value to a double value.

convertToDoubleExpr :: TypedExpr a -> TypedExpr Double;

public convertToDoubleExpr = functionExpr1 ConvertToDoubleFunction;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// String functions

asciiExpr :: TypedExpr String -> TypedExpr Int;

public asciiExpr = functionExpr1 AsciiFunction;

charExpr :: TypedExpr Int -> TypedExpr String;

public charExpr = functionExpr1 CharFunction;

differenceExpr :: TypedExpr String -> TypedExpr String -> TypedExpr Int;

public differenceExpr = functionExpr2 DifferenceFunction;

insertExpr :: TypedExpr String -> TypedExpr Int -> TypedExpr Int -> TypedExpr String -> TypedExpr String;

public insertExpr stringExpr start length insertStr = functionExpr4 InsertFunction stringExpr start length insertStr;

lcaseExpr :: TypedExpr String -> TypedExpr String;

public lcaseExpr = functionExpr1 LcaseFunction;

leftExpr :: TypedExpr String -> TypedExpr Int -> TypedExpr String;

public leftExpr = functionExpr2 LeftFunction;

lengthExpr :: TypedExpr String -> TypedExpr Int;

public lengthExpr = functionExpr1 LengthFunction;

locateExpr :: TypedExpr String -> TypedExpr String -> TypedExpr Int;

public locateExpr searchExpr stringExpr = functionExpr2 LocateFunction searchExpr stringExpr;

locate2Expr :: TypedExpr String -> TypedExpr String -> TypedExpr Int -> TypedExpr Int;

public locate2Expr searchExpr stringExpr start = functionExpr3 LocateFunction searchExpr stringExpr start;

ltrimExpr :: TypedExpr String -> TypedExpr String;

public ltrimExpr = functionExpr1 LtrimFunction;

repeatExpr :: TypedExpr String -> TypedExpr Int -> TypedExpr String;

public repeatExpr stringExpr count = functionExpr2 RepeatFunction stringExpr count;

replaceExpr :: TypedExpr String -> TypedExpr String -> TypedExpr String -> TypedExpr String;

public replaceExpr stringExpr searchStr replacementStr = functionExpr3 ReplaceFunction stringExpr searchStr replacementStr;

rightExpr :: TypedExpr String -> TypedExpr Int -> TypedExpr String;

public rightExpr = functionExpr2 RightFunction;

rtrimExpr :: TypedExpr String -> TypedExpr String;

public rtrimExpr = functionExpr1 RtrimFunction;

soundexExpr :: TypedExpr String -> TypedExpr String;

public soundexExpr = functionExpr1 SoundexFunction;

spaceExpr :: TypedExpr Int -> TypedExpr String;

public spaceExpr nSpaces = functionExpr1 SpaceFunction nSpaces;

substringExpr :: TypedExpr String -> TypedExpr Int -> TypedExpr Int -> TypedExpr String;

public substringExpr stringExpr start length = functionExpr3 SubstringFunction stringExpr start length;

ucaseExpr :: TypedExpr String -> TypedExpr String;

public ucaseExpr = functionExpr1 UcaseFunction;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Misc functions

databaseExpr :: TypedExpr String;

public databaseExpr = functionExpr0 DatabaseFunction;

ifNullExpr :: TypedExpr a -> TypedExpr a -> TypedExpr a;

public ifNullExpr = functionExpr2 IfNullFunction;

nullIfExpr :: TypedExpr a -> TypedExpr a -> TypedExpr a;

public nullIfExpr = functionExpr2 NullIfFunction;

userExpr :: TypedExpr String;

public userExpr = functionExpr0 UserFunction;

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Date/Time functions

dayNameExpr :: TypedExpr Time -> TypedExpr String;

public dayNameExpr = functionExpr1 DayNameFunction;

dayOfWeekExpr :: TypedExpr Time -> TypedExpr Int;

public dayOfWeekExpr = functionExpr1 DayOfWeekFunction;

dayOfMonthExpr :: TypedExpr Time -> TypedExpr Int;

public dayOfMonthExpr = functionExpr1 DayOfMonthFunction;

dayOfYearExpr :: TypedExpr Time -> TypedExpr Int;

public dayOfYearExpr = functionExpr1 DayOfYearFunction;

hourExpr :: TypedExpr Time -> TypedExpr Int;

public hourExpr = functionExpr1 HourFunction;

minuteExpr :: TypedExpr Time -> TypedExpr Int;

public minuteExpr = functionExpr1 MinuteFunction;

monthExpr :: TypedExpr Time -> TypedExpr Int;

public monthExpr = functionExpr1 MonthFunction;

monthNameExpr :: TypedExpr Time -> TypedExpr String;

public monthNameExpr = functionExpr1 MonthNameFunction;

nowExpr :: TypedExpr Time;

public nowExpr = functionExpr0 NowFunction;

quarterExpr :: TypedExpr Time -> TypedExpr Int;

public quarterExpr = functionExpr1 QuarterFunction;

secondExpr :: TypedExpr Time -> TypedExpr Int;

public secondExpr = functionExpr1 SecondFunction;

// TODO: create helper functions for TimestampAddFunction and

// TimestampDiffFunction...

weekExpr :: TypedExpr Time -> TypedExpr Int;

public weekExpr = functionExpr1 WeekFunction;

yearExpr :: TypedExpr Time -> TypedExpr Int;

public yearExpr = functionExpr1 YearFunction;

dateTimeAddExpr :: TimeInterval -> TypedExpr Int -> TypedExpr Time -> TypedExpr Time;

public dateTimeAddExpr timeInterval = functionExpr2 (DateTimeAddFunction timeInterval);

dateTimeDiffExpr :: TimeInterval -> TypedExpr Time -> TypedExpr Time -> TypedExpr Int;

public dateTimeDiffExpr timeInterval = functionExpr2 (DateTimeDiffFunction timeInterval);

//////////////////////////////////////////////////////////////////////////////////////////////////////

// Aggregate functions

untypedAggregationExpr :: DbFunction -> Expr -> Expr;

public untypedAggregationExpr aggrOp baseExpr =

if (isAggregationFunction aggrOp) then FunctionExpr aggrOp [baseExpr]

else error "The specified function is not an aggregation function.";

countExpr :: TypedExpr a -> TypedExpr Int;

public countExpr = functionExpr1 AggrCount;

sumExpr :: Num a => TypedExpr a -> TypedExpr a;

public sumExpr = functionExpr1 AggrSum;

avgExpr :: Num a => TypedExpr a -> TypedExpr Double;

public avgExpr = functionExpr1 AggrAvg;

minExpr :: Ord a => TypedExpr a -> TypedExpr a;

public minExpr = functionExpr1 AggrMin;

maxExpr :: Ord a => TypedExpr a -> TypedExpr a;

public maxExpr = functionExpr1 AggrMax;

distinctCountExpr :: TypedExpr a -> TypedExpr Int;

public distinctCountExpr = functionExpr1 AggrDistinctCount;

distinctSumExpr :: Num a => TypedExpr a -> TypedExpr a;

public distinctSumExpr = functionExpr1 AggrDistinctSum;

distinctAvgExpr :: Num a => TypedExpr a -> TypedExpr Double;

public distinctAvgExpr = functionExpr1 AggrDistinctAvg;

countAllExpr :: TypedExpr Double;

public countAllExpr = functionExpr0 AggrCountAll;

stdDevExpr :: Num a => TypedExpr a -> TypedExpr Double;

public stdDevExpr = functionExpr1 AggrStdDev;

stdDevPExpr :: Num a => TypedExpr a -> TypedExpr Double;

public stdDevPExpr = functionExpr1 AggrStdDevP;

varianceExpr :: Num a => TypedExpr a -> TypedExpr Double;

public varianceExpr = functionExpr1 AggrVar;

variancePExpr :: Num a => TypedExpr a -> TypedExpr Double;

public variancePExpr = functionExpr1 AggrVarP;

/**

* A database SELECT query.

data public Query =

private Query

options :: [QueryOption]

columns :: [(Expr, String)]

restriction :: (Maybe (TypedExpr Boolean))

orderings :: [(Expr, Boolean)]

joins :: [JoinNode]

groups :: [Expr]

groupRestriction :: (Maybe (TypedExpr Boolean)) |

private Union

query1 :: Query

query2 :: Query

unionAll :: Boolean

deriving Eq, Show;

/**

* Creates a new, empty query.

newQuery :: Query;

public newQuery = Query [] [] Nothing [] [] [] Nothing;

/**

* Combines the 2 queries into a Union query.

unionQuery :: Query -> Query -> Boolean -> Query;

public unionQuery query1 query2 unionAll =

let

query1Cols = getProjectedColumns query1;

query2Cols = getProjectedColumns query2;

// Remove any ordering from the first query.

updatedQuery1 = removeOrdering query1;

// Check that the queries have the same number of columns.

if (length query1Cols == length query2Cols) then Union updatedQuery1 query2 unionAll

else error "Both queries in a Union must have the same number of columns";

/**

* Produces a query which returns the intersection of the two specified queries.

* The final result columns will be based on the first query.

* The intersection will be performed on the corresponding columns in each

* query.

* It is ok for the queries to have different numbers of columns.

intersectionQuery :: Query -> Query -> Query;

public intersectionQuery query1 query2 =

let

origAliases = map snd (projectedColumnsWithAliases query1);

// Set a known alias for the colums.

itemColumnAliases = map (\i -> "COL_VALUE" ++ (intToString i)) (upFrom 1);

// Wrap these subqueries in tables.

subQueryTable1 = makeSubQueryTable (setColumnAliases query1 itemColumnAliases) "Q1";

subQueryTable2 = makeSubQueryTable (setColumnAliases query2 itemColumnAliases) "Q2";

nJoinColumns = length (zip (getProjectedColumns query1) (getProjectedColumns query2));

joinInfos = map (\alias -> makeJoinInfo (stringField subQueryTable1 alias) (stringField subQueryTable2 alias) InnerJoin) (take nJoinColumns itemColumnAliases);

resultCols = zip (map (untypedField subQueryTable1) itemColumnAliases) origAliases;

join2 (projectWithAliases newQuery resultCols) joinInfos;

/**

* Produces a query which returns the intersection of the two specified queries.

* The final result columns will be based on the first query.

* The intersection will be performed on the specified expressions.

intersectionQuery2 :: Query -> Query -> [(Expr, Expr)] -> Query;

public intersectionQuery2 query1 query2 compareExprs =

let

origAliases = map snd (projectedColumnsWithAliases query1);

compareExprs1 = if (isEmpty compareExprs) then getProjectedColumns query1

else map fst compareExprs;

compareExprs2 = if (isEmpty compareExprs) then getProjectedColumns query2

else map snd compareExprs;

// Ensure that all the comparison expressions are projected from both

// queries.

queryAllColumns1 = projectColumnsIfNecessary query1 compareExprs1;

queryAllColumns2 = projectColumnsIfNecessary query2 compareExprs2;

// Set a known alias for the colums.

itemColumnAliases = map defaultColumnAlias (upFrom 0);

// Wrap these subqueries in tables.

subQueryTable1 = makeSubQueryTable (setColumnAliases queryAllColumns1 itemColumnAliases) "Q1";

subQueryTable2 = makeSubQueryTable (setColumnAliases queryAllColumns2 itemColumnAliases) "Q2";

// Find the join expression for each of the comparison expressions.

lookupJoinExpr origQuery subQueryTable origExpr =

let

indexInOrigQuery = fromJust (elemIndex origExpr (getProjectedColumns origQuery));

alias = defaultColumnAlias indexInOrigQuery;

stringField subQueryTable alias;

joinExprs1 = map (lookupJoinExpr queryAllColumns1 subQueryTable1) compareExprs1;

joinExprs2 = map (lookupJoinExpr queryAllColumns2 subQueryTable2) compareExprs2;

joinInfos = zipWith (\e1 e2 -> makeJoinInfo e1 e2 InnerJoin) joinExprs1 joinExprs2;

resultCols = zip (map (untypedField subQueryTable1) itemColumnAliases) origAliases;

join2 (projectWithAliases newQuery resultCols) joinInfos;

/**

* This is an alternate version of {@link intersectionQuery@}.

* Instead of creating 2 subqueries and linking them, this function creates a

* subquery for the second query

* and links it directly to the first query (although special handling is needed

* if the first query is uses {@link TopN@}).

* This should give the same results as {@code intersectionQuery@}.

* TODO: check which option is most efficient and get rid of the other one.

alternateIntersectionQuery :: Query -> Query -> Query;

public alternateIntersectionQuery query1 query2 =

let

origAliases = map snd (projectedColumnsWithAliases query1);

// Set a known alias for the colums.

itemColumnAliases = map defaultColumnAlias (upFrom 0);

nJoinColumns = length (zip (getProjectedColumns query1) (getProjectedColumns query2));

needToWrapQuery query = isTopNQuery query;

wrapQueryIfNeeded query = if (needToWrapQuery query) then wrapQuery query else query;

updatedQuery1 = wrapQueryIfNeeded query1;

subQueryTable2 = makeSubQueryTable (setColumnAliases query2 itemColumnAliases) "";

joinInfos = zipWith (\query1Col alias -> makeJoinInfo (toTypedExpr query1Col) (stringField subQueryTable2 alias) InnerJoin) (getProjectedColumns updatedQuery1) (take nJoinColumns itemColumnAliases);

setColumnAliases (join2 updatedQuery1 joinInfos) origAliases;

/**

* Produces a query which returns the difference of the two specified queries.

* The final result columns will be based on the first query.

* The difference will be performed on the corresponding columns in each query.

* It is ok for the queries to have different numbers of columns.

differenceQuery :: Query -> Query -> Query;

public differenceQuery query1 query2 =

differenceQuery2 query1 query2 (zip (getProjectedColumns query1) (getProjectedColumns query2));

/**

* Produces a query which returns the difference of the two specified queries.

* The final result columns will be based on the first query.

* The difference will be performed on the specified expressions.

differenceQuery2 :: Query -> Query -> [(Expr, Expr)] -> Query;

public differenceQuery2 query1 query2 compareExprs =

let

origAliases = map snd (projectedColumnsWithAliases query1);

compareExprs1 = if (isEmpty compareExprs) then getProjectedColumns query1

else map fst compareExprs;

compareExprs2 = if (isEmpty compareExprs) then getProjectedColumns query2

else map snd compareExprs;

// Ensure that all the comparison expressions are projected from both

// queries.

queryAllColumns1 = projectColumnsIfNecessary query1 compareExprs1;

queryAllColumns2 = projectColumnsIfNecessary query2 compareExprs2;

// Take the first query, and add a restriction of the following form:

// NOT EXISTS (SELECT ... WHERE qry2Field1 <> qry1Field1 AND qry2Field2

// <> qry1Field2 AND ....

needToWrapQuery query = isTopNQuery query;

wrapQueryIfNeeded query = if (needToWrapQuery query) then wrapQuery query else query;

updatedQuery1 = wrapQueryIfNeeded queryAllColumns1;

updatedQuery2 = wrapQueryIfNeeded queryAllColumns2;

// Find the updated expression for each of the comparison expressions.

lookupUpdatedExpr origQuery updatedQuery origExpr =

let

lookupMap = zip (getProjectedColumns origQuery) (getProjectedColumns updatedQuery);

fromJust (List.lookup origExpr lookupMap);

updatedCompareExprs1 = map (lookupUpdatedExpr queryAllColumns1 updatedQuery1) compareExprs1;

updatedCompareExprs2 = map (lookupUpdatedExpr queryAllColumns2 updatedQuery2) compareExprs2;

exclusionRestrictions = zipWith (\q2Col q1Col -> toTypedExpr (untypedBinaryExpr OpEq q2Col q1Col)) updatedCompareExprs2 updatedCompareExprs1;

exclusionExpr = notExpr (existsExpr (toTypedExpr (subQueryExpr (restrict2 updatedQuery2 exclusionRestrictions))));

restrictedQuery = restrict updatedQuery1 exclusionExpr;

allResultExpr = getProjectedColumns restrictedQuery;

// Set the correct aliases and truncate the projected columns to match

// the original list.

projectWithAliases (removeProjectedColumns restrictedQuery) (zip allResultExpr origAliases);

/**

* Returns a default column alias.

* The column index is the zero-based index of the expression in the query's

* projected columns.

defaultColumnAlias :: Int -> String;

private defaultColumnAlias colIndex = "COL_VALUE" ++ (intToString (colIndex + 1));

/**

* Adds the specified expression to the projected columns, if it isn't already

* projected.

projectColumnIfNecessary :: Query -> Expr -> Query;

private projectColumnIfNecessary !query !expr =

if (isElem expr (getProjectedColumns query)) then query

else project query [expr];

/**

* Adds the specified expressions to the projected columns, if they aren't

* already projected.

projectColumnsIfNecessary :: Query -> [Expr] -> Query;

private projectColumnsIfNecessary query exprs = foldLeftStrict projectColumnIfNecessary query exprs;

/**

* Wraps the query in an outer query which projects the same columns as the

* original.

* The ordering from the original query is also preserved.

wrapQuery :: Query -> Query;

public wrapQuery query = fst (wrapQuery2 query []);

/**

* Wraps the query in an outer query which projects the same columns as the

* original.

* The ordering from the original query is also preserved.

* The wrapped query will be returned along with wrapped versions of the

* specified additional expressions.

wrapQuery2 :: Query -> [Expr] -> (Query, [Expr]);

public wrapQuery2 query additionalExprs =

let

origAliases = projectedColumnAliases query ++ repeat "";

originalOrdering = orderingExpressions query;

origProjectedExprs = getProjectedColumns query;

origOrderingExprs = map fst originalOrdering;

additionalProjectedExprs = deleteFirsts (removeDuplicates (origOrderingExprs ++ additionalExprs)) origProjectedExprs;

itemColumnAliases = map defaultColumnAlias (upFrom 0);

subQueryTable = makeSubQueryTable (setColumnAliases (project query additionalProjectedExprs) itemColumnAliases) "";

allSubqueryProjectedExprs = origProjectedExprs ++ additionalProjectedExprs;

wrappedQueryExprs = map (untypedField subQueryTable) itemColumnAliases;

wrappedQueryProjectedExprs = take (length origProjectedExprs) wrappedQueryExprs;

exprMap = zip allSubqueryProjectedExprs wrappedQueryExprs;

lookupNewExpr origExpr = lookupWithDefault origExpr exprMap (error "Failed to find expression in wrapped query");

// Preverse the ordering from the original query.

newOrdering = map (\pr -> (lookupNewExpr (fst pr), snd pr)) originalOrdering;

wrappedQuery = order2 (projectWithAliases newQuery (zip wrappedQueryProjectedExprs origAliases)) newOrdering;

wrappedAdditionalExprs = map lookupNewExpr additionalExprs;

(wrappedQuery, wrappedAdditionalExprs);

/**

* Returns a query field for the specified table.

untypedField :: QueryTable -> String -> Expr;

public untypedField table fieldName = QueryField fieldName table;

stringField :: QueryTable -> String -> TypedExpr String;

public stringField table fieldName = TypedExpr (QueryField fieldName table);

booleanField :: QueryTable -> String -> TypedExpr Boolean;

public booleanField table fieldName = TypedExpr (QueryField fieldName table);

doubleField :: QueryTable -> String -> TypedExpr Double;

public doubleField table fieldName = TypedExpr (QueryField fieldName table);

intField :: QueryTable -> String -> TypedExpr Int;

public intField table fieldName = TypedExpr (QueryField fieldName table);

timeField :: QueryTable -> String -> TypedExpr Time;

public timeField table fieldName = TypedExpr (QueryField fieldName table);

binaryField :: QueryTable -> String -> TypedExpr (Array Byte);

public binaryField table fieldName = TypedExpr (QueryField fieldName table);

/**

* Returns the query options.

* For {@link Union@} queries, this will return the options for the first query.

queryOptions :: Query -> [QueryOption];

public queryOptions query =

case query of

Query {options} -> options;

Union {query1} -> queryOptions query1;

;

/**

* Adds an option to the query.

addOption :: Query -> QueryOption -> Query;

public addOption !query !newOption =

case (removeOption query newOption) of

Query options columns restriction orderings joins groups groupRestriction ->

Query (options ++ [newOption]) columns restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

// Add the option to both queries.

Union (addOption query1 newOption) (addOption query2 newOption) unionAll;

;

/**

* Removes the option of the same type as the specified one (even if the exact

* values don't match).

removeOption :: Query -> QueryOption -> Query;

public removeOption query optionTypeToRemove =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

let

remainingOptions = filter (\o -> not (isSameOptionType optionTypeToRemove o)) options;

Query remainingOptions columns restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

// Remove the option from both queries.

Union (removeOption query1 optionTypeToRemove) (removeOption query2 optionTypeToRemove) unionAll;

;

/**

* Returns whether the specified options are of the same type (not necessarily

* the same value).

isSameOptionType :: QueryOption -> QueryOption -> Boolean;

isSameOptionType !option1 !option2 =

case option1 of

Distinct ->

case option2 of

Distinct -> True;

_ -> False;

;

TopN {} ->

case option2 of

TopN {} -> True;

_ -> False;

;

/**

* Returns whether a {@link TopN@} option is specified for the query.

isTopNQuery :: Query -> Boolean;

private isTopNQuery !query =

case query of

Query {options} -> any isTopNOption options;

// Check whether either query is a topN query.

Union {query1, query2} -> isTopNQuery query1 || isTopNQuery query2;

;

/**

* Adds the specified expression as a result column in the query.

projectColumn :: Query -> TypedExpr a -> Query;

public projectColumn query newColumn =

project query [toUntypedExpr newColumn];

/**

* Adds the specified expression as a result column in the query.

projectColumnWithAlias :: Query -> TypedExpr a -> String -> Query;

public projectColumnWithAlias query newColumn columnAlias =

projectWithAliases query [(toUntypedExpr newColumn, columnAlias)];

/**

* Adds the specified expressions as result columns in the query.

project :: Query -> [Expr] -> Query;

public project query newColumns =

let

newColumnsAndAliases = zip newColumns (repeat "");

projectWithAliases query newColumnsAndAliases;

/**

* Adds the specified expressions as result columns with the corresponding

* aliases in the query.

* TODO: don't add the same field multiple times...

projectWithAliases :: Query -> [(Expr, String)] -> Query;

public projectWithAliases query newColumns =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

Query options (columns ++ newColumns) restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

// Product the column in both queries.

Union (projectWithAliases query1 newColumns) (projectWithAliases query2 newColumns) unionAll;

;

/**

* Returns the projected columns for the query.

* For a {@link Union@} query, this will return only the projected columns for the first

* query.

* TODO: rename this to projectedColumns...

getProjectedColumns :: Query -> [Expr];

public getProjectedColumns query =

case query of

Query {columns} -> map fst columns;

Union {query1} -> getProjectedColumns query1;

;

/**

* Returns the projected columns for the query.

* For a {@link Union@} query, this will return only the projected columns for the first

* query.

projectedColumnsWithAliases :: Query -> [(Expr, String)];

public projectedColumnsWithAliases query =

case query of

Query {columns} -> columns;

Union {query1} -> projectedColumnsWithAliases query1;

;

/**

* Returns the aliases for the query's projected columns.

projectedColumnAliases :: Query -> [String];

public projectedColumnAliases query = map snd (projectedColumnsWithAliases query);

/**

* Removes all projected columns from the query.

removeProjectedColumns :: Query -> Query;

public removeProjectedColumns query =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

Query options [] restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

// Remove the projected columns from both queries.

Union (removeProjectedColumns query1) (removeProjectedColumns query2) unionAll;

;

/**

* Removes the specified expression from the projected columns list.

* For {@link Union@} queries, the specified expression will be removed from the both

* queries along with the

* corresponding columns in the other query.

removeProjectedColumn :: Query -> Expr -> Query;

public removeProjectedColumn query exprToRemove =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

Query options (filter (\c -> (fst c) != exprToRemove) columns) restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

let

exprIndicesInQuery qry =

case qry of

Query {columns} -> List.findIndices (\c -> fst c == exprToRemove) columns;

Union {query1, query2} -> removeDuplicates (exprIndicesInQuery query1 ++ exprIndicesInQuery query2);

;

// Determine the combined set of indices to be removed.

indicesToRemove = exprIndicesInQuery query;

// Remove the appropriate columns from a query.

removeExprs qry =

case qry of

Query options columns restriction orderings joins groups groupRestriction ->

let

newColumns = map snd (filter (\pr -> not (isElem (fst pr) indicesToRemove)) (zip (upFrom 0) columns));

Query options newColumns restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

Union (removeExprs query1) (removeExprs query2) unionAll;

;

removeExprs query;

;

/**

* Sets the aliases for the first N projected columns.

* Any other existing aliases will be left untouched.

setColumnAliases :: Query -> [String] -> Query;

public setColumnAliases query newAliases =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

let

oldAliases = map snd columns;

projectedExprs = map fst columns;

finalAliases = newAliases ++ (drop (length newAliases) oldAliases);

newColumns = zip projectedExprs finalAliases;

Query options newColumns restriction orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

// Set the column aliases in both queries.

Union (setColumnAliases query1 newAliases) (setColumnAliases query2 newAliases) unionAll;

;

/**

* Adds a restriction on the rows returned by the query.

restrict :: Query -> TypedExpr Boolean -> Query;

public restrict !query !newRestriction =

let

// Returns a list of expressions which are ANDed together, if any.

getAndedExprs :: TypedExpr Boolean -> [TypedExpr Boolean];

getAndedExprs expr =

case (toUntypedExpr) expr of

FunctionExpr {func, arguments} ->

case func of

OpAnd -> concatMap (Prelude.compose getAndedExprs toTypedExpr) arguments;

_ -> [expr];

;

_ -> [expr];

;

restrictHelper :: Query -> TypedExpr Boolean -> Query;

restrictHelper !query !newRestriction =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

let

mergedRestriction =

case restriction of

Nothing -> newRestriction;

Just restrictionValue -> andExpr restrictionValue newRestriction;

;

mergedGroupRestriction =

case groupRestriction of

Nothing -> newRestriction;

JustgroupRestrictionValue -> andExpr groupRestrictionValue newRestriction;

;

// Don't change anything if the new criteria is simply

// 'True'.

if (newRestriction == trueConstant) then

query

else if (isGroupRestriction newRestriction) then

Query options columns restriction orderings joins groups (Just mergedGroupRestriction)

else

Query options columns (Just mergedRestriction) orderings joins groups groupRestriction;

Union query1 query2 unionAll ->

// Restrict both queries.

Union (restrict query1 newRestriction) (restrict query2 newRestriction) unionAll;

;

// Split up the restriction into separate ANDed expressions and apply

// each individually.

// This is needed in cases where the restriction expression contains

// some record restrictions

// and some group restrictions.

foldLeftStrict restrictHelper query (getAndedExprs newRestriction);

/**

* Adds the specified restrictions on the rows returned by the query.

restrict2 :: Query -> [TypedExpr Boolean] -> Query;

public restrict2 query newRestrictions =

foldLeftStrict restrict query newRestrictions;

/**

* Returns whether the restriction expression applies to groups or record

* values.

isGroupRestriction :: TypedExpr Boolean -> Boolean;

private isGroupRestriction restriction = exprUsesAggregation (toUntypedExpr restriction);

/**

* Returns whether the expression uses aggregate functions.

* TODO: is there anything else to look for?

exprUsesAggregation :: Expr -> Boolean;

public exprUsesAggregation expr =

case expr of

(QueryField |

ConstExpr |

SubQueryExpr) {} -> False;

ListExpr {listValues} -> any exprUsesAggregation listValues;

FunctionExpr {func, arguments} -> isAggregationFunction func || (any exprUsesAggregation arguments);

;

/**

* Returns the restriction expressions (including group restrictions) for the

* query.

* For a {@link Union@} query, this only returns the restrictions for the first query.

restrictionExpressions :: Query -> [TypedExpr Boolean];

public restrictionExpressions query =

case query of

Query {restriction, groupRestriction} ->

(Prelude.maybeToList restriction) ++ (Prelude.maybeToList groupRestriction);

Union {query1} ->

restrictionExpressions query1;

;

/**

* Adds sorting on the specified expression.

order :: Ord a => Query -> TypedExpr a -> Boolean -> Query;

public order query sortExpr sortAscending = order2 query [(toUntypedExpr sortExpr, sortAscending)];

/**

* Adds sorting on the specified fields.

order2 :: Query -> [(Expr, Boolean)] -> Query;

public order2 query newOrderings =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

Query options columns restriction (orderings ++ newOrderings) joins groups groupRestriction;

Union query1 query2 unionAll ->

// Add ordering to only the 2nd query.

Union query1 (order2 query2 newOrderings) unionAll;

;

/**

* Returns the ordering info for the query.

orderingExpressions :: Query -> [(Expr, Boolean)];

public orderingExpressions query =

case query of

Query {orderings} -> orderings;

Union {query2} -> orderingExpressions query2;

;

/**

* Removes all ordering from the query.

removeOrdering :: Query -> Query;

public removeOrdering query =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

Query options columns restriction [] joins groups groupRestriction;

Union query1 query2 unionAll ->

// Remove ordering from both queries (even though the first one

// shouldn't have any ordering to begin with).

Union (removeOrdering query1) (removeOrdering query2) unionAll;

;

/**

* Projects the specified expressions from the query, and group and order (ASC) on the expressions.

projectGroupAndOrder :: Query -> [Expr] -> Query;

public projectGroupAndOrder !query !exprs =

order2 (group2 (project query exprs) exprs) (zip exprs $ repeat True);

/**

* Adds a join between 2 tables.

join :: Query -> JoinInfo -> Query;

public join !query newJoinInfo =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

let

newJoinNodes = applyJoinInfo joins newJoinInfo;

Query options columns restriction orderings newJoinNodes groups groupRestriction;

Union query1 query2 unionAll ->

// Add the join to both queries.

Union (join query1 newJoinInfo) (join query2 newJoinInfo) unionAll;

;

/**

* Adds the specified joins to the query.

join2 :: Query -> [JoinInfo] -> Query;

public join2 query newJoins =

foldLeft join query newJoins;

/**

* Adds the specified join nodes to the query.

addJoins :: Query -> [JoinNode] -> Query;

public addJoins query newJoins =

setJoins query (joins query ++ newJoins);

/**

* Sets the join information for the query as a list of join node trees.

* This will replace any existing join info.

setJoins :: Query -> [JoinNode] -> Query;

public setJoins query newJoins =

case query of

Query options columns restriction orderings joins groups groupRestriction ->

Query options columns restriction orderings (cleanupJoinNodes newJoins) groups groupRestriction;

Union query1 query2 unionAll ->

// Set the joins in both queries.

Union (setJoins query1 newJoins) (setJoins query2 newJoins) unionAll;

;

/**

* Returns the join information from the query.

* For a {@link Union@} query, the joins from the first query will be returned.

joins :: Query -> [JoinNode];

public joins query =

case query of

Query {joins} -> joins;

Union {query1} -> joins query1;

;

/**