generating primary keys w/ SQLite?

[prev] [thread] [next] [Date index for 2004/10/19]

Given the following simple schema:
   CREATE TABLE artist ( artistid INTEGER PRIMARY KEY,  name VARCHAR);

And the associated Class DBI definition, Music.pm:
   package Music::DBI;
   use base 'Class::DBI';
   Music::DBI->connection('dbi:SQLite:dbname=music.sqlite', '', '');

   package Music::Artist;
   use base 'Music::DBI';
   Music::Artist->table('artist');
   Music::Artist->columns(All => qw/artistid name/);

I get the following warning when I create a new artist using this code 
snippet:
   use Music;
   my $artist = Music::Artist->create({ name => 'U2' });

"Use of uninitialized value in subroutine entry at 
/Library/Perl/5.8.4/DBIx/ContextualFetch.pm line 51."

The Class::DBI caller is Class::DBI::_insert_row(). If I point a break 
point at Class/DBI.pm:636, the class data shows that the uninitialized 
value is the primary key for this object:
0  HASH(0x1982e78)
    'artistid' => undef
    'name' => 'U2'

Do I need to specify the primary key for each new object I create? 
(I've been using sequences for my Postgres databases, but this is my 
first time using Class::DBI w/ SQLite. I'd really like the automatic 
primary key generation.)

I noticed Class::DBI::SQLite overrides _auto_increment_value(), but 
adding this to my class definition fix the warning. I worked around the 
problem by overriding _insert_row() and having it call my own 
_auto_increment_value() if the primary key is not defined.

How are other people using SQLite?

I'm using Class::DBI v0.96, DBD::SQLite 1.07, and SQLite 3.

Thanks,

Drew