Recently, there has been a new addition in xotcl-core that allows to have different database contexts to interact with the database. At run time, one can dynamically change the interface one wants to talk to the DB with. This allows us to operate simultaneously with two db interfaces talking to the same database: nsdb (nsdbpg ) and nsdbi (nsdbipg). The reasons we want to have such a setup are the following:
- First of all, giving the way nsdbpgi is implemented, queries run sometimes up to 10 times faster through that interface. Therefore we want to be able to use this interface to run our queries. The following tests show how fast it is:
# dbi_* calls beling to the nsdbi interface and db_* calls to the nsdb one.
append _ [time {dbi_rows {select object_id from acs_objects limit 200}} 100]
append _ [time {dbi_rows {select object_id, title from acs_objects limit 200}} 100]
append _ [time {dbi_rows {select object_id, i.storage_type from acs_objects, cr_items i where i.item_id = object_id limit 200}} 100]
append _ [time {db_list _ {select object_id from acs_objects limit 200}} 100]
append _ [time {db_list_of_lists _ {select object_id, title from acs_objects limit 200}} 100]
append _ [time {db_list_of_lists _ {select object_id, i.storage_type from acs_objects, cr_items i where i.item_id = object_id limit 200}} 100]returns e.g.
269.35302 microseconds per iteration
285.24578 microseconds per iteration
275.28688 microseconds per iteration
1268.44453 microseconds per iteration
2037.16921 microseconds per iteration
969.6990499999999 microseconds per iteration- Unfortunatelly we can not afford to entirely switch from the nsdb interface to the nsdbi. The commands that nsdbi exposes to the tcl developers are slightly different and that means we would have to change all implementations of the db_* api. So, for now, we still get to keep the db_* api as it is.
Within xotcl-core now there is a new object called ::xo::dc (dc stands for database context), that is used to talk to the database. This new object has to be used instead of the previous api : ::xo::db_*. Specifically in the following cases:
- use
::xo::dc 1rowinstead of::xo::db_1row - use
::xo::dc 0or1rowinstead of::xo::db_0or1row - use
::xo::dc dmlinstead of::xo::db_dml - use
::xo::dc list_of_listsinstead of::xo::db_list_of_lists - use
::xo::dc 1rowinstead of::xo::db_list - use
::xo::dc get_valueinstead of::xo::db_string - use
::xo::dc foreachinstead of::xo::db_foreach - use
::xo::dc transactioninstead of::xo::db_transaction - use
::xo::dc selectinstead of::xo::sql::db select
The api db_* stays as it is and will always use the nsdb interface.
Now, within xotcl-core we have the option to use the nsdb or the nsdbi interface. Switching back and forth is possible by calling ::xo::select_driver {DB|DBI}. Theoretically you should never need to change the interface you are using.
- xotcl-core automatically generates wrapper methods for all stored procedures in the database (plpgsql functions defined in our PG database e.g.:
::xo::db::sql::content_folder del -folder_id $folder_id -cascade_p t), those remain unchanged (same name within the same namespaces). Internally calls to those wrappers are forwarded to the righ functions depending the driver selected at the moment of the call. - Now, regarding
db_transactioncode: There are some case where the body of the transaction includes calls to xotcl based code (e.g. initialization of xowiki based packages), depending on the database interface selected at the moment, all calls to::xo::dccould go to the nsdb or to nsdbi. This could lead of course to nasty consequences if calls go to the nsdbi interfance because those queries will be executed out of the transaction context that is happening on the nsdb driver at the moment. Therefore, before starting a transaction viadb_transaction, the code makes sure that the ::xo::dc object is using the nsdb interface so that all queries stays within the transaction context. - There has been as well changes in the
db_multirowimplementation. Now the code uses thexo::dcobjects to fetch the results of the query in question to iterate over them. Then only drawback being that it does not allow to havebrakestatements within the body of the multirow. A common practice has been to usebrakestatements to skip rows to be included in the resulting multirows; instead, those conditions that filter rows should be translated to the query. E.g., If you want to limit the result of your queries to a given amount of rows ( lets say, you just want to show the 10 most recent news items ). instead of using break statements in the body of the multirow, move that condition to the query and leverage on the LIMIT and OFFSET capabilities of PG queries.
Right now all needed changes (configuration, wrappers, code changes) were done in vguerra-dev and testing is needed. If no critical bugs are found, I would ask VRL to go ahead with testing all affected parts of the system.
All internal changes were already taked care of. You just have to adopt now the usage of the ::xo::dc object and avoid the ::xo::db_* api (This api is gone, you'll get an error if you try to use it).
In order to define a pool of conections to postgress via nsdbipg please add the following configuration to your naviserver config file:
ns_section "ns/modules"
ns_param pool1 nsdbipg.so
ns_section "ns/module/pool1"
ns_param default true
ns_param maxhandles 10
ns_param timeout 10
ns_param maxidle 0
ns_param maxopen 0
ns_param maxqueries 0
ns_param maxrows 1000000
ns_param datasource "port=$db_port host=$db_host dbname=${db_name} user=${db_user}"
ns_param cachesize [expr 1024*1024]
ns_param checkinterval 600