1{-# LANGUAGE BangPatterns, DeriveDataTypeable, OverloadedStrings #-}
2{-# LANGUAGE DoAndIfThenElse, RecordWildCards, NamedFieldPuns #-}
3------------------------------------------------------------------------------
5-- Module: Database.PostgreSQL.Simple
6-- Copyright: (c) 2011 MailRank, Inc.
7-- (c) 2011 Leon P Smith
9-- Maintainer: Leon P Smith <leon@melding-monads.com>
10-- Stability: experimental
11-- Portability: portable
13-- A mid-level client library for the PostgreSQL database, aimed at ease of
14-- use and high performance.
16------------------------------------------------------------------------------
18module Database.PostgreSQL.Simple
26 -- ** Parameter substitution
32 -- ** Substituting a single parameter
35 -- ** Representing a list of values
38 -- ** Modifying multiple rows at once
41 -- * Extracting results
44 -- ** Handling null values
47 -- ** Type conversions
59 , FormatError(fmtMessage, fmtQuery, fmtParams)
60 , QueryError(qeMessage, qeQuery)
61 , ResultError(errSQLType, errHaskellType, errMessage)
62 -- * Connection management
64 , Base.connectPostgreSQL
65 , Base.postgreSQLConnectionString
66 , Base.defaultConnectInfo
68 -- * Queries that return results
71 -- * Queries that stream results
76 -- * Statements that do not return results
81 -- * Transaction handling
92import Blaze.ByteString.Builder (Builder, fromByteString, toByteString)
93import Blaze.ByteString.Builder.Char8 (fromChar)
94import Control.Applicative ((<$>), pure)
95import Control.Concurrent.MVar
96import Control.Exception (Exception, bracket, onException, throw, throwIO)
97import Control.Monad (foldM)
98import Control.Monad.Fix (fix)
99import Data.ByteString (ByteString)
101import Data.Int (Int64)
102import qualified Data.IntMap as IntMap
103import Data.List (intersperse)
104import Data.Monoid (mappend, mconcat)
105import Data.Typeable (Typeable)
106import Database.PostgreSQL.Simple.BuiltinTypes (oid2builtin, builtin2typname)
107import Database.PostgreSQL.Simple.Param (Action(..), inQuotes)
108import Database.PostgreSQL.Simple.QueryParams (QueryParams(..))
109import Database.PostgreSQL.Simple.Result (ResultError(..))
110import Database.PostgreSQL.Simple.QueryResults (QueryResults(..))
111import Database.PostgreSQL.Simple.Types (Binary(..), In(..), Only(..), Query(..))
112import Database.PostgreSQL.Simple.Internal as Base
113import qualified Database.PostgreSQL.LibPQ as PQ
114import Text.Regex.PCRE.Light (compile, caseless, match)
115import qualified Data.ByteString.Char8 as B
117-- | Exception thrown if a 'Query' could not be formatted correctly.
118-- This may occur if the number of \'@?@\' characters in the query
119-- string does not match the number of parameters provided.
120data FormatError = FormatError {
123 , fmtParams :: [ByteString]
124 } deriving (Eq, Show, Typeable)
126instance Exception FormatError
128-- | Exception thrown if 'query' is used to perform an @INSERT@-like
129-- operation, or 'execute' is used to perform a @SELECT@-like operation.
130data QueryError = QueryError {
133 } deriving (Eq, Show, Typeable)
135instance Exception QueryError
137-- | Format a query string.
139-- This function is exposed to help with debugging and logging. Do not
140-- use it to prepare queries for execution.
142-- String parameters are escaped according to the character set in use
143-- on the 'Connection'.
145-- Throws 'FormatError' if the query string could not be formatted
147formatQuery :: QueryParams q => Connection -> Query -> q -> IO ByteString
148formatQuery conn q@(Query template) qs
149 | null xs && '?' `B.notElem` template = return template
150 | otherwise = toByteString <$> buildQuery conn q template xs
151 where xs = renderParams qs
153-- | Format a query string with a variable number of rows.
155-- This function is exposed to help with debugging and logging. Do not
156-- use it to prepare queries for execution.
158-- The query string must contain exactly one substitution group,
159-- identified by the SQL keyword \"@VALUES@\" (case insensitive)
160-- followed by an \"@(@\" character, a series of one or more \"@?@\"
161-- characters separated by commas, and a \"@)@\" character. White
162-- space in a substitution group is permitted.
164-- Throws 'FormatError' if the query string could not be formatted
166formatMany :: (QueryParams q) => Connection -> Query -> [q] -> IO ByteString
167formatMany _ q [] = fmtError "no rows supplied" q []
168formatMany conn q@(Query template) qs = do
169 case match re template [] of
170 Just [_,before,qbits,after] -> do
171 bs <- mapM (buildQuery conn q qbits . renderParams) qs
172 return . toByteString . mconcat $ fromByteString before :
173 intersperse (fromChar ',') bs ++
174 [fromByteString after]
177 re = compile "^([^?]+\\bvalues\\s*)\
178 \(\\(\\s*[?](?:\\s*,\\s*[?])*\\s*\\))\
182escapeStringConn conn s = withConnection conn $ \c -> do
183 PQ.escapeStringConn c s
185buildQuery :: Connection -> Query -> ByteString -> [Action] -> IO Builder
186buildQuery conn q template xs = zipParams (split template) <$> mapM sub xs
187 where quote = inQuotes . fromByteString . maybe undefined id
188 sub (Plain b) = pure b
189 sub (Escape s) = quote <$> escapeStringConn conn s
190 sub (Many ys) = mconcat <$> mapM sub ys
191 split s = fromByteString h : if B.null t then [] else split (B.tail t)
192 where (h,t) = B.break (=='?') s
193 zipParams (t:ts) (p:ps) = t `mappend` p `mappend` zipParams ts ps
195 zipParams _ _ = fmtError (show (B.count '?' template) ++
196 " '?' characters, but " ++
197 show (length xs) ++ " parameters") q xs
199-- | Execute an @INSERT@, @UPDATE@, or other SQL query that is not
200-- expected to return results.
202-- Returns the number of rows affected.
204-- Throws 'FormatError' if the query could not be formatted correctly.
205execute :: (QueryParams q) => Connection -> Query -> q -> IO Int64
206execute conn template qs = do
207 result <- exec conn =<< formatQuery conn template qs
208 finishExecute conn template result
210-- | A version of 'execute' that does not perform query substitution.
211execute_ :: Connection -> Query -> IO Int64
212execute_ conn q@(Query stmt) = do
213 result <- exec conn stmt
214 finishExecute conn q result
216-- | Execute a multi-row @INSERT@, @UPDATE@, or other SQL query that is not
217-- expected to return results.
219-- Returns the number of rows affected.
221-- Throws 'FormatError' if the query could not be formatted correctly.
222executeMany :: (QueryParams q) => Connection -> Query -> [q] -> IO Int64
223executeMany _ _ [] = return 0
224executeMany conn q qs = do
225 result <- exec conn =<< formatMany conn q qs
226 finishExecute conn q result
228finishExecute :: Connection -> Query -> PQ.Result -> IO Int64
229finishExecute conn q result = do
230 status <- PQ.resultStatus result
233 ncols <- PQ.nfields result
235 then throwIO $ QueryError ("execute resulted in " ++ show ncols ++
238 nstr <- PQ.cmdTuples result
239 return $ case nstr of
240 Nothing -> 0 -- is this appropriate?
241 Just str -> toInteger str
243 ncols <- PQ.nfields result
244 throwIO $ QueryError ("execute resulted in " ++ show ncols ++
246 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
247 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
249 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
250 statusmsg <- PQ.resStatus status
251 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
252 throwIO $ SqlError { sqlState = state
253 , sqlNativeError = fromEnum status
254 , sqlErrorMsg = B.concat [ "execute: ", statusmsg
257 toInteger str = B.foldl' delta 0 str
260 if '0' <= c && c <= '9'
261 then 10 * acc + fromIntegral (ord c - ord '0')
262 else error ("finishExecute: not an int: " ++ B.unpack str)
266-- | Perform a @SELECT@ or other SQL query that is expected to return
267-- results. All results are retrieved and converted before this
270-- When processing large results, this function will consume a lot of
271-- client-side memory. Consider using 'fold' instead.
273-- Exceptions that may be thrown:
275-- * 'FormatError': the query string could not be formatted correctly.
277-- * 'QueryError': the result contains no columns (i.e. you should be
278-- using 'execute' instead of 'query').
280-- * 'ResultError': result conversion failed.
281query :: (QueryParams q, QueryResults r)
282 => Connection -> Query -> q -> IO [r]
283query conn template qs = do
284 result <- exec conn =<< formatQuery conn template qs
285 finishQuery conn template result
287-- | A version of 'query' that does not perform query substitution.
288query_ :: (QueryResults r) => Connection -> Query -> IO [r]
289query_ conn q@(Query que) = do
290 result <- exec conn que
291 finishQuery conn q result
293-- | Perform a @SELECT@ or other SQL query that is expected to return
294-- results. Results are streamed incrementally from the server, and
295-- consumed via a left fold.
297-- The result consumer must be carefully written to execute
298-- quickly. If the consumer is slow, server resources will be tied up,
299-- and other clients may not be able to update the tables from which
300-- the results are being streamed.
302-- When dealing with small results, it may be simpler (and perhaps
303-- faster) to use 'query' instead.
305-- This fold is /not/ strict. The stream consumer is responsible for
306-- forcing the evaluation of its result to avoid space leaks.
308-- Exceptions that may be thrown:
310-- * 'FormatError': the query string could not be formatted correctly.
312-- * 'QueryError': the result contains no columns (i.e. you should be
313-- using 'execute' instead of 'query').
315-- * 'ResultError': result conversion failed.
316fold :: (QueryParams q, QueryResults r) =>
318 -> Query -- ^ Query template.
319 -> q -- ^ Query parameters.
320 -> a -- ^ Initial state for result consumer.
321 -> (a -> r -> IO a) -- ^ Result consumer.
323fold conn template qs z f = withConnection conn $ \c -> do
324 success <- PQ.sendQuery c =<< formatQuery conn template qs
326 then finishFold conn c template z f
328 msg <- maybe "fold error" id <$> PQ.errorMessage c
329 throwIO $ SqlError { sqlNativeError = -1
333-- | A version of 'fold' that does not perform query substitution.
334fold_ :: (QueryResults r) =>
337 -> a -- ^ Initial state for result consumer.
338 -> (a -> r -> IO a) -- ^ Result consumer.
340fold_ conn q@(Query que) z f = withConnection conn $ \c -> do
341 success <- PQ.sendQuery c que
343 then finishFold conn c q z f
345 msg <- maybe "fold_ error" id <$> PQ.errorMessage c
346 throwIO $ SqlError { sqlNativeError = -1
350-- | A version of 'fold' that does not transform a state value.
351forEach :: (QueryParams q, QueryResults r) =>
353 -> Query -- ^ Query template.
354 -> q -- ^ Query parameters.
355 -> (r -> IO ()) -- ^ Result consumer.
357forEach conn template qs = fold conn template qs () . const
358{-# INLINE forEach #-}
360-- | A version of 'forEach' that does not perform query substitution.
361forEach_ :: (QueryResults r) =>
363 -> Query -- ^ Query template.
364 -> (r -> IO ()) -- ^ Result consumer.
366forEach_ conn template = fold_ conn template () . const
367{-# INLINE forEach_ #-}
369forM' :: (Ord n, Num n) => n -> n -> (n -> IO a) -> IO [a]
370forM' lo hi m = loop hi []
378finishQuery :: (QueryResults r) => Connection -> Query -> PQ.Result -> IO [r]
379finishQuery conn q result = do
380 status <- PQ.resultStatus result
383 throwIO $ QueryError "query resulted in a command response" q
385 ncols <- PQ.nfields result
386 fields <- forM' 0 (ncols-1) $ \column -> do
387 type_oid <- PQ.ftype result column
388 typename <- getTypename conn type_oid
390 nrows <- PQ.ntuples result
391 forM' 0 (nrows-1) $ \row -> do
392 values <- forM' 0 (ncols-1) (\col -> PQ.getvalue result row col)
393 case convertResults fields values of
394 Left err -> throwIO err
396 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
397 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
399 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
400 statusmsg <- PQ.resStatus status
401 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
402 throwIO $ SqlError { sqlState = state
403 , sqlNativeError = fromEnum status
404 , sqlErrorMsg = B.concat [ "query: ", statusmsg
408finishFold :: (QueryResults r) =>
412 -> a -- ^ Initial state for result consumer.
413 -> (a -> r -> IO a) -- ^ Result consumer.
415finishFold conn c q a_ f = loop a_
418 mres <- PQ.getResult c
422 stat <- PQ.resultStatus result
425 ncols <- PQ.nfields result
426 fields <- forM' 0 (ncols-1) $ \column -> do
427 type_oid <- PQ.ftype result column
428 typename <- getTypename conn type_oid
430 nrows <- PQ.ntuples result
431 a' <- foldM (\a row -> do
432 values <- forM' 0 (ncols-1) (PQ.getvalue result row)
433 case convertResults fields values of
434 Left err -> clear c >> throwIO err
439 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
440 statusmsg <- PQ.resStatus stat
441 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
445 sqlNativeError = fromEnum stat,
446 sqlErrorMsg = B.concat [ "fold: ", statusmsg
449 mres <- PQ.getResult c
454-- | Execute an action inside a SQL transaction.
456-- This function initiates a transaction with a \"@begin
457-- transaction@\" statement, then executes the supplied action. If
458-- the action succeeds, the transaction will be completed with
459-- 'Base.commit' before this function returns.
461-- If the action throws /any/ kind of exception (not just a
462-- PostgreSQL-related exception), the transaction will be rolled back using
463-- 'rollback', then the exception will be rethrown.
464withTransaction :: Connection -> IO a -> IO a
465withTransaction conn act = do
467 r <- act `onException` rollback conn
471-- | Rollback a transaction.
472-- rollback :: (MonadCatchIO m,MonadIO m) => Connection -> m ()
473rollback :: Connection -> IO ()
475 _ <- execute_ conn "ABORT"
478-- | Commit a transaction.
479-- commit :: (MonadCatchIO m,MonadIO m) => Connection -> m ()
480commit :: Connection -> IO ()
482 _ <- execute_ conn "COMMIT"
485-- | Begin a transaction.
486-- begin :: (MonadCatchIO m,MonadIO m) => Connection -> m ()
487begin :: Connection -> IO ()
489 _ <- execute_ conn "BEGIN"
492fmtError :: String -> Query -> [Action] -> a
493fmtError msg q xs = throw FormatError {
496 , fmtParams = map twiddle xs
498 where twiddle (Plain b) = toByteString b
499 twiddle (Escape s) = s
500 twiddle (Many ys) = B.concat (map twiddle ys)
504-- SQL-based applications are somewhat notorious for their
505-- susceptibility to attacks through the injection of maliciously
506-- crafted data. The primary reason for widespread vulnerability to
507-- SQL injections is that many applications are sloppy in handling
508-- user data when constructing SQL queries.
510-- This library provides a 'Query' type and a parameter substitution
511-- facility to address both ease of use and security.
515-- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally
516-- exposes a tiny API that is not compatible with the 'ByteString'
517-- API; this makes it difficult to construct queries from fragments of
518-- strings. The 'query' and 'execute' functions require queries to be
521-- To most easily construct a query, enable GHC's @OverloadedStrings@
522-- language extension and write your query as a normal literal string.
524-- > {-# LANGUAGE OverloadedStrings #-}
526-- > import Database.PostgreSQL.Simple
529-- > conn <- connect defaultConnectInfo
530-- > query conn "select 2 + 2"
532-- A 'Query' value does not represent the actual query that will be
533-- executed, but is a template for constructing the final query.
537-- Since applications need to be able to construct queries with
538-- parameters that change, this library provides a query substitution
541-- The 'Query' template accepted by 'query' and 'execute' can contain
542-- any number of \"@?@\" characters. Both 'query' and 'execute'
543-- accept a third argument, typically a tuple. When constructing the
544-- real query to execute, these functions replace the first \"@?@\" in
545-- the template with the first element of the tuple, the second
546-- \"@?@\" with the second element, and so on. If necessary, each
547-- tuple element will be quoted and escaped prior to substitution;
548-- this defeats the single most common injection vector for malicious
551-- For example, given the following 'Query' template:
553-- > select * from user where first_name = ? and age > ?
555-- And a tuple of this form:
557-- > ("Boris" :: String, 37 :: Int)
559-- The query to be executed will look like this after substitution:
561-- > select * from user where first_name = 'Boris' and age > 37
563-- If there is a mismatch between the number of \"@?@\" characters in
564-- your template and the number of elements in your tuple, a
565-- 'FormatError' will be thrown.
567-- Note that the substitution functions do not attempt to parse or
568-- validate your query. It's up to you to write syntactically valid
569-- SQL, and to ensure that each \"@?@\" in your query template is
570-- matched with the right tuple element.
574-- Automated type inference means that you will often be able to avoid
575-- supplying explicit type signatures for the elements of a tuple.
576-- However, sometimes the compiler will not be able to infer your
577-- types. Consider a care where you write a numeric literal in a
580-- > query conn "select ? + ?" (40,2)
582-- The above query will be rejected by the compiler, because it does
583-- not know the specific numeric types of the literals @40@ and @2@.
584-- This is easily fixed:
586-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)
588-- The same kind of problem can arise with string literals if you have
589-- the @OverloadedStrings@ language extension enabled. Again, just
590-- use an explicit type signature if this happens.
594-- Haskell lacks a single-element tuple type, so if you have just one
595-- value you want substituted into a query, what should you do?
597-- The obvious approach would appear to be something like this:
599-- > instance (Param a) => QueryParam a where
602-- Unfortunately, this wreaks havoc with type inference, so we take a
603-- different tack. To represent a single value @val@ as a parameter, write
604-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.
606-- Here's an example using a singleton list:
608-- > execute conn "insert into users (first_name) values (?)"
613-- Suppose you want to write a query using an @IN@ clause:
615-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')
617-- In such cases, it's common for both the elements and length of the
618-- list after the @IN@ keyword to vary from query to query.
620-- To address this case, use the 'In' type wrapper, and use a single
621-- \"@?@\" character to represent the list. Omit the parentheses
622-- around the list; these will be added for you.
626-- > query conn "select * from users where first_name in ?" $
627-- > In ["Anna", "Boris", "Carla"]
629-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be
630-- substituted instead, to ensure that your clause remains
631-- syntactically valid.
635-- If you know that you have many rows of data to insert into a table,
636-- it is much more efficient to perform all the insertions in a single
637-- multi-row @INSERT@ statement than individually.
639-- The 'executeMany' function is intended specifically for helping
640-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for
641-- query substitution are different than those for 'execute'.
643-- What 'executeMany' searches for in your 'Query' template is a
644-- single substring of the form:
648-- The rules are as follows:
650-- * The keyword @VALUES@ is matched case insensitively.
652-- * There must be no other \"@?@\" characters anywhere in your
655-- * There must one or more \"@?@\" in the parentheses.
657-- * Extra white space is fine.
659-- The last argument to 'executeMany' is a list of parameter
660-- tuples. These will be substituted into the query where the @(?,?)@
661-- string appears, in a form suitable for use in a multi-row @INSERT@
664-- Here is an example:
667-- > "insert into users (first_name,last_name) values (?,?)"
668-- > [("Boris","Karloff"),("Ed","Wood")]
670-- The query that will be executed here will look like this
671-- (reformatted for tidiness):
673-- > insert into users (first_name,last_name) values
674-- > ('Boris','Karloff'),('Ed','Wood')
678-- The 'query' and 'query_' functions return a list of values in the
679-- 'QueryResults' typeclass. This class performs automatic extraction
680-- and type conversion of rows from a query result.
682-- Here is a simple example of how to extract results:
684-- > import qualified Data.Text as Text
686-- > xs <- query_ conn "select name,age from users"
687-- > forM_ xs $ \(name,age) ->
688-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
690-- Notice two important details about this code:
692-- * The number of columns we ask for in the query template must
693-- exactly match the number of elements we specify in a row of the
694-- result tuple. If they do not match, a 'ResultError' exception
697-- * Sometimes, the compiler needs our help in specifying types. It
698-- can infer that @name@ must be a 'Text', due to our use of the
699-- @unpack@ function. However, we have to tell it the type of @age@,
700-- as it has no other information to determine the exact type.
704-- The type of a result tuple will look something like this:
708-- Although SQL can accommodate @NULL@ as a value for any of these
709-- types, Haskell cannot. If your result contains columns that may be
710-- @NULL@, be sure that you use 'Maybe' in those positions of of your
713-- > (Text, Maybe Int, Int)
715-- If 'query' encounters a @NULL@ in a row where the corresponding
716-- Haskell type is not 'Maybe', it will throw a 'ResultError'
721-- To specify that a query returns a single-column result, use the
724-- > xs <- query_ conn "select id from users"
725-- > forM_ xs $ \(Only dbid) -> {- ... -}
729-- Conversion of SQL values to Haskell values is somewhat
730-- permissive. Here are the rules.
732-- * For numeric types, any Haskell type that can accurately represent
733-- all values of the given PostgreSQL type is considered \"compatible\".
734-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@
735-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately
736-- represent a @SMALLINT@, so it is considered compatble with those types.
738-- * A numeric compatibility check is based only on the type of a
739-- column, /not/ on its values. For instance, a PostgreSQL 64-bit
740-- @BIGINT@ column will be considered incompatible with a Haskell
741-- 'Int16', even if it contains the value @1@.
743-- * If a numeric incompatibility is found, 'query' will throw a
746-- * The 'String' and 'Text' types are assumed to be encoded as
747-- UTF-8. If you use some other encoding, decoding may fail or give
748-- wrong results. In such cases, write a @newtype@ wrapper and a
749-- custom 'Result' instance to handle your encoding.
751getTypename :: Connection -> PQ.Oid -> IO ByteString
752getTypename conn@Connection{..} oid =
753 case oid2builtin oid of
754 Just builtin -> return $! builtin2typname builtin
755 Nothing -> modifyMVar connectionObjects $ \oidmap -> do
756 case IntMap.lookup (oid2int oid) oidmap of
757 Just name -> return (oidmap, name)
759 names <- query conn "SELECT typname FROM pg_type WHERE oid=?"
761 name <- case names of
762 [] -> return $ throw SqlError {
764 sqlErrorMsg = "invalid type oid",
768 _ -> fail "typename query returned more than one result"
769 -- oid is a primary key, so the query should
770 -- never return more than one result
771 return (IntMap.insert (oid2int oid) name oidmap, name)
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