| Subject: | Oracle Driver for Data-ObjectDriver |
Hi,
We are developing a job queue using TheSchwartz, which requires Data-
ObjectDriver.
But our backend database is Oracle, and I find there is no Oracle
driver for Data-ObjectDriver now.
Attached is my version of Oracle Driver :-)
--
Xiaoou
| Subject: | v1.diff |
--- /dev/null 2008-12-24 00:39:55.772008657 +0800
+++ lib/Data/ObjectDriver/Driver/DBD/Oracle.pm 2008-12-25 08:23:33.000000000 +0800
@@ -0,0 +1,110 @@
+# $Id$
+# Contributor(s): Xiaoou Wu <xiaoou.wu@oracle.com>
+#
+=head1 NAME
+
+Data::ObjectDriver::Driver::DBD::Oracle - Oracle Driver for Data::ObjectDriver
+
+=head1 DESCRIPTION
+
+This module overrides methods of the Data::ObjectDriver::Driver::DBD module
+with Oracle specific implementation.
+
+=cut
+
+package Data::ObjectDriver::Driver::DBD::Oracle;
+
+use strict;
+
+use base qw( Data::ObjectDriver::Driver::DBD );
+
+use Data::ObjectDriver::SQL::Oracle;
+use DBD::Oracle qw(:ora_types);
+
+sub init_dbh {
+ my $dbd = shift;
+ my ($dbh) = @_;
+ $dbh->{LongReadLen} = 1024000;
+ $dbh->{FetchHashKeyName} = 'NAME_lc';
+ return bless $dbh, 'Data::ObjectDriver::Driver::DBD::Oracle::db';
+}
+
+sub bind_param_attributes {
+ my ($dbd, $data_type) = @_;
+ if ($data_type && $data_type eq 'blob') {
+ return { ora_type => ORA_BLOB };
+ }
+ return;
+}
+
+## Oracle doesn't support auto-increment, it needs a SEQUENCE to emulate
+## this feature.
+sub fetch_id {
+ my $dbd = shift;
+ my ($class, $dbh, $sth, $driver) = @_;
+ my $seq = $dbd->sequence_name($class, $driver);
+ my ($last_insert_id) = $dbh->selectrow_array("SELECT $seq.CURRVAL "
+ . " FROM DUAL");
+ return $last_insert_id;
+}
+
+sub sequence_name {
+ my $dbd = shift;
+ my ($class, $driver) = @_;
+ my $datasource = $class ->datasource;
+ my $prefix = $driver->prefix;
+ $datasource = join('', $prefix, $datasource) if $prefix;
+ join '_', $datasource,
+ $dbd->db_column_name($class->datasource,
+ $class->properties->{primary_key}),
+ 'seq';
+}
+
+sub bulk_insert {
+ my $dbd = shift;
+ my $dbh = shift;
+ my $table = shift;
+ my $cols = shift;
+ my $rows_ref = shift;
+
+ my $sql = "INSERT INTO $table("
+ . join(',', @{$cols})
+ . ") VALUES ("
+ . join(',', map {'?'} @{$cols})
+ . ")";
+ my $sth = $dbh->prepare($sql);
+ foreach my $row (@{$rows_ref}) {
+ $sth->execute(@{$row});
+ }
+ return 1;
+}
+
+##
+sub sql_class { 'Data::ObjectDriver::SQL::Oracle' }
+
+package Data::ObjectDriver::Driver::DBD::Oracle::db;
+
+use strict;
+
+## Inherit the DB class from DBI::db.
+use base qw(DBI::db);
+
+## Oracle doesn't allow a SELECT statement without FROM.
+sub _adjust_stmt {
+ my $stmt = shift;
+ my $has_select = ($stmt =~ m/^\s*SELECT\b/io);
+ my $has_from = ($stmt =~ m/\bFROM\b/io);
+ $stmt .= " FROM DUAL" if ($has_select and !$has_from);
+ return $stmt;
+}
+
+sub selectrow_array {
+ my $self = shift;
+ my $stmt = shift;
+ $stmt = _adjust_stmt($stmt);
+ unshift @_, $stmt;
+ $self->SUPER::selectrow_array(@_);
+}
+
+1;
+
--- /dev/null 2008-12-24 00:39:55.772008657 +0800
+++ lib/Data/ObjectDriver/SQL/Oracle.pm 2008-12-25 08:17:21.000000000 +0800
@@ -0,0 +1,39 @@
+# $Id$
+# Contributor(s): Xiaoou Wu <xiaoou.wu@oracle.com>
+#
+
+package Data::ObjectDriver::SQL::Oracle;
+
+use strict;
+use base qw(Data::ObjectDriver::SQL);
+
+## Oracle doesn't have the LIMIT clause.
+sub as_limit {
+ return;
+}
+
+## Override as_sql to emulate the LIMIT clause.
+sub as_sql {
+ my $stmt = shift;
+ my $limit = $stmt->limit;
+ my $offset = $stmt->offset;
+
+ if (defined $limit && defined $offset){
+ my @fields = $stmt->select;
+ push(@fields, "ROW_NUMBER() OVER (ORDER BY 1) R");
+ }
+
+ my $sql = $stmt->SUPER::as_sql(@_);
+
+ if (defined $limit){
+ $sql = "SELECT * FROM ( $sql ) WHERE ";
+ if(defined $offset){
+ $sql = $sql . " R BETWEEN $offset + 1 AND $limit + $offset";
+ } else {
+ $sql = $sql . " rownum <= $limit";
+ }
+ }
+ return $sql;
+}
+
+1;