10 ms·

SQLite Release 3.25.0 adds support for window functions

333 points by MarkusWinand 8y ago

Dinux 8y ago
Not sure if SQLite is still a 'lite' database
- felixge 8y ago
  In the category of SQL databases, it certainly is. I mean it's not called LiteDB for a reason I suppose ;).
- MarkusWinand 8y ago
  I've just built the snapshot. The binary is still less than 2MB. And it works: sqlite> with t(x) as (values (1), (2)) select sum(x) over (order by x) from t; 1 3
  - unixhero 8y ago
    Insightful. Thanks!
  - davidgould 8y ago
    I just checked a stripped copy of postgresql-10 built with all options on and it is 7MB. That could be reduced a little by leaving out language support and ssl support etc.
    - est 8y ago
      Do you have tutorials to strip lang and ssl? Is it possible to dynamic link system openssl to reduce size?
      - davidgould 8y ago
        If you look at the build instructions you will see that in the ./configure step you can enable and disable different features. Try ./configure --help to get a list. It is dynamically linked to openssl, but you can configure out the internal code to support the functionality. "strip" however meant the unix strip command to remove debug symbols.
    - plq 8y ago
      While binary size could certainly be a factor in a good deal of embedded environments, we also need to look at the resource requirements of the binary in question as well. Sqlite doesn't need too much more memory than what its binary needs whereas with postgresql, you need all sorts of bells and whistles just to get the database system to boot.
      - davidgould 8y ago
        Well, sure, sqllite is smaller than postgres, but database system performance is largely dictated by the size of the buffer cache. For something like a configuration database (a great use of sqllite) this does not matter. But for more than a very modest amount of data more memory for buffers will benefit both postgresql and sqllite. Anyway, not trying to make the case that postgresql is small compared to sqllite, it obviously isn't, just wanted to point out that it's not _that_ big either.
    - mamcx 8y ago
      And how possible to embeb it in a mobile device? Too crazy, I imagine...
- interfixus 8y ago
  I just had a look at the executable of my latest - extremely simple - webapp. A 64 bit elf, statically linked with Musl libc and SQLite, written in Nim. 1.4MB all told, with no optimizations whatsoever. In the year 2018 AD, we count that as light.
- MrEfficiency 8y ago
  Interesting, I noticed how big my database file was as well. I considered the Lite meant offline storage. Still a huge fan, I went from noob programmer to knowing databases because how quickly I could make and play with databases in sqlite.
- cryptonector 8y ago
  The "lite" refers to things like: - it only has b*-tree indexes - it only has one index per-table source
  - SQLite 8y ago
    > it only has one index per-table source I don't know for sure what this means, but it sounds like it is incorrect.
    - cryptonector 8y ago
      I could swear I've seen you say this. Maybe I'm mis-remembering? ISTR it was that for each table source in a query SQLite3 uses just one index (or the table itself) for indexing or scanning to find relevant rows in that table source.
      - SQLite 8y ago
        SQLite can use multiple indexes if there are OR terms in the WHERE clause. SQLite tries to only uses indexes in situations where they help the query run faster. SQLite is not limited in its use of indexes. It is just that the use of multiple indexes for a single FROM-clause term is rarely helpful.
        cryptonector 8y ago
        Ah, OK, thanks!
- amyjess 8y ago
  To me, what makes it 'lite' is that it doesn't have a client-server architecture that requires a running daemon. It's just a file format and a library designed to interact with it.
