Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What If OpenDocument Used SQLite?

sqlite.org

Post reply on HN
Print view View original

91–100 of 109 posts

Re: What If OpenDocument Used SQLite?

#91

Post by tekkk »

The fundamental problem in my mind is the mixing of binary and text content. An optimal solution would separate them, allowing systems like Git do the versioning. But separating the tightly coupled parts into own files would also be annoying sharing/management wise.

Base64:ing the images into strings, like one could do with html, would probably not be ideal for compression. As a matter of fact, text-files as such would not be ideal compression-wise.

So I suppose if binary-format cant be avoided, SQLite would be as good as any other compression format. But without built-in collaboration protocol support, like CRDT, with history truncation (and diverged histories can always fall back to diff) I dont think it'd be good enough to justify the migration.

Re: What If OpenDocument Used SQLite?

#92

Post by liuliu »

Retr0id wrote: post #29
liuliu wrote: post #24

One thing I would call out, if you use SQLite as an application format: BLOB type is limited to 2GiB in size (int32). Depending on your use cases, that might seem high, or not. People would argue that if you store that much of binary data in a SQLite database, it is not really appropriate. But, application format usually has this requirement to bundle large binary data in one nice file, rather than many files that yo…

You can split your data up across multiple blobs

That's right, but it is much easier to just use blob without application logic to worry about chunking. It is the same reason why we use SQLite in the first place, a lot of transaction / rollback logic now is on SQLite layer, not the application layer.

Also, SQLite did provide good support for read / write the blob in streamable fashion, see: https://www.sqlite.org/c3ref/blob_read.html

So the limitation is really a structural issue that Dr. Hipp at some point might resolve (or not), but pretty much has to be resolved by SQLite core team, not outside contributors (of course you can resolve it by forking, but...).

Re: What If OpenDocument Used SQLite?

#93

Post by perching_aix »

charleslmunger wrote: post #43
agwa wrote: post #28

If you're going to use SQLite as an application file format, you should: 1. Enable the secure_delete pragma https://antonz.org/sqlite-secure-delete/ > so that when your user deletes something, the data is actually erased. Otherwise, when a user shares one of your application's files with someone else, the recipient could recover information that the sender thought they had deleted. 2. Enable the options described at…

>and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed. I think that's an unfair reading. Sqlite runs fuzzers itself and quickly addresses bugs found by fuzzers externally. There's an entire section in their documentation about their own fuzzers and thanking third party fuzzers, including credit to individual engineers. https://www.sqlite…

> The tone of the CVE docs are because people freak out about CVEs flagged by automated tools when the CVEs are for issues that have no security impact for typical usage of SQLite, or have prerequisites that would already have resulted in some form of compromise.

The CVE docs:

> The attacker can submit a maliciously crafted database file to the application that the application will then open and query

This is exactly the normal use case GP talks about with application file formats.

Re: What If OpenDocument Used SQLite?

#94

Post by psnehanshu »

sigwinch wrote: post #88
sakesun wrote: post #16

If I remember correctly Mendix project file format is simply a sqlite db. I thought the designer was lazy but it turns out it's a reasonable decision. Recently, DuckDB team raise similar question on DataLake catalog format. Why not just use SQL database for that ? It's simpler and more efficient as well.

With regard to DuckDB catalogs, I think a database is preferred for that. In particular, the tutorials assume PostGres.

It should be Postgres not PostGres. The latter looks weird.

Re: What If OpenDocument Used SQLite?

#95

Post by burnte »

korkor55 wrote: post #42
chmaynard wrote: post #9

Dr. Hipp occasionally gets on a soapbox and extolls the virtue of sqlite databases for use as an application file format. He also preaches about the superiority of Fossil over Git. His arguments generally make sense. I tolerate his sermons because he is one of the truly great software developers of our time, and a personal hero of mine.

These are thought-experiments to help better understand how SQLite works. This is exactly how supporting documentation should be written so that others read it. He even went over the top with the disclaimers.

I was skeptical at the start but by the end I didn't care if it was a good idea or a bad one, I learned so much it was a great read.

Re: What If OpenDocument Used SQLite?

#96

Post by sigmarule »

perching_aix wrote: post #93
charleslmunger wrote: post #43

Earlier quoted context omitted.

