[Info-vax] VMS databases

[Arne Vajhøj]

On 11/20/2023 2:46 PM, Jake Hamby (Solid State Jake) wrote:
> On Saturday, November 18, 2023 at 3:44:00 AM UTC-8, Neil Rieck wrote:
>> On Friday, November 17, 2023 at 8:10:25 PM UTC-5, Arne Vajhøj wrote:
>>> I just did another little article.
>>>
>>> https://www.vajhoej.dk/arne/articles/vmsdbx.html
>>>
>>> providing some examples for the databases available on VMS (Rdb, MySQL,
>>> SQLite, Mimer, Derby, H2, HSQLDB) in various programming languages
>>> (Cobol, C, Pascal, Java, Jython, Python, PHP).
>>>
>>> Nothing surprising and a lot of overlap with previous
>>> articles, but a different perspective.

>> Two additional points.
>>

>> 1) I've done a bit of hacking with SQLite (on both OpenVMS + Linux
>> ) and can inform that it should only be used in single user
>> applications. When any process issues an "update table" command,
>> all other processes are locked out.
> 
> I’ve also long considered SQLite to be basically only useful for
> single-process use. SQLite 3 has file locking support using various
> OS-specific APIs but I’m not sure how efficient or concurrent it is
> in practice.

Embedded database are really for the single-process use case.

> I see Arne’s blog post doesn’t appear to mention the native VMS port
> of SQLite by David L. Jones.

It is the one I am using.  I am not even aware of any other.

>                            I downloaded it and looked at it very
> briefly and it has a custom locking implementation using a “lock
> nexus file” (default sys$login:sqlite3_lock_nexus.nxs). Code comment:
> “The intent is the actual files are unlocked and shared access is
> coordinated through locking of records in the nexus files (which
> allows waits) as a proxy.”
> 
> There’s clearly a lot of effort that has gone into the native port
> but I’m not sure if it matters much when multi-process access doesn’t
> seem to be a common use case for SQLite.

I think it works pretty well within its expected "comfort zone".

The JDBC driver could need some work though.

>                                          It’s also an unusual choice
> to create a separate file for lock management and put it in the
> user’s home directory. What if you want to make the database file
> accessible to multiple users?
Multi-users does not necessarily mean multiple usernames.

But if multiple usernames then you can define the logical
SQLITE3_NEXUS_FILE to point to a file shared among them.

Arne