1{-# LANGUAGE DeriveDataTypeable #-}
2{-# LANGUAGE NamedFieldPuns #-}
3{-# LANGUAGE OverloadedStrings #-}
4{-# LANGUAGE PatternGuards #-}
5{-# LANGUAGE RecordWildCards #-}
6{-# LANGUAGE ViewPatterns #-}
7{-# LANGUAGE QuasiQuotes #-}
9------------------------------------------------------------------------------
11-- Module: Database.PostgreSQL.Simple
12-- Copyright: (c) 2011 MailRank, Inc.
13-- (c) 2011-2012 Leon P Smith
15-- Maintainer: Leon P Smith <leon@melding-monads.com>
16-- Stability: experimental
17-- Portability: portable
19-- A mid-level client library for the PostgreSQL database, aimed at ease of
20-- use and high performance.
22------------------------------------------------------------------------------
24module Database.PostgreSQL.Simple
32 -- ** Parameter substitution
38 -- ** Substituting a single parameter
41 -- ** Representing a list of values
44 -- ** Modifying multiple rows at once
47 -- ** @RETURNING@: modifications that returns results
50 -- * Extracting results
53 -- ** Handling null values
56 -- ** Type conversions
72 , FormatError(fmtMessage, fmtQuery, fmtParams)
73 , QueryError(qeMessage, qeQuery)
74 , ResultError(errSQLType, errHaskellType, errMessage)
75 -- * Connection management
77 , Base.connectPostgreSQL
78 , Base.postgreSQLConnectionString
79 , Base.defaultConnectInfo
81 -- * Queries that return results
84 -- * Queries that stream results
95 -- * Statements that do not return results
100 -- * Transaction handling
102 , withTransactionSerializable
103 , TransactionMode(..)
106 , defaultTransactionMode
107 , defaultIsolationLevel
108 , defaultReadWriteMode
109 , withTransactionLevel
110 , withTransactionMode
111 , withTransactionModeRetry
118 -- * Helper functions
123import Blaze.ByteString.Builder
124 ( Builder, fromByteString, toByteString )
125import Blaze.ByteString.Builder.Char8 (fromChar)
126import Control.Applicative ((<$>), pure)
127import Control.Concurrent.MVar
128import Control.Exception
129 ( Exception, onException, throw, throwIO, finally
130 , try, SomeException, fromException )
131import Control.Monad (foldM)
132import Data.ByteString (ByteString)
133import Data.Int (Int64)
134import qualified Data.IntMap as IntMap
135import Data.List (intersperse)
136import Data.Monoid (mconcat)
137import Data.Typeable (Typeable)
138import Database.PostgreSQL.Simple.BuiltinTypes ( oid2typname )
139import Database.PostgreSQL.Simple.Compat ( mask, (<>) )
140import Database.PostgreSQL.Simple.FromField (ResultError(..))
141import Database.PostgreSQL.Simple.FromRow (FromRow(..))
142import Database.PostgreSQL.Simple.Ok
143import Database.PostgreSQL.Simple.ToField (Action(..), inQuotes)
144import Database.PostgreSQL.Simple.ToRow (ToRow(..))
145import Database.PostgreSQL.Simple.Types
146 ( Binary(..), In(..), Only(..), Query(..), (:.)(..) )
147import Database.PostgreSQL.Simple.Internal as Base
148import Database.PostgreSQL.Simple.SqlQQ (sql)
149import qualified Database.PostgreSQL.LibPQ as PQ
150import qualified Data.ByteString.Char8 as B
151import qualified Data.Text as T
152import qualified Data.Text.Encoding as TE
153import qualified Data.Vector as V
154import Control.Monad.Trans.Reader
155import Control.Monad.Trans.State.Strict
157-- | Exception thrown if a 'Query' could not be formatted correctly.
158-- This may occur if the number of \'@?@\' characters in the query
159-- string does not match the number of parameters provided.
160data FormatError = FormatError {
163 , fmtParams :: [ByteString]
164 } deriving (Eq, Show, Typeable)
166instance Exception FormatError
168-- | Format a query string.
170-- This function is exposed to help with debugging and logging. Do not
171-- use it to prepare queries for execution.
173-- String parameters are escaped according to the character set in use
174-- on the 'Connection'.
176-- Throws 'FormatError' if the query string could not be formatted
178formatQuery :: ToRow q => Connection -> Query -> q -> IO ByteString
179formatQuery conn q@(Query template) qs
180 | null xs && '?' `B.notElem` template = return template
181 | otherwise = toByteString <$> buildQuery conn q template xs
184-- | Format a query string with a variable number of rows.
186-- This function is exposed to help with debugging and logging. Do not
187-- use it to prepare queries for execution.
189-- The query string must contain exactly one substitution group,
190-- identified by the SQL keyword \"@VALUES@\" (case insensitive)
191-- followed by an \"@(@\" character, a series of one or more \"@?@\"
192-- characters separated by commas, and a \"@)@\" character. White
193-- space in a substitution group is permitted.
195-- Throws 'FormatError' if the query string could not be formatted
197formatMany :: (ToRow q) => Connection -> Query -> [q] -> IO ByteString
198formatMany _ q [] = fmtError "no rows supplied" q []
199formatMany conn q@(Query template) qs = do
200 case parseTemplate template of
201 Just (before, qbits, after) -> do
202 bs <- mapM (buildQuery conn q qbits . toRow) qs
203 return . toByteString . mconcat $ fromByteString before :
204 intersperse (fromChar ',') bs ++
205 [fromByteString after]
206 Nothing -> fmtError "syntax error in multi-row template" q []
208-- Split the input string into three pieces, @before@, @qbits@, and @after@,
209-- following this grammar:
211-- start: ^ before qbits after $
212-- before: ([^?]* [^?\w])? 'VALUES' \s*
213-- qbits: '(' \s* '?' \s* (',' \s* '?' \s*)* ')'
217-- \w: [A-Z] | [a-z] | [\x80-\xFF] | '_' | '$' | [0-9]
219-- This would be much more concise with some sort of regex engine.
220-- 'formatMany' used to use pcre-light instead of this hand-written parser,
221-- but pcre is a hassle to install on Windows.
222parseTemplate :: ByteString -> Maybe (ByteString, ByteString, ByteString)
223parseTemplate template =
224 -- Convert input string to uppercase, to facilitate searching.
225 search $ B.map toUpper_ascii template
227 -- Search for the next occurrence of "VALUES"
229 case B.breakSubstring "VALUES" bs of
231 -- If "VALUES" is not present in the string, or any '?' characters
232 -- were encountered prior to it, fail.
233 | B.null y || ('?' `B.elem` x)
236 -- If "VALUES" is preceded by an identifier character (a.k.a. \w),
237 -- try the next occurrence.
238 | not (B.null x) && isIdent (B.last x)
239 -> search $ B.drop 6 y
241 -- Otherwise, we have a legitimate "VALUES" token.
243 -> parseQueryBits $ skipSpace $ B.drop 6 y
245 -- Parse '(' \s* '?' \s* . If this doesn't match
246 -- (and we don't consume a '?'), look for another "VALUES".
248 -- qb points to the open paren (if present), meaning it points to the
249 -- beginning of the "qbits" production described above. This is why we
250 -- pass it down to finishQueryBits.
252 | Just ('(', skipSpace -> bs1) <- B.uncons qb
253 , Just ('?', skipSpace -> bs2) <- B.uncons bs1
254 = finishQueryBits qb bs2
258 -- Parse (',' \s* '?' \s*)* ')' [^?]* .
260 -- Since we've already consumed at least one '?', there's no turning back.
261 -- The parse has to succeed here, or the whole thing fails
262 -- (because we don't allow '?' to appear outside of the VALUES list).
263 finishQueryBits qb bs0
264 | Just (')', bs1) <- B.uncons bs0
265 = if '?' `B.elem` bs1
267 else Just $ slice3 template qb bs1
268 | Just (',', skipSpace -> bs1) <- B.uncons bs0
269 , Just ('?', skipSpace -> bs2) <- B.uncons bs1
270 = finishQueryBits qb bs2
274 -- Slice a string into three pieces, given the start offset of the second
275 -- and third pieces. Each "offset" is actually a tail of the uppercase
276 -- version of the template string. Its length is used to infer the offset.
278 -- It is important to note that we only slice the original template.
279 -- We don't want our all-caps trick messing up the actual query string.
280 slice3 source p1 p2 =
283 (s1, source') = B.splitAt (B.length source - B.length p1) source
284 (s2, source'') = B.splitAt (B.length p1 - B.length p2) source'
286 toUpper_ascii c | c >= 'a' && c <= 'z' = toEnum (fromEnum c - 32)
289 -- Based on the definition of {ident_cont} in src/backend/parser/scan.l
290 -- in the PostgreSQL source. No need to check [a-z], since we converted
291 -- the whole string to uppercase.
292 isIdent c = (c >= '0' && c <= '9')
293 || (c >= 'A' && c <= 'Z')
294 || (c >= '\x80' && c <= '\xFF')
298 -- Based on {space} in scan.l
299 isSpace_ascii c = (c == ' ') || (c >= '\t' && c <= '\r')
301 skipSpace = B.dropWhile isSpace_ascii
303escapeStringConn :: Connection -> ByteString -> IO (Either ByteString ByteString)
304escapeStringConn conn s =
305 withConnection conn $ \c ->
306 PQ.escapeStringConn c s >>= checkError c
308escapeByteaConn :: Connection -> ByteString -> IO (Either ByteString ByteString)
309escapeByteaConn conn s =
310 withConnection conn $ \c ->
311 PQ.escapeByteaConn c s >>= checkError c
313checkError :: PQ.Connection -> Maybe a -> IO (Either ByteString a)
314checkError _ (Just x) = return $ Right x
315checkError c Nothing = Left . maybe "" id <$> PQ.errorMessage c
317buildQuery :: Connection -> Query -> ByteString -> [Action] -> IO Builder
318buildQuery conn q template xs = zipParams (split template) <$> mapM sub xs
319 where quote = either (\msg -> fmtError (utf8ToString msg) q xs)
320 (inQuotes . fromByteString)
321 utf8ToString = T.unpack . TE.decodeUtf8
322 sub (Plain b) = pure b
323 sub (Escape s) = quote <$> escapeStringConn conn s
324 sub (EscapeByteA s) = quote <$> escapeByteaConn conn s
325 sub (Many ys) = mconcat <$> mapM sub ys
326 split s = fromByteString h : if B.null t then [] else split (B.tail t)
327 where (h,t) = B.break (=='?') s
328 zipParams (t:ts) (p:ps) = t <> p <> zipParams ts ps
330 zipParams _ _ = fmtError (show (B.count '?' template) ++
331 " '?' characters, but " ++
332 show (length xs) ++ " parameters") q xs
334-- | Execute an @INSERT@, @UPDATE@, or other SQL query that is not
335-- expected to return results.
337-- Returns the number of rows affected.
339-- Throws 'FormatError' if the query could not be formatted correctly.
340execute :: (ToRow q) => Connection -> Query -> q -> IO Int64
341execute conn template qs = do
342 result <- exec conn =<< formatQuery conn template qs
343 finishExecute conn template result
345-- | Execute a multi-row @INSERT@, @UPDATE@, or other SQL query that is not
346-- expected to return results.
348-- Returns the number of rows affected.
350-- Throws 'FormatError' if the query could not be formatted correctly.
351executeMany :: (ToRow q) => Connection -> Query -> [q] -> IO Int64
352executeMany _ _ [] = return 0
353executeMany conn q qs = do
354 result <- exec conn =<< formatMany conn q qs
355 finishExecute conn q result
357-- | Execute @INSERT ... RETURNING@, @UPDATE ... RETURNING@, or other SQL
358-- query that accepts multi-row input and is expected to return results.
359-- Note that it is possible to write
360-- @'query' conn "INSERT ... RETURNING ..." ...@
361-- in cases where you are only inserting a single row, and do not need
362-- functionality analogous to 'executeMany'.
364-- Throws 'FormatError' if the query could not be formatted correctly.
365returning :: (ToRow q, FromRow r) => Connection -> Query -> [q] -> IO [r]
366returning _ _ [] = return []
367returning conn q qs = do
368 result <- exec conn =<< formatMany conn q qs
369 finishQuery conn q result
371-- | Perform a @SELECT@ or other SQL query that is expected to return
372-- results. All results are retrieved and converted before this
375-- When processing large results, this function will consume a lot of
376-- client-side memory. Consider using 'fold' instead.
378-- Exceptions that may be thrown:
380-- * 'FormatError': the query string could not be formatted correctly.
382-- * 'QueryError': the result contains no columns (i.e. you should be
383-- using 'execute' instead of 'query').
385-- * 'ResultError': result conversion failed.
386query :: (ToRow q, FromRow r)
387 => Connection -> Query -> q -> IO [r]
388query conn template qs = do
389 result <- exec conn =<< formatQuery conn template qs
390 finishQuery conn template result
392-- | A version of 'query' that does not perform query substitution.
393query_ :: (FromRow r) => Connection -> Query -> IO [r]
394query_ conn q@(Query que) = do
395 result <- exec conn que
396 finishQuery conn q result
398-- | Perform a @SELECT@ or other SQL query that is expected to return
399-- results. Results are streamed incrementally from the server, and
400-- consumed via a left fold.
402-- When dealing with small results, it may be simpler (and perhaps
403-- faster) to use 'query' instead.
405-- This fold is /not/ strict. The stream consumer is responsible for
406-- forcing the evaluation of its result to avoid space leaks.
408-- Exceptions that may be thrown:
410-- * 'FormatError': the query string could not be formatted correctly.
412-- * 'QueryError': the result contains no columns (i.e. you should be
413-- using 'execute' instead of 'query').
415-- * 'ResultError': result conversion failed.
417fold :: ( FromRow row, ToRow params )
422 -> (a -> row -> IO a)
424fold = foldWithOptions defaultFoldOptions
432 fetchQuantity :: !FetchQuantity,
433 transactionMode :: !TransactionMode
436defaultFoldOptions :: FoldOptions
437defaultFoldOptions = FoldOptions {
438 fetchQuantity = Automatic,
439 transactionMode = TransactionMode ReadCommitted ReadOnly
442foldWithOptions :: ( FromRow row, ToRow params )
448 -> (a -> row -> IO a)
450foldWithOptions opts conn template qs a f = do
451 q <- formatQuery conn template qs
452 doFold opts conn template (Query q) a f
454-- | A version of 'fold' that does not perform query substitution.
455fold_ :: (FromRow r) =>
458 -> a -- ^ Initial state for result consumer.
459 -> (a -> r -> IO a) -- ^ Result consumer.
461fold_ = foldWithOptions_ defaultFoldOptions
464foldWithOptions_ :: (FromRow r) =>
468 -> a -- ^ Initial state for result consumer.
469 -> (a -> r -> IO a) -- ^ Result consumer.
471foldWithOptions_ opts conn query a f = doFold opts conn query query a f
474doFold :: ( FromRow row )
480 -> (a -> row -> IO a)
482doFold FoldOptions{..} conn _template q a f = do
483 stat <- withConnection conn PQ.transactionStatus
485 PQ.TransIdle -> withTransactionMode transactionMode conn go
486 PQ.TransInTrans -> go
487 PQ.TransActive -> fail "foldWithOpts FIXME: PQ.TransActive"
488 -- This _shouldn't_ occur in the current incarnation of
489 -- the library, as we aren't using libpq asynchronously.
490 -- However, it could occur in future incarnations of
491 -- this library or if client code uses the Internal module
492 -- to use raw libpq commands on postgresql-simple connections.
493 PQ.TransInError -> fail "foldWithOpts FIXME: PQ.TransInError"
494 -- This should be turned into a better error message.
495 -- It is probably a bad idea to automatically roll
496 -- back the transaction and start another.
497 PQ.TransUnknown -> fail "foldWithOpts FIXME: PQ.TransUnknown"
498 -- Not sure what this means.
501 -- FIXME: what about name clashes with already-declared cursors?
502 _ <- execute_ conn ("DECLARE fold NO SCROLL CURSOR FOR " <> q)
503 loop a `finally` execute_ conn "CLOSE fold"
505-- FIXME: choose the Automatic chunkSize more intelligently
506-- One possibility is to use the type of the results, although this
507-- still isn't a perfect solution, given that common types (e.g. text)
508-- are of highly variable size.
509-- A refinement of this technique is to pick this number adaptively
510-- as results are read in from the database.
511 chunkSize = case fetchQuantity of
515 rs <- query conn "FETCH FORWARD ? FROM fold" (Only chunkSize)
516 if null rs then return a else foldM f a rs >>= loop
518-- | A version of 'fold' that does not transform a state value.
519forEach :: (ToRow q, FromRow r) =>
521 -> Query -- ^ Query template.
522 -> q -- ^ Query parameters.
523 -> (r -> IO ()) -- ^ Result consumer.
525forEach conn template qs = fold conn template qs () . const
526{-# INLINE forEach #-}
528-- | A version of 'forEach' that does not perform query substitution.
529forEach_ :: (FromRow r) =>
531 -> Query -- ^ Query template.
532 -> (r -> IO ()) -- ^ Result consumer.
534forEach_ conn template = fold_ conn template () . const
535{-# INLINE forEach_ #-}
537forM' :: (Ord n, Num n) => n -> n -> (n -> IO a) -> IO [a]
538forM' lo hi m = loop hi []
546finishQuery :: (FromRow r) => Connection -> Query -> PQ.Result -> IO [r]
547finishQuery conn q result = do
548 status <- PQ.resultStatus result
551 throwIO $ QueryError "query: Empty query" q
553 throwIO $ QueryError "query resulted in a command response" q
555 ncols <- PQ.nfields result
556 let unCol (PQ.Col x) = fromIntegral x :: Int
557 typeinfos <- V.generateM (unCol ncols)
558 (\(PQ.Col . fromIntegral -> col) -> do
559 getTypeInfo conn =<< PQ.ftype result col)
560 nrows <- PQ.ntuples result
561 ncols <- PQ.nfields result
562 forM' 0 (nrows-1) $ \row -> do
563 let rw = Row row typeinfos result
564 case runStateT (runReaderT (unRP fromRow) rw) 0 of
565 Ok (val,col) | col == ncols -> return val
567 vals <- forM' 0 (ncols-1) $ \c -> do
568 v <- PQ.getvalue result row c
569 return ( typeinfos V.! unCol c
571 throw (ConversionFailed
572 (show (unCol ncols) ++ " values: " ++ show vals)
575 (show (unCol col) ++ " slots in target type")
576 "mismatch between number of columns to \
577 \convert and number in target type")
578 Errors [] -> throwIO $ ConversionFailed "" Nothing "" "" "unknown error"
579 Errors [x] -> throwIO x
580 Errors xs -> throwIO $ ManyErrors xs
582 throwIO $ QueryError "query: COPY TO is not supported" q
584 throwIO $ QueryError "query: COPY FROM is not supported" q
585 PQ.BadResponse -> throwResultError "query" result status
586 PQ.NonfatalError -> throwResultError "query" result status
587 PQ.FatalError -> throwResultError "query" result status
589ellipsis :: ByteString -> ByteString
591 | B.length bs > 15 = B.take 10 bs `B.append` "[...]"
595-- | Of the four isolation levels defined by the SQL standard,
596-- these are the three levels distinguished by PostgreSQL as of version 9.0.
597-- See <http://www.postgresql.org/docs/9.1/static/transaction-iso.html>
598-- for more information. Note that prior to PostgreSQL 9.0, 'RepeatableRead'
599-- was equivalent to 'Serializable'.
602 = DefaultIsolationLevel -- ^ the isolation level will be taken from
603 -- PostgreSQL's per-connection
604 -- @default_transaction_isolation@ variable,
605 -- which is initialized according to the
606 -- server's config. The default configuration
607 -- is 'ReadCommitted'.
611 deriving (Show, Eq, Ord, Enum, Bounded)
614 = DefaultReadWriteMode -- ^ the read-write mode will be taken from
615 -- PostgreSQL's per-connection
616 -- @default_transaction_read_only@ variable,
617 -- which is initialized according to the
618 -- server's config. The default configuration
622 deriving (Show, Eq, Ord, Enum, Bounded)
624data TransactionMode = TransactionMode {
625 isolationLevel :: !IsolationLevel,
626 readWriteMode :: !ReadWriteMode
627 } deriving (Show, Eq)
629defaultTransactionMode :: TransactionMode
630defaultTransactionMode = TransactionMode
631 defaultIsolationLevel
634defaultIsolationLevel :: IsolationLevel
635defaultIsolationLevel = DefaultIsolationLevel
637defaultReadWriteMode :: ReadWriteMode
638defaultReadWriteMode = DefaultReadWriteMode
640-- | Execute an action inside a SQL transaction.
642-- This function initiates a transaction with a \"@begin
643-- transaction@\" statement, then executes the supplied action. If
644-- the action succeeds, the transaction will be completed with
645-- 'Base.commit' before this function returns.
647-- If the action throws /any/ kind of exception (not just a
648-- PostgreSQL-related exception), the transaction will be rolled back using
649-- 'rollback', then the exception will be rethrown.
650withTransaction :: Connection -> IO a -> IO a
651withTransaction = withTransactionMode defaultTransactionMode
653-- | Execute an action inside of a 'Serializable' transaction. If a
654-- serialization failure occurs, roll back the transaction and try again.
655-- Be warned that this may execute the IO action multiple times.
657-- A 'Serializable' transaction creates the illusion that your program has
658-- exclusive access to the database. This means that, even in a concurrent
659-- setting, you can perform queries in sequence without having to worry about
660-- what might happen between one statement and the next.
662-- Think of it as STM, but without @retry@.
663withTransactionSerializable :: Connection -> IO a -> IO a
664withTransactionSerializable =
665 withTransactionModeRetry
667 { isolationLevel = Serializable
668 , readWriteMode = ReadWrite
670 retryOnNotSerializable
672 retryOnNotSerializable exception =
674 SqlError{..} | sqlState == serialization_failure
677 -- http://www.postgresql.org/docs/current/static/errcodes-appendix.html
678 serialization_failure = "40001"
680-- | Execute an action inside a SQL transaction with a given isolation level.
681withTransactionLevel :: IsolationLevel -> Connection -> IO a -> IO a
682withTransactionLevel lvl
683 = withTransactionMode defaultTransactionMode { isolationLevel = lvl }
685-- | Execute an action inside a SQL transaction with a given transaction mode.
686withTransactionMode :: TransactionMode -> Connection -> IO a -> IO a
687withTransactionMode mode conn act =
688 mask $ \restore -> do
690 r <- restore act `onException` rollback conn
694-- | Like 'withTransactionMode', but also takes a custom callback to
695-- determine if a transaction should be retried if an 'SqlError' occurs.
696-- If the callback returns True, then the transaction will be retried.
697-- If the callback returns False, or an exception other than an 'SqlError'
698-- occurs then the transaction will be rolled back and the exception rethrown.
700-- This is used to implement 'withTransactionSerializable'.
701withTransactionModeRetry :: TransactionMode -> (SqlError -> Bool) -> Connection -> IO a -> IO a
702withTransactionModeRetry mode shouldRetry conn act =
709 retryLoop :: IO (Either SomeException a) -> IO a
716 case fmap shouldRetry (fromException e) of
717 Just True -> retryLoop act'
722-- | Rollback a transaction.
723rollback :: Connection -> IO ()
724rollback conn = execute_ conn "ABORT" >> return ()
726-- | Commit a transaction.
727commit :: Connection -> IO ()
728commit conn = execute_ conn "COMMIT" >> return ()
730-- | Begin a transaction.
731begin :: Connection -> IO ()
732begin = beginMode defaultTransactionMode
734-- | Begin a transaction with a given isolation level
735beginLevel :: IsolationLevel -> Connection -> IO ()
736beginLevel lvl = beginMode defaultTransactionMode { isolationLevel = lvl }
738-- | Begin a transaction with a given transaction mode
739beginMode :: TransactionMode -> Connection -> IO ()
740beginMode mode conn = do
741 _ <- execute_ conn $! Query (B.concat ["BEGIN", isolevel, readmode])
744 isolevel = case isolationLevel mode of
745 DefaultIsolationLevel -> ""
746 ReadCommitted -> " ISOLATION LEVEL READ COMMITTED"
747 RepeatableRead -> " ISOLATION LEVEL REPEATABLE READ"
748 Serializable -> " ISOLATION LEVEL SERIALIZABLE"
749 readmode = case readWriteMode mode of
750 DefaultReadWriteMode -> ""
751 ReadWrite -> " READ WRITE"
752 ReadOnly -> " READ ONLY"
754fmtError :: String -> Query -> [Action] -> a
755fmtError msg q xs = throw FormatError {
758 , fmtParams = map twiddle xs
760 where twiddle (Plain b) = toByteString b
761 twiddle (Escape s) = s
762 twiddle (EscapeByteA s) = s
763 twiddle (Many ys) = B.concat (map twiddle ys)
767-- SQL-based applications are somewhat notorious for their
768-- susceptibility to attacks through the injection of maliciously
769-- crafted data. The primary reason for widespread vulnerability to
770-- SQL injections is that many applications are sloppy in handling
771-- user data when constructing SQL queries.
773-- This library provides a 'Query' type and a parameter substitution
774-- facility to address both ease of use and security.
778-- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally
779-- exposes a tiny API that is not compatible with the 'ByteString'
780-- API; this makes it difficult to construct queries from fragments of
781-- strings. The 'query' and 'execute' functions require queries to be
784-- To most easily construct a query, enable GHC's @OverloadedStrings@
785-- language extension and write your query as a normal literal string.
787-- > {-# LANGUAGE OverloadedStrings #-}
789-- > import Database.PostgreSQL.Simple
792-- > conn <- connect defaultConnectInfo
793-- > query conn "select 2 + 2"
795-- A 'Query' value does not represent the actual query that will be
796-- executed, but is a template for constructing the final query.
800-- Since applications need to be able to construct queries with
801-- parameters that change, this library provides a query substitution
804-- The 'Query' template accepted by 'query' and 'execute' can contain
805-- any number of \"@?@\" characters. Both 'query' and 'execute'
806-- accept a third argument, typically a tuple. When constructing the
807-- real query to execute, these functions replace the first \"@?@\" in
808-- the template with the first element of the tuple, the second
809-- \"@?@\" with the second element, and so on. If necessary, each
810-- tuple element will be quoted and escaped prior to substitution;
811-- this defeats the single most common injection vector for malicious
814-- For example, given the following 'Query' template:
816-- > select * from user where first_name = ? and age > ?
818-- And a tuple of this form:
820-- > ("Boris" :: String, 37 :: Int)
822-- The query to be executed will look like this after substitution:
824-- > select * from user where first_name = 'Boris' and age > 37
826-- If there is a mismatch between the number of \"@?@\" characters in
827-- your template and the number of elements in your tuple, a
828-- 'FormatError' will be thrown.
830-- Note that the substitution functions do not attempt to parse or
831-- validate your query. It's up to you to write syntactically valid
832-- SQL, and to ensure that each \"@?@\" in your query template is
833-- matched with the right tuple element.
837-- Automated type inference means that you will often be able to avoid
838-- supplying explicit type signatures for the elements of a tuple.
839-- However, sometimes the compiler will not be able to infer your
840-- types. Consider a case where you write a numeric literal in a
843-- > query conn "select ? + ?" (40,2)
845-- The above query will be rejected by the compiler, because it does
846-- not know the specific numeric types of the literals @40@ and @2@.
847-- This is easily fixed:
849-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)
851-- The same kind of problem can arise with string literals if you have
852-- the @OverloadedStrings@ language extension enabled. Again, just
853-- use an explicit type signature if this happens.
857-- Haskell lacks a single-element tuple type, so if you have just one
858-- value you want substituted into a query, what should you do?
860-- The obvious approach would appear to be something like this:
862-- > instance (Param a) => QueryParam a where
865-- Unfortunately, this wreaks havoc with type inference, so we take a
866-- different tack. To represent a single value @val@ as a parameter, write
867-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.
869-- Here's an example using a singleton list:
871-- > execute conn "insert into users (first_name) values (?)"
876-- Suppose you want to write a query using an @IN@ clause:
878-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')
880-- In such cases, it's common for both the elements and length of the
881-- list after the @IN@ keyword to vary from query to query.
883-- To address this case, use the 'In' type wrapper, and use a single
884-- \"@?@\" character to represent the list. Omit the parentheses
885-- around the list; these will be added for you.
889-- > query conn "select * from users where first_name in ?" $
890-- > In ["Anna", "Boris", "Carla"]
892-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be
893-- substituted instead, to ensure that your clause remains
894-- syntactically valid.
898-- If you know that you have many rows of data to insert into a table,
899-- it is much more efficient to perform all the insertions in a single
900-- multi-row @INSERT@ statement than individually.
902-- The 'executeMany' function is intended specifically for helping
903-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for
904-- query substitution are different than those for 'execute'.
906-- What 'executeMany' searches for in your 'Query' template is a
907-- single substring of the form:
911-- The rules are as follows:
913-- * The keyword @VALUES@ is matched case insensitively.
915-- * There must be no other \"@?@\" characters anywhere in your
918-- * There must one or more \"@?@\" in the parentheses.
920-- * Extra white space is fine.
922-- The last argument to 'executeMany' is a list of parameter
923-- tuples. These will be substituted into the query where the @(?,?)@
924-- string appears, in a form suitable for use in a multi-row @INSERT@
927-- Here is an example:
930-- > "insert into users (first_name,last_name) values (?,?)"
931-- > [("Boris","Karloff"),("Ed","Wood")]
933-- The query that will be executed here will look like this
934-- (reformatted for tidiness):
936-- > insert into users (first_name,last_name) values
937-- > ('Boris','Karloff'),('Ed','Wood')
941-- The 'returning' function is similar to 'executeMany' but is
942-- intended for use with multi-tuple @INSERT@ or @UPDATE@ statements
943-- that make use of @RETURNING@.
945-- For example, were there an auto-incrementing @id@ column and
946-- timestamp column @t@ that defaulted to the present time for the
947-- @sales@ table, then the following query would insert two new
948-- sales records and also return their new @id@s and timestamps.
950-- > let q = "insert into sales (amount, label) values (?,?) returning id, t"
951-- > xs :: [(Int, UTCTime)] <- returning conn q [(20,"Chips"),(300,"Wood")]
955-- The 'query' and 'query_' functions return a list of values in the
956-- 'FromRow' typeclass. This class performs automatic extraction
957-- and type conversion of rows from a query result.
959-- Here is a simple example of how to extract results:
961-- > import qualified Data.Text as Text
963-- > xs <- query_ conn "select name,age from users"
964-- > forM_ xs $ \(name,age) ->
965-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
967-- Notice two important details about this code:
969-- * The number of columns we ask for in the query template must
970-- exactly match the number of elements we specify in a row of the
971-- result tuple. If they do not match, a 'ResultError' exception
974-- * Sometimes, the compiler needs our help in specifying types. It
975-- can infer that @name@ must be a 'Text', due to our use of the
976-- @unpack@ function. However, we have to tell it the type of @age@,
977-- as it has no other information to determine the exact type.
981-- The type of a result tuple will look something like this:
985-- Although SQL can accommodate @NULL@ as a value for any of these
986-- types, Haskell cannot. If your result contains columns that may be
987-- @NULL@, be sure that you use 'Maybe' in those positions of of your
990-- > (Text, Maybe Int, Int)
992-- If 'query' encounters a @NULL@ in a row where the corresponding
993-- Haskell type is not 'Maybe', it will throw a 'ResultError'
998-- To specify that a query returns a single-column result, use the
1001-- > xs <- query_ conn "select id from users"
1002-- > forM_ xs $ \(Only dbid) -> {- ... -}
1006-- Conversion of SQL values to Haskell values is somewhat
1007-- permissive. Here are the rules.
1009-- * For numeric types, any Haskell type that can accurately represent
1010-- all values of the given PostgreSQL type is considered \"compatible\".
1011-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@
1012-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately
1013-- represent a @SMALLINT@, so it is considered compatble with those types.
1015-- * A numeric compatibility check is based only on the type of a
1016-- column, /not/ on its values. For instance, a PostgreSQL 64-bit
1017-- @BIGINT@ column will be considered incompatible with a Haskell
1018-- 'Int16', even if it contains the value @1@.
1020-- * If a numeric incompatibility is found, 'query' will throw a
1023-- * The 'String' and 'Text' types are assumed to be encoded as
1024-- UTF-8. If you use some other encoding, decoding may fail or give
1025-- wrong results. In such cases, write a @newtype@ wrapper and a
1026-- custom 'Result' instance to handle your encoding.
1028getTypeInfo :: Connection -> PQ.Oid -> IO TypeInfo
1029getTypeInfo conn@Connection{..} oid =
1030 case oid2typname oid of
1031 Just name -> return $! TypeInfo { typ = NamedOid oid name
1034 Nothing -> modifyMVar connectionObjects $ \oidmap -> do
1035 case IntMap.lookup (oid2int oid) oidmap of
1036 Just typeinfo -> return (oidmap, typeinfo)
1039 [sql| SELECT p.oid, p.typname, c.oid, c.typname
1040 FROM pg_type AS p LEFT OUTER JOIN pg_type AS c
1041 ON c.oid = p.typelem
1044 typinf <- case names of
1045 [] -> return $ throw (fatalError "invalid type oid")
1046 [(pOid, pTypName, mbCOid, mbCTypName)] ->
1047 return $! TypeInfo { typ = NamedOid pOid pTypName
1050 cTypName <- mbCTypName
1051 return $ NamedOid cOid cTypName
1053 _ -> fail "typename query returned more than one result"
1054 -- oid is a primary key, so the query should
1055 -- never return more than one result
1056 return (IntMap.insert (oid2int oid) typinf oidmap, typinf)
keysesc to close
- j k
- move down, up
- enter
- open
- esc
- back, or up a level
- 1 2 3 4
- readme, files, commits, refs
- /
- filter
- y
- copy the clone command
- c
- compare the chosen commit with another (or shift-click both)
- ?
- show or hide this list