persistent-1.1.5.1: Type-safe, multi-backend data serialization.

Safe Haskell | None

Database.Persist.GenericSql

Contents

- Useful data types
- Raw SQL queries
- Migrations

Description

This is a helper module for creating SQL backends. Regular users do not need to use this module.

Synopsis

- newtype SqlPersist m a = SqlPersist {
  - unSqlPersist :: ReaderT Connection m a
  }
- data Connection
- type ConnectionPool = Pool Connection
- data Statement
- runSqlConn :: MonadBaseControl IO m => SqlPersist m a -> Connection -> m a
- runSqlPool :: MonadBaseControl IO m => SqlPersist m a -> Pool Connection -> m a
- type Key val = KeyBackend (PersistEntityBackend val) val
- data Checkmark
  - = Active
  - | Inactive
- rawSql :: (RawSql a, MonadResource m, MonadLogger m) => Text -> [PersistValue] -> SqlPersist m [a]
- data Entity entity = Entity {
  - entityKey :: Key entity
  - entityVal :: entity
  }
- newtype Single a = Single {
  - unSingle :: a
  }
- class RawSql a
- type Migration m = WriterT [Text] (WriterT CautiousMigration m) ()
- parseMigration :: Monad m => Migration m -> m (Either [Text] CautiousMigration)
- parseMigration' :: Monad m => Migration m -> m CautiousMigration
- printMigration :: (MonadBaseControl IO m, MonadIO m) => Migration (SqlPersist m) -> SqlPersist m ()
- getMigration :: (MonadBaseControl IO m, MonadIO m) => Migration (SqlPersist m) -> SqlPersist m [Sql]
- runMigration :: (MonadIO m, MonadBaseControl IO m, MonadLogger m) => Migration (SqlPersist m) -> SqlPersist m ()
- runMigrationSilent :: (MonadBaseControl IO m, MonadIO m, MonadLogger m) => Migration (SqlPersist m) -> SqlPersist m [Text]
- runMigrationUnsafe :: (MonadBaseControl IO m, MonadIO m, MonadLogger m) => Migration (SqlPersist m) -> SqlPersist m ()
- migrate :: (MonadIO m, MonadBaseControl IO m, PersistEntity val) => [EntityDef] -> val -> Migration (SqlPersist m)
- commit :: MonadIO m => SqlPersist m ()
- rollback :: MonadIO m => SqlPersist m ()

# Documentation

newtype SqlPersist m a

Constructors

SqlPersist |
Fields unSqlPersist :: ReaderT Connection m a

Instances

MonadTrans SqlPersist |
MonadTransControl SqlPersist |
MonadBaseControl backend m => MonadBaseControl backend (SqlPersist m) |
MonadBase backend m => MonadBase backend (SqlPersist m) |
Monad m => Monad (SqlPersist m) |
Functor m => Functor (SqlPersist m) |
MonadPlus m => MonadPlus (SqlPersist m) |
Applicative m => Applicative (SqlPersist m) |
MonadThrow m => MonadThrow (SqlPersist m) |
MonadResource m => MonadResource (SqlPersist m) |
MonadLogger m => MonadLogger (SqlPersist m) |
MonadIO m => MonadIO (SqlPersist m) |
(MonadResource m, MonadLogger m) => PersistUnique (SqlPersist m) |
(MonadResource m, MonadLogger m) => PersistStore (SqlPersist m) |
(MonadIO m, MonadLogger m) => MonadSqlPersist (SqlPersist m) |
(MonadResource m, MonadLogger m) => PersistQuery (SqlPersist m) |

data Connection

type ConnectionPool = Pool Connection

data Statement

runSqlConn :: MonadBaseControl IO m => SqlPersist m a -> Connection -> m a

runSqlPool :: MonadBaseControl IO m => SqlPersist m a -> Pool Connection -> m a

Get a connection from the pool, run the given action, and then return the connection to the pool.

type Key val = KeyBackend (PersistEntityBackend val) val

Helper wrapper, equivalent to `Key (PersistEntityBackend val) val`.

Since 1.1.0

# Useful data types

data Checkmark

A `Checkmark` should be used as a field type whenever a uniqueness constraint should guarantee that a certain kind of record may appear at most once, but other kinds of records may appear any number of times.

