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

What if OpenDocument used SQLite? (2014)

sqlite.org

Post reply on HN
Print view View original

191–200 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#191

Post by tannhaeuser »

mrighele wrote: post #132
stareatgoats wrote: post #119

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what yo…

> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from vers…

I agree, that doesn't make sense to me either. What about select versionId, max(checkinTime), min(checkinTime)? Can as well query SqlGPT. And above all, it's not what the SQL standard says when that's the entire point of using a standard in the first place.

Re: What if OpenDocument used SQLite? (2014)

#192

Post by paulddraper »

wongarsu wrote: post #189
paulddraper wrote: post #184

Earlier quoted context omitted.

MySQL allows the query, but the non aggregate fields are selected randomly

Following MySQL's longstanding tradition of just doing whatever instead of showing an error message, no matter how unreasonable the result.

MySQL + PHP, name a more iconic match

Re: What if OpenDocument used SQLite? (2014)

#193

Post by cryptonector »

stareatgoats wrote: post #119

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what yo…

That's basically short-hand for

  SELECT manifest, versionId, max(checkinTime)
  FROM version
  GROUP BY manifest, versionId
  ORDER BY 3 DESC LIMIT 1;
or

  WITH m AS (SELECT max(checkinTime) AS checkinTime FROM version)
  SELECT v.manifest, v.versionId, v.checkinTime
  FROM version v
  JOIN m m USING (checkinTime)
  LIMIT 1;
It's a bit of a footgun though because there is some randomness here if multiple rows have the same max checkinTime, so I try not to use this SQLite3-ism. You want to also do something to deterministically pick a "best" row, but for that you need to do something like the above.

Re: What if OpenDocument used SQLite? (2014)

#194

Post by cryptonector »

miki123211 wrote: post #136

The problem with SQLite is that it's not a standardized file format. It's well-documented and pretty well understood for sure, but there's no ISO standard defining how to interpret an SQLite file in excruciating detail. Same goes for competing implementations, Zip and XML have a much smaller API surface than SQLite, whose API, apart from a bunch of C functions, is the SQL language itself. Writing an XML parser is not…

This could have been used as an opportunity to standardize the SQLite3 DB file format.

Re: What if OpenDocument used SQLite? (2014)

#195

Post by jrockway »

naniwaduni wrote: post #156
cduzz wrote: post #78

Earlier quoted context omitted.

Well, copying is simply not atomic in linux; directory entry operations (rename, link, unlink) are atomic. There is a definition somewhere that says how many bytes may be written atomically; that's it -- past that writes are not atomic. The prior comment of "model user interactions in the database" seems spot on -- just keep track of what the user's doing as unsaved data in the database and commit it (in the appropri…

> The prior comment of "model user interactions in the database" seems spot on -- just keep track of what the user's doing as unsaved data in the database and commit it (in the appropriate way) to the DB as it happens; save is just another user action. The trouble is that often in the wild, the content of the file on the filesystem is a user-facing interface . Users will copy it around and attach the whole document o…

> they do not expect the file to contain data that they didn't want to save.

I think that's fine. Just remove "save" from the UI, and save after every keystroke. This may sound crazy in 1970, but it's how nearly everything works today. It's really only us weirdos that started using computers before "the cloud" that think "save" is an operation that does something, and we're dwindling in numbers!

Re: What if OpenDocument used SQLite? (2014)

#196

Post by sethev »

miki123211 wrote: post #136

The problem with SQLite is that it's not a standardized file format. It's well-documented and pretty well understood for sure, but there's no ISO standard defining how to interpret an SQLite file in excruciating detail. Same goes for competing implementations, Zip and XML have a much smaller API surface than SQLite, whose API, apart from a bunch of C functions, is the SQL language itself. Writing an XML parser is not…

You seem to be mixing up the file format with how it's used. An application that uses SQLite's file format would use SQLite's library as part of the application. Yes, it would be quite a lot of work to replicate that library but in the same way that replicating the code that uses OpenDocument's file format would be.

The file format itself is pretty straightforward.

Re: What if OpenDocument used SQLite? (2014)

#197

Post by cornstalks »

joshspankit wrote: post #162
bane wrote: post #157

Earlier quoted context omitted.

I'm not sure if the problem you are pointing out has to do with: a) SQLite the file format - which is Public Domain and so well documented that parsers for it exist in numerous other languages even though it's almost pointless because... b) SQLite, the Public Domain (and thus entirely source available) C implementation of the library that can operate on the file format -- and is documented to a level well above what…

