[Wellington-pm] Perl database stuff....

[Jacinta Richardson]

Andrew McMillan wrote:

> (c) In the perl you can make it "select, select, insert" as:
>  1. select the record (and fail).
>  2. select the sequence nextval
>  3. insert the row using the sequence.
> 
> Which is actually the way I would tend to do it myself because it seems
> so much cleaner to know the ID in advance and then write multiple SQL
> calls using that value.  I tend to take the (a) approach when I have
> some values that I want to 'either update or insert these values', which
> is slightly different from your question here.

Make sure you're aware of how your database handles sequencing here, and also 
what requirements you have on your data.  For example, if your database does not 
increment the sequence when you merely look at it, but rather does so when it is 
used then that could cause the same sequence number to be given out to multiple 
records.  You can test this pretty easily by just doing a number of reads and 
seeing whether they're all different.

If you're using the sequence number to just ensure unique identifiers then 
that's fine.  However, if you need all numbers to exist, then you've got a 
problem again.  How do you handle the case where you select the sequence number 
successfully, but then fail to insert the row using that number?  Assuming that 
sequencing is handled correctly (incremented upon view) then you'll end up with 
holes in your numbering.

Finally, you need to know how your database handles such holes (which may also 
be caused by item deletion).  Some databases re-use missing id numbers, some 
don't.  Are you relying on id numbers to give you chronological ordering of your 
data?  If so, does it matter that item 56 might actually hit the database sooner 
than items 54 and 55 due to network congestion?

If you don't want to have to worry about this sort of thing, you may find a 
database abstraction layer to be a better solution.  For example Class::DBI 
allows you to to solve this problem with code similar to the following:

	my $object = $class->find_or_create(\%data);
	my $seq = "$object";  # assuming this seq number is the primary key

I'm sure there are equally easy solutions in DBIx::Class, Tanagram and the others.

All the best,

	Jacinta