7 ms·

SQLite 3.43

174 points by justinclift 3y ago

deleted 3y ago
[deleted]
OhNoNotAgain_99 3y ago
[dead]
milliams 3y ago
Does anyone know how the `make verify-source` works?
- sltkr 3y ago
  It's explained here: https://sqlite.org/getthecode.html#verifying_that_the_code_is_unmodified https://sqlite.org/getthecode.html#verifying_that_the_code_i...
- Denvercoder9 3y ago
  You can find the source here: https://www.sqlite.org/cgi/src/file?name=tool/src-verify.c&ci=trunk https://www.sqlite.org/cgi/src/file?name=tool/src-verify.c&c... It seems to use a manifest with hashes for every file generated by their VCS.
- tedunangst 3y ago
  [god damn is it annoying to navigate through fossil to actually find a file] It appears to be basically sha3sum against an included manifest file. https://sqlite.org/src/file?name=tool/src-verify.c https://sqlite.org/src/file?name=tool/src-verify.c I am kinda perplexed at the threat model resolved by including a manifest in the same tree as the adulterated sources, checked by a running a tool compiled from those same adulterated sources.
  - deleted 3y ago
    [deleted]
  - istjohn 3y ago
    Cosmic rays or other random sources of data corruption?
  - inferiorhuman 3y ago
    [god damn is it annoying to navigate through fossil to actually find a file] Hey it still sucks less than the deuce Github's UI people dropped. They somehow managed to break using non-default branches. Imagine a world where git branches are an untested feature.
    - justinclift 3y ago
      > They somehow managed to break using non-default branches. Interesting. What's the thing with non-default branches that's broken?
      - inferiorhuman 3y ago
        The first two have have finally been fixed more or less but: The compact navigation bar hides the branch (this part has been fixed). Scroll down and you can no longer jump to a different branch without scrolling back to the top of the page… which takes a while because the code view widget (UGH) punts navigation key presses off into a black hole. The new symbol navigator only links to the default branch even if you're on a different branch. The compare branches button is gone. You have to open a pull request to compare branches (or manually craft the path). Hyperlinks within markdown that reference the repo only go to the default branch. Searching within a repo only searches default branch even if you're searching from a different branch. Granted the search results have never been good, but still. More or less pick a non-default branch and experiment a little. Chances are you'll be forced back to the default branch in short order. Even when the ref name is a parameter in the URL. And that's about the nicest thing I can say about the new Github UI. Pretty much everything else is just worse universally (blurry text, laggy find within page, nothing rendered if javascript is disabled, etc, etc, etc).
        justinclift 3y ago
        Ouch. Yeah, those sound like a pain. I don't like the recent UI overhaul much either, as it makes navigating around a bunch harder in most situations. :/
jokoon 3y ago
It's funny, 10min ago, I had some comment on a 10 years old question of mine, and my question linked to another opinionated answer on stackoverflow. which still has 136 upvotes: https://stackoverflow.com/questions/1643365/why-no-love-for-sql/1643440#1643440 https://stackoverflow.com/questions/1643365/why-no-love-for-... I mean I was never crazy, RDBMS are useful, but SQL languages feel awkward not just to me. I'm curious about an alternative to the SQL language or API for a database system, and I guess that sort of RDBMS would also have a very different design. BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL. For some reason, python list comprehension/dict comprehension and sets somehow feel like a weird, light alternative to a database system. I just get the raw data with SQL and do data things I should do with SQL, and even if it's ill-advised, it feels so much better, and it works. Not to dismiss sqlite though, it's a wonderful software.
- sedatk 3y ago
  You might like EdgeQL https://www.edgedb.com/docs/edgeql/index https://www.edgedb.com/docs/edgeql/index