Sounds like a solution is to use the C implementation to define the standard and have it canonized in to an ISO.

That's what Opus did. The RFC[1] has a base-64 encoded libopus.tar.gz appendix (Appendix A), which is the "primary normative part of this [Opus] specification." If the prose and source code disagree, the source code takes priority and "wins" when it comes to which is normative.

I have a love-hate relationship with this approach.

[1]: https://datatracker.ietf.org/doc/html/rfc6716

Re: What if OpenDocument used SQLite? (2014)

#198

Post by dfox »

cornstalks wrote: post #197
joshspankit wrote: post #162

Earlier quoted context omitted.

Sounds like a solution is to use the C implementation to define the standard and have it canonized in to an ISO.

That's what Opus did. The RFC[1] has a base-64 encoded libopus.tar.gz appendix (Appendix A), which is the "primary normative part of this [Opus] specification." If the prose and source code disagree, the source code takes priority and "wins" when it comes to which is normative. I have a love-hate relationship with this approach. [1]: https://datatracker.ietf.org/doc/html/rfc6716

That is common for codec standards, the normative part of many MPEG specifications is the parser/decoder in C-like pseudo-code. What is somewhat unique for Xiph is that their normative reference decoders are actually usable.

Re: What if OpenDocument used SQLite? (2014)

#199

Post by Lockal »

internetter wrote: post #126
Lockal wrote: post #113

Sadly they did not include bad sides: 1) Vulnerabilities: not only in SQLite, but also in wrappers like https://nvd.nist.gov/vuln/detail/CVE-2023-32697 2) Lack of transparency: zip with xml's contains only xml's; meanwhile SQLite contains by design all kinds of traces with sensitive information or empty blocks. Attempts to fix these issues removes benefits that were mentioned. 3) Lack of implementer support. It was o…

> Vulnerabilities: not only in SQLite, but also in wrappers like Yes, parsing encoded files tends to introduce vulnerabilities. ZIP parsers have had plenty of vulnerabilities. This is not exclusive to SQLite. > Lack of transparency: zip with xml's contains only xml's Both zips and sqlite cannot be read with a text editor. Both are open formats with widely available tools to read them. The sqlite binary might, in fact…

> parsing encoded files tends to introduce vulnerabilities

If we are talking about binary formats, now there are systematic solutions like https://github.com/google/wuffs that protect against vulnerabilities. But SQLite is not just a format - it's an evolving ecosystem with constantly added features. And the most prominent issue was not even in core, it was in FTS3. What will SQLite add next? More json-related functions? Maybe BSON? It is useful, but does not help in this situation.

Regarding traces, there are many forensics tools and even books about forensic analysis of SQLite databases. In well-designed format such tools should not exist in the first place. This is hard requirement: if it requires rewriting the whole file - then so be it.

Re: What if OpenDocument used SQLite? (2014)

#200

Post by nelgaard »

justin66 wrote: post #148
miki123211 wrote: post #136

The problem with SQLite is that it's not a standardized file format. It's well-documented and pretty well understood for sure, but there's no ISO standard defining how to interpret an SQLite file in excruciating detail. Same goes for competing implementations, Zip and XML have a much smaller API surface than SQLite, whose API, apart from a bunch of C functions, is the SQL language itself. Writing an XML parser is not…

> This isn't a concern for most software. It's not even a concern for the US Library of Congress, which defined SQLite as a recommended storage format for datasets alongside CSV, XML, and JSON.

But those are completely different uses of a storage format.

Library of congress considers if someone a 100 years from now could write a new importer in whatever langauge/AI they might use by then.

Office documents are something you send in email attachments to people you often barely know, and expect them to read it in whatever office system they have. And if the recipient uses e.g., Microsoft Word, OFD/Sqlite might not work.

Post reply on HN