Database.Selda

Some monad with Selda SQL capabilitites.

Type of database backend used by m.

Thrown by any function in SeldaT if an error occurs.

An error occurred when validating a database table. If this error is thrown, there is a bug in your database schema, and the particular table that triggered the error is unusable. Since validation is deterministic, this error will be thrown on every consecutive operation over the offending table.

Therefore, it is not meaningful to handle this exception in any way, just fix your bug instead.

Monad transformer adding Selda SQL capabilities.

The simplest form of Selda computation; SeldaT specialized to IO.

Any type which has a corresponding relation. To make a Relational instance for some type, simply derive Generic.

Note that only types which have a single data constructor, and where all fields are instances of SqlValue can be used with this module. Attempting to use functions in this module with any type which doesn't obey those constraints will result in a very confusing type error.

Wrapper for single column tables. Use this when you need a table with only a single column, with table or selectValues.

A database table, based on some Haskell data type. Any single constructor type can form the basis of a table, as long as it derives Generic and all of its fields are instances of SqlType.

Name of the table. NOT guaranteed to be a valid SQL name.

An SQL query.

A database row. A row is a collection of one or more columns.

A database column. A column is often a literal column table, but can also be an expression over such a column or a constant expression.

An acceptable query result type; one or more columns stitched together with :*:.

Run a query within a Selda monad. In practice, this is often a SeldaT transformer on top of some other monad. Selda transformers are entered using backend-specific withX functions, such as withSQLite from the SQLite backend.

Perform the given query, and insert the result into the given table. Returns the number of inserted rows.

Perform the given computation atomically. If an exception is raised during its execution, the entire transaction will be rolled back and the exception re-thrown, even if the exception is caught and handled within the transaction.

Run the given computation as a transaction without enforcing foreign key constraints.

If the computation finishes with the database in an inconsistent state with regards to foreign keys, the resulting behavior is undefined. Use with extreme caution, preferably only for migrations.

On the PostgreSQL backend, at least PostgreSQL 9.6 is required.

Using this should be avoided in favor of deferred foreign key constraints. See SQL backend documentation for deferred constraints.

Generate a new random UUID using the system's random number generator. UUIDs generated this way are (astronomically likely to be) unique, but not necessarily unpredictable.

For applications where unpredictability is crucial, take care to use a proper cryptographic PRNG to generate your UUIDs.

Any datatype representable in (Selda's subset of) SQL.

Any type that's bounded, enumerable and has a text representation, and thus representable as a Selda enumerable.

While it would be more efficient to store enumerables as integers, this makes hand-rolled SQL touching the values inscrutable, and will break if the user a) derives Enum and b) changes the order of their constructors. Long-term, this should be implemented in PostgreSQL as a proper enum anyway, which mostly renders the performance argument moot.

Any column tuple.

Denotes that scopes s and t are identical.

The order in which to sort result rows.

An inductively defined "tuple", or heterogeneous, non-empty list.

Query the given table.

Query an ad hoc table of type a. Each element in the given list represents one row in the ad hoc table.

Convenient shorthand for fmap (! sel) q. The following two queries are quivalent:

q1 = name `from` select people
q2 = do
  person <- select people
  return (person ! name)

Remove all duplicates from the result set.

Restrict the query somehow. Roughly equivalent to WHERE.

Drop the first m rows, then get at most n of the remaining rows from the given subquery.

Sort the result rows in ascending or descending order on the given row.

If multiple order directives are given, later directives are given precedence but do not cancel out earlier ordering directives. To get a list of persons sorted primarily on age and secondarily on name:

peopleInAgeAndNameOrder = do
  person <- select people
  order (person ! name) ascending
  order (person ! age) ascending
  return (person ! name)

For a table [(Alice, 20), (Bob, 20), (Eve, 18)], this query will always return [Eve, Alice, Bob].

The reason for later orderings taking precedence and not the other way around is composability: order should always sort the current result set to avoid weird surprises when a previous order directive is buried somewhere deep in an earlier query. However, the ordering must always be stable, to ensure that previous calls to order are not simply erased.

Ordering for order.

Ordering for order.

Sort the result rows in random order.

The set union of two queries. Equivalent to the SQL UNION operator.

The multiset union of two queries. Equivalent to the SQL UNION ALL operator.