- winrid 3y ago
  The problem is this exposes you directly to storage mechanism internals. A good example of this is Redis. Redis is exactly what you described. It's just data structures as a service. For Redis - this works. It's its purpose. It's a tool. Same with KV stores. Again, you are working directly with the storage mechanism. However, now you're limited to that implementation detail. With SQL, or a traditional DB, your SELECT x by y could be served by a hashmap today, and maybe a btree tomorrow, and you don't need to change your app. Whether this makes sense for you depends entirely on your application. For loops in Python are okay for some apps. It won't work when you have a billion records.
- summarity 3y ago
  I use KQL (inside ADX/Kusto) at work, it's awesome. Not just saying that bc I work for an MSFT company, I legit enjoy it more than SQL. And with Kusto one can Python inside of KQL as well, which sounds bonkers but is actually pretty neat. I found through teaching people KQL that the learning curve (until they can do medium-complexity analysis) is also much shorter.
summarity 3y ago
This version is still buggy for large CTE or JOINs for edge cases. A bug has been introduced with the query planner optimizations in SQLite >3.39 (3.4X) that degrades query performance in some cases predictably by 3 orders of magnitude. So just to see if this was still a problem, I recompiled findsight.ai with this new amalgamation and yep, all queries that used to take about 300ms now take 3 to 10 seconds to complete. There's no difference in the query plans created, it's just a bug. I'll probably bisect by commit (or whatever Fossil calls it) at some point to nail down what changed, but then again 3.39 is rock solid enough I don't have a reason to upgrade. In the meantime, if you're running into one of these edge cases, try downgrading to 3.39. I also have no gripes, it clearly works well enough for many people (since by now most bundles and bindings are running 3.4X). Variants[1][2] of this bug have been reported, and some apparently fixed, but this continues to be an issue. [1] https://sqlite.org/forum/forumpost/bcc4375032 https://sqlite.org/forum/forumpost/bcc4375032 [2] https://sqlite.org/forum/info/96b9e5709cf47cda https://sqlite.org/forum/info/96b9e5709cf47cda
- kccqzy 3y ago
  While I sympathize with you, in both links the maintainer said it had been fixed. Maybe you ought to report a new bug? I didn't see any evidence regarding "maintainers consider[ing] this not a bug worth porting back fixes for because it still gives correct results" in your links.
  - summarity 3y ago
    No, you are correct, it's just hard to debug something and produce a minimal case when the only change is "performance bad". This is mostly a hint to people that may run into similar issues while upgrading. And my reference concerned this comment (which I may have misunderstood, sorry if that's the case): > It is not planned for any patch releases because it is only a performance issue and not a malfunction.
    - TheRealPomax 3y ago
      patch releases are the third number (e.g. the "1" in "3.43.1"). The comment says nothing about it not qualifying as work to be done for a minor release (e.g. the "43" in "3.43"). If the bug is closed, and you still see it happening, file a new bug with a reproducible example so that the maintainers know it's (still) happening. And if that example means setting up a small dedicate repo with enough data to show the problem off, that's probably worth doing.
irrelative 3y ago
Anyone know why this chart[1] hasn't been updated in a while? One of my favorite displays of incremental improvements in a project. [1] https://www.sqlite.org/cpu.html https://www.sqlite.org/cpu.html
- RVuRnvbM2e 3y ago
  Looks like the web page graphic is not up to date. The data that the graph is based on is here (and is up to date): https://sqlite.org/docsrc/file?name=misc/speed-size-graph.ods https://sqlite.org/docsrc/file?name=misc/speed-size-graph.od... Looks like 3.43.0 uses about 9% fewer cpu cycles than 3.26.0 (the last version in that graphic).