nanimo 8y ago
https://www.windowfunctions.com https://www.windowfunctions.com is a good introduction to window functions. 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.
- cosmie 8y ago
  Ditto! For anyone who isn't familiar with SQLite's testing procedures, read this[1] fascinating page. The SQLite project has a mind boggling 711 times more test code than SQLite itself has. Put another way, only 0.1% of the project's code is SQLite itself. The other 99.9% consists of tests for that 0.1%. [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
  - OskarS 8y ago
    In a similar vein, I rarely (if ever) seen a library that handles dynamic memory allocation more robustly than SQLite. This page is a glory to behold: https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html
    - cosmie 8y ago
      Oh that is a fun read! I hadn't seen that before.
    - clappski 8y ago
      That page is inspiring, for a commodity, open source and old software project everything is very clearly defined and the language is very definitive which I hope is indicative of the actual library quality (I’ve never used SQLite).
  - mabbo 8y ago
    While in complete agreement with you on how amazing SQLite's engineering practices are, your math is off by an order of magnitude. 1/711 = 0.00140646976 0.00140646976 ~= 0.14%, not 0.01%. I'll go put on my "pedant" hat now.
silvestrov 8y ago
I’d really really like if they improved “alter table” to include dropping/renaming columns/constraints, even if it required rewriting the whole table.
- PetahNZ 8y ago
  Well if you don't mind rewriting tables, just create a table in the new structure, insert data from the old table to the new one, drop the old table, and rename the new table.
  - silvestrov 8y ago
    That’s not enough because it ignores all foreign key constraints.
    - ifdefdebug 8y ago
      Lock db exclusively, disable foreign key checking, rebuild and replace table, reconfigure foreign keys (they are gone after delete and rename), and everything should be fine.
  - rypskar 8y ago
    One problem is that you then might need a special case for SQLite in migrations which can lead to different db schemas in tests and production
    - ngrilly 8y ago
      Use the same database for test and production. Nowadays, it is really easy to install PostgreSQL or MySQL locally or in your test environment. SQLite is great, but not for replacing PostgreSQL or MySQL during tests.
  - ngrilly 8y ago
    Any idea on how to do this without blocking writes when SQLite is embedded in a server process?
usgroup 8y ago
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.
- gaius 8y ago
  I’d argue what they would need for that is tighter integration with Pandas (Python) and data.table (R) but that would be nice Clever support for multilevel indexes would be top of my wishlist
- Mikhail_Edoshin 8y ago
  As far as I know you already can extend SQLite with custom scalar and aggregate functions and virtual tables (FDW) at least in Python (with apsw). Am I missing something?
- htgb 8y ago
  I might be missing something, but it sounds to me like you're describing this: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...
- ddebernardy 8y ago
  SQLite competes with fopen; not SQL. [1] It's great for embedded systems and small single-user apps. It's not when your data doesn't fit in memory. [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
  - peatmoss 8y ago
    Many analytics use cases are single user. I’ve often thought you could do worse than SQLite as a first pass at a dataframe implementation. And SQLite is a great fit for a range of analyses on a single-user computer where you’re looking to sample from or calculate aggregations from data that fits on harddisk but not in RAM. Now, where SQLite starts to fall down in analytics workloads is that it’s row-oriented rather than column oriented. Performance could be better. Still, even for analytic workloads SQLite can be good enough for medium-sized data!
est 8y ago
This is super cool. Does anyone know how to upgrade python3's sqlite module to the latest version?
- mci 8y ago
  Use APSW instead of sqlite3: https://rogerbinns.github.io/apsw/ https://rogerbinns.github.io/apsw/
- MrEfficiency 8y ago
  Is this going to be an issue? Or just a benefit? Ive had python libraries break with updates.
- coleifer 8y ago
  You can use https://github.com/coleifer/pysqlite3 https://github.com/coleifer/pysqlite3 It even supports user-defined window functions using the new sqlite apis.
  - simonw 8y ago
    Charles provided really great documentation on how to build this here: http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... Or if you're feeling lazy (like I was), there's a fork of his library at https://github.com/karlb/pysqlite3 https://github.com/karlb/pysqlite3 which compiles the 3.25.0 amalgamation by default. This worked for me: $ python3 -mvirtualenv venv $ source venv/bin/activate $ pip install git+git://github.com/karlb/pysqlite3 Collecting git+git://github.com/karlb/pysqlite3 ... Installing collected packages: pysqlite3 Successfully installed pysqlite3-0.2.0 $ python Python 3.6.5 (default, Mar 30 2018, 06:41:53) [GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)] on darwin Type "help", "copyright", "credits" or "license" for more information. >>> import pysqlite3 >>> pysqlite3.connect(":memory:").execute("select sqlite_version()").fetchall() [('3.25.0',)]
