FAQ
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

Search Discussions

  • Ihnen, David at Jan 20, 2010 at 10:12 pm
    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: dbix-class@lists.scsys.co.uk
    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/dbix-class@lists.scsys.co.uk
  • Wes Cravens at Jan 20, 2010 at 10:34 pm

    Ihnen, David wrote:
    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.
    DB Specific sequences do not guarantee the necessary result. They can
    have both gaps and 'numerically' out of sequence inserts.
    David

    -----Original Message-----
    From: Wes Cravens
    Sent: Wednesday, January 20, 2010 1:59 PM
    To: dbix-class@lists.scsys.co.uk
    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/dbix-class@lists.scsys.co.uk

    _______________________________________________
    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/dbix-class@lists.scsys.co.uk
  • Ronald J Kimball at Jan 20, 2010 at 10:14 pm

    Wes Cravens wrote:
    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?
    Why do require strictly sequential primary keys?

    Ronald
  • Wes Cravens at Jan 20, 2010 at 10:36 pm

    Ronald J Kimball wrote:
    Wes Cravens wrote:
    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?
    Why do require strictly sequential primary keys?
    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
  • Rob Kinyon at Jan 20, 2010 at 11:15 pm
    THIS IS A RELATIONAL THEORY ANSWER, NOT A DBIx::Class SPECIFIC ANSWER.
    YOU HAVE BEEN WARNED.
    On Wed, Jan 20, 2010 at 17:36, Wes Cravens wrote:
    Why do require strictly sequential primary keys?
    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.
    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.
    From this, sequentiality can be reconstructed as needed, plus you are
    capturing more information.

    Rob
  • Wes Cravens at Jan 21, 2010 at 3:01 pm

    Rob Kinyon wrote:
    THIS IS A RELATIONAL THEORY ANSWER, NOT A DBIx::Class SPECIFIC ANSWER.
    YOU HAVE BEEN WARNED.
    On Wed, Jan 20, 2010 at 17:36, Wes Cravens wrote:
    Why do require strictly sequential primary keys?
    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.
    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.
    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.
    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.
    Indeed account numbers themselves work for the grouping.
    From this, sequentiality can be reconstructed as needed, plus you are
    capturing more information.
    Thanks for your thoughts,

    Wes
  • Robert Stockdale at Jan 20, 2010 at 10:17 pm
    *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
    *
    On Wed, Jan 20, 2010 at 4:59 PM, Wes Cravens wrote:

    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/dbix-class@lists.scsys.co.uk


    --
    Bob Stockdale
    robert.stockdale@gmail.com
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100120/06fef29b/attachment.htm
  • Wes Cravens at Jan 20, 2010 at 10:36 pm

    Robert Stockdale wrote:
    *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:*
    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.
    *
    *
    *
    http://search.cpan.org/~frew/DBIx-Class-0.08115/lib/DBIx/Class/Storage.pm#txn_do
    *
    *
    *
    *-Bob
    *
    On Wed, Jan 20, 2010 at 4:59 PM, Wes Cravens wrote:

    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/dbix-class@lists.scsys.co.uk



    ------------------------------------------------------------------------

    _______________________________________________
    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/dbix-class@lists.scsys.co.uk

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedJan 20, '10 at 9:59p
activeJan 21, '10 at 3:01p
posts9
users5
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2022 Grokbase