NOTE: You need to mark any `Checkmark` fields as `nullable` (see the following example).

For example, suppose there's a `Location` entity that represents where a user has lived:

     Location
         user    UserId
         name    Text
         current Checkmark nullable

    UniqueLocation user current

The `UniqueLocation` constraint allows any number of `Inactive` `Location`s to be `current`. However, there may be at most one `current` `Location` per user (i.e., either zero or one per user).

This data type works because of the way that SQL treats `NULL`able fields within uniqueness constraints. The SQL standard says that `NULL` values should be considered different, so we represent `Inactive` as SQL `NULL`, thus allowing any number of `Inactive` records. On the other hand, we represent `Active` as `TRUE`, so the uniqueness constraint will disallow more than one `Active` record.

Note: There may be DBMSs that do not respect the SQL standard's treatment of `NULL` values on uniqueness constraints, please check if this data type works before relying on it.

The SQL `BOOLEAN` type is used because it's the smallest data type available. Note that we never use `FALSE`, just `TRUE` and `NULL`. Provides the same behavior `Maybe ()` would if `()` was a valid `PersistField`.

Constructors

Active | When used on a uniqueness constraint, there may be at most one `Active` record.
Inactive | When used on a uniqueness constraint, there may be any number of `Inactive` records.

Instances

Bounded Checkmark |
Enum Checkmark |
Eq Checkmark |
Ord Checkmark |
Read Checkmark |
Show Checkmark |
PathPiece Checkmark |
PersistField Checkmark |

# Raw SQL queries

Although it covers most of the useful cases, `persistent`'s API may not be enough for some of your tasks. May be you need some complex `JOIN` query, or a database-specific command needs to be issued.

To issue raw SQL queries you could use `withStmt`, which allows you to do anything you need. However, its API is low-level and you need to parse each row yourself. However, most of your complex queries will have simple results -- some of your entities and maybe a couple of derived columns.

This is where `rawSql` comes in. Like `withStmt`, you may issue any SQL query. However, it does all the hard work for you and automatically parses the rows of the result. It may return:

- An `Entity`, that which `selectList` returns. All of your entity's fields are automatically parsed.
- A ``Single` a`, which is a single, raw column of type `a`. You may use a Haskell type (such as in your entity definitions), for example `Single Text` or `Single Int`, or you may get the raw column value with `Single `PersistValue``.
- A tuple combining any of these (including other tuples). Using tuples allows you to return many entities in one query.

The only difference between issuing SQL queries with `rawSql` and using other means is that we have an entity selection placeholder, the double question mark `??`. It must be used whenever you want to `SELECT` an `Entity` from your query. Here's a sample SQL query `sampleStmt` that may be issued:

     SELECT ??, ??
     FROM "Person", "Likes", "Object"
     WHERE "Person".id = "Likes"."personId"
     AND "Object".id = "Likes"."objectId"
     AND "Person".name LIKE ?

To use that query, you could say

     do results <- rawSql sampleStmt ["%Luke%"]
        forM_ results $
          \( Entity personKey person
           , Entity objectKey object
           ) -> do ...

Note that `rawSql` knows how to replace the double question marks `??` because of the type of the `results`.

rawSql

Arguments

:: (RawSql a, MonadResource m, MonadLogger m) |
=> Text | SQL statement, possibly with placeholders.
-> [PersistValue] | Values to fill the placeholders.
-> SqlPersist m [a] |

Execute a raw SQL statement and return its results as a list.

If you're using `Entity` `s` (which is quite likely), then you must use entity selection placeholders (double question mark, `??`). These `??` placeholders are then replaced for the names of the columns that we need for your entities. You'll receive an error if you don't use the placeholders. Please see the `Entity` `s` documentation for more details.

You may put value placeholders (question marks, `?`) in your SQL query. These placeholders are then replaced by the values you pass on the second parameter, already correctly escaped. You may want to use `toPersistValue` to help you constructing the placeholder values.

Since you're giving a raw SQL statement, you don't get any guarantees regarding safety. If `rawSql` is not able to parse the results of your query back, then an exception is raised. However, most common problems are mitigated by using the entity selection placeholder `??`, and you shouldn't see any error at all if you're not using `Single`.

