[interchange-cvs] interchange - jon modified lib/Vend/Table/DBI.pm

[interchange-cvs at icdevgroup.org]

User:      jon
Date:      2005-09-14 02:01:14 GMT
Modified:  lib/Vend/Table DBI.pm
Log:
Solve the problem of fetching column names on databases that don't support
LIMIT, such as Oracle, by having a successful query that always returns no
rows.

This also solves an obscure problem in PostgreSQL (and presumably also
MySQL with InnoDB tables) where an un-vacuumed table with a large
number of dead rows at the beginning can take a long time to do the
previous query SELECT * FROM table LIMIT 1, because no index is used
and a lengthy sequential scan must first pass all the dead rows before
retrieving a single live row. (Using LIMIT 0 would also have worked,
but would've been less portable SQL.)

Tested on PostgreSQL 7.4, MySQL 3.23, and Oracle 8i.

Revision  Changes    Path
2.65      +4 -4      interchange/lib/Vend/Table/DBI.pm


rev 2.65, prev_rev 2.64
Index: DBI.pm
===================================================================
RCS file: /var/cvs/interchange/lib/Vend/Table/DBI.pm,v
retrieving revision 2.64
retrieving revision 2.65
diff -u -u -r2.64 -r2.65
--- DBI.pm	7 Sep 2005 08:43:54 -0000	2.64
+++ DBI.pm	14 Sep 2005 02:01:14 -0000	2.65
@@ -1,6 +1,6 @@
 # Vend::Table::DBI - Access a table stored in an DBI/DBD database
 #
-# $Id: DBI.pm,v 2.64 2005/09/07 08:43:54 racke Exp $
+# $Id: DBI.pm,v 2.65 2005/09/14 02:01:14 jon Exp $
 #
 # Copyright (C) 2002-2004 Interchange Development Group
 # Copyright (C) 1996-2002 Red Hat, Inc.
@@ -21,7 +21,7 @@
 # MA  02111-1307  USA.
 
 package Vend::Table::DBI;
-$VERSION = substr(q$Revision: 2.64 $, 10);
+$VERSION = substr(q$Revision: 2.65 $, 10);
 
 use strict;
 no warnings qw(uninitialized numeric);
@@ -1743,8 +1743,8 @@
 	my($db, $name, $config) = @_;
 	my @fld;
 
-	my $q = "select * from $name";
-	$q .= " limit 1" if $config->{HAS_LIMIT};
+	my $q = "SELECT * FROM $name WHERE 2 = 1";
+
 	my $sth = $db->prepare($q)
 		or die ::errmsg("%s prepare on %s: %s", 'list_fields', $name, $DBI::errstr);