Forum OpenACS Q&A: Re: RFC: Avoid exposing object_ids in permanent URLs
I have one table with all the last keys, which can also be initialised to logical start values and an increment value as well as a limit, where the numbering restarts.
This is some overhead but faster than the aggregate function solution and it leaves you in control. As it is in one place its functionality can be changed or improved without changing the logic in the different places of usage.
As it is a logical key (usually the table name) one can use any combination, i.e package-id+subsite-id etc).
This is just a simple approach and in now way the most sophisticated solution. It had to be done quickly and it does it's job since very well.
I insert the code here (out of my environment) just for illustration.
-- DBTYPE=pgs7, generated by zt_gen_sql at Tue Jan 14 16:29:16 GMT 2003
-- for PostgreSQL-7.x, to be executed by psql
-- (C) 1996/99 by Panorgan AG, CH-8820 Wädenswil
-- TABLE taf_idref - TAF ID Referenz Verwaltung
create table taf_idref
(
i_idref char(20) not null, -- ID Referenz Name
i_last decimal(10) not null, -- ID Letzter Wert
i_start decimal(10) not null, -- ID Startwert
i_incr decimal(4) not null, -- ID Inkrement
i_limit decimal(10) not null, -- ID Limite
primary key ( i_idref))
# ==========================================================
# y_db_getId -- generate uniqe id's for key fields
#
# be aweare of calling the proc in a db transaction
# otherwise we will commit the changes in here!
#
# USAGE: idref identifier for unique key (name of field)
# tablename idref table name (default taf_idref)
# start idref start value (default db definition)
# incr idref increment value (default db definition)
# limit idref limit value (default db definition)
#
# start, incr, limit are only considered for a new idref!
#
# RETURN: key generated key value
# ==========================================================
proc y_db_getId {idref {table {taf_idref}} {start {}} \
{incr {}} {limit {}} } {
global errorInfo errorCode DB_lib_priv Fields
# make sure we have the table def
y_table_def $table
# lock the table to make sure we are the only one fiddling
switch -- $DB_lib_priv(DB_TYPE) {
adab {
set lock "lock (wait) table $table in exclusive mode"
}
orac {
set lock "lock table $table in exclusive mode"
}
}
set ret [sql run $lock ]
Debug $ret 9
# read the last value
set cmd "select i_last, i_start, i_incr, i_limit from $table
where i_idref = '$idref'"
# open cursor
if [catch {sql open $cmd} c] {
Debug "Error in sql open: $c error=$errorCode"
if {$errorCode < 0} {
# return error
return -code error
}
}
Debug curs=$c 9
if {![string length $c]} {
# no record found, insert it
if {[string length $start]} {
set i_start $start
} else {
set i_start $Fields(i_start.value)
}
set i_last $i_start
if {[string length $incr]} {
set i_incr $incr
} else {
set i_incr $Fields(i_incr.value)
}
if {[string length $limit]} {
set i_limit $limit
} else {
set i_limit $Fields(i_limit.value)
}
set ins "insert into $table (i_idref,i_last,i_start,i_incr,i_limit) \
values ('$idref',$i_last,$i_start,$i_incr,$i_limit)"
set ret [sql run $ins]
if {$ret <= 0} {
# return error
return -code error -errorcode $ret
}
} else {
# fetch record
set row [sql fetch $c ]
Debug $row 9
set ret [sql close $c]
array set r $row
set i_last $r(i_last)
set i_start $r(i_start)
set i_incr $r(i_incr)
set i_limit $r(i_limit)
}
# if freshly inserted
if { $i_last == 0 } {
set i_last $i_start
}
# check limit
if { $i_limit > 0 && $i_last >= $i_limit } {
set i_last $i_start
}
# now increment last and update row on the fly
incr i_last $i_incr
set upd "update $table set i_last = $i_last \
where i_idref = '$idref'"
set ret [sql run $upd]
if {$ret <= 0} {
# return error
return -code error -errorcode $ret
}
# commit if not within transaction !
if {$DB_lib_priv(tr_stat) == 0 } {
set ret [sql run "COMMIT WORK"]
}
y_db_replication write $i_last
return $i_last
}
