[Dbix-class] ok - opening salvo of questions!

[Jess Robinson]

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