| Subject: | enum leads to incorrect DDL for PostgeSQL and SQLite |
| Date: | Fri, 16 Mar 2012 14:57:36 +0000 |
| To: | bug-DBIx-Class [...] rt.cpan.org |
| From: | Frank Schwach <fs5 [...] sanger.ac.uk> |
Versions:
SQL::Translator::Parser::DBIx::Classs: 1.10
Perl: 5.8.8
Problem:
generating DDL from a DBIC schema that uses an enum field results in
incorrect code for PostgreSQL and SQLite.
Here is the Schema::Result class from an example, using a column "type"
that is defined as an ENUM data type:
Show quoted text
>>
package MusicBase::Schema::Result::Artist;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('artist');
__PACKAGE__->add_columns(
'artist_id' => { data_type => 'integer', },
'name' => {
data_type => 'varchar',
size => '96',
},
"type",
{
data_type => "enum",
extra => { custom_type_name => "artist_types", list => ["singer",
"instrumentalist"] },
is_nullable => 0,
},
);
__PACKAGE__->set_primary_key('artist_id');
<<
running the following commands:
use MusicBase::Schema;
my $schema =
MusicBase::Schema->connect("dbi:SQLite:share/muscbase-schema.db");
$schema->create_ddl_dir();
results in these files:
MusicBase-Schema-1-PostgreSQL.sql
MusicBase-Schema-1-SQLite.sql
MusicBase-Schema-1-MySQL.sql
The enum file "type" in the artists table is correctly defined in
MusicBase-Schema-1-MySQL.sql:
CREATE TABLE `artist` (
`artist_id` integer NOT NULL,
`name` varchar(96) NOT NULL,
`type` enum('singer', 'instrumentalist') NOT NULL,
PRIMARY KEY (`artist_id`)
) ENGINE=InnoDB;
but not in the other two:
MusicBase-Schema-1-PostgreSQL.sql:
...
"type" character varying NOT NULL,
MusicBase-Schema-1-SQLite.sql:
...
type enum NOT NULL
As there is no enum type in SQLite, an error should be raised to handle
this case correctly, I think.
For PostgreSQL, a user-defined ENUM type has to be defined and then used
by name. Alternatively, an error or warning could be raised to inform
the user that the definition must be revised manually.
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.