[Dbix-class] DBIx::Class::Schema->deploy and ORACLE woes

[Doug Scoular]

Hi All,
	I've been a happy user of Tangram for a while
	now and it generates sqlite, mysql and ORACLE
	DDL for me... however, I note that DBIx::Class
	has a much larger developer base so I thought I'd
	better think about making the switch.

	My ORACLE skills are woeful so I was hoping to use
	the "deploy" method found in DBIx::Class::Schema
	to generate the ORACLE schema for me. Especially as
	it's no longer marked "experimental" in the POD.

	First I updated my CPAN modules to:

DBIx::Class 0.08010
SQL::Translator 0.08001

	However, I'm finding that it's not as simple as changing
	the connect string from "dbi:SQLite" to "dbi:Oracle".

	It appears that I cannot just say:

package Runner::Model::HostToTestGroup;

use base qw/DBIx::Class/;
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('host_to_testgroup');
__PACKAGE__->add_columns(qw/HostID TestGroupID/);
__PACKAGE__->set_primary_key(qw/HostID TestGroupID/);
__PACKAGE__->belongs_to('HostID' => 'Runner::Model::Host');
__PACKAGE__->belongs_to('TestGroupID' => 'Runner::Model::TestGroup');

	and then call $schema->deploy({ add_drop_table => 1, no_comments => 1}).

	As this produces broken DDL (I've left the <*> error indicator
	in the output below):

CREATE TABLE host_to_testgroup (
   HostID varchar2 <*>NOT NULL,
   TestGroupID varchar2 NOT NULL,
   PRIMARY KEY (HostID, TestGroupID),
   CONSTRAINT fk_HostID FOREIGN KEY (HostID) REFERENCES hosts (id)
     ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT fk_TestGroupID FOREIGN KEY (TestGroupID)
     REFERENCES testgroups (id) ON DELETE CASCADE ON UPDATE CASCADE
)

	The first problem is that the varchar2 has no size value.

	I had hoped that the examples in the documentation would
	work regardless of the backend database engine... but I
	guess not. So I've changed the code to be more explicit
	about the data types:

__PACKAGE__->add_columns(
                          HostID => {
                                     data_type => 'integer',
                                    },
                          TestGroupID => {
                                          data_type => 'integer',
                                         }
                         );

	However, this only gets me a little further and I get
	the following (again I've left the <*> error indicator in):

CREATE TABLE host_to_testgroup (
   HostID number NOT NULL,
   TestGroupID number NOT NULL,
   PRIMARY KEY (HostID, TestGroupID),
   CONSTRAINT fk_HostID FOREIGN KEY (HostID) REFERENCES hosts (id)
     ON DELETE CASCADE <*>ON UPDATE CASCADE,
   CONSTRAINT fk_TestGroupID FOREIGN KEY (TestGroupID)
     REFERENCES testgroups (id) ON DELETE CASCADE ON UPDATE CASCADE
)

	So I'm not sure how to correct the issue with "ON UPDATE CASCADE".
	The specific ORACLE error is:

ORA-00907: missing right parenthesis.

	Any thoughts on how I can make the DBIx::Class::Schema
	deploy() method produce valid ORACLE DDL ?

	Any help much appreciated.

	Cheers,

	Doug

-- 
"The big print giveth and the small print taketh away"