Author: turnstep
Date: Sun Jan 13 20:54:09 2008
New Revision: 10548
Modified:
DBD-Pg/trunk/Changes
DBD-Pg/trunk/Pg.pm
DBD-Pg/trunk/Pg.xs
DBD-Pg/trunk/dbdimp.c
DBD-Pg/trunk/dbdimp.h
DBD-Pg/trunk/t/07copy.t
Log:
Write new COPY methods, and deprecate pg_putline and pg_getline.
Thought about simply replaceing them, but there was no easy way
to make them polymorphic, and better to make a clean break from
that lousy "size of variable" requirement. Instead, we'll again
follow libpq's naming lead and we thus now have pg_getcopydata,
pg_putcopydata, pg_putcopyend, and even pg_getcopydata_async just
to be complete, although it is not likely to be used. All of this
was done in one concentrated burst and I'm tired, so there may be
some rough edges: eyeballs welcome. Note that the old functions
will continue to work just fine.
Modified: DBD-Pg/trunk/Changes
==============================================================================
--- DBD-Pg/trunk/Changes (original)
+++ DBD-Pg/trunk/Changes Sun Jan 13 20:54:09 2008
@@ -1,6 +1,9 @@
('GSM' is Greg Sabino Mullane, [EMAIL PROTECTED])
2.0.0
+ - Overhaul COPY functions: deprecate pg_getline, pg_putline,
+ and pg_endcopy. The new functions are pg_getcopydata,
+ pg_getcopydata_asyn, pg_putcopydata, and pg_putcopyend. [GSM]
- Change $dbh->{User} to $dbh->{Username} [GSM]
- Add three new columns to column_info, to return unquoted
version: pg_schema, pg_table, and pg_columns. Add all
@@ -12,7 +15,7 @@
(CPAN bug #24579) [GSM]
- Use adbin, not adsrc, when figuring out the sequence name for the
last_insert_id() method. This allows the function to work
properly
- if the sequence name is changed. Not that {pg_cache=>0} should
be
+ if the sequence name is changed. Note that {pg_cache=>0} should
be
passed to the function if you expect this might happen.
(CPAN bug #30924) [GSM]
- Use unsigned chars when parsing passed-in queries, preventing UTF-8
Modified: DBD-Pg/trunk/Pg.pm
==============================================================================
--- DBD-Pg/trunk/Pg.pm (original)
+++ DBD-Pg/trunk/Pg.pm Sun Jan 13 20:54:09 2008
@@ -106,6 +106,10 @@
DBD::Pg::db->install_method("pg_cancel");
DBD::Pg::db->install_method("pg_endcopy");
DBD::Pg::db->install_method("pg_getline");
+ DBD::Pg::db->install_method("pg_getcopydata");
+ DBD::Pg::db->install_method("pg_getcopydata_async");
+ DBD::Pg::db->install_method("pg_putcopydata");
+ DBD::Pg::db->install_method("pg_putcopyend");
DBD::Pg::db->install_method("pg_ping");
DBD::Pg::db->install_method("pg_putline");
DBD::Pg::db->install_method("pg_ready");
@@ -1770,7 +1774,7 @@
=item B<state>
- $str = $h->state;
+< $str = $h->state;
Supported by this driver. Returns a five-character "SQLSTATE" code.
Success is indicated by a "00000" code, which gets mapped to an
@@ -3411,11 +3415,10 @@
=head2 COPY support
-DBD::Pg supports the COPY command through three functions: pg_putline,
-pg_getline, and pg_endcopy. The COPY command allows data to be quickly
-loaded or read from a table. The basic process is to issue a COPY
-command via $dbh->do(), do either $dbh->pg_putline or $dbh->pg_getline,
-and then issue a $dbh->pg_endcopy (for pg_putline only).
+DBD::Pg allows for the quick (bulk) reading and storing of data by using
+the COPY command. The basic process is to use $dbh->do to issue a
+COPY command, and then to either add rows using pg_putcopydata, or to
+read them by using pg_getcopydata.
The first step is to put the server into "COPY" mode. This is done by
sending a complete COPY command to the server, by using the do() method.
@@ -3423,64 +3426,72 @@
$dbh->do("COPY foobar FROM STDIN");
-This would tell the server to enter a COPY IN state. It is now ready to
-receive information via the pg_putline method. The complete syntax of the
+This would tell the server to enter a COPY OUT state. It is now ready to
+receive information via the pg_putcopydata method. The complete syntax of the
COPY command is more complex and not documented here: the canonical
PostgreSQL documentation for COPY be found at:
http://www.postgresql.org/docs/current/static/sql-copy.html
Once the COPY command has been issued, no other SQL commands are allowed
-until after pg_endcopy has been successfully called. If in a COPY IN state,
-you cannot use pg_getline, and if in COPY OUT state, you cannot use pg_putline.
+until pg_putcopyend() has been issued, of the final pg_getcopydata has
+been called.
-=over 4
-
-=item B<pg_putline>
-
-Used to put data into a table after the server has been put into COPY IN mode
-by calling "COPY tablename FROM STDIN". The only argument is the data you want
-inserted. The default delimiter is a tab character, but this can be changed in
-the COPY statement. Returns a 1 on successful input. Examples:
-
- $dbh->do("COPY mytable FROM STDIN");
- $dbh->pg_putline("123\tPepperoni\t3\n");
- $dbh->pg_putline("314\tMushroom\t8\n");
- $dbh->pg_putline("6\tAnchovies\t100\n");
- $dbh->pg_endcopy;
+Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
+deprecated in favor of the pg_getcopydata, pg_putcopydata, and
+pg_putcopyend.
- ## This example uses explicit columns and a custom delimiter
- $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
- $dbh->pg_putline("Pepperoni~123\n");
- $dbh->pg_putline("Mushroom~314\n");
- $dbh->pg_putline("Anchovies~6\n");
- $dbh->pg_endcopy;
+=over 4
-=item B<pg_getline>
+=item B<pg_getcopydata>
Used to retrieve data from a table after the server has been put into COPY OUT
-mode by calling "COPY tablename TO STDOUT". The first argument to pg_getline
is
-the variable into which the data will be stored (this variable should not be
undefined,
-or it may throw a warning). The second argument is the size of the variable:
this should
-be greater than the expected size of the row. Returns a 1 on success, and an
empty
-string when the last row has been fetched. Example:
+mode by calling "COPY tablename TO STDOUT". The first argument to
pg_getcopydata
+is the variable into which the data will be stored (this variable should not
+be undefined, or it may throw a warning, although it may be a reference). This
+argument returns a number greater than 1 indicating the new size of the
variable,
+or a -1 when the COPY has finished. Once a -1 has been returned, no other
action is
+necessary, as COPY mode will have already terminated. Example:
$dbh->do("COPY mytable TO STDOUT");
my @data;
my $x=0;
- 1 while($dbh->pg_getline($data[$x++], 100));
- pop @data; ## Remove final "\\.\n" line
+ 1 while $dbh->pg_getcopydata($data[$x++]) > 0;
+
+There is also a variation of this function called pg_getcopydata_async, which,
+as the name suggests, returns immediately. The only difference from the
original
+function is that this version may return a 0, indicating that the row is not
+ready to be delivered yet. When this happens, the variable has not been
changed,
+and you will need to call the function again until you get a non-zero result.
+
+=item B<pg_putcopydata>
+
+Used to put data into a table after the server has been put into COPY IN mode
+by calling "COPY tablename FROM STDIN". The only argument is the data you want
+inserted. Issue a pg_putcopyend() when you have added all your rows.
-=item B<pg_endcopy>
+The default delimiter is a tab character, but this can be changed in
+the COPY statement. Returns a 1 on successful input. Examples:
-When you are finished with pg_putline, call pg_endcopy to put the server back
in
-a normal state. Returns a 1 on success. This method will fail if called when
not
-in a COPY IN or COPY OUT state. Note that you no longer need to send "\\.\n"
when
-in COPY IN mode: pg_endcopy will do this for you automatically as needed.
-Note that pg_endcopy is only needed after getline if you are using the
-old-style method, $dbh->func($data, 100, 'getline'). If using pg_getline,
-pg_endcopy should not be used.
+ ## Simple example:
+ $dbh->do("COPY mytable FROM STDIN");
+ $dbh->pg_putcopydata("123\tPepperoni\t3\n");
+ $dbh->pg_putcopydata("314\tMushroom\t8\n");
+ $dbh->pg_putcopydata("6\tAnchovies\t100\n");
+ $dbh->pg_putcopyend();
+ ## This example uses explicit columns and a custom delimiter
+ $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
+ $dbh->pg_putcopydata("Pepperoni~123\n");
+ $dbh->pg_putcopydata("Mushroom~314\n");
+ $dbh->pg_putcopydata("Anchovies~6\n");
+ $dbh->pg_putcopyend();
+
+=item B<pg_putcopyend>
+
+When you are finished with pg_putcopydata, call pg_putcopyend to let the
server know
+that you are done, and it will return to a normal, non-COPY state. Returns a 1
on
+success. This method will fail if called when not in a COPY IN or COPY OUT
state.
=back
Modified: DBD-Pg/trunk/Pg.xs
==============================================================================
--- DBD-Pg/trunk/Pg.xs (original)
+++ DBD-Pg/trunk/Pg.xs Sun Jan 13 20:54:09 2008
@@ -529,6 +529,42 @@
SvSETMAGIC(ST(1));
ST(0) = (-1 != ret) ? &sv_yes : &sv_no;
+I32
+pg_getcopydata(dbh, dataline)
+ INPUT:
+ SV * dbh
+ CODE:
+ RETVAL = pg_db_getcopydata(dbh, SvROK(ST(1)) ? SvRV(ST(1)) :
ST(1), 0);
+ OUTPUT:
+ RETVAL
+
+I32
+pg_getcopydata_async(dbh, dataline)
+ INPUT:
+ SV * dbh
+ CODE:
+ RETVAL = pg_db_getcopydata(dbh, SvROK(ST(1)) ? SvRV(ST(1)) :
ST(1), 1);
+ OUTPUT:
+ RETVAL
+
+I32
+pg_putcopydata(dbh, dataline)
+ INPUT:
+ SV * dbh
+ SV * dataline
+ CODE:
+ RETVAL = pg_db_putcopydata(dbh, dataline);
+ OUTPUT:
+ RETVAL
+
+I32
+pg_putcopyend(dbh)
+ INPUT:
+ SV * dbh
+ CODE:
+ RETVAL = pg_db_putcopyend(dbh);
+ OUTPUT:
+ RETVAL
void
getline(dbh, buf, len)
Modified: DBD-Pg/trunk/dbdimp.c
==============================================================================
--- DBD-Pg/trunk/dbdimp.c (original)
+++ DBD-Pg/trunk/dbdimp.c Sun Jan 13 20:54:09 2008
@@ -2431,8 +2431,14 @@
croak("execute on disconnected handle");
/* Abort if we are in the middle of a copy */
- if (imp_dbh->copystate!=0)
- croak("Must call pg_endcopy before issuing more commands");
+ if (imp_dbh->copystate != 0) {
+ if (PGRES_COPY_IN == imp_dbh->copystate) {
+ croak("Must call pg_putcopyend before issuing more
commands");
+ }
+ else {
+ croak("Must call pg_getcopydata until no more rows
before issuing more commands");
+ }
+ }
/* If we are still waiting on an async, handle it */
if (imp_dbh->async_status) {
@@ -3300,7 +3306,7 @@
/* We must be in COPY OUT state */
if (PGRES_COPY_OUT != imp_dbh->copystate)
- croak("pg_getline can only be called directly after issuing a
COPY OUT command\n");
+ croak("pg_getline can only be called directly after issuing a
COPY command\n");
length = 0; /* Make compilers happy */
if (dbis->debug >= 5)
@@ -3326,6 +3332,135 @@
/* ================================================================== */
+int
+pg_db_getcopydata (SV * dbh, SV * dataline, int async)
+{
+ D_imp_dbh(dbh);
+ int copystatus;
+ char * tempbuf;
+
+ if (dbis->debug >= 4)
+ (void)PerlIO_printf(DBILOGFP, "dbdpg: pg_db_getcopydata\n");
+
+ /* We must be in COPY OUT state */
+ if (PGRES_COPY_OUT != imp_dbh->copystate)
+ croak("pg_getcopydata can only be called directly after issuing
a COPY command\n");
+
+ tempbuf = NULL;
+
+ copystatus = PQgetCopyData(imp_dbh->conn, &tempbuf, async);
+
+ if (copystatus > 0) {
+ sv_setpv(dataline, tempbuf);
+ PQfreemem(tempbuf);
+ }
+ else if (0 == copystatus) { /* async and still in progress: consume and
return */
+ if (!PQconsumeInput(imp_dbh->conn)) {
+ pg_error(dbh, PGRES_FATAL_ERROR,
PQerrorMessage(imp_dbh->conn));
+ return -2;
+ }
+ }
+ else if (-1 == copystatus) {
+ PGresult * result;
+ ExecStatusType status;
+ SvCUR_set(dataline, 0);
+ imp_dbh->copystate=0;
+ result = PQgetResult(imp_dbh->conn);
+ status = _sqlstate(imp_dbh, result);
+ PQclear(result);
+ if (PGRES_COMMAND_OK != status) {
+ pg_error(dbh, status, PQerrorMessage(imp_dbh->conn));
+ }
+ }
+ else {
+ pg_error(dbh, PGRES_FATAL_ERROR, PQerrorMessage(imp_dbh->conn));
+ }
+ return copystatus;
+}
+
+/* ================================================================== */
+int
+pg_db_putcopydata (SV * dbh, SV * dataline)
+{
+ D_imp_dbh(dbh);
+ int copystatus;
+
+ if (dbis->debug >= 4)
+ (void)PerlIO_printf(DBILOGFP, "dbdpg: pg_db_putcopydata\n");
+
+ /* We must be in COPY IN state */
+ if (PGRES_COPY_IN != imp_dbh->copystate)
+ croak("pg_putcopydata can only be called directly after issuing
a COPY command\n");
+
+ copystatus = PQputCopyData
+ (
+ imp_dbh->conn,
+ SvUTF8(dataline) ? SvPVutf8_nolen(dataline) :
SvPV_nolen(dataline),
+ (int)sv_len(dataline)
+ );
+
+ if (1 == copystatus) {
+ }
+ else if (0 == copystatus) { /* non-blocking mode only */
+ }
+ else {
+ pg_error(dbh, PGRES_FATAL_ERROR, PQerrorMessage(imp_dbh->conn));
+ }
+
+ return copystatus == 1 ? 1 : 0;
+}
+
+/* ================================================================== */
+int pg_db_putcopyend (SV * dbh)
+{
+
+ /* If in COPY_IN mode, terminate the COPYing */
+ /* Returns 1 on success, otherwise 0 (plus a probably warning/error) */
+
+ D_imp_dbh(dbh);
+ int copystatus;
+
+ if (dbis->debug >= 4)
+ (void)PerlIO_printf(DBILOGFP, "dbdpg: dbd_pg_putcopyend\n");
+
+ if (0 == imp_dbh->copystate) {
+ warn("pg_putcopyend cannot be called until a COPY is issued");
+ return 0;
+ }
+
+ if (PGRES_COPY_OUT == imp_dbh->copystate) {
+ warn("PQputcopyend does not need to be called when using
PGgetcopydata");
+ return 0;
+ }
+
+ /* Must be PGRES_COPY_IN at this point */
+
+ copystatus = PQputCopyEnd(imp_dbh->conn, NULL);
+
+ if (1 == copystatus) {
+ PGresult * result;
+ ExecStatusType status;
+ imp_dbh->copystate = 0;
+ result = PQgetResult(imp_dbh->conn);
+ status = _sqlstate(imp_dbh, result);
+ PQclear(result);
+ if (PGRES_COMMAND_OK != status) {
+ pg_error(dbh, status, PQerrorMessage(imp_dbh->conn));
+ return 0;
+ }
+ return 1;
+ }
+ else if (0 == copystatus) { /* non-blocking mode only */
+ return 0;
+ }
+ else {
+ pg_error(dbh, PGRES_FATAL_ERROR, PQerrorMessage(imp_dbh->conn));
+ return 0;
+ }
+}
+
+
+/* ================================================================== */
int pg_db_endcopy (SV * dbh)
{
D_imp_dbh(dbh);
Modified: DBD-Pg/trunk/dbdimp.h
==============================================================================
--- DBD-Pg/trunk/dbdimp.h (original)
+++ DBD-Pg/trunk/dbdimp.h Sun Jan 13 20:54:09 2008
@@ -109,6 +109,10 @@
/* Other (non-static) functions we have added to dbdimp.c */
+int pg_db_getcopydata (SV *dbh, SV * dataline, int async);
+int pg_db_putcopydata (SV *dbh, SV * dataline);
+int pg_db_putcopyend (SV * dbh);
+
int dbd_db_ping(SV *dbh);
int dbd_db_getfd (SV *dbh, imp_dbh_t *imp_dbh);
SV * dbd_db_pg_notifies (SV *dbh, imp_dbh_t *imp_dbh);
Modified: DBD-Pg/trunk/t/07copy.t
==============================================================================
--- DBD-Pg/trunk/t/07copy.t (original)
+++ DBD-Pg/trunk/t/07copy.t Sun Jan 13 20:54:09 2008
@@ -4,13 +4,15 @@
use strict;
use warnings;
+use Data::Dumper;
use Test::More;
+use Test::Warn;
use lib 't','.';
require 'dbdpg_test_setup.pl';
select(($|=1,select(STDERR),$|=1)[1]);
if (defined $ENV{DBI_DSN}) {
- plan tests => 25;
+ plan tests => 54;
} else {
plan skip_all => 'Cannot run test unless DBI_DSN is defined. See the
README file';
}
@@ -137,6 +139,168 @@
ok($@, 'pg_endcopy fails when called twice after COPY OUT');
+
+
+
+
+
+
+##
+## Test the new COPY methods
+##
+
+$dbh->do("DELETE FROM $table");
+
+my $t=q{pg_putcopydata fails if not after a COPY statement};
+eval {
+ $dbh->pg_putcopydata("pizza\tpie");
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{pg_getcopydata fails if not after a COPY statement};
+eval {
+ $dbh->pg_getcopydata($data[0]);
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{pg_getcopydata_async fails if not after a COPY statement};
+eval {
+ $dbh->pg_getcopydata_async($data[0]);
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{pg_putcopyend warns but does not die if not after a COPY statement};
+warning_like (sub { $dbh->pg_putcopyend(); }, qr/until a COPY/, $t);
+
+$t=q{pg_getcopydata does not work if we are using COPY .. FROM};
+$dbh->rollback();
+$dbh->do("COPY $table FROM STDIN");
+eval {
+ $dbh->pg_getcopydata($data[0]);
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{pg_putcopydata does not work if we are using COPY .. TO};
+$dbh->rollback();
+$dbh->do("COPY $table TO STDOUT");
+eval {
+ $dbh->pg_putcopydata("pizza\tpie");
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{pg_putcopydata works and returns a 1 on success};
+$dbh->rollback();
+$dbh->do("COPY $table FROM STDIN");
+$result = $dbh->pg_putcopydata("15\tBlueberry");
+is ($result, 1, $t);
+
+$t=q{pg_putcopydata works on second call};
+$dbh->rollback();
+$dbh->do("COPY $table FROM STDIN");
+$result = $dbh->pg_putcopydata("16\tMoreBlueberries");
+is ($result, 1, $t);
+
+$t=q{pg_putcopydata fails with invalid data};
+$dbh->rollback();
+$dbh->do("COPY $table FROM STDIN");
+eval {
+ $dbh->pg_putcopydata();
+};
+ok($@, $t);
+
+$t=q{Calling pg_getcopydata gives an errors when in the middle of COPY ..
FROM};
+eval {
+ $dbh->pg_getcopydata($data[0]);
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{Calling do() gives an error when in the middle of COPY .. FROM};
+eval {
+ $dbh->do('SELECT 123');
+};
+like($@, qr{call pg_putcopyend}, $t);
+
+$t=q{pg_putcopydata works after a rude non-COPY attempt};
+eval {
+ $result = $dbh->pg_putcopydata("17\tMoreBlueberries");
+};
+is($@, q{}, $t);
+is ($result, 1, $t);
+
+$t=q{pg_putcopyend works and returns a 1};
+eval {
+ $result = $dbh->pg_putcopyend();
+};
+is($@, q{}, $t);
+is ($result, 1, $t);
+
+$dbh->commit();
+$t=q{pg_putcopydata fails after pg_putcopyend is called};
+eval {
+ $result = $dbh->pg_putcopydata('root');
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{Normal queries work after pg_putcopyend is called};
+eval {
+ $dbh->do('SELECT 123');
+};
+is($@, q{}, $t);
+
+$t=q{Data from pg_putcopydata was entered correctly};
+$result = $dbh->selectall_arrayref("SELECT id2,val2 FROM $table ORDER BY id2");
+$expected =
[['12','Mulberry'],['13','Strawberry'],[14,'Blueberry'],[17,'MoreBlueberries']];
+is_deeply($result, $expected, $t);
+
+$dbh->do("COPY $table TO STDOUT");
+$t=q{pg_getcopydata fails when argument is not a variable};
+eval {
+ $dbh->pg_getcopydata('wrongo');
+};
+like($@, qr{read-only}, $t);
+
+$t=q{pg_getcopydata works and returns the length of the string};
+$data[0] = 'old';
+eval {
+ $dbh->pg_getcopydata($data[0]);
+};
+is($@, q{}, $t);
+is($data[0], "13\tStrawberry\n", $t);
+
+$t=q{pg_getcopydata works when argument is a reference};
+eval {
+ $dbh->pg_getcopydata(\$data[0]);
+};
+is($@, q{}, $t);
+is($data[0], "14\tBlueberry\n", $t);
+
+$t=q{Calling do() gives an error when in the middle of COPY .. TO};
+eval {
+ $dbh->do('SELECT 234');
+};
+like($@, qr{pg_getcopydata}, $t);
+
+$t=q{Calling pg_putcopydata gives an errors when in the middle of COPY .. TO};
+eval {
+ $dbh->pg_putcopydata('pie');
+};
+like($@, qr{COPY command}, $t);
+
+$t=q{pg_getcopydata returns 0 when no more data};
+$dbh->pg_getcopydata(\$data[0]);
+eval {
+ $result = $dbh->pg_getcopydata(\$data[0]);
+};
+is($@, q{}, $t);
+is($data[0], '', $t);
+is($result, -1, $t);
+
+$t=q{Normal queries work after pg_getcopydata runs out};
+eval {
+ $dbh->do('SELECT 234');
+};
+is($@, q{}, $t);
+
#
# Make sure rollback and commit reset our internal copystate tracking
#