Explicitly create an inner query. Equivalent to innerJoin (const true).

Sometimes it's handy, for performance reasons and otherwise, to perform a subquery and restrict only that query before adding the result of the query to the result set, instead of first adding the query to the result set and restricting the whole result set afterwards.

Create and filter an inner query, before adding it to the current result set.

q suchThat p is generally more efficient than select q >>= x -> restrict (p x) >> pure x.

A column selector. Column selectors can be used together with the ! and with functions to get and set values on rows, or to specify foreign keys.

Coalesce nested nullable column into a single level of nesting.

Any table type t, which has a field named name.

The type of the name field, in the record type t.

Extract the given column from the given row.

Extract the given column from the given nullable row. Nullable rows usually result from left joins. If a nullable column is extracted from a nullable row, the resulting nested Maybes will be squashed into a single level of nesting.

A selector-value assignment pair.

For each selector-value pair in the given list, on the given tuple, update the field pointed out by the selector with the corresponding value.

Add the given column to the column pointed to by the given selector.

Subtract the given column from the column pointed to by the given selector.

Multiply the column pointed to by the given selector, by the given column.

Logically OR the column pointed to by the given selector with the given column.

Logically AND the column pointed to by the given selector with the given column.

Apply the given function to the given column.

Any container type for which we can check object membership.

The class of monoids (types with an associative binary operation that has an identity). Instances should satisfy the following:

Right identity

x <> mempty = x

Left identity

mempty <> x = x

Associativity

x <> (y <> z) = (x <> y) <> z

(

Semigroup

law)

Concatenation

mconcat = foldr (<>) mempty

You can alternatively define mconcat instead of mempty, in which case the laws are:

Unit

mconcat (pure x) = x

Multiplication

mconcat (join xss) = mconcat (fmap mconcat xss)

Subclass

mconcat (toList xs) = sconcat xs

The method names refer to the monoid of lists under concatenation, but there are many other instances.

Some types can be viewed as a monoid in more than one way, e.g. both addition and multiplication on numbers. In such cases we often define newtypes and make those instances of Monoid, e.g. Data.Semigroup.Sum and Data.Semigroup.Product.

NOTE: Semigroup is a superclass of Monoid since base-4.11.0.0.

The class of semigroups (types with an associative binary operation).

Instances should satisfy the following:

Associativity

x <> (y <> z) = (x <> y) <> z

You can alternatively define sconcat instead of (<>), in which case the laws are:

Unit

sconcat (pure x) = x

Multiplication

sconcat (join xss) = sconcat (fmap sconcat xss)

A typed row identifier. Generic tables should use this instead of RowID. Use untyped to erase the type of a row identifier, and cast from the Database.Selda.Unsafe module if you for some reason need to add a type to a row identifier.

A typed row identifier which is guaranteed to not match any row in any table.

Is the given typed row identifier invalid? I.e. is it guaranteed to not match any row in any table?

Create a typed row identifier from an integer. Use with caution, preferably only when reading user input.

Create a typed row identifier from an integer. Use with caution, preferably only when reading user input.

Any type which is backed by an UUID.

An UUID identifying a database row.

Convert an untyped UUID to a typed one. Use sparingly, preferably only during deserialization.

A row identifier for some table. This is the type of auto-incrementing primary keys.

A row identifier which is guaranteed to not match any row in any table.

Is the given row identifier invalid? I.e. is it guaranteed to not match any row in any table?

Inspect a row identifier.

Create a row identifier from an integer. Use with caution, preferably only when reading user input.

Comparisons over columns. Note that when comparing nullable (i.e. Maybe) columns, SQL NULL semantics are used. This means that comparing to a NULL field will remove the row in question from the current set. To test for NULL, use isNull instead of .== literal Nothing.

Comparisons over columns. Note that when comparing nullable (i.e. Maybe) columns, SQL NULL semantics are used. This means that comparing to a NULL field will remove the row in question from the current set. To test for NULL, use isNull instead of .== literal Nothing.

The SQL LIKE operator; matches strings with % wildcards. For instance:

"%gon" `like` "dragon" .== true

Boolean negation.

A literal expression.

Returns true if the given field in the given row is equal to the given literal.

Specialization of literal for integers.

Specialization of literal for doubles.

