Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBIx::MySQLSequence(3)User Contributed Perl DocumentatioDBIx::MySQLSequence(3)

       DBIx::MySQLSequence - Proper and	correct	(emulated) sequence support
       for MySQL

	 # Get a handle	to a new or existing sequence
	 $dbh	   = DBI->connect( 'dbi:mysql:db:host',	'user',	'pass' );
	 $sequence = DBIx::MySQLSequence->new( $dbh, 'sequence_name' );

	 # Does	the sequence already exist?
	 if ( $sequence->exists	) {
	       die "Sequence already exists";

	 # Create the sequence
	 unless	( $sequence->create ) {
	       die "Failed to create sequence";

	 # Get the next	value off the sequence
	 $id = $sequence->nextval;

	 # Drop	the sequence
	 unless	( $sequence->drop ) {
	       die "Failed to drop sequence";

	 # Remove sequence emulation support entirely
	 DBIx::MySQLSequence->remove_sequence_support( $dbh );

       "DBIx::MySQLSequence" is	complete and has been used to real
       application, but	does not have paranoidly thorough unit testing (yet).

       Please report any issues	you encounter.

       The "DBIx::MySQLSequence" package implements an emulation layer that
       provides	"real" sequences on MySQL. The module works by creating	a
       "sequence table", a single table	where each record represents a single
       sequence, and performing	some "magic" MySQL specific SQL	to ensure the
       sequences will work correctly.

   What	is a sequence?
       A sequence is a source of guarenteed unique numbers within a particular
       context.	These may or may not be	in order, and in fact in typical
       database	systems	they are rarely	perfectly incremental. It is much more
       preferrable that	they are strictly unique than that they	are perfectly
       in order. In any	case, DBIx::MySQLSequence does actually	return
       sequence	values in order, but this will probably	change once caching is

       In short, this is AUTO_INCREMENT	done right. Oracle, PostgreSQL and
       practically all other major database support sequences. MySQL does not.

   Why do I need sequences? Isn't AUTO_INCREMENT enough?
       MySQL provides its own AUTO_INCREMENT extention to SQL92	to implement
       incrementing values for primary keys.

       However,	this is	not a very nice	way to do them.	I won't	get into the
       reasoning in depth here,	but primarily there are	huge advantages	to be
       had by knowing the value	you are	going to use BEFORE you	insert the
       record into the database. Additionally, if records with the highest
       value for the AUTO_INCREMENT are	deleted, their values will (in some
       versions	of MySQL) be re-used for the next record.  This	is very	very

   DBIx::MySQLSequence Feature Summary
	 - Sequence names are case insensitive.
	 - Sequence names can be any string 1 to 32 chars in length.
	 - Sequence names can include spaces and other control characters.
	 - Sequence values use BIGINT fields, so the start, increment
	   and current values can be any integer between
	   -9223372036854775808	and 9223372036854775807.
	 - The module is safe for multiple database users or connections.
	 - The module is not transaction friendly. ( See below )
	 - The module is probably NOT thread safe.

   Transaction Safety
       Because the sequences are emulated through tables, they will have
       problems	with transactions, if used inside the same database connection
       as your normal code. This is not	normally a problem, since MySQL
       databases are not historically used for transaction based database

       If you are using	transactions in	MySQL, you can and should ensure have
       a seperate connection open to do	additional statements outside the
       scope of	the task the transaction is being used for.

       You should use that connection to get the sequence values.

       Any "DBIx::MySQLSequence" methods called	on a handle that isn't in an
       autocommit state	will cause a fatal error.

       It is highly recommended	that if	you need to do transactions, you
       should consider looking at something ore	robust that supports
       suequences properly. Most people	running	up against the limits and
       idiosyncracies of MySQL tend to be much more relaxed once they discover

   MySQL Permissions
       At the time the first sequence is created, you will need	"CREATE"
       permissions in the database. After this,	you will need "INSERT",
       "UPDATE"	and "DELETE" on	the sequence table. Should you want to remove
       sequence	support	completely, the	"DROP" permission will also be needed.

       The default name	for the	sequence table is contained in the variable

       The interface for "DBIx::MySQLSequence" is very flexible, and largely
       inspired	by the interface to "DBIx::OracleSequence". It is somewhat
       simpler though, as we don't need	or aren't capable of everything	Oracle

       To quickly summarise the	main methods.

	 exists	 - Does	a sequence exist
	 create	 - Create a sequence
	 drop	 - Drop	a sequence
	 reset	 - Resets the current value to the start value
	 currval - Get the current value
	 nextval - Get the next	value
	 errstr	 - Retrieve an error message should one	occur
	 remove_sequence_support - Removes the sequence	table completely

   Hybrid Interface
       Most of the methods in "DBIx::MySQLSequence" will act in	a hybrid
       manner, allowing	you to interact	with an	object or directly with	the
       class (statically).

       For example, the	following two code fragments are equivalent.

	 # Instantiation and Object Method
	 $sequence = DBIx::MySQLSequence->new( $dbh, 'sequence_name' );
	 $sequence->create( $start_value );

	 # Static Method
	 DBIx::MySQLSequence->create( $dbh, 'sequence_name', $start_value );

       As demonstated here, when calling a method statically, you should
       prepend a DBI database handle and sequence name to the method's

       Note: "remove_sequence_support" can ONLY	be called as a static method.

   new $dbh, $name
       The "new" constructor creates a handle to a new or existing sequence.
       It is passed as arguments a valid autocommit state MySQL	DBI handle,
       and the name of the sequence. Returns a new DBIx::MySQLSequence object,
       or undef	on error.

       The "dbh" object	method returns the DBI handle of the database the
       object is using.

       The "name" object method	returns	the sequence name for the handle

       Static Syntax: "DBIx::MySQLSequence-"exists( $dbh, $name	);>

       Examines	the database to	determine if a sequence	exists in the
       database.  Returns true if the sequence exists. Returns false if	the
       sequence	does not exists, or sequence support has not been created in
       the database.

   create [ $start ][, $increment ]
       Static Syntax: "DBIx::MySQLSequence-"create( $dbh, $name	[, $start ][,
       $increment ] );>

       Creates a sequence in the database. The create method takes optional
       arguments of the	value you want to sequence to start at,	and the	amount
       you want	the value to increment ( or decrement )	by.

       For example

       "$sequence-"create( 10, 5 )>

       The above would create a	new sequence whose value starts	at 10, and
       increments by 5 each time a value is returned. If not passed, the
       default is a starting value of 1, and an	increment of 1.	These are the
       defaults	typically used by databases internally.

       If called as an object method, returns a	true if	the sequence is
       created,	or undef if an error occurs, or	the sequence already exists.

       If called as a static method, it	will return a new handle to the
       created sequence, or undef if an	error occurs, or the sequence already
       exists. You can use this	as a sort of alternate constructor.

       "my $sequence = DBIx::MySQLSequence-"create( $dbh, $name, 5 );>

       DBIx::MySQLSequence will	work quite happily without the sequence	table
       existing. It will be automatically created for you the first time that
       you create a sequence. Please note that this will mean that you need
       CREATE and INSERT permissions when you create the first sequence.

       Once the	first sequence is created, you will only need INSERT

       DBIx::MySQLSequence will	not check for permissions for you, as the
       MySQL process for checking permissions is a bit too involved, so	you
       will most likely	only find out about this when the SQL statement	fails.
       You should check	that you have CREATE permissions before	you start
       using the database.

       Static Syntax: "DBIx::MySQLSequence-"drop( $dbh,	$name );>

       The "drop" method will drop a sequence from the database. It returns
       true on success,	or undef on error.

       Please note that	when the last sequence is removed, the module will NOT
       remove the sequence table. This is done in case you are operating on a
       database, and do	not have CREATE	permissions.  In this situation, the
       module would not	be able	to re-create the sequence table	should it need

       To remove the sequence table completely,	see the
       "remove_sequence_support" method.

       Static Syntax: "DBIx::MySQLSequence-"reset( $dbh, $name );>

       The "reset" method will return the sequence to the state	it was in when
       it was originally created.  Unlike Oracle, we do	not need to drop and
       re-create the sequence in order to do this. Returns true	on success, or
       undef on	error.

       Static Syntax: "DBIx::MySQLSequence-"currval( $dbh, $name );>

       The "currval" method retrieves the current value	of a sequence from the
       database.  The value that this returns is currently unreliable, but
       SHOULD match the	last value returned from the sequence. Returns the
       sequence	value, or undef	on error.

       Static Syntax: "DBIx::MySQLSequence-"nextval( $dbh, $name );>

       The "nextval" method retrieves the next value of	a sequence from	the
       database.  Returns the next value, or undef on error.

       The "remove_sequence_support" method is a static	only method that is
       used to remove sequence support completely from a database, should you
       no longer need it.  Effectively,	this just deletes the sequence table.
       Once you	have removed sequence support, any existing sequence object
       will most likely	throw errors should you	try to use them.

       Static Syntax: "DBIx::MySQLSequence-"errstr;>

       When an error occurs ( usually indicated	by a method return value of
       "undef" ), the "errstr" method is used to retrieve any error message
       that may	be available.  Any error message specific to a object method
       will be available from that object using.


       If you use a static method, or one of the above object method in	its
       static form, you	should retrieve	the error message from the class
       statically, using


       - More testing, but then	there's	ALWAYS more testing to do

       In Oracle, sequence values are cached server side. We can emulate this
       by creating a DBIx::MySQLSequence::Cache	object to do caching client
       side, for when people want to get a lot of sequence values without
       having to go back to the	server all the time.

       This would be a good thing. It would make things	MUCH faster.

       Adam Kennedy <>

       Patches are welcome


       Copyright 2002, 2007 Adam Kennedy.

       This program is free software; you can redistribute it and/or modify it
       under the same terms as Perl itself.

       The full	text of	the license can	be found in the	LICENSE	file included
       with this module.

perl v5.32.0			  2007-12-11		DBIx::MySQLSequence(3)


Want to link to this manual page? Use this URL:

home | help