bertil 8y ago
> Named window-defn <3 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.
yread 8y ago
A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite or something else?
- newusertoday 8y ago
  I am interested in this topic as well, although sqlite explicitly says that it is not meant for client-server configuration. I still want to see if it is feasible and someone is using it in production.
  - move-on-by 8y ago
    I got thrown into a legacy web project that used sqlite as the database. It was a small internal-only app, I guess the original developer(s) figured it was so small that sqlite would be plenty and it would reduce the environment complexity. Unsurprisingly they were wrong. It was small, but sqlite couldn't handle multiple users. I believe this was before sqlite had WAL support, so reading would lock the DB. The 'solution' was to split the sqlite database into many smaller DBs that would allow users to use the site at the same time as long as they were in different areas. This greatly added to the complexity of the application. Some reports would need to access multiple databases to get what it needed, so it would still lock out people. Complexity was much higher then having all the data in a single postgresql/mysql database. All the users hated the system and often ran into DB lock issues. > sqlite explicitly says that it is not meant for client-server configuration They are right and their advice should be heeded.
    - zip1234 8y ago
      Since 2010, SQLite has had Write-Ahead-Logging. Perhaps your project was using an older version of SQLite? https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
  - misframer 8y ago
    Expensify uses SQLite for their core database. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
usermac 8y ago
I don't even know what that means but again, because it is SQLite, I up vote automatically ^_^
- dougmwne 8y ago
  There's a whole categories of analysis queries that are really easy to write with window functions and very annoying to write without. They help eliminate nested subqueries and self joins. This should make SQLlite a better choice for data analysis and reporting.
molyss 8y ago
The query optimizer improvements are pretty cool too. 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...
- SQLite 8y ago
  Suppose your query is: SELECT * FROM tab WHERE key1=1 AND key2 IN (2,3,4,5); SQLite starts by doing a single b-tree lookup on the index on (1,2) - composed from the key1 field and the first possibility of the key2 field. If that works, then it proceeds to look up (1,3), (1,4), and (1,5). But if the (1,2) lookup fails, then it backs off and tries just (1,) to see if that matches anything at all. If (1,) finds any record, the search proceeds with (1,3), (1,4),etc. But if (1,*) fails, the search stops immediately. The insight here is that a multi-column key value can be resolved using a single binary search. It is not a sequence thing where we first look for the key1=1 and then do a separate lookup in a subtree for key2. Both key1 and key2 are resolved in the same binary search.
  - molyss 8y ago
    That makes sense. I completely misunderstood the release note. Thanks for the clarification. The insight is very interesting. I always thought there would be 2 separate binary searches...
  - irishsultan 8y ago
    This is what I expected the optimization to be, except I'm still not sure that I understand the wording of "that match the columns to the right", I'd expect that to be "that match the columns to the left", after all, you're checking the existence of (1,* ), not of (* ,2) or (* ,3).
coleifer 8y ago
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. [0] http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... [1] https://github.com/coleifer/pysqlite3 https://github.com/coleifer/pysqlite3
anarchimedes 8y ago
This is awesome! Does anyone know if this update will effect the sqldf package in R?
virtualwhys 8y ago
A bit off topic, but would be great to use SQLite in the browser instead of IndexedDB. 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. Combine that with a single language driving the backend and frontend and voila, no duplication of model, validation, etc. layers on server and client. One can dream I guess, but the reality is NoSQL fits the modern web app like a glove, for better or worse.
- azinman2 8y ago
  Ironically, it’s my understanding many browsers use SQLite under the hood for storage of indexeddb