Specialization of literal for text.

True and false boolean literals.

True and false boolean literals.

SQL NULL, at any type you like.

Round a column to the given number of decimals places.

Calculate the length of a string column.

Is the given column null?

Perform a conditional on a column

If the second value is Nothing, return the first value. Otherwise return the second value.

Applies the given function to the given nullable column where it isn't null, and returns the given default value where it is.

This is the Selda equivalent of maybe.

Convert the given string to uppercase.

Convert the given string to lowercase.

Create a new row with the given fields. Any unassigned fields will contain their default values.

Create a new row from the given value. This can be useful when you want to update all or most of a row:

update users (#uid `is` user_id)
             (\old -> row user_info `with` [...])

Create a singleton table column from an appropriate value.

Any container type which can be mapped over. Sort of like Functor, if you squint a bit.

Round a value to the nearest integer. Equivalent to roundTo 0.

Lift a non-nullable column to a nullable one. Useful for creating expressions over optional columns:

data Person = Person {name :: Text, age :: Int, pet :: Maybe Text}
  deriving Generic
instance SqlRow Person

people :: Table Person
people = table "people" []

peopleWithCats = do
  person <- select people
  restrict (person ! #pet .== just "cat")
  return (person ! #name)

Convert a boolean column to any numeric type.

Convert an integer column to any numeric type.

Convert any SQL type to a string.

A single aggregate column. Aggregate columns may not be used to restrict queries. When returned from an aggregate subquery, an aggregate column is converted into a non-aggregate column.

One or more aggregate columns.

Convert one or more inner column to equivalent columns in the outer query. OuterCols (Aggr (Inner s) a :*: Aggr (Inner s) b) = Col s a :*: Col s b, for instance.

The results of a left join are always nullable, as there is no guarantee that all joined columns will be non-null. JoinCols a where a is an extensible tuple is that same tuple, but in the outer query and with all elements nullable. For instance:

 LeftCols (Col (Inner s) Int :*: Col (Inner s) Text)
   = Col s (Maybe Int) :*: Col s (Maybe Text)

Denotes an inner query. For aggregation, treating sequencing as the cartesian product of queries does not work well. Instead, we treat the sequencing of aggregate with other queries as the cartesian product of the aggregated result of the query, a small but important difference.

However, for this to work, the aggregate query must not depend on any columns in the outer product. Therefore, we let the aggregate query be parameterized over Inner s if the parent query is parameterized over s, to enforce this separation.

Any column type that can be used with the min_ and max_ functions.

Perform an INNER JOIN with the current result set and the given query.

Perform a LEFT JOIN with the current result set (i.e. the outer query) as the left hand side, and the given query as the right hand side. Like with aggregate, the inner (or right) query must not depend on the outer (or right) one.

The given predicate over the values returned by the inner query determines for each row whether to join or not. This predicate may depend on any values from the outer query.

For instance, the following will list everyone in the people table together with their address if they have one; if they don't, the address field will be NULL.

getAddresses :: Query s (Col s Text :*: Col s (Maybe Text))
getAddresses = do
  (name :*: _) <- select people
  (_ :*: address) <- leftJoin (\(n :*: _) -> n .== name)
                              (select addresses)
  return (name :*: address)

Execute a query, returning an aggregation of its results. The query must return an inductive tuple of Aggregate columns. When aggregate returns, those columns are converted into non-aggregate columns, which may then be used to further restrict the query.

Note that aggregate queries must not depend on outer queries, nor must they return any non-aggregate columns. Attempting to do either results in a type error.

The SQL HAVING keyword can be implemented by combining aggregate and restrict:

-- Find the number of people living on every address, for all addresses
-- with more than one tenant:
-- SELECT COUNT(name) AS c, address FROM housing GROUP BY name HAVING c > 1

numPpl = do
  (num_tenants :*: theAddress) <- aggregate $ do
    h <- select housing
    theAddress <- groupBy (h ! address)
    return (count (h ! address) :*: theAddress)
 restrict (num_tenants .> 1)
 return (num_tenants :*: theAddress)

Group an aggregate query by a column. Attempting to group a non-aggregate query is a type error. An aggregate representing the grouped-by column is returned, which can be returned from the aggregate query. For instance, if you want to find out how many people have a pet at home:

aggregate $ do
  person <- select people
  name' <- groupBy (person ! name)
  return (name' :*: count(person ! pet_name) .> 0)

The number of non-null values in the given column.

The average of all values in the given column.

Sum all values in the given column.

The greatest value in the given column. Texts are compared lexically.

The smallest value in the given column. Texts are compared lexically.

Insert the given values into the given table. All columns of the table must be present. If your table has an auto-incrementing primary key, use the special value def for that column to get the auto-incrementing behavior. Returns the number of rows that were inserted.

To insert a list of tuples into a table with auto-incrementing primary key:

data Person = Person
  { id :: ID Person
  , name :: Text
  , age :: Int
  , pet :: Maybe Text
  } deriving Generic
instance SqlResult Person

people :: Table Person
people = table "people" [autoPrimary :- id]

main = withSQLite "my_database.sqlite" $ do
  insert_ people
    [ Person def "Link" 125 (Just "horse")
    , Person def "Zelda" 119 Nothing
    , ...
    ]

Note that if one or more of the inserted rows would cause a constraint violation, NO rows will be inserted; the whole insertion fails atomically.

Like insert, but does not return anything. Use this when you really don't care about how many rows were inserted.

Like insert, but returns the primary key of the last inserted row. Attempting to run this operation on a table without an auto-incrementing primary key will always return a row identifier that is guaranteed to not match any row in any table.

Attempt to insert a list of rows into a table, but don't raise an error if the insertion fails. Returns True if the insertion succeeded, otherwise False.

Like insert, if even one of the inserted rows would cause a constraint violation, the whole insert operation fails.

Perform the given insert, if no rows already present in the table match the given predicate. Returns the primary key of the last inserted row, if the insert was performed. If called on a table which doesn't have an auto-incrementing primary key, Just id is always returned on successful insert, where id is a row identifier guaranteed to not match any row in any table.

Like insertUnless, but performs the insert when at least one row matches the predicate.

The default value for a column during insertion. For an auto-incrementing primary key, the default value is the next key.

Using def in any other context than insertion results in a runtime error.

Update the given table using the given update function, for all rows matching the given predicate. Returns the number of updated rows.

Like update, but doesn't return the number of updated rows.

Attempt to perform the given update. If no rows were updated, insert the given row. Returns the primary key of the inserted row, if the insert was performed. Calling this function on a table which does not have a primary key will return Just id on a successful insert, where id is a row identifier guaranteed to not match any row in any table.

Note that this may perform two separate queries: one update, potentially followed by one insert.

From the given table, delete all rows matching the given predicate. Returns the number of deleted rows.

Like deleteFrom, but does not return the number of deleted rows.

Some parameterized query q that can be prepared into a function f in some MonadSelda.

Create a prepared Selda function. A prepared function has zero or more arguments, and will get compiled into a prepared statement by the first backend to execute it. Any subsequent calls to the function for the duration of the connection to the database will reuse the prepared statement.

Preparable functions are of the form (SqlType a, SqlType b, ...) => Col s a -> Col s b -> ... -> Query s r. The resulting prepared function will be of the form MonadSelda m => a -> b -> ... -> m [Res r]. Note, however, that when using prepared, you must give a concrete type for m due to how Haskell's type class resolution works.

Prepared functions rely on memoization for just-in-time preparation and caching. This means that if GHC accidentally inlines your prepared function, it may get prepared twice. While this does not affect the correctness of your program, and is fairly unlikely to happen, if you want to be absolutely sure that your queries aren't re-prepared more than absolutely necessary, consider adding a NOINLINE annotation to each prepared function.

Note that when using a constrained backend type variable (i.e. foo :: Bar b => SeldaM b [Int]), optimizations must be enabled for prepared statements to be effective.

A usage example:

persons :: Table (Text, Int)
(persons, name :*: age) = tableWithSelectors "ages" [name :- primary]

{-# NOINLINE ageOf #-}
ageOf :: Text -> SeldaM [Int]
ageOf = prepared $ \n -> do
  person <- select ages
  restrict $ (person!name .== n)
  return age

Representable types of kind *. This class is derivable in GHC with the DeriveGeneric flag on.

A Generic instance must satisfy the following laws:

from . to ≡ id
to . from ≡ id