How to default all primary keys to auto_insert

[Ross Attrill]

Hi All,

I have an MSSQL database with single column primary keys with identity insert.

Does anybody have the trick to globally set the primary key column to auto_insert?   I am wanting to do something like the below.

DBIx::DataModel->define_schema(

  class => 'My::Schema',

  %options,

);

my %options = (

  auto_insert_columns => $schema->primary_keys(), # or something?

);

Or will I need to do this on a table by table basis? Thank you in advance for any help for this.

[Dami Laurent (PJ)]

 

 

De : dbix-da...@googlegroups.com [mailto:dbix-da...@googlegroups.com] De la part de Ross Attrill
Envoyé : jeudi, 26. avril 2012 09:33
À : dbix-da...@googlegroups.com
Objet : [dbix-datamodel] How to default all primary keys to auto_insert

Hi Ross,

 

Nice to hear of a new DBIDM user !

 

I dont't really understand what you are trying to achieve, nor what you mean by "primary keys with identity insert".

 

- if keys are computed by the database (through triggers or sequences), then you don't need to use auto_insert, because the client-side has nothing to do. The DBIx::DataModel layer will use last_insert_id to retrieve the key, return it as result from the insert(), and also inject it within the record.

 

- if you want to automatically compute keys for each table from the client-side (but I wouldn't recommend this way -- it is far better to let the database do the work), then you could defined a generic sub that computes keys for every table, according to whichever algorithm you want to implement:

 

sub compute_key_for_table {

  my ($record_ref, $table_class) = @_;

  my $pk = …. ; # generate a new key for that class

  return $pk;

}

 

my %schema_options = (auto_insert_columns => \&compute_key_for_table);

 

Cheers, Laurent Dami

[Ross Attrill]

Thank you Laurent,

What I meant by 'primary keys with identity insert' was that the database server automatically provides an incrementing integer primary key.  This is an MSSQL feature and available in some other platforms.   The equivalent with triggers and sequences would be a before insert trigger getting a new number from a sequence.

If I understand your answer correctly, DBIDM will 'do the right thing' automatically and let the database assign the primary key value - which is great.   I will do some more experimentation.

I am very interested in DBIDM since it has a cleaner API than DBIC and simpler approach to dumping retrieved results. 

Thanks again,

Ross.

On Thursday, 26 April 2012 18:01:50 UTC+10, dami wrote:

 

 

De :  [mailto:] De la part de Ross Attrill

Envoyé : jeudi, 26. avril 2012 09:33
À :