Checksums use CPU cycles. SQLite will do checksums with an extension (https://sqlite.org/cksumvfs.html) but that is off by default since an overwhelming majority of developers are more interested in day-to-day…
SQLite file format spec: <https://sqlite.org/fileformat.html>
SQLite allows multiple writers. The constraint is that only one of how writers can be actively writing at any moment in time. If there are multiple processes wanting to write, they take turns. SQLite prevents two or…
Checking the datatype is not the same as validating. There is lots of data out there that is invalid, and yet still has the correct type. In fact, that is the common case. I dare say you will be hard pressed to find a…
No, I think that people can use SQLite anyway they want. I'm glad people find it useful. I do remain perplexed, though, about how people continue to think that rigid typing helps reliability in a scripting language…
Flexible typing works really well with JSON, which is also flexibly typed. Are you familiar with the ->> operator that extracts a value from JSON object or array? If jjj is a column that holds a JSON object, then…
If an I/O error happens with read()/write(), you get back an error code, which SQLite can deal with and pass back up to the application, perhaps accompanied by a reasonable error message. But if you get an I/O error…
That depends on the query. SQLite tries to use LIMIT to restrict the amount of reading that it does. It is often successful at that. But some queries, by their very nature, logically require reading the whole input in…
Website updated with more precise wording. Sorry for the confusion.
No, Simon, we don't "refuse". We are just very selective and there is a lot of paperwork involved to confirm the contribution is in the public domain and does not contaminate the SQLite core with licensed code. Please…
SQLite runs about 5 times faster compiled with GCC (13.3.0) than it does when compiled with FIL-C. And the resulting compiled binary from GCC is 13 times smaller.
The top-level routine is here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6767-6818>. Small (32-bit) integer literals are compared numerically, here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6526>. They don't have…
Yeah, but who ever writes "x=0.9" as a constraint on a partial index? Really? Don't you know you aren't suppose to compare floating point quantities for equality? If P is the expression on the partial index and Q is the…
I think (I hope!) we are probably done adding keywords to SQLite. Furthermore, all of the more recently added keywords (ex: WITHIN, RETURNING, MATERIALIZED) make use of special capabilities in SQLite's parser that…
> Is there an SQL injection attack vulnerability there? No, at least not if you put the SQL inside of {...}, which IIRC the documentation strongly recommends. The $uid is passed down into SQLite. It is a single token…
The paper is from 2017. Fossil got chat support in 2021 and the developers now use Fossil-chat. https://fossil-scm.org/home/doc/trunk/www/chat.md Fossil chat has the advantages that (1) it is fully encrypted and (2) it…
> Why not just have the value be the base 2 logarithm of the page size, i.e. a value between 9 and 16? Yes, that would have been a better choice. Originally, the file format only supported page sizes between 512 and…
> Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. I don't remember ever saying that. Rather, see…
> sqlite in its default (journal_mode = DELETE) is not durable. Not true. In its default configuration, SQLite is durable. If you switch to WAL mode, the default behavior is that transactions are durable across…
Update: This enhancement is now on trunk and will be included in the 3.50.0 release of SQLite due out in about four weeks.
I just checked in an experimental change to sqlite3_rsync that allows it to work on non-WAL-mode database files, as long as you do not use the --wal-only command-line option. The downside of this is that the origin…
I claim the edit is neither a hardening nor a softening but rather a clarification and an attempt to better explain the original intent.
SQLite supports indexes on expressions. So if you have an expression that accesses some subcomponent of JSON that you want to index, just create an index on that expression.
Seeing this pop up on HN prompted me to review the 13-year-old document, and bring it up to date with the latest enhancements to SQLite. Specifically, three ways for safely backing up a live SQLite database are now…
Author here: My title was imprecise and unclear. I didn't mean that you should raise errors if CRLF is used as a line terminator in (for example) HTTP, only that a bare NL should be allowed as an acceptable line…
Checksums use CPU cycles. SQLite will do checksums with an extension (https://sqlite.org/cksumvfs.html) but that is off by default since an overwhelming majority of developers are more interested in day-to-day…
SQLite file format spec: <https://sqlite.org/fileformat.html>
SQLite allows multiple writers. The constraint is that only one of how writers can be actively writing at any moment in time. If there are multiple processes wanting to write, they take turns. SQLite prevents two or…
Checking the datatype is not the same as validating. There is lots of data out there that is invalid, and yet still has the correct type. In fact, that is the common case. I dare say you will be hard pressed to find a…
No, I think that people can use SQLite anyway they want. I'm glad people find it useful. I do remain perplexed, though, about how people continue to think that rigid typing helps reliability in a scripting language…
Flexible typing works really well with JSON, which is also flexibly typed. Are you familiar with the ->> operator that extracts a value from JSON object or array? If jjj is a column that holds a JSON object, then…
If an I/O error happens with read()/write(), you get back an error code, which SQLite can deal with and pass back up to the application, perhaps accompanied by a reasonable error message. But if you get an I/O error…
That depends on the query. SQLite tries to use LIMIT to restrict the amount of reading that it does. It is often successful at that. But some queries, by their very nature, logically require reading the whole input in…
Website updated with more precise wording. Sorry for the confusion.
No, Simon, we don't "refuse". We are just very selective and there is a lot of paperwork involved to confirm the contribution is in the public domain and does not contaminate the SQLite core with licensed code. Please…
SQLite runs about 5 times faster compiled with GCC (13.3.0) than it does when compiled with FIL-C. And the resulting compiled binary from GCC is 13 times smaller.
The top-level routine is here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6767-6818>. Small (32-bit) integer literals are compared numerically, here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6526>. They don't have…
Yeah, but who ever writes "x=0.9" as a constraint on a partial index? Really? Don't you know you aren't suppose to compare floating point quantities for equality? If P is the expression on the partial index and Q is the…
I think (I hope!) we are probably done adding keywords to SQLite. Furthermore, all of the more recently added keywords (ex: WITHIN, RETURNING, MATERIALIZED) make use of special capabilities in SQLite's parser that…
> Is there an SQL injection attack vulnerability there? No, at least not if you put the SQL inside of {...}, which IIRC the documentation strongly recommends. The $uid is passed down into SQLite. It is a single token…
The paper is from 2017. Fossil got chat support in 2021 and the developers now use Fossil-chat. https://fossil-scm.org/home/doc/trunk/www/chat.md Fossil chat has the advantages that (1) it is fully encrypted and (2) it…
> Why not just have the value be the base 2 logarithm of the page size, i.e. a value between 9 and 16? Yes, that would have been a better choice. Originally, the file format only supported page sizes between 512 and…
> Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. I don't remember ever saying that. Rather, see…
> sqlite in its default (journal_mode = DELETE) is not durable. Not true. In its default configuration, SQLite is durable. If you switch to WAL mode, the default behavior is that transactions are durable across…
Update: This enhancement is now on trunk and will be included in the 3.50.0 release of SQLite due out in about four weeks.
I just checked in an experimental change to sqlite3_rsync that allows it to work on non-WAL-mode database files, as long as you do not use the --wal-only command-line option. The downside of this is that the origin…
I claim the edit is neither a hardening nor a softening but rather a clarification and an attempt to better explain the original intent.
SQLite supports indexes on expressions. So if you have an expression that accesses some subcomponent of JSON that you want to index, just create an index on that expression.
Seeing this pop up on HN prompted me to review the 13-year-old document, and bring it up to date with the latest enhancements to SQLite. Specifically, three ways for safely backing up a live SQLite database are now…
Author here: My title was imprecise and unclear. I didn't mean that you should raise errors if CRLF is used as a line terminator in (for example) HTTP, only that a bare NL should be allowed as an acceptable line…