| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.Selda
Contents
Description
Selda is not LINQ, but they're definitely related.
Selda is a high-level EDSL for interacting with relational databases.
All database computations are performed within some monad implementing
the MonadSelda type class. The SeldaT monad over any MonadIO is the
only pre-defined instance of MonadSelda.
SeldaM is provided as a convenient short-hand for SeldaT IO.
To actually execute a database computation, you need one of the database
backends: selda-sqlite or selda-postgresql.
All Selda functions may throw SeldaError when something goes wrong.
This includes database connection errors, uniqueness constraint errors,
etc.
The following example shows off Selda's most basic features -- creating, populating, modifying and querying tables -- and is intended to act as a Hello World-ish quickstart.
{-# LANGUAGE TypeOperators, OverloadedStrings #-}
import Data.Text (Text, unpack)
import Database.Selda
import Database.Selda.SQLite
people :: Table (Text :*: Int :*: Maybe Text)
(people, pName :*: pAge :*: pPet)
= tableWithSelectors "people"
$ primary "name"
:*: required "age"
:*: optional "pet"
main = withSQLite "people.sqlite" $ do
createTable people
insert_ people
[ "Velvet" :*: 19 :*: Nothing
, "Kobayashi" :*: 23 :*: Just "dragon"
, "Miyu" :*: 10 :*: Nothing
]
update_ people
(\person -> person ! pName .== "Velvet")
(\person -> person `with` [pPet := just "orthros"])
adults <- query $ do
person <- select people
restrict (person ! pAge .> 20)
return (person ! pName :*: person ! pAge)
n <- deleteFrom people (\person -> isNull (person ! pPet))
liftIO $ do
putStrLn "The adults in the room are:"
mapM_ printPerson adults
putStrLn $ show n ++ " people were deleted for having no pets."
printPerson :: Text :*: Int -> IO ()
printPerson (name :*: age) = putStrLn $ unpack name ++ ", age " ++ show agePlease see http://hackage.haskell.org/package/selda/#readme for a more comprehensive tutorial.
- class MonadIO m => MonadSelda m
- data SeldaError
- data ValidationError
- data SeldaT m a
- type SeldaM = SeldaT IO
- data Table a
- data Query s a
- data Col s a
- class Typeable (Res r) => Result r where
- type Res r
- query :: (MonadSelda m, Result a) => Query s a -> m [Res a]
- transaction :: (MonadSelda m, MonadCatch m) => m a -> m a
- setLocalCache :: MonadIO m => Int -> m ()
- data Selector t a
- (!) :: forall s t a. ToDyn (Cols () t) => Cols s t -> Selector t a -> Col s a
- data Assignment s t where
- (:=) :: Selector t a -> Col s a -> Assignment s t
- with :: forall s t. ToDyn (Cols () t) => Cols s t -> [Assignment s t] -> Cols s t
- class Typeable a => SqlType a where
- class (Typeable a, Bounded a, Enum a) => SqlEnum a where
- type family Cols s a where ...
- class Columns a
- data Order
- data a :*: b where
- select :: Columns (Cols s a) => Table a -> Query s (Cols s a)
- selectValues :: (Insert a, Columns (Cols s a)) => [a] -> Query s (Cols s a)
- from :: ToDyn (Cols () a) => Selector a b -> Query s (Cols s a) -> Query s (Col s b)
- distinct :: Query s a -> Query s a
- restrict :: Col s Bool -> Query s ()
- limit :: Int -> Int -> Query (Inner s) a -> Query s (OuterCols a)
- order :: Col s a -> Order -> Query s ()
- ascending :: Order
- descending :: Order
- inner :: (Columns a, Columns (OuterCols a)) => Query (Inner s) a -> Query s (OuterCols a)
- suchThat :: (Columns a, Columns (OuterCols a)) => Query (Inner s) a -> (a -> Col (Inner s) Bool) -> Query s (OuterCols a)
- class Set set where
- data RowID
- invalidRowId :: RowID
- isInvalidRowId :: RowID -> Bool
- fromRowId :: RowID -> Int
- (.==) :: SqlType a => Col s a -> Col s a -> Col s Bool
- (./=) :: SqlType a => Col s a -> Col s a -> Col s Bool
- (.>) :: SqlOrd a => Col s a -> Col s a -> Col s Bool
- (.<) :: SqlOrd a => Col s a -> Col s a -> Col s Bool
- (.>=) :: SqlOrd a => Col s a -> Col s a -> Col s Bool
- (.<=) :: SqlOrd a => Col s a -> Col s a -> Col s Bool
- like :: Col s Text -> Col s Text -> Col s Bool
- (.&&) :: Col s Bool -> Col s Bool -> Col s Bool
- (.||) :: Col s Bool -> Col s Bool -> Col s Bool
- not_ :: Col s Bool -> Col s Bool
- literal :: SqlType a => a -> Col s a
- int :: Int -> Col s Int
- float :: Double -> Col s Double
- text :: Text -> Col s Text
- true :: Col s Bool
- false :: Col s Bool
- null_ :: SqlType a => Col s (Maybe a)
- roundTo :: Col s Int -> Col s Double -> Col s Double
- length_ :: Col s Text -> Col s Int
- isNull :: Col s (Maybe a) -> Col s Bool
- ifThenElse :: Col s Bool -> Col s a -> Col s a -> Col s a
- matchNull :: SqlType a => Col s b -> (Col s a -> Col s b) -> Col s (Maybe a) -> Col s b
- round_ :: forall s a. (SqlType a, Num a) => Col s Double -> Col s a
- just :: SqlType a => Col s a -> Col s (Maybe a)
- fromBool :: (SqlType a, Num a) => Col s Bool -> Col s a
- fromInt :: (SqlType a, Num a) => Col s Int -> Col s a
- toString :: Col s a -> Col s Text
- data Aggr s a
- class Aggregates a
- type family OuterCols a where ...
- type family LeftCols a where ...
- data Inner s
- class SqlType a => SqlOrd a
- innerJoin :: (Columns a, Columns (OuterCols a)) => (OuterCols a -> Col s Bool) -> Query (Inner s) a -> Query s (OuterCols a)
- leftJoin :: (Columns a, Columns (OuterCols a), Columns (LeftCols a)) => (OuterCols a -> Col s Bool) -> Query (Inner s) a -> Query s (LeftCols a)
- aggregate :: (Columns (OuterCols a), Aggregates a) => Query (Inner s) a -> Query s (OuterCols a)
- groupBy :: Col (Inner s) a -> Query (Inner s) (Aggr (Inner s) a)
- count :: SqlType a => Col s a -> Aggr s Int
- avg :: (SqlType a, Num a) => Col s a -> Aggr s a
- sum_ :: (SqlType a, Num a) => Col s a -> Aggr s a
- max_ :: SqlOrd a => Col s a -> Aggr s a
- min_ :: SqlOrd a => Col s a -> Aggr s a
- class Insert a
- insert :: (MonadSelda m, Insert a) => Table a -> [a] -> m Int
- insert_ :: (MonadSelda m, Insert a) => Table a -> [a] -> m ()
- insertWithPK :: (MonadSelda m, Insert a) => Table a -> [a] -> m RowID
- tryInsert :: (MonadCatch m, MonadSelda m, Insert a) => Table a -> [a] -> m Bool
- insertUnless :: (MonadCatch m, MonadSelda m, Insert a, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> [a] -> m (Maybe RowID)
- insertWhen :: (MonadCatch m, MonadSelda m, Insert a, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> [a] -> m (Maybe RowID)
- def :: SqlType a => a
- update :: (MonadSelda m, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> (Cols s a -> Cols s a) -> m Int
- update_ :: (MonadSelda m, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> (Cols s a -> Cols s a) -> m ()
- upsert :: (MonadCatch m, MonadSelda m, Insert a, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> (Cols s a -> Cols s a) -> [a] -> m (Maybe RowID)
- deleteFrom :: (MonadSelda m, Columns (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> m Int
- deleteFrom_ :: (MonadSelda m, Columns (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> m ()
- class Preparable q
- class Prepare q f
- prepared :: (Preparable q, Prepare q f, Equiv q f) => q -> f
- class TableSpec a
- type family ColSpecs a where ...
- data ColSpec a
- data TableName
- data ColName
- class SqlType a => NonNull a
- type family IsNullable a where ...
- data Nullable
- data NotNullable
- class Append a b where
- type family a :++: b where ...
- type family Selectors t a where ...
- class HasSelectors t a
- table :: forall a. TableSpec a => TableName -> ColSpecs a -> Table a
- tableWithSelectors :: forall a. (TableSpec a, HasSelectors a a) => TableName -> ColSpecs a -> (Table a, Selectors a a)
- selectors :: forall a. HasSelectors a a => Table a -> Selectors a a
- required :: NonNull a => ColName -> ColSpec a
- optional :: NonNull a => ColName -> ColSpec (Maybe a)
- primary :: NonNull a => ColName -> ColSpec a
- autoPrimary :: ColName -> ColSpec RowID
- fk :: ColSpec a -> (Table t, Selector t a) -> ColSpec a
- optFk :: ColSpec (Maybe a) -> (Table t, Selector t a) -> ColSpec (Maybe a)
- unique :: SqlType a => ColSpec a -> ColSpec a
- createTable :: MonadSelda m => Table a -> m ()
- tryCreateTable :: MonadSelda m => Table a -> m ()
- validateTable :: MonadSelda m => Table a -> m ()
- dropTable :: MonadSelda m => Table a -> m ()
- tryDropTable :: MonadSelda m => Table a -> m ()
- data OnError
- compile :: Result a => Query s a -> (Text, [Param])
- compileCreateTable :: PPConfig -> OnError -> Table a -> Text
- compileDropTable :: OnError -> Table a -> Text
- compileInsert :: Insert a => PPConfig -> Table a -> [a] -> [(Text, [Param])]
- compileUpdate :: forall s a. (Columns (Cols s a), Result (Cols s a)) => PPConfig -> Table a -> (Cols s a -> Cols s a) -> (Cols s a -> Col s Bool) -> (Text, [Param])
- class Tup a
- type family Head a where ...
- first :: Tup a => a -> Head a
- second :: Tup b => (a :*: b) -> Head b
- third :: Tup c => (a :*: (b :*: c)) -> Head c
- fourth :: Tup d => (a :*: (b :*: (c :*: d))) -> Head d
- fifth :: Tup e => (a :*: (b :*: (c :*: (d :*: e)))) -> Head e
- sixth :: Tup f => (a :*: (b :*: (c :*: (d :*: (e :*: f))))) -> Head f
- seventh :: Tup g => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: g)))))) -> Head g
- eighth :: Tup h => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: (g :*: h))))))) -> Head h
- ninth :: Tup i => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: (h :*: (h :*: i)))))))) -> Head i
- tenth :: Tup j => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: (g :*: (h :*: (i :*: j))))))))) -> Head j
- class Monad m => MonadIO (m :: * -> *) where
- liftIO :: MonadIO m => forall a. IO a -> m a
- data Text :: *
- data Day :: *
- data TimeOfDay :: *
- data UTCTime :: *
Running queries
class MonadIO m => MonadSelda m Source #
Some monad with Selda SQL capabilitites.
Minimal complete definition
data SeldaError Source #
Thrown by any function in SeldaT if an error occurs.
Constructors
| DbError String | Unable to open or connect to database. |
| SqlError String | An error occurred while executing query. |
Instances
data ValidationError Source #
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.
Instances
Monad transformer adding Selda SQL capabilities.
Instances
| MonadTrans SeldaT Source # | |
| Monad m => Monad (SeldaT m) Source # | |
| Functor m => Functor (SeldaT m) Source # | |
| Monad m => Applicative (SeldaT m) Source # | |
| MonadIO m => MonadIO (SeldaT m) Source # | |
| MonadThrow m => MonadThrow (SeldaT m) Source # | |
| MonadCatch m => MonadCatch (SeldaT m) Source # | |
| MonadMask m => MonadMask (SeldaT m) Source # | |
| (MonadIO m, MonadMask m) => MonadSelda (SeldaT m) Source # | |
A database table.
Tables are parameterized over their column types. For instance, a table
containing one string and one integer, in that order, would have the type
Table (Text :*: Int), and a table containing only a single string column
would have the type Table Text.
Table and column names may contain any character except NUL, and be
non-empty. Column names must be unique per table.
An SQL query.
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.
Instances
| Columns b => Columns ((:*:) (Col k s a) b) Source # | |
| (SqlType a, Result b) => Result ((:*:) (Col * s a) b) Source # | |
| (SqlType a, Preparable b) => Preparable (Col * s a -> b) Source # | |
| Fractional (Col k s (Maybe Int)) Source # | |
| Fractional (Col k s Int) Source # | |
| Fractional (Col k s (Maybe Double)) Source # | |
| Fractional (Col k s Double) Source # | |
| (SqlType a, Num a) => Num (Col k s (Maybe a)) Source # | |
| (SqlType a, Num a) => Num (Col k s a) Source # | |
| IsString (Col k s Text) Source # | |
| Columns (Col k s a) Source # | |
| SqlType a => Result (Col * s a) Source # | |
| type Res ((:*:) (Col * s a) b) Source # | |
| type Res (Col * s a) Source # | |
class Typeable (Res r) => Result r Source #
An acceptable query result type; one or more columns stitched together
with :*:.
Minimal complete definition
toRes, finalCols
query :: (MonadSelda m, Result a) => Query s a -> m [Res a] Source #
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.
transaction :: (MonadSelda m, MonadCatch m) => m a -> m a Source #
Perform the given computation atomically. If an exception is raised during its execution, the enture transaction will be rolled back, and the exception re-thrown.
setLocalCache :: MonadIO m => Int -> m () Source #
Set the maximum local cache size to n. A cache size of zero disables
local cache altogether. Changing the cache size will also flush all
entries. Note that the cache is shared among all Selda computations running
within the same process.
By default, local caching is turned off.
WARNING: local caching is guaranteed to be consistent with the underlying database, ONLY under the assumption that no other process will modify it. Also note that the cache is shared between ALL Selda computations running within the same process.
Constructing queries
(!) :: forall s t a. ToDyn (Cols () t) => Cols s t -> Selector t a -> Col s a Source #
Get the value at the given index from the given inductive tuple.
data Assignment s t where Source #
A selector-value assignment pair.
Constructors
| (:=) :: Selector t a -> Col s a -> Assignment s t infixl 2 |
with :: forall s t. ToDyn (Cols () t) => Cols s t -> [Assignment s t] -> Cols s t Source #
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.
class Typeable a => SqlType a where Source #
Any datatype representable in (Selda's subset of) SQL.
Minimal complete definition
Methods
Create a literal of this type.
sqlType :: Proxy a -> SqlTypeRep Source #
The SQL representation for this type.
fromSql :: SqlValue -> a Source #
Convert an SqlValue into this type.
defaultValue :: Lit a Source #
Default value when using def at this type.
Instances
| SqlType Bool Source # | |
| SqlType Double Source # | |
| SqlType Int Source # | |
| (Typeable * a, SqlEnum a) => SqlType a Source # | |
| SqlType ByteString Source # | |
| SqlType ByteString Source # | |
| SqlType Text Source # | |
| SqlType TimeOfDay Source # | |
| SqlType UTCTime Source # | |
| SqlType Day Source # | |
| SqlType RowID Source # | |
| SqlType a => SqlType (Maybe a) Source # | |
class (Typeable a, Bounded a, Enum a) => SqlEnum a where Source #
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.
Minimal complete definition
toTup, fromTup
The order in which to sort result rows.
data a :*: b where infixr 1 Source #
An inductively defined "tuple", or heterogeneous, non-empty list.
Instances
| (Typeable * a, HasSelectors t b) => HasSelectors t ((:*:) a b) Source # | |
| (Eq a, Eq b) => Eq ((:*:) a b) Source # | |
| (Ord a, Ord b) => Ord ((:*:) a b) Source # | |
| (Show a, Show b) => Show ((:*:) a b) Source # | |
| Generic ((:*:) a b) Source # | |
| Tup ((:*:) a b) Source # | |
| TableSpec b => TableSpec ((:*:) a b) Source # | |
| (SqlType a, Insert b) => Insert ((:*:) a b) Source # | |
| Columns b => Columns ((:*:) (Col k s a) b) Source # | |
| Aggregates b => Aggregates ((:*:) (Aggr (Inner s) a) b) Source # | |
| (SqlType a, Result b) => Result ((:*:) (Col * s a) b) Source # | |
| Append b c => Append ((:*:) a b) c Source # | |
| type Rep ((:*:) a b) Source # | |
| type Res ((:*:) (Col * s a) b) Source # | |
select :: Columns (Cols s a) => Table a -> Query s (Cols s a) Source #
Query the given table. Result is returned as an inductive tuple, i.e.
first :*: second :*: third <- query tableOfThree.
selectValues :: (Insert a, Columns (Cols s a)) => [a] -> Query s (Cols s a) Source #
Query an ad hoc table of type a. Each element in the given list represents
one row in the ad hoc table.
from :: ToDyn (Cols () a) => Selector a b -> Query s (Cols s a) -> Query s (Col s b) infixr 7 Source #
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)
restrict :: Col s Bool -> Query s () Source #
Restrict the query somehow. Roughly equivalent to WHERE.
limit :: Int -> Int -> Query (Inner s) a -> Query s (OuterCols a) Source #
Drop the first m rows, then get at most n of the remaining rows from the
given subquery.
order :: Col s a -> Order -> Query s () Source #
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 (name :*: age) <- select people order name ascending order age ascending return 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.
descending :: Order Source #
Ordering for order.
inner :: (Columns a, Columns (OuterCols a)) => Query (Inner s) a -> Query s (OuterCols a) Source #
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.
suchThat :: (Columns a, Columns (OuterCols a)) => Query (Inner s) a -> (a -> Col (Inner s) Bool) -> Query s (OuterCols a) infixr 7 Source #
Create and filter an inner query, before adding it to the current result set.
q is generally more efficient than
suchThat pselect q >>= x -> restrict (p x) >> pure x.
Expressions over columns
Any container type for which we can check object membership.
Minimal complete definition
A row identifier for some table. This is the type of auto-incrementing primary keys.
invalidRowId :: RowID Source #
A row identifier which is guaranteed to not match any row in any table.
isInvalidRowId :: RowID -> Bool Source #
Is the given row identifier invalid? I.e. is it guaranteed to not match any row in any table?
like :: Col s Text -> Col s Text -> Col s Bool infixl 4 Source #
The SQL LIKE operator; matches strings with % wildcards.
For instance:
"%gon" `like` "dragon" .== true
roundTo :: Col s Int -> Col s Double -> Col s Double Source #
Round a column to the given number of decimals places.
matchNull :: SqlType a => Col s b -> (Col s a -> Col s b) -> Col s (Maybe a) -> Col s b Source #
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.
Converting between column types
round_ :: forall s a. (SqlType a, Num a) => Col s Double -> Col s a Source #
Round a value to the nearest integer. Equivalent to roundTo 0.
just :: SqlType a => Col s a -> Col s (Maybe a) Source #
Lift a non-nullable column to a nullable one. Useful for creating expressions over optional columns:
people :: Table (Text :*: Int :*: Maybe Text) people = table "people" $ required "name" :*: required "age" :*: optional "pet" peopleWithCats = do name :*: _ :*: pet <- select people restrict (pet .== just "cat") return name
fromBool :: (SqlType a, Num a) => Col s Bool -> Col s a Source #
Convert a boolean column to any numeric type.
fromInt :: (SqlType a, Num a) => Col s Int -> Col s a Source #
Convert an integer column to any numeric type.
Inner queries
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.
Instances
| Aggregates b => Aggregates ((:*:) (Aggr (Inner s) a) b) Source # | |
| Aggregates (Aggr (Inner s) a) Source # | |
class Aggregates a Source #
One or more aggregate columns.
Minimal complete definition
unAggrs
Instances
| Aggregates b => Aggregates ((:*:) (Aggr (Inner s) a) b) Source # | |
| Aggregates (Aggr (Inner s) a) Source # | |
type family OuterCols a where ... Source #
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.
type family LeftCols a where ... Source #
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.
Instances
| Aggregates b => Aggregates ((:*:) (Aggr (Inner s) a) b) Source # | |
| Aggregates (Aggr (Inner s) a) Source # | |
Arguments
| :: (Columns a, Columns (OuterCols a)) | |
| => (OuterCols a -> Col s Bool) | Predicate determining which lines to join. | Right-hand query to join. |
| -> Query (Inner s) a | |
| -> Query s (OuterCols a) |
Perform an INNER JOIN with the current result set and the given query.
Arguments
| :: (Columns a, Columns (OuterCols a), Columns (LeftCols a)) | |
| => (OuterCols a -> Col s Bool) | Predicate determining which lines to join. | Right-hand query to join. |
| -> Query (Inner s) a | |
| -> Query s (LeftCols a) |
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)aggregate :: (Columns (OuterCols a), Aggregates a) => Query (Inner s) a -> Query s (OuterCols a) Source #
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 :*: address) <- aggregate $ do
(_ :*: address) <- select housing
groupBy address
return (count address :*: some address)
restrict (num_tenants .> 1)
return (num_tenants :*: address)groupBy :: Col (Inner s) a -> Query (Inner s) (Aggr (Inner s) a) Source #
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 (name :*: pet_name) <- select people name' <- groupBy name return (name' :*: count(pet_name) > 0)
count :: SqlType a => Col s a -> Aggr s Int Source #
The number of non-null values in the given column.
avg :: (SqlType a, Num a) => Col s a -> Aggr s a Source #
The average of all values in the given column.
max_ :: SqlOrd a => Col s a -> Aggr s a Source #
The greatest value in the given column. Texts are compared lexically.
min_ :: SqlOrd a => Col s a -> Aggr s a Source #
The smallest value in the given column. Texts are compared lexically.
Modifying tables
An inductive tuple of Haskell-level values (i.e. Int :*: Maybe Text)
which can be inserted into a table.
Minimal complete definition
params
insert :: (MonadSelda m, Insert a) => Table a -> [a] -> m Int Source #
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:
people :: Table (Int :*: Text :*: Int :*: Maybe Text)
people = table "ppl"
$ autoPrimary "id"
:*: required "name"
:*: required "age"
:*: optional "pet"
main = withSQLite "my_database.sqlite" $ do
insert_ people
[ def :*: "Link" :*: 125 :*: Just "horse"
, 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.
insert_ :: (MonadSelda m, Insert a) => Table a -> [a] -> m () Source #
Like insert, but does not return anything.
Use this when you really don't care about how many rows were inserted.
insertWithPK :: (MonadSelda m, Insert a) => Table a -> [a] -> m RowID Source #
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.
tryInsert :: (MonadCatch m, MonadSelda m, Insert a) => Table a -> [a] -> m Bool Source #
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.
insertUnless :: (MonadCatch m, MonadSelda m, Insert a, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> [a] -> m (Maybe RowID) Source #
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.
insertWhen :: (MonadCatch m, MonadSelda m, Insert a, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> [a] -> m (Maybe RowID) Source #
Like insertUnless, but performs the insert when at least one row matches
the predicate.
def :: SqlType a => a Source #
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.
Arguments
| :: (MonadSelda m, Columns (Cols s a), Result (Cols s a)) | |
| => Table a | The table to update. |
| -> (Cols s a -> Col s Bool) | Predicate. |
| -> (Cols s a -> Cols s a) | Update function. |
| -> m Int |
Update the given table using the given update function, for all rows matching the given predicate. Returns the number of updated rows.
update_ :: (MonadSelda m, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> (Cols s a -> Cols s a) -> m () Source #
Like update, but doesn't return the number of updated rows.
upsert :: (MonadCatch m, MonadSelda m, Insert a, Columns (Cols s a), Result (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> (Cols s a -> Cols s a) -> [a] -> m (Maybe RowID) Source #
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.
deleteFrom :: (MonadSelda m, Columns (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> m Int Source #
From the given table, delete all rows matching the given predicate. Returns the number of deleted rows.
deleteFrom_ :: (MonadSelda m, Columns (Cols s a)) => Table a -> (Cols s a -> Col s Bool) -> m () Source #
Like deleteFrom, but does not return the number of deleted rows.
Prepared statements
class Preparable q Source #
Minimal complete definition
mkQuery
Instances
| (SqlType a, Preparable b) => Preparable (Col * s a -> b) Source # | |
| Result a => Preparable (Query s a) Source # | |
Some parameterized query q that can be prepared into a function f
in some MonadSelda.
Minimal complete definition
mkFun
prepared :: (Preparable q, Prepare q f, Equiv q f) => q -> f Source #
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.
A usage example:
ages :: Table (Text :*: Int)
ages = table "ages" $ primary "name" :*: required "age"
{-# NOINLINE ageOf #-}
ageOf :: Text -> SeldaM [Int]
ageOf = prepared $ \n -> do
(name :*: age) <- select ages
restrict $ name .== n
return ageDefining schemas
An inductive tuple forming a table specification.
Minimal complete definition
mergeSpecs
type family ColSpecs a where ... Source #
An inductive tuple where each element is a column specification.
Name of a database table.
Name of a database column.
class SqlType a => NonNull a Source #
Any SQL type which is NOT nullable.
Instances
| (SqlType a, (~) * (IsNullable a) NotNullable) => NonNull a Source # | |
type family IsNullable a where ... Source #
Is the given type nullable?
Equations
| IsNullable (Maybe a) = Nullable | |
| IsNullable a = NotNullable |
Used by IsNullable to indicate a nullable type.
data NotNullable Source #
Used by IsNullable to indicate a nullable type.
type family a :++: b where ... Source #
Normalized append of two inductive tuples. Note that this will flatten any nested inductive tuples.
class HasSelectors t a Source #
Any table type that can have selectors generated.
Minimal complete definition
mkSel
Instances
| (~) * (Selectors t a) (Selector t a) => HasSelectors t a Source # | |
| (Typeable * a, HasSelectors t b) => HasSelectors t ((:*:) a b) Source # | |
table :: forall a. TableSpec a => TableName -> ColSpecs a -> Table a Source #
A table with the given name and columns.
tableWithSelectors :: forall a. (TableSpec a, HasSelectors a a) => TableName -> ColSpecs a -> (Table a, Selectors a a) Source #
A pair of the table with the given name and columns, and all its selectors. For example:
tbl :: Table (Int :*: Text) (tbl, tblBar :*: tblBaz) = tableWithSelectors "foo" $ required "bar" :*: required "baz" q :: Query s Text q = tblBaz <$> select tbl
selectors :: forall a. HasSelectors a a => Table a -> Selectors a a Source #
Generate selector functions for the given table. Selectors can be used to access the fields of a query result tuple, avoiding the need to pattern match on the entire tuple.
tbl :: Table (Int :*: Text) tbl = table "foo" $ required "bar" :*: required "baz" (tblBar :*: tblBaz) = selectors tbl q :: Query s Text q = tblBaz <$> select tbl
optional :: NonNull a => ColName -> ColSpec (Maybe a) Source #
A nullable column with the given name.
primary :: NonNull a => ColName -> ColSpec a Source #
Marks the given column as the table's primary key.
A table may only have one primary key; marking more than one key as
primary will result in ValidationError during validation.
autoPrimary :: ColName -> ColSpec RowID Source #
Automatically increment the given attribute if not specified during insert.
Also adds the PRIMARY KEY and UNIQUE attributes on the column.
fk :: ColSpec a -> (Table t, Selector t a) -> ColSpec a Source #
Add a foreign key constraint to the given column, referencing
the column indicated by the given table and selector.
If the referenced column is not a primary key or has a
uniqueness constraint, a ValidationError will be thrown
during validation.
optFk :: ColSpec (Maybe a) -> (Table t, Selector t a) -> ColSpec (Maybe a) Source #
Like fk, but for nullable foreign keys.
unique :: SqlType a => ColSpec a -> ColSpec a Source #
Add a uniqueness constraint to the given column. Adding a uniqueness constraint to a column that is already implied to be unique, such as a primary key, is a no-op.
Creating and dropping tables
createTable :: MonadSelda m => Table a -> m () Source #
Create a table from the given schema.
tryCreateTable :: MonadSelda m => Table a -> m () Source #
Create a table from the given schema, unless it already exists.
validateTable :: MonadSelda m => Table a -> m () Source #
Validate a table schema.
Throws a ValidationError if the schema does not validate.
Currently does not check the schema against what's actually in the
current database.
dropTable :: MonadSelda m => Table a -> m () Source #
Drop the given table.
tryDropTable :: MonadSelda m => Table a -> m () Source #
Drop the given table, if it exists.
Compiling and inspecting queries
compile :: Result a => Query s a -> (Text, [Param]) Source #
Compile a query into a parameterised SQL statement.
The types given are tailored for SQLite. To translate SQLite types into
whichever types are used by your backend, use compileWith.
compileCreateTable :: PPConfig -> OnError -> Table a -> Text Source #
Compile a CREATE TABLE query from a table definition.
compileInsert :: Insert a => PPConfig -> Table a -> [a] -> [(Text, [Param])] Source #
Compile an INSERT query, given the keyword representing default values
in the target SQL dialect, a table and a list of items corresponding
to the table.
Arguments
| :: (Columns (Cols s a), Result (Cols s a)) | |
| => PPConfig | SQL pretty-printer config. |
| -> Table a | The table to update. |
| -> (Cols s a -> Cols s a) | Update function. |
| -> (Cols s a -> Col s Bool) | Predicate: update only when true. |
| -> (Text, [Param]) |
Compile an UPDATE query.
Tuple convenience functions
Minimal complete definition
tupHead
fourth :: Tup d => (a :*: (b :*: (c :*: d))) -> Head d Source #
Get the fourth element of an inductive tuple.
fifth :: Tup e => (a :*: (b :*: (c :*: (d :*: e)))) -> Head e Source #
Get the fifth element of an inductive tuple.
sixth :: Tup f => (a :*: (b :*: (c :*: (d :*: (e :*: f))))) -> Head f Source #
Get the sixth element of an inductive tuple.
seventh :: Tup g => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: g)))))) -> Head g Source #
Get the seventh element of an inductive tuple.
eighth :: Tup h => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: (g :*: h))))))) -> Head h Source #
Get the eighth element of an inductive tuple.
ninth :: Tup i => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: (h :*: (h :*: i)))))))) -> Head i Source #
Get the ninth element of an inductive tuple.
tenth :: Tup j => (a :*: (b :*: (c :*: (d :*: (e :*: (f :*: (g :*: (h :*: (i :*: j))))))))) -> Head j Source #
Get the tenth element of an inductive tuple.
Useful re-exports
class Monad m => MonadIO (m :: * -> *) where #
Monads in which IO computations may be embedded.
Any monad built by applying a sequence of monad transformers to the
IO monad will be an instance of this class.
Instances should satisfy the following laws, which state that liftIO
is a transformer of monads:
Minimal complete definition
Instances
| MonadIO IO | Since: 4.9.0.0 |
| MonadIO m => MonadIO (ListT m) | |
| MonadIO m => MonadIO (MaybeT m) | |
| MonadIO m => MonadIO (SeldaT m) # | |
| (Error e, MonadIO m) => MonadIO (ErrorT e m) | |
| MonadIO m => MonadIO (ExceptT e m) | |
| MonadIO m => MonadIO (StateT s m) | |
| MonadIO m => MonadIO (StateT s m) | |
| (Monoid w, MonadIO m) => MonadIO (WriterT w m) | |
| (Monoid w, MonadIO m) => MonadIO (WriterT w m) | |
| MonadIO m => MonadIO (IdentityT * m) | |
| MonadIO m => MonadIO (ContT * r m) | |
| MonadIO m => MonadIO (ReaderT * r m) | |
| (Monoid w, MonadIO m) => MonadIO (RWST r w s m) | |
| (Monoid w, MonadIO m) => MonadIO (RWST r w s m) | |
A space efficient, packed, unboxed Unicode text type.
The Modified Julian Day is a standard count of days, with zero being the day 1858-11-17.
Time of day as represented in hour, minute and second (with picoseconds), typically used to express local time of day.
This is the simplest representation of UTC. It consists of the day number, and a time offset from midnight. Note that if a day has a leap second added to it, it will have 86401 seconds.