>and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed. I think that's an unfair reading. Sqlite runs fuzzers itself and quickly addresses bugs found by fuzzers externally. There's an entire section in their documentation about their own fuzzers and thanking third party fuzzers, including credit to individual engineers. https://www.sqlite…

> The tone of the CVE docs are because people freak out about CVEs flagged by automated tools when the CVEs are for issues that have no security impact for typical usage of SQLite, or have prerequisites that would already have resulted in some form of compromise. The CVE docs: > The attacker can submit a maliciously crafted database file to the application that the application will then open and query This is exactly…

On the other hand, exploiting weaknesses in MITRE’s CVE program to create ticket management primitives, creating “shellcode” that composes them to implement a feature request tracking API, using it to manage your open source organization’s feature roadmap, sure would make for a great 2600 article…

Re: What If OpenDocument Used SQLite?

#97

Post by [deleted] »

agwa wrote: post #28

If you're going to use SQLite as an application file format, you should: 1. Enable the secure_delete pragma https://antonz.org/sqlite-secure-delete/ > so that when your user deletes something, the data is actually erased. Otherwise, when a user shares one of your application's files with someone else, the recipient could recover information that the sender thought they had deleted. 2. Enable the options described at…

[deleted]

Re: What If OpenDocument Used SQLite?

#98

Post by netsharc »

jdboyd wrote: post #60
floating-io wrote: post #3

An interesting skim, but it would have been more meaningful if it had tackled text documents or spreadsheets to show what additional functionality would be enabled with those beyond "versioning". Maybe it's just me, but I see the presentation functionality as one of the less used aspects of the OpenOffice family.

What he listed as the first improvement, "Replace ZIP with SQLite" would certainly apply to the other ODF formats. He advocates breaking the XML into smaller pieces in SQLite. I suppose making each slide a new XML record could make sense. Moving over to spreadsheets, I don't know how ODF does it now, but making each sheet a separate XML could make sense. Thinking about Write documents, I wonder what a good smaller un…

> I think one XML per page would be too fine a granularity.

If I add a 1/3 page graphic on page 2, it'd have to repaginate pages 2-n of that chapter, modifying n-1 XML files...

Re: What If OpenDocument Used SQLite?

#99

Post by charleslmunger »

perching_aix wrote: post #93
charleslmunger wrote: post #43

Earlier quoted context omitted.

>and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed. I think that's an unfair reading. Sqlite runs fuzzers itself and quickly addresses bugs found by fuzzers externally. There's an entire section in their documentation about their own fuzzers and thanking third party fuzzers, including credit to individual engineers. https://www.sqlite…

> The tone of the CVE docs are because people freak out about CVEs flagged by automated tools when the CVEs are for issues that have no security impact for typical usage of SQLite, or have prerequisites that would already have resulted in some form of compromise. The CVE docs: > The attacker can submit a maliciously crafted database file to the application that the application will then open and query This is exactly…

That's true, but most usage of sqlite is not as an application file format, and many of those CVEs don't apply even to that use case. The reason people have policies around CVE scanning is because CVEs often represent real vulnerabilities. But there's also a stuff like "this regex has exponential or polynomial runtime on bad inputs", which is a real security issue for some projects and not others, depending on what the input to the regex is. That's also true for SQLite, and I'm guessing that the author of that page has spent a bunch of time explaining to people worried about some CVE that their usage is not vulnerable. The maintainer of cURL has expressed similar frustration.

Re: What If OpenDocument Used SQLite?

#100

Post by duskwuff »

Mikhail_Edoshin wrote: post #85
duskwuff wrote: post #13

Earlier quoted context omitted.

You can't* index into XML. You have to read through the whole document until you get to the part you want. *: without adding an index of your own, at which point it isn't really XML anymore, it's some kind of homebrew XML-based archive format.

This applies to any secondary index. The data themselves can only be ordered by a single criteria. It may be a meaningful one, but I guess in most cases it is merely the internal ID, which means you will have to scan the whole table too. XML was meant for documents so in most cases the sequence of elements is given. But technically if I compose XML myself I can lay it out the way I want and thus can have it sorted to…

Blindly seeking into XML data is a risky, error-prone approach. It's not impossible to do, but doing it correctly is difficult - even if the tags you're looking for are unique, there are a lot of messy edge cases involving comments and blocks.
Post reply on HN