topic Re: Other db alternatives: sqlite3? in Operating System - OpenVMS

Other db alternatives: sqlite3?

Ben Armstrong — Mon, 07 Apr 2008 13:22:56 GMT

MySQL is only one of a number of possible databases we could use. I think we'd be happy with sqlite3 if it worked. Unfortunately, all I see out there is an old port of sqlite3 that does not support locking:

http://www.bourles.fr/Portage.html

Sqlite: Cest une bibliothèque qui permet d'embarquer une base de données SQL dans une application. Le portage sur OpenVMS est pour l'instant incomplet car il n'y a pas de mise en oeuvre des verrous. Le portage n'est donc pas adapté à une application multi-processus ou multi-threads. Le portage complet est en cours de réalisation néanmoins les sources SQLITE-3_3_4-VMS.ZIP sont disponibles dans l'état actuel. L'utilitaire de gestion sqlite3.exe est compris dans l'archive ainsi que la bibliothèque d'objets sqlite3.olb.

http://www.bourles.fr/kits_openvms/SQLITE-3_3_4-VMS.ZIP

I compared this with the latest upstream release, and in addition to including a .com to make the package, it also has an os_vms.c not present upstream. I also checked upstream's revision control system logs and found the last changelog mentioning VMS was back in 2003. Not looking good.

Is there a more recent port I'm missing? How much work is involved here in fixing locking?

Re: Other db alternatives: sqlite3?

labadie_1 — Mon, 07 Apr 2008 14:11:01 GMT

Hello

May be you should drop him (Bourles) an email ?

The best source of information about databases for Vms is
http://dba.openvms.org/stories.php?page=4&topic=

Have you tried Mimer (www.mimer.com), Ingres (which has gone open-source) ?

If you have some free time, you could try to port Interbase/Firebird (http://sourceforge.net/project/showfiles.php?group_id=9028)

Re: Other db alternatives: sqlite3?

Ian Miller. — Mon, 07 Apr 2008 14:41:17 GMT

The previous link should be

http://dba.openvms.org

(thanks for the plug :-) )

Re: Other db alternatives: sqlite3?

Ben Armstrong — Mon, 07 Apr 2008 15:08:11 GMT

Yeah, I've been to that site. Couldn't find anything there on sqlite3, but if the community here doesn't know, perhaps I'll ask there. I just started here because it was at least familiar to me.

We don't want to pass the cost of proprietary licenses down to the clients, so that rules out Mimer. Ingres has the same problem as MySQL, which is it will require an up-to-date Ruby and porting the Ruby bindings for it to VMS Ruby. And no, we really aren't interested in 'owning' another port.

Re: Other db alternatives: sqlite3?

Willem Grooters — Mon, 07 Apr 2008 16:32:30 GMT

I've been looking into porting sqlite, but my Unix knwoledge is too little to get on with it at the moment (as there are more urgent things to do). I used the latest version, as far as I know (3.5.4, downloaded a few weeks ago).
I know that it is embedded in some packages, to it is possible (and works fine). What I learned from those that use it:
A number of files contain platform specific code: mutex*.c and os*.c. The best thing to do is create VMS-specific versions of these files and used these. Most important is locking (in os_unix.c) that needs a rework.

Probably this os-vms.c is a good start.

Re: Other db alternatives: sqlite3?

Ben Armstrong — Mon, 07 Apr 2008 16:36:06 GMT

It may be a good start, but is already ~2000 lines of code. I am not sure it's going to be worth it to us unless it turns out to be mostly done already.

Re: Other db alternatives: sqlite3?

Chris Barratt — Mon, 07 Apr 2008 23:51:21 GMT

If you have Rdb available to you, one of the programmers here has used the Rdb JDBC driver to connect to Rdb databases from JRuby running on VMS.

Not using it for any applications, but it seems to work ok.

cheers,
chris

Re: Other db alternatives: sqlite3?

Ben Armstrong — Tue, 08 Apr 2008 10:13:08 GMT

Well, we're looking for a cross-platform solution, so that rules out Rdb. Also, depending on JRuby is not a big plus until the performance problems can be solved.

Re: Other db alternatives: sqlite3?

Craig A Berry — Sun, 13 Apr 2008 21:05:57 GMT

The port of 3.3.4 mentioned above does not appear to be particularly interesting. The 2,000-line os_vms.c is 99% the same as another file in the same distribution called os_unix.c. Most of the changes consist of renaming all the functions so they have "VMS" rather than "UNIX" in the names.

Rather than assuming a port is needed, why not just try building it? If you download the "amalgamated" source code of the current release from here:

http://www.sqlite.org/sqlite-amalgamation-3_5_7.zip

Unzip it, and execute the following command:

$ cc/names=(as_is,shortened) -
_$ /define=(NO_GETTOD,RTLD_GLOBAL=0 -
_$ ,_USE_STD_STAT,_XOPEN_SOURCE_EXTENDED) -
_$ sqlite3

You will get a sqlite3.obj file and you might as well just try linking against it and seeing what happens. You'll also get one QUESTCOMPARE informational diagnostic that does not seem to be all that dire, but if it bothers you it should be an easy fix.

On the locking issue, it looks like sqlite uses the locking flags of fcntl(). The CRTL acquired these somewhat recently, so if you are on a recent enough version of VMS there shouldn't be any work at all in getting locking to work. I'm afraid I don't know offhand what version that is.

I also managed to build it by downloading the complete source here:

http://www.sqlite.org/sqlite-3.5.7.tar.gz

copying the Makefile.linux-gcc to Makefile, fiddling with some flags and macros, and then built it with GNV's make under GNV's bash. I could post my Makefile if anyone is interested. I don't see any advantage to building it this way unless you want to take another step and integrate TCL, which appears to be required to run the test suite.

