SQLite Release 3.25.0 adds support for window functions

Post by mercer » Wed, Aug 15, 2018, 5:42 PM UTC

AlisdairO wrote: post #76

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.

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

Post by simonw » Wed, Aug 15, 2018, 5:56 PM UTC

coleifer wrote: post #67

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.

    $ 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',)]

Post by kodablah » Wed, Aug 15, 2018, 6:05 PM UTC

simonw wrote: post #87

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…

This almost happened. Ten years ago "WebSQL" was being considered as a standard - it would have introduced SQLite-backed databases as a built-in browser feature: https://en.wikipedia.org/wiki/Web_SQL_Database The W3C dropped it in November 2010 because they were uncomfortable supporting a web standard which was entirely tied to a single implementation of SQL (SQLite). I adore SQLite but I think the W3C made the right…

Post by amyjess » Wed, Aug 15, 2018, 6:17 PM UTC

Dinux wrote: post #2

Not sure if SQLite is still a 'lite' database

Post by simonw » Wed, Aug 15, 2018, 6:56 PM UTC

mercer wrote: post #92

simonw wrote: post #87

Earlier quoted context omitted.

This almost happened. Ten years ago "WebSQL" was being considered as a standard - it would have introduced SQLite-backed databases as a built-in browser feature: https://en.wikipedia.org/wiki/Web_SQL_Database The W3C dropped it in November 2010 because they were uncomfortable supporting a web standard which was entirely tied to a single implementation of SQL (SQLite). I adore SQLite but I think the W3C made the right…

> I adore SQLite but I think the W3C made the right call there - web standards need more than one compatible implementation. could you elaborate on that? I get the impression that now, eight years later, we're much worse off. I don't really see the upside of not going for what is, AFAIK, a relatively vanilla implementation of SQL.