Sequence(3pm)

💖 Sequence(3pm) — DBIx::Sequence, bestie edition 💖

okay so real talk, this is the manual page for Sequence(3pm), a.k.a. DBIx::Sequence, coming at you from the User Contributed Perl Documentation corner of the world. she's a little vintage (year 2000 vibes, we love her) but she WORKS. let's get into it 🦋

---

✨ NAME ✨

DBIx::Sequence — a simple SQL92 ID generator. yes bestie, that's the whole job. she generates IDs. that's her main character arc. 👑

---

✨ SYNOPSIS ✨

use DBIx::Sequence;

my $sequence = new DBIx::Sequence({ dbh => $dbh });
my $next_id = $sequence->Next('dataset');

that's it. that's the whole flow. you make her, you ask her for a number, she gives you a number. iconic behaviour fr fr 💅

---

✨ DESCRIPTION ✨

so the whole POINT of this module is making your Perl database app portable. instead of getting tied down to your specific database's SEQUENCE or auto_increment (imagine being that codependent 😭), you let DBIx::Sequence hand out your unique IDs, and suddenly your app can live its best life on multiple database environments. freedom!! 🦋

under the hood she implements a simple Spin Locker mechanism, and she is guaranteed to return a unique value every single time you call her — even with concurrent processes running around being messy. no cap. she uses your database for state storage, via ANSI SQL92 compliant SQL. and here's the slay: every SQL query inside DBIx::Sequence is pre cached and very efficient, especially under mod_perl. obsessed with the efficiency honestly ✨

---

✨ INSTALLATION ✨

perl Makefile.PL
make
make test
make install

Note, bestie:

if you decide to run the extended tests for the module, you're gonna have to hand make test a DSN (connect string) to your database — like dbi:Driver:db;host=hostname — plus a valid username/password combo for a privileged user. she needs credentials, she's not gonna guess 🌸

DBIx::Sequence uses 2 tables for its operation, namely dbix_sequence_state and dbix_sequence_release. those tables get created for you if you run the extended tests — if you don't, you will need to create them yourself. no shortcuts, babe 🎀

dbix_sequence_state:
| dataset  | varchar(50) |
| state_id | int(11)     |

dbix_sequence_release:
| dataset     | varchar(50) |
| released_id | int(11)     |

and good news: those table names are overloadable at your convenience. see the OVERLOADING section for the deets 💜

---

✨ BASIC USAGE ✨

the basic vibe is: generate a unique ID so you don't have to use your database's SEQUENCE or auto_increment field. simple. clean. slay.

🌈 INIT

first things first, you gotta create the sequence object:

use DBIx::Sequence;
my $sequence = new DBIx::Sequence({
                                        db_user => 'scott',
                                        db_pw => 'tiger',
                                        db_dsn => 'dbi:mysql:scottdb',
                                        allow_id_reuse => 1,
                                        });

DBIx::Sequence can manage multiple sets of IDs — maybe you want one dataset per table, or one and only one dataset, your call bestie. this means you can handle multiple applications with the same sequence class. the dataset is normally just a token string representing your ID set. and here's the cute part: if the dataset doesn't exist, DBIx::Sequence will create it automagically for you. no special steps, no ceremony, she just handles it ✨

the arguments carry the database info — db_user, db_pw and db_dsn — and they're stored in a hash reference.

at this point the object has already pre cached all of the SQL that'll be used to generate the spin locker race. it's normally a good idea to have a shared sequence object (especially under mod_perl) so you save on the prepare overhead. and the allow_id_reuse argument can be passed to the constructor to either allow the use of Release() or deny it. (true value = allowed, periodt 💅)

🌈 GETTING THE NEXT ID

to get the next id you simply use the Next() method of your sequence, while specifying the dataset you're getting the next id for:

my $next_id = $sequence->Next($dataset);

look at her go 👑

🌈 RELEASING ID'S.

generated IDs can be _explicitly_ released in your application. when an ID is released, the sequence will be able to give this id back to you through the Next() method. recycling era ♻️✨

here's how it's done:

$sequence->Release($dataset, $id);

Note:

you must use release only when you are _CERTAIN_ that your ID is not used anymore and that you want it recycled. also, the Spin Locking mechanism takes place on released IDs too, so no two processes can ever grab the same ID. the lock is locking, bestie 🔒

🌈 PERMANENTLY REMOVING A DATASET

to make DBIx::Sequence forget about an existing dataset, you use the Delete_Dataset() method:

$sequence->Delete_Dataset($dataset);

this clears all state and existence for that dataset, and it also clears its released IDs. heads up though: if your application still uses this dataset, it'll be automatically recreated blank. so like… it's gone, but not gone gone 😭

🌈 BOOTSTRAPPING A DATASET FROM EXISTING DATA