Re: Other db alternatives: sqlite3?

Ben Armstrong — Mon, 14 Apr 2008 13:44:10 GMT

Thanks, Craig. Yeah, we noticed that's all that was in os_unix.c and independently concluded that we should just start fresh. But I think OpenVMS/Alpha 7.3-2 and/or our compiler might be too old to get all of the posix goodies. It builds but won't run. I'll have to check with my associate here to get the exact errors if we're going to pursue further debugging on this system. But first, we have an OpenVMS/Alpha 8.3 system and will see if we can at least replicate your results there.

Re: Other db alternatives: sqlite3?

labadie_1 — Mon, 14 Apr 2008 14:52:00 GMT

There is another database that is great, Postgresql. But it is a huge work to port it to Vms, as it was written for Linux.
(Yes, it has been ported to Windows.)

I think the port of Sqlite3 is much much simpler.

Re: Other db alternatives: sqlite3?

Ben Armstrong — Mon, 14 Apr 2008 18:01:54 GMT

Our test of SQLite3 3.5.7 was partially successful when I switched to OpenVMS V8.3. It now builds correctly but we have run-time problems. An in-memory btree works, but we can't create a database file:

Success (using in memory btree):

$ sqlite
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table foobar(a,b,c);
sqlite> select * from foobar;
sqlite> insert into foobar values ("Brad", "dv", 2002);
sqlite> select * from foobar;
Brad|dv|2002
sqlite> drop table foobar;
sqlite> .quit
$

We've included some DEBUG print statements to try to figure out what's going on, here. We start by creating a database on another host and ftping it over because we have found we cannot write a db, but we can read one. Also, relative filepaths don't work and neither do VMS filepaths, so we give an absolute filepath /tmp/foo.db (/tmp maps to sys$scratch:):

$ sqlite=="$DSA0:[DYMAX.001029.sqlite]sqlite3.exe"
$ unzip foo.zip
Archive: DSA0:[DYMAX]foo.zip;1
inflating: foo.db
$ sqlite /tmp/foo.db
DEBUG: database name=/tmp/foo.db
DEBUG: attempt open of db(/tmp/foo.db)'s btree
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /tmp/foo.db
sqlite> .schema
CREATE TABLE foobar(a,b,c);
sqlite> select * from foobar;
Brad|dv|2002
sqlite> create table bar(a,b);
SQL error: unable to open database file
sqlite> .quit

Craig, would you please do these tests and see if you get similar results? Which OS version / compiler version?

We're using:

Compaq C V6.5-001 on OpenVMS Alpha V8.3

Ben

Re: Other db alternatives: sqlite3?

Jean-François Piéronne — Mon, 14 Apr 2008 20:31:45 GMT

Ben,

I have take a look, the offending routine is
openDirectory which try to open the directory using open and failed on VMS.

JF

Re: Other db alternatives: sqlite3?

Jean-François Piéronne — Mon, 14 Apr 2008 20:54:31 GMT

I have a working version as a Python module, I will publish a patch tomorrow.

I just remove the call of openDirectory on OpenVMS and fix another small problem.

My first test:
$ dir

Directory DISK$KITS:[tmp.test]

bar.py;5

Total of 1 file.
$ ty bar.py
import sqlite3
conn=sqlite3.connect('./example')
c=conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.execute("select * from stocks order by price")
print c.fetchall()
c.close()
$ reca py
$ python bar.py
[(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001)]
$ dir

Directory DISK$KITS:[tmp.test]

bar.py;5 example.;1

Total of 2 files.

I will do more test later.

JF

Re: Other db alternatives: sqlite3?

Craig A Berry — Tue, 15 Apr 2008 01:44:01 GMT

Ben, I should have said I was using:

$ cc/vers
HP C V7.1-015 on OpenVMS Alpha V8.3

with

$ prod show hist VMS83A_ACRTL
------------------------------------ ----------- ----------- --- -----------
PRODUCT KIT TYPE OPERATION VAL DATE
------------------------------------ ----------- ----------- --- -----------
DEC AXPVMS VMS83A_ACRTL V2.0 Patch Install Val 31-MAY-2007
------------------------------------ ----------- ----------- --- -----------

1 item found

The CRTL update may be more important than the compiler version, but I don't think either is the source of your problem. I had already done the equivalent of your first test and had no trouble, but providing a file to sqlite3 as in your second test I got the same error you did. It looks like JF is well on his way to solving that one.

It's rather curious that they apparently open a filehandle on the directory file that the database file is in so that they can flush the directory explicitly. Kind of sounds like a poor man's RMS.

It must be said that keeping VMS-specific build requirements at a minimum and providing optimal native performance and functionality are not necessarily the same thing. My tendency would be to see how far I could get with a minimal build and resist the temptation to write a bunch of VMS-specific code unless there are specific deal-breakers that require workarounds.

A side note on your C compiler version. C 6.5 is neither current nor ancient, but there were several ECOs with important bug fixes in the 6.5 era. I believe I remember an "049" release where yours has "001". Which is just to say that even if you don't have upgrade rights or an upgrade budget, getting the latest patchlevel for your version should still have benefit.

Re: Other db alternatives: sqlite3?

Jean-François Piéronne — Tue, 15 Apr 2008 06:14:54 GMT

I have attached the patch against the "amalgamated" source code.

I seem to work correctly on my IA64 8.3 box.

I will do more test (including using django).

JF
ps
I have put in the python repository the source code and the building procedure (http://hg.vmspython.dyndns.org/vmspython/).