Re: possible Class::DBI::Oracle [patch]

[prev] [thread] [next] [Date index for 2005/04/21]

This is a multi-part message in MIME format.
--------------070802030201090602040007
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Jay Strauss wrote:
> Todd Hepler wrote:
> 
>> Hi all,
>>
>> The attached patch would allow you to specify an Oracle schema along 
>> with your table.  So this module currently lets you do:
>>
> 
> The only thing I see that may introduce some bugs is the uppering and 
> lowering of table and column names.  You probably know, but I'll point 
> it out, Oracle is case sensitive about that stuff.  I've never tested 
> CDBI against tables and columns of mixed case, so I don't know if it 
> currently works anyway.
> 

Thanks to you both for the feedback.

I like the idea of not mucking with the case of things where possible. 
Attached is another try that only mucks where I'm thinking it's 
desired/necessary.

The current version (0.51) uppercases $table before hitting the data 
dictionary.  I guess this is a convenience, so I kept that in.  And if we're 
doing it for $table, seems like we should do it for $schema too.

If the schema isn't specified, then it seems we would have to uc 
$dbh->{Username} anyway since the following appear to work the same:

DBI->connect('dbi:Oracle:server', 'MYUSER' ...)
DBI->connect('dbi:Oracle:server', 'myuser' ...)

-thepler

--------------070802030201090602040007
Content-Type: text/plain;
 name="Oracle.pm.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="Oracle.pm.patch"

--- Oracle.pm.orig	2003-07-15 19:56:23.000000000 -0500
+++ Oracle.pm	2005-04-21 16:09:07.000000000 -0500
@@ -16,6 +16,15 @@
   
   # ... see the Class::DBI documentation for details on Class::DBI usage
 
+
+  # the Oracle schema can optionally be specified:
+
+  __PACKAGE__->set_up_table('Schema.Table');
+
+  # -or-
+
+  __PACKAGE__->set_up_table('Table', 'Schema');
+
 =head1 DESCRIPTION
 
 This is an extension to Class::DBI that currently implements:
@@ -88,60 +97,78 @@
 sub _die { require Carp; Carp::croak(@_); } 
 
 sub set_up_table {
-	my($class, $table) = @_;
+	my($class, $table, $schema) = @_;
 	my $dbh = $class->db_Main();
 
-	$class->table($table);
+        # SCHEMA.TABLE
+        #
+        # There are 3 important variables, here is what they are for:
+        #     $schema        - Just the SCHEMA
+        #     $table         - Just the TABLE
+        #     $cdbi_table    - This is what will get passed
+        #                      to $class->table()
+        #
+        # We assume that if a schema was passed in (either
+        # as a prefix in $table or as a second argument, $schema)
+        # it should be used by all Class::DBI generated SQL.  So
+        # $cdbi_table will include the schema prefix in these cases.
+        # Otherwise, we assume the schema prefix will not be
+        # needed by Class::DBI generated SQL.  However, $schema
+        # will still be needed for the call to primary_key_info()
+        # (because apparently '%' or '' will not work).
+
+        my $cdbi_table;
+
+        if ($table =~ /\./ and not $schema) {
+            $cdbi_table = $table;
+            ($schema, $table) = split(/\./, $cdbi_table);
+        }
+        elsif ($schema) {
+            $cdbi_table = join('.', $schema, $table);
+        }
+        else {
+            $schema ||= $dbh->{Username};
+            $cdbi_table = $table;
+        }
+
+	$class->table($cdbi_table);
 
+        # upper-case here for backward compatibility with 0.51?
+        # convenience?
 	$table = uc $table;
+        $schema = uc $schema;
 
 	# alias the table if needed.
 	(my $alias = $class) =~ s/.*:://g;
 	$class->table_alias(qq["$alias"]) if grep /$alias/i, @problemWords;
 
-	# find the primary key and column names.
-	my $sql = qq[
-		select 	lower(a.column_name), b.position
-		from 	user_tab_columns a,
-				(
-				select 	column_name, position
-				from   	user_constraints a, user_cons_columns b
-				where 	a.constraint_name = b.constraint_name
-				and	a.constraint_type = 'P'
-				and	a.table_name = ?
-				) b
-		where 	a.column_name = b.column_name (+)
-		and	a.table_name = ?
-		order by position, a.column_name];
+        my $row;
+        my $sth;
+        my @all;
+        my @primary;
+
+        $sth = $dbh->column_info('', $schema, $table, '%');
+        while ($row = $sth->fetch) {
+            push @all, $row->[3];
 
-	my $sth = $dbh->prepare($sql);
-	$sth->execute($table,$table);
+        }
 	
-	my $col = $sth->fetchall_arrayref;
+        $sth = $dbh->primary_key_info('', $schema, $table);
+        while ($row = $sth->fetch) {
+            push @primary, $row->[3];
+        }
 	
-	$sth->finish();
+	my $msg = qq{has no primary key} unless @primary;
 
 	# deal with old revisions
-	my $msg;
-	my @primary = ();
-
-	$msg = qq{has no primary key} unless $col->[0][1];
-
 	# Class::DBI >= 0.93 can use multiple-primary-column keys.
-	if ($Class::DBI::VERSION >= 0.93) {
-
-		map { push @primary, $_->[0] if $_->[1] } @$col;
-
-	} else {
-
-		$msg = qq{has a composite primary key} if $col->[1][1];
-
-		push @primary, $col->[0][0];
+	if ($Class::DBI::VERSION < 0.93) {
+		$msg = qq{has a composite primary key} if @primary > 1;
 	}
 
 	_die('The "',$class->table,qq{" table $msg}) if $msg;
 
-	$class->columns(All => map {$_->[0]} @$col);
+	$class->columns(All => @all);
 	$class->columns(Primary => @primary);
 
 	# attempt to guess the sequence from the table name.
@@ -154,7 +181,7 @@
 
 	# Go and get all the sequences where the table name is within the
 	# name of the sequence
-	$sql = qq[
+	my $sql = qq[
 		select	sequence_name
 		from	user_sequences
 		where	sequence_name like (?)

--------------070802030201090602040007--