- beiller 8y ago
  https://github.com/kripken/sql.js/ https://github.com/kripken/sql.js/ I have used this and it is slow. But it was interesting!
- tzs 8y ago
  > A bit off topic, but would be great to use SQLite in the browser instead of IndexedDB That almost happened. There was a thing called WebSQL [1] that was W3C was working on to add SQL to the browser. Everyone who implemented it used SQLite. Apparently, that disqualified it from standardization. To move ahead, they wanted to see independent implementations of the standard. No browser makers stepped up to reduce the quality of their implementation by replacing some of the best designed, best written, best tested code on the planet with some other SQL back end to satisfy the committee, and so Mozilla was able to push IndexedDB as the standard browser DB interface. [1] https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
  - dragonwriter 8y ago
    > Everyone who implemented it used SQLite They had to, since what was standardized was specifically the SQL dialect of SQLite v3.6.19. > No browser makers stepped up to reduce the quality of their implementation by replacing some of the best designed, best written, best tested code on the planet with some other SQL back end to satisfy the committee There were only two implementations at all: WebKit and Opera. Mozilla and Microsoft weren't going to implement it without a spec decoupled from particular backend.
fpgaminer 8y ago
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. 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). 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! In hindsight, it makes a lot of sense, and a well built VM can be nearly as efficient as any other alternative. https://sqlite.org/arch.html https://sqlite.org/arch.html https://sqlite.org/opcode.html https://sqlite.org/opcode.html 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 (N.B. the bytecode is for internal use only; it's not a public facing API. You should never, ever use bytecode directly yourself.) There are some painful parts of the codebase though. These aren't "cons" per se. More like necessarily evils. 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".) 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. 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). 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 (https://sqlite.org/fileformat2.html https://sqlite.org/fileformat2.html). 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.
WorkLifeBalance 8y ago
According to [1] windowing functions make SQL turing complete. Does this make SQLite turing complete or has it been turing complete before? [1] http://beza1e1.tuxen.de/articles/accidentally_turing_complete.html http://beza1e1.tuxen.de/articles/accidentally_turing_complet...

333 points by MarkusWinand 8y ago

Dinux 8y ago

Not sure if SQLite is still a 'lite' database

felixge 8y ago
In the category of SQL databases, it certainly is. I mean it's not called LiteDB for a reason I suppose ;).
MarkusWinand 8y ago
I've just built the snapshot. The binary is still less than 2MB. And it works: sqlite> with t(x) as (values (1), (2)) select sum(x) over (order by x) from t; 1 3
- unixhero 8y ago
  Insightful. Thanks!
- davidgould 8y ago
  I just checked a stripped copy of postgresql-10 built with all options on and it is 7MB. That could be reduced a little by leaving out language support and ssl support etc.
  - est 8y ago
    Do you have tutorials to strip lang and ssl? Is it possible to dynamic link system openssl to reduce size?
    - davidgould 8y ago
      If you look at the build instructions you will see that in the ./configure step you can enable and disable different features. Try ./configure --help to get a list. It is dynamically linked to openssl, but you can configure out the internal code to support the functionality. "strip" however meant the unix strip command to remove debug symbols.
  - plq 8y ago
    While binary size could certainly be a factor in a good deal of embedded environments, we also need to look at the resource requirements of the binary in question as well. Sqlite doesn't need too much more memory than what its binary needs whereas with postgresql, you need all sorts of bells and whistles just to get the database system to boot.
    - davidgould 8y ago
      Well, sure, sqllite is smaller than postgres, but database system performance is largely dictated by the size of the buffer cache. For something like a configuration database (a great use of sqllite) this does not matter. But for more than a very modest amount of data more memory for buffers will benefit both postgresql and sqllite. Anyway, not trying to make the case that postgresql is small compared to sqllite, it obviously isn't, just wanted to point out that it's not _that_ big either.
  - mamcx 8y ago
    And how possible to embeb it in a mobile device? Too crazy, I imagine...
