Description
An incompatibility has been discovered between the Status client database created/managed by status-go and the equivalent database created/managed by nim-status.
This issue explores the nature of the problem and proposes a workaround. It also retraces a past problem (linking order) that wasn't well understood at the time but is better understood now.
When writing tests for nim-status, it was previously discovered that the linking order of status-go (compiled and linked as a shared library) relative to libsqlcipher.a (from vendor/nim-sqlcipher) could result in unexpected behavior re: the encoding and decoding of boolean values in the database.
More specifically, on Linux/macOS if status-go precedes (left-to-right) libsqlcipher.a in the linking order then the SQLCipher embedded in status-go will be used by the compiled nim-status code instead of libsqlcipher.a. The same is experienced on Windows but with the linking order reversed. The problem is addressed by varying the linking order relative to the detected OS.
But what's the underlying cause of the unexpected behavior for boolean values?
nim-status consumes nim-sqlcipher, which builds the latest release of SQLCipher (v4.4.2) and provides a Nim API wrapper. SQLCipher v4.4.2 is a superset of SQLite v3.33.0. Note that linking the built libsqlcipher.[a|dll|dylib|so] into a final executable is the responsibility of nim-sqlcipher's consumer.
status-go embeds SQLCipher by way of a somewhat dated version of go-sqlcipher (see go.mod#L47) that corresponds to SQLCipher v3.4.2, which is a superset of SQLite v3.20.1.
From SQLite's docs:
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
Beginning with SQLite 3.23.0 (2018-04-02), SQLite recognizes the identifiers "TRUE" and "FALSE" as boolean literals... The boolean identifiers TRUE and FALSE are usually just aliases for the integer values 1 and 0, respectively.
status-go's embedded SQLCipher/SQLite is old enough that it does not "recognize the identifiers 'TRUE' and 'FALSE' as boolean literals". The effect is that when a boolean literal is used in a SQL statement executed by status-go (e.g. in a migration) the value will be encoded as text instead of an integer. SQLite tolerates values that don't match the declared column type and status-go/go-sqlcipher handle text/int boolean decoding consistently.
A problem arises when nim-status/nim-sqlcipher is used to decode boolean values from a database created/managed by status-go: nim-sqlcipher's API expects that boolean columns will (properly) contain only integer values (0/1) such that decoding "text bools" written by status-go results in a runtime exception. Note that this problem is related to the "linking order" problem described earlier but arises independently of it, i.e. it's a problem even if the linking order is correct.
In order for nim-status to concurrently access a database created and managed by status-go (such an arrangement is used in an experimental branch of status-desktop), nim-status or nim-sqlcipher needs a "compatibility behavior" for boolean values.
At present it's not clear how to situate that change in nim-status, so it's been introduced in a branch on nim-sqlcipher and there is a corresponding PR for nim-status.
Another possibility is updating status-go's version of go-sqlcipher to be on par with nim-sqlcipher (@cammellos). However, see the Incompatibilities section of go-sqlcipher's README:
go-sqlcipher does not implement any migration strategies at the moment. So if you upgrade a major version of go-sqlcipher, you yourself are responsible to upgrade existing database files.
Given the planning and testing that such an update would involve, I don't expect it to be a near-term solution. In the meantime, a compatibility behavior will be maintained in nim-status/nim-sqlcipher.