4 ms·

SQLite 3.44.0

92 points by justinclift 3y ago

simonw 3y ago
See also these unofficial interactive release notes: https://antonz.org/sqlite-3-44/ https://antonz.org/sqlite-3-44/ - discussed here: https://news.ycombinator.com/item?id=38095239 https://news.ycombinator.com/item?id=38095239 My favourite new feature is order by in aggregates, e.g. select group_concat(name order by name desc)
- _a_a_a_ 3y ago
  Umm, isn't htat what 'over' is for? From memory (prob wrong but close enough) select string_concat(name) over (order by name) from ...
  - deleted 3y ago
    [deleted]
  - simonw 3y ago
    That's for window functions, which work differently from aggregate functions.
    - _a_a_a_ 3y ago
      In MS SQL they are used together although I don't know if this is the standard. eg. SELECT city, STRING_AGG(email,';') WITHIN GROUP (ORDER BY email) email_list FROM sales.customers GROUP BY city; https://www.sqlservertutorial.net/sql-server-string-functions/sql-server-string_agg-function/ https://www.sqlservertutorial.net/sql-server-string-function... But even this isn't using the OVER() clause as I remembered. I'm a bit confused about what the standard is, if there is one. Anyway thanks.
- kaptainscarlet 3y ago
  I find group concat very useful when I want to aggregate foreign keys as an attribute in the parent row of an 1-N relationship
tantaman 3y ago
order by aggregates. I can finally resume work on TreeSQL -- https://github.com/tantaman/TreeSQL https://github.com/tantaman/TreeSQL :)
- smitty1e 3y ago
  WITH RECURSIVE seems OK https://sqlite.org/lang_with.html https://sqlite.org/lang_with.html
- _a_a_a_ 3y ago
  It would be helpful if you could put a small, indeed minimal, example of tree SQL on that page, including the schema for data and some idea of the syntax, especially showing where the recursion happens (if it happens). The query currently there is not something I want to fight through, but whatever you're doing sounds interesting.
neves 3y ago
SQLite is excellent and a very stable software. I really don't follow its development, and settled in an old version. Sure every problem is different, but what are the really useful features of the recent releases that would make me upgrade?
- AlexErrant 3y ago
  Not highlighted are bug fixes like this [0] which may have led to this [1] user's malformed database disk image. 0: https://www.sqlite.org/src/info/c9fdd6805df04f05 https://www.sqlite.org/src/info/c9fdd6805df04f05 1: https://github.com/vlcn-io/js/issues/31 https://github.com/vlcn-io/js/issues/31 Sure Sqlite is stable, but everything is relative.

92 points by justinclift 3y ago

See also these unofficial interactive release notes: https://antonz.org/sqlite-3-44/ https://antonz.org/sqlite-3-44/ - discussed here: https://news.ycombinator.com/item?id=38095239 https://news.ycombinator.com/item?id=38095239 My favourite new feature is order by in aggregates, e.g. select group_concat(name order by name desc)

_a_a_a_ 3y ago
Umm, isn't htat what 'over' is for? From memory (prob wrong but close enough) select string_concat(name) over (order by name) from ...
- deleted 3y ago
  [deleted]
- simonw 3y ago
  That's for window functions, which work differently from aggregate functions.
  - _a_a_a_ 3y ago
    In MS SQL they are used together although I don't know if this is the standard. eg. SELECT city, STRING_AGG(email,';') WITHIN GROUP (ORDER BY email) email_list FROM sales.customers GROUP BY city; https://www.sqlservertutorial.net/sql-server-string-functions/sql-server-string_agg-function/ https://www.sqlservertutorial.net/sql-server-string-function... But even this isn't using the OVER() clause as I remembered. I'm a bit confused about what the standard is, if there is one. Anyway thanks.
kaptainscarlet 3y ago
I find group concat very useful when I want to aggregate foreign keys as an attribute in the parent row of an 1-N relationship

tantaman 3y ago

order by aggregates. I can finally resume work on TreeSQL -- https://github.com/tantaman/TreeSQL https://github.com/tantaman/TreeSQL :)

smitty1e 3y ago
WITH RECURSIVE seems OK https://sqlite.org/lang_with.html https://sqlite.org/lang_with.html
_a_a_a_ 3y ago
It would be helpful if you could put a small, indeed minimal, example of tree SQL on that page, including the schema for data and some idea of the syntax, especially showing where the recursion happens (if it happens). The query currently there is not something I want to fight through, but whatever you're doing sounds interesting.

neves 3y ago

SQLite is excellent and a very stable software. I really don't follow its development, and settled in an old version. Sure every problem is different, but what are the really useful features of the recent releases that would make me upgrade?

AlexErrant 3y ago
Not highlighted are bug fixes like this [0] which may have led to this [1] user's malformed database disk image. 0: https://www.sqlite.org/src/info/c9fdd6805df04f05 https://www.sqlite.org/src/info/c9fdd6805df04f05 1: https://github.com/vlcn-io/js/issues/31 https://github.com/vlcn-io/js/issues/31 Sure Sqlite is stable, but everything is relative.