interfixus 8y ago
I just had a look at the executable of my latest - extremely simple - webapp. A 64 bit elf, statically linked with Musl libc and SQLite, written in Nim. 1.4MB all told, with no optimizations whatsoever. In the year 2018 AD, we count that as light.
MrEfficiency 8y ago
Interesting, I noticed how big my database file was as well. I considered the Lite meant offline storage. Still a huge fan, I went from noob programmer to knowing databases because how quickly I could make and play with databases in sqlite.
cryptonector 8y ago
The "lite" refers to things like: - it only has b*-tree indexes - it only has one index per-table source
- SQLite 8y ago
  > it only has one index per-table source I don't know for sure what this means, but it sounds like it is incorrect.
  - cryptonector 8y ago
    I could swear I've seen you say this. Maybe I'm mis-remembering? ISTR it was that for each table source in a query SQLite3 uses just one index (or the table itself) for indexing or scanning to find relevant rows in that table source.
    - SQLite 8y ago
      SQLite can use multiple indexes if there are OR terms in the WHERE clause. SQLite tries to only uses indexes in situations where they help the query run faster. SQLite is not limited in its use of indexes. It is just that the use of multiple indexes for a single FROM-clause term is rarely helpful.
      - cryptonector 8y ago
        Ah, OK, thanks!
amyjess 8y ago
To me, what makes it 'lite' is that it doesn't have a client-server architecture that requires a running daemon. It's just a file format and a library designed to interact with it.

nanimo 8y ago

https://www.windowfunctions.com https://www.windowfunctions.com is a good introduction to window functions. 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.

cosmie 8y ago
Ditto! For anyone who isn't familiar with SQLite's testing procedures, read this[1] fascinating page. The SQLite project has a mind boggling 711 times more test code than SQLite itself has. Put another way, only 0.1% of the project's code is SQLite itself. The other 99.9% consists of tests for that 0.1%. [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- OskarS 8y ago
  In a similar vein, I rarely (if ever) seen a library that handles dynamic memory allocation more robustly than SQLite. This page is a glory to behold: https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html
  - cosmie 8y ago
    Oh that is a fun read! I hadn't seen that before.
  - clappski 8y ago
    That page is inspiring, for a commodity, open source and old software project everything is very clearly defined and the language is very definitive which I hope is indicative of the actual library quality (I’ve never used SQLite).
- mabbo 8y ago
  While in complete agreement with you on how amazing SQLite's engineering practices are, your math is off by an order of magnitude. 1/711 = 0.00140646976 0.00140646976 ~= 0.14%, not 0.01%. I'll go put on my "pedant" hat now.

silvestrov 8y ago

I’d really really like if they improved “alter table” to include dropping/renaming columns/constraints, even if it required rewriting the whole table.

PetahNZ 8y ago
Well if you don't mind rewriting tables, just create a table in the new structure, insert data from the old table to the new one, drop the old table, and rename the new table.
- silvestrov 8y ago
  That’s not enough because it ignores all foreign key constraints.
  - ifdefdebug 8y ago
    Lock db exclusively, disable foreign key checking, rebuild and replace table, reconfigure foreign keys (they are gone after delete and rename), and everything should be fine.
- rypskar 8y ago
  One problem is that you then might need a special case for SQLite in migrations which can lead to different db schemas in tests and production
  - ngrilly 8y ago
    Use the same database for test and production. Nowadays, it is really easy to install PostgreSQL or MySQL locally or in your test environment. SQLite is great, but not for replacing PostgreSQL or MySQL during tests.
- ngrilly 8y ago
  Any idea on how to do this without blocking writes when SQLite is embedded in a server process?

usgroup 8y ago

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.

