On 12/5/06 8:19 AM, Michael Lackhoff wrote:
> I need some pseudo-unique numbers like invoice numbers. They start every
> year with 1, so autoincrement won't work. My idea was to use a helper
> table with just just two fields 'year' and 'last_used_number'. Is it
> enough to create a two column unique key or would it be better to do
> something along these lines:
> - lock table (or row?)
> - read last_used_number for the year
> - increment it
> - save
> - unlock
>
> Is there an idiom for this kind of task? And how can I do it with RDBO?
Not really, you basically just do what you said. The only "extra" bits are
the locks and unlocks. (The locking behavior will probably be db-specific.)
Something like this:
package My::InvoiceNumber::Manager;
...
sub generate_invoice_number
{
my $class = shift;
my $db = My::DB->new; # Rose::DB subclass
my $num;
eval
{
my $table = My::InvoiceNumber->meta->table;
my $year = (localtime(time))[5] + 1900;
$db->dbh->do("LOCK $table ...");
my $nums =
$class->get_numbers(db => $db,
query => [ year => $year ]);
$num = $nums ? $nums->[0] :
My::InvoiceNumber->new(year => $year, last_used_number => 0);
$num->last_used_number($num->last_used_number + 1);
$num->save;
$db->dbh->do("UNLOCK $table");
};
die "Could not get invoice number - $@" if($@);
return $num->last_used_number;
}
You can even set it as the default value of a column using Scalar::Defer:
package My::Order;
...
use Scalar::Defer;
use My::InvoiceNumber::Manager;
...
__PACKAGE__->meta->setup
(
...
columns =>
[
invoice_number =>
{
type => 'int',
not_null => 1,
default => defer
{
My::InvoiceNumber::Manager->generate_invoice_number()
},
...
}
],
);
But that may be a bit too magical for your taste :)
If you're using Postgres, you can do the invoice number generation much
faster using a stored procedure, and then you can link it up to a column
using a trigger (also much faster than doing it perl-side).
-John
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
