This is pgin.tcl/README, describing pgin.tcl: A PostgreSQL interface in Tcl Last updated for pgin.tcl-3.0.2 on 2008-04-26 The project home page is: http://pgfoundry.org/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://gborg.postgresql.org/project/pgtclng/ (It will be moving to pgfoundry soon.) The same test suite is used to verify both interfaces. This is version 3 of pgin.tcl, which does Unicode character set encoding and decoding. This version has been 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 (Tcl 8.3.x has not been tested lately but might work) PostgreSQL-7.4 or higher Recent testing used: PostgreSQL-8.3.0 and 8.2.5, and Tcl-8.4.15. Platforms: Linux, Windows NT/2000; should run on all systems which have the proper Tcl version, but others are untested. 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 (bundled) libpgtcl calls + Does large object calls + Supports listen/notify + Supports replacing the notice handler (extension) + Supports the new pg_execute call + Supports PostgreSQL MD5 challenge/response authentication + New pg_result -cmdTuples returns the number of tuples affected by an INSERT, DELETE, or UPDATE (feature in PostgreSQL 7.4 libpgtcl) + Supports distinguishing NULL database values from empty strings (extension) + Implements new pg_result -list, and pg_result -llist (as found Gborg 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 Gborg 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] LIMITATIONS and DIFFERENCES: + pg_connect supports only the newer "-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 libpgtcl, but nobody should be relying on these anyway. + No pg_on_connection_loss (New at PostgreSQL 7.3). + No asynchronous calls (found in Gborg 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. + [Newly 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). + [Newly 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. RELEASE ISSUES: This version 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.