SQLite 2020

[David Jones]

I added kit #43 to the files area. It uses the SQLite 3.43.0 sources, which required an extra patch to build under X86_64. SQLite wants to support the long double data type, but will let you fall back to another type by defining a SQLITE_LONGDOUBLE_TYPE macro. For prior versions of SQLITE I used this workaround for building on X86 due to the VSI C compiler's broken support for long double. A change to the SQLite sources started using long double constants in a few areas, which makes the compiler crash.

[Craig A. Berry]

Would it help to use /L_DOUBLE=64 on the command line to tell it to use ordinary double as long double instead of doing anything fancy inside of SQLite? The problems with long double on x86 are in the release notes, so they know about it but it just hasn't gotten prioritized yet.

On Sep 7, 2023, at 3:53 PM, David Jones osuvman@users.sourceforge.net wrote:

I added kit #43 to the files area. It uses the SQLite 3.43.0 sources, which required an extra patch to build under X86_64. SQLite wants to support the long double data type, but will let you fall back to another type by defining a SQLITE_LONGDOUBLE_TYPE macro. For prior versions of SQLITE I used this workaround for building on X86 due to the VSI C compiler's broken support for long double. A change to the SQLite sources started using long double constants in a few areas, which makes the compiler crash.

---

SQLite 2020

---

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/vms-ports/discussion/portingprojects/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

---

Craig A. Berry

"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser

[David Jones]

Would it help to use /L_DOUBLE=64 on the command line to tell it to use ordinary double as long double instead of doing anything fancy inside of SQLite?

Adding that qualifier does stop the compiler crashes and my main test program (speedtest1) runs the same as the previously patched code.

All I did was edit build_library.com, changing line 22 to:
$ x86_skip_list = ",GEOPOLY,UTIL,"

And insert after line 114:
$ if f$getsyi("ARCH_NAME") .eqs. "x86_64" then cflags = cflags+"/L_DOUBLE=64"

[David Jones]

I put kit #44 in the files area. It uses the latest SQLite version, 3.43.2. They seemed to have tweaked something that makes the speedtest1 benchmark faster (noticeable, though not dramatic).

[David Jones]

The files area now has kit #46, intended for building with SQLite 3.45.0. It also includes the updated vtablib.c I enhanced for use with the vms_auth_xx.zip kits (vms$auth is a virtual table function that lets privileged users map the SYSUAF data to an SQLite virtual table).

[David Jones]

I added kit 48 to the files area, mainly to update the embedded SQLite sources to 3.46.0.

I also added the cstruct_01 kit for a virtual table extenssion I wrote to optimize buik loads of database tables. Strangely the performance improvement is much more my laptop with X86 VMS and other platforms I've tested: Alpha OVms, IA64 OVms, Pi 4 (ARM) Debian Linux, i5 Mac mini.

Cstruct Example <nowiki> // database schema: CREATE TABLE parts(id INTEGER, name TEXT); // CREATE VIRTUAL TABLE input USING // cstruct('{int a, char* b}'); #define ROWS 12 int rc; char *err; struct {int id; char *name;} input[ROWS]; cstruct_bind_map map; sqlite3 *db; rc = sqlite3_initialize(); rc = sqlite3_auto_extension ( cstruct_auto_init1 ); ... (open db file and initialize input[] array here) ... rc = cstruct_create_map2 (db, "input", &map, &err, 1, "{int a; char *b;}"); rc = cstruct_bind_data3 ( &map, ROWS, input ); sqlite3_exec ( db, "INSERT INTO parts SELECT a,b FROM input;", show_row, 0, &err ); cstruct_destroy_map4 ( &map ); </nowiki>

SQLite only equivalent <nowiki> // database schema: CREATE TABLE parts(id INTEGER, name TEXT); // #define ROWS 12 sqlite3_stmt *stmt; sqlite3 *db; struct {int id; char *name;} input[ROWS]; int rc, expected; const char *tail; rc = sqlite3_initialize(); ... (open db file and initialize input[] array here) ... rc = sqlite3_prepare_v2 (db, "INSERT INTO parts VALUES(?1,?2);", -1, &stmt, &tail); sqlite3_exec (db, "BEGIN TRANSACTION;", 0); for (i=0; i<ROWS; i++) { rc = sqlite3_bind_int ( stmt, 1, input[i].id ); rc = sqlite3_bind_text (stmt, 2, input[i].name, -1, SQLITE_STATIC); if ( rc == SQLITE_OK ) { rc = sqlite3_step(stmt); /* expect empty result set */ if ( rc != SQLITE_DONE ) break; } if ( rc != SQLITE_DONE ) { printf ("Insert error!\n"); break; } rc = sqlite_reset(stmt); } sqlite3_exec (db, "COMMIT;", -1, 0); sqlite3_finalize (stmt); </nowiki>

