Committed by Warstone <[email protected]>
Add statement handle methods pg_canonical_ids and
pg_canonical_names. Patch per "Warstone" from RT 106858:
https://rt.cpan.org/Ticket/Display.html?id=106858 Mild adjustments by myself.
---
Pg.h | 1 +
Pg.pm | 25 ++++++++++++++
Pg.xs | 17 ++++++++++
dbdimp.c | 107 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
dbdimp.h | 7 ++++
t/03smethod.t | 34 ++++++++++++++++++-
testme.tmp.pl | 4 +++
7 files changed, 194 insertions(+), 1 deletion(-)
diff --git a/Pg.h b/Pg.h
index 1df5c88..8577529 100644
--- a/Pg.h
+++ b/Pg.h
@@ -141,6 +141,7 @@ DBISTATE_DECLARE;
#define TRACE_PQGETCOPYDATA TRACE_XX "%sPQgetCopyData\n",
THEADER_slow)
#define TRACE_PQGETISNULL TRACE_XX "%sPQgetisnull\n",
THEADER_slow)
#define TRACE_PQGETRESULT TRACE_XX "%sPQgetResult\n",
THEADER_slow)
+#define TRACE_PQGETLENGTH TRACE_XX "%sPQgetLength\n",
THEADER_slow)
#define TRACE_PQGETVALUE TRACE_XX "%sPQgetvalue\n",
THEADER_slow)
#define TRACE_PQHOST TRACE_XX "%sPQhost\n",
THEADER_slow)
#define TRACE_PQISBUSY TRACE_XX "%sPQisBusy\n",
THEADER_slow)
diff --git a/Pg.pm b/Pg.pm
index b01618d..e594985 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -147,6 +147,8 @@ use 5.008001;
DBD::Pg::st->install_method('pg_cancel');
DBD::Pg::st->install_method('pg_result');
DBD::Pg::st->install_method('pg_ready');
+ DBD::Pg::st->install_method('pg_canonical_ids');
+ DBD::Pg::st->install_method('pg_canonical_names');
DBD::Pg::db->install_method('pg_lo_creat');
DBD::Pg::db->install_method('pg_lo_open');
@@ -3626,6 +3628,29 @@ For further information and examples about blobs, please
read the chapter
about Large Objects in the PostgreSQL Programmer's Guide at
L<http://www.postgresql.org/docs/current/static/largeobjects.html>.
+=head3 B<pg_canonical_ids>
+
+ $data = $sth->pg_canonical_ids;
+
+DBD::Pg specific method. It returns Oid of table and position in table for
+every column in resultset.
+
+Returns array of arrays with F<Table Oid> and F<Column Position> for every
+column in resultset or undef if current column is not a simple reference.
+
+=head3 B<pg_canonical_names>
+
+ $data = $sth->pg_canonical_names;
+
+DBD::Pg specific method. It returns array of original (or canonical) names
+(from where this data is actually came from) of columns in
+F<Schema>.F<Table>.F<Column> format or undef if current column is not a
+simple reference.
+
+Note that this method is quite slow because it need additional information from
+server for every column that is simple reference. Consider to use
L</pg_canonical_ids>
+instead.
+
=head2 Statement Handle Attributes
=head3 B<NUM_OF_FIELDS> (integer, read-only)
diff --git a/Pg.xs b/Pg.xs
index e2c8fb3..fa8c667 100644
--- a/Pg.xs
+++ b/Pg.xs
@@ -866,5 +866,22 @@ pg_result(sth)
#endif
+SV*
+pg_canonical_ids(sth)
+ SV *sth
+ CODE:
+ D_imp_sth(sth);
+ RETVAL = dbd_st_canonical_ids(sth, imp_sth);
+ OUTPUT:
+ RETVAL
+
+SV*
+pg_canonical_names(sth)
+ SV *sth
+ CODE:
+ D_imp_sth(sth);
+ RETVAL = dbd_st_canonical_names(sth, imp_sth);
+ OUTPUT:
+ RETVAL
# end of Pg.xs
diff --git a/dbdimp.c b/dbdimp.c
index c12701a..939d83c 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -5345,6 +5345,113 @@ int dbd_st_cancel(SV *sth, imp_sth_t *imp_sth)
} /* end of dbd_st_cancel */
+
+/* ================================================================== */
+/*
+ Retrieves table oid and column position (in that table) for every column in
resultset
+ Returns array of arrays of table oid and column pos or undef if column is
not a simple reference
+*/
+SV* dbd_st_canonical_ids(SV *sth, imp_sth_t *imp_sth)
+{
+ dTHX;
+ TRACE_PQNFIELDS;
+ int fields = PQnfields(imp_sth->result);
+ AV* result = newAV();
+ av_extend(result, fields);
+ while(fields--){
+ int stored = 0;
+ TRACE_PQFTABLE;
+ int oid = PQftable(imp_sth->result, fields);
+ if(oid != InvalidOid){
+ TRACE_PQFTABLECOL;
+ int pos = PQftablecol(imp_sth->result, fields);
+ if(pos > 0){
+ AV * row = newAV();
+ av_extend(row, 2);
+ av_store(row, 0, newSViv(oid));
+ av_store(row, 1, newSViv(pos));
+ av_store(result, fields, newRV_noinc((SV
*)row));
+ stored = 1;
+ }
+ }
+ if(!stored){
+ av_store(result, fields, newSV(0));
+ }
+ }
+ SV* sv = newRV_noinc((SV*) result);
+ return sv;
+
+} /* end of dbd_st_canonical_ids */
+
+
+/* ================================================================== */
+/*
+ Retrieves canonical name (schema.table.column) for every column in resultset
+ Returns array of strings or undef if column is not a simple reference
+*/
+SV* dbd_st_canonical_names(SV *sth, imp_sth_t *imp_sth)
+{
+ dTHX;
+ D_imp_dbh_from_sth;
+ ExecStatusType status = PGRES_FATAL_ERROR;
+ PGresult * result;
+ TRACE_PQNFIELDS;
+ int fields = PQnfields(imp_sth->result);
+ AV* result_av = newAV();
+ av_extend(result_av, fields);
+ while(fields--){
+ TRACE_PQFTABLE;
+ int oid = PQftable(imp_sth->result, fields);
+ int stored = 0;
+
+ if(oid != InvalidOid) {
+ TRACE_PQFTABLECOL;
+ int pos = PQftablecol(imp_sth->result, fields);
+ if(pos > 0){
+ char statement[200];
+ snprintf(statement, sizeof(statement),
+ "SELECT n.nspname, c.relname, a.attname
FROM pg_class c LEFT JOIN pg_namespace n ON c.relnamespace = n.oid LEFT JOIN
pg_attribute a ON a.attrelid = c.oid WHERE c.oid = %d AND a.attnum = %d", oid,
pos);
+ TRACE_PQEXEC;
+ result = PQexec(imp_dbh->conn, statement);
+ TRACE_PQRESULTSTATUS;
+ status = PQresultStatus(result);
+ if (PGRES_TUPLES_OK == status) {
+ TRACE_PQNTUPLES;
+ if (PQntuples(result)!=0) {
+ TRACE_PQGETLENGTH;
+ int len = PQgetlength(result,
0, 0) + 1;
+ TRACE_PQGETLENGTH;
+ len += PQgetlength(result, 0,
1) + 1;
+ TRACE_PQGETLENGTH;
+ len += PQgetlength(result, 0,
2);
+ SV* table_name = newSV(len);
+ TRACE_PQGETVALUE;
+ char *nsp = PQgetvalue(result,
0, 0);
+ TRACE_PQGETVALUE;
+ char *tbl = PQgetvalue(result,
0, 1);
+ TRACE_PQGETVALUE;
+ char *col = PQgetvalue(result,
0, 2);
+ sv_setpvf(table_name,
"%s.%s.%s", nsp, tbl, col);
+ if (imp_dbh->pg_utf8_flag)
+ SvUTF8_on(table_name);
+ av_store(result_av, fields,
table_name);
+ stored = 1;
+ }
+ }
+ TRACE_PQCLEAR;
+ PQclear(result);
+ }
+ }
+ if(!stored){
+ av_store(result_av, fields, newSV(0));
+ }
+ }
+ SV* sv = newRV_noinc((SV*) result_av);
+ return sv;
+
+} /* end of dbd_st_canonical_names */
+
+
/*
Some information to keep you sane:
typedef enum
diff --git a/dbdimp.h b/dbdimp.h
index 09d8f7c..9596fb8 100644
--- a/dbdimp.h
+++ b/dbdimp.h
@@ -195,6 +195,13 @@ void dbd_st_destroy (SV * sth, imp_sth_t * imp_sth);
#define dbd_st_blob_read pg_st_blob_read
int dbd_st_blob_read (SV * sth, imp_sth_t * imp_sth, int lobjId, long offset,
long len, SV * destrv, long destoffset);
+#define dbd_st_canonical_ids pg_st_canonical_ids
+SV* dbd_st_canonical_ids(SV *sth, imp_sth_t *imp_sth);
+
+#define dbd_st_canonical_names pg_st_canonical_names
+SV* dbd_st_canonical_names(SV *sth, imp_sth_t *imp_sth);
+
+
/*
Everything else should map back to the DBI version, or be handled by Pg.pm
TODO: Explicitly map out each one.
diff --git a/t/03smethod.t b/t/03smethod.t
index 859a295..2bc6e90 100644
--- a/t/03smethod.t
+++ b/t/03smethod.t
@@ -21,7 +21,7 @@ my $dbh = connect_database();
if (! $dbh) {
plan skip_all => 'Connection to database failed, cannot continue
testing';
}
-plan tests => 122;
+plan tests => 126;
isnt ($dbh, undef, 'Connect to database for statement handle method testing');
@@ -739,6 +739,38 @@ SKIP: {
$dbh2->disconnect();
}
+#
+# Test of the statement handle methods "pg_canonical_names"
+#
+
+$t=q{Statement handle method "pg_canonical_names" returns expected values};
+$sth = $dbh->prepare("SELECT id, id AS not_id, id + 1 AS not_a_simple FROM
dbd_pg_test LIMIT 1");
+$sth->execute;
+
+is_deeply($sth->pg_canonical_names, [
+ 'dbd_pg_testschema.dbd_pg_test.id',
+ 'dbd_pg_testschema.dbd_pg_test.id',
+ undef
+], $t);
+
+#
+# Test of the statement handle methods "pg_canonical_ids"
+#
+
+$t=q{Statement handle method "pg_canonical_ids" returns correct length};
+my $data = $sth->pg_canonical_ids;
+is ($#$data, 2, $t);
+
+$t=q{Statement handle method pg_canonical_ids has undef as the last element in
returned array};
+is ($data->[2], undef, $t);
+
+$t=q{Statement handle method "pg_canonical_ids" returns identical first and
second elements};
+$t=q{first and second array elements must be the same};
+is_deeply($data->[0], $data->[1], $t);
+
+$sth->finish;
+
+
cleanup_database($dbh,'test');
$dbh->rollback();
$dbh->disconnect();
diff --git a/testme.tmp.pl b/testme.tmp.pl
index 0231f83..94ae5dd 100755
--- a/testme.tmp.pl
+++ b/testme.tmp.pl
@@ -28,6 +28,10 @@ my $dbh = DBI->connect($DSN, '', '',
{AutoCommit=>0,RaiseError=>1,PrintError=>0}
my $me = $dbh->{Driver}{Name};
print "DBI is version $DBI::VERSION, I am $me, version of DBD::Pg is
$DBD::Pg::VERSION\n";
+print "Name: $dbh->{Name}\n";
+
+exit;
+
#user_arrays();
#commit_return_test();
--
1.8.4
