Skip Menu |
 

This queue is for tickets about the SQL-Translator CPAN distribution.

Report information
The Basics
Id: 75818
Status: rejected
Priority: 0/
Queue: SQL-Translator

People
Owner: Nobody in particular
Requestors: fs5 [...] sanger.ac.uk
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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.
Moving ticket to the correct rt-queue
Download (untitled) / with headers
text/plain 1.7k
On 2012-03-16 14:57:56, fs5@sanger.ac.uk wrote: Show quoted text
> Problem: > generating DDL from a DBIC schema that uses an enum field results in > incorrect code for PostgreSQL and SQLite.
[…] Show quoted text
> __PACKAGE__->add_columns(
[…] Show quoted text
> "type", > { > data_type => "enum", > extra => { custom_type_name => "artist_types", list => ["singer", > "instrumentalist"] }, > is_nullable => 0, > }, > > );
[…] Show quoted text
> running the following commands: > use MusicBase::Schema; > my $schema = > MusicBase::Schema->connect("dbi:SQLite:share/muscbase-schema.db"); > $schema->create_ddl_dir();
[…] Show quoted text
> As there is no enum type in SQLite, an error should be raised to handle > this case correctly, I think.
SQLite is by nature very lax when it comes to types, and I don't think DBIC or SQLT should impose any further strictures on it. The generated DDL will work, and will let you insert the values declared in the enum list (but won't stop you inserting other values). Show quoted text
> 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.
SQL::Translator::Producer::PostgreSQL will only output the necessary CREATE TYPE … AS ENUM (…) statements if it's been told that the PostgreSQL version is >= 8.3, since older versions don't support it. ->create_ddl_dir() lets you pass options to SQL::Translator, so you can do: $schema->create_ddl_dir(undef, undef, undef, undef, # use defaults { producer_args => { postgres_version => 8.4 } } # or whatever version ); See https://metacpan.org/pod/DBIx::Class::Storage::DBI#create_ddl_dir for more details. Marking this ticket as rejected, as this is not a bug in DBIC nor SQLT.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.