-- Copyright 2010, 2011, 2012 Chris FornomoduleDatabase.TemplatePG(-- *Introduction-- $intro-- *Usage-- $usage-- **Compile-Time Parameters-- $compiletime-- *Caveats-- $caveats-- *Limitations and Workarounds-- **A Note About NULL-- $nulls-- **Tips-- $tips-- **Other Workarounds-- $otherPGException(..),pgConnect,pgDisconnect,queryTuples,queryTuple,execute,withTransaction,rollback,insertIgnore)whereimportDatabase.TemplatePG.ProtocolimportDatabase.TemplatePG.SQL-- $intro-- TemplatePG is designed with 2 goals in mind: safety and performance. The-- primary focus is on safety.---- To help ensure safety, it uses the PostgreSQL server to parse every query-- and statement in your code to infer types at compile-time. This means that-- in theory you cannot get a syntax error at runtime. Getting proper types at-- compile time has the nice side-effect that it eliminates run-time type-- casting and usually results in less code. This approach was inspired by-- MetaHDBC (<http://haskell.org/haskellwiki/MetaHDBC>) and PG'OCaml-- (<http://pgocaml.berlios.de/>).---- While compile-time query analysis eliminates many errors, it doesn't-- eliminate all of them. If you modify the database without recompilation or-- have an error in a trigger or function, for example, you can still trigger a-- 'PGException'.---- With that in mind, TemplatePG currently does a number of unsafe things. It-- doesn't properly close the connection with the PostgreSQL server. It doesn't-- handle unexpected messages from the server very gracefully, and it's not-- entirely safe when working with nullable result fields. I hope to fix all of-- these at some point in the future. In the meantime, use the software at your-- own risk. Note however that TemplatePG is currently powering-- <http://www.vocabulink.com/> with no problems yet. (For usage examples, you-- can see the Vocabulink source code at <http://jekor.com/vocabulink/vocabulink.tar.gz>).---- To improve performance, TemplatePG does not use prepared statements. In-- theory, this saves bandwidth (and a potential round-trip) and time for the-- extra step of binding parameters. Again in theory, this is also safe because-- we know the types of parameters at compile time. However, it still feels-- risky (and I would appreciate any audit of the code doing this, especially-- 'escapeString').---- Note that you'll need to use -XConstraintKinds to use TemplatePG.-- $usage-- 'queryTuples' does all the work ('queryTuple' and 'execute' are convenience-- functions).---- It's a Template Haskell function, so you need to splice it into your program-- with @$()@. It requires a 'Handle' to a PostgreSQL server, but can't be-- given one at compile-time, so you need to pass it after the splice:---- @h <- pgConnect ...-- -- tuples <- $(queryTuples \"SELECT * FROM pg_database\") h-- @---- To pass parameters to a query, include them in the string with {}. Most-- Haskell expressions should work. For example:---- @let owner = 33-- -- tuples <- $(queryTuples \"SELECT * FROM pg_database WHERE datdba = {owner} LIMIT {2 * 3}\") h-- @---- Note that parameters may only be used where PostgreSQL will allow them. This-- will not work:---- @tuples <- $(queryTuples \"SELECT * FROM {tableName}\") h@---- And in general, you cannot construct queries at run-time, since they-- wouldn't be available to be analyzed at compile time.-- $compiletime-- TemplatePG needs information about the database to connect to at compile-- time (in the form of environment variables).-- -- You must set at least @TPG_DB@:-- -- [@TPG_DB@] the database name to use-- -- [@TPG_USER@] the username to connect as (default: @postgres@)-- -- [@TPG_PASS@] the password to use (default: /empty/)-- -- [@TPG_HOST@] the host to connect to (default: @localhost@)-- -- [@TPG_PORT@] the port number to connect on (default: @5432@)-- -- You can set @TPG_DEBUG@ to get a rough protocol-level trace (pipe to-- @hexdump@).-- $caveats-- TemplatePG assumes that it has a UTF-8 connection to a UTF-8 database.---- TemplatePG does not bind parameters with prepared statements (at run-time),-- instead it relies on its own type conversion and string escaping. The-- technique might have a security vulnerability. You should also set-- @standard_conforming_strings = on@ in your @postgresql.conf@.-- -- I've included 'withTransaction', 'rollback', and 'insertIgnore', but they've-- not been thoroughly tested, so use them at your own risk.-- $nulls-- Sometimes TemplatePG cannot determine whether or not a result field can-- potentially be @NULL@. In those cases it will assume that it can. Basically,-- any time a result field is not immediately tracable to an originating table-- and column (such as when a function is applied to a result column), it's-- assumed to be nullable and will be returned as a 'Maybe' value.---- Additionally, you cannot directly use @NULL@ values in parameters. As a-- workaround, you might have to use 2 or more separate queries (and @DEFAULT-- NULL@) to @INSERT@ rows with @NULL@s.---- Nullability is indicated incorrectly in the case of outer joins. TemplatePG-- incorrectly infers that a field cannot be @NULL@ when it's able to trace the-- result field back to a non-@NULL@ table column. As a workround, you can wrap-- columns with @COALESCE()@ to force them to be returned as 'Maybe' values.-- $other-- There's no support for reading time intervals yet. As a workaround, you can-- use @extract(epoch from ...)::int@ to get the interval as a number of-- seconds.-- $tips-- If you find yourself pattern matching on result tuples just to pass them on-- to functions, you can use @uncurryN@ from the tuple package. The following-- examples are equivalent.---- @(a, b, c) <- $(queryTuple \"SELECT a, b, c FROM {tableName} LIMIT 1\")---- someFunction a b c-- @---- @uncurryN someFunction \`liftM\` $(queryTuple \"SELECT a, b, c FROM {tableName} LIMIT 1\")-- @