Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Release 3.25.0 adds support for window functions

sqlite.org

Post reply on HN
Print view View original

21–30 of 116 posts

Re: SQLite Release 3.25.0 adds support for window functions

#21

Post by cosmie »

mabbo wrote: post #17
cosmie wrote: post #11

Earlier quoted context omitted.

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

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.

Thanks for catching that!

Although it was more a typo than a math error (as the 99.9% figure was correct). I'll chalk it up to posting when I should be sleeping (4:30am local time).

Re: SQLite Release 3.25.0 adds support for window functions

#22

Post by cosmie »

OskarS wrote: post #16
cosmie wrote: post #11

Earlier quoted context omitted.

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

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

Oh that is a fun read! I hadn't seen that before.

Re: SQLite Release 3.25.0 adds support for window functions

#23

Post by plq »

silvestrov wrote: post #6

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

There is nothing stopping you from doing it yourself. The Sqlite FAQ[1] even has an entry on it.

Two things holding this back: 1. code being inside our outside sqlite would not be much different. 2. the amount of additional test code would be humongous for an operation of that level of complexity.

So, just do it yourself. It's not that hard.

[1]: https://www.sqlite.org/faq.html#q11

Re: SQLite Release 3.25.0 adds support for window functions

#24

Post by plq »

davidgould wrote: post #10
MarkusWinand wrote: post #4

Earlier quoted context omitted.

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

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.

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.

Re: SQLite Release 3.25.0 adds support for window functions

#25

Post by sehugg »

cosmie wrote: post #11
nanimo wrote: post #5

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.

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

Good thing too, since it's in just about everything: https://www.sqlite.org/mostdeployed.html

Re: SQLite Release 3.25.0 adds support for window functions

#28

Post by rypskar »

PetahNZ wrote: post #19
silvestrov wrote: post #6

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

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.

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

Re: SQLite Release 3.25.0 adds support for window functions

#29

Post by blattimwind »

plq wrote: post #23
silvestrov wrote: post #6

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

There is nothing stopping you from doing it yourself. The Sqlite FAQ[1] even has an entry on it. Two things holding this back: 1. code being inside our outside sqlite would not be much different. 2. the amount of additional test code would be humongous for an operation of that level of complexity. So, just do it yourself. It's not that hard. [1]: https://www.sqlite.org/faq.html#q11

> There is nothing stopping you from doing it yourself.

Good database migration tools (e.g. Alembic) do this automatically.

Re: SQLite Release 3.25.0 adds support for window functions

#30

Post by blattimwind »

usgroup wrote: post #8

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.

SQLite has had FDW since forever. https://sqlite.org/vtab.html

SQLite has had user-defined functions since forever. https://sqlite.org/c3ref/create_function.html

Another underappreciated feature (while we're at it) would be WAL instead of undo-journaling (https://www.sqlite.org/wal.html), which enables concurrent reading and writing of SQLite databases. Has been available for some ten years or so, but is off by default.

Functions are available through the Python bindings (which are not maintained by the SQLite project), virtual tables I think, aren't. Alternate bindings (https://rogerbinns.github.io/apsw/) claim to achieve "Everything you can do from the SQLite C API" interop, which would include virtual tables.

Post reply on HN