Other db alternatives: sqlite3?

[labadie_1]

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.

[Ben Armstrong]

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

[Jean-François Piéronne]

Ben,

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

JF

[Jean-François Piéronne]

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

[Craig A Berry]

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.

[Jean-François Piéronne · Accepted solution]

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/).