Read Me

This is pgintcl/README, describing pgintcl: A PostgreSQL interface in Tcl
Last updated for pgintcl-3.4.0 on 2011-09-17
The project home page is: http://sourceforge.net/projects/pgintcl/
-----------------------------------------------------------------------------

OVERVIEW:

This is a pure-Tcl interface to the PostgreSQL Database Management System.
It implements almost all the commands in libpgtcl, the Tcl interface
bundled with PostgreSQL (until release 8.0), plus it has some extensions.
But it is written entirely in Tcl, so does not require compilation for a
specific platform.

I wrote this to be able to use Tcl/Tk database clients on platforms where
the PostgreSQL client library (libpq) and the Tcl interface (libpgtcl) are
not available (or were not available at the time, or were too much trouble
to build).

pgin.tcl uses the Tcl binary data and TCP socket features to communicate
directly with a PostgreSQL database server, using the internal PostgreSQL
frontend/backend protocol. Therefore, pgin.tcl is dependent on the
protocol, rather than being protected from its details as are libpq-based
applications. This version of pgin.tcl uses version 3 of the PostgreSQL
protocol, and only communicates with PostgreSQL-7.4 and higher servers.

pgin.tcl is also highly compatible with pgtcl-ng, the "Next Generation"
libpq-based implementation of the pgtcl interface. pgtcl-ng can be found at
  http://sourceforge.net/projects/pgtclng/
The same test suite is used to verify both interfaces.

Version 3 of pgin.tcl added Unicode character set encoding and decoding.
It was tested with LATIN1 and UTF8 database encodings, as well as
SQL_ASCII. (Note SQL_ASCII encoded databases are meant for 7-bit ASCII
characters only. Do not use SQL_ASCII databases if your data includes
non-ASCII characters.) It should work with any PostgreSQL database
encoding, but user testing is encouraged. (The previous version 2 of
pgin.tcl does not include character set encoding handling. It may only work
properly with SQL_ASCII encoded databases.)


REQUIREMENTS:

   Tcl-8.4.4 or higher, with the latest Tcl-8.5.x recommended.
   PostgreSQL-9.1.x, 9.0.x, or 8.4.x database server.
   Recent testing used the following:
      Database server: PostgreSQL-9.1.0, 9.0.4, and 8.4.8
      Client on Linux: Tcl-8.6b2 (beta), Tcl-8.5.9, and Tcl-8.4.19
      Client on Windows XP: ActiveState Tcl-8.5.10 and Tcl-8.4.19
   (Older version of PostgreSQL and Tcl might work but are no longer tested.)
   Pgin.tcl should be usable on all platforms with Tcl.

CONTENTS:

  Documentation:
         Note: In the zip file distribution only, these documentation
         files have a ".txt" extension and MS-DOS line endings.
    README ........... This file
    COPYING .......... The license for pgin.tcl (BSD/Berkeley Open Source)
    NEWS ............. Release information and change history
    REFERENCE ........ Reference documentation for programmers using pgin.tcl
    INTERNALS ........ Some information about the innards of pgin.tcl

  Scripts:

    pgin.tcl ......... This is the complete implementation of the interface.
    pkgIndex.tcl ..... Package index file
    tkpsql.tcl ....... An example wish script for interactive database querying


FEATURES:

+ Written completely in Tcl 
+ Implements virtually all the standard (original, bundled) libpgtcl calls
+ Does large object calls
+ Supports listen/notify
+ Supports passing a payload with NOTIFY (PostgreSQL-9.0.0 and higher)
+ Supports replacing the notice handler (extension)
+ Supports pg_execute command
+ Supports PostgreSQL MD5 challenge/response authentication
+ pg_result -cmdTuples returns the number of tuples affected by an 
  INSERT, DELETE, or UPDATE
+ Supports distinguishing NULL database values from empty strings (extension)
+ Implements pg_result -list, and pg_result -llist (as found pgtcl)
+ Implements pg_escape_string (was in beta Gborg pgtcl CVS, but removed),
  and pg_quote (from Gborg pgtcl)
+ Execute prepared statements with: pg_exec_prepared, including sending
  and receiving un-escaped binary data (extension)
+ Get PostgreSQL parameters with: pg_parameter_status (extension)
+ Get transaction status with: pg_transaction_status (extension)
+ Access expanded error message fields with: pg_result -errorField
  (extension). This was extended [at 2.2.0] to also apply to pg_result -error
  for compatibility with pgtcl.
+ Access extended attribute information with: pg_result -lxAttributes
  (extension)
+ Get command status tag with pg_result -cmdStatus (extension) [New: 2.0.1]
+ Separate parse and execute with: pg_exec_params, binary safe [New: 2.1.0]
+ Escape/unescape bytea with: pg_escape_bytea, pg_unescape_bytea [New: 2.2.0]
+ Return query results as a dictionary with pg_result -dict [New: 3.3.0]
+ Access to process ID (PID) of backend and in notifications [New: 3.4.0]


LIMITATIONS and DIFFERENCES:

+ pg_connect supports only the "-conninfo" style.
+ Does not support $HOME/.pgpass password file.
+ Only talks to v3 backend (PostgreSQL 7.4 or higher required).
+ Uses only TCP/IP sockets (defaults host to localhost, postmaster needs -i),
  does not support Unix Domain sockets.
