Updated port of SQLite

There was some discussion last month around SQLite and getting it updated. I’ve taken the old port of SQLite 3.3.6 created by The Really Small Software Company and have updated it to the latest version of SQLite (3.42). It’s built with the DDE and should work back to RISC OS 3.1.

“Beta 8” is available here or via PackMan. Source is here (you may wish to read the BuildNotes file).

Changed in beta 8

• The SQLite library and shell are now version 3.42.

Changed in beta 7

• The SQLite library and shell are now version 3.41.2.

Changed in beta 6

• The SQLite library and shell are now version 3.40.1.
• The WimpSlot is now set differently, as documented in the comments on page 3 of this thread.
• SQLite$Path is now set correctly.

Changed in beta 5

• The SQLite library and shell are now version 3.40.

Changed in beta 4

• The SQLite library and shell are now version 3.39.4.

Changed in beta 3

• The SQLite library and shell are now version 3.39.3.

Changed in beta 2

• The SQLite library and shell are now version 3.39.2.

Changed in beta 1

• The SQLite library is identical to that of alpha 13. However:
• SQLite is now packaged as a RISC OS application, and double-clicking it launches the shell.
• Database files (SQLiteDB, &A70) now have a custom icon.
• The app has a help file, accessed through Filer in the usual way. This explains how to use the command-line shell. This documentation is effectively a copy of the official documentation, but has been updated with RISC OS-specific notes.
• The actual C headers and object files can be found inside the SQLite app directory. You can therefore use the single app for both development and testing.

Fixed in alpha 13

• The SQLite library and shell are now version 3.39.
• Fixed a regression in the shell (not the actual library) that caused a handful of SQL statements to fail.

Fixed in alpha 12

• The database file will no longer get stuck open if the CLI gives an error.

Changed in alpha 11

• The SQLite library and shell are now version 3.38.5.

Changed in alpha 10

• The SQLite library and shell are now version 3.38.2.

Changed in alpha 9

• The SQLite library and shell are now version 3.38.1.
• The shell is now built using the additional recommended defines mentioned at the bottom of this page. This means that to build it yourself you will now need to have zlib exported (or you can remove the define from MakeShell if you don’t want it).

Fixed in alpha 8

• The SQLite library and shell are now version 3.38.
• The command-line shell now sets the RISC OS file type correctly when saving to the various output formats.
• When attempting to ‘launch’ a generated file, the CLI now uses Filer_Run instead of the non-existent (Unix) “xdg-open” command.

Changed in alpha 7

• The SQLite library and shell are now version 3.37.2.

Changed in alpha 6

• The SQLite library and shell are now version 3.37.

Fixed in alpha 5

• Databases containing floating-point values are now compatible with the non-RISC OS versions of SQLite. This is a breaking change. Please refer to below if you have older databases.

Fixed in alpha 4

• Filename canonicalisation now returns the correct error code upon failure.
• Built with -zpz0 to work around some failed test cases.
• Uses system-provided isnan() to work around word order issues.

Changed in alpha 3

• The SQLite library is now version 3.36.
• The command-line shell is also version 3.36 and I intend to keep it aligned with the library version from here on out.

Fixed in alpha 2

• ‘Delete on Close’ is now functional.
• Temporary files are now created in Scrap.
• The SQLITE_FCNTL_SIZE_HINT and SQLITE_FCNTL_HAS_MOVED operations are now implemented (although the latter always returns false at this stage).
• Sector size lookup is now implemented.

Notes

• The library is compiled using the 12 recommended options, with the exception of SQLITE_USE_ALLOCA (since the DDE doesn’t supply it).
• As of alpha 4, the entirety of SQL Logic Test passes.
• There is no support for extensions at present. I haven’t yet investigated how feasible this will be.
• If you want to use a “sqliterc” file with the shell, put it in Choices:SQLite.
• Any databases you create will be given file type SQLiteDB (&A70), but you can open SQLite databases of any file type.
• The database file must be on a filing system that supports long filenames.
• If you get an abort on data transfer, try a bigger WimpSlot. 1.5 MB is sufficient for simple databases.
• The shell doesn’t create a history file.
• You must not issue a *Close command while a database is open.
• The “box” mode in the shell uses Unicode box characters. You will need to use a font that supplies them.
• The archive file support (including ZIP) doesn’t handle RISC OS file types.
• Be sure to call sqlite3_initialize() and sqlite3_shutdown() when using C. See the SQLite documentation for more details.

Please report any bugs in this thread. There are certain to be some I haven’t found!

Thanks to Really Small for the original port; I probably wouldn’t have got this far without that as a baseline.

