haskelldb-2.2.4: A library of combinators for generating and executing SQL statements.

Copyright | Daan Leijen (c) 1999, daan@cs.uu.nl HWT Group (c) 2003, haskelldb-users@lists.sourceforge.net
License | BSD-style
Maintainer | haskelldb-users@lists.sourceforge.net
Stability | experimental
Portability | non portable
Safe Haskell | None
Language | Haskell98

Database.HaskellDB.Query

Contents

- Data and class declarations
- Operators
- Function declarations
- Internals

Description

Basic combinators for building type-safe queries. The Query monad constructs a relational expression (`PrimQuery`).

Synopsis

- data Rel r = Rel Alias Scheme
- data Attr f a = Attr Attribute
- data Table r = Table TableName Assoc
- data Query a
- newtype Expr a = Expr PrimExpr
- data OrderExpr
- class ToPrimExprs r
- class ConstantRecord r cr | r -> cr where
  - constantRecord :: r -> cr
- class ShowConstant a where
  - showConstant :: a -> Literal
- class ExprC e where
  - primExpr :: e a -> PrimExpr
- class ExprC e => ProjectExpr e
- class ProjectRec r er | r -> er
- class InsertRec r er | r -> er
- newtype ExprAggr a = ExprAggr PrimExpr
- newtype ExprDefault a = ExprDefault PrimExpr
- copy :: HasField f r => Attr f a -> Rel r -> Record (RecCons f (Expr a) RecNil)
- copyAll :: RelToRec r => Rel r -> Record r
- class RelToRec a
- (.==.) :: Eq a => Expr a -> Expr a -> Expr Bool
- (.<>.) :: Eq a => Expr a -> Expr a -> Expr Bool
- (.<.) :: Ord a => Expr a -> Expr a -> Expr Bool
- (.<=.) :: Ord a => Expr a -> Expr a -> Expr Bool
- (.>.) :: Ord a => Expr a -> Expr a -> Expr Bool
- (.>=.) :: Ord a => Expr a -> Expr a -> Expr Bool
- (.&&.) :: Expr Bool -> Expr Bool -> Expr Bool
- (.||.) :: Expr Bool -> Expr Bool -> Expr Bool
- (.*.) :: Num a => Expr a -> Expr a -> Expr a
- (./.) :: Num a => Expr a -> Expr a -> Expr a
- (.+.) :: Num a => Expr a -> Expr a -> Expr a
- (.-.) :: Num a => Expr a -> Expr a -> Expr a
- (.%.) :: Num a => Expr a -> Expr a -> Expr a
- (.++.) :: Expr String -> Expr String -> Expr String
- (<<) :: Attr f a -> e a -> Record (RecCons f (e a) RecNil)
- (<<-) :: ShowConstant a => Attr f a -> a -> Record (RecCons f (Expr a) RecNil)
- project :: (ShowLabels r, ToPrimExprs r, ProjectRec r er) => Record r -> Query (Rel er)
- restrict :: Expr Bool -> Query ()
- table :: ShowRecRow r => Table r -> Query (Rel r)
- unique :: Query ()
- union :: Query (Rel r) -> Query (Rel r) -> Query (Rel r)
- unionAll :: Query (Rel r) -> Query (Rel r) -> Query (Rel r)
- intersect :: Query (Rel r) -> Query (Rel r) -> Query (Rel r)
- divide :: Query (Rel r) -> Query (Rel r) -> Query (Rel r)
- minus :: Query (Rel r) -> Query (Rel r) -> Query (Rel r)
- _not :: Expr Bool -> Expr Bool
- like :: Expr String -> Expr String -> Expr Bool
- _in :: Eq a => Expr a -> [Expr a] -> Expr Bool
- cat :: Expr String -> Expr String -> Expr String
- _length :: Expr String -> Expr Int
- isNull :: Expr a -> Expr Bool
- notNull :: Expr a -> Expr Bool
- fromNull :: Expr a -> Expr (Maybe a) -> Expr a
- fromVal :: ShowConstant a => a -> Expr (Maybe a) -> Expr a
- constant :: ShowConstant a => a -> Expr a
- constVal :: ShowConstant a => a -> Expr (Maybe a)
- constNull :: Expr (Maybe a)
- constExpr :: Expr a -> Expr (Maybe a)
- param :: Expr a -> Expr a
- namedParam :: Name -> Expr a -> Expr a
- class Args a
- func :: Args a => String -> a
- cast :: String -> Expr a -> Expr b
- toStr :: BStrToStr s d => s -> d
- coerce :: Expr a -> Expr b
- select :: HasField f r => Attr f a -> Rel r -> Expr a
- count :: Expr a -> ExprAggr Int
- _sum :: Num a => Expr a -> ExprAggr a
- _max :: Ord a => Expr a -> ExprAggr a
- _min :: Ord a => Expr a -> ExprAggr a
- avg :: Num a => Expr a -> ExprAggr a
- literal :: String -> Expr a
- stddev :: Num a => Expr a -> ExprAggr a
- stddevP :: Num a => Expr a -> ExprAggr a
- variance :: Num a => Expr a -> ExprAggr a
- varianceP :: Num a => Expr a -> ExprAggr a
- asc :: HasField f r => Rel r -> Attr f a -> OrderExpr
- desc :: HasField f r => Rel r -> Attr f a -> OrderExpr
- order :: [OrderExpr] -> Query ()
- top :: Int -> Query ()
- offset :: Int -> Query ()
- _case :: [(Expr Bool, Expr a)] -> Expr a -> Expr a
- _default :: ExprDefault a
- runQuery :: Query (Rel r) -> PrimQuery
- runQueryRel :: Query (Rel r) -> (PrimQuery, Rel r)
- unQuery :: Query a -> a
- subQuery :: Query (Rel r) -> Query (Rel r)
- attribute :: String -> Expr a
- attributeName :: Attr f a -> Attribute
- tableName :: Table t -> TableName
- baseTable :: (ShowLabels r, ToPrimExprs r) => TableName -> Record r -> Table r
- emptyTable :: TableName -> Table (Record RecNil)
- exprs :: ToPrimExprs r => Record r -> [PrimExpr]
- labels :: ShowLabels r => r -> [String]
- tableRec :: Table (Record r) -> Record r

