On Sun, 29 Oct 2006, Corey wrote:
Mainly concerning DBIx::Class::ResultSource::add_columns(), and
SQL::Translator::Producer::DBIx::Class::File .
I created a pretty simple db (w/ postgresql ) call it 'MyDB'. I'll provide
a tiny snippet of the portions relevant to my questions:
CREATE TABLE animal (
id serial NOT NULL,
species_binomial varchar NOT NULL,
varchar NOT NULL,
);
CREATE TABLE animal_species (
binomial varchar NOT NULL,
common_name varchar NOT NULL
);
ALTER TABLE ONLY animal
ADD CONSTRAINT "animal_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY animal_species
ADD CONSTRAINT "animal_species_pkey" PRIMARY KEY (binomial);
ALTER TABLE ONLY animal
ADD CONSTRAINT "animal_species_binomial_fkey" FOREIGN KEY (species_binomial)
REFERENCES animal_species(binomial);
I then used sqlt to spit out a "MyDB.pm":
sqlt --trace --from DBI --dsn dbi:Pg:dbname=MyDB \
--db-user whatever --db-password whatever \
--to DBIx::Class::File --prefix "MyDB" > MyDB.pm
... and this is where the questions started to roll in:
package MyDB::Animal;
#<snip>
__PACKAGE__->add_columns(
'id' => {
'data_type' => 'integer',
'is_auto_increment' => 0,
'default_value' => 'nextval(\'animal_id_seq\'::regclass)',
'is_foreign_key' => 0,
'name' => 'id',
'is_nullable' => 0,
'size' => '4'
},
'species_binomial' => {
'data_type' => 'character varying',
'is_auto_increment' => 0,
'default_value' => undef,
'is_foreign_key' => 0,
'name' => 'species_binomial',
'is_nullable' => 0,
'size' => 0
},
#<snip>
};
Question #1:
As you can see above, first off, the relations weren't created - i.e.,
{'species_binomial'}->{'is_foreign_key'}
was set to 0; in addition, no __PACKAGE__->set_primary_key() was defined within
the package, nor was
there defined an add_relationship().
Is that due to the fact that sqlt needs foreign/primary key definitions to be
held/defined with the
CREATE TABLE, and using ALTER TABLE will not work? Or is something else amiss?
Are you using SQLT 0.07 or 0.08_01? (the development release) .. the dev
release is more likely to work, if neither do, then poke at the
Parser::DBI::PostgreSQL code and see why not? It shouldn't be how you
defined the foreign key relationships as it's not parsing the SQL when you
use the DBI version, but asking the DB directly. (Unless that SQL somehow
sets it up differently, no idea).
Question #2:
After browsing through the ResultSource perldoc, I see that many of those
attributes defined within
add_columns() were included by sqlt/DBIx::Class::File, but are not actually
used by DBIx::Class: i.e,
default_value, is_foreign_key, name, is_nullable, size. Additionaly, some
attributes that _are_ used
by DBIx::Class, were _not_ set: i.e., accessor, sequence.
Should I keep the unused attributes around for any reason, or delete them?
Does 'accessor' functionally replace 'name', and does 'sequence' functionally
replace 'default_value'?
Depends, what that should really say is "Only used by deploy", i.e. if you
want to create your tables from your schema data. Having them around
certainly won't harm anything.
Accessor is used for "having a different accessor method than the column
name", so it's an optional thing, useful if your column name has spaces or
other odd characters in it, or clashes with a DBIC built-in method.
Sequence doesn't replace default_value, again, it's optional to give the
sequence name for the fetching of PK values, if you are using sequences.
(The Pg parser should probably set that for you, it doesn't yet)
I'm not sure how you got the impression that some of the column_info
values replace other ones, since they are all described together?
Question #3:
Do I need to use add_relationship() in order to define foreign keys?
No, there's a whole bunch of convenience methods described in
DBIx::Class::Relationship.
Question #4:
Am I simply missing something regarding the correct usage of sqlt?
Nope, just that some Parsers are better than others at it, and you
probably want 0.08_01.
Jess
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
