SQLite Forum

Well, ln and log_e are equivalent notations in mathematics for the natural logarithm. And in math textbooks you frequently find lg as an equivalent for log_10 resp ld as an equivalent for log_2. Personally, I'm fine with all these notations.

I recommended to use LN, LOG10 and LOG2, because those are the names chosen in the SQLite Math Extension for the variants of the logarithmic function, and because those names are the most common in other database systems, too. Actually, I haven't come across any database system implementing LOGE for the natural logarithm up to now. However, it would be a one-liner to add LOGE to the SQLite Math Extension.

It is a bit unfortunate that there isn't a common definition for the logarithmic function upon which all database systems agree - for mathematicians the base-e logarithm (LN) is the only natural logarithm, for computer scientist the base-2 logarithm (LOG2) is the only natural logarithm.

PostGreSQL uses indeed LOG for the base-10 logarithm (see PostGreSQL documentation, while MySQL and SQL Server both use LOG for the natural logarithm. Oracle also uses LOG for the natural logarithm.

AFAIK the latest SQL standard defines the name LN for the natural logarithm, but no names for other logarithms.

Fortunately, the SQLite Math Extension implements LN, LOG10, and LOG2 in addition to LOG. So, my recommendation would be:

"Be explicit and use LN, LOG10, or LOG2 depending on the type of logarithm you need. Don't use LOG unless you need to explicitly specify the base."

Note: This does not relieve you from checking and potentially adjusting the SQL code when moving from one database system to another.

I guess that then the math functions of extension-functions.c override those that have been added to the Sqlite core in version 3.35 and higher.

Yes, and usually this does no harm, because most functions with identical names deliver identical results.

Presumably there is no significant difference between them, and this is fine.

Unfortunately there is a small subtle difference: the log function computes log10 (base 10 logarithm) in the SQLite Math Extension, but ln (natural logarithm) in the Extension Functions. For a full comparison see SQLite Math Extension vs Extension Functions.

For my own project I adjusted the code of the Extension Functions module to peacefully coexist with the new SQLite Math Extension, favoring the latter implementation, if both extensions are enabled.

Your HAVING clause "having AgeG between 19 and 97" causes this behaviour: AgeG is not an integer, but a string as a result from the typeof function. Therefore the HAVING clause is not fulfilled for any selected row, and you get an empty result set. Simply leaving out the HAVING clause should give you the expected result.

It seems that always the x value from the last line of the CSV file is returned for SELECT MAX(x) FROM text;. If you change the SQL command to SELECT MIN(x) FROM text;, you get the same result as for the MAX function.

Obviously, there is something going wrong on evaluating the aggregate function MIN or MAX.

DRH and the SQLite developer team decided in February to remove the undocumented and officially unsupported SQLITE_HAS_CODEC feature (see DRH's response to a similar question a few days ago). SQLite version 3.32.x was the first released version affected by this change.

System.Data.SQLite 1.0.113.1 is based on SQLite version 3.32.1, and is therefore also affected.

Taking into account that the SQLITE_HAS_CODEC feature was around in the SQLite code base for about 15 years or so, it was a bit unfortunate that its removal was not even mentioned in the release notes. However, for undocumented features one has to live with such decisions, unfortunately.

Of course, I have customers using the existing encrypted db format, so I have to make sure that any VFS edition of the encryption is 100% backward compatible.

You might be interested in what is going on in my GitHub account. :-)

You're exactly right: each page has a different nonce for encryption, so the CODEC interface was just perfect.

This makes a VFS implementation of your encryption layer slightly more difficult ... but not impossible.

I would vastly prefer not to have to backport the CODEC interface or manually merge upstream changes to SQLite, but if DRH isn't going to provide the interface any more it looks like I have no choice.

I doubt that the SQLITE_HAS_CODEC encryption API will ever come back. IMHO the removal of that API indicates that most likely the "official" commercial encryption extension offered by DRH (see SEE) has been reimplemented based on a different API (VFS or something else). From DRH's point of view this decision makes it certainly easier for the SQLite developer team to adopt new modern features in the future. That also means that supporting the SQLITE_HAS_CODEC API manually for future versions of SQLite will become more and more cumbersome over time.

In principal, the VFS interface should be sufficient to implement your own encryption layer. However, the devil could be in the details - it heavily depends on the requirements of your encryption layer.

Most likely, the main problem will be to reliably identify which database page is read or written, because the VFS interface is rather low-level and provides for example only information about offset and size of a chunk to be read or written, but no information about the page number (which most encryption extensions need to know, but maybe yours is an exception).

Another critical issue could be the handling of attached database files. The ATTACH command had an undocumented additional keyword, namely KEY, for specifying the encryption key for the attached database. Most likely, support for this undocumented keyword has also gone.

You are certainly not the only one affected by this change. For example, there is the SQLCipher project. Sooner or later they will have to tackle this problem, too. And if you are lucky, they will make their solution publicly available.