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

231–240 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#231

Post by btilly »

bane wrote: post #157
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…

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…

It is possible that the C implementation of SQLite is the single most commonly deployed software library ever. If not, then it is probably the second, after zlib.

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

Therefore I consider it a better supported format than most standardized formats.

Re: What if OpenDocument used SQLite? (2014)

#232

Post by jimbokun »

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…

Just define the schema and the semantics of each column for each table.

Re: What if OpenDocument used SQLite? (2014)

#234

Post by paulddraper »

sgarland wrote: post #221
paulddraper wrote: post #184

Earlier quoted context omitted.

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

It only allows that if you’ve set it to do so. The default SQL_MODE variable includes ONLY_FULL_GROUP_BY. However, in their brilliance, AWS RDS defaults to only NO_ENGINE_SUBSTITUTION for SQL_MODE, thus merrily allowing partial aggregates with non-deterministic results. Wheee! https://github.com/awsdocs/amazon-rds-user-guide/issues/160

Prior to 5.7, MySQL always accepted non-aggregated fields.

Version 5.7 introduced ONLY_FULL_GROUP_BY, but since that change broke lots of code that depended on this historical behavior, many people disabled it.

Re: What if OpenDocument used SQLite? (2014)

#235

Post by kevincox »

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…

The docs don't make it clear that this works as stated. The first docs I found don't say that they come from the matching row:

From https://www.sqlite.org/lang_select.html#generation_of_the_se...

> Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression.

Does `max` somehow only affect the selected rows? Or is this relying on a side affect of the query planner sorting the table to optimize max?

However then I found https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...

> If there is exactly one min() or max() aggregate in the query, then all bare columns in the result set take values from an input row which also contains the minimum or maximum.

In all of the nearby examples contain an explicit "GROUP BY" clause but I don't think that this section says that one is required for this behaviour. So I guess this is the behaviour that is being described.

However I found this rule as well:

> If the same minimum or maximum value occurs on two or more rows, then bare values might be selected from any of those rows. [...] The choice might be different for different bare columns within the same query.

Which is in conflict with the earlier rule which says that the row is consistent. Or is this consistent row rule only provided for the implicit grouping. AKA is a and b guaranteed to be from the same row for the first query but not the second? That would be very surprising, maybe the docs just promise too little?

    SELECT a, b, MAX(c)
    FROM t

    SELECT 1 as group, a, b, MAX(c)
    FROM t
    GROUP BY 1
There are also more not-well sepcified results if multiple of MIN or MAX are used or if these functions or customized. So overall it is probably best to avoid this in "production" use. But can be convenient for some quick exportation if you are careful.

Re: What if OpenDocument used SQLite? (2014)

#236

Post by euroderf »

iefbr14 wrote: post #44
pgeorgi wrote: post #41

Earlier quoted context omitted.

WinFS ( https://en.wikipedia.org/wiki/WinFS ) without the mssql Engine?

Or this: https://github.com/narumatt/sqlitefs

Homebrew can't install its prerequisite osxfuse onto Ventura.

There is also this, which seems to work: https://github.com/jacobsa/fuse

and this: https://github.com/jilio/sqlitefs

Re: What if OpenDocument used SQLite? (2014)

#237

Post by toast0 »

nelgaard wrote: post #200
justin66 wrote: post #148

Earlier quoted context omitted.

> 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…

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

Eh, if they're not running the same office system, down to patches, you can't really expect much.

Re: What if OpenDocument used SQLite? (2014)

#238

Post by patapong »

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 sounds exactly like the argument that killed WebSQL in 2010: https://en.wikipedia.org/wiki/Web_SQL_Database

I am still salty about this, as WebSQL would have made it much easier to build a certain class of web apps.

Re: What if OpenDocument used SQLite? (2014)

#239

Post by mcpackieh »

rini17 wrote: post #11
nyanpasu64 wrote: post #4

I was optimistic that Audacity adopting SQLite would be a substantial improvement in its file saving capabilities. In practice I encountered many gotchas: - On Linux, saving into a new file onto a root-owned but world-writable NTFS mount created in /etc/fstab, fails due to permission errors or something. Saving into an existing file works as usual. - Files are modified on disk when you edit the project in the program…

Yes it should replicate the functionality user expects - save everything into temporary file and overwrite the original file only on explicit save action. As for Git, it would benefit from using text format specifically aimed for easy diffing/merging. No idea how easy the sqlite dump is in this regard.

I have been told that the new generation of users does not expect, want or appreciate applications that use explicit saves.

I've also been told that they don't understand or even want to understand folders...

Re: What if OpenDocument used SQLite? (2014)

#240

Post by asddubs »

paulddraper wrote: post #184
mrighele wrote: post #132

Earlier quoted context omitted.

> 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…

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

randomly, but after filtering by the criteria in the WHERE part of the query. This can actually be useful sometimes if all non-aggregate fields contain the same value (though I wouldn't actually rely on it, since whether this is allowed depends on how the database is configured, and it makes it easy to introduce errors by changing the query)
Post reply on HN