# Data and class declarations

data Rel r Source

Type of relations, contains the attributes of the relation and an `Alias` to which the attributes are renamed in the `PrimQuery`.

Constructors

Rel Alias Scheme |

Instances

ExprTypes r => ExprTypes (Rel r) Source |
ExprType a => ExprType (Rel a) Source |
HasField f r => Select (Attr f a) (Rel r) (Expr a) Source | Field selection operator. It is overloaded to work for both relations in a query and the result of a query. That is, it corresponds to both `!` and `!.` from the original HaskellDB. An overloaded operator was selected because users (and the developers) always forgot to use !. instead of ! on query results.

data Attr f a Source

Typed attributes

Constructors

Attr Attribute |

Instances

HasField f r => Select (Attr f a) (Rel r) (Expr a) Source | Field selection operator. It is overloaded to work for both relations in a query and the result of a query. That is, it corresponds to both `!` and `!.` from the original HaskellDB. An overloaded operator was selected because users (and the developers) always forgot to use !. instead of ! on query results.

data Table r Source

Basic tables, contains table name and an association from attributes to attribute names in the real table.

Constructors

Table TableName Assoc |

data Query a Source

Instances

Monad Query Source |
Functor Query Source |
Applicative Query Source |

newtype Expr a Source

Type of normal expressions, contains the untyped PrimExpr.

Constructors

Expr PrimExpr |

Instances

ProjectExpr Expr Source |
ExprC Expr Source |
Read (Expr a) Source |
Show (Expr a) Source |
Args (Expr a) Source |
ExprType a => ExprType (Expr a) Source |
Args (Expr a -> ExprAggr c) Source |
(IsExpr tail, Args tail) => Args (Expr a -> tail) Source |
HasField f r => Select (Attr f a) (Rel r) (Expr a) Source | Field selection operator. It is overloaded to work for both relations in a query and the result of a query. That is, it corresponds to both `!` and `!.` from the original HaskellDB. An overloaded operator was selected because users (and the developers) always forgot to use !. instead of ! on query results.
(RelToRec rest, FieldTag f) => RelToRec (RecCons f (Expr a) rest) Source |
(ShowConstant a, ConstantRecord r cr) => ConstantRecord (RecCons f a r) (RecCons f (Expr a) cr) Source |
(ProjectExpr e, ProjectRec r er) => ProjectRec (RecCons f (e a) r) (RecCons f (Expr a) er) Source |
(InsertExpr e, InsertRec r er) => InsertRec (RecCons f (e a) r) (RecCons f (Expr a) er) Source |
(GetValue a, GetRec er vr) => GetRec (RecCons f (Expr a) er) (RecCons f a vr) Source |

data OrderExpr Source

Instances

Show OrderExpr Source |

class ToPrimExprs r Source

Minimal complete definition

