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

161–170 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#161

Post by kortex »

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…

There is nonzero overhead for doing so: optimizing for duplicate strings invariably adds cost to handling unique strings.

This sounds like something you could do at the schema and application level.

Re: What if OpenDocument used SQLite? (2014)

#162

Post by joshspankit »

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…

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

Re: What if OpenDocument used SQLite? (2014)

#163

Post by numeromancer »

amiga386 wrote: post #87
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…

Exactly. Some formats are designed, first and foremost, for interchange. SQLite is pitching that you, as an "app" owner, force the SQLite format upon your users to make it a de-facto standard, without putting the work in to make it a de-jure standard. Show me a formalised ISO / IEC / ANSI / ETSI SQLite standard that the Richard Hipp and his company never deviates from, and the full legal search to ensure there are no…

How much work does it take to go from an engine that can read standard XML to one that can read an ODT document's XML and do something useful with it? At what point of complexity does that engine create a de facto standard?

Re: What if OpenDocument used SQLite? (2014)

#164

Post by belenos46 »

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.

Yeah, a solution in search of a problem.

Re: What if OpenDocument used SQLite? (2014)

#165

Post by rakoo »

livrem wrote: post #137
rakoo wrote: post #95

Earlier quoted context omitted.

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

As described in https://fossil-scm.org/home/doc/trunk/www/tech_overview.wiki, all the commits are stored as artifacts, and then fossil creates metadata tables for quick access to useful information.

Configuration of a repo indeed isn't defined as an artifact but as a SQLite table. One may wonder if this should be part of a repo, and I would say it should, so it actually is surprising that it's not also stored as artifacts

Re: What if OpenDocument used SQLite? (2014)

#166

Post by p4bl0 »

rewmie wrote: post #61
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…

> I can directly make the change in the database I don't need to have a model of the document other than its database format. I don't get your point. Are you saying that you don't need to have a model of the document other than the model of the document? What's the nuance I'm missing?

When an application loads a document, for example if the document is formally a list of things (imagine a very simple TODO app), the usual approach is to have this data represented (modeled) as an actual list in your program, like a Python list of objects, because it's what is easy to manipulate programmatically.

Then, saving your document means serializing the data in some format (which could be JSON, XML, CSV, an SQLite database, …) and writing that to disk, and opening a document means reading the file from disk and unserializing it to your internal model.

What I'm saying is that my approach is to use an in-memory SQLite database as the internal model of the data in the applications. I presented an upside (opening and saving are easy), but is also has downsides: I have to do SQL queries to manipulate the data rather than manipulating objects directly (which could be mitigated using an ORM but that's outside my point). In Python-like pseudo-code you can imagining something like:

    self.todos[42].status = 'DONE'
vs

    self._db.query("UPDATE todos SET status='DONE' WHERE id=42")
(Of course there is the possibility of using ORMs or other approach in between the two.)

Re: What if OpenDocument used SQLite? (2014)

#167

Post by rakoo »

p4bl0 wrote: post #150
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…

> Why do you use a secondary, volatile database ? For the exact reason I gave in the comment you are replying to: I want to keep a usual workflow for users. Principle of least surprise. Users are okay with change being autosaved when there is a single "thing" that can be edited to the point that you don't even have to open it, it's just there, it can be seen as a property (as in ownership) of the application more tha…

You can keep a distinction between old and new version inside the same database, by having a pointer to the "current" version, and updating the pointer when clicking on "save". You could store all changes in the database in a "staging area", such that when you reopen the app you can load the changes and you don't need a recovery phase, but with the "save" button active meaning that something changed since last save.

Re: What if OpenDocument used SQLite? (2014)

#168

Post by indymike »

> 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 treated as a defacto standard defined by the SQLite project.

Re: What if OpenDocument used SQLite? (2014)

#169

Post by eviks »

pornel wrote: post #158
eviks wrote: post #100

Earlier quoted context omitted.

Then don't give people access to your drafts but exported versions without history? Why put the limits on the efficiency of a format by forcing it to store changes elsewhere?

It's better if such gotchas don't exist. Otherwise you'll have every user get burned by it at least once, and blaming them for not knowing the subtle consequences of using "Save As" instead of "Export As" is not going to help anyone.

There are plenty of burns on the other side as well with users losing edits and the consequences of copying your file in a file manager with/without some out-of-tree-out-of-sight history are even more subtle.

This is an app feature (it doesn't have to be "Export As", it can be a "clean history" toggle in the same "Save As" dialog and a separate command), so not a reason to excise efficient history preservation from the file format

Re: What if OpenDocument used SQLite? (2014)

#170

Post by kortex »

3cats-in-a-coat wrote: post #114

OpenDocument is zipped images and XML. Implying you parse the entire format and put it in RAM. And frankly I don't see how SQLite can improve this. Well XML isn't ideal, but it's zipped, so there's no huge penalty in size here. All benefits SQLite's article lists (and I love SQLite to death by the way) can be implemented by having SQLite be the runtime model of the document. On disk and in memory. But SQLite doesn't…

XML and Zip don't really do incremental updates, meaning the whole application file has to be written on save, meaning corruption can occur due to hiccups mid-write. Sqlite as a disk format and the right application implementation means you can't end up in a corrupted state.

I think you can achieve the same thing with xml/zip and some rename shenanigans, but sqlite lets you get that in a single file on disk.

Also if you are using sqlite as the memory model, why not use it as the disk/transport format? It's basically free at that point.

The file size issue can be dealt with VACUUM (I believe, haven't personally dealt with sqlite-as-file-format).

Post reply on HN