Database.HaskellDB.Query

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

Data and class declarations

data Rel r

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

Show (Query (Rel r))

Shows the optimized SQL for the query.

ExprTypes r => ExprTypes (Rel r)

ExprType a => ExprType (Rel a)

HasField f r => Select (Attr f a) (Rel r) (Expr a)

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

Typed attributes

Constructors

Attr Attribute

Instances

HasField f r => Select (Attr f a) (Rel r) (Expr a)

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

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

Constructors

Table TableName Assoc

data Query a

Instances

Monad Query

Functor Query

Applicative Query

Show (Query (Rel r))

Shows the optimized SQL for the query.

newtype Expr a

Type of normal expressions, contains the untyped PrimExpr.

Constructors

Expr PrimExpr

Instances

ProjectExpr Expr

ExprC Expr

Read (Expr a)

Show (Expr a)

Args (Expr a)

ExprType a => ExprType (Expr a)

Args (Expr a -> ExprAggr c)

(IsExpr tail, Args tail) => Args (Expr a -> tail)

HasField f r => Select (Attr f a) (Rel r) (Expr a)

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)

(ShowConstant a, ConstantRecord r cr) => ConstantRecord (RecCons f a r) (RecCons f (Expr a) cr)

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

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

(GetValue a, GetRec er vr) => GetRec (RecCons f (Expr a) er) (RecCons f a vr)

data OrderExpr

Instances

Show OrderExpr

class ToPrimExprs r

Minimal complete definition

toPrimExprs

Instances

ToPrimExprs RecNil

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

class ConstantRecord r cr | r -> cr where

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

Methods

constantRecord :: r -> cr

Instances

ConstantRecord RecNil RecNil

ConstantRecord r cr => ConstantRecord (Record r) (Record cr)

(ShowConstant a, ConstantRecord r cr) => ConstantRecord (RecCons f a r) (RecCons f (Expr a) cr)

class ShowConstant a where

Methods

showConstant :: a -> Literal

Instances

ShowConstant Bool

ShowConstant Double

ShowConstant Int

ShowConstant Integer

ShowConstant String

ShowConstant CalendarTime

ShowConstant a => ShowConstant (Maybe a)

Size n => ShowConstant (BoundedString n)

class ExprC e where

Class of expression types.

Methods

primExpr :: e a -> PrimExpr

Get the underlying untyped PrimExpr.

Instances

ExprC ExprDefault

ExprC ExprAggr

ExprC Expr

class ExprC e => ProjectExpr e

Class of expressions that can be used with project.

Instances

ProjectExpr ExprAggr

ProjectExpr Expr

class ProjectRec r er | r -> er

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

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

class InsertRec r er | r -> er

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

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

newtype ExprAggr a

Type of aggregate expressions.

Constructors

ExprAggr PrimExpr

Instances

ProjectExpr ExprAggr

ExprC ExprAggr

Read (ExprAggr a)

Show (ExprAggr a)

Args (Expr a -> ExprAggr c)

newtype ExprDefault a

The type of default expressions.

Constructors

ExprDefault PrimExpr

Instances

ExprC ExprDefault

Read (ExprDefault a)

Show (ExprDefault a)

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

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

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

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

Minimal complete definition

relToRec

Instances

RelToRec RecNil

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

Operators

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

Equality comparison on Exprs, = in SQL.

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

Inequality on Exprs, <> in SQL.

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

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

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

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

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

"Logical and" on Expr, AND in SQL.

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

"Logical or" on Expr. OR in SQL.

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

Multiplication

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

Division

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

Addition

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

Subtraction

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

Modulo

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

Concatenates two String-expressions.

(<<) infix 6

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

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)

Specifies a subset of the columns in the table.

restrict :: Expr Bool -> Query ()

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

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

Return all records from a specific table.

unique :: Query ()

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)

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

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

UNION ALL

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

Return all records which are present in both relations.

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

Not in SQL92.

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

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

_not :: Expr Bool -> Expr Bool

The inverse of an Expr Bool.

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

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

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

Produces the concatenation of two String-expressions.

_length :: Expr String -> Expr Int

Gets the length of a string.

isNull :: Expr a -> Expr Bool

Returns true if the expression is Null.

notNull :: Expr a -> Expr Bool

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

fromNull

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

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

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

Creates a constant expression from a haskell value.

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

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

constNull :: Expr (Maybe a)

Represents a null value.

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

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

param

Arguments

:: Expr a

Default value.

-> Expr a

Create an anonymous parameter with a default value.

namedParam

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

Used to implement variable length arguments to func, below.

Minimal complete definition

arg_

Instances

Args (Expr a)

Args (Expr a -> ExprAggr c)

(IsExpr tail, Args tail) => Args (Expr a -> tail)

func :: Args a => String -> a

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

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

Convert a bounded string to a real string.

coerce

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

count :: Expr a -> ExprAggr Int

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

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

Returns the total sum of a column.

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

Returns the highest value of a column.

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

Returns the lowest value of a column.

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

Returns the average of a column.

literal :: String -> Expr a

Inserts the string literally - no escaping, no quoting.

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

Returns the standard deviation of a column.

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

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

Returns the standard variance of a column.

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

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

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

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 ()

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

top :: Int -> Query ()

Return the n topmost records.

offset :: Int -> Query ()

Skip the n topmost records.

_case

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

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

Internals

runQuery :: Query (Rel r) -> PrimQuery

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

unQuery :: Query a -> a

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

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

attributeName :: Attr f a -> Attribute

tableName :: Table t -> TableName

Get the name of a table.

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

emptyTable :: TableName -> Table (Record RecNil)

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]

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

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