toPrimExprs

Instances

ToPrimExprs RecNil Source |
(ExprC e, ToPrimExprs r) => ToPrimExprs (RecCons l (e a) r) Source |

class ConstantRecord r cr | r -> cr where Source

Converts records w/o Expr (usually from database queries) to records with Expr types.

Methods

constantRecord :: r -> cr Source

Instances

ConstantRecord RecNil RecNil Source |
ConstantRecord r cr => ConstantRecord (Record r) (Record cr) Source |
(ShowConstant a, ConstantRecord r cr) => ConstantRecord (RecCons f a r) (RecCons f (Expr a) cr) Source |

class ShowConstant a where Source

Methods

showConstant :: a -> Literal Source

Instances

ShowConstant Bool Source |
ShowConstant Double Source |
ShowConstant Int Source |
ShowConstant Integer Source |
ShowConstant String Source |
ShowConstant CalendarTime Source |
ShowConstant a => ShowConstant (Maybe a) Source |
Size n => ShowConstant (BoundedString n) Source |

class ExprC e where Source

Class of expression types.

Methods

primExpr :: e a -> PrimExpr Source

Get the underlying untyped `PrimExpr`.

Instances

ExprC ExprDefault Source |
ExprC ExprAggr Source |
ExprC Expr Source |

class ExprC e => ProjectExpr e Source

Class of expressions that can be used with `project`.

Instances

ProjectExpr ExprAggr Source |
ProjectExpr Expr Source |

class ProjectRec r er | r -> er Source

Class of records that can be used with `project`. All all the values must be instances of `ProjectExpr` for the record to be an instance of `ProjectRec`.

Instances

ProjectRec RecNil RecNil Source |
(ProjectExpr e, ProjectRec r er) => ProjectRec (RecCons f (e a) r) (RecCons f (Expr a) er) Source |

class InsertRec r er | r -> er Source

Class of records that can be used with `insert`. All all the values must be instances of `InsertExpr` for the record to be an instance of `InsertRec`.

Instances

InsertRec RecNil RecNil Source |
(InsertExpr e, InsertRec r er) => InsertRec (RecCons f (e a) r) (RecCons f (Expr a) er) Source |

newtype ExprAggr a Source

Type of aggregate expressions.

Constructors

ExprAggr PrimExpr |

Instances

ProjectExpr ExprAggr Source |
ExprC ExprAggr Source |
Read (ExprAggr a) Source |
Show (ExprAggr a) Source |
Args (Expr a -> ExprAggr c) Source |

newtype ExprDefault a Source

The type of default expressions.

Constructors

ExprDefault PrimExpr |

Instances

ExprC ExprDefault Source |
Read (ExprDefault a) Source |
Show (ExprDefault a) Source |

copy :: HasField f r => Attr f a -> Rel r -> Record (RecCons f (Expr a) RecNil) Source

Creates a single-field record from an attribute and a table. Useful for building projections that will re-use the same attribute name. `copy attr tbl` is equivalent to:

    attr .=. (tbl .!. attr)

copyAll :: RelToRec r => Rel r -> Record r Source

Copies all columns in the relation given. Useful for appending the remaining columns in a table to a projection. For example:

      query = do
        tbl <- table some_table
        project $ copyAll tbl

will add all columns in "some_table" to the query.

class RelToRec a Source

Helper class which gives a polymorphic copy function that can turn a Rel into a Record.

Minimal complete definition

relToRec

Instances

RelToRec RecNil Source |
(RelToRec rest, FieldTag f) => RelToRec (RecCons f (Expr a) rest) Source |

# Operators

(.==.) :: Eq a => Expr a -> Expr a -> Expr Bool infix 4 Source

Equality comparison on Exprs, = in SQL.

(.<>.) :: Eq a => Expr a -> Expr a -> Expr Bool infix 4 Source

Inequality on Exprs, <> in SQL.

(.<.) :: Ord a => Expr a -> Expr a -> Expr Bool infix 4 Source

(.<=.) :: Ord a => Expr a -> Expr a -> Expr Bool infix 4 Source

(.>.) :: Ord a => Expr a -> Expr a -> Expr Bool infix 4 Source

(.>=.) :: Ord a => Expr a -> Expr a -> Expr Bool infix 4 Source

(.&&.) :: Expr Bool -> Expr Bool -> Expr Bool infixr 3 Source

"Logical and" on `Expr`, AND in SQL.

(.||.) :: Expr Bool -> Expr Bool -> Expr Bool infixr 2 Source

"Logical or" on `Expr`. OR in SQL.

