Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 106858
Status: resolved
Priority: 0/
Queue: DBD-Pg

People
Owner: greg [...] turnstep.com
Requestors: Warstone [...] list.ru
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 3.6.0



Subject: New feature: pg_canonical_X
Date: Thu, 03 Sep 2015 22:40:23 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: Warstone [...] list.ru <warstone [...] list.ru>
I'd like to introduce 2 new methods: $sth->pg_canonical_names $sth->pg_canonical_ids This methods gives you information about "where the data came from". It uses  PQftable /  PQftablecol from  http://www.postgresql.org/docs/9.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO and very handly for ORMs (You can construct ORM with your data types from any raw SQL query, according to your meta). In example: You have BYTEA "data" column in "tmp_table" table. Somewhere in your apps metadata you know that this is gzipped JSON. And when you use plain SQL like... SELECT u.id, t.data AS json FROM "user" u LEFT JOIN tmp_table t ON t.user = u.id WHERE u.id = 1234567; this method (pg_canonical_names) will tell you that you have 2 columns (schema, table, column): public.user.id public.tmp_table.data and your ORM can construct JSON from unpacked data here, This methods supported by libpq and will be very usefull. pg_canonical_ids can be used if you know Oids for tables and column positions (PostgreSQL internals) and it's fast, pg_canonical_names can be used for retreeving human readable names, buy it need to go to server for every column you have in query (so use it only for debug)
Subject: [rt.cpan.org #106858] forgot to add patch
Date: Thu, 03 Sep 2015 22:43:46 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: Warstone [...] list.ru <warstone [...] list.ru>
Forgot to add a patch Here it is.

Message body is not shown because sender requested not to inline it.

Subject: [rt.cpan.org #106858] Dummy ORM that uses this feature
Date: Thu, 10 Sep 2015 19:40:38 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: Warstone [...] list.ru <warstone [...] list.ru>
https://github.com/Warstone/SORM This is early prototype, but it can illustrate what I mean... ORM from any SQL Query and metadata. Show quoted text
>Четверг, 3 сентября 2015, 15:40 -04:00 от "Bugs in DBD-Pg via RT" <bug-DBD-Pg@rt.cpan.org>: > > >Greetings, > >This message has been automatically generated in response to the >creation of a trouble ticket regarding: >"New feature: pg_canonical_X", >a summary of which appears below. > >There is no need to reply to this message right now. Your ticket has been >assigned an ID of [rt.cpan.org #106858]. Your ticket is accessible >on the web at: > >     https://rt.cpan.org/Ticket/Display.html?id=106858 > >Please include the string: > >         [rt.cpan.org #106858] > >in the subject line of all future correspondence about this issue. To do so, >you may reply to this message. > >                        Thank you, >                         bug-DBD-Pg@rt.cpan.org > >------------------------------------------------------------------------- > I'd like to introduce 2 new methods: >$sth->pg_canonical_names >$sth->pg_canonical_ids > >This methods gives you information about "where the data came from". It uses  PQftable /  PQftablecol from  http://www.postgresql.org/docs/9.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO >and very handly for ORMs (You can construct ORM with your data types from any raw SQL query, according to your meta). > >In example: >You have BYTEA "data" column in "tmp_table" table. Somewhere in your apps metadata you know that this is gzipped JSON. And when you use plain SQL like... > >SELECT u.id, t.data AS json FROM "user" u >LEFT JOIN tmp_table t ON t.user = u.id WHERE u.id = 1234567; > >this method (pg_canonical_names) will tell you that you have 2 columns (schema, table, column): >public.user.id >public.tmp_table.data > >and your ORM can construct JSON from unpacked data here, > >This methods supported by libpq and will be very usefull. > >pg_canonical_ids can be used if you know Oids for tables and column positions (PostgreSQL internals) and it's fast, >pg_canonical_names can be used for retreeving human readable names, buy it need to go to server for every column you have in query (so use it only for debug)
Applied with minor changes in: 6418b7e5807d64d81d4e25df65804104ca4193c6
Live as of version 3.6.0