Problem with Class::DBI's create

[Molt]

I'm currently working on learning modules that look interesting, and have decided to do this by writing a simple web-based image gallery. One of the modules I'm most interested in is Class::DBI, but I've begun to hit problems with this.

I'm trying to create new database rows with the create method, yet it just doesn't seem to be working at all.

The database I'm using is Postgres, and the table I'm trying to work off is set up so the id field is automatically set for me. I know it's trying to do something since if I miss out a required field it throws errors, and also if I try and load an invalid value such as a string into a numeric field. The sequence number Postgres uses to base new entries off is also incrementing. There currently aren't any constraints set up in the database itself, and the data used in the test program inserts no problem from the command line psql program.

The class works fine for reading the contents of the database too. Is it possible that Class::DBI is somehow creating the row and then deleting it again?

A cut-down test version of the code:

#!/usr/bin/perl

use strict;
use warnings;
use Class::DBI;

# Declare a base class for us.  Gallery::Class::DBI
package Gallery::Class::DBI;
use base 'Class::DBI';
Gallery::Class::DBI->set_db('Main', 'dbi:Pg:dbname=database', 'user','
+password');

# Basic image declaration.
package Gallery::Image;
use base 'Gallery::Class::DBI';
Gallery::Image->table('gallery_images');
Gallery::Image->columns(All => qw/id title author height width filenam
+e gallery/);

# Do a creation.
my $image = Gallery::Image->create({
  title => 'Test',
  author => 1,
  height => 100,
  width => 100,
  filename => 'test.jpg',
  gallery => 1,
});
$image->commit;

The Postgres SQL used to create the table:

CREATE TABLE gallery_images (
  id SERIAL NOT NULL PRIMARY KEY,
  title TEXT,
  author INT NOT NULL,
  width INT,
  height INT,
  filename TEXT NOT NULL,
  gallery INT
);

[Ovid]

Postgres, unless I am terribly mistaken, uses sequences to generate IDs. Class::DBI requires that you identify the sequences, if used.

  $class->sequence( $sequence_name );

For your code, that should probably be something like the following in your base class:

  Gallery::Image->sequence('gallery_images_id_seq');

I also see that you're calling $class->commit without calling $class->dbi_commit. If you have transactions enabled, you'll have to call the latter after the former as the commit() method has nothing to do with committing a transaction. You may wish to read the docs about this issue. It's been a source of confusion here at work (and I stills screw it up).

Cheers,
Ovid

New address of my CGI Course.
Silence is Evil (feel free to copy and distribute widely - note copyright text)

[Molt]

Thanks, it was the dbi_commit which was the issue. Didn't think it could be that since I had tried autocommit, and also since the docs said that the DESTROY method of Class::DBI would warn if the program exited with uncommited changes.

Only goes to show you can't believe everything you read.