(.*.) :: Num a => Expr a -> Expr a -> Expr a infixl 7 Source

Multiplication

(./.) :: Num a => Expr a -> Expr a -> Expr a infixl 7 Source

Division

(.+.) :: Num a => Expr a -> Expr a -> Expr a infixl 6 Source

Addition

(.-.) :: Num a => Expr a -> Expr a -> Expr a infixl 6 Source

Subtraction

(.%.) :: Num a => Expr a -> Expr a -> Expr a infixl 7 Source

Modulo

(.++.) :: Expr String -> Expr String -> Expr String infixr 5 Source

Concatenates two String-expressions.

(<<) infix 6 Source

Arguments

:: Attr f a | Label
-> e a | Expression
-> Record (RecCons f (e a) RecNil) | New record

Creates a record field. Similar to '(.=.)', but gets the field label from an `Attr`.

(<<-) infix 6 Source

Arguments

:: ShowConstant a |
=> Attr f a | Field label
-> a | Field value
-> Record (RecCons f (Expr a) RecNil) | New record

Convenience operator for constructing records of constants. Useful primarily with `insert`. `f <<- x` is the same as `f << constant x`

# Function declarations

project :: (ShowLabels r, ToPrimExprs r, ProjectRec r er) => Record r -> Query (Rel er) Source

Specifies a subset of the columns in the table.

restrict :: Expr Bool -> Query () Source

Restricts the records to only those who evaluates the expression to True.

table :: ShowRecRow r => Table r -> Query (Rel r) Source

Return all records from a specific table.

unique :: Query () Source

Restricts the relation given to only return unique records. Upshot is all projected attributes will be `grouped`.

union :: Query (Rel r) -> Query (Rel r) -> Query (Rel r) Source

Return all records which are present in at least one of the relations.

unionAll :: Query (Rel r) -> Query (Rel r) -> Query (Rel r) Source

UNION ALL

intersect :: Query (Rel r) -> Query (Rel r) -> Query (Rel r) Source

Return all records which are present in both relations.

divide :: Query (Rel r) -> Query (Rel r) -> Query (Rel r) Source

Not in SQL92.

minus :: Query (Rel r) -> Query (Rel r) -> Query (Rel r) Source

Return all records from the first relation that are not present in the second relation.

_not :: Expr Bool -> Expr Bool Source

The inverse of an Expr Bool.

like :: Expr String -> Expr String -> Expr Bool infix 8 Source

The HaskellDB counterpart to the SQL LIKE keyword. In the expresions, % is a wildcard representing any characters in the same position relavtive to the given characters and _ is a wildcard representing one character e.g.

    like (constant "ABCDEFFF") (constant "AB%F_F")

is true while

    like (constant "ABCDEF") (constant "AC%F")

is false.

Note that SQL92 does not specify whether LIKE is case-sensitive or not. Different database systems implement this differently.

_in :: Eq a => Expr a -> [Expr a] -> Expr Bool infix 8 Source

Returns true if the value of the first operand is equal to the value of any of the expressions in the list operand.

cat :: Expr String -> Expr String -> Expr String Source

Produces the concatenation of two String-expressions.

_length :: Expr String -> Expr Int Source

Gets the length of a string.

isNull :: Expr a -> Expr Bool Source

Returns true if the expression is Null.

notNull :: Expr a -> Expr Bool Source

The inverse of `isNull`, returns false if the expression supplied is Null.

fromNull Source

Arguments

:: Expr a | Default value (to be returned for `Nothing`)
-> Expr (Maybe a) | A nullable expression
-> Expr a |

Takes a default value a and a nullable value. If the value is NULL, the default value is returned, otherwise the value itself is returned. Simliar to `fromMaybe`

fromVal :: ShowConstant a => a -> Expr (Maybe a) -> Expr a Source

Similar to fromNull, but takes a value argument rather than an Expr.

constant :: ShowConstant a => a -> Expr a Source

Creates a constant expression from a haskell value.

constVal :: ShowConstant a => a -> Expr (Maybe a) Source

Turn constant data into a nullable expression. Same as `constant . Just`

constNull :: Expr (Maybe a) Source

Represents a null value.

constExpr :: Expr a -> Expr (Maybe a) Source

Turn constant data into a nullable expression. Same as `constant . Just`

param Source

Arguments

:: Expr a | Default value.
-> Expr a |

Create an anonymous parameter with a default value.

namedParam Source

Arguments

:: Name | Name of the parameter.
-> Expr a | Default value for the parameter.
-> Expr a |

Create a named parameter with a default value.