+ Notification messages are only received while reading query results.
+ Performance isn't great, especially when retrieving large amounts of data.
+ The values of connection handles and result handles are of a different
  format than other implementations, but nobody should be relying on these.
+ No pg_on_connection_loss (New at PostgreSQL 7.3).
+ No asynchronous calls (found in pgtcl and pgtcl-ng).
+ Support for COPY FROM/TO is not compatible with other versions of the
  interface - must use pg_copy_read and pg_copy_write, no I/O directly to
  connection handle.
+ With other pgtcl's, you can have up to 128 active result structures (so leaks
  can be caught). pgin.tcl has no limits and will not catch result structure
  leaks.
+ [Added at 2.1.0] Do not use "return -code N" (for N>4) in the script
  body for pg_select or pg_execute, because the effect is not well defined.
  You can safely use return, break, continue, and error (either directly
  or via return -code).
+ [Added at 2.2.0] pg_escape_bytea (and pg_unescape_bytea, to a
  lesser extent) is quite slow. Using it on large bytea objects is not
  recommended; you should use binary prepared queries instead.
+ [Added at 3.1.0] Whether or not you use the $conn argument to the string
  and bytea escape routines, pgin.tcl does not use encoding-aware escaping.
+ [Added at 3.2.0] pg_escape_bytea always uses the older 'escape' encoding
  in the returned result, never the newer 'hex' encoding.


RELEASE ISSUES:

Versions 3.4.0 and up handle notification names (also known as channel
names) in pg_listen differently from previous versions. This can result in
compatibility problems if you used mixed-case names in pg_listen. Starting
with version 3.4.0, pgintcl folds the channel name to lower case unless it
is in double quotes. This now matches the behavior of pgtcl-ng. See the
REFERENCE file and pgintcl bug #3410251 for more details.

Versions 3.3.0 and up require Tcl 8.4 or higher. Previous versions checked
for Tcl 8.3 or higher, but were not actually tested with Tcl 8.3.

Versions 3.2.0 and up pass a payload argument to a notification listener handler
procedure if a non-empty payload was provided in the SQL NOTIFY command.
See the NEWS file for more information and compatibility issues.

Version 3 does encoding and decoding of character data, as described in
the REFERENCE file. It also sets the PostgreSQL parameter
CLIENT_ENCODING to UNICODE when a connection is opened to the server.  This
is the same behavior as Pgtcl and pgtcl-ng.  This informs PostgreSQL that
UNICODE data (encoded as UTF-8) will be sent and received.

Note that the client application using pgin.tcl can have any encoding
which Tcl supports. Tcl converts between the client encoding and Unicode,
and the PostgreSQL server converts between Unicode and the database
encoding. This assumes the database encoding is other than SQL_ASCII.

            * * * CAUTION * * *

  Do not store non-ASCII characters in character or text fields in a
  PostgreSQL database which was created with encoding SQL_ASCII.
  The SQL_ASCII encoding provides no information to PostgreSQL on
  how to translate characters, so the server will be unable to
  translate.  Applications using a Tcl interface, including
  pgin.tcl, will encode these characters using UTF-8 for storage
  in the database, but PostgreSQL will not know it due to the
  SQL_ASCII encoding setting.  The result is that it may be
  impossible to access the data correctly from other applications.
  Always use the correct encoding when creating a database: for
  example, LATIN1 or Unicode.

  Pgin.tcl-2.x and older do not convert to/from Unicode and do not set
  client_encoding at all. These older versions may not work with non-ASCII
  characters in any database encoding.

  At this time, Pgin.tcl does not recode the connection string parameters
  such as Username, Database Name, or Password. Non-ASCII characters in these
  fields will probably not work.


Older Information:

There are some incompatibilities between this release and pre-2.0.0 releases:
  + pg_parameter_status can no longer fetch all parameters at once;
  + "pg_configure nulls" option is no longer available. The only way
    to distinguish NULL from empty string now is with pg_result -getNull.
  + Changes in large object call error handling.
  + COPY FROM/TO must use pg_copy_read/pg_copy_write; you cannot read or
    write copy data from the connection.
You will have to change your application if it relies on behavior which
changed.  See the file NEWS for more information.


INSTALLATION AND USAGE:

There is no install script. Just copy the script "pgin.tcl" anywhere your
application can access it.  In your application, insert "source .../pgin.tcl"
at the top level, where ... is the directory. This must be run at the top
level, so if you need it inside a proc use uplevel as shown below.

Optionally, you can install and use pgin.tcl as a Tcl package. You should
copy pgin.tcl and pkgIndex.tcl into a sub-directory of your Tcl
installation package library root directory (or you can extend auto_path:
see the Tcl documentation for the 'package' and 'pkgMkIndex' commands).
Then your application can load pgin.tcl with the following:
           package require pgintcl


You can use the included "tkpsql.tcl" script to try it out.  This is a
simple interactive GUI program to issue database queries, vaguely like the
Sybase ASA "dbisql" program.  On **ix systems, type "wish tkpsql.tcl" to
start it; under Windows you should be able to double click on it from
Explorer. You need to press F1 or click on the Run button after each query.