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

131–140 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#131

Post by ilyt »

p4bl0 wrote: post #6

I'm currently working on an application where I use SQLite as the file format. I want to keep a usual workflow for users where you can make edit to your document and it only changes the file when you save it. So to open a file I copy it into the :memory: database [1], then the user can do whatever manipulation they want and I can directly make the change in the database I don't need to have a model of the document ot…

We used something similar (DB doing caching run in memory but saved periodically on disk) but with backup API

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

Re: What if OpenDocument used SQLite? (2014)

#132

Post by mrighele »

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 version order by checkinTime desc limit 1

or something like that. This should work in SQlite and Postgresql at the minimum. I think to remember that in Oracle you have to use "where rownum=1" so indeed you have to use a nested query. I don't know about other databases.

Re: What if OpenDocument used SQLite? (2014)

#133

Post by The_Colonel »

cm2187 wrote: post #85

Sqlite format is smaller than the original format only because xml is super verbose, so any uncompressed binary format ends up being less than lightly zipped xml. But sqlite files aren't small. One thing I don't understand is why they don't do string deduplication in sqlite (as in you only store a string once and every other occurence is just a pointer to that string). It seems such an obvious and easy way to reduce…

If you have the same (long-ish) string repeating many times in a database, it points to a DB schema needing normalization.

Re: What if OpenDocument used SQLite? (2014)

#134

Post by rakoo »

fluidcruft wrote: post #116
rakoo wrote: post #86

Earlier quoted context omitted.

Why do you use a secondary, volatile database ? Performance-wise you won't gain a lot more (we're talking about a user editing a file, so not even 1 write per second). A proposal: write directly, and automatically in the database. No more Save button. There are multiple advantages: - the system is crash-resistant. I like taking the approach of CouchDB where the only correct way to close the system is to crash it. Tha…

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 that)

In the case of checksums in a database, that is why read-only modes should be used and I don't see what automatically saving would change. If anything, when the user zooms on a document in read-only mode, either it shouldn't be stored or storing it should trigger the same flow as modifying the document

Re: What if OpenDocument used SQLite? (2014)

#135

Post by gwbas1c »

I shipped a product that used both SQLite and XML files.

One of the improvements that I made was moving a few tables that contained small amounts of data to xml files. Because these files were small and rarely written; it simplified the data access layer, and simplified diagnostics. (I made sure the files were multi-line tabbed xml.)

For "technical" people who needed to diagnose the product, asking them to crack open a SQLite database was a huge ask; but for the major part of the product that used SQLite, it was hands-down better than XML files. (An older version of the product used XML files. It had scalability problems because there's no good way to make an incremental update to an XML file.)

The advantages of XML, specifically, a human-readable format; really only work for small files when the design of the schema is optimized for readable XML. Unfortunately, the need to always rewrite the entire XML file, and the "complexities" that come with lots and lots of features will quickly erode XML's biggest advantages.

IMO: A "lay" person needing to muck around with the internals of an office document is fringe enough that learning to use a SQLite reader is an acceptable speed bump. The limitations of XML + Zip, when it comes to random writes in the middle of a file, just can't be overcome by Moore's law.

Re: What if OpenDocument used SQLite? (2014)

#136

Post by miki123211 »

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 a trivial task, but it's still simpler than writing an SQL parser, query optimizer, compiler, bytecode VM, full-text search engine, and whatever else Sqlite offers, without any data corruption in the process. If Open Office used SQLite, its programmers would inevitably start using its more esoteric features and writing queries that a less-capable engine wouldn't be able to optimize too well.

This isn't a concern for most software. If you're writing a domain-specific, closed-source application where interoperability with other apps or ISO standardization isn't a concern, SQLite is a perfectly fine file format, but as far as I understand the situation, those concerns did exist for Open Office.

Re: What if OpenDocument used SQLite? (2014)

#137

Post by livrem »

rakoo wrote: post #95
orf wrote: post #14

Coupling a file format to SQLite smells wrong. SQLite is good, but it is also fairly unique in this space. Why? Because it’s hard to replicate everything it does, because it does a lot. But… for this case, do we need it do a lot? No, not really. We don’t need the full SQL standard, a query optimiser, etc etc for basic (+ safe) transaction semantics and the ability to store data in a basic table structure. Perhaps the…

To add to your point, fossil, the versioning system designed by the people of SQLite, and using SQLite, doesn't even use SQLite as a file format. It's all a bunch of blobs, each with its own format, that happen to be stored on SQLite. SQLite offers safe storage and a bunch of helpful indexes and views, but is not necessary for fossil-the-data to work.

Looking in sqlite.fossil there are 27 tables in it and most are not used for storing blobs. I know when looking up how to do things in the past the answer has sometimes been "run this SQL query". The event table for instance looks like a list of all commits with dates and comments etc. There is a config table that looks like the kind of stuff git stores in .git/config (URL to upstream repo etc) and so on. Well, yes there are some blobs in it too.

Re: What if OpenDocument used SQLite? (2014)

#138

Post by globular-toast »

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…

I think this is a convenient side-effect of the implementation which was later turned into official behaviour. A bit like Python dictionary key ordering.

In Postgres you can do similar things with a DISTINCT ON query.

I always found this one of the hardest simple things to do in SQL.

Re: What if OpenDocument used SQLite? (2014)

#139

Post by coliveira »

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

Re: What if OpenDocument used SQLite? (2014)

#140

Post by globular-toast »

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…

The interesting thing is if you want more than one record, like you want the latest version number for each document ID. In SQLite you could do: `SELECT documentId, versionId, max(checkInTime) FROM version GROUP BY documentId`. In Postgres you can do `SELECT DISTINCT ON (documentId) documentId, versionId, checkInTime FROM version ORDER BY versionId, checkInTime DESC`.

See: https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...

Post reply on HN