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

221–230 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#221

Post by sgarland »

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

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

Re: What if OpenDocument used SQLite? (2014)

#222

Post by mixmastamyk »

skybrian wrote: post #219

Implementing versioning in the file format conflicts with git, because each document is essentially its own little source control system. This can be surprising to users who copy the file and don’t realize that they’ve effectively copied the entire repo. Copying a file will sometimes include drafts they didn’t want to share. It can mean you lose control over when things are committed, and so you don’t end up with a u…

It's pretty rare to put office docs into version control, as they are typically binary instead of text. So, doesn't work well. Perhaps there is a version of open-doc that doesn't use the zip file but a folder of XML instead? Also the XML might need to be optimized to prefer line-oriented operations.

Re: What if OpenDocument used SQLite? (2014)

#223

Post by mixmastamyk »

fluidcruft wrote: post #185
didntcheck wrote: post #179

Earlier quoted context omitted.

> Not to mention that writing a single byte of content to a filesystems marks the entire blob as needing backup. If the size, mtime, and inode number stay the same (i.e. it writes into the file directly instead of replacing it), then most backup software will skip it. AFAIK to do otherwise you either need to read the whole file every time, or be live monitoring audit events to see what files have been opened for writ…

I am under the impression that modifying a file's content updates the modification time. Is this incorrect? Modifying a file without updating the mtime or allowing mtimes to be edited in userspace sounds like a security nightmare.

Yes it does. mtimes can definitely be edited, if you have permission, but it is rare. I have a photo script that pulls the taken time from Exif and writes it to the file mtime.

Re: What if OpenDocument used SQLite? (2014)

#224

Post by [deleted] »

indymike wrote: post #168

> there's no ISO standard defining how to interpret an SQLite file in excruciating detail. There comes a point where ISOing things doesn't help. The SQLite format belongs to SQLite, and an ISO standard would result in that standard being rendered irrelevant by the SQLite team, should they wish to make a change for any reason. Also, people would have to pay ISO for access to the specifications. SQLite should be treate…

[deleted]

Re: What if OpenDocument used SQLite? (2014)

#225

Post by marcinzm »

coliveira wrote: post #139
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…

But you don't need a standard, because all interaction between applications and the document is made through SQL. And SQL is standardized (at least the parts that matter). If you have concerns about compatibility, make sure that the document can also be accessed through other databases (like mysql).

>at least the parts that matter

In my experience every part matters in non-trivial use cases since someone somewhere will use that part.

Re: What if OpenDocument used SQLite? (2014)

#226

Post by simonw »

swiftcoder wrote: post #220

> The use of a ZIP archive to encapsulate XML files plus resources is an elegant approach to an application file format. It is clearly superior to a custom binary file format. I feel like I have considerable disagreement with the author of these sentences.

Why do you disagree?

Re: What if OpenDocument used SQLite? (2014)

#228

Post by orra »

bane wrote: post #146
orra wrote: post #141

Earlier quoted context omitted.

But other databases cannot access sqlite databases, because the file format is internal...

https://github.com/pgspider/sqlite_fdw

I'll admit, that's a fantastic third party effort. But there definitely isn't the same level of first party support as there is for zip files.

Re: What if OpenDocument used SQLite? (2014)

#229

Post by atoav »

rakoo wrote: post #134
fluidcruft wrote: post #116

Earlier quoted context omitted.

There is nothing I hate more than an app that modifies files secretly when I open them. Then I have to get all defensive to copy files before I open them to keep them intact. You may not see the problem with changing the checksum or hash of a file, but silently tampering with files is a nightmare in many domains. If you open a file and accidentally change something trivial (some apps like to store things like present…

You are right, this is a use case I absolutely did not take into account, but I want to separate user-defined actions and app-defined actions. A level of zoom is something a user does to read a document, but I wouldn't consider that as data to be persisted automatically, unlike characters typed or a font chosen. I value the idea of persisting it, but that would be a user-specific action ("Save view" or something like…

Why not do it like Blender: just autosave into some software directory, have the possibility to restore on crash, have the possibility to restore the last n autosaves from disk and add a setting for how many to save etc in the options.

Re: What if OpenDocument used SQLite? (2014)

#230

Post by dunham »

nojvek wrote: post #154
orra wrote: post #141

Earlier quoted context omitted.

But other databases cannot access sqlite databases, because the file format is internal...

SQL file format is very well documented. In some universities it is an assignment to directly read and write sqllite files from disk and understand the paged and blocks structure. You don’t need sql for any of it. https://www.sqlite.org/fileformat.html

It's interesting that this is a classroom assignment, like the sibling comment, I'd curious which university / class this was. I did the read part (+ query planning) on my own as an exercise, but I haven't gotten around to implementing writing yet.

You do need to parse DDL to get the column names, they're stored as a "CREATE TABLE" string. But you don't have to if you want to dump the file without names.

https://github.com/dunhamsteve/sqljs

Post reply on HN