Database.Persist.Sql

Documentation

data InsertSqlResult

Constructors

ISRSingle Text

ISRInsertGet Text Text

ISRManyKeys Text [PersistValue]

type Connection = SqlBackend

Deprecated: Please use SqlBackend instead

Deprecated synonym for SqlBackend.

data SqlBackend

Constructors

SqlBackend

Fields

connPrepare :: Text -> IO Statement

connInsertSql :: EntityDef -> [PersistValue] -> InsertSqlResult

table name, column names, id name, either 1 or 2 statements to run

connInsertManySql :: Maybe (EntityDef -> [[PersistValue]] -> InsertSqlResult)

SQL for inserting many rows and returning their primary keys, for backends that support this functioanlity. If Nothing, rows will be inserted one-at-a-time using connInsertSql.

connStmtMap :: IORef (Map Text Statement)

connClose :: IO ()

connMigrateSql :: [EntityDef] -> (Text -> IO Statement) -> EntityDef -> IO (Either [Text] [(Bool, Text)])

connBegin :: (Text -> IO Statement) -> IO ()

connCommit :: (Text -> IO Statement) -> IO ()

connRollback :: (Text -> IO Statement) -> IO ()

connEscapeName :: DBName -> Text

connNoLimit :: Text

connRDBMS :: Text

connLimitOffset :: (Int, Int) -> Bool -> Text -> Text

connLogFunc :: LogFunc

Instances

HasPersistBackend SqlBackend SqlBackend

data BackendKey SqlBackend = SqlBackendKey {

• unSqlBackendKey :: Int64

}

type LogFunc = Loc -> LogSource -> LogLevel -> LogStr -> IO ()

data Statement

Constructors

Statement

Fields

stmtFinalize :: IO ()

stmtReset :: IO ()

stmtExecute :: [PersistValue] -> IO Int64

stmtQuery :: forall m. MonadIO m => [PersistValue] -> Acquire (Source m [PersistValue])

data Column

Constructors

Column

Fields

cName :: !DBName

cNull :: !Bool

cSqlType :: !SqlType

cDefault :: !(Maybe Text)

cDefaultConstraintName :: !(Maybe DBName)

cMaxLen :: !(Maybe Integer)

cReference :: !(Maybe (DBName, DBName))

Instances

Eq Column

Ord Column

Show Column

data PersistentSqlException

Constructors

StatementAlreadyFinalized Text

Couldn'tGetSQLConnection

Instances

Show PersistentSqlException

Exception PersistentSqlException

type SqlPersistT = ReaderT SqlBackend

type SqlPersist = SqlPersistT

Deprecated: Please use SqlPersistT instead

type SqlPersistM = SqlPersistT (NoLoggingT (ResourceT IO))

type Sql = Text

type CautiousMigration = [(Bool, Sql)]

type Migration = WriterT [Text] (WriterT CautiousMigration (ReaderT SqlBackend IO)) ()

type ConnectionPool = Pool SqlBackend

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.

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 where

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

Methods

rawSqlCols :: (DBName -> Text) -> a -> (Int, [Text])

Number of columns that this data type needs and the list of substitutions for SELECT placeholders ??.

rawSqlColCountReason :: a -> String

A string telling the user why the column count is what it is.

rawSqlProcessRow :: [PersistValue] -> Either Text a

Transform a row of the result into the data type.

Instances

RawSql a => RawSql (Maybe a)

Since 1.0.1.

(PersistEntity record, (~) * (PersistEntityBackend record) SqlBackend) => RawSql (Entity record)

(PersistEntity a, (~) * (PersistEntityBackend a) SqlBackend) => RawSql (Key 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)

class PersistField a => PersistFieldSql a where

Methods

sqlType :: Proxy a -> SqlType

Instances

PersistFieldSql Bool

PersistFieldSql Double

PersistFieldSql Int

PersistFieldSql Int8

PersistFieldSql Int16

PersistFieldSql Int32

PersistFieldSql Int64

PersistFieldSql Rational

PersistFieldSql Word

PersistFieldSql Word8

PersistFieldSql Word16

PersistFieldSql Word32

PersistFieldSql Word64

PersistFieldSql String

PersistFieldSql ByteString

PersistFieldSql Text

PersistFieldSql UTCTime

PersistFieldSql Text

PersistFieldSql Html

PersistFieldSql TimeOfDay

PersistFieldSql Day

PersistFieldSql PersistValue

PersistFieldSql Checkmark

PersistFieldSql a => PersistFieldSql [a]

HasResolution a => PersistFieldSql (Fixed a)

PersistFieldSql v => PersistFieldSql (IntMap v)

(Ord a, PersistFieldSql a) => PersistFieldSql (Set a)

PersistFieldSql a => PersistFieldSql (Vector a)

(PersistField record, PersistEntity record) => PersistFieldSql (Entity record)

(PersistFieldSql a, PersistFieldSql b) => PersistFieldSql (a, b)

PersistFieldSql v => PersistFieldSql (Map Text v)

runSqlPool :: MonadBaseControl IO m => SqlPersistT m a -> Pool SqlBackend -> m a

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

Note: This function previously timed out after 2 seconds, but this behavior was buggy and caused more problems than it solved. Since version 2.1.2, it performs no timeout checks.

withResourceTimeout

Arguments

:: MonadBaseControl IO m

=> Int

Timeout period in microseconds

-> Pool a

