12 ms·
SQLite 3.33
- chmaynard 6y agoIf you appreciate SQL, the example (update inventory from daily sales) is a thing of beauty.
- dtf 6y agoIs “GROUP BY 2” a shorthand for “GROUP BY itemId”, the second column in the FROM SELECT? Not sure I’ve seen that syntax before.
- tmoertel 6y agoYes. GROUP BY n refers to the nth column of the result set.
- barrkel 6y agoYou can mention columns from the select clause by ordinal in group by and order by clauses, in lieu of restating the full expression. Very helpful with complex expressions, but IMO not the greatest for readability, so I tend to only use it in ad hoc queries. It's part of SQL-92, but I believe it is deprecated. Many interpreters let you use a column alias from the select clause in the group and order clauses. This has better readability IMO but I'm not sure it's in the SQL-92 standard, but I believe it is now standardized.
- nicoburns 6y ago> Many interpreters let you use a column alias from the select clause in the group and order clauses. This has better readability IMO but I'm not sure it's in the SQL-92 standard, but I believe it is now standardized. Not sure if it's standardised, but Postgres doesn't let you do this (rather irritatingly).
- formerly_proven 6y agoYes, it's not in the standard. IIRC they only don't do it because it's not in the standard, somewhat annoying for iterating on queries. In stuff like SQLAlchemy it's not as annoying, since you can write it DRY.
- barrkel 6y agoI believe it's in SQL:1999. It's tedious that all this is hearsay without open access standards. It's "only" $195 for the latest.
- benesch 6y agoNo, PostgreSQL definitely lets you do this. > An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. [0] > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. [1] Perhaps you are thinking of trying to use an output column name in a WHERE clause? [0]: https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY https://www.postgresql.org/docs/current/sql-select.html#SQL-... [1]: https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/current/sql-select.html#SQL-...
- nicoburns 6y ago> Perhaps you are thinking of trying to use an output column name in a WHERE clause? Yeah, I think I am thinking about this. Its pretty frustrating because the actual query engine is perfectly capable of executing fairly complex SQL expressions efficiently (they're not really that complex computationally, but they are syntactically because of SQL's verbosity), but the code becomes quite unmaintainable if you use too many of them. For example, the following expression: ROUND(( (EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) -- Shift length in minutes - (FLOOR((EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) / 380) * 20) -- Break length in minutes )::numeric / 60 , 2) as hours_planned, It repeats the sub-expression `EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60)`. If I could name that sub expression and reference it multiple times then the overall expression would be a lot more readable.
- cabaalis 6y agoOrder by [column number] is also a useful shortcut in mssql. Very often my columns are a compilation of functions, it feels clean to not repeat them. I'm not sure of availability in other sqls.
- nogabebop23 6y agoIMO it looks cleaner but adds cognitive overhead for readability so I'm not sure if it is actually better. Feels kind of like a lookup table that a reader now has to reference. YMMV
- sroussey 6y agoIn code, I agree. But when you are a REPL, it is handy to have shortcuts.
- tgb 6y agoI'm only passingly familiar with SQL, can anyone explain?
- headalgorithm 6y agoEssentially the FROM part calculates how many of each item has been sold today and then uses this amount to reduce how many are currently in stock (the UPDATE and SET parts).
- tgb 6y agoI see what it's doing but I don't really see what the benefit is. Maybe a comparison to what previously had to be done would help me? Would you have to make the 'daily' table in a separate step and then UPDATE using that?
- bepvte 6y agoIs the "set" evaluated where its written or only during "sum()"? If not, whats the sum for?
- dilandau 6y agoYou can examine the query plan using explain.
- brilee 6y agoIt isn't idempotent, though - if you execute the query twice, you're kind of screwed, since your inventory numbers are now bad. A better way to do it would be to create a new table that consists of daily snapshots of inventory, select the latest row, and then use that row's date as a WHERE filter when aggregating the latest transactions.
- chmaynard 6y ago> It isn't idempotent, though Agreed, but the purpose of the example is to demonstrate how to use UPDATE FROM, nothing more. I appreciate how it accomplishes a complex update in a very clear, concise, elegant way.
- yread 6y agoNice feature, I hate having to work around it with subqueries or worse
- chmaynard 6y agoAgreed. It would be interesting to compare the UPDATE FROM query plan with the equivalent subquery-based update.
- pella 6y agohttps://www.sqlite.org/releaselog/3_33_0.html https://www.sqlite.org/releaselog/3_33_0.html
- Communitivity 6y agoInteresting. Great for Command Query Responsibility Segregation (CQRS), but I could see this leading to a reduction in data normalization for a lot of teams not using CQRS too. CQRS is a great tool - but the complexity is high so you only want to use it where you need it. CQRS overused a lot though, like using a $75k surveillance robotic dog from Boston Dynamics [https://spectrum.ieee.org/automaton/robotics/industrial-robots/boston-dynamics-spot-robot-dog-now-available https://spectrum.ieee.org/automaton/robotics/industrial-robo...] (Massive Dynamics?) to see who's at the door, when you could have just looked through the peephole. I could see this feature becoming the CQRS of the SQL world for a while, used in many places where it should be considered harmful, in addition to the places where it is helpful.
- stingraycharles 6y agoI don’t understand why you focus on CQRS; I don’t think it’s specifically related? At least, not any more related than say, materialized views are useful for CQRS. I’d expect a CQRS system on top of SQL to be implemented using a single event table and a lot of triggers, each triggering a different aggregate.
- Communitivity 6y agoNot sure why I got downvoted. Also, Domain Driven Design (which is where I've usually seen aggregates discussed), Event Sourcing, and CQRS are three different things that might be used together in an implementation but don't have to be. The reason I noted how it's great for CQRS is that I've usually seen CQRS implemented with two data models. One is queried, and one is modified via commands. The one that's queried is updated according to some strategy, sometimes on an event triggered by the command model being modified. This this you could trigger and update on your query model when your command model is changed, all within SQL.
- stingraycharles 6y agoI think you're mostly downvoted because: * It's only about CQRS, which in no way was mentioned in the article; * You're not properly qualifying why this feature is so important for CQRS; * In the same breath, you're also claiming it's overused and complex, while in fact you're the one who's "promoting" it. I agree that in my reply, I was actually thinking about ES + CQRS rather than pure CQRS, but the gist of my comment still stands: I would expect a CQRS implementation to work with triggers (for incremental updates) or materialized views (one table insert-only, the "view" is kept in sync automatically by the database, which is what users query). Could you elaborate on why you think this new feature is better than those two approaches?
- simonw 6y agoSQLite 3.33 also increases the maximum size of a SQLite database file from 140TB to 281TB: https://www.sqlite.org/releaselog/3_33_0.html https://www.sqlite.org/releaselog/3_33_0.html I asked about this on the SQLite forum and D. Richard Hipp said "This change was in response to a customer request. They still have a factor of 4 before reaching the old upper limit, but asked for additional headroom." https://sqlite.org/forum/forumpost/8e40a7f588428077d7f073c004f704467e947c372ad507b7324c92ad7a118da8 https://sqlite.org/forum/forumpost/8e40a7f588428077d7f073c00...
- bob1029 6y agoIt is very good to hear there are others leveraging the full capabilities of this software. We haven't broken the 100GB barrier for a single SQLite database file yet, but we have strong confidence that everything will simply continue working as expected once we do.
- misev 6y agoThe largest I've worked with until now is a bit over 0.5TB. It's still performing as well as if it was a few GB.
- NewEntryHN 6y agoOut of curiosity I wondered what was the biggest Sqlite databse in my filesystem: find / -name *.sqlite -printf '%s %p\n' 2>/dev/null | sort -nr | head -n 1 The winner is `favicons.sqlite` from Firefox profile directory, which is 40 MB.
- lazulicurio 6y agoAs someone who uses SQL Server at work, I was surprised to find that this wasn't a standard feature in other DB engines. I use it all the time! Glad to see it spreading.
- deleted 6y ago[deleted]
- zingplex 6y agoSQLite isn't a database so much as a file format with a handy API, so it should be expected it won't have all of the feature you may expect from a full database engine. It doesn't support users and permissions for example. I don't think a features presence in SQLite can be seen as indicative of the feature set of any other database software.
- isoprophlex 6y agoPostgres has this too afaik? Yeah![1] with some caveats, that also apply to the sqlite implementation if im not mistaken: When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. https://www.postgresql.org/docs/current/sql-update.html https://www.postgresql.org/docs/current/sql-update.html
- anarazel 6y agoThe release notes are directly referencing that they copied us / PG: "Support for UPDATE FROM following the PostgreSQL syntax. The sqlite folks have done that a few times, and I think that's good. No need to unnecessarily proliferate more variants of doing the same.
- thom 6y agoThis is useful but the clunkyness of using FROM/WHERE vs JOIN ... USING has always annoyed me in Postgres, which they've based this on. The MySQL version is sort of what my brain expects.
- zeroimpl 6y agoI wonder if they could just make this syntax work in Postgres: UPDATE inventory SET quantity = inventory.quantity - daily.amt FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) daily USING( itemId ); Basically just allowing a ON/USING clause after the first FROM entry as if it was joining to the table being updated. Otherwise it's kind of annoying when you end up joining to several tables in the update, but have to use the WHERE clause to join back to the main table.
- Izkata 6y agoPersonally I prefer UPDATE JOIN as I've used it in mysql: UPDATE inventory INNER JOIN (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily USING (itemId) SET quantity = quantity - daily.amt; I had thought postgres could do it this way without the extra FROM syntax, but apparently not.
- zeroimpl 6y agoThe idea of being able to update multiple tables in a single query this way is intriguing to me! But I like the fact that the Postgresql syntax makes it crystal clear which table is being updated.
- funny_falcon 6y agoIn fact, MySQL allows update of multiple tables. https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query https://stackoverflow.com/questions/4361774/mysql-update-mul...
- gigatexal 6y agoa better link to the changes in this ver than what the entry provides: https://www.sqlite.org/releaselog/3_33_0.html https://www.sqlite.org/releaselog/3_33_0.html
- justinclift 6y agoAs a data point, the nightly builds of DB Browser for SQLite now include SQLite 3.33: https://nightlies.sqlitebrowser.org/latest/ https://nightlies.sqlitebrowser.org/latest/
- emddudley 6y agoBummer, Python 3.9.0 RC1 just missed it! It bundles SQLite 3.32.3. Ah well, I guess we can look forward to it in 3.9.1 in a few months.
- leeoniya 6y agoFossil also got a new release today: https://fossil-scm.org/home/uv/download.html https://fossil-scm.org/home/uv/download.html
- ufo 6y agoIs UPDATE FROM part of ISO SQL or is it just a very popular extension?
- josefx 6y agoGoing by the SQLite documentation it isn't. https://www.sqlite.org/lang_update.html https://www.sqlite.org/lang_update.html
- mamcx 6y agoOh, decimal support is landing! I have a lot of stuff dancing around the lacking of decimals in sqlite, so this is so welcoming!
- miohtama 6y agoDecimal type, finally. Now SQLite is 100x easier to use in finance.
- fauigerzigerk 6y agoThat's brilliant! I've been waiting for this for years.
- SloopJon 6y agoI'm intrigued by the decimal extension. I thought SQLite was IEEE double only, but it handles 64-bit integers, and now decimals. They don't seem to be fully incorporated into the documentation yet, and decimal.c leaves me with questions, so I guess I'll just have to play around with it.
- nabla9 6y agoDecimals are represented as strings. They have add, sub, mul and sum functions for text strings as well as comparison.
- stef-13013 6y agoThis software is a piece of magic...
- ZnZirconium 6y agoSQLite is great because I don't want to run a database server if I don't need to and whenever it seems like I might have to give up and run a database server instead I find a configuration option in SQLite that solves my scaling problem or performance problem or concurrency problem. I don't like to recompile SQLite to set compile time options but I'm perfectly capable of doing it though. I wish WAL mode were the default. I wish read only WAL mode were easier to do without putting the database file in a sticky directory. Oh well.
- tmp538394722 6y agoWhy do you need read only WAL mode? Or do you just mean some processes are read only, while others can write?
- ZnZirconium 6y agoThe real reason I need WAL mode is so I can read the database while vacuum is running. The web server runs unprivileged and only needs read permission. Insert and vacuum are done as a user which has write permission. The documentation says read only WAL mode is possible if there is write permission on the directory. So I put the database file in its own directory and set the mode of the database file to 0664 and set the mode of the directory to 3777. So an unprivileged process has permission to create WAL files which become group writable.
- tmp538394722 6y agoThank you for explaining.
- anitil 6y agoMy understanding is that this is because a temporary file needs to be created next to the db in WAL mode so write permissions are necessary on on directory. (https://www.sqlite.org/tempfiles.html https://www.sqlite.org/tempfiles.html, search for 'WAL')
- wfriesen 6y agoUPDATE FROM is a nice extension. In Oracle I've done similar things with an UPSERT/MERGE statement. The linked example of: UPDATE inventory SET quantity = quantity - daily.amt FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily WHERE inventory.itemId = daily.itemId; Could be done with MERGE using: MERGE INTO inventory i USING ( SELECT SUM(quantity) AS amt , itemId FROM sales GROUP BY 2 ) daily ON ( i.itemId = daily.itemId ) WHEN MATCHED THEN UPDATE SET i.quantity = i.quantity - daily.amt;
- oars 6y agoIs SQLite capable of supporting multiple writes to the database from different processes at the same time? This question is purely around INSERTS - no UPDATES. Anything I should be aware of? Looking to write data from multiple services to an SQLite file.
- ficklepickle 6y agoFrom the FAQ: > Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however. Sounds like you can, just not at the same moment as it locks the file during writes. It appears to retry if the file is locked, so not the end of the world. Faq- https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5 Relevant SO post - https://stackoverflow.com/questions/15383615/multiple-access-to-a-single-sqlite-database-file-via-system-data-sqlite-and-c-sh#15402706 https://stackoverflow.com/questions/15383615/multiple-access...
- itroot 6y agoI think that this applies to all modifying operations - INSERTs, UPDATEs, DELETEs. It's perfectly ok to write to sqlite from different processes in the same time, but to achieve good results it's better to: * use WAL mode - so the readers and writers do not block (you can turn on it with `PRAGMA journal_mode=WAL;` in CLI, and it's better to add `PRAGMA main.synchronous=NORMAL;` also). * all concurrent writes will be queued by sqlite3 lib and done in sequential manner, and if any write attempt will wait longer that BUSY_TIMEOUT ( see https://www.sqlite.org/pragma.html#pragma_busy_timeout https://www.sqlite.org/pragma.html#pragma_busy_timeout ) , it will return error. Snippet # setting things up... itroot@l7490:/tmp$ grep -i pragma ~/.sqliterc PRAGMA journal_mode=WAL; PRAGMA main.synchronous=NORMAL; PRAGMA busy_timeout=1000; itroot@l7490:/tmp$ sqlite3 test.sqlite 'CREATE TABLE records (id INTEGER PRIMARY KEY, record TEXT);' > /dev/null 2>&1 # running 10 parallel processes that inserts numbers from 1 to 1000... itroot@l7490:/tmp$ echo {1..1000} | xargs -n1 -d' ' -P 10 -i% sqlite3 test.sqlite 'INSERT INTO records (record) VALUES (%);' >/dev/null 2>&1 # getting number of records itroot@l7490:/tmp$ sqlite3 test.sqlite 'SELECT count(*) FROM records;' count(*) = 1000