+ We define this here in 20-db-bootstrap-procs.tcl rather than
+ acs-tcl/tcl/00-database-procs.tcl, as we also need to call it from
+ db_bootstrap_set_db_type, below, and from db_bootstrap_checks,
+ before all the rest of the db_* api code in 00-database-procs.tcl
+ is sourced.
+
+ @param dbn The database name to use. If empty_string, uses the default database.
+
+ @author Andrew Piskorski (atp@piskorski.com)
+ @creation-date 2003/03/16
+} {
+ if { [empty_string_p $dbn] } {
+ set dbn [nsv_get {db_default_database} .]
+ }
+ return [nsv_get {db_available_pools} $dbn]
+}
+
+
ad_proc db_bootstrap_set_db_type { errors } {
@author Don Baccus (dhogaza@pacifier.com)
@@ -11,6 +34,7 @@
time at the moment to change how the bootstrapper communicates
database problems to the installer.
} {
+ set proc_name {Database API}
# Might as well get el grosso hacko out of the way...
upvar $errors database_problem
@@ -69,22 +93,70 @@
#
set server_name [ns_info server]
- append config_path "ns/server/$server_name/acs/database"
- set the_set [ns_configsection $config_path]
- set pools [list]
+ set config_path "ns/server/$server_name/acs/database"
+ set all_pools [ns_db pools]
- if { [string length $the_set] > 0 } {
- for {set i 0} {$i < [ns_set size $the_set]} {incr i} {
- if { [string tolower [ns_set key $the_set $i]] == "availablepool" } {
- lappend pools [ns_set value $the_set $i]
+ set database_names [ns_config $config_path {database_names}]
+
+ if { [llength $database_names] <= 0 } {
+ # Fall back to old OpenACS 4.6.x pre-multi-db style.
+ set old_availablepool_p 1
+ set default_dbn {default}
+ } else {
+ # The config file is using the new multi-db format.
+ set old_availablepool_p 0
+
+ set default_dbn [lindex $database_names 0]
+ if { [empty_string_p $default_dbn] } {
+ set default_dbn {default}
+ set old_availablepool_p 1
+
+ } else {
+ foreach dbn $database_names {
+ # TODO: For each pool, may want to add a check against
+ # all_pools to ensure that the pool is valid.
+
+ set dbn_pools [ns_config $config_path "pools_${dbn}"]
+ nsv_set {db_available_pools} $dbn $dbn_pools
+ ns_log Notice "$proc_name: For database '$dbn', the following pools are available: $dbn_pools"
}
+
+ if { [empty_string_p [db_available_pools -dbn $default_dbn]] } {
+ ns_log Error "$proc_name: No pools specified for database '$default_dbn'."
+ set old_availablepool_p 1
+ }
}
}
- if { [llength $pools] == 0 } {
- set pools [ns_db pools]
+ nsv_set {db_default_database} . $default_dbn
+ ns_log Notice "$proc_name: Default database (dbn) is: '$default_dbn'"
+
+ if { $old_availablepool_p } {
+ # We ONLY do this as a fallback, if something was wrong with
+ # the newer multi-db config above, or if it was simply missing
+ # entirely: --atp@piskorski.com, 2003/03/17 00:55 EST
+
+ set dbn_pools [list]
+ set the_set [ns_configsection $config_path]
+ if { [string length $the_set] > 0 } {
+ for {set i 0} {$i < [ns_set size $the_set]} {incr i} {
+ if { [string tolower [ns_set key $the_set $i]] == "availablepool" } {
+ lappend dbn_pools [ns_set value $the_set $i]
+ }
+ }
+ }
+
+ nsv_set {db_available_pools} $default_dbn $dbn_pools
}
+ set pools [db_available_pools]
+ if { [llength $pools] <= 0 } {
+ nsv_set {db_available_pools} $default_dbn $all_pools
+ set pools $all_pools
+ ns_log Notice "$proc_name: Using ALL database pools for OpenACS."
+ }
+ ns_log Notice "$proc_name: The following pools are available for OpenACS: $pools"
+
# DRB: if the user hasn't given us enough database pools might as well tell
# them in plain english
@@ -96,8 +168,14 @@
run correctly."
}
- ns_log Notice "Database API: The following pools are available: $pools"
- nsv_set db_available_pools . $pools
+ # We're done with the mult-db dbn stuff, from now on we deal only
+ # with the OpenACS default database pools:
+ #
+ # TODO: For now the below pool-checking code runs ONLY for the
+ # default database. Should probalby extend the checking to all
+ # configured databases:
+ #
+ # --atp@piskorski.com, 2003/03/17 00:53 EST
# DRB: Try to allocate a handle from each pool and determine its database type.
# I wrote this to break after the first allocation failure because a defunct
Index: openacs-4/packages/acs-tcl/tcl/00-database-procs-oracle.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/00-database-procs-oracle.tcl,v
diff -u -r1.19 -r1.20
--- openacs-4/packages/acs-tcl/tcl/00-database-procs-oracle.tcl 12 Feb 2003 16:00:16 -0000 1.19
+++ openacs-4/packages/acs-tcl/tcl/00-database-procs-oracle.tcl 17 Mar 2003 20:18:59 -0000 1.20
@@ -7,6 +7,7 @@
@cvs-id $Id$
}
+
ad_proc -public db_nullify_empty_string { string } {
A convenience function that returns [db_null] if $string is the empty string.
} {
@@ -17,8 +18,9 @@
}
}
-ad_proc -public db_nextval { sequence } {
+ad_proc db_nextval {{ -dbn "" } sequence } {
+
Returns the next value for a sequence. This can utilize a pool of
sequence values.
@@ -31,14 +33,17 @@
@param sequence the name of an sql sequence
- @see /doc/db-api-detailed.html
+ @param dbn The database name to use. If empty_string, uses the default database.
+
+ @see /doc/db-api-detailed.html
} {
- return [db_string "nextval" "select $sequence.nextval from dual"]
+ return [db_string -dbn $dbn "nextval" "select $sequence.nextval from dual"]
}
-ad_proc -public db_exec_plsql { statement_name sql args } {
+ad_proc -public db_exec_plsql {{ -dbn "" } statement_name sql args } {
+
Executes a PL/SQL statement, returning the variable of bind
variable :1.
@@ -112,10 +117,9 @@
Note that this description is oracle specific, because
this api-browser is running under oracle.
+ @param dbn The database name to use. If empty_string, uses the default database.
@see /doc/db-api-detailed.html
-
-
} {
ad_arg_parser { bind_output bind } $args
@@ -126,7 +130,7 @@
return -code error "the -bind_output switch is not currently supported"
}
- db_with_handle db {
+ db_with_handle -dbn $dbn db {
# Right now, use :1 as the output value if it occurs in the statement,
# or not otherwise.
set test_sql [db_qd_replace_sql $full_statement_name $sql]
@@ -138,6 +142,7 @@
}
}
+
ad_proc -private db_exec { type db statement_name pre_sql {ulevel 2} args } {
A helper procedure to execute a SQL statement, potentially binding
@@ -185,12 +190,14 @@
return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error
}
-ad_proc -public db_dml { statement_name sql args } {
+ad_proc -public db_dml {{ -dbn "" } statement_name sql args } {
+
Do a DML statement.
- @see /doc/db-api-detailed.html
+ @param dbn The database name to use. If empty_string, uses the default database.
+ @see /doc/db-api-detailed.html
} {
ad_arg_parser { clobs blobs clob_files blob_files bind } $args
@@ -225,7 +232,8 @@
if { $lob_argc > 1 } {
error "Only one of -clobs, -blobs, -clob_files, or -blob_files may be specified as an argument to db_dml"
}
- db_with_handle db {
+
+ db_with_handle -dbn $dbn db {
if { $lob_argc == 1 } {
# Bind :1, :2, ..., :n as LOBs (where n = [llength $lob_argv])
set bind_vars [list]
@@ -239,41 +247,48 @@
}
}
-proc_doc db_resultrows {} { Returns the number of rows affected by the last DML command. } {
- global db_state
+
+ad_proc db_resultrows {{ -dbn "" }} {
+ Returns the number of rows affected by the last DML command.
+
+ @param dbn The database name to use. If empty_string, uses the default database.
+} {
+ upvar "#0" [db_state_array_name_is -dbn $dbn] db_state
return [ns_ora resultrows $db_state(last_used)]
}
-ad_proc db_write_clob { statement_name sql args } {
- ad_arg_parser { bind } $args
+ad_proc db_write_clob {{ -dbn "" } statement_name sql args } {
+ @param dbn The database name to use. If empty_string, uses the default database.
+} {
+ ad_arg_parser { bind } $args
set full_statement_name [db_qd_get_fullname $statement_name]
-
- db_with_handle db {
+ db_with_handle -dbn $dbn db {
db_exec write_clob $db $full_statement_name $sql
}
}
-ad_proc db_write_blob { statement_name sql args } {
+ad_proc db_write_blob {{ -dbn "" } statement_name sql args } {
+ @param dbn The database name to use. If empty_string, uses the default database.
+} {
ad_arg_parser { bind } $args
-
set full_statement_name [db_qd_get_fullname $statement_name]
-
- db_with_handle db {
+ db_with_handle -dbn $dbn db {
db_exec_lob write_blob $db $full_statement_name $sql
}
}
-ad_proc db_blob_get_file { statement_name sql args } {
+ad_proc db_blob_get_file {{ -dbn "" } statement_name sql args } {
+ @param dbn The database name to use. If empty_string, uses the default database.
+} {
ad_arg_parser { bind file args } $args
-
set full_statement_name [db_qd_get_fullname $statement_name]
-
- db_with_handle db {
+ db_with_handle -dbn $dbn db {
eval [list db_exec_lob blob_get_file $db $full_statement_name $sql 2 $file] $args
}
}
+
ad_proc -private db_exec_lob { type db statement_name pre_sql {ulevel 2} args } {
A helper procedure to execute a SQL statement, potentially binding
@@ -372,13 +387,14 @@
return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error
}
-ad_proc db_get_sql_user { } {
+ad_proc db_get_sql_user {{ -dbn "" }} {
+
Returns a valid user@database/password string to access a database through sqlplus.
+ @param dbn The database name to use. If empty_string, uses the default database.
} {
-
- set pool [lindex [nsv_get db_available_pools .] 0]
+ set pool [lindex [db_available_pools -dbn $dbn] 0]
set datasource [ns_config ns/db/pool/$pool DataSource]
if { ![empty_string_p $datasource] && ![string is space $datasource] } {
return "[ns_config ns/db/pool/$pool User]/[ns_config ns/db/pool/$pool Password]@$datasource"
@@ -387,14 +403,17 @@
}
}
-ad_proc db_source_sql_file { {-callback apm_ns_write_callback} file } {
+ad_proc db_source_sql_file {{
+ -dbn ""
+ -callback apm_ns_write_callback
+} file } {
Sources a SQL file (in SQL*Plus format).
+ @param dbn The database name to use. If empty_string, uses the default database.
} {
-
global env
- set user_pass [db_get_sql_user]
+ set user_pass [db_get_sql_user -dbn $dbn]
cd [file dirname $file]
set fp [open "|[file join $env(ORACLE_HOME) bin sqlplus] $user_pass @$file" "r"]
@@ -408,17 +427,18 @@
}
-ad_proc db_source_sqlj_file { {-callback apm_ns_write_callback} file } {
-
+ad_proc db_source_sqlj_file {{
+ -dbn ""
+ -callback apm_ns_write_callback
+} file } {
Sources a SQLJ file using loadjava.
+ @param dbn The database name to use. If empty_string, uses the default database.
} {
-
global env
- set user_pass [db_get_sql_user]
+ set user_pass [db_get_sql_user -dbn $dbn]
set fp [open "|[file join $env(ORACLE_HOME) bin loadjava] -verbose -user $user_pass $file" "r"]
-
# Despite the fact that this works, the text does not get written to the stream.
# The output is generated as an error when you attempt to close the input stream as
# done below.
@@ -435,27 +455,33 @@
}
}
-ad_proc -public db_tables { -pattern } {
+
+ad_proc -public db_tables {
+ -pattern
+ {-dbn ""}
+} {
Returns a Tcl list of all the tables owned by the connected user.
@param pattern Will be used as LIKE 'pattern%' to limit the number of tables returned.
+ @param dbn The database name to use. If empty_string, uses the default database.
+
@author Lars Pind lars@pinds.com
@change-log yon@arsdigita.com 20000711 changed to return lower case table names
} {
set tables [list]
-
+
if { [info exists pattern] } {
- db_foreach table_names_with_pattern {
+ db_foreach -dbn $dbn table_names_with_pattern {
select lower(table_name) as table_name
from user_tables
where table_name like upper(:pattern)
} {
lappend tables $table_name
}
} else {
- db_foreach table_names_without_pattern {
+ db_foreach -dbn $dbn table_names_without_pattern {
select lower(table_name) as table_name
from user_tables
} {
@@ -466,26 +492,31 @@
}
-ad_proc -public db_table_exists { table_name } {
+ad_proc -public db_table_exists {{ -dbn "" } table_name } {
Returns 1 if a table with the specified name exists in the database, otherwise 0.
-
+
+ @param dbn The database name to use. If empty_string, uses the default database.
+
@author Lars Pind (lars@pinds.com)
} {
- set n_rows [db_string table_count {
+ set n_rows [db_string -dbn $dbn table_count {
select count(*) from user_tables where table_name = upper(:table_name)
}]
return $n_rows
}
-ad_proc -public db_columns { table_name } {
+
+ad_proc -public db_columns {{ -dbn "" } table_name } {
Returns a Tcl list of all the columns in the table with the given name.
-
+
+ @param dbn The database name to use. If empty_string, uses the default database.
+
@author Lars Pind lars@pinds.com
@change-log yon@arsdigita.com 20000711 changed to return lower case column names
} {
set columns [list]
- db_foreach table_column_names {
+ db_foreach -dbn $dbn table_column_names {
select lower(column_name) as column_name
from user_tab_columns
where table_name = upper(:table_name)
@@ -496,13 +527,15 @@
}
-ad_proc -public db_column_exists { table_name column_name } {
+ad_proc -public db_column_exists {{ -dbn "" } table_name column_name } {
Returns 1 if the row exists in the table, 0 if not.
+
+ @param dbn The database name to use. If empty_string, uses the default database.
@author Lars Pind lars@pinds.com
} {
set columns [list]
- set n_rows [db_string column_exists {
+ set n_rows [db_string -dbn $dbn column_exists {
select count(*)
from user_tab_columns
where table_name = upper(:table_name)
@@ -512,11 +545,13 @@
}
-ad_proc -public db_column_type { table_name column_name } {
+ad_proc -public db_column_type {{ -dbn "" } table_name column_name } {
Returns the Oracle Data Type for the specified column.
Returns -1 if the table or column doesn't exist.
+ @param dbn The database name to use. If empty_string, uses the default database.
+
@author Yon Feldman (yon@arsdigita.com)
@change-log 10 July, 2000: changed to return error
@@ -530,33 +565,32 @@
(yon@arsdigita.com)
} {
-
- return [db_string column_type_select "
+ return [db_string -dbn $dbn column_type_select "
select data_type as data_type
from user_tab_columns
where upper(table_name) = upper(:table_name)
and upper(column_name) = upper(:column_name)
" -default "-1"]
-
}
-ad_proc -public ad_column_type { table_name column_name } {
+ad_proc -public ad_column_type {{ -dbn "" } table_name column_name } {
+
Returns 'numeric' for number type columns, 'text' otherwise
Throws an error if no such column exists.
+ @param dbn The database name to use. If empty_string, uses the default database.
+
@author Yon Feldman (yon@arsdigita.com)
} {
+ set column_type [db_column_type -dbn $dbn $table_name $column_name]
- set column_type [db_column_type $table_name $column_name]
-
if { $column_type == -1 } {
return "Either table $table_name doesn't exist or column $column_name doesn't exist"
} elseif { [string compare $column_type "NUMBER"] } {
return "numeric"
} else {
return "text"
}
-
}
Index: openacs-4/packages/acs-tcl/tcl/00-database-procs-postgresql.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/00-database-procs-postgresql.tcl,v
diff -u -r1.40 -r1.41
--- openacs-4/packages/acs-tcl/tcl/00-database-procs-postgresql.tcl 15 Feb 2003 23:55:59 -0000 1.40
+++ openacs-4/packages/acs-tcl/tcl/00-database-procs-postgresql.tcl 17 Mar 2003 20:18:59 -0000 1.41
@@ -7,8 +7,9 @@
@cvs-id $Id$
}
-ad_proc -public db_nextval { sequence } {
+ad_proc db_nextval {{ -dbn "" } sequence } {
+
Returns the next value for a sequence. This can utilize a pool
of sequence values to save hits to the database.
@@ -22,24 +23,27 @@
@param sequence the name of an sql sequence
+ @param dbn The database name to use. If empty_string, uses the default database.
+
} {
# the following query will return a nextval if the sequnce
# is of relkind = 'S' (a sequnce). if it is not of relkind = 'S'
# we will try querying it as a view
- if {[db_0or1row nextval_sequence "select nextval('${sequence}') as nextval
+ 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 nextval_view "select nextval from ${sequence}"
+ db_0or1row -dbn $dbn nextval_view "select nextval from ${sequence}"
return $nextval
}
}
-ad_proc -public db_exec_plsql { statement_name sql args } {
+ad_proc -public db_exec_plsql {{ -dbn "" } statement_name sql args } {
+
Perform a pl/pgsql function or procedure call.
@@ -107,6 +111,7 @@ Note that this description is postgresql specific, because this api-browser is running under postgresql. + @param dbn The database name to use. If empty_string, uses the default database. @see /doc/db-api-detailed.html @@ -131,7 +136,7 @@ return -code error "the -bind_output switch is not currently supported" } - db_with_handle db { + db_with_handle -dbn $dbn db { # plsql calls that are simple selects bypass the plpgsql # mechanism for creating anonymous functions (OpenACS - Dan). # if a table is being created, we need to bypass things, too (OpenACS - Ben). @@ -152,25 +157,26 @@ } } -# emulation of plsql calls from oracle. This routine takes the plsql -# statements and wraps them in a function call, calls the function, and then -# drops the function. Future work might involve converting this to cache the -# function calls ad_proc -private db_exec_plpgsql { db statement_name pre_sql fname } { - A helper procedure to execute a SQL statement, potentially binding depending on the value of the $bind variable in the calling environment (if set). +
Low level replacement for db_exec which replaces inline code with a proc. db proc is dropped after execution. This is a temporary fix until we can port all of the db_exec_plsql calls to simple selects of the inline code wrapped in function calls.
+ emulation of plsql calls from oracle. This routine takes the plsql + statements and wraps them in a function call, calls the function, and then + drops the function. Future work might involve converting this to cache the + function calls +
This proc is private - use db_exec_plsql instead! @see db_exec_plsql @@ -301,6 +307,7 @@ return $lsql } + ad_proc -private db_exec { type db statement_name pre_sql {ulevel 2} } { A helper procedure to execute a SQL statement, potentially binding @@ -397,51 +404,57 @@ } } -proc_doc db_resultrows {} { Returns the number of rows affected by the last DML command. } { - global db_state + +ad_proc db_resultrows {{ -dbn "" }} { + Returns the number of rows affected by the last DML command. + + @param dbn The database name to use. If empty_string, uses the default database. +} { + upvar "#0" [db_state_array_name_is -dbn $dbn] db_state + return [ns_pg ntuples $db_state(last_used)] } -ad_proc db_write_clob { statement_name sql args } { - ad_arg_parser { bind } $args - db_with_handle db { +ad_proc db_write_clob {{ -dbn "" } statement_name sql args } { + @param dbn The database name to use. If empty_string, uses the default database. +} { + ad_arg_parser { bind } $args + db_with_handle -dbn $dbn db { db_exec write_clob $db $statement_name $sql } } -ad_proc db_blob_get { statement_name sql args } { +ad_proc db_blob_get {{ -dbn "" } statement_name sql args } { ad_arg_parser { bind } $args - set full_statement_name [db_qd_get_fullname $statement_name] - - db_with_handle db { + db_with_handle -dbn $dbn db { set data [db_exec_lob blob_get $db $full_statement_name $sql] } - return $data } -ad_proc db_write_blob { statement_name sql args } { +ad_proc db_write_blob {{ -dbn "" } statement_name sql args } { + @param dbn The database name to use. If empty_string, uses the default database. +} { ad_arg_parser { bind } $args - set full_statement_name [db_qd_get_fullname $statement_name] - - db_with_handle db { + db_with_handle -dbn $dbn db { db_exec_lob write_blob $db $full_statement_name $sql } } -ad_proc db_blob_get_file { statement_name sql args } { +ad_proc db_blob_get_file {{ -dbn "" } statement_name sql args } { + @param dbn The database name to use. If empty_string, uses the default database. +} { ad_arg_parser { bind file args } $args - set full_statement_name [db_qd_get_fullname $statement_name] - - db_with_handle db { + db_with_handle -dbn $dbn db { db_exec_lob blob_select_file $db $full_statement_name $sql $file } } + ad_proc -private db_exec_lob { type db statement_name pre_sql { file "" } } { A helper procedure to execute a SQL statement, potentially binding @@ -646,47 +659,45 @@ return -code $errno -errorinfo $errinfo -errorcode $errcode $error } -ad_proc db_get_pgbin { } { +ad_proc db_get_pgbin {{ -dbn "" }} { Returns the pgbin parameter from the driver section of the first database pool. + @param dbn The database name to use. If empty_string, uses the default database. } { - - set pool [lindex [nsv_get db_available_pools .] 0] + set pool [lindex [db_available_pools -dbn $dbn] 0] set driver [ns_config ns/db/pool/$pool Driver] return [ns_config ns/db/driver/$driver pgbin] } -ad_proc db_get_username { } { - +ad_proc db_get_username {{ -dbn "" }} { Returns the username parameter from the driver section of the first database pool. + @param dbn The database name to use. If empty_string, uses the default database. } { - - set pool [lindex [nsv_get db_available_pools .] 0] + set pool [lindex [db_available_pools -dbn $dbn] 0] return [ns_config ns/db/pool/$pool User] } -ad_proc db_get_password { } { - +ad_proc db_get_password {{ -dbn "" }} { Returns the username parameter from the driver section of the first database pool. + @param dbn The database name to use. If empty_string, uses the default database. } { - - set pool [lindex [nsv_get db_available_pools .] 0] + set pool [lindex [db_available_pools -dbn $dbn] 0] return [ns_config ns/db/pool/$pool Password] } -ad_proc db_get_port { } { +ad_proc db_get_port {{ -dbn "" }} { Returns the port number from the first database pool. It assumes the datasource is properly formatted since we've already verified that we can connect to the pool. It returns an empty string for an empty port value. + @param dbn The database name to use. If empty_string, uses the default database. } { - - set pool [lindex [nsv_get db_available_pools .] 0] + set pool [lindex [db_available_pools -dbn $dbn] 0] set datasource [ns_config ns/db/pool/$pool DataSource] set last_colon_pos [string last ":" $datasource] if { $last_colon_pos == -1 } { @@ -703,15 +714,15 @@ return [string range $datasource [expr $first_colon_pos + 1] [expr $last_colon_pos - 1] ] } -ad_proc db_get_database { } { +ad_proc db_get_database {{ -dbn "" }} { Returns the database name from the first database pool. It assumes the datasource is properly formatted since we've already verified that we can connect to the pool. + @param dbn The database name to use. If empty_string, uses the default database. } { - - set pool [lindex [nsv_get db_available_pools .] 0] + set pool [lindex [db_available_pools -dbn $dbn] 0] set datasource [ns_config ns/db/pool/$pool DataSource] set last_colon_pos [string last ":" $datasource] if { $last_colon_pos == -1 } { @@ -721,15 +732,15 @@ return [string range $datasource [expr $last_colon_pos + 1] end] } -ad_proc db_get_dbhost { } { +ad_proc db_get_dbhost {{ -dbn "" }} { Returns the name of the database host from the first database pool. It assumes the datasource is properly formatted since we've already verified that we can connect to the pool. + @param dbn The database name to use. If empty_string, uses the default database. } { - - set pool [lindex [nsv_get db_available_pools .] 0] + set pool [lindex [db_available_pools -dbn $dbn] 0] set datasource [ns_config ns/db/pool/$pool DataSource] set first_colon_pos [string first ":" $datasource] if { $first_colon_pos == -1 } { @@ -739,6 +750,7 @@ return [string range $datasource 0 [expr $first_colon_pos - 1]] } + ad_proc db_source_sql_file { {-callback apm_ns_write_callback} file } { Sources a SQL file (in psql format). @@ -819,18 +831,24 @@ } } -ad_proc -public db_tables { -pattern } { + +ad_proc -public db_tables { + -pattern + {-dbn ""} +} { Returns a Tcl list of all the tables owned by the connected user. @param pattern Will be used as LIKE 'pattern%' to limit the number of tables returned. + @param dbn The database name to use. If empty_string, uses the default database. + @author Don Baccus (dhogaza@pacifier.com) } { set tables [list] if { [info exists pattern] } { - db_foreach table_names_with_pattern { + db_foreach -dbn $dbn table_names_with_pattern { select relname from pg_class where relname like lower(:pattern) and @@ -839,7 +857,7 @@ lappend tables $relname } } else { - db_foreach table_names_without_pattern { + db_foreach -dbn $dbn table_names_without_pattern { select relname from pg_class where relname !~ '^pg_' and relkind = 'r' @@ -850,29 +868,34 @@ return $tables } -ad_proc -public db_table_exists { table_name } { + +ad_proc -public db_table_exists {{ -dbn "" } table_name } { Returns 1 if a table with the specified name exists in the database, otherwise 0. + @param dbn The database name to use. If empty_string, uses the default database. + @author Don Baccus (dhogaza@pacifier.com) - } { - set n_rows [db_string table_count { + set n_rows [db_string -dbn $dbn table_count { select count(*) from pg_class where relname = lower(:table_name) and relname !~ '^pg_' and relkind = 'r' }] return $n_rows } -ad_proc -public db_columns { table_name } { + +ad_proc -public db_columns {{ -dbn "" } table_name } { Returns a Tcl list of all the columns in the table with the given name. - + + @param dbn The database name to use. If empty_string, uses the default database. + @author Lars Pind lars@pinds.com @change-log yon@arsdigita.com 20000711 changed to return lower case column names } { set columns [list] - db_foreach table_column_names { + db_foreach -dbn $dbn table_column_names { select lower(column_name) as column_name from user_tab_columns where table_name = upper(:table_name) @@ -883,13 +906,15 @@ } -ad_proc -public db_column_exists { table_name column_name } { +ad_proc -public db_column_exists {{ -dbn "" } table_name column_name } { Returns 1 if the row exists in the table, 0 if not. - + + @param dbn The database name to use. If empty_string, uses the default database. + @author Lars Pind lars@pinds.com } { set columns [list] - set n_rows [db_string column_exists { + set n_rows [db_string -dbn $dbn column_exists { select count(*) from user_tab_columns where table_name = upper(:table_name) @@ -899,11 +924,13 @@ } -ad_proc -public db_column_type { table_name column_name } { +ad_proc -public db_column_type {{ -dbn "" } table_name column_name } { Returns the Oracle Data Type for the specified column. Returns -1 if the table or column doesn't exist. + @param dbn The database name to use. If empty_string, uses the default database. + @author Yon Feldman (yon@arsdigita.com) @change-log 10 July, 2000: changed to return error @@ -917,27 +944,26 @@ (yon@arsdigita.com) } { - - return [db_string column_type_select " + return [db_string -dbn $dbn column_type_select " select data_type as data_type from user_tab_columns where upper(table_name) = upper(:table_name) and upper(column_name) = upper(:column_name) " -default "-1"] - } -ad_proc -public ad_column_type { table_name column_name } { +ad_proc -public ad_column_type {{ -dbn "" } table_name column_name } { + Returns 'numeric' for number type columns, 'text' otherwise Throws an error if no such column exists. - @author Yon Feldman (yon@arsdigita.com) + @param dbn The database name to use. If empty_string, uses the default database. + @author Yon Feldman (yon@arsdigita.com) } { + set column_type [db_column_type -dbn $dbn $table_name $column_name] - set column_type [db_column_type $table_name $column_name] - if { $column_type == -1 } { return "Either table $table_name doesn't exist or column $column_name doesn't exist" } elseif { [string compare $column_type "NUMBER"] } { Index: openacs-4/packages/acs-tcl/tcl/00-database-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/00-database-procs.tcl,v diff -u -r1.24 -r1.25 --- openacs-4/packages/acs-tcl/tcl/00-database-procs.tcl 10 Mar 2003 19:19:14 -0000 1.24 +++ openacs-4/packages/acs-tcl/tcl/00-database-procs.tcl 17 Mar 2003 20:18:59 -0000 1.25 @@ -7,10 +7,159 @@ @cvs-id $Id$ } + +# TODO: The multi-db work below is incomplete. Using the default +# database should work the same as it always has in OpenACS, but using +# the new -dbn switch will give errors in many cases. See below under +# BUGS: --atp@piskorski.com, 2003/03/17 14:27 EST + +# As originally released in (at least) ACS 4.2 through OpenACS 4.6, +# this DB API supported only a single, default database. You could +# define any number of different database drivers and pools in +# AOLserver, but could only use ONE database here. +# +# I have eliminated this restriction. Now, in OpenACS 4.7 and later, +# to access a non-default database, simply pas the optional -dbn +# (Database Name) switch to any of the DB API procs which support it. +# +# BUGS, and Other Limitations and Caveats: +# +# - Currently, using the -dbn switch to access any database which +# uses a different driver than the default database will fail, because +# there are separte Oracle and PostgreSQL implementations of db_exec, +# and db_exec is used for basica stuff like db_string, etc. Argh. +# --atp@piskorski.com, 2003/03/17 14:27 EST +# +# Note that -dbn specifies a "Database Name", NOT a database pool! +# +# I could have provided access to secondary databases via a -pool +# rather than a -dbn switch, but chose not to, as the existing DB API +# already had the nicely general feature that if you try to do nested +# queries, the DB API will transparently grab a second database handle +# from another pool to make it work. You can nest your queries as +# many levels deep as you have database pools defined for that +# database. So, the existing API essentially already supported the +# notion of "binning" database pools into logical "databases", it just +# didn't provide any way to define more than the single, default +# database! Thus I chose to preserve this "binning" by specifying +# databases via the -dbn switch rather than database pools via a -pool +# switch. +# +# To define what databases exist, and what pools belong to what +# databases, you need to put something like this in your nsd.tcl +# AOLserver config file: +# +# ns_section ns/server/$server_name/acs/database +# ns_param databases [list ora pg foo] +# ns_param pools_ora [list main subquery log] +# ns_param pools_pg [list pg-main pg-subquery pg-log] +# ns_param pools_foo [list foo1 foo2] +# + +# Note that the FIRST database listed in the databases parameter - in +# this case 'ora' - becomes the default database, used for all normal +# OpenACS transactions! +# +# Note also that since each database is just a logical bucket for +# holding database pools, you are free to do weird stuff like assign +# the same pool to more than one database, or define more than one +# logical database for a single real, physical database. +# +# --atp@piskorski.com, 2003/03/16 21:30 EST + + +# We now use the following global variables: +# +# Server-Global variables: +# One nsv array: +# db_default_database . +# One nsv array per Database Name: +# db_available_pools $dbn +# db_driver_type_is $dbn +# +# Thread-Global variables: +# One Tcl array per Database Name: +# db_state_${dbn} +# +# The db_available_pools and db_state arrays are used in exactly the +# same manner as they were before, except that in the original DB API +# we had only one of each array total, while now we have one of each +# array per database. +# +# We use the procs db_state_array_name_is, db_available_pools, and +# db_driver_type_is to help keep track of these different arrays. +# Note that most code should now NEVER read from any of the +# db_available_pools nsvs listed above, but should instead use the +# proc db_available_pools provided for that purpose. +# +# The original implementation comments on the use of these global +# variables are below: +# +# --atp@piskorski.com, 2003/03/16 21:30 EST + + +# Old comments, from before adding the above multi-db support: +# --atp@piskorski.com, 2003/03/16 21:31 EST + +# NSV db_pooled_sequences($sequence) is the number of sequence values for the +# sequence named $sequence that should be pooled. +# NSV db_pooled_nextvals($sequence) is a list of available sequence values for +# the sequence named $sequence. It is a ring buffer (values are added to the +# end and popped from the beginning). +# NSV db_pooled_nextvals(.mutex) is a mutex guarding the db_pooled_nextvals. + +# global db_state(handles) is a list of handles that have been allocated. +# +# global db_state(n_handles_used) is the number of handles in this list that are +# presently in use. +# +# E.g.: +# +# db_foreach statement_name "select ..." { +# # $db_state(handles) is "nsdb1"; $db_state(n_handles_used) is 1 +# db_foreach statement_name "select ..." { +# # $db_state(handles) is "nsdb1 nsdb2"; $db_state(n_handles_used) is 2 +# } +# # $db_state(handles) is "nsdb1 nsdb2"; $db_state(n_handles_used) is 1 +# db_release_unused_handles +# # $db_state(handles) is "nsdb1"; $db_state(n_handles_used) is 1 +# } +# # $db_state(handles) is "nsdb1"; $db_state(n_handles_used) is 0 +# db_release_unused_handles +# # $db_state(handles) is ""; $db_state(n_handles_used) is 0 +# +# The list of available pools are stored in the nsv db_available_pools(.) = { pool1 pool2 pool3 } +# +# This list is defined in the [ns/server/yourserver/acs/database] section using the key +# AvailablePool=foo (one line per pool). +# +# If none are specified, it defaults to all the pools available to AOLserver. + + +ad_proc -private db_state_array_name_is {{ -dbn "" }} { + Returns the name of the global db_state array for the given + database name. + + @param dbn The database name to use. If empty_string, uses the + default database. + + @author Andrew Piskorski (atp@piskorski.com) + @creation-date 2003/03/16 +} { + if { [empty_string_p $dbn] } { + set dbn [nsv_get {db_default_database} .] + } + return "db_state_${dbn}" +} + + proc_doc db_type { } { Returns the RDBMS type (i.e. oracle, postgresql) this OpenACS installation is using. The nsv ad_database_type is set up during the bootstrap process. } { + # Currently this should always be either "oracle" or "postgresql": + # --atp@piskorski.com, 2003/03/16 22:01 EST + return [nsv_get ad_database_type .] } @@ -90,10 +239,14 @@ return $result } -proc_doc db_nth_pool_name { n } { + +ad_proc db_nth_pool_name {{ -dbn "" } n } { Returns the name of the pool used for the nth-nested selection (0-relative). + + @param dbn The database name to use. If empty_string, uses the default database. } { - set available_pools [nsv_get db_available_pools .] + set available_pools [db_available_pools -dbn $dbn] + if { $n < [llength $available_pools] } { set pool [lindex $available_pools $n] } else { @@ -102,15 +255,16 @@ return $pool } -proc_doc db_with_handle { db code_block } { -Places a usable database handle in $db and executes $code_block. +ad_proc db_with_handle {{ -dbn "" } db code_block } { + Places a usable database handle in $db and executes $code_block. + + @param dbn The database name to use. If empty_string, uses the default database. } { upvar 1 $db dbh + upvar "#0" [db_state_array_name_is -dbn $dbn] db_state - global db_state - # Initialize bookkeeping variables. if { ![info exists db_state(handles)] } { set db_state(handles) [list] @@ -119,7 +273,7 @@ set db_state(n_handles_used) 0 } if { $db_state(n_handles_used) >= [llength $db_state(handles)] } { - set pool [db_nth_pool_name $db_state(n_handles_used)] + set pool [db_nth_pool_name -dbn $dbn $db_state(n_handles_used)] set start_time [clock clicks] set errno [catch { set db [ns_db gethandle $pool] @@ -168,12 +322,14 @@ } } -proc_doc db_release_unused_handles {} { +ad_proc db_release_unused_handles {{ -dbn "" }} { + Releases any database handles that are presently unused. + @param dbn The database name to use. If empty_string, uses the default database. } { - global db_state + upvar "#0" [db_state_array_name_is -dbn $dbn] db_state if { [info exists db_state(n_handles_used)] } { # Examine the elements at the end of db_state(handles), killing off @@ -198,6 +354,7 @@ } } + ad_proc -private db_getrow { db selection } { A helper procedure to perform an ns_db getrow, invoking developer support @@ -214,19 +371,22 @@ return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error } -proc_doc db_string { statement_name sql args } { + +ad_proc db_string {{ -dbn "" } statement_name sql args } { + Usage: db_string statement-name sql [ -default default ] [ -bind bind_set_id | -bind bind_value_list ]
Returns the first column of the result of the SQL query $sql. If the query doesn't return a row, returns $default (or raises an error if no $default is provided). + @param dbn The database name to use. If empty_string, uses the default database. } { # Query Dispatcher (OpenACS - ben) set full_name [db_qd_get_fullname $statement_name] ad_arg_parser { default bind } $args - db_with_handle db { + db_with_handle -dbn $dbn db { set selection [db_exec 0or1row $db $full_name $sql] } @@ -239,20 +399,23 @@ return [ns_set value $selection 0] } -proc_doc db_list { statement_name sql args } { + +ad_proc db_list {{ -dbn "" } statement_name sql args } { + Usage: db_list statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
Returns a Tcl list of the values in the first column of the result of SQL query sql. If sql doesn't return any rows, returns an empty list. Analogous to database_to_tcl_list. - + + @param dbn The database name to use. If empty_string, uses the default database. } { ad_arg_parser { bind } $args # Query Dispatcher (OpenACS - SDW) set full_statement_name [db_qd_get_fullname $statement_name] # Can't use db_foreach here, since we need to use the ns_set directly. - db_with_handle db { + db_with_handle -dbn $dbn db { set selection [db_exec select $db $full_statement_name $sql] set result [list] while { [db_getrow $db $selection] } { @@ -262,22 +425,25 @@ return $result } -proc_doc db_list_of_lists { statement_name sql args } { + +ad_proc db_list_of_lists {{ -dbn "" } statement_name sql args } { + Usage: db_list_of_lists statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
Returns a Tcl list, each element of which is a list of all column values in a row of the result of the SQL querysql. If sql doesn't return any rows, returns an empty list. Analogous to database_to_tcl_list_list. + @param dbn The database name to use. If empty_string, uses the default database. } { ad_arg_parser { bind } $args # Query Dispatcher (OpenACS - SDW) set full_statement_name [db_qd_get_fullname $statement_name] # Can't use db_foreach here, since we need to use the ns_set directly. - db_with_handle db { + db_with_handle -dbn $dbn db { set selection [db_exec select $db $full_statement_name $sql] set result [list] @@ -293,7 +459,8 @@ return $result } -ad_proc -public db_list_of_ns_sets { + +ad_proc -public db_list_of_ns_sets {{ -dbn "" } statement_name sql {args ""} @@ -308,12 +475,14 @@ @param args Any additional arguments. @return list of ns_sets, one per each row return by the SQL query + + @param dbn The database name to use. If empty_string, uses the default database. } { ad_arg_parser { bind } $args set full_statement_name [db_qd_get_fullname $statement_name] - db_with_handle db { + db_with_handle -dbn $dbn db { set result [list] set selection [db_exec select $db $full_statement_name $sql] @@ -325,7 +494,9 @@ return $result } -proc_doc db_foreach { statement_name sql args } { + +ad_proc db_foreach {{ -dbn "" } statement_name sql args } { + Usage:
db_foreach statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \ @@ -349,6 +520,7 @@ ns_write "<li>No greebles!\n" }+ @param dbn The database name to use. If empty_string, uses the default database. } { # Query Dispatcher (OpenACS - ben) set full_statement_name [db_qd_get_fullname $statement_name] @@ -383,7 +555,7 @@ upvar 1 $column_set selection } - db_with_handle db { + db_with_handle -dbn $dbn db { set selection [db_exec select $db $full_statement_name $sql] set counter 0 @@ -439,16 +611,20 @@ } } + ad_proc -public db_multirow { -local:boolean -append:boolean {-upvar_level 1} {-extend {}} + {-dbn ""} var_name statement_name sql args } { + @param dbn The database name to use. If empty_string, uses the default database. + Usage:
db_multirow [ -local ] [ -upvar_level n_levels_up ] [ -append ] [ -extend column_list ] \ @@ -564,7 +740,7 @@ set counter 0 } - db_with_handle db { + db_with_handle -dbn $dbn db { set selection [db_exec select $db $full_statement_name $sql] set local_counter 0 @@ -661,7 +837,8 @@ } -ad_proc db_0or1row { statement_name sql args } { +ad_proc db_0or1row {{ -dbn "" } statement_name sql args } { + Usage:db_0or1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \ @@ -674,6 +851,7 @@ or column_set is specified) and returns 1. If no rows are returned, returns 0. If more than one row is returned, throws an error. + @param dbn The database name to use. If empty_string, uses the default database. } { ad_arg_parser { bind column_array column_set } $args @@ -695,7 +873,7 @@ upvar 1 $column_set selection } - db_with_handle db { + db_with_handle -dbn $dbn db { set selection [db_exec 0or1row $db $full_statement_name $sql] } @@ -717,6 +895,7 @@ return 1 } + ad_proc db_1row { args } { Usage:@@ -730,14 +909,15 @@ or column_set is specified). If no rows are returned, throws an error. + @param dbn The database name to use. If empty_string, uses the default database. } { if { ![uplevel db_0or1row $args] } { return -code error "Query did not return any rows." } } -ad_proc db_transaction { transaction_code args } { +ad_proc db_transaction {{ -dbn ""} transaction_code args } { Usage: db_transaction transaction_code [ on_error { error_code_block } ] Executes transaction_code with transactional semantics. This means that either all of the database commands @@ -772,9 +952,9 @@ } + @param dbn The database name to use. If empty_string, uses the default database. } { - - global db_state + upvar "#0" [db_state_array_name_is -dbn $dbn] db_state set syn_err "db_transaction: Invalid arguments. Use db_transaction { code } \[on_error { error_code_block }\] " set arg_c [llength $args] @@ -795,7 +975,7 @@ # Make the error message and database handle available to the on_error block. upvar errmsg errmsg - db_with_handle db { + db_with_handle -dbn $dbn db { # Preserve the handle, since db_with_handle kills it after executing # this block. set dbh $db @@ -825,7 +1005,7 @@ 3 { # TCL_BREAK - Abort the transaction and do the break. ns_db dml $dbh "abort transaction" - db_release_unused_handles + db_release_unused_handles -dbn $dbn break } 4 { @@ -942,23 +1122,29 @@ } -ad_proc db_abort_transaction {} { +ad_proc db_abort_transaction {{ -dbn "" }} { Aborts all levels of a transaction. That is if this is called within several nested transactions, all of them are terminated. Use this instead of db_dml "abort" "abort transaction". + + @param dbn The database name to use. If empty_string, uses the default database. } { - global db_state - db_with_handle db { + upvar "#0" [db_state_array_name_is -dbn $dbn] db_state + + db_with_handle -dbn $dbn db { # We set the abort flag to true. set db_state(db_abort_p,$db) 1 } } -ad_proc db_abort_transaction_p {} { } { - global db_state - db_with_handle db { +ad_proc db_abort_transaction_p {{ -dbn "" }} { + @param dbn The database name to use. If empty_string, uses the default database. +} { + upvar "#0" [db_state_array_name_is -dbn $dbn] db_state + + db_with_handle -dbn $dbn db { if { [info exists db_state(db_abort_p,$db)] } { return $db_state(db_abort_p,$db) } else { @@ -968,48 +1154,15 @@ } } -ad_proc -public db_name { } { +ad_proc -public db_name {{ -dbn "" }} { + Returns the name of the database as reported by the driver. + @param dbn The database name to use. If empty_string, uses the default database. } { - db_with_handle db { + db_with_handle -dbn $dbn db { set dbtype [ns_db dbtype $db] } return $dbtype } - -# NSV db_pooled_sequences($sequence) is the number of sequence values for the -# sequence named $sequence that should be pooled. -# NSV db_pooled_nextvals($sequence) is a list of available sequence values for -# the sequence named $sequence. It is a ring buffer (values are added to the -# end and popped from the beginning). -# NSV db_pooled_nextvals(.mutex) is a mutex guarding the db_pooled_nextvals. - -# global db_state(handles) is a list of handles that have been allocated. -# -# global db_state(n_handles_used) is the number of handles in this list that are -# presently in use. -# -# E.g.: -# -# db_foreach statement_name "select ..." { -# # $db_state(handles) is "nsdb1"; $db_state(n_handles_used) is 1 -# db_foreach statement_name "select ..." { -# # $db_state(handles) is "nsdb1 nsdb2"; $db_state(n_handles_used) is 2 -# } -# # $db_state(handles) is "nsdb1 nsdb2"; $db_state(n_handles_used) is 1 -# db_release_unused_handles -# # $db_state(handles) is "nsdb1"; $db_state(n_handles_used) is 1 -# } -# # $db_state(handles) is "nsdb1"; $db_state(n_handles_used) is 0 -# db_release_unused_handles -# # $db_state(handles) is ""; $db_state(n_handles_used) is 0 -# -# The list of available pools are stored in the nsv db_available_pools(.) = { pool1 pool2 pool3 } -# -# This list is defined in the [ns/server/yourserver/acs/database] section using the key -# AvailablePool=foo (one line per pool). -# -# If none are specified, it defaults to all the pools available to AOLserver. -# Index: openacs-4/packages/acs-tcl/tcl/table-display-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/table-display-procs.tcl,v diff -u -r1.9 -r1.10 --- openacs-4/packages/acs-tcl/tcl/table-display-procs.tcl 20 Sep 2002 22:18:16 -0000 1.9 +++ openacs-4/packages/acs-tcl/tcl/table-display-procs.tcl 17 Mar 2003 20:19:00 -0000 1.10 @@ -202,6 +202,7 @@ -Textra_vars {} -Textra_rows {} -bind {} + -dbn {} } statement_name sql_qry Tdatadef } { @@ -270,12 +271,13 @@