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

11–20 of 116 posts

Re: SQLite Release 3.25.0 adds support for window functions

#11

Post by cosmie »

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

Re: SQLite Release 3.25.0 adds support for window functions

#13

Post by Mikhail_Edoshin »

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.

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?

Re: SQLite Release 3.25.0 adds support for window functions

#14

Post by est »

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.

Do you have tutorials to strip lang and ssl?

Is it possible to dynamic link system openssl to reduce size?

Re: SQLite Release 3.25.0 adds support for window functions

#15

Post by htgb »

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.

I might be missing something, but it sounds to me like you're describing this: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...

Re: SQLite Release 3.25.0 adds support for window functions

#16

Post by OskarS »

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

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

Re: SQLite Release 3.25.0 adds support for window functions

#17

Post by mabbo »

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

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.

Re: SQLite Release 3.25.0 adds support for window functions

#18

Post by ddebernardy »

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 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

Re: SQLite Release 3.25.0 adds support for window functions

#19

Post by PetahNZ »

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.

Re: SQLite Release 3.25.0 adds support for window functions

#20

Post by silvestrov »

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.

That’s not enough because it ignores all foreign key constraints.
Post reply on HN