[Wellington-pm] Perl database stuff....
Jacinta Richardson
jarich at perltraining.com.au
Sun Dec 24 03:55:18 PST 2006
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
More information about the Wellington-pm
mailing list