SQLite Forum

SQLite 3.33.0 beta-1
Login

SQLite 3.33.0 beta-1

(1.1) By Richard Hipp (drh) on 2020-08-07 20:20:39 edited from 1.0 [link] [source]

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 and see if you can break it. We'd prefer that you report bugs before the release rather than afterwards.

Other links:

(2) By Simon Slavin (slavin) on 2020-08-08 12:56:35 in reply to 1.1 [source]

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.


integrity_check(TABLENAME) checks for errors only in the named table and indexes of that table.

integrity_check with no arguments does the same as iterating through all tables using integrity_check(TABLENAME) but also detects all the other errors listed above.

integrity_check(N) 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 sqlite_stat1 table, unless the database has been opened with r/o permission ?

In addition can ANALYZE delete stat1, stat2, stat3 and stat4, before it starts compiling new tables ? My logic is that if the programmer has specifically chosen to use ANALYZE they are explicitly stating that they don't care about any old data.

(3) By Keith Medcalf (kmedcalf) on 2020-08-08 14:57:31 in reply to 2 [link] [source]

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".

(10) By Simon Slavin (slavin) on 2020-08-10 09:54:48 in reply to 3 [link] [source]

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.

(4) By anonymous on 2020-08-08 20:23:37 in reply to 1.1 [link] [source]

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.

(5) By Tim Streater (Clothears) on 2020-08-08 20:48:47 in reply to 4 [link] [source]

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.

(6) By anonymous on 2020-08-08 22:23:06 in reply to 4 [link] [source]

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.

(7) By Simon Willison (simonw) on 2020-08-08 23:54:16 in reply to 1.1 [link] [source]

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?

(8.1) By Richard Hipp (drh) on 2020-08-09 00:11:01 edited from 8.0 in reply to 7 [link] [source]

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.

(9) By anonymous on 2020-08-10 05:43:02 in reply to 8.1 [link] [source]

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 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).

(11) By Richard Hipp (drh) on 2020-08-10 10:13:56 in reply to 9 [link] [source]

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 248 seems to be an upper limit on the size of the database file using the current file format.

(12) By anonymous on 2020-08-10 18:21:23 in reply to 11 [link] [source]

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.

(13) By anonymous on 2020-08-12 04:49:48 in reply to 1.1 [link] [source]

The "box" mode should use VT100 line drawing characters instead of Unicode if the locale does not specify "UTF-8".

(14) By anonymous on 2020-08-20 23:35:12 in reply to 1.1 [link] [source]

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. 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.