SQLite 3.33.0 beta-1
The 3.33.0 release of SQLite is schedule to occur in about two weeks. Now would be a great time for you to download the latest [prerelease snapshot](https://sqlite.org/download.html) and see if you can break it. We'd prefer that you report bugs before the release rather than afterwards. Other links: * [Summary of changes](https://www.sqlite.org/draft/releaselog/3_33_0.html) * [All 223 changes that are in the 2020-08-07 prerelease snapshot](https://www.sqlite.org/src/timeline?p=trunk&bt=version-3.32.0&n=all) * [The 3.33.0 checklist](https://www.sqlite.org/src/ext/checklist/top/index) (When this checklist goes all green, we will cut the release.)
Item 3, in the documentation refers to <https://www.sqlite.org/draft/pragma.html#pragma_integrity_check>. Because the features of that PRAGMA were introduced 'backwards', the documentation looks a little strange. I came up with this, though there may be problems with that too. ___ <code>integrity_check(TABLENAME)</code> checks for errors only in the named table and indexes of that table. <code>integrity_check</code> with no arguments does the same as iterating through all tables using <code>integrity_check(TABLENAME)</code> but also detects all the other errors listed above. <code>integrity_check(N)</code> does the same as the option with no arguments, but stops checking once N errors have been found. This allows a faster check if you are interested just in whether the database file does, or does not, have faults. ___ Can the function described in 7b please delete the dodgy <code>sqlite_stat1</code> table, unless the database has been opened with r/o permission ? In addition can <code>ANALYZE</code> delete stat1, stat2, stat3 and stat4, before it starts compiling new tables ? My logic is that if the programmer has specifically chosen to use <code>ANALYZE</code> they are explicitly stating that they don't care about any old data.
> This allows a faster check if you are interested just in whether the database file does, or does not, have faults. This is incorrect. If the database has no errors in it, then specifying a value of N (any value) does not make any difference in the length of time that will be consumed to determine that no errors exist. The value of N limits the number of errors reported, so in a database that contains multiple errors the value of N does not have any effect on how long it takes to check that database for errors, merely once having found that there are errors, how many of those errors are reported. While it is quite possibly true that for a database containing 47 errors which takes 5 minutes to scan and detect all of them, that you might be done reporting 5 errors after only 3 minutes, the number N does not "allow a faster check" -- it merely sets the number of error messages after which one "throws up hands in disgust".
In `.mode box`, which is very welcome addition, if the field contains multiple lines (line feeds, either with CF/LF or just LF) the format is messed up. Example: ``` create table t(a,b); insert into t values('Hello'||char(10)||'World','Hello World'); insert into t values('Two lines','Two'||char(10)||'lines'); .mode box select * from t; ``` outputs: ``` ┌─────────────┬─────────────┐ │ a │ b │ ├─────────────┼─────────────┤ │ Hello World │ Hello World │ │ Two lines │ Two lines │ └─────────────┴─────────────┘ ``` instead of ``` ┌─────────────┬─────────────┐ │ a │ b │ ├─────────────┼─────────────┤ │ Hello │ Hello World │ │ World │ │ │ Two lines │ Two │ │ │ lines │ └─────────────┴─────────────┘ ``` In other words, for text affinity fields, I would expect the second line of each field to appear in the same column, and the remaining columns which have no corresponding content to be blank. If that's not possible for now, how about truncate the content on the line feed character(s), and assume that as its length, and show only the first line followed perhaps by `...` to let one know there's more. This will at least not mess up the display.
Sounds to me like an unreasonable ask. Your "instead of" is actually four results' worth of output - or looks like that, anyway. And nothing stops you downloading the source of the CLI and applying appropriate modifications.
>truncate the content on the line feed character(s), and assume that as its length, and show only the first line followed perhaps by ... to let one know there's more. This seems like a simpler solution that also avoids the potential confusion about the number of rows displayed.
The changelog mentions an increase in the maximum database size from 140TB to 281TB. Out of interest: are there SQLite users out there who have run into the 140TB size limit?
This change was in response to a customer request. They still have a factor of 4 before reaching the old upper limit, but asked for additional headroom. Note that the 281 TB limit is only reachable when using a 65536-byte page size. For the more common 4096 page size, the size limit is raised from 8.8 TB up to 17.5 TB by this enhancement. We do not believe further size increases are possible without an incompatible file format change.
> We do not believe further size increases are possible without an incompatible file format change. I think it may be possible. The [file format documentation](https://sqlite.org/fileformat2.html#page_size) says, for the page size, "This value can be interpreted as a big-endian 1 and thought of as a magic number to represent the 65536 page size. Or one can view the two-byte field as a little endian number and say that it represents the page size divided by 256." If you use the little endian interpretation, then you could increase the maximum page size up to 8388608 bytes (I think).
My text is correct. It allows a faster check, in some situations. It does not make every check faster. But perhaps my text should be altered to make it more clear.
There are other parts of the file format that record an offset into the page which is only 16-bits in size. And there are places that record the page number using 32-bits. So 2<sup>48</sup> seems to be an upper limit on the size of the database file using the current file format.
> There are other parts of the file format that record an offset into the page which is only 16-bits in size. O, yes, I forgot about that.
The "box" mode should use VT100 line drawing characters instead of Unicode if the locale does not specify "UTF-8".
the new output options are very welcome - working from the sqlite prompt becomes more pleasant. One feature is missing though to get comfortable - using a user provided PAGER like more/less. The latter having screen overflow and non-wrap features [less -SniFX](https://explainshell.com/explain?cmd=less+-SniFX). Right now `.output` gives you the option to either provide a filepath to write to `stdout`. Optionally piping to the env set PAGER and/or interactively setting `.pager` would be a dream. See https://stackoverflow.com/a/55072741/7010943 for context.