v0.9.0

Version 0.9.0 of the SQLite/CE suite is a major infrastructural update that has touched every single binary in the package, with some critical bugfixes and enhancements to the existing interfaces and features. 0.9.0 also features a new application to replace SQLite/CE Test: SQLite/CEbench, a new benchmarking and validation tool that offers more granular timing and categorization of tests, as well as many new test cases. All applications in the SQLite/CE suite now also have proper versioning with their own independent versions and conventions.

SQLite/CEbench

The SQLite/CE Test harness application has been refactored and renamed to SQLite/CEbench, implementing a number of new validation test cases as well as more performance-focused tests with more granular timing per operation. SQLite/CEbench also categorizes tests to report on their timing specifically. As a benchmark, SQLite/CEbench is still quite rudimentary and will be reworked as development progresses, but it can still be a useful tool for cross-device validation and performance testing.

UI enhancements

SQLite/CE 0.9.0 includes several further enhancements to the SQLite/CEdit user interface and experience, most notably with the addition of a full screen view mode which hides the status bar and command bar to maximize screen utilization in the query editor, results view and Schema Explorer. Full screen view can be toggled at any time using Alt+Enter or F11 on CE devices with function row keys. Hitting Escape while in full screen mode will also exit it.

Other enhancements include:

• Toggleable status bar
• Ctrl+N shortcut to start a new query and clear the existing buffer with save confirmation, with smart rebinding behavior to create a new database on initial launch
• Queries returning no rowsets will now automatically default to text output mode to display messages if Grid View is enabled otherwise, and the grid view button will be disabled for no-row returns.

Database restore

SQLite/CEdit 0.9.0 can now restore database backups from the File menu, and automatically seeks to your defined backup path for the current database if it exists.

Critical library bug fixes

During the development of SQLite/CEbench's new math test cases, a set of serious bugs were discovered in the ported library's ABS and ROUND functions due to oversight in the reimplementation of sprintf(), which is not available on Windows CE, when the library was first ported. The original port featured a minimal interface-compatible implementation of sprintf() that did not actually fully handle all arguments, with the agent only implementing enough of a subset to pass the (at the time) small set of basic test cases we had built to validate basic functionality. A valuable lesson in the risks of AI-assisted coding, and why it is important to be thorough and appropriately steer and double-check agent work even if it seems to function on the surface.

While the two bugs found were not serious enough to impact most basic operations (hence why they went undiscovered until work began on the ninth release) they can severely impact more advanced SQLite databases and queries that use ROUND() or ABS().

Two bugs in the SQLite library port have been fixed in this release:

ROUND() producing incorrect results

SQLite's ROUND function relies on the C standard library sprintf() function to round a number, passing the input value in with a precision specifier and receiving sprintf()'s rounded output, which is then returned. In our early implementation of ce_vsprintf(), the function, while it did have rounding logic, would parse and store the precision specifier but not actually use it in operations, with the agent instead hard-coding it to always use the default precision of 6 decimal places regardless of what was actually specified. To fix this, ce_vsprintf() was enhanced with proper handling of the parsed precision for %f/%g, which ensured it was passed into the otherwise already present rounding logic.

ABS() heap corruption with multiple calls

The more serious bug of the two discovered involved test cases where the SQLite ABS() function is called more than once in succession, particularly if ABS() is called on a negative number, and then a positive number immediately afterwards. Under the previous version of the library, this would cause the front-end application to hang and become unresponsive until closed, and even lock the library file and itself, preventing the files from being deleted or moved until the device was reset.

The root cause of this behavior was - you guessed it - partial implementation of ce_vsprintf(), which in this case was ignoring precision, but in a different, more dangerous way than ROUND() was. When presented with a string like ABS(-3) + ABS(6), SQLite's tokenizer uses sprintf() to "extract" 3 from the first call, prepending it with - in the format and using a precision specifier starting at its index to copy it and only the next character (a null terminator) into a pre-allocated 3-byte buffer. Because ce_vsprintf() was ignoring precision, it instead copied 3) + ABS(6) into the buffer, overrunning it and corrupting the memory beyond. The fix was the same as for ROUND(): fully implementing precision in ce_vsprintf().

Internal refactoring

SQLite/CEdit 0.9.0 has been reworked substantially with unified functions for managing interface control states for more consistent behavior, as well as centralized management of keyboard shortcuts that apply to all views. Keyboard shortcuts for specific views are still handled explicitly within the relevant controls and subclasses. This work should reduce potential interface state and keyboard shortcut bugs in future releases. A new standard function for passing text messages to the text results pane has also been implemented to be leveraged by more system-level operations in future releases.

SQLite/CEdit's dynamic context menu system has been significantly refactored in this release to fix a long-standing bug that prevented it from functioning in the CE 2.0 x86 emulator environment, which appears to have a longstanding bug causing the standard RemoveMenu() function to corrupt internal state when invoked on a menu directly attached to the command bar. The new dynamic context implementation completely destroys, rebuilds and reattaches the top-level menu and redraws the command bar with every view switch, reattaching the existing submenus afterwards.

Binaries

This release includes both SH3 and MIPS binaries for Windows CE 2.0 systems. SH3 binaries are tested on an HP 620LX and an HP 360LX, both running CE 2.0. MIPS binaries are currently tested on an NEC MobilePro 790 running CE 3.0, as I lack an earlier CE 2.0 MIPS system to test with, but this package is expected to be reliable on such devices.