IIRC the main issues we had with latest sqlite in Iris (it uses it for various local storage things) related to re-enterancy and handling of multiple instances. I guess for non-ported apps (generally DDE’s strength) it wouldn’t matter, but is there anything here that might help with such things?

It’s a pretty basic port at the moment, so doesn’t have much in the way of handling RISC OSisms. The SQLite docs actually advise against having multiple instances so I haven’t done anything special to handle them.

Presumably Iris is using UnixLib and the “Unix version” of SQLite. Functionally it’s probably fairly similar to this new one.

Alpha 2 is now available, complete with source code. The first post has been updated with details.

very nice job @ Chris! :)

As a little interim update, here is an up-to-date version of the command-line shell. This is shell 3.35.5, as opposed to the older version included with alpha 2. The underlying SQLite library is the same as alpha 2.

Next on the agenda for the main library itself is to get the automated tests working. I’ve run some of them manually and none of them have failed, but I haven’t yet automated them. There are a lot, so automation is a necessary step!

Alpha 3 is now available (see first post). This is based on SQLite 3.36, and released on the same day as the non-RISC OS version :)

This is based on SQLite 3.36, and released on the same day as the non-RISC OS version

You’re on fire. Nice work.

Next on the agenda for the main library itself is to get the automated tests working. I’ve run some of them manually and none of them have failed, but I haven’t yet automated them. There are a lot, so automation is a necessary step!

I haven’t had much free time over the past couple of months, but today I finally got this up and running.

Unfortunately there are some failed tests, so this library shall retain the “alpha” tag for now. I’m not sure when I’ll have time to fix up the issues, but will post any updates in this thread.

I fixed¹ an issue and a number of the failed tests now pass. Unfortunately it didn’t fix all of them, so I’ll have to delve deeper at some point.

The sorts of things that are failing are edge cases with SQL that nobody in their right mind would actually write:

SELECT + - CAST ( + SUM ( CAST ( CAST ( 32 AS REAL ) AS INTEGER ) ) AS INTEGER ) * - 39 col2 FROM tab2 cor0;
SELECT ( + - col1 ) AS col4 FROM tab0 AS cor0 WHERE NOT - col1 * + col0 + - - ( col1 ) + + col3 IS NOT NULL;

Nevertheless, I want to get all the tests working for the confidence that everything behaves correctly.

¹ It’s really a workaround rather than a fix; I had to disable CSE optimisation to get it to work. I’m not sure whether it’s something non-standard in the code doing it, or whether it’s an issue with the compiler. I simply noticed that it worked fine² in DDT but not when running a ‘real’ build.

² And by “fine” I mean “the file’s so large that DDT starts truncating line numbers once you pass line 99999 and then crashes on quit”…

I simply noticed that it worked fine2 in DDT but not when running a ‘real’ build.

In my experience, that is often something using a value that it expects to have a default “unset” value of null, which isn’t the case in the real build.

For example, if you have:

int  x;
int  y;

Then at runtime, until x and y have been explicity set to something, they will be “whatever”. Under any sane Debugger they will be zeroed.
Some compilers have the option to zero-initialise all variables that aren’t explicitly given a value.

I always declare as:

int  x = 0;
int  y = 0;

even if three lines down I set it to be something different. Always begin with a known state, or relying upon any sort of state that you can’t guarantee will eventually give you headaches.

Under any sane Debugger they will be zeroed.

FTFY: When built for debug, they will be zeroed.

On platforms where you can run debug builds without a debugger, this catches out a lot of people when they finally make a release build. At least with Norcroft, having to run under DDT ensures no one ever builds for debug if they don’t need to.

The problematic code is (or rather, appears to be) part of SQLite itself and not my ‘interface’ code, so I don’t want to make any changes to it. The last thing I want to do is introduce strange new bugs :)

I finally stopped procrastinating and had a look into this. First of all, here’s the actual failing SQL test case:

CREATE TABLE tab0(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);
INSERT INTO tab0 VALUES(0,26,690.51,'vkasq',738,541.24,'zdmee');
INSERT INTO tab0 VALUES(1,621,84.92,'jnvsy',783,88.94,'swdpn');
INSERT INTO tab0 VALUES(2,865,358.72,'gwuaz',225,162.41,'pgyvg');
INSERT INTO tab0 VALUES(3,763,777.26,'vqjrp',355,169.58,'jnwpl');
INSERT INTO tab0 VALUES(4,925,793.34,'irlrv',131,517.38,'tzczq');
INSERT INTO tab0 VALUES(5,688,675.30,'ebxej',302,242.88,'dunwk');
INSERT INTO tab0 VALUES(6,244,132.20,'vwfmh',533,629.36,'cnoua');
INSERT INTO tab0 VALUES(7,762,934.85,'hmzfr',530,842.49,'gxohz');
INSERT INTO tab0 VALUES(8,516,544.4,'tqtme',164,548.43,'pfoxu');
INSERT INTO tab0 VALUES(9,7,737.59,'hpnbs',599,539.96,'kgygk');
SELECT ( + - col1 ) AS col4 FROM tab0 AS cor0 WHERE NOT - col1 * + col0 + - - ( col1 ) + + col3 IS NOT NULL;