class Args a Source

Used to implement variable length arguments to `func`, below.

Minimal complete definition

arg_

Instances

Args (Expr a) Source |
Args (Expr a -> ExprAggr c) Source |
(IsExpr tail, Args tail) => Args (Expr a -> tail) Source |

func :: Args a => String -> a Source

Can be used to define SQL functions which will appear in queries. Each argument for the function is specified by its own Expr value. Examples include:

     lower :: Expr a -> Expr (Maybe String)
     lower str = func "lower" str

The arguments to the function do not have to be Expr if they can be converted to Expr:

     data DatePart = Day | Century deriving Show

     datePart :: DatePart -> Expr (Maybe CalendarTime) -> Expr (Maybe Int)
     datePart date col = func "date_part" (constant $ show date) col

Aggregate functions can also be defined. For example:

     every :: Expr Bool -> ExprAggr Bool
     every col = func "every" col

Aggregates are implemented to always take one argument, so any attempt to define an aggregate with any more or less arguments will result in an error.

Note that type signatures are usually required for each function defined, unless the arguments can be inferred.

cast Source

Arguments

:: String | Destination type.
-> Expr a | Source expression.
-> Expr b |

Generates a `CAST` expression for the given expression, using the argument given as the destination type.

toStr :: BStrToStr s d => s -> d Source

Convert a bounded string to a real string.

coerce Source

Arguments

:: Expr a | Source expression
-> Expr b | Destination type.

Coerce the type of an expression to another type. Does not affect the actual primitive value - only the `phantom` type.

select :: HasField f r => Attr f a -> Rel r -> Expr a Source

count :: Expr a -> ExprAggr Int Source

Returns the number of records (=rows) in a query.

_sum :: Num a => Expr a -> ExprAggr a Source

Returns the total sum of a column.

_max :: Ord a => Expr a -> ExprAggr a Source

Returns the highest value of a column.

_min :: Ord a => Expr a -> ExprAggr a Source

Returns the lowest value of a column.

avg :: Num a => Expr a -> ExprAggr a Source

Returns the average of a column.

literal :: String -> Expr a Source

Inserts the string literally - no escaping, no quoting.

stddev :: Num a => Expr a -> ExprAggr a Source

Returns the standard deviation of a column.

stddevP :: Num a => Expr a -> ExprAggr a Source

variance :: Num a => Expr a -> ExprAggr a Source

Returns the standard variance of a column.

varianceP :: Num a => Expr a -> ExprAggr a Source

asc :: HasField f r => Rel r -> Attr f a -> OrderExpr Source

Use this together with the function `order` to order the results of a query in ascending order. Takes a relation and an attribute of that relation, which is used for the ordering.

desc :: HasField f r => Rel r -> Attr f a -> OrderExpr Source

Use this together with the function `order` to order the results of a query in descending order. Takes a relation and an attribute of that relation, which is used for the ordering.

order :: [OrderExpr] -> Query () Source

Order the results of a query. Use this with the `asc` or `desc` functions.

top :: Int -> Query () Source

Return the n topmost records.

offset :: Int -> Query () Source

Skip the n topmost records.

_case Source

Arguments

:: [(Expr Bool, Expr a)] | A list of conditions and expressions.
-> Expr a | Else-expression.
-> Expr a |

Creates a conditional expression. Returns the value of the expression corresponding to the first true condition. If none of the conditions are true, the value of the else-expression is returned.

_default :: ExprDefault a Source

The default value of the column. Only works with `insert`.

# Internals

runQuery :: Query (Rel r) -> PrimQuery Source

runQueryRel :: Query (Rel r) -> (PrimQuery, Rel r) Source

unQuery :: Query a -> a Source

subQuery :: Query (Rel r) -> Query (Rel r) Source

Allows a subquery to be created between another query and this query. Normally query definition is associative and query definition is interleaved. This combinator ensures the given query is added as a whole piece.

attribute :: String -> Expr a Source

attributeName :: Attr f a -> Attribute Source

tableName :: Table t -> TableName Source

Get the name of a table.

baseTable :: (ShowLabels r, ToPrimExprs r) => TableName -> Record r -> Table r Source

emptyTable :: TableName -> Table (Record RecNil) Source

For queries against fake tables, such as 'information_schema.information_schema_catalog_name'. Useful for constructing queries that contain constant data (and do not select from columns) but need a table to select from.

exprs :: ToPrimExprs r => Record r -> [PrimExpr] Source

labels :: ShowLabels r => r -> [String] Source

tableRec :: Table (Record r) -> Record r Source