gaius 8y ago
I’d argue what they would need for that is tighter integration with Pandas (Python) and data.table (R) but that would be nice Clever support for multilevel indexes would be top of my wishlist
Mikhail_Edoshin 8y ago
As far as I know you already can extend SQLite with custom scalar and aggregate functions and virtual tables (FDW) at least in Python (with apsw). Am I missing something?
htgb 8y ago
I might be missing something, but it sounds to me like you're describing this: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...
ddebernardy 8y ago
SQLite competes with fopen; not SQL. [1] It's great for embedded systems and small single-user apps. It's not when your data doesn't fit in memory. [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- peatmoss 8y ago
  Many analytics use cases are single user. I’ve often thought you could do worse than SQLite as a first pass at a dataframe implementation. And SQLite is a great fit for a range of analyses on a single-user computer where you’re looking to sample from or calculate aggregations from data that fits on harddisk but not in RAM. Now, where SQLite starts to fall down in analytics workloads is that it’s row-oriented rather than column oriented. Performance could be better. Still, even for analytic workloads SQLite can be good enough for medium-sized data!

est 8y ago

This is super cool. Does anyone know how to upgrade python3's sqlite module to the latest version?

mci 8y ago
Use APSW instead of sqlite3: https://rogerbinns.github.io/apsw/ https://rogerbinns.github.io/apsw/
MrEfficiency 8y ago
Is this going to be an issue? Or just a benefit? Ive had python libraries break with updates.
coleifer 8y ago
You can use https://github.com/coleifer/pysqlite3 https://github.com/coleifer/pysqlite3 It even supports user-defined window functions using the new sqlite apis.
- simonw 8y ago
  Charles provided really great documentation on how to build this here: http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... Or if you're feeling lazy (like I was), there's a fork of his library at https://github.com/karlb/pysqlite3 https://github.com/karlb/pysqlite3 which compiles the 3.25.0 amalgamation by default. This worked for me: $ python3 -mvirtualenv venv $ source venv/bin/activate $ pip install git+git://github.com/karlb/pysqlite3 Collecting git+git://github.com/karlb/pysqlite3 ... Installing collected packages: pysqlite3 Successfully installed pysqlite3-0.2.0 $ python Python 3.6.5 (default, Mar 30 2018, 06:41:53) [GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)] on darwin Type "help", "copyright", "credits" or "license" for more information. >>> import pysqlite3 >>> pysqlite3.connect(":memory:").execute("select sqlite_version()").fetchall() [('3.25.0',)]

bertil 8y ago

> Named window-defn <3 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.

yread 8y ago

A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite or something else?

newusertoday 8y ago
I am interested in this topic as well, although sqlite explicitly says that it is not meant for client-server configuration. I still want to see if it is feasible and someone is using it in production.
- move-on-by 8y ago
  I got thrown into a legacy web project that used sqlite as the database. It was a small internal-only app, I guess the original developer(s) figured it was so small that sqlite would be plenty and it would reduce the environment complexity. Unsurprisingly they were wrong. It was small, but sqlite couldn't handle multiple users. I believe this was before sqlite had WAL support, so reading would lock the DB. The 'solution' was to split the sqlite database into many smaller DBs that would allow users to use the site at the same time as long as they were in different areas. This greatly added to the complexity of the application. Some reports would need to access multiple databases to get what it needed, so it would still lock out people. Complexity was much higher then having all the data in a single postgresql/mysql database. All the users hated the system and often ran into DB lock issues. > sqlite explicitly says that it is not meant for client-server configuration They are right and their advice should be heeded.
  - zip1234 8y ago
    Since 2010, SQLite has had Write-Ahead-Logging. Perhaps your project was using an older version of SQLite? https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- misframer 8y ago
  Expensify uses SQLite for their core database. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

usermac 8y ago

I don't even know what that means but again, because it is SQLite, I up vote automatically ^_^

dougmwne 8y ago
There's a whole categories of analysis queries that are really easy to write with window functions and very annoying to write without. They help eliminate nested subqueries and self joins. This should make SQLlite a better choice for data analysis and reporting.

molyss 8y ago

The query optimizer improvements are pretty cool too. 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...

