Committed by Tim Bunce <[email protected]>
Subject: [DBD::Pg 1/2] Make get_info much more efficient and slightly simpler.
Previously on every call get_info copied a large hash into another hash,
then added more keys to the new hash, then discarded the new hash.
I've moved the hash out from the method and made it static.
There's no longer any hash copying.
Also there's a long elsif chain to handle special cases (which has a
poor-failure mode). I've added two new general mechanisms to remove the
need for many of the special cases: A code ref in the hash is called
with the dbh and the return value returned. A scalar ref is treated as
a ref to an SQL statement which is executed and the first value returned.
The same mechanisms should be extended to the other special cases but I
stopped here to keep the patch reasonably clear.
---
Pg.pm | 56 ++++++++++++++++++++++++--------------------------------
t/03dbmethod.t | 11 ++++++++++-
2 files changed, 34 insertions(+), 33 deletions(-)
diff --git a/Pg.pm b/Pg.pm
index 948db96..5687d57 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -1322,13 +1322,7 @@ use 5.008001;
DBI::SQL_INTEGER, DBI::SQL_SMALLINT, DBI::SQL_BIGINT, DBI::SQL_DECIMAL,
DBI::SQL_FLOAT, DBI::SQL_REAL, DBI::SQL_DOUBLE, DBI::SQL_NUMERIC;
- sub get_info {
-
- my ($dbh,$type) = @_;
-
- return undef unless defined $type and length $type;
-
- my %type = (
+ my %get_info_type = (
## Driver information:
@@ -1336,7 +1330,7 @@ use 5.008001;
10021 => ['SQL_ASYNC_MODE', 2
], ## SQL_AM_STATEMENT
120 => ['SQL_BATCH_ROW_COUNT', 2
], ## SQL_BRC_EXPLICIT
121 => ['SQL_BATCH_SUPPORT', 3
], ## 12 SELECT_PROC + ROW_COUNT_PROC
- 2 => ['SQL_DATA_SOURCE_NAME', "dbi:Pg:$dbh->{Name}"
],
+ 2 => ['SQL_DATA_SOURCE_NAME', sub { sprintf "dbi:Pg:%",
shift->{Name} } ],
3 => ['SQL_DRIVER_HDBC', 0
], ## not applicable
135 => ['SQL_DRIVER_HDESC', 0
], ## not applicable
4 => ['SQL_DRIVER_HENV', 0
], ## not applicable
@@ -1356,21 +1350,21 @@ use 5.008001;
150 => ['SQL_KEYSET_CURSOR_ATTRIBUTES1', 0
], ## applies to us?
151 => ['SQL_KEYSET_CURSOR_ATTRIBUTES2', 0
], ## see above
10022 => ['SQL_MAX_ASYNC_CONCURRENT_STATEMENTS', 0
], ## unlimited, probably
- 0 => ['SQL_MAX_DRIVER_CONNECTIONS', 'MAXCONNECTIONS'
], ## magic word
+ 0 => ['SQL_MAX_DRIVER_CONNECTIONS', \'SHOW max_connections'
],
152 => ['SQL_ODBC_INTERFACE_CONFORMANCE', 1
], ## SQL_OIC_LEVEL_1
10 => ['SQL_ODBC_VER', '03.00.0000'
],
153 => ['SQL_PARAM_ARRAY_ROW_COUNTS', 2
], ## correct?
154 => ['SQL_PARAM_ARRAY_SELECTS', 3
], ## PAS_NO_SELECT
11 => ['SQL_ROW_UPDATES', 'N'
],
14 => ['SQL_SEARCH_PATTERN_ESCAPE', '\\'
],
- 13 => ['SQL_SERVER_NAME', 'CURRENTDB'
], ## magic word
+ 13 => ['SQL_SERVER_NAME', \'SELECT
pg_catalog.current_database()' ],
166 => ['SQL_STANDARD_CLI_CONFORMANCE', 2
], ## ??
167 => ['SQL_STATIC_CURSOR_ATTRIBUTES1', 519
], ## ??
168 => ['SQL_STATIC_CURSOR_ATTRIBUTES2', 5209
], ## ??
## DBMS Information
- 16 => ['SQL_DATABASE_NAME', 'CURRENTDB'
], ## magic word
+ 16 => ['SQL_DATABASE_NAME', \'SELECT
pg_catalog.current_database()' ],
17 => ['SQL_DBMS_NAME', 'PostgreSQL'
],
18 => ['SQL_DBMS_VERSION', 'ODBCVERSION'
], ## magic word
@@ -1380,7 +1374,7 @@ use 5.008001;
19 => ['SQL_ACCESSIBLE_TABLES', 'Y'
], ## is this really true?
82 => ['SQL_BOOKMARK_PERSISTENCE', 0
],
42 => ['SQL_CATALOG_TERM', ''
], ## empty = catalogs are not supported
- 10004 => ['SQL_COLLATION_SEQ', 'ENCODING'
], ## magic word
+ 10004 => ['SQL_COLLATION_SEQ', \'SHOW server_encoding'
],
22 => ['SQL_CONCAT_NULL_BEHAVIOR', 0
], ## SQL_CB_NULL
23 => ['SQL_CURSOR_COMMIT_BEHAVIOR', 1
], ## SQL_CB_CLOSE
24 => ['SQL_CURSOR_ROLLBACK_BEHAVIOR', 1
], ## SQL_CB_CLOSE
@@ -1398,7 +1392,7 @@ use 5.008001;
45 => ['SQL_TABLE_TERM', 'table'
],
46 => ['SQL_TXN_CAPABLE', 2
], ## SQL_TC_ALL
72 => ['SQL_TXN_ISOLATION_OPTION', 10
], ## 2+8
- 47 => ['SQL_USER_NAME', $dbh->{CURRENT_USER}
],
+ 47 => ['SQL_USER_NAME', sub {
shift->{CURRENT_USER} } ],
## Supported SQL
@@ -1509,21 +1503,28 @@ use 5.008001;
122 => ['SQL_CONVERT_WCHAR', 0
],
125 => ['SQL_CONVERT_WLONGVARCHAR', 0
],
126 => ['SQL_CONVERT_WVARCHAR', 0
],
+ ); ## end of %get_info_type
+ ## Add keys for names into the hash
+ for (keys %get_info_type) {
+ $get_info_type{$get_info_type{$_}->[0]} = $get_info_type{$_};
+ }
- ); ## end of %type
+ sub get_info {
- ## Put both numbers and names into a hash
- my %t;
- for (keys %type) {
- $t{$_} = $type{$_}->[1];
- $t{$type{$_}->[0]} = $type{$_}->[1];
- }
+ my ($dbh,$type) = @_;
- return undef unless exists $t{$type};
+ return undef unless defined $type;
+ return undef unless exists $get_info_type{$type};
- my $ans = $t{$type};
+ my $ans = $get_info_type{$type}->[1];
- if ($ans eq 'NAMEDATALEN') {
+ if (ref $ans eq 'CODE') {
+ $ans = $ans->($dbh);
+ }
+ elsif (ref $ans eq 'SCALAR') { # SQL
+ return $dbh->selectall_arrayref($$ans)->[0][0];
+ }
+ elsif ($ans eq 'NAMEDATALEN') {
return $dbh->selectall_arrayref('SHOW
max_identifier_length')->[0][0];
}
elsif ($ans eq 'ODBCVERSION') {
@@ -1536,21 +1537,12 @@ use 5.008001;
$simpleversion =~ s/_/./g;
return sprintf '%02d.%02d.%1d%1d%1d%1d', split (/\./,
"$simpleversion.0.0.0.0.0.0");
}
- elsif ($ans eq 'MAXCONNECTIONS') {
- return $dbh->selectall_arrayref('SHOW max_connections')->[0][0];
- }
- elsif ($ans eq 'ENCODING') {
- return $dbh->selectall_arrayref('SHOW server_encoding')->[0][0];
- }
elsif ($ans eq 'KEYWORDS') {
##
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
## Basically, we want ones that are 'reserved' for PostgreSQL but
not 'reserved' in SQL:2003
##
return join ',' => (qw(ANALYSE ANALYZE ASC DEFERRABLE DESC DO
FREEZE ILIKE INITIALLY ISNULL LIMIT NOTNULL OFF OFFSET PLACING RETURNING
VERBOSE));
}
- elsif ($ans eq 'CURRENTDB') {
- return $dbh->selectall_arrayref('SELECT
pg_catalog.current_database()')->[0][0];
- }
elsif ($ans eq 'READONLY') {
my $SQL = q{SELECT CASE WHEN setting = 'on' THEN 'Y' ELSE 'N' END
FROM pg_settings WHERE name = 'transaction_read_only'};
my $info = $dbh->selectall_arrayref($SQL);
diff --git a/t/03dbmethod.t b/t/03dbmethod.t
index 0e5d94e..3f29ca6 100644
--- a/t/03dbmethod.t
+++ b/t/03dbmethod.t
@@ -444,6 +444,10 @@ my %get_info = (
SQL_IDENTIFIER_QUOTE_CHAR => 29,
SQL_CATALOG_NAME_SEPARATOR => 41,
SQL_USER_NAME => 47,
+ # this also tests the dynamic attributes that run SQL
+ SQL_COLLATION_SEQ => 10004,
+ SQL_DATABASE_NAME => 16,
+ SQL_SERVER_NAME => 13,
);
for (keys %get_info) {
@@ -459,6 +463,11 @@ for (keys %get_info) {
is ($back, $forth, $t);
}
+# Make sure SQL_MAX_COLUMN_NAME_LEN looks normal
+$t='DB handle method "get_info" returns a valid looking
SQL_MAX_COLUMN_NAME_LEN string}';
+my $namedatalen = $dbh->get_info('SQL_MAX_COLUMN_NAME_LEN');
+cmp_ok ($namedatalen, '>=', 63, $t);
+
# Make sure odbcversion looks normal
$t='DB handle method "get_info" returns a valid looking ODBCVERSION string}';
my $odbcversion = $dbh->get_info(18);
@@ -466,7 +475,7 @@ like ($odbcversion, qr{^([1-9]\d|\d[1-9])\.\d\d\.\d\d00$},
$t);
# Testing max connections is good as this info is dynamic
$t='DB handle method "get_info" returns a number for
SQL_MAX_DRIVER_CONNECTIONS';
-my $maxcon = $dbh->get_info(0);
+my $maxcon = $dbh->get_info('SQL_MAX_DRIVER_CONNECTIONS');
like ($maxcon, qr{^\d+$}, $t);
$t='DB handle method "get_info" returns correct string for
SQL_DATA_SOURCE_READ_ONLY when "on"';
--
1.8.4
