-- Copyright 2010, 2011 Chris Forno

module Database.TemplatePG (-- *Introduction
                            -- $intro

                            -- *Usage
                            -- $usage

                            -- **Compile-Time Parameters
                            -- $compiletime

                            -- *Caveats
                            -- $caveats

                            -- *Limitations and Workarounds
                            -- **A Note About NULL
                            -- $nulls

                            -- **Other Workarounds
                            -- $other
                             PGException(..)
                           , pgConnect
                           , pgDisconnect
                           , queryTuples
                           , queryTuple
                           , execute
                           , withTransaction
                           , rollback
                           , insertIgnore ) where

import Database.TemplatePG.Protocol
import Database.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').

-- $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.