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 with mmap, you get a signal. SQLite itself ought not be setting signal handlers, as that is the domain of the application and SQLite is just a lowly library. And even if SQLite could set signal handlers, it would be difficult to associate a signal with a particular I/O operation. So there isn't a good way to deal with I/O errors when using mmap(). With mmap(), you just have to assume that the filesystem/mass-storage works flawlessly and never runs out of space.
SQLite can use mmap(). That is a tested and supported capability. But we don't advocate it because of the inability to precisely identify I/O errors and report them back up into the application.
> The operating system must have a unified buffer cache in order for the memory-mapped I/O extension to work correctly, especially in situations where two processes are accessing the same database file and one process is using memory-mapped I/O while the other is not. Not all operating systems have a unified buffer cache. In some operating systems that claim to have a unified buffer cache, the implementation is buggy and can lead to corrupt databases.
What are those OSes with buggy unified buffer caches? More importantly, is there a list of platforms where the use of mmap in sqlite can lead to data loss?
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 order to compute the correct answer, regardless of whether or not there is a LIMIT clause.
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 put the false narrative that "SQLite refuses outside contributions" to rest. The bar is high to get there, but the SQLite code base does contain contributed code.
Dr. Hipp, I love SQLite but also had simonw's misapprehension that the project did not accept contributions. The SQLite copyright page says:
> Contributed Code
> In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches. If you would like to suggest a change and you include a patch as a proof-of-concept, that would be great. However, please do not be offended if we rewrite your patch from scratch.
I realize that the section, "Open-Source, not Open-Contribution" says that the project accepts contributions, but I'm having trouble understanding how that section and the "Contributed Code" section can both be accurate. Is there a distinction between accepting a "patch" vs. accepting a "contribution?"
If you're planning to update this page to reduce confusion of the contribution policy, I humbly suggest a rewrite of this sentence to eliminate the single and double negatives, which make it harder to understand:
> In order to keep SQLite in the public domain and ensure that the code does not become contaminated with proprietary or licensed content, the project does not accept patches from people who have not submitted an affidavit dedicating their contribution into the public domain.
Could be rewritten as:
> In order to keep SQLite in the public domain and prevent contamination of the code from proprietary or licensed content, the project only accepts patches from people who have submitted an affidavit dedicating their contribution into the public domain.
I will make sure not to spread that misinformation further in the future!
Update: I had a look in fossil and counted 38 contributors:
brew install fossil
fossil clone https://www.sqlite.org/src sqlite.fossil
fossil sql -R sqlite.fossil "
SELECT user, COUNT(*) as commits
FROM event WHERE type='ci'
GROUP BY user ORDER BY commits DESC
"
Man, your behavior when you realize you got something wrong is something the rest of us can aspire to. This is one of the things I like the best about you.
I learned it from newspapers: papers that publish prompt and clear corrections when they publish mistakes are more credible than papers that don't acknowledge their errors.
Something better than CVS was needed. (I'm not being critical of CVS. I had to use the VCSes that can before, and CVS was amazing compared to them.) Monochrome gave me the idea of doing a distributed VCS and storing content in SQLite, but Monochrome didn't support sync over HTTP, which I definitely wanted. Git had just appeared, and was really bad back in those early years. (It still isn't great, IMO, though people who have never used anything other than Git are quick to dispute that claim.) Mercurial was... Mercurial. So I decided to write my own DVCS.
This turned out to be a good thing, though not in the way I expected. Since Fossil is built on top of SQLite, Fossil became a test platform for SQLite. Furthermore, when I work on Fossil, I see SQLite from the point of view of an application developer using SQLite, rather than in my usual role of a developer of SQLite. That change in perspective has helps me to make SQLite better. Being the primary developer of the DVCS for SQLite in addition to SQLite itself also give me the freedom to adapt the DVCS to the specific needs of the SQLite project, which I have done on many occasions. People make fun of me for writing my own DVCS for SQLite, but in the balance it was a good move.
Note that Fossil is like Git in that it stores check-ins an a directed acyclic graph (DAG), though the details of each node are different. The key difference is that Fossil stores the DAG in a relational database (SQLite) whereas Git uses a custom "packfile" key/value store. Since the content is in a relational database, it is really easy to add features like tickets, and wiki, and a forum, and chat - you've got an RDBMS sitting there, so why not use it? Even without those bonus features, you also have the benefit of being about to query the DAG using SQL to get useful information that is difficult to obtain from Git. "Detached heads" are not possible in Fossil, for example. Tags are not limited by filesystem filename restrictions. You can tag multiple check-ins with the same tag (ex: all releases are tagged "release".) If you reference an older check-in in the check-in comment of a newer check-in, then go back and look at the older check-in (perhaps you bisected there), it will give a forward reference to the newer one. And so forth.
About Fossil, I really liked how everything is integrated into the VCS.
My friends also make fun of me having some tools that only I use. Somehow understanding a tool down to the last little bit of detail is satisfying by itself. We live in an era of software bloat that does not make too much sense.
Anyways, thanks for SQLite, I use it for teaching SQL for students and for my custom small scale monitoring system.
Just like Fossil vs Git, SQLite vs $SomeRealSQLServer, I wish someday Althttpd would become a no-bullshit self-contained replacement for Nginx/Apache/whatever bloated HTTP servers. It has already proved its working by serving Fossil/SQLite, but configuration/features for serving actual web site is not yet "real production quality", at least that is how I feel.
Overall, what an amazing legacy this set of software has been to the world.
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.
Interesting! I guess that's from your standard benchmark setup. Please note that Fil-C makes no secret of having a performance penalty. It's definitely a pre-1.0 toolchain and only recently starting to pick up some momentum. The author is eager to keep improving it, and seems to think that there's still plenty of low hanging and medium hanging fruit to pick.
It does (or did, at some point) pass the thorough SQLite test suite, so at least it's probably correct! The famous SQLite test coverage and general proven quality might make SQLite itself less interesting to harden, but in order to run less comprehensively verified software that links with SQLite, we have to build SQLite with Fil-C too.
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 WHERE clause of the query, then the partial index is only usable if Q implies P for all possible assignments of variables. A theorem prover is needed to establish this. Every RDBMS has one. The one inside SQLite is not terribly bright, true enough. It leans toward usually little memory and few CPU cycles. It does not do a good job if P contains "x=0.9". On the other hand, SQLite's theorem prover is decent if P contains "x IS NOT NULL", because in actual practice, probably about 90% of partial index WHERE clauses are some variation on "x IS NOT NULL".
The partial index expression does not always have to be exactly the same as what is in the WHERE clause of the query. SQLite will always find the match if P is a subset of Q; if Q can be rewritten as "R AND P". But if P is "x IS NOT NULL" and Q does anything that restricts x from being NULL, for example if Q contains "x>0", then SQLite's theorem prover will find that match too, even if "IS NOT NULL" never appears in Q.
Will the theorem prover in SQLite get better someday? Perhaps. It has gotten better over the years. The question becomes, how much more code space and query-planning CPU cycles are you willing to spend to get a slightly better query planner? This trade-off is different for a client/server database engine. With SQLite being embedded, the trade-off tends to fall more on the side of "keep it simple". If you have followed SQLite over many years, you might have noticed it is shifting toward more complex decision making as memory becomes cheaper and CPUs get faster. It's a tricky balancing act to find the sweet spot.
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 to exactly match. So if you say "x=0x123" in the WHERE clause of the partial index and "x=291" in the WHERE clause of the query, and that will still work. However, 64-bit integer literals and floating-point literals are compared using strcmp(), here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6570>, so they do need to match exactly, at least in the current implementation. Maybe that is something I should work on...
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 allows keywords to be used as identifiers as long as the identifier usage does not occur in a context where the keyword is allowed.
So, for example, you can used MATERIALIZED as a keyword in a common-table expression ("WITH xyzzy(a,b) AS MATERIALIZED (...)") but MATERIALIZED can also be used as a column or table name. Hence, the following SQL actually works in SQLite:
WITH xyz(MATERIALIZED) AS MATERIALIZED(
VALUES(1),(2),(3)
)
SELECT * FROM xyz;
> 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 recognized by the SQL parser itself. It does not get expanded by TCL. The $uid token serves the same roll as a "?" or ":abc" token would in some other SQL implementations. It is a placeholder for a value. The tclsqlite3.c interface first parses the SQL, then asks for the names of all of the placeholder tokens. Then it binds the values in TCL variables of the same name to those placeholders.
Indeed, this whole mechanism is specifically designed to make it easy to write SQL-injection-free code. As long as you put your SQL inside of {...}, you are completely safe from SQL injections.
If your TCL script includes SQL text inside of "...", then TCL will do the expansion and SQL injection is possible. But as long as the SQL text is inside of {...}, SQL injection is not possible.
Fully cognizant that I'm rolling the dice by responding to this comment, but isn't picking a variable syntax that could resolve in the unsafe way a "you're holding it wrong" waiting to happen?
% set ex1 "SELECT * FROM FOO WHERE alpha=$bravo"
can't read "bravo": no such variable
% set ex2 "SELECT * FROM FOO WHERE alpha=?1"
SELECT * FROM FOO WHERE alpha=?1
Don't get me wrong, https://peps.python.org/pep-0249/#paramstyle allowing %s and %(alpha)s are similar footguns and I wish they didn't exist, but at least they don't automatically resolve in the way that $ does in Tcl
It's an idiomatic Tcl thing. E.g. `expr` (the standard word used to evaluate infix expressions like `1+2`) does the same exact thing, handling the expansion itself and expecting the caller to use {} to ensure that a variable expansion not incorrectly treated as a bunch of operators. Similarly, when you're writing the condition for a loop, you need to use {} to delay expansion so that the loop word can do it anew for each iteration. One can argue that this is somewhat error prone, but at least there's a consistent pattern here, and once you know what it is and why it exists, it's pretty straightforward.
That said, to the best of my knowledge git-lfs operates upon stdin and stdout, like much of git, so I'd guess you could actually just commit the tracking file and manually run $(git-lfs scrub) et al. I do hear that "manually run" isn't the same as the way it works in git, but that's why fossil does things the fossil way
"may be aborted if a file contains content that appears to be binary, Unicode text, or text with CR/LF line endings unless the interactive user chooses to proceed. If there is no interactive user or these warnings should be skipped for some other reason, the --no-warnings option may be used."
I use fossil and checking in binaries works beautifully. You _do_ get a warning, but as seen in the documentation, you can use --no-warnings if you don't want that.
Note however, that you can't use diff on binaries, and since the entire history of the repository and the versions is shipped to all developers, storing large binaries quickly becomes cumbersome. I would in that case, store links to binaries, which themselves are stored in an archive, or switch to another scm program.
Not the same thing, but in addition to binary file support as mentioned in other comment, fossil supports adding unversioned files to the repository that are not version-managed at all. Might make sense for some large files if you just want it available but not versioned (only using space for the latest version) or automatically checked out.
> I suppose the encryption is only at the TLS layer?
Correct unless the fossil repository in question uses SQLite's SEE (encryption) extension (which fossil can, but relatively few repositories use that, AFAIK).