This is supposed to return nothing, but instead returns -544.4.

The underlying issue is that SQLite calls sqlite3IsNaN(-280366.000000) at one point, which turns true instead of false, and therefore it goes down the wrong code path. However, if I revert a 2019 checkin that offers a “Smaller and faster sqlite3IsNaN()”, it works. The code itself is a bit beyond my understanding so I’m not sure why this has fixed it.

Even more interestingly, if I separate the code off into its own file (nantest.c) then it works as expected! Can anyone shed any light on this?

I don’t really want to modify the core sqlite3.c file if I can help it, so I’d love to understand what’s going on here.

Smaller and faster sqlite3IsNaN()

The reason the smaller/faster version doesn’t work is because the order of words in an FPA double is the opposite way round to that of the words in a u64, which the memcpy() isn’t respecting. That’s why the high priests of ANSI gave us isnan() which uses the FPA classification function in the C library which gets the word order the right way round.

I’m also not convinced when you say

sqlite3IsNaN(-280366.000000) at one point, which turns false instead of true

as -280366 looks pretty number-y, and not at all nan-y to me.

I’m also not convinced

You’re quite right! Post updated to match reality :)

Thanks for the explanation.

Upon further investigation, I’ve had to add a new entry to the Known Issues list.

Databases containing floating point values do not conform to the SQLite spec and are only compatible with the RISC OS version of SQLite.

Hopefully this will be something I can fix, but it probably won’t be any time soon.

I expect to release Alpha 4 shortly, which makes use of isnan() and fixes the immediate bug I reported above.

Alpha 4 is now available. This is a milestone release, as SQL Logic Test now passes in its entirety.

I forgot to update the BuildNotes text file in the source archive: sqlite3-in is documented as being an untouched copy from SQLite 3.36. This is not correct, as it contains the isnan() fix mentioned above. This fix should be included in SQLite 3.37 so a hand-edited file should no longer be required in the future.

Hopefully this will be something I can fix, but it probably won’t be any time soon.

It ended up being trivial. So, hot on the heels of alpha 4 comes alpha 5. Details in the first post. Please note that this version will not correctly read databases created with alpha 4. If you need to retain existing data then you should open your database with alpha 4, .dump your data to a text file, and then open alpha 5 and .read the data into a new database.

should work back to RISC OS 3.1

While some simple actions (creating a DB, adding data, reading it back) are successful under 3.1, some of the test scripts are failing under ArcEm when using HostFS. I don’t yet know whether this is a SQLite issue, or an ArcEm issue. I also tried using ADFS under ArcEm but this crashed.

The SQLite tests all pass under OS 3.6, but at this stage I’m not ‘certifying’ the port on earlier OS versions.

I tried running under Arculator on Windows 10 but couldn’t figure out how to get the mouse working. There don’t seem to be any settings for it and Google didn’t return anything useful! I can see the mouse pointer, but it moves in steps of ~64 pixels and therefore I can’t click anything on the icon bar as the pointer just skips right past it. Has anyone else run into this?

some of the test scripts are failing under ArcEm when using HostFS

I can now confirm that this is an ArcEm/HostFS issue. I’ve only run a limited number of tests under 3.1 but it’s now returning correct results when using ADFS :)

Nice!

BTW Chris, would it be ok if I upload both the HTTPLib and the SQLite ports on the RISC OS Community on GitHub? I am working on create a cross platform build (that runs on RISC OS too) to build code that also pulls down dependencies. Given that I am using your HTTPLib and the SQLite port for a couple of projects it would be nice to have the source there (mostly handy).

Thanks for this effort BTW, really cool to see SQLite working on RISC OS 3.1 as well!

That’s fine, but please put a note on there making it clear that that’s not their official home.

One of these days I need to finish off HTTPLib…

thanks and of course I’ll make sure it’s displayed that those are only mirror repositories, thanks a lot! :)

Alpha 6 of SQLite for RISC OS is now available from the usual place, based on SQLite 3.37. There are no RISC OS-specific changes.

If you’re building from source, please note that the “amalgamate” utility has been updated, and that the new version is required when using 3.37. It’s also compatible with 3.36.