SQLite 3.33

https://www.sqlite.org/releaselog/3_33_0.html

by chmaynard • 6 years ago

272 108 6 years ago

chmaynard3• 6 years ago

If you appreciate SQL, the example (update inventory from daily sales) is a thing of beauty.

dtf• 6 years ago

Is “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• 6 years ago

Yes. GROUP BY n refers to the nth column of the result set.

barrkel2• 6 years ago

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

cabaalis• 6 years ago

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

tgb2• 6 years ago

I'm only passingly familiar with SQL, can anyone explain?

headalgorithm• 6 years ago

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

bepvte• 6 years ago

Is the "set" evaluated where its written or only during "sum()"? If not, whats the sum for?

brilee• 6 years ago

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

chmaynard3• 6 years 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• 6 years ago

Nice feature, I hate having to work around it with subqueries or worse

chmaynard3• 6 years ago

Agreed. It would be interesting to compare the UPDATE FROM query plan with the equivalent subquery-based update.

pella• 6 years ago

https://www.sqlite.org/releaselog/3_33_0.html

Communitivity2• 6 years ago

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

stingraycharles4• 6 years ago

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

Communitivity2• 6 years ago

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

simonw• 6 years ago

SQLite 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

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/8e40a7f588428077d7f073c00...

bob1029• 6 years ago

It 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• 6 years ago

The 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• 6 years ago

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

koeng2• 6 years ago

I have a ~1TB SQLite database (genetic data). All my queries run really fast, and overall I'm very impressed with the performance.

giancarlostoro4• 6 years ago

That is kind of entertaining to think someone can store a 281TB SQLite file and I am still waiting for companies to sell 1PB hard drives.

Back in 2010 I thought yeah soon we will have 50TB hard drives then 500TB and someday I can have 1PB it will be all I will need! Sadly no such change. I would be happy to be in the 10s of TBs rage for under 200 dollars.

Games are getting stupid wasteful. Someone told me Fortnite is almost 90GB or so and I flinched considering GTA5 is around that range and offers a loooot more rich gameplay and features! What a wasteful game resource wise.

slightwinder• 6 years ago

Gameplay and features are cheap, they don't take as much storage as assets. All the dances, outfits and gears in fortnite are heavy it seems.

Q6T46nT668w6i3m• 6 years ago

The size is typically from assets, not gameplay and features.

tyingq3• 6 years ago

"I can have 1PB it will be all I will need"

You can have a 1PB file without a 1PB drive. I think md has a limit of 8 Zib. Several filesystems allow files up to 8EB.

brian_herman• 6 years ago

Yeah Call of Duty: Modern Warfare (the latest one). https://i.imgur.com/bsiGKFp.png Their patches are 30-40gb each!

nine_k2• 6 years ago

A SAN with a volume manager, or just a big enough RAID NAS could be it. You need mere 36 8TB drives, not even a full Backblaze pod.

Stratoscope• 6 years ago

Kids these days.

My first hard drive was an 8" 20MB drive. That's about 9.5 x 4.6 x 14.2 inches and weighed about 20 pounds. Only cost me $6000, in 1981. And with that massive capacity, I never did run out of room.

Now I have a 200GB MicroSD in my phone. It cost $75. Haven't run out of room on that one either.

tyingq3• 6 years ago

Json and markdown output for the CLI also...that's nice.

sprash• 6 years ago

Just as a tip if you experience bad performance of SQLite once the size reaches a triple GB range: Understand how the BEGIN/END TRANSACTION and transaction control in general works, learn about journaling why and when you need it and how to control it via the appropriate pragmas and be careful about indexing. You can increase sqllite3 performance by literally 4 orders of magnitude with simple tweaking to the point that you could easily serve a website with a million hits per day off a single SQLite database.

nine_k2• 6 years ago

Still I see that a mere 60GB SQLite database for browser history, accumulated in maybe 10 years, causes trouble with Firefox, and I had to rename it and make Firefox create a new one in order for it to continue storing my recent history.

I bet it's not SQLite's problem.

ufmace2• 6 years ago

I'd bet that the appropriate table is missing an index, since whoever designed it didn't anticipate it growing to 60GB.

crazygringo2• 6 years ago

Those limits seem so... arbitrary. And at that scale, why bother having a limit at all? Genuinely curious for the justification.

lopsidedBrain2• 6 years ago

https://sqlite.org/limits.html

SQLite was originally designed with a policy of avoiding arbitrary limits. [...] Unfortunately, the no-limits policy has been shown to create problems. Because the upper bounds were not well defined, they were not tested, and bugs were often found when pushing SQLite to extremes.

jraph2• 6 years ago

Maybe upping the limit required some changes in the way some things are handled in the code. Therefore, the limit would not be arbitrary, but a consequence of how SQLite is written.

Or the authors want to provide some guaranties and upper limits are set according to what is known to work / tested.

I'd be interested in knowing the actual answer to your question though.

edit: the actual answer is in the sibling comment, ah ah.

lazulicurio• 6 years ago

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

zingplex• 6 years ago

SQLite 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• 6 years ago

Postgres 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

anarazel• 6 years ago

The 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• 6 years ago

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

zeroimpl2• 6 years ago

I 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• 6 years ago

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

gigatexal• 6 years ago

a better link to the changes in this ver than what the entry provides: https://www.sqlite.org/releaselog/3_33_0.html

justinclift3• 6 years ago

As a data point, the nightly builds of DB Browser for SQLite now include SQLite 3.33:

https://nightlies.sqlitebrowser.org/latest/

emddudley• 6 years ago

Bummer, 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• 6 years ago

Fossil also got a new release today: https://fossil-scm.org/home/uv/download.html

ufo• 6 years ago

Is UPDATE FROM part of ISO SQL or is it just a very popular extension?

josefx• 6 years ago

Going by the SQLite documentation it isn't.

https://www.sqlite.org/lang_update.html

mamcx• 6 years ago

Oh, decimal support is landing!

I have a lot of stuff dancing around the lacking of decimals in sqlite, so this is so welcoming!

miohtama• 6 years ago

Decimal type, finally. Now SQLite is 100x easier to use in finance.

fauigerzigerk• 6 years ago

That's brilliant! I've been waiting for this for years.

SloopJon• 6 years ago

I'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• 6 years ago

Decimals are represented as strings.

They have add, sub, mul and sum functions for text strings as well as comparison.

stef-13013• 6 years ago

This software is a piece of magic...

ZnZirconium2• 6 years ago

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

tmp5383947222• 6 years ago

Why do you need read only WAL mode? Or do you just mean some processes are read only, while others can write?

ZnZirconium2• 6 years ago

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

wfriesen• 6 years ago

UPDATE 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• 6 years ago

Is 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• 6 years ago

From 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

Relevant SO post - https://stackoverflow.com/questions/15383615/multiple-access...

itroot3• 6 years ago

I 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 ) , 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

SQLite 3.33

Related Stories