Database.Persist.GenericSql

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

Documentation

newtype SqlPersist m a

Constructors

SqlPersist

Fields

unSqlPersist :: ReaderT Connection m a

Instances

MonadTrans SqlPersist

MonadTransControl SqlPersist

(MonadBase backend (SqlPersist m), MonadBaseControl backend m) => MonadBaseControl backend (SqlPersist m)

(Applicative backend, Applicative (SqlPersist m), Monad backend, Monad (SqlPersist m), MonadBase backend m) => MonadBase backend (SqlPersist m)

Monad m => Monad (SqlPersist m)

Functor m => Functor (SqlPersist m)

(Monad (SqlPersist m), MonadPlus m) => MonadPlus (SqlPersist m)

(Functor (SqlPersist m), Applicative m) => Applicative (SqlPersist m)

(Monad (SqlPersist m), MonadThrow m) => MonadThrow (SqlPersist m)

(MonadThrow (SqlPersist m), MonadUnsafeIO (SqlPersist m), MonadIO (SqlPersist m), Applicative (SqlPersist m), MonadResource m) => MonadResource (SqlPersist m)

(Monad (SqlPersist m), MonadLogger m) => MonadLogger (SqlPersist m)

(Monad (SqlPersist m), MonadIO m) => MonadIO (SqlPersist m)

(PersistStore (SqlPersist m), MonadResource m, MonadLogger m) => PersistUnique (SqlPersist m)

(MonadIO (SqlPersist m), MonadResource m, MonadLogger m) => PersistStore (SqlPersist m)

(MonadIO (SqlPersist m), MonadLogger (SqlPersist m), MonadIO m, MonadLogger m) => MonadSqlPersist (SqlPersist m)

(PersistStore (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 Locations 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 NULLable 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 Entitys (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 Entitys 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)

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

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