[jifty-devel] Fix to Jifty/DBI/Handle/Oracle.pm,
but there's still a problem with Oracle
Mark Gardner
mark.gardner at bms.com
Thu Jun 1 17:26:45 EDT 2006
I'm trying to work through Jifty::Manual::Tutorial, but with Oracle as
the database. This revealed a bug in Jifty/DBI/Handle/Oracle.pm -- the
hash keys used to build the DSN need to be in all lowercase. I've
attached a fixed version.
I'm still getting the following error when doing a "jifty schema
--setup", though, and I think it's because Jifty wants to create a table
that begins with an underscore ( _ ). Oracle table names have to start
with an alphanumeric character.
Hope this helps -- looking forward to better Oracle support.
C:\Documents and Settings\gardnema\My Documents\Projects\jifty\MyWeblog>jifty schema --setup
INFO - Generating SQL for application MyWeblog...
INFO - Using MyWeblog::Model::Post
INFO - Using Jifty::Model::Session
INFO - Using Jifty::Model::Metadata
WARN - DBD::Oracle::st execute failed: ORA-00911: invalid character (DBD ERROR:error possibly near <*> indicator at char 13 in 'CREATE TABLE <*>_jifty_metadata
(
id INTEGER 0 ,
data_key text NULL ,
value text NULL ,
PRIMARY KEY (id)
)
') [for Statement "CREATE TABLE _jifty_metadata (
id INTEGER 0 ,
data_key text NULL ,
value text NULL ,
PRIMARY KEY (id)
)
"] at C:/Perl/site/lib/Jifty/DBI/Handle.pm line 458.
WARN - Jifty::Handle=HASH(0x4905b18) couldn't execute the query 'CREATE TABLE _jifty_metadata (
id INTEGER 0 ,
data_key text NULL ,
value text NULL ,
PRIMARY KEY (id)
)
' at C:/Perl/site/lib/Jifty/DBI/Handle.pm line 472
Jifty::DBI::Handle::simple_query('Jifty::Script') called at C:/Perl/site/lib/Jifty/Script/Schema.pm line 221
Jifty::Script::Schema::create_all_tables('Jifty::Script') called at C:/Perl/site/lib/Jifty/Script/Schema.pm line 48
Jifty::Script::Schema::run('Jifty::Script') called at C:/Perl/site/lib/App/CLI/Command.pm line 52
App::CLI::Command::run_command('Jifty::Script') called at C:/Perl/site/lib/App/CLI.pm line 80
App::CLI::dispatch('Jifty::Script') called at C:\Perl\bin\jifty line 14
error creating a table: Couldn't execute the query 'CREATE TABLE _jifty_metadata
(
id INTEGER 0 ,
data_key text NULL ,
value text NULL ,
PRIMARY KEY (id)
)
'ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 13 in 'CREATE TABLE <*>_jifty_metadata (
id INTEGER 0 ,
data_key text NULL ,
value text NULL ,
PRIMARY KEY (id)
)
') at C:/Perl/site/lib/Jifty/Script/Schema.pm line 222.
-------------- next part --------------
package Jifty::DBI::Handle::Oracle;
use base qw/Jifty::DBI::Handle/;
use DBD::Oracle qw(:ora_types);
use vars qw($VERSION $DBIHandle $DEBUG);
=head1 NAME
Jifty::DBI::Handle::Oracle - An oracle specific Handle object
=head1 SYNOPSIS
=head1 DESCRIPTION
This module provides a subclass of L<Jifty::DBI::Handle> that
compensates for some of the idiosyncrasies of Oracle.
=head1 METHODS
=head2 connect PARAMHASH: Driver, Database, Host, User, Password
Takes a paramhash and connects to your DBI datasource.
=cut
sub connect {
my $self = shift;
my %args = (
driver => undef,
database => undef,
user => undef,
password => undef,
sid => undef,
host => undef,
@_
);
$self->SUPER::connect(%args);
$self->dbh->{LongTruncOk} = 1;
$self->dbh->{LongReadLen} = 8000;
$self->simple_query(
"ALTER SESSION set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
return ($DBIHandle);
}
=head2 insert
Takes a table name as the first argument and assumes that the rest of
the arguments are an array of key-value pairs to be inserted.
=cut
sub insert {
my $self = shift;
my $table = shift;
my ($sth);
# Oracle Hack to replace non-supported mysql_rowid call
my %attribs = @_;
my ( $unique_id, $query_string );
if ( $attribs{'Id'} || $attribs{'id'} ) {
$unique_id = ( $attribs{'Id'} ? $attribs{'Id'} : $attribs{'id'} );
} else {
$query_string = "SELECT " . $table . "_seq.nextval FROM DUAL";
$sth = $self->simple_query($query_string);
if ( !$sth ) {
if ($main::debug) {
die "Error with $query_string";
} else {
return (undef);
}
}
#needs error checking
my @row = $sth->fetchrow_array;
$unique_id = $row[0];
}
#TODO: don't hardcode this to id pull it from somewhere else
#call super::insert with the new column id.
$attribs{'id'} = $unique_id;
delete $attribs{'Id'};
$sth = $self->SUPER::insert( $table, %attribs );
unless ($sth) {
if ($main::debug) {
die "Error with $query_string: " . $self->dbh->errstr;
} else {
return (undef);
}
}
$self->{'id'} = $unique_id;
return ( $self->{'id'} ); #Add Succeded. return the id
}
=head2 build_dsn PARAMHASH
Takes a bunch of parameters:
Required: Driver, Database or Host/SID,
Optional: Port and RequireSSL
Builds a DSN suitable for an Oracle DBI connection
=cut
sub build_dsn {
my $self = shift;
my %args = (
driver => undef,
database => undef,
host => undef,
port => undef,
sid => undef,
requiressl => undef,
@_
);
my $dsn = "dbi:$args{'driver'}:";
if ( defined $args{'host'}
&& $args{'host'}
&& defined $args{'sid'}
&& $args{'sid'} )
{
$dsn .= "host=$args{'host'};sid=$args{'sid'}";
} else {
$dsn .= "$args{'database'}"
if ( defined $args{'database'} && $args{'database'} );
}
$dsn .= ";port=$args{'port'}"
if ( defined $args{'port'} && $args{'port'} );
$dsn .= ";requiressl=1"
if ( defined $args{'requiressl'} && $args{'requiressl'} );
$self->{'dsn'} = $dsn;
}
=head2 blob_params column_NAME column_type
Returns a hash ref for the bind_param call to identify BLOB types used
by the current database for a particular column type. The current
Oracle implementation only supports ORA_CLOB types (112).
=cut
sub blob_params {
my $self = shift;
my $column = shift;
# Don't assign to key 'value' as it is defined later.
return (
{ ora_column => $column,
ora_type => ORA_CLOB,
}
);
}
=head2 apply_limits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE
starting with FIRST_ROW;
=cut
sub apply_limits {
my $self = shift;
my $statementref = shift;
my $per_page = shift;
my $first = shift;
# Transform an SQL query from:
#
# SELECT main.*
# FROM Tickets main
# WHERE ((main.EffectiveId = main.id))
# AND ((main.Type = 'ticket'))
# AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
# AND ( (main.Queue = '1') ) )
#
# to:
#
# SELECT * FROM (
# SELECT limitquery.*,rownum limitrownum FROM (
# SELECT main.*
# FROM Tickets main
# WHERE ((main.EffectiveId = main.id))
# AND ((main.Type = 'ticket'))
# AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
# AND ( (main.Queue = '1') ) )
# ) limitquery WHERE rownum <= 50
# ) WHERE limitrownum >= 1
#
if ($per_page) {
# Oracle orders from 1 not zero
$first++;
# Make current query a sub select
$$statementref
= "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( $$statementref ) limitquery WHERE rownum <= "
. ( $first + $per_page - 1 )
. " ) WHERE limitrownum >= "
. $first;
}
}
=head2 distinct_query STATEMENTREF
takes an incomplete SQL SELECT statement and massages it to return a
DISTINCT result set.
=cut
sub distinct_query {
my $self = shift;
my $statementref = shift;
my $sb = shift;
my $table = $sb->Table;
# Wrapp select query in a subselect as Oracle doesn't allow
# DISTINCT against CLOB/BLOB column types.
if ( $sb->_order_clause =~ /(?<!main)\./ ) {
# If we are ordering by something not in 'main', we need to GROUP
# BY and adjust the ORDER_BY accordingly
local $sb->{group_by}
= [ @{ $sb->{group_by} || [] }, { column => 'id' } ];
local $sb->{order_by} = [
map {
( $_->{alias} and $_->{alias} ne "main" )
? { %{$_}, column => "min(" . $_->{column} . ")" }
: $_
} @{ $sb->{order_by} }
];
my $group = $sb->_group_clause;
my $order = $sb->_order_clause;
$$statementref
= "SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)";
} else {
$$statementref
= "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";
$$statementref .= $sb->_group_clause;
$$statementref .= $sb->_order_clause;
}
}
1;
__END__
=head1 AUTHOR
Jesse Vincent, jesse at fsck.com
=head1 SEE ALSO
L<Jifty::DBI>, L<Jifty::DBI::Handle>, L<DBD::Oracle>
=cut
More information about the jifty-devel
mailing list