[Dbix-class] Strict sequence primary key

You're missing the database specific facilities for this - which generally materializes as an 'auto_increment' data type (Sybase derivatives, mysql) modifier or the concept of a 'sequence' (oracle, db2, postgres(I think)) which you create and can query from with the nextval/curval functions.

David

-----Original Message-----
From: Wes Cravens
Sent: Wednesday, January 20, 2010 1:59 PM
To: [email protected]
Subject: [Dbix-class] Strict sequence primary key

In an effort to create strictly sequential (Incrementing Integer no
gaps) primary keys my method has traditionally been:

BEGIN;
LOCK table;
SELECT MAX(id) FROM table;
INSERT INTO table...;
COMMIT;

I'm relatively new to DBIC and have not really seen a way to do this
unless I create a wrapper to handle it.

Is there something I am missing?

Thanks,

Wes

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]

DB Specific sequences do not guarantee the necessary result. They can
have both gaps and 'numerically' out of sequence inserts.

Why do require strictly sequential primary keys?

Ronald

It's not always necessary but we do have architecture in an accounting
transaction system where items must be strictly sequential and can also
never be deleted. The auditing side of the application require this.

Wes

THIS IS A RELATIONAL THEORY ANSWER, NOT A DBIx::Class SPECIFIC ANSWER.
YOU HAVE BEEN WARNED.
Relational databases, particularly ones that may need to scale into
clusters, are very poor candidates for providing strict sequentiality.
Instead, sequentiality should be ensured through grouping and columns
recording high-resolution time-of-mutation (mutation == insertion /
modification / deletion). Time of mutation can be synchronized across
servers using NTP or similar protocols. Or, database servers do
provide timing mechanisms. Given that this is an accounting
application, you are likely using Oracle or Sybase. Both provide
high-resolution (1000th of a second) timestamps that are mediated by
the server.

Grouping can be done by either identifying a parent row(s) and/or
providing a group number that's assigned when the group is formed.
capturing more information.

Rob

I have been starting to re-think the architecture and use the timestamp
(it is also recorded), so your remarks reinforce this idea. So back to
the drawing board we go.
Indeed account numbers themselves work for the grouping.
Thanks for your thoughts,

Wes

*I'm not sure about how to lock the table, but you should be able to
accomplish the rest of the transaction using the txn_do method:*
*
*
*
http://search.cpan.org/~frew/DBIx-Class-0.08115/lib/DBIx/Class/Storage.pm#txn_do
*
*
*
*-Bob
*

Yes indeed the transaction functionality of DBIC is part of what I'm
after but it's not complete. I can get the lock by accessing the dbh
directly if need be. But in this case I will be writing my own 'create'
functions rather than leveraging the strength of DBIC directly. Which
is OK... I'm just trying to discover if there is a standard or more
direct way that I am missing.