you can also sync the state of a DBIx::Sequence dataset using the Bootstrap() method:

$sequence->Bootstrap('my_dataset','my_bootstrap_table','my_primary_field');

Bootstrap() takes 3 arguments:

• the dataset to bootstrap
• the table from which you will bootstrap
• the field in the bootstrap table that will be used to bootstrap the dataset

Bootstrap will then sync up DBIx::Sequence's state with the maximum id of the 'my_primary_field' in 'my_bootstrap_table'. the bootstrap field must be a numeric field, as you can probably suspect 💀 and the SQL function MAX() will be called on it during the bootstrap process.

Note: the bootstrap method _can_ be used at runtime, since it will initiate a race for updating the value, thus following the same algorithm. it is recommended though that you use Bootstrap() when no other concurrent processes are requesting ids. timing is everything, babe 🕐

---

✨ OVERLOADING ✨

you can totally create an overloaded class of DBIx::Sequence. this lets you build a DBIx::Sequence with different properties than the original one. and the only things you really have to overload to change her behaviour are some constants:

• STATE_TABLE : defines the table used by DBIx::Sequence to store dataset's states.
• RELEASE_TABLE : defines the table used by DBIx::Sequence to store released id's.
• COLUMN_PREFIX : a string to be prepended to every column in the internal SQL statements.
• DEFAULT_INIT_VALUE : value used to initialize a dataset when it is first created.
• DEFAULT_ALLOW_ID_REUSE : when set to true, will allow the use of Release() if not specified in the constructor. (allow_id_reuse)
• DEBUG_LEVEL : when set to true, will enable debugging to STDERR.

so yeah, it's very easy to specify the behaviour of DBIx::Sequence you wish to use, just by creating an overloaded class 🎀

also — and this one is very important — a method you'll want to overload is getDbh(). this is the function that returns the database handle to the DBIx::Sequence. your overloaded class should redefine the getDbh method.

overloading getDbh will make your sequence class integrate more cleanly with your application. chef's kiss 🤌

i.e.

package MySequence;

use DBI;
use DBIx::Sequence;

use vars qw(@ISA);
@ISA = qw(DBIx::Sequence);

use constant STATE_TABLE => 'my_state_table';
use constant RELEASE_TABLE => 'my_release_table';
use constant COLUMN_PREFIX => '';
use constant DEFAULT_INIT_VALUE => '100';
use constant DEFAULT_ALLOW_ID_REUSE => 1;
use constant DEBUG_LEVEL => 0;

sub getDbh
{
        my $self = shift;

        return MyApplication::MyDBModule::getDbh();
}

1;

then your code can use this class for its sequencing. and notice: since we overloaded getDbh(), we don't need to pass a second parameter to new(). she already knows 🧚

use MySequence;

my $sequence = new MySequence();
my $next_id = $sequence->Next($dataset);

---

✨ SPECIAL NOTE ON DATABASE HANDLE OPTIONS ✨

okay this one's a big deal so listen up: DBIx::Sequence requires that the dbh object you pass to it has the AutoCommit flag set to 1. the main reason is that if AutoCommit is off, DBIx::Sequence would have to do an implicit commit() call, which in most cases is a bad idea — especially when the dbh passed to the sequence object already has transactions prelogged in it. don't do that to her 🙈

---

✨ CVS AND BLEEDING VERSIONS ✨

for the latest development information, CVS access and Changelog, please visit:

http://labs.turbulent.ca

if you use this module in a project, please let me know! 💖

your comments and rants are more than welcomed! 😤

commercial support for this module is available, please contact me for info! 💰

---

✨ TODO ✨

• implement multiple locking mechanism (semaphore, spin, db locker)
• implement pluggable locking module support

she's got plans. she's got vision. she's got a roadmap 📝✨

---

✨ AUTHOR ✨

Benoit Beausejour, <bbeausej@pobox.com>

---

✨ NOTES ✨

this code was made possible by the help of individuals:

Philippe "Gozer" M. Chiasson <gozer@cpan.org>

thanks to Uri Guttman for documentation checks ;) 🫶

---

✨ CONTRIBUTORS ✨

here are the people who submitted patches and changes to the module, they have my thanks for their contributions:

Trevor Shellhorn <trevor.schellhorn-perl@marketingtips.com>

Dan Kubb <dkubb@cpan.org>

---

✨ SEE ALSO ✨

perl(1).

---

✨ COPYRIGHT ✨

Copyright (c) 2000 Benoit Beausejour <bbeausej@pobox.com> All rights reserved. This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

---

and that's the whole page, bestie!! 🎀 this was perl v5.36.0, dated 2022-12-13, and she's still out here handing out IDs like the queen she is. go forth and sequence responsibly 💅💖🌈