Forum OpenACS CMS: OpenACS database API

Collapse
Posted by Bernard Devlin on
Hi all, I recently returned to looking at Naviserver for a project.  After several failed attempts to get it installed I finally happened upon Gustaf's _excellent_ install.sh which made installing Naviserver, Postgres and drivers a one-liner on several Linux distributions I tried.

I'd like to add the sophistication of the OpenACS database API to my project. If necessary I will install the full OpenACS and just use the database API.

What would be the cleanest way to install either the full package or just the database API with my working Naviserver?

BTW Gustaf, I would suggest putting a link to the install.sh in a prominent place on the Naviserver home page.

Collapse
2: Re: OpenACS database API (response to 1)
Posted by Antonio Pisano on
Dear Bernard,

even if you just plan on using the database api in your project, I still suggest you install all the packages you need as per a normal installation, as there are sometimes tcl-level callbacks responsible to define data model and/or fill tables with fundamental data.

I do not know enough about your project to suggest further, but from what I understand, just install the packages you need. This is as clean as it gets :-)

Hope it helps.

Collapse
3: Re: OpenACS database API (response to 1)
Posted by Bernard Devlin on
Hi Antonio

Thanks for your reply.

I've since discovered the nsdbi alternative to ns_db. I think this will do what I want without installing the database api from OpenACS. It could be that as I learn more with Naviserver I'll have reason to use more of what OpenACS provides.

Basically I want a programmable webserver with straightforward relational database access. It's beyond me why so few developers want this niche filled. In the Nginx world it appears that OpenResty is reinventing the wheel that is Naviserver.

Incidentally I have been working with PostgREST. They laud the speed of their Haskell/Postgresql combo, saying that people will be surprised at the responsiveness of such a compiled solution. Well, I've tested it against Naviserver and they are as fast as each other. Only Naviserver allows one to do so much more in the webserver itself.

Past is prologue.

Collapse
4: Re: OpenACS database API (response to 1)
Posted by Gustaf Neumann on
Dear Bernard,

NaviServer supports two interface families (nsdb and nsdbi) to talk to the database backends. The first one (nsdb) is the classical one, which is also the one used for the OpenACS "db_*" interface [1], used in AOLServer and NaviServer. The nsdbi interface is newer, and comes with prepared statements, which is for many applications performance-wise better than nsdb. The xotcl-core package of OpenACS offers a uniform interface to both, nsdb and nsdbi [2], supporting also prepared statements for nsdb.

One disadvantage of nsdbi it has no means to deactivate prepared statements, such that under certain conditions (SQL-queries with constants, inserted via e.g. Tcl substitutions - actually a bad style) might cause a bloat of the prepared statements. When this happens, there is a significance performance hit. This can be a problem with huge legacy DB applications like with OpenACS, but it is normally not an issue. When you run into problems, just post, we are here to help you.

One can certainly use the nsdb interface of NaviServer also without OpenACS [3].

-gn
PS: a few years ago, a student made a comparison of Apache+PHP+PostgreSQL vs. NaviServer+nsdbi+PostgreSQL with the same queries (taken from real-world applications), which showed a significant advantage of the NaviServer performance.

[1] https://openacs.org/api-doc/procs-file-view?path=packages/acs-tcl/tcl/01-database-procs.tcl
[2] https://openacs.org/xotcl/show-object?object=::xo::db::DBI&show_methods=2&show_source=1
[3] https://naviserver.sourceforge.io/n/nsdb/files/ns_db.html

Collapse
5: Re: OpenACS database API (response to 1)
Posted by Bernard Devlin on
Hi Gustaf

Thanks for the clarification (especially about potential problems with nsdbi - I can look out for them in case I hit them).

There's so much power lying in just Naviserver, Tcl pages, nsdbi/nsdb, postgresql. My test database has tables with 50 million rows, getting average request-response times on the url client of 20 - 50 ms (the tests exercise random access over the data in the tables). That's with no tuning, just a standard install from your install.sh script.

I really like being able to use the Naviserver console as a REPL. That facility should really appeal to those who use dynamic languages on the front-end.

Regards Bernard

Collapse
6: Re: OpenACS database API (response to 1)
Posted by Gustaf Neumann on

Talking about expected performance magnitudes: On one of our OpenACS-based systems, we have currently 400 mio acs-objects (422,722,032), which are tuples of a single table. The full system has 1,168 tables and 452 views. On this system, we see an average response time from the DB of <1ms:

statements 460.5M gethandles 14.7M handles 170 connected 73 used 130 waittime 12.686245 sqltime 331744.583118 avgwaittime 862.3ns avgsqltime 720.4µs

But of course, on every large db one can get slow responses from the DB, it really depends on the queries.

Concerning "good" and "bad" queries for nsdbi: The simple rule is to use always bin variables, and avoid plain Tcl substitution.

Good (with bind variable):

set x 1
dbi_1row {select column from table where key = :x}

Bad (without bind variable):

set x 1
dbi_1row "select column from table where key = $x"

If one has e.g. 10.000 different values for variable x, in the first case, one ends up with just a single prepared statement, in the latter case with 10K prepared statements.

When nsdbi has to clean up its prepared cache, performance degrades, since potentially, for every statement there will be a cleanup operation, the generation of a new prepared statement, and then the execution of the query. The first two steps require in addition mutex locks, thus reducing concurrency. When there is already the prepared statement, just the last step is needed.

Collapse
7: Re: OpenACS database API (response to 1)
Posted by Siqsuruq Siqsuruq on
Hello, I've seen you discuss nsdbi, and I have a question not really related to API but nsdbi itself. I have a table in my DB when im trying to get 1row or 0or1row, its always rising an error "query returned more than 1 row", and to be honest no idea why? I know there is only one row in a table with such value here is my proc:
	proc select_id_by_name {table name} {
		set ser [ns_info server]
		if {[dbi_0or1row "SELECT uuid_${table} FROM $table WHERE name = :name"]} {
			set mysteryMan "$uuid_client"
		} else {
			set mysteryMan 0
		}
	}
Collapse
8: Re: OpenACS database API (response to 1)
Posted by Gustaf Neumann on
Dear Siqsuruq,

You are right, the command "dbi_0or1row" and "dbi_1row" returned complaints in situations, where they should not. This is fixed now on bitbucket in the nsdbi repository [1]. This will be as well part of the next release.

all the best
-gn

[1] https://bitbucket.org/naviserver/nsdbi/commits/