db_nextval (public)
db_nextval [ -dbn dbn ] sequence
Defined in packages/acs-tcl/tcl/01-database-procs.tcl
Example:
set new_object_id [db_nextval acs_object_id_seq]
- Switches:
- -dbn
(optional)- The database name to use. If empty_string, uses the default database.
- Parameters:
- sequence - the name of an SQL sequence
- Returns:
- the next value for a sequence. This can utilize a pool of sequence values.
- See Also:
- Partial Call Graph (max 5 caller/called nodes):
- Testcases:
- acs_object_procs_test, ad_proc_permission_grant_and_revoke, ad_proc_permission_permission_p
Source code: set driverkey [db_driverkey $dbn] # PostgreSQL has a special implementation here, any other db will # probably work with the default: switch -- $driverkey { postgresql { # # the following query will return a nextval if the sequence # # is of relkind = 'S' (a sequence). if it is not of relkind = 'S' # # we will try querying it as a view: # if { [db_0or1row -dbn $dbn nextval_sequence " # select nextval('${sequence}') as nextval # where (select relkind # from pg_class # where relname = '${sequence}') = 'S' # "]} { # return $nextval # } else { # ns_log debug "db_nextval: sequence($sequence) is not a real sequence. perhaps it uses the view hack." # db_0or1row -dbn $dbn nextval_view "select nextval from ${sequence}" # return $nextval # } # # The code above is just for documentation, how it worked # before the change below. We keep now a per-thread table of # the "known" sequences to avoid at run time the query, # whether the specified sequence is a real sequence or a # view. This change makes this function more than a factor # of 2 faster than before. # # Note that solely the per-thread information won't work for # freshly created sequences. Therefore, we keep the old # code for checking at run time in the database for such # occurrences. # # Note that the sequence handling in OpenACS is quite a # mess. Some sequences are named t_SEQUENCE (10 in # dotlrn), others are called just SEQUENCE (18 in dotlrn), # for some sequences, additional views are defined with an # attribute 'nextval', and on top of this, db_nextval is # called sometimes with the view name and sometimes with # the sequence name. Checking this at run time is # unnecessary complex and costly. # # The best solution would certainly be to call "db_nextval" # only with real sequence names (as defined in SQL). In that # case, the whole function would for postgres would collapse # to a single line, without any need for sequence name # caching. But in that case, one should rename the sequences # from t_SEQUENCE to SEQUENCE for postgres. # # However, since Oracle uses the pseudo column ".nextval", # which is emulated via the view, it is not clear, how # feasible this is to remove all such views without breaking # installed applications. We keep for such cases the view, # but nevertheless, the function "db_nextval" should always # be called with names without the "t_" prefix to achieve # Oracle compatibility. if {![info exists ::db::sequences]} { ns_log notice "-- creating per thread sequence table" namespace eval ::db {} foreach s [db_list -dbn $dbn relnames "select relname, relkind from pg_class where relkind = 'S'"] { set ::db::sequences($s) 1 } } if {[info exists ::db::sequences(t_$sequence)]} { #ns_log notice "-- found t_$sequence #ad_log Warning "Deprecated sequence name 't_$sequence' is used. Use instead 't_$sequence'" set nextval [db_string -dbn $dbn nextval "select nextval('t_$sequence')"] } elseif {[info exists ::db::sequences($sequence)]} { #ns_log notice "-- found $sequence" set nextval [db_string -dbn $dbn nextval "select nextval('$sequence')"] if {[string match t_* $sequence]} { ad_log Warning "For portability, db_nextval should be called without the leading 't_' prefix: 't_$sequence'" } } elseif { [db_0or1row -dbn $dbn nextval_sequence " select nextval('${sequence}') as nextval where (select relkind from pg_class where relname = '${sequence}') = 'S' "]} { # # We do not have an according sequence-table. Use the system catalog to check # for the sequence # # ... the query sets nextval if it succeeds # ad_log Warning "Probably deprecated sequence name '$sequence' is used (no sequence table found)" } else { # # Finally, there might be a view with a nextval # ns_log debug "db_nextval: sequence($sequence) is not a real sequence. perhaps it uses the view hack." set nextval [db_string -dbn $dbn nextval "select nextval from $sequence"] ad_log Warning "Using deprecated sequence view hack for '$sequence'. Is there not real sequence?" } return $nextval } oracle - nsodbc - default { return [db_string -dbn $dbn nextval "select $sequence.nextval from dual"] } }XQL Not present: Generic, PostgreSQL, Oracle