Database.Persist.Sql.Raw.QQ

Module: module Database.Persist.Sql.Raw.QQ Description: QuasiQuoters for performing raw sql queries

This module exports convenient QuasiQuoters to perform raw SQL queries. All QuasiQuoters follow the same pattern and are analogous to the similar named functions exported from Raw. Neither the quoted function's behaviour, nor it's return value is altered during the translation and all documentation provided with it holds.

The QuasiQuoters in this module perform a simple substitution on the query text, that allows value substitutions, table name substitutions as well as column name substitutions.

Since: 2.9.0

Sql QuasiQuoters

queryQQ :: QuasiQuoter

Analoguous to rawQuery

Since: 2.9.0

queryResQQ :: QuasiQuoter

Analoguous to rawQueryRes

Since: 2.9.0

sqlQQ :: QuasiQuoter

QuasiQuoter for performing raw sql queries, analoguous to rawSql

This and the following are convenient QuasiQuoters to perform raw SQL queries. They each follow the same pattern and are analogous to the similarly named raw functions. Neither the quoted function's behaviour, nor it's return value is altered during the translation and all documentation provided with it holds.

These QuasiQuoters perform a simple substitution on the query text, that allows value substitutions, table name substitutions as well as column name substitutions.

Here is a small example:

Given the following simple model:

Category
  rgt Int default=0
  lft Int default=0
  nam Text

We can now execute this raw query:

let lft = 10 :: Int
    rgt = 20 :: Int
    width = rgt - lft
    nams = "first" :| ["second", "third"]
 in [sqlQQ|
      DELETE FROM ^{Category} WHERE @{CategoryLft} BETWEEN #{lft} AND #{rgt};
      UPDATE category SET @{CategoryRgt} = @{CategoryRgt} - #{width} WHERE @{CategoryRgt} > #{rgt};
      UPDATE category SET @{CategoryLft} = @{CategoryLft} - #{width} WHERE @{CategoryLft} > #{rgt};
      SELECT ?? FROM ^{Category} WHERE ^{Category}.@{CategoryNam} IN %{nams};
      INSERT INTO ^{Category}(@{CategoryNam}) VALUES *{Single <$> nams};
    |]

• ^{TableName} looks up the table's name and escapes it
• @{ColumnName} looks up the column's name and properly escapes it
• #{value} inserts the value via the usual parameter substitution mechanism
• %{values} inserts comma separated values (of a NonEmpty list) (since 2.9.1)
• *{rows} inserts a NonEmpty list of tuples for use with a multirow INSERT statement (since 2.9.2)

Since: 2.9.0

executeQQ :: QuasiQuoter

Analoguous to rawExecute

Since: 2.9.0

executeCountQQ :: QuasiQuoter

Analoguous to rawExecuteCount

Since: 2.9.0

class ToRow a where

Methods

toRow :: a -> NonEmpty PersistValue

Instances

PersistField a => ToRow (Single a)

Defined in Database.Persist.Sql.Raw.QQ

Methods

toRow :: Single a -> NonEmpty PersistValue

(PersistField a, PersistField b) => ToRow (a, b)

Defined in Database.Persist.Sql.Raw.QQ

Methods

toRow :: (a, b) -> NonEmpty PersistValue

(PersistField a, PersistField b, PersistField c) => ToRow (a, b, c)

Defined in Database.Persist.Sql.Raw.QQ

Methods

toRow :: (a, b, c) -> NonEmpty PersistValue

(PersistField a, PersistField b, PersistField c, PersistField d) => ToRow (a, b, c, d)

Defined in Database.Persist.Sql.Raw.QQ

Methods

toRow :: (a, b, c, d) -> NonEmpty PersistValue

(PersistField a, PersistField b, PersistField c, PersistField d, PersistField e) => ToRow (a, b, c, d, e)

Defined in Database.Persist.Sql.Raw.QQ

Methods

toRow :: (a, b, c, d, e) -> NonEmpty PersistValue