SQLite Release 3.25.0 adds support for window functions • Hacker News Board

Re: SQLite Release 3.25.0 adds support for window functions

#61

Post by ktr » Wed, Aug 15, 2018, 2:52 PM UTC

twoquestions wrote: post #43

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.

This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this? A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.

My introduction to window functions (and the best write-up I’ve seen) was through the SQL Cookbook (http://shop.oreilly.com/product/9780596009762.do). I highly recommend.

Re: SQLite Release 3.25.0 adds support for window functions

#62

Post by blattimwind » Wed, Aug 15, 2018, 2:53 PM UTC

mamcx wrote: post #59

blattimwind wrote: post #56

Earlier quoted context omitted.

Make your logged events invertible and get an undo/redo function for free. That's how I kill three to four birds with one stone.

invertible? What is that?

Say you record an event like "name of customer X has been changed to FooCo". Instead, record "name of customer X has been changed to FooCo from Foo & Sons Co". If you want to undo it, just swap from/to (=invert it).

Re: SQLite Release 3.25.0 adds support for window functions

#63

Post by cryptonector » Wed, Aug 15, 2018, 3:04 PM UTC

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

A lot of table alterations can be done without rebuilding the table, though perhaps a table scan might be needed to validate constraints (though this should optionally be a separate statement). To do this safely requires the RDBMS to support the functionality.

Re: SQLite Release 3.25.0 adds support for window functions

#64

Post by cryptonector » Wed, Aug 15, 2018, 3:06 PM UTC

Dinux wrote: post #2

Not sure if SQLite is still a 'lite' database

The "lite" refers to things like:

   - it only has b*-tree indexes
   - it only has one index per-table source

Re: SQLite Release 3.25.0 adds support for window functions

#65

Post by cryptonector » Wed, Aug 15, 2018, 3:08 PM UTC

blattimwind wrote: post #30

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

Not only that, the GNOME Data Access (GDA) uses this and has virtual tables for accessing disparate remote databases (including LDAP), so we know this works. What u/usgroup may be asking for is for a bunch of virtual tables to be included in SQLite3 proper.

Re: SQLite Release 3.25.0 adds support for window functions

#66

Post by yread » Wed, Aug 15, 2018, 3:16 PM UTC

misframer wrote: post #51

newusertoday wrote: post #33

Earlier quoted context omitted.

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.

Expensify uses SQLite for their core database. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Interesting discussion of bedrock

https://news.ycombinator.com/item?id=12739771

Re: SQLite Release 3.25.0 adds support for window functions

#67

Post by coleifer » Wed, Aug 15, 2018, 3:19 PM UTC

est wrote: post #12

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

You can use https://github.com/coleifer/pysqlite3

It even supports user-defined window functions using the new sqlite apis.

Re: SQLite Release 3.25.0 adds support for window functions

#68

Post by electroly » Wed, Aug 15, 2018, 3:28 PM UTC

blattimwind wrote: post #30

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

Having written a fairly significant amount of SQLite virtual table module code, I think it isn't quite sophisticated enough to be useful. I regret building my application atop that virtual table functionality. Many parts of queries that are vital for efficient execution are not pushed down to the virtual table provider; for instance there is no way for "SELECT COUNT(*) FROM table" to do anything but retrieve every field of every row and return them to the SQLite engine, because the interface does not push the "COUNT" down to the virtual table provider. Even if your underlying data store can trivially answer the question directly, you have to pull all the data and let SQLite compute the answer. So on and so forth.

Re: SQLite Release 3.25.0 adds support for window functions

#69

Post by coleifer » Wed, Aug 15, 2018, 3:38 PM UTC

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

[1] https://github.com/coleifer/pysqlite3

Re: SQLite Release 3.25.0 adds support for window functions

#70

Post by bobwaycott » Wed, Aug 15, 2018, 3:57 PM UTC

electroly wrote: post #68

blattimwind wrote: post #30

Earlier quoted context omitted.

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

Having written a fairly significant amount of SQLite virtual table module code, I think it isn't quite sophisticated enough to be useful. I regret building my application atop that virtual table functionality. Many parts of queries that are vital for efficient execution are not pushed down to the virtual table provider; for instance there is no way for "SELECT COUNT(*) FROM table" to do anything but retrieve every fi…

Out of curiosity, why would you ever run a SELECT COUNT(*)? What use case demands that versus, say, SELECT COUNT(col) where col is the name of a column in the table?