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 more writes from running concurrently, so there is nothing the application needs to do to implement this, other than responding to SQLITE_BUSY replies from failed (concurrent) write attempts and retrying after a short delay.
Why this constraint? Because SQLite is serverless. There is no central server available to coordinate concurrent writes.
At the lowest level of the stack, every database engine has this same constraint, as there is only one wire connecting the CPU to the SSD, and you cannot send multiple writes over the same wire at the same time. But in a client/server database, the server (in cooperation with the filesystem) is at hand to serialize the writes and prevent problems in ways that are not possible without a server. The server creates the illusion of concurrent writes by multiplexing the single write wire efficiently and making that multiplexing transparent to the application.
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 dataset of significant size that doesn't have at least one invalid entry somewhere. Increasingly strict type rules will not fix that.
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 (like SQL or JSON) where all values are subclasses of a single superclass. I have never seen that in my own practice. I don't know of any objective research that supports the idea that rigid typing is helpful in that context. Maybe I missed something...
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 jjj->>'xyz' is the value of the "xyz" field of that object.
I copied the idea for the ->> operator from PostgreSQL. But in PostgreSQL, the ->> operator always returns a text rendering of the value from the JSON, even if the value is really an integer or floating point number. PG is rigidly typed, so that's all it can do. But SQLite is flexibly typed, so the ->> operator can return anything - text, integer, floating-point, NULL - whatever value if finds in the JSON.
Minor correction: SQLite is not closed to contributions. It just has an unusually high bar to accepting contributions. The project does not commonly accept pull requests from random passers-by on the internet. But SQLite does accept outside contributed code from time to time. Key gates include that paperwork is in place to verify that the contributed code is in the public domain and that the code meets certain high quality standards.
As long as you are not using indexes on expressions where the expression value is a floating point number that is computed using one or more text->binary conversions, then you should be fine.
You do not recall correctly. There is more than 500K SLOC of test code in the public source tree. If you "make releasetest" from the public source tarball on Linux, it runs more than 15 million test cases.
It is true that the half-million lines of test code found in the public source tree are not the entirety of the SQLite test suite. There are other parts that are not open-source. But the part that is public is a big chunk of the total.
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.
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.
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.
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 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...
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.
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.
> 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 32768, though, and so it just seemed natural to stuff the actual number into a 2-byte integer. The 65536 page size capability was added years later (at the request of a client) and so I had to implement the 65536 page size in a backwards compatible way. The design is not ideal for human readability, but there are no performance issues nor unreasonable code complications.
The page size value is not the only oddity. There other details in the file format that could have been done better. But with trillions of databases in circulation, it seems best to leave these minor quirks as they are rather than to try to create a new, more perfect, but also incompatible format.
Why this constraint? Because SQLite is serverless. There is no central server available to coordinate concurrent writes.
At the lowest level of the stack, every database engine has this same constraint, as there is only one wire connecting the CPU to the SSD, and you cannot send multiple writes over the same wire at the same time. But in a client/server database, the server (in cooperation with the filesystem) is at hand to serialize the writes and prevent problems in ways that are not possible without a server. The server creates the illusion of concurrent writes by multiplexing the single write wire efficiently and making that multiplexing transparent to the application.