-> (a -> m b)

-> m (Maybe b)

Like withResource, but times out the operation if resource allocation does not complete within the given timeout period.

Since 2.0.0

runSqlConn :: MonadBaseControl IO m => SqlPersistT m a -> SqlBackend -> m a

runSqlPersistM :: SqlPersistM a -> SqlBackend -> IO a

runSqlPersistMPool :: SqlPersistM a -> Pool SqlBackend -> IO a

withSqlPool

Arguments

:: (MonadIO m, MonadLogger m, MonadBaseControl IO m)

=> (LogFunc -> IO SqlBackend)

create a new connection

-> Int

connection count

-> (Pool SqlBackend -> m a)

-> m a

createSqlPool :: (MonadIO m, MonadLogger m, MonadBaseControl IO m) => (LogFunc -> IO SqlBackend) -> Int -> m (Pool SqlBackend)

askLogFunc :: forall m. (MonadBaseControl IO m, MonadLogger m) => m LogFunc

withSqlConn :: (MonadIO m, MonadBaseControl IO m, MonadLogger m) => (LogFunc -> IO SqlBackend) -> (SqlBackend -> m a) -> m a

close' :: SqlBackend -> IO ()

parseMigration :: MonadIO m => Migration -> ReaderT SqlBackend m (Either [Text] CautiousMigration)

parseMigration' :: MonadIO m => Migration -> ReaderT SqlBackend m CautiousMigration

printMigration :: MonadIO m => Migration -> ReaderT SqlBackend m ()

showMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Text]

getMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Sql]

runMigration :: MonadIO m => Migration -> ReaderT SqlBackend m ()

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

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

runMigrationUnsafe :: MonadIO m => Migration -> ReaderT SqlBackend m ()

migrate :: [EntityDef] -> EntityDef -> Migration

module Database.Persist

withRawQuery :: MonadIO m => Text -> [PersistValue] -> Sink [PersistValue] IO a -> ReaderT SqlBackend m a

toSqlKey :: ToBackendKey SqlBackend record => Int64 -> Key record

fromSqlKey :: ToBackendKey SqlBackend record => Key record -> Int64

getFieldName :: forall record typ m. (PersistEntity record, PersistEntityBackend record ~ SqlBackend, Monad m) => EntityField record typ -> ReaderT SqlBackend m Text

get the SQL string for the field that an EntityField represents Useful for raw SQL queries

Your backend may provide a more convenient fieldName function which does not operate in a Monad

getTableName :: forall record m. (PersistEntity record, PersistEntityBackend record ~ SqlBackend, Monad m) => record -> ReaderT SqlBackend m Text

get the SQL string for the table that a PeristEntity represents Useful for raw SQL queries

Your backend may provide a more convenient tableName function which does not operate in a Monad

tableDBName :: forall record. (PersistEntity record, PersistEntityBackend record ~ SqlBackend) => record -> DBName

useful for a backend to implement tableName by adding escaping

fieldDBName :: forall record typ. PersistEntity record => EntityField record typ -> DBName

useful for a backend to implement fieldName by adding escaping

rawQuery :: (MonadResource m, MonadReader env m, HasPersistBackend env SqlBackend) => Text -> [PersistValue] -> Source m [PersistValue]

rawQueryRes :: (MonadIO m1, MonadIO m2) => Text -> [PersistValue] -> ReaderT SqlBackend m1 (Acquire (Source m2 [PersistValue]))

rawExecute

Arguments

:: MonadIO m

=> Text

SQL statement, possibly with placeholders.

-> [PersistValue]

Values to fill the placeholders.

-> ReaderT SqlBackend m ()

Execute a raw SQL statement

rawExecuteCount

Arguments

:: MonadIO m

=> Text

SQL statement, possibly with placeholders.

-> [PersistValue]

Values to fill the placeholders.

-> ReaderT SqlBackend m Int64

Execute a raw SQL statement and return the number of rows it has modified.

rawSql

Arguments

:: (RawSql a, MonadIO m)

=> Text

SQL statement, possibly with placeholders.

-> [PersistValue]

Values to fill the placeholders.

-> ReaderT SqlBackend 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.

deleteWhereCount :: (PersistEntity val, MonadIO m, PersistEntityBackend val ~ SqlBackend) => [Filter val] -> ReaderT SqlBackend m Int64

Same as deleteWhere, but returns the number of rows affected.

Since 1.1.5

updateWhereCount :: (PersistEntity val, MonadIO m, SqlBackend ~ PersistEntityBackend val) => [Filter val] -> [Update val] -> ReaderT SqlBackend m Int64

Same as updateWhere, but returns the number of rows affected.

Since 1.1.5

transactionSave :: MonadIO m => ReaderT SqlBackend m ()

Commit the current transaction and begin a new one.

Since 1.2.0

transactionUndo :: MonadIO m => ReaderT SqlBackend m ()

Roll back the current transaction and begin a new one.

Since 1.2.0

getStmtConn :: SqlBackend -> Text -> IO Statement

Internal

mkColumns :: [EntityDef] -> EntityDef -> ([Column], [UniqueDef], [ForeignDef])

Create the list of columns for the given entity.

defaultAttribute :: [Attr] -> Maybe Text

decorateSQLWithLimitOffset :: Text -> (Int, Int) -> Bool -> Text -> Text

Generates sql for limit and offset for postgres, sqlite and mysql.