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