Notes:

1. Registers an init function which will invoke cstruct_create_module(db,"cstruct") on every new database open.
2. Makes an internal representation of the 6th argument, verifies it matches the table named by the 2nd argument, and prepares a map object for use by cstruct_bind_data call. While this map object exists, the table it maps is locked against deletion.
3. Updates internal cells in the virtual table instance with the address of the input array and its size.
4. Tear down map object, unbinding data array and allowing database connection to close.

[David Jones]

I found a bug in VSI's C compiler for X86 that makes some of SQLite's builtin aggregate SQL functions not work. I made a workaround (re-implement SUM(), AVG(), TOTAL() in a auto-loaded extension built into the VMS VFS) and made it available in an 'increment' kit: vmsvfs_in20240623.zip.

Note that the bug does not affect Alpha or IA64.

[David Jones]

I found it strange that I hadn't encounter the problem with SUM before now, further investigation shows that:
1. SQLite didn't use the Kahan-Babushka algorithm until version 3.43.0, released in August 2023.
2. The cross-compiler for X86 doesn't appear to have the bug.

The updated algorithm attempts to get a more accurate sum when the list of addends includes both large magnitude and small magnitude values. The problem is it depends upon strict ordering of operations which optimizing compilers would have a proclivity to re-order or void out (same value being added and subtracted in different places). The SQLite code was declaring variables and parameters volatile in order to defeat the optimizer, exposed the bug in the VSI compiler.

[David Jones]

I added a new incremental update to kit 48, superceding the 20240623 incremental (I'll wait until SQLite makes a new release before making a new full kit). The changes made have to do with the perf_timer VFS option: I wanted to better track how well the internal file cache was working so added statistics counters to that code. When enabled by bit 2 in the perf_timer setting, the new counters will produce a report showing:
* Read/write ratio of calls to from the SQLite library to the VFS
* The read hit rate of the buffer pool in the file cache (misses result in RMS reads).
* Number of RMS writes due to buffer replacement or sync requests.

On my DS10L, running the speedtest.c program with buffer counts of 200, 400, 800, 1600, and 2400 shows pool read hit rates of 10.2%, 22.6%, 51.0%, 71.1%, and 82.2%, respectively. Clock time from 200 to 2400 buffers dropped from 24.75 minutes to 10 minutes (CPU was 60% less as well).

A similar speedtest run on my SSD-equipped X86 laptop has wall clock dropping from 109 seconds to 64.75 seconds with consumed CPU 46% less.

[David Jones]

The current kit is now 50 and comes with SQLite 3.47.0. The speedtest1 program runs dramatically faster with 3.47 than 3.46. They apparently tweaked the resident pages it the cache so speedtest1 makes 98% fewer I/O calls to the VFS.

[David Jones]

I add kit 52 to the files area. It has an experiment MMS file for building with clang on X86 systems.
It also fixes a bug in handling of filenames/directories which contain special characters (the kind which require quoting with '^'). The names tend to give the CRTL problems, and failure of a decc$translate_vms() can lead to a program crash.

[David Jones]

I did a routine update of the kit to use the latest SQLite release, 3.49.2. There is also an updated gstvtab virtual table kit (gstvtab_003.zip). The new gstvtab tracks the library modules so you can find out which module defines a symbol. It can now show you symbol attributes for object library symbols (but not shareable image library symbols).

[David Jones]

I put out a new exec_convert kit (007). The main new feature is it better handles text in the spreadsheet that isn't 7-bit ASCII. .xlsx files store text using UTF8, which xlsxvtab virtual tables now decode and convert to the 8-bit DEC MCS character set byte default. If you want to disable the conversion, issue the SQL statement "SELECT xlsxvtab_config('text_convert', NULL);" prior to creating the virtual table (or first access if already defined in the schema).

The default DECMCS mode can do some transliteration, e.g. changing dash characters to ASCII minus. If you want to use another 8-bit code set, use the xlsxvab_config function giving the iconv() function to argument as the second argument: "SELECT xlsxvtab_config('text_convert', 'ISO8859-1');"