PEAR óÐÒÁ×ÏÞÎÏÅ ÒÕËÏ×ÏÄÓÔ×Ï
Пред.След.

Sequences

Sequences -- Sequences and auto-incrementing

ïÐÉÓÁÎÉÅ

Sequences are a way of offering unique IDs for data rows. If you do most of your work with e.g. MySQL, think of sequences as another way of doing AUTO_INCREMENT.

It's quite simple, first you request an ID, and then you insert that value in the ID field of the new row you're creating. You can have more than one sequence for all your tables, just be sure that you always use the same sequence for any particular table. To get the value of this unique ID use nextID(), if a sequence doesn't exists, it will be created automatically.

The sequence is automatically incremented each time nextID() is called.

Пример 34-1. Using a sequence

<?php
// Once you have a valid MDB2 object named $mdb2...
$id = $mdb2->nextID('mySequence');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

// Use the ID in your INSERT query
$res =& $mdb2->query("INSERT INTO myTable (id, text) VALUES ($id, 'foo')");
?>

úÁÍÅÔËÁ

Внимание

When using PEAR MDB2's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR MDB2's methods to access sequences that were created directly in the DBMS.

If you have a compelling reason to ignore this advice, be aware that the $seq_name argument given to all of PEAR MDB2's sequence methods are modified before MDB2 calls the underlying DBMS.

$seq_name is passed through PHP's sprintf() function using the value from the seqname_format option as sprintf()'s format argument. The default seqname_format is %s_seq. So, for example, if you use person_id_sequence as the $seq_name, PEAR MDB2 will change that name to person_id_sequence_seq when querying the DBMS about creating/accessing/updating the sequence.

Also note that the default table layout for sequences emulated in PEAR DB is slightly different in PEAR MDB2. Where PEAR DB calls the column "id" PEAR MDB2 instead calls it "sequence" to make its purpose more clear. For backward compatibility this can be controlled via the seqcol_name option.

The seqname_format and seqcol_name can be modified when connecting or via setOption().

Getting the last inserted ID

If you prefer using AUTO_INCREMENT you can alternatively use the lastInsertID() method to retrieve the last generated value. This method alternatively also supports getting the current ID from a sequence using the format defined in PostgreSQL's SERIAL data type. MDB2 can emulate this behaviour for RDBMS that do not support autoincrement at table creation time when using the createTable() method.

Пример 34-2. Using lastInsertID()

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query("INSERT INTO myTable (id, text) VALUES (NULL, 'foo')");

// optionally pass in a table and fieldname in order to call nextID()
// when autoincrement is not supported
$id = $mdb2->lastInsertID('myTable', 'id');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

?>

Getting the current ID

If you can get the current global value of a sequence using the currID() method.

Пример 34-3. Using currID()

<?php

// getting the current value of a sequence
$id = $mdb2->currID('myseq');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

?>

Getting around emulation

Finally if you prefer using what ever native feature the RDBMS supports you can use the getBeforeID() and getAfterID() methods from the Extended module. This way MDB2 will automatically use AUTO_INCREMENT if its natively supported. If not MDB2 will instead use a sequence to get the next id.

Пример 34-4. Using getBeforeID()/getAfterID()

<?php
// Once you have a valid MDB2 object named $mdb2...
// $id may either be a quoted integer or php null
$id = $mdb2->getBeforeID('myTable', 'id', true, true);
if (PEAR::isError($id)) {
    die($id->getMessage());
}

$res =& $mdb2->query("INSERT INTO myTable (id, text) VALUES ($id, 'foo')");

// $id is now equivalent to the value in the id field that was inserted
$id = $mdb2->getAfterID($id, 'myTable', 'id');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

?>

óÍ. ÔÁËÖÅ

createSequence(), dropSequence(), listSequences()

Пред.НачалоСлед.
PortabilityУровень вышеFAQ

HIVE: All information for read only. Please respect copyright!
Hosted by hive ÊÃÁ: Êèåâñêàÿ ãîðîäñêàÿ áèáëèîòåêà