nssqlite3: SQLite 3 driver for AOLserver

Last night, I spent about three hours implementing nssqlite3. The motivations behind this included the fact that I’ve been working on a replacement for Movable Type which I currently use to manage my blog, and I’ve configured MT to use SQLite as its database. I figured it would be nice to be able to use the same database to make migration to Soapbox — the name of my blogging software package — easier, for myself and other MT users. But, this means needing a SQLite v3 driver for AOLserver, and the existing SQLite driver from Wojciech Kocjan is nssqlite2, which is for SQLite v2. So, I went ahead and implemented the driver for SQLite v3.

I sent an email to the AOLSERVER mailing list last night announcing the new SQLite database drivers [also: link, link].

I only tested the driver on my primary dev. environment so far, which is a Debian Linux box. I installed the following Debian packages to get SQLite v3: sqlite3 sqlite3-doc libsqlite3-0 libsqlite3-dev. I tested using AOLserver 4.0.10 with the following config. snippet appended to the sample-config.tcl:

#
# SQLite v3 -- nssqlite3
#
ns_section  "ns/server/${servername}/modules"
ns_param    nsdb            nsdb.so

ns_section  "ns/server/${servername}/db"
ns_param    defaultpool     sqlite3
ns_param    pools           *

ns_section  "ns/db/drivers"
ns_param    sqlite3         nssqlite3.so

ns_section  "ns/db/pools"
ns_param    sqlite3         "sqlite3"

ns_section  "ns/db/pool/sqlite3"
ns_param    driver          sqlite3
ns_param    connections     1
ns_param    datasource      /tmp/sqlite3.db
ns_param    verbose         off

I’ve created a nssqlite3 wiki page for the driver, which currently just contains a link to the initial nssqlite3 documentation. As more issues and questions arise, I’ll update the wiki page and the documentation.

Comments

Alexey Pechnikov says

February 23, 2008 at 5:10 am

Are you using nssqlite3? I try to use it, but aolserver 4.0.10 in my debian etch box is dead…

Alexey Pechnikov says

February 23, 2008 at 6:44 am

# tail -f /var/log/aolserver4/offline.sid.mobigroup.ru.log
“db_foreach “select value from session” {
alloc: invalid block: 0xb4cf5378: 78 b4 20

Dossy says

February 23, 2008 at 10:23 am

Alexey: Yes, I’m using nssqlite3 and it seems to work for me.

db_foreach, hm? Can you do a test using AOLserver’s lower-level ns_db API and see if you can get queries to execute? Also, can you get a gdb backtrace of the crash?

Alexey Pechnikov says

March 19, 2008 at 7:47 am

I use now libsqlite-tcl in AOL. It work better (I like sqlite-tcl interface with type control, variable bindings and many things). But I must load package sqlite3 in all threads. May be is better use nsproxy for this? I want to read in many threads parallel and write in one thread. With database lock wrapper (get db lock or sleep some times if db is locked already) it’s work good.

I think, ns_db is very complex and universal but for better performance and code readable is not best idea. Sqlite transaction is performed about by 500 microseconds and complex wrappers make big overhead. And high concurent applications must use parallel readings. On my laptop sqlite database can perform about 1000 write transactions per second and so if my application in the ratio between 1:10 and 1:100 write to read transactions and one client send query every 5 seconds then sqlite-driven application can serve about between 50 000 and 500 000 clients at same times (with enable full sync write for sqlite database file).

Dossy says

March 19, 2008 at 8:18 am

Alexey: You could definitely load sqlite-tcl in an nsproxy pool. The overhead of doing IPC to nsproxy should be minimal.

Alexey Pechnikov says

March 19, 2008 at 8:56 am

Thanks, I’m using debian etch with AOL 4.0.10 now and I like to optimize my code for AOL 4.5 in debian lenny.

Jeff says

March 21, 2008 at 3:56 pm

Are you interested in an nssqlite patch to make empty return sets work? I suggested it, with an underwhelming response last month..

Alexey Pechnikov says

February 28, 2009 at 10:12 am

Is sqlite-tcl in an nsproxy pool the best solution? I’m use SQLite in all my projects and AOL Server in all web projects and it’s important for me to optimize this.

P.S. Did you write how-to for nsproxy newbies? And nsrpc is very interesting especially for send requests from external applications to AOL Server clusters. I didn’t find manuals and how-to for these.

Dossy Shiobara says

February 28, 2009 at 11:56 am

Alexey: I think asking these questions to the AOLserver mailing list would be better than commenting here – you’ll reach a larger audience of AOLserver developers that way.

Nikolay Shulga says

June 19, 2010 at 9:13 am

Both links are dead:
http://aolserver.com/wiki/nssqlite3
and
http://cvs.sourceforge.net/viewcvs.py/*checkout*/aolserver/nssqlite3/nssqlite3.html?rev=HEAD