SQLite 8y ago
Suppose your query is: SELECT * FROM tab WHERE key1=1 AND key2 IN (2,3,4,5); SQLite starts by doing a single b-tree lookup on the index on (1,2) - composed from the key1 field and the first possibility of the key2 field. If that works, then it proceeds to look up (1,3), (1,4), and (1,5). But if the (1,2) lookup fails, then it backs off and tries just (1,) to see if that matches anything at all. If (1,) finds any record, the search proceeds with (1,3), (1,4),etc. But if (1,*) fails, the search stops immediately. The insight here is that a multi-column key value can be resolved using a single binary search. It is not a sequence thing where we first look for the key1=1 and then do a separate lookup in a subtree for key2. Both key1 and key2 are resolved in the same binary search.
- molyss 8y ago
  That makes sense. I completely misunderstood the release note. Thanks for the clarification. The insight is very interesting. I always thought there would be 2 separate binary searches...
- irishsultan 8y ago
  This is what I expected the optimization to be, except I'm still not sure that I understand the wording of "that match the columns to the right", I'd expect that to be "that match the columns to the left", after all, you're checking the existence of (1,* ), not of (* ,2) or (* ,3).

coleifer 8y ago

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. [0] http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... [1] https://github.com/coleifer/pysqlite3 https://github.com/coleifer/pysqlite3

anarchimedes 8y ago

This is awesome! Does anyone know if this update will effect the sqldf package in R?

virtualwhys 8y ago

A bit off topic, but would be great to use SQLite in the browser instead of IndexedDB. 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. Combine that with a single language driving the backend and frontend and voila, no duplication of model, validation, etc. layers on server and client. One can dream I guess, but the reality is NoSQL fits the modern web app like a glove, for better or worse.

azinman2 8y ago
Ironically, it’s my understanding many browsers use SQLite under the hood for storage of indexeddb
beiller 8y ago
https://github.com/kripken/sql.js/ https://github.com/kripken/sql.js/ I have used this and it is slow. But it was interesting!
tzs 8y ago
> A bit off topic, but would be great to use SQLite in the browser instead of IndexedDB That almost happened. There was a thing called WebSQL [1] that was W3C was working on to add SQL to the browser. Everyone who implemented it used SQLite. Apparently, that disqualified it from standardization. To move ahead, they wanted to see independent implementations of the standard. No browser makers stepped up to reduce the quality of their implementation by replacing some of the best designed, best written, best tested code on the planet with some other SQL back end to satisfy the committee, and so Mozilla was able to push IndexedDB as the standard browser DB interface. [1] https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
- dragonwriter 8y ago
  > Everyone who implemented it used SQLite They had to, since what was standardized was specifically the SQL dialect of SQLite v3.6.19. > No browser makers stepped up to reduce the quality of their implementation by replacing some of the best designed, best written, best tested code on the planet with some other SQL back end to satisfy the committee There were only two implementations at all: WebKit and Opera. Mozilla and Microsoft weren't going to implement it without a spec decoupled from particular backend.

fpgaminer 8y ago

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. 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). 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! In hindsight, it makes a lot of sense, and a well built VM can be nearly as efficient as any other alternative. https://sqlite.org/arch.html https://sqlite.org/arch.html https://sqlite.org/opcode.html https://sqlite.org/opcode.html 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 (N.B. the bytecode is for internal use only; it's not a public facing API. You should never, ever use bytecode directly yourself.) There are some painful parts of the codebase though. These aren't "cons" per se. More like necessarily evils. 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".) 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. 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). 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 (https://sqlite.org/fileformat2.html https://sqlite.org/fileformat2.html). 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.

WorkLifeBalance 8y ago

According to [1] windowing functions make SQL turing complete. Does this make SQLite turing complete or has it been turing complete before? [1] http://beza1e1.tuxen.de/articles/accidentally_turing_complete.html http://beza1e1.tuxen.de/articles/accidentally_turing_complet...