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 (required)

the name of an SQL sequence

Returns:

the next value for a sequence. This can utilize a pool of sequence values.

See Also:

• /doc/db-api-detailed

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