data Entity entity

Datatype that represents an entity, with both its key and its Haskell representation.

When using the an SQL-based backend (such as SQLite or PostgreSQL), an `Entity` may take any number of columns depending on how many fields it has. In order to reconstruct your entity on the Haskell side, `persistent` needs all of your entity columns and in the right order. Note that you don't need to worry about this when using `persistent`'s API since everything is handled correctly behind the scenes.

However, if you want to issue a raw SQL command that returns an `Entity`, then you have to be careful with the column order. While you could use `SELECT Entity.* WHERE ...` and that would work most of the time, there are times when the order of the columns on your database is different from the order that `persistent` expects (for example, if you add a new field in the middle of you entity definition and then use the migration code -- `persistent` will expect the column to be in the middle, but your DBMS will put it as the last column). So, instead of using a query like the one above, you may use `rawSql` (from the Database.Persist.GenericSql module) with its /entity selection placeholder/ (a double question mark `??`). Using `rawSql` the query above must be written as `SELECT ?? WHERE ..`. Then `rawSql` will replace `??` with the list of all columns that we need from your entity in the right order. If your query returns two entities (i.e. `(Entity backend a, Entity backend b)`), then you must you use `SELECT ??, ?? WHERE ...`, and so on.

Constructors

Entity |
Fields entityKey :: Key entity entityVal :: entity

Instances

Eq entity => Eq (Entity entity) |
Ord entity => Ord (Entity entity) |
Read entity => Read (Entity entity) |
Show entity => Show (Entity entity) |
PersistEntity a => RawSql (Entity a) |

newtype Single a

A single column (see `rawSql`). Any `PersistField` may be used here, including `PersistValue` (which does not do any processing).

Constructors

Single |
Fields unSingle :: a

Instances

Eq a => Eq (Single a) |
Ord a => Ord (Single a) |
Read a => Read (Single a) |
Show a => Show (Single a) |
PersistField a => RawSql (Single a) |

class RawSql a

Class for data types that may be retrived from a `rawSql` query.

Instances

RawSql a => RawSql (Maybe a) | Since 1.0.1.
PersistEntity a => RawSql (Entity a) |
PersistField a => RawSql (Single a) |
(RawSql a, RawSql b) => RawSql (a, b) |
(RawSql a, RawSql b, RawSql c) => RawSql (a, b, c) |
(RawSql a, RawSql b, RawSql c, RawSql d) => RawSql (a, b, c, d) |
(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e) => RawSql (a, b, c, d, e) |
(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e, RawSql f) => RawSql (a, b, c, d, e, f) |
(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e, RawSql f, RawSql g) => RawSql (a, b, c, d, e, f, g) |
(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e, RawSql f, RawSql g, RawSql h) => RawSql (a, b, c, d, e, f, g, h) |

# Migrations

type Migration m = WriterT [Text] (WriterT CautiousMigration m) ()

parseMigration :: Monad m => Migration m -> m (Either [Text] CautiousMigration)

parseMigration' :: Monad m => Migration m -> m CautiousMigration

printMigration :: (MonadBaseControl IO m, MonadIO m) => Migration (SqlPersist m) -> SqlPersist m ()

getMigration :: (MonadBaseControl IO m, MonadIO m) => Migration (SqlPersist m) -> SqlPersist m [Sql]

runMigration :: (MonadIO m, MonadBaseControl IO m, MonadLogger m) => Migration (SqlPersist m) -> SqlPersist m ()

runMigrationSilent :: (MonadBaseControl IO m, MonadIO m, MonadLogger m) => Migration (SqlPersist m) -> SqlPersist m [Text]

Same as `runMigration`, but returns a list of the SQL commands executed instead of printing them to stderr.

runMigrationUnsafe :: (MonadBaseControl IO m, MonadIO m, MonadLogger m) => Migration (SqlPersist m) -> SqlPersist m ()

migrate :: (MonadIO m, MonadBaseControl IO m, PersistEntity val) => [EntityDef] -> val -> Migration (SqlPersist m)

commit :: MonadIO m => SqlPersist m ()

Perform a database commit.

rollback :: MonadIO m => SqlPersist m ()

Perform a database rollback.