{-# LANGUAGE DeriveDataTypeable, DeriveFunctor, GeneralizedNewtypeDeriving #-} ------------------------------------------------------------------------------ -- | -- Module: Database.PostgreSQL.Simple.Types -- Copyright: (c) 2011 MailRank, Inc. -- (c) 2011-2012 Leon P Smith -- License: BSD3 -- Maintainer: Leon P Smith <leon@melding-monads.com> -- Stability: experimental -- -- Basic types. -- ------------------------------------------------------------------------------ module Database.PostgreSQL.Simple.Types ( Null(..) , Default(..) , Only(..) , In(..) , Binary(..) , Identifier(..) , QualifiedIdentifier(..) , Query(..) , Oid(..) , (:.)(..) , Savepoint(..) , PGArray(..) , Values(..) ) where import Control.Arrow (first) import Data.ByteString (ByteString) import Data.Hashable (Hashable(hashWithSalt)) import Data.Monoid (Monoid(..)) import Data.String (IsString(..)) import Data.Typeable (Typeable) import Data.ByteString.Builder ( stringUtf8 ) import qualified Data.ByteString as B import Data.Text (Text) import qualified Data.Text as T import Database.PostgreSQL.LibPQ (Oid(..)) import Database.PostgreSQL.Simple.Compat (toByteString) -- | A placeholder for the SQL @NULL@ value. data Null = Null deriving (Read, Show, Typeable) instance Eq Null where _ == _ = False _ /= _ = False -- | A placeholder for the PostgreSQL @DEFAULT@ value. data Default = Default deriving (Read, Show, Typeable) -- | A query string. This type is intended to make it difficult to -- construct a SQL query by concatenating string fragments, as that is -- an extremely common way to accidentally introduce SQL injection -- vulnerabilities into an application. -- -- This type is an instance of 'IsString', so the easiest way to -- construct a query is to enable the @OverloadedStrings@ language -- extension and then simply write the query in double quotes. -- -- > {-# LANGUAGE OverloadedStrings #-} -- > -- > import Database.PostgreSQL.Simple -- > -- > q :: Query -- > q = "select ?" -- -- The underlying type is a 'ByteString', and literal Haskell strings -- that contain Unicode characters will be correctly transformed to -- UTF-8. newtype Query = Query { fromQuery :: ByteString } deriving (Eq, Ord, Typeable) instance Show Query where show = show . fromQuery instance Read Query where readsPrec i = fmap (first Query) . readsPrec i instance IsString Query where fromString = Query . toByteString . stringUtf8 instance Monoid Query where mempty = Query B.empty mappend (Query a) (Query b) = Query (B.append a b) {-# INLINE mappend #-} mconcat xs = Query (B.concat (map fromQuery xs)) -- | A single-value \"collection\". -- -- This is useful if you need to supply a single parameter to a SQL -- query, or extract a single column from a SQL result. -- -- Parameter example: -- -- @query c \"select x from scores where x > ?\" ('Only' (42::Int))@ -- -- Result example: -- -- @xs <- query_ c \"select id from users\" --forM_ xs $ \\('Only' id) -> {- ... -}@ newtype Only a = Only { fromOnly :: a } deriving (Eq, Ord, Read, Show, Typeable, Functor) -- | Wrap a list of values for use in an @IN@ clause. Replaces a -- single \"@?@\" character with a parenthesized list of rendered -- values. -- -- Example: -- -- > query c "select * from whatever where id in ?" (Only (In [3,4,5])) newtype In a = In a deriving (Eq, Ord, Read, Show, Typeable, Functor) -- | Wrap binary data for use as a @bytea@ value. newtype Binary a = Binary {fromBinary :: a} deriving (Eq, Ord, Read, Show, Typeable, Functor) -- | Wrap text for use as sql identifier, i.e. a table or column name. newtype Identifier = Identifier {fromIdentifier :: Text} deriving (Eq, Ord, Read, Show, Typeable, IsString) instance Hashable Identifier where hashWithSalt i (Identifier t) = hashWithSalt i t -- | Wrap text for use as (maybe) qualified identifier, i.e. a table -- with schema, or column with table. data QualifiedIdentifier = QualifiedIdentifier (Maybe Text) Text deriving (Eq, Ord, Read, Show, Typeable) instance Hashable QualifiedIdentifier where hashWithSalt i (QualifiedIdentifier q t) = hashWithSalt i (q, t) -- | @\"foo.bar\"@ will get turned into -- @QualifiedIdentifier (Just \"foo\") \"bar\"@, while @\"foo\"@ will get -- turned into @QualifiedIdentifier Nothing \"foo\"@. Note this instance -- is for convenience, and does not match postgres syntax. It -- only examines the first period character, and thus cannot be used if the -- qualifying identifier contains a period for example. instance IsString QualifiedIdentifier where fromString str = let (x,y) = T.break (== '.') (fromString str) in if T.null y then QualifiedIdentifier Nothing x else QualifiedIdentifier (Just x) (T.tail y) -- | Wrap a list for use as a PostgreSQL array. newtype PGArray a = PGArray {fromPGArray :: [a]} deriving (Eq, Ord, Read, Show, Typeable, Functor) -- | A composite type to parse your custom data structures without -- having to define dummy newtype wrappers every time. -- -- -- > instance FromRow MyData where ... -- -- > instance FromRow MyData2 where ... -- -- -- then I can do the following for free: -- -- @ -- res <- query' c "..." -- forM res $ \\(MyData{..} :. MyData2{..}) -> do -- .... -- @ data h :. t = h :. t deriving (Eq,Ord,Show,Read,Typeable) infixr 3 :. newtype Savepoint = Savepoint Query deriving (Eq, Ord, Show, Read, Typeable) -- | Represents a @VALUES@ table literal, usable as an alternative -- to @executeMany@ and @returning@. The main advantage is that -- you can parametrize more than just a single @VALUES@ expression. -- For example, here's a query to insert a thing into one table -- and some attributes of that thing into another, returning the -- new id generated by the database: -- -- -- > query c [sql| -- > WITH new_thing AS ( -- > INSERT INTO thing (name) VALUES (?) RETURNING id -- > ), new_attributes AS ( -- > INSERT INTO thing_attributes -- > SELECT new_thing.id, attrs.* -- > FROM new_thing JOIN ? attrs -- > ) SELECT * FROM new_thing -- > |] ("foo", Values [ "int4", "text" ] -- > [ ( 1 , "hello" ) -- > , ( 2 , "world" ) ]) -- -- (Note this example uses writable common table expressions, -- which were added in PostgreSQL 9.1) -- -- The second parameter gets expanded into the following SQL syntax: -- -- > (VALUES (1::"int4",'hello'::"text"),(2,'world')) -- -- When the list of attributes is empty, the second parameter expands to: -- -- > (VALUES (null::"int4",null::"text") LIMIT 0) -- -- By contrast, @executeMany@ and @returning@ don't issue the query -- in the empty case, and simply return @0@ and @[]@ respectively. -- This behavior is usually correct given their intended use cases, -- but would certainly be wrong in the example above. -- -- The first argument is a list of postgresql type names. Because this -- is turned into a properly quoted identifier, the type name is case -- sensitive and must be as it appears in the @pg_type@ table. Thus, -- you must write @timestamptz@ instead of @timestamp with time zone@, -- @int4@ instead of @integer@, @_int8@ instead of @bigint[]@, etcetera. -- -- You may omit the type names, however, if you do so the list -- of values must be non-empty, and postgresql must be able to infer -- the types of the columns from the surrounding context. If the first -- condition is not met, postgresql-simple will throw an exception -- without issuing the query. In the second case, the postgres server -- will return an error which will be turned into a @SqlError@ exception. -- -- See <http://www.postgresql.org/docs/9.3/static/sql-values.html> for -- more information. data Values a = Values [QualifiedIdentifier] [a] deriving (Eq, Ord, Show, Read, Typeable)