jeffwiederkehr 3y ago
Not affilated in any way, listened to a podcast with Richard Hipp the founder of SQLite https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/. Super interesting especially his thoughts/work on exhaustive tests.
great_psy 3y ago
I’ve used SQLite for some small projects and it was a very pleasant experience. Does anyone have examples of the complex/ big projects that use SQLite ? I’m curious what problems they ran into, and how they were addressed.
- swasheck 3y ago
  expensify used sqlite iirc
  - w10-1 3y ago
    https://github.com/Expensify/Bedrock https://github.com/Expensify/Bedrock
    - swasheck 3y ago
      yep! thanks "Bedrock was built by Expensify, and is a networking and distributed transaction layer built atop SQLite, the fastest, most reliable, and most widely distributed database in the world." (https://bedrockdb.com/ https://bedrockdb.com/)
- kristopolous 3y ago
  I used to run a company with hundreds of in field machines that used sqlite for error reporting and various metrics. There were phone home scenarios of the databases transferring over to some server which then would get dumped in a directory. There were other systems that would use these hundreds of databases a few gigabytes each and create models from them for early failure detection and other things. I wish I could say this was difficult or didn't work but pretty basic scripting and sqlite on commodity hardware doing normal cpu computation got us there beyond our expectations using cheap digital ocean VPS servers with modest memory. I can be a pretty obnoxious brat when open source things are broken and nobody is taking them seriously. Here though, people took it seriously and it wasn't broken. I'm continually impressed by it. This was pre-pandemic for a timeline.
- nine_k 3y ago
  Firefox uses SQLite for history and other things. One of the problems is that the history may grow excessively large, which SQLite handles well but the History UI does not :)
ncrmro 3y ago
Started going really deep into SQLite. Couple hiccups like making sure when creating tables to use strict mode and enabling foreign keys that every one needs to read about. I exported about 8million rows from my Apple health records (about eight years for something’s like steps from before watch) and into a SQLite database from the xml dump using python (twice as fast a rust for some reason) in about 110 seconds. Dates are stored as Unix time stamps. Had ChatGPT generate some queries that showed my percent change in things like resting heart rate for each year using LAG and AVG return in less than a second. My job at work https://unsupervised.com/ https://unsupervised.com/ does a lot of KPI work so I’ve been interesting in learning how to to correlate data to show improvements to KPIs (in my case resting heart rate or lean body mass) Nextjs server components and https://github.com/kysely-org/kysely https://github.com/kysely-org/kysely also make working with SQLite a breeze.

SQLite 3.43

174 points by justinclift 3y ago

deleted 3y ago

[deleted]

OhNoNotAgain_99 3y ago

[dead]

milliams 3y ago

Does anyone know how the `make verify-source` works?

sltkr 3y ago
It's explained here: https://sqlite.org/getthecode.html#verifying_that_the_code_is_unmodified https://sqlite.org/getthecode.html#verifying_that_the_code_i...
Denvercoder9 3y ago
You can find the source here: https://www.sqlite.org/cgi/src/file?name=tool/src-verify.c&ci=trunk https://www.sqlite.org/cgi/src/file?name=tool/src-verify.c&c... It seems to use a manifest with hashes for every file generated by their VCS.
tedunangst 3y ago
[god damn is it annoying to navigate through fossil to actually find a file] It appears to be basically sha3sum against an included manifest file. https://sqlite.org/src/file?name=tool/src-verify.c https://sqlite.org/src/file?name=tool/src-verify.c I am kinda perplexed at the threat model resolved by including a manifest in the same tree as the adulterated sources, checked by a running a tool compiled from those same adulterated sources.
- deleted 3y ago
  [deleted]
- istjohn 3y ago
  Cosmic rays or other random sources of data corruption?
