<a href="https://www.windowfunctions.com" rel="nofollow">https://www.windowfunctions.com</a> is a good introduction to window functions.<p>Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.
For Python folks interested in using these features, you might be interested in this post [0] which describes how to compile the latest SQLite and the python sqlite3 driver. I've got a fork of the standard lib sqlite3 driver that includes support for user-defined window functions in Python as well which may interest you.<p>[0] <a href="http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/" rel="nofollow">http://charlesleifer.com/blog/compiling-sqlite-for-use-with-...</a><p>[1] <a href="https://github.com/coleifer/pysqlite3" rel="nofollow">https://github.com/coleifer/pysqlite3</a>
The query optimizer improvements are pretty cool too.<p>Even though, I don't really understand that one : "The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column other than the left-most column, then if no rows match against the first IN value, check to make sure there exist rows that match the columns to the right before continuing with the next IN value.
". I would think there's no need to check the right column(s) if the leftmost one has no match...
A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite
<a href="https://github.com/rqlite/rqlite" rel="nofollow">https://github.com/rqlite/rqlite</a>
or something else?
A bit off topic, but would be great to use SQLite in the browser instead of IndexedDB.<p>I love relational databases, but you're almost forced into a NoSQL approach when developing a SPA since the client (browser) only supports simple key -> value storage. It would be a dream to use LINQ-to-SQL, or similar type safe query DSLs like Slick or Quill (Scala), or Esqueleto (Haskell) in the browser.<p>Combine that with a single language driving the backend and frontend and voila, no duplication of model, validation, etc. layers on server and client.<p>One can dream I guess, but the reality is NoSQL fits the modern web app like a glove, for better or worse.
> Named window-defn<p><3<p>I really prefer that syntax and I’m always a little sad when I have to copy-paste windows across average, total, count, standard deviation, max, min… I fully admit that it’s syntactic sugar but it’s the elegant kind.
I’d really really like if they improved “alter table” to include dropping/renaming columns/constraints, even if it required rewriting the whole table.
Unrelated to window functions, but I finally took the time to start digging into SQLite's internals. People always sing its praises, so it was time to see what all the fuss was about.<p>Someone else already mentioned that the vast majority of SQLite's codebase are tests. Well, on top of that, of the real "working" codebase I'd say the majority of it is comments. It's incredible. The source is more book than code. If you have any when, why, or how question about SQLite, I guarantee it's answered in the code comments (or at least one of the hundreds of superb documents on their website).<p>Another surprise I discovered: SQLite has a virtual machine and its own bytecode. All queries you execute against a SQLite database are compiled into SQLite's own little bytecode and then executed on a VM designed for working with SQLite's database. Go ahead, start `sqlite3 yourdb.sqlite` and then run `explain select * from yourtable;`. It'll dump the bytecode for that statement; or any statement you put after `explain`. So cool!<p>In hindsight, it makes a lot of sense, and a well built VM can be nearly as efficient as any other alternative.<p><a href="https://sqlite.org/arch.html" rel="nofollow">https://sqlite.org/arch.html</a>
<a href="https://sqlite.org/opcode.html" rel="nofollow">https://sqlite.org/opcode.html</a><p>Fun bit of history. The VM used to be stack based, but now it's register based. I guess they learned the same lessons the rest of the industry learned over that time period :P<p>(N.B. the bytecode is for internal use only; it's not a public facing API. You should never, ever use bytecode directly yourself.)<p>There are some painful parts of the codebase though. These aren't "cons" per se. More like necessarily evils.<p>1) It is filled to the brim with backwards compatibility hacks that make the code more complex than it strictly needs to be. (Most of these are the result of various users of the library misusing the API. The SQLite devs are generous enough to grandfather in the bugs that made those applications work. That's excellent, but it definitely makes the code more "crusty".)<p>2) One of SQLite's big features is its flexible memory subsystem. It handles OOM, and provides an API for completely customizing the memory subsystem. But given that this is C and memory allocation and interaction is pervasive, the code ends up littered with function calls and clauses. Handling OOM is no small task, and often how to handle the OOM is different in different places. So you can imagine the complexity that adds to the codebase.<p>Again, those are necessary evils, so its not something I'm "complaining" about. But I thought they were worth mentioning for fellow adventures like me who decide to dive in (which I highly recommend).<p>So, thanks to how well designed SQLite is overall, and their great documentation, I was able to write a parser in Rust for the SQLite file format in a handful of hours (<a href="https://sqlite.org/fileformat2.html" rel="nofollow">https://sqlite.org/fileformat2.html</a>). The file format is surprisingly simple. I'm now writing a Cursor to walk the tables, which is a fun exercise of classic B-Tree algorithms.
According to [1] windowing functions make SQL turing complete.<p>Does this make SQLite turing complete or has it been turing complete before?<p>[1] <a href="http://beza1e1.tuxen.de/articles/accidentally_turing_complete.html" rel="nofollow">http://beza1e1.tuxen.de/articles/accidentally_turing_complet...</a>
SQLite guys, please add FDW support ala Postgres and easy foreign function support for Python and R, and you’ll corner most of analytics and data science.