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

SYNOPSIS
     # 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 );

STATUS
   "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.

DESCRIPTION
   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
   implemented.

   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
   bad.

 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 work.

   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 PostgreSQL.

 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
   $DBIx::MySQLSequence::MYSQL_SEQUENCE_TABLE.

INTERFACE
   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
   does.

   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
   arguments.

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

METHODS
 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.

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

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

 exists
   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
   permissions.

   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.

 drop
   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.

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

 reset
   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.

 currval
   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.

 nextval
   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.

 remove_sequence_support
   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.

 errstr
   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.

   "$sequence-"errstr;>

   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

   "DBIx::MySQLSequence-"errstr;>

TO DO
   - 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.

AUTHORS
   Adam Kennedy <[email protected]>

   Patches are welcome

SEE ALSO
   DBIx::OracleSequence

COPYRIGHT
   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.

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.