- inferiorhuman 3y ago
  [god damn is it annoying to navigate through fossil to actually find a file] Hey it still sucks less than the deuce Github's UI people dropped. They somehow managed to break using non-default branches. Imagine a world where git branches are an untested feature.
  - justinclift 3y ago
    > They somehow managed to break using non-default branches. Interesting. What's the thing with non-default branches that's broken?
    - inferiorhuman 3y ago
      The first two have have finally been fixed more or less but: The compact navigation bar hides the branch (this part has been fixed). Scroll down and you can no longer jump to a different branch without scrolling back to the top of the page… which takes a while because the code view widget (UGH) punts navigation key presses off into a black hole. The new symbol navigator only links to the default branch even if you're on a different branch. The compare branches button is gone. You have to open a pull request to compare branches (or manually craft the path). Hyperlinks within markdown that reference the repo only go to the default branch. Searching within a repo only searches default branch even if you're searching from a different branch. Granted the search results have never been good, but still. More or less pick a non-default branch and experiment a little. Chances are you'll be forced back to the default branch in short order. Even when the ref name is a parameter in the URL. And that's about the nicest thing I can say about the new Github UI. Pretty much everything else is just worse universally (blurry text, laggy find within page, nothing rendered if javascript is disabled, etc, etc, etc).
      - justinclift 3y ago
        Ouch. Yeah, those sound like a pain. I don't like the recent UI overhaul much either, as it makes navigating around a bunch harder in most situations. :/

jokoon 3y ago

It's funny, 10min ago, I had some comment on a 10 years old question of mine, and my question linked to another opinionated answer on stackoverflow. which still has 136 upvotes: https://stackoverflow.com/questions/1643365/why-no-love-for-sql/1643440#1643440 https://stackoverflow.com/questions/1643365/why-no-love-for-... I mean I was never crazy, RDBMS are useful, but SQL languages feel awkward not just to me. I'm curious about an alternative to the SQL language or API for a database system, and I guess that sort of RDBMS would also have a very different design. BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL. For some reason, python list comprehension/dict comprehension and sets somehow feel like a weird, light alternative to a database system. I just get the raw data with SQL and do data things I should do with SQL, and even if it's ill-advised, it feels so much better, and it works. Not to dismiss sqlite though, it's a wonderful software.

sedatk 3y ago
You might like EdgeQL https://www.edgedb.com/docs/edgeql/index https://www.edgedb.com/docs/edgeql/index
winrid 3y ago
The problem is this exposes you directly to storage mechanism internals. A good example of this is Redis. Redis is exactly what you described. It's just data structures as a service. For Redis - this works. It's its purpose. It's a tool. Same with KV stores. Again, you are working directly with the storage mechanism. However, now you're limited to that implementation detail. With SQL, or a traditional DB, your SELECT x by y could be served by a hashmap today, and maybe a btree tomorrow, and you don't need to change your app. Whether this makes sense for you depends entirely on your application. For loops in Python are okay for some apps. It won't work when you have a billion records.
summarity 3y ago
I use KQL (inside ADX/Kusto) at work, it's awesome. Not just saying that bc I work for an MSFT company, I legit enjoy it more than SQL. And with Kusto one can Python inside of KQL as well, which sounds bonkers but is actually pretty neat. I found through teaching people KQL that the learning curve (until they can do medium-complexity analysis) is also much shorter.

summarity 3y ago

This version is still buggy for large CTE or JOINs for edge cases. A bug has been introduced with the query planner optimizations in SQLite >3.39 (3.4X) that degrades query performance in some cases predictably by 3 orders of magnitude. So just to see if this was still a problem, I recompiled findsight.ai with this new amalgamation and yep, all queries that used to take about 300ms now take 3 to 10 seconds to complete. There's no difference in the query plans created, it's just a bug. I'll probably bisect by commit (or whatever Fossil calls it) at some point to nail down what changed, but then again 3.39 is rock solid enough I don't have a reason to upgrade. In the meantime, if you're running into one of these edge cases, try downgrading to 3.39. I also have no gripes, it clearly works well enough for many people (since by now most bundles and bindings are running 3.4X). Variants[1][2] of this bug have been reported, and some apparently fixed, but this continues to be an issue. [1] https://sqlite.org/forum/forumpost/bcc4375032 https://sqlite.org/forum/forumpost/bcc4375032 [2] https://sqlite.org/forum/info/96b9e5709cf47cda https://sqlite.org/forum/info/96b9e5709cf47cda

