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

71–80 of 116 posts

Re: SQLite Release 3.25.0 adds support for window functions

#71

Post by zbentley »

blattimwind wrote: post #62
mamcx wrote: post #59

Earlier quoted context omitted.

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

A common pattern to implement this is an audit table: https://dba.stackexchange.com/questions/15186/what-is-an-aud...

...which is itself a special case of an audit trail or log: https://en.wikipedia.org/wiki/Audit_trail

Re: SQLite Release 3.25.0 adds support for window functions

#72

Post by siscia »

yread wrote: post #27

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

Shameless plug since I am the author, but you could be interested in https://redisql.com/

Basically an Redis module that embed SQLite, I offer replication on the PRO version, if you want to try it out you can download the trial version for free.

If the trial version is not enough, send me an email and we could work something out ;)

Re: SQLite Release 3.25.0 adds support for window functions

#74

Post by siscia »

newusertoday wrote: post #33
yread wrote: post #27

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

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.

Most applications don't need the level of concurrency that Pg or MySQL provide and they will be just fine with something simple to operate, which is why I wrote https://redisql.com/

Re: SQLite Release 3.25.0 adds support for window functions

#75

Post by SQLite »

cryptonector wrote: post #64
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

> it only has one index per-table source

I don't know for sure what this means, but it sounds like it is incorrect.

Re: SQLite Release 3.25.0 adds support for window functions

#76

Post by AlisdairO »

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.

(self plug) try pgexercises.com. It's in a very similar vein to windowfunctions - I understand it was one of the original inspirations - but it covers a much wider range of material, with a gradual growth from very basic stuff up to slightly advanced.

Re: SQLite Release 3.25.0 adds support for window functions

#77

Post by virtualwhys »

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.

Re: SQLite Release 3.25.0 adds support for window functions

#78

Post by azinman2 »

virtualwhys wrote: post #77

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

Ironically, it’s my understanding many browsers use SQLite under the hood for storage of indexeddb

Re: SQLite Release 3.25.0 adds support for window functions

#79

Post by SQLite »

molyss wrote: post #58

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

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.

Re: SQLite Release 3.25.0 adds support for window functions

#80

Post by AlisdairO »

ktr wrote: post #61
twoquestions wrote: post #43

Earlier quoted context omitted.

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.

This is a fantastic book - the name doesn't do it justice.
Post reply on HN