Обсуждение: pgsql and adodb's genID() for unique id sequence...

does anybody use this technique?  it is supported by
pgsql, however, setting it up isn't intuitive to this
newbie.

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

http://phplens.com/lens/adodb/tips_portable_sql.htm

When you create records, you need to generate unique
id's for each record. There are two common techniques:
(1) auto-incrementing columns and (2) sequences.

Auto-incrementing columns are supported by MySQL,
Sybase and Microsoft Access and SQL Server. However
most other databases do not support this feature. So
for portability, you have little choice but to use
sequences. Sequences are special functions that return
a unique incrementing number every time you call it,
suitable to be used as database keys. In ADOdb, we use
the GenID( ) function. It has takes a parameter, the
sequence name. Different tables can have different
sequences.

$id = $connection->GenID('sequence_name');
$connection->Execute("insert into table (id,
firstname, lastname) values ($id, $firstname,
$lastname)");

For databases that do not support sequences natively,
ADOdb emulates sequences by creating a table for every
sequence.

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

i'm left wondering if i need to create a sequence
table with name 'sequence_name' before this technique
will work.  or, rather, is it just a name i need to
plug in to uniquely identify a particular sequence
controlled by the function?

i'm sorry this is 50% off topic (adodb) and 50% on
topic (pqsql).

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--- operationsengineer1@yahoo.com wrote:

> does anybody use this technique?  it is supported by
> pgsql, however, setting it up isn't intuitive to
> this
> newbie.
>
> -------------------------
>
> http://phplens.com/lens/adodb/tips_portable_sql.htm
>
> When you create records, you need to generate unique
> id's for each record. There are two common
> techniques:
> (1) auto-incrementing columns and (2) sequences.
>
> Auto-incrementing columns are supported by MySQL,
> Sybase and Microsoft Access and SQL Server. However
> most other databases do not support this feature. So
> for portability, you have little choice but to use
> sequences. Sequences are special functions that
> return
> a unique incrementing number every time you call it,
> suitable to be used as database keys. In ADOdb, we
> use
> the GenID( ) function. It has takes a parameter, the
> sequence name. Different tables can have different
> sequences.
>
> $id = $connection->GenID('sequence_name');
> $connection->Execute("insert into table (id,
> firstname, lastname) values ($id, $firstname,
> $lastname)");
>
> For databases that do not support sequences
> natively,
> ADOdb emulates sequences by creating a table for
> every
> sequence.
>
> -----------------------
>
> i'm left wondering if i need to create a sequence
> table with name 'sequence_name' before this
> technique
> will work.  or, rather, is it just a name i need to
> plug in to uniquely identify a particular sequence
> controlled by the function?
>
> i'm sorry this is 50% off topic (adodb) and 50% on
> topic (pqsql).
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>

i found out how to create a pgsql sequence in pgsql
7.4...

http://www.postgresql.org/docs/7.4/static/sql-createsequence.html

i guess my question now becomes - what are the
relative merits of using a serial within each table or
using a sequence that supports all tables (or several
sequences to support tables of my choosing)?



__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com

On Wed, 2 Feb 2005 12:21:51 -0800 (PST), operationsengineer1@yahoo.com
<operationsengineer1@yahoo.com> wrote:
> does anybody use this technique?  it is supported by
> pgsql, however, setting it up isn't intuitive to this
> newbie.

Perhaps we should start this off with a very simple example:

CREATE TABLE bands (
  id serial NOT NULL PRIMARY KEY,
  band_name varchar(32) NOT NULL UNIQUE
)

When you create this table, it basially takes care of the sequence
with the 'serial' datatype. This is analogous to MySQL's
AUTO_INCREMENT feature.

If you use this philosophy for your datamodel, then you won't need to
create sequences as they will be done automatically for you when the
tables are created.

Now, how do you get access to them? Well just like MySQL'sa
AUTO_INCREMENT, they are automatic (default values) for inserts into
that table. So the following:

INSERT INTO bands (band_name) VALUES ('green day');

Would give you a record with an id of 1, and a band_name of 'green
day'. If you were to repeat with different bands, they also would get
increasing id numbers as well. This is all driven by the sequence that
was automatically created for you when you defined the id column as a
serial datatype (which in essence is an integer with a sequence
created and set as the default value).

If you ever need to know the new value of that id right after you
insert a record, you can also get it by using ADOdb's Insert_ID()
function, documented here:

    http://phplens.com/lens/adodb/docs-adodb.htm#inserted_id

One other thing - I would suggest setting up PgAdminIII or phpPgAdmin
for visual access to the database, as that will let you explore the
schema and see the sequences and their values. Much easier than on the
CLI for most newcomers.

HTH,

-- Mitch