kccqzy 3y ago
While I sympathize with you, in both links the maintainer said it had been fixed. Maybe you ought to report a new bug? I didn't see any evidence regarding "maintainers consider[ing] this not a bug worth porting back fixes for because it still gives correct results" in your links.
- summarity 3y ago
  No, you are correct, it's just hard to debug something and produce a minimal case when the only change is "performance bad". This is mostly a hint to people that may run into similar issues while upgrading. And my reference concerned this comment (which I may have misunderstood, sorry if that's the case): > It is not planned for any patch releases because it is only a performance issue and not a malfunction.
  - TheRealPomax 3y ago
    patch releases are the third number (e.g. the "1" in "3.43.1"). The comment says nothing about it not qualifying as work to be done for a minor release (e.g. the "43" in "3.43"). If the bug is closed, and you still see it happening, file a new bug with a reproducible example so that the maintainers know it's (still) happening. And if that example means setting up a small dedicate repo with enough data to show the problem off, that's probably worth doing.

irrelative 3y ago

Anyone know why this chart[1] hasn't been updated in a while? One of my favorite displays of incremental improvements in a project. [1] https://www.sqlite.org/cpu.html https://www.sqlite.org/cpu.html

RVuRnvbM2e 3y ago
Looks like the web page graphic is not up to date. The data that the graph is based on is here (and is up to date): https://sqlite.org/docsrc/file?name=misc/speed-size-graph.ods https://sqlite.org/docsrc/file?name=misc/speed-size-graph.od... Looks like 3.43.0 uses about 9% fewer cpu cycles than 3.26.0 (the last version in that graphic).

jeffwiederkehr 3y ago

Not affilated in any way, listened to a podcast with Richard Hipp the founder of SQLite https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/. Super interesting especially his thoughts/work on exhaustive tests.

great_psy 3y ago

I’ve used SQLite for some small projects and it was a very pleasant experience. Does anyone have examples of the complex/ big projects that use SQLite ? I’m curious what problems they ran into, and how they were addressed.

swasheck 3y ago
expensify used sqlite iirc
- w10-1 3y ago
  https://github.com/Expensify/Bedrock https://github.com/Expensify/Bedrock
  - swasheck 3y ago
    yep! thanks "Bedrock was built by Expensify, and is a networking and distributed transaction layer built atop SQLite, the fastest, most reliable, and most widely distributed database in the world." (https://bedrockdb.com/ https://bedrockdb.com/)
kristopolous 3y ago
I used to run a company with hundreds of in field machines that used sqlite for error reporting and various metrics. There were phone home scenarios of the databases transferring over to some server which then would get dumped in a directory. There were other systems that would use these hundreds of databases a few gigabytes each and create models from them for early failure detection and other things. I wish I could say this was difficult or didn't work but pretty basic scripting and sqlite on commodity hardware doing normal cpu computation got us there beyond our expectations using cheap digital ocean VPS servers with modest memory. I can be a pretty obnoxious brat when open source things are broken and nobody is taking them seriously. Here though, people took it seriously and it wasn't broken. I'm continually impressed by it. This was pre-pandemic for a timeline.
nine_k 3y ago
Firefox uses SQLite for history and other things. One of the problems is that the history may grow excessively large, which SQLite handles well but the History UI does not :)

ncrmro 3y ago

Started going really deep into SQLite. Couple hiccups like making sure when creating tables to use strict mode and enabling foreign keys that every one needs to read about. I exported about 8million rows from my Apple health records (about eight years for something’s like steps from before watch) and into a SQLite database from the xml dump using python (twice as fast a rust for some reason) in about 110 seconds. Dates are stored as Unix time stamps. Had ChatGPT generate some queries that showed my percent change in things like resting heart rate for each year using LAG and AVG return in less than a second. My job at work https://unsupervised.com/ https://unsupervised.com/ does a lot of KPI work so I’ve been interesting in learning how to to correlate data to show improvements to KPIs (in my case resting heart rate or lean body mass) Nextjs server components and https://github.com/kysely-org/kysely https://github.com/kysely-org/kysely also make working with SQLite a breeze.