SQLite Forum

Thanks, it looks interesting. I could perhaps use it instead of my own unpublished, less complete array extension. Perhaps you can clarify:

• is endianness observed? Meaning, that a binary copy of the database file with blobs storing the arrays will also work on a computer with a different endianness than the one where the database was generated?

• what is the storage overhead, which could be significant for small arrays, e.g. representing 2d points (latitude, longitude).

• extend functionality to 32 bit float arrays? 32 bit resolution is often fully sufficient for observations by physical sensors, saving up to almost 50 % storage space. In some image processing even 16 bit floats are used.

I don't have a direct answer to the questions, but if power failures are a concern, and you want to do explicit transactions, why not implement the circular buffer as a normal table, one row per record? Then everything is well defined and more portable. See for example this thread.

If write performance has the highest priority (at the expense of data integrity in case of power failures), then I would use a normal file and unbuffered write/read or buffered fwrite/fread, see here for a comparison. I.e. no database engine in between.

The upcoming STRICT tables have already been discussed in several threads. I think it is a useful feature. For example, when processing a SELECT from a STRICT table, the code does not necessarily need to check the column type in each row and be prepared for all eventualities. How do I know whether a table is STRICT? Well, by parsing the create statement in sqlite_schema, but it would be nice to have an easier method.

Obviously sqlite_schema could get another column to indicate whether a table is strict or not, but this may not be feasible or the best. BTW, an easy indicator whether a table is with or WITHOUT ROWID would also be nice.

My wish list for SQLite shell enhancements:

1. Before the SQLite shell introduced its own tab completion, readline's tab would come up with suggestions for file names. This was quite useful, especially for ".read ..." etc. The SQLite shell completion (it suggests tables, columns etc.) is of course also useful, but the best would be to have both. Can file name completion be put back (as part of the SQLite shell).

2. Other shells that I use (bash, ohmyrepl, ...) become greatly more powerful when embedding fuzzy search [https://github.com/junegunn/fzf] into the shell, especially for command history. (Fuzzy search is kind of competitor to SQL search :). This would be great to have also in the SQLite shell. I often want to reuse complicated SELECTs, but they have dropped out from the command history or are difficult to find "way back". With fzf, integrated into the SQLITE shell, I could much more easily find commands also in very long history.

Sorry for semi-hijacking the thread again, this is only partially related to embedding the shell.

Copying in memory might in this case be avoidable to some extend by using SQLite's incremental BLOB I/O.

Though there still seems to be room for further optimization: The SQLite API could provide

• a function sqlite3_blob_write_from_io (or other name) where a file descriptor is passed to SQLite. The function can then retrieve the data directly from the file into its memory, or,

• provide a pointer to its internal buffer and allow the user to fill it (using read(fd, ...) if it is directly from I/O represented by a file descriptor).

It seems to me that there is potential to boost insert performance in this case and perhaps many others.

The OP is using an existing SQLite db. The problem came up when this db was created as already discussed in several of the replies, and fixes were suggested.

If, however, the db cannot be obtained in some corrected form, then the only way would be to try to fix when SELECTing from the db. typeof to indicate when the undesired conversion to numeric had occured and printf would convert back to the 3-4 digit airport code. But this seems ambiguous: For example 200000 was probably '2E5' (Dell City Municipal Airport), but could also have been '20E4'. The latter does not seem to exist as an airport code, so the database entry should be updated to '2E5'. But whether an ambiguity can be resolved in all cases is uncertain. And even if ambiguities could be resolved programming the update in SQL would be quite a project and involve another Sqlite database of all existing airport codes. Would this be available?

Ok, then I guess it made sense that Sqlite adopted the way of other database systems in this respect.

Thanks, this is a useful comparison that I was not aware of.

The C standard math library, Matlab, Python, Julia, ... all return the natural logarithm for log, i.e. log(10) -> 2.302585092994046. Does the SQL standard mandate anything different? If not the choice of the SQLite Math Extension seems a bit unfortunate to me (and sorry for not having adviced in time).

extension-functions.c include statistical stuff stdev, variance, mode, median, lower_quartile, upper_quartile. I have good use of these, and cannot miss them. Separately compiling extension-functions.c to a shared object and loading it works without problems for me.

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. Presumably there is no significant difference between them, and this is fine.

Computing systems like Matlab, Python, Julia, ... do raise an exception for expressions like log(-1) even if returning a complex value in principle would be an option (one needs to use log(-1+0i) to avoid the exception). But these systems do also provide mechanisms to catch exceptions. The SQL standard is short-sighted in the sense that if raising exceptions is specified then also an SQL catch mechanism should have been provided. Therefore I find a non-compliance of Sqlite here acceptable.

Mathematical analytic continuation is not very useful if the value in question represents a real world parameter such as a density or temperature. When measuring such parameters, impossible negative values can occur for various reasons (instrumental offsets, noise, ...) and must be dealt with in the data processing. A good strategy is to check the value before functions like log are called, and then conditionally do something appropriate.

Yes, the precision of calls to the system clock is typically significantly better than milliseconds, and the "maximum possible precision" would be even more digits than YYYY-MM-SSTHH:MM:SS.SSS . Details depend of course on the hardware.

A format like YYYY-MM-SSTHH:MM:SS.SSS suggests to me as a physicist that it is an absolute time value, and I expect the number of digits to roughly represent the accuracy of this value. For time differences (where precision rather than accuracy is the issue) it is a rather clumsy format. For example, in Sqlite julianday('now') would be suitable for differences. But I guess that this is even less standard than CURRENT_TIMESTAMP across different database systems.

The accuracy and precision of timestamps depends on the hardware and how the computer is connected to the reference time, irrespective of what the database system prints. At the home office a PC with configured NTP via WAN/internet the seconds are probably reliable, but not 100 ms. YYYY-MM-DD HH:MM:SS would be adequate. Higher accuracy is possible within a LAN where an accurate NTP server is running (universities, ...), in a mobile phone having GPS and an OS syncing to the system clock to the GPS reference, or with other special arrangements for time synchronization.

Even if a database system prints CURRENT_TIMESTAMP as YYYY-MM-DDTHH:MM:SS.SSS, this is not guaranteed to be a timestamp with millisecond accuracy and actually unlikely to be the case.

UT1 is also off from UTC by up to 900 milliseconds. If for example a timestamp is obtained as SELECT datetime('now'); then this is UTC (assuming the clock of the computer, or smartphone is synchronized with a standard configured NTP client). To convert it to UT1 one can

1. download from https://www.iers.org/IERS/EN/Science/EarthRotation/UT1LOD.html the Earth rotation data which include the preliminary and final DUT1 (UT1-UTC) and correct the UTC timestamp for the offset.

2. in the US register at the NIST, https://www.nist.gov/pml/time-and-frequency-division/time-services/ut1-ntp-time-dissemination, and configure the NTP client such that it synchronizes instead to the NIST UT1 NTP server.

Both are quite tedious, I doubt that many Sqlite users are having datetime in "UT1 Zulu time".

The Sqlite documentation is correct:

"Universal Coordinated Time (UTC) is used."

"It is also assumed that every day is exactly 86400 seconds in duration."

This is together is not the same as using UT1