5 ms·
SQLite 3.44: Interactive release notes
- compilator1 3y agoSQlite is good as software, but PR for it is annoying and to much.
- matharmin 3y agoSQLite comes up a lot on HN, but it's not PR - it's content that others find interesting, myself included. In most cases it's not even official SQLite content being posted, but rather other projects or blogs around it.
- mpixel 3y agoIt's almost as if a site for tech has bias for tech commonly enjoyed by the readers of the said site. I'm shocked, truly.
- thatwasunusual 3y agoIf anything, SQLite deserves _more_ PR. It's a fantastic piece of software, and at my company we use it _a lot_; mostly for testing, but also for some micro-services where data amount is small-ish, and almost never change (postal codes, country data etc.) I hate it when I see .NET Core tutorials (as an example) where they fire up MSSQL in EF just for tiny amounts of data, and then using a bloated DB-viewer GUI to show what's in the database, instead of just going to the CLI asking SQLite "directly."
- empthought 3y agoSQLite works pretty great for managing larger or more dynamic datasets, too; it’s just where concurrent access begins to increase that it stops being appropriate.
- masklinn 3y agoConcurrent writes, not concurrent accesses. Especially after enabling wal mode.
- simonw 3y agoAs far as I can tell the SQLite team's main PR strategy is consistently and quietly releasing software that's so good that large numbers of people do PR for them, for free.
- matharmin 3y agoI can definitely see where ORDER BY in aggregates would be useful - I recently used it with Postgres, and didn't even know it's not available in SQLite. Often you can do the sorting in the app code, but it's nice to do directly in the query. Other niche but useful features I've found with GROUP BY: 1. You can filter the rows used for the aggregate using a FILTER clause [1]: `group_concat(name order by name desc) filter where(salary > 100)`. While you could do the same filter in the main query, this form would still give you departments where all employees are filtered out. 2. When selecting non-aggregate values in a GROUP BY, an arbitrary row is typically picked. You can control which one is picked by using min() or max() on a different column [2]. Example: `select depertment, name as best_earner, max(salary) as best_salary from employees group by department` [1]: https://www.sqlite.org/lang_aggfunc.html https://www.sqlite.org/lang_aggfunc.html [2]: https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...
- aidos 3y agoSurprised they let you pick something without defining the aggregate as it seems really dangerous. Not entirely sure how far it extends but I’ve noticed that Postgres will let you select any rows from the table that way if you’ve included the primary key in the group by. Back when I used sql server / MySQL you needed to specify them all in the group by.
- dspillett 3y agoSQL Server forces determinism here: anything projected when there is a GROUP BY must be one of the grouping properties, an aggregate, or a constant. mySql does allow other columns to be projected, which has always felt wrong to me as I like my results from the DB to be dertemanistic. I wasn't aware sqlite allowed it too, though I've not directly used sqlite much. It is something that causes issues migrating to other DBs (or when trying to be cross-DB compatible). Often it is used accidentally and by chance gives good results, where is is used deliberately IME it is where the same value will always return due to properties of the that data being queried (in which case the "fix" for other DBs is to use an aggregate like MIN or MAX). Sometimes accidentally use leads to subtle bugs which, as you say, can be rather dangerous. Note though that these days this can only be used dangerously if the ONLY_FULL_GROUP_BY option is on, and it is off by default. Allowing it in safe circumstances actual makes modern mySql more standards compliant in this respect, see https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.ht... for more detail.
- pixel_tracing 3y agoCan we please get array support and aggregates like collect_set
- matharmin 3y agoSQLite's json1 works pretty well for arrays
- karolist 3y agoIs there any chance SQLite will sometime work stored under CIFS/SMB shares? I wanted to store all my container /config directories under NAS but can't with the ones using SQLite.
- masklinn 3y agoIt will sometimes work. Eventually it’ll get hopelessly corrupted because that’s what network filesystems do to shared files.
- Kenji 3y ago[dead]
- dgl 3y agoDepending what NAS you’re using maybe it supports iSCSI? It can be a better option for things where you need to make sure only one system accesses the data at a time.
- remram 3y agoIt probably works if you don't have concurrent access.
- bambax 3y ago> As you probably know, the group_concat function is SQLite (and MySQL) specific. In other databases (namely, PostgreSQL and SQL Server) the function is called string_agg. As of 3.44, SQLite also supports string_agg as an alias for group_concat(expr, sep) (...) Now the only black sheep is Oracle, where the poor function is called listagg. Minor nitpick, but why not go all the way and add the Oracle syntax while they were at it? Is there a technical reason not to? (I've never used Oracle)
- ranting-moth 3y agoI don't know why exacly, but there's a good general reason not to touch anything made by Oracle: lawsuits.
- giraffe_lady 3y agoI don't think they're doing it to match a DB vendor consensus I suspect they're doing it because postgres specifically does. I'm not aware of any official expression of this, but for the past several years at least sqlite seems to be treating what postgres does as a sort of spec, and aligning with its behavior unless they have a specific reason not to. Which is great imo.
- bambax 3y agoAh yes that would explain it, thanks.
- stef-13013 3y agoNice... SQLite remains an exceptional piece of software !!
- howtofly 3y agoI found SQLite grow bigger and bigger: - For 3.7.8, its default build on x86 is only 350KiB (1). - Now it is 650 KB (2). This is really worrying, especially for the field of embedded development. [1] https://www.hwaci.com/sw/sqlite/footprint.html https://www.hwaci.com/sw/sqlite/footprint.html [2] https://www.sqlite.org/footprint.html https://www.sqlite.org/footprint.html
- HumanOstrich 3y agoVersion 3.7.8 is over a decade old and the 64-bit version of it is actually 409KB. I'd say the rate of growth is impressively small. Are you locked in to using only default builds for embedded?
- howtofly 3y ago[dead]
- spiffytech 3y agoFrom the full release notes: > The PRAGMA case_sensitive_like statement is deprecated, as its use when the schema contains LIKE operators can lead to reports of database corruption by PRAGMA integrity_check. Without enabling that pragma, you get the default behavior where 'a' LIKE 'A' is true. This is unfortunate, since case-sensitive comparisons seem like an important tool. I'm not sure there's another built-in option for this. SQLite doesn't ship with a regex engine (you have to wire up an application-defined SQL function, if your driver supports it).
- masklinn 3y agoTIL sqlite's LIKE is case insensitive, I had no idea. That's almost as annoying as FKs having to be enabled on a per-connection basis. > I'm not sure there's another built-in option for this. SQLite doesn't ship with a regex engine Apparently sqlite has a GLOB operator (and function) which is case sensitive, and is slightly more expressive.
- spiffytech 3y agoThe docs for the pragma are confusing me about whether or not GLOB's behavior is also affected: > This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application. This pragma only changes the behavior of the SQL LIKE operator. It does not change the behavior of the sqlite3_strlike() C-language interface, which is always case insensitive. You're right though, GLOB defaults to being case-sensitive! > GLOB is case sensitive, unlike LIKE.
- masklinn 3y agoIndeed the docs are very confusing but I think what it's saying is it uses sqlite3_create_function to replace the "default" implementation of LIKE (which calls the like function: https://www.sqlite.org/lang_corefunc.html#like https://www.sqlite.org/lang_corefunc.html#like). It might be mentioning GLOB because it also resets its function (to the default https://www.sqlite.org/lang_corefunc.html#glob https://www.sqlite.org/lang_corefunc.html#glob) so a client which installed its own glob() function would get an odd behaviour. I can't say I understand why it resets glob though. From looking at archive.org, until 2011 the doc said: > This pragma only works if the built-in like() SQL function has not been overloaded using sqlite3_create_function(). then in mid 2011 it changes to > This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application. but the only changelog entry about case_sensitive_like before then was its introduction back in 2005. 3.0.6 beta (september 2004) does indicate > Combine the implementations of LIKE and GLOB into a single pattern-matching subroutine. but the link still seems debatable, surely overriding glob() does not normally change the behaviour of like(), it's only the default impls which are shared? Anyway if someone wants to code-dive sqlite, that's an open question.
- stux 3y ago> Aggregate functions can now include an ORDER BY clause after their last parameter. Hopefully that means we'll soon get support for `DISTINCT` in `GROUP_CONCAT`. Atm you can do `group_concat(x, '|')` and `group_concat(distinct x)`, but you can't do `group_concat(distinct x, '|')`.
- jddj 3y agoYeah this would be nice