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

21–30 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#21

Post by severak_cz »

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…

SQLite is already used for exactly this purpose. It's used as OGC GeoPackage and Mabox/Maptiler datasets use this.

Re: What if OpenDocument used SQLite? (2014)

#23

Post by quickthrower2 »

vmfunction wrote: post #5

At this point, why are we still using JSON/XML when there is SQLite for new projects? Stop the non sense of JSON/XML. SQLite is like json, but very queryable. Just send SQLite files around. MongoDB also saves document db type of store space just FYI.

With JSON/XML the app owner decides the schema of the saved file, as they should. One day Sqlite will do some perfectly fine change that’ll break people who outsource their file format to it. Own your file format!

That said there is some nuance and it depends what the user expects. Is you app more of an MSWord where people expect a format that is decades backward compatible and only changes on explicit save, or is it more like a live app with a db back end. If the latter there should be no save concept around the DB file but perhaps a backup and restore function that exports to a controlled format.

Re: What if OpenDocument used SQLite? (2014)

#24

Post by nyanpasu64 »

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…

> The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

> The VACUUM INTO command works the same way except that it uses the file named on the INTO clause in place of the temporary database and omits the step of copying the vacuumed database back over top of the original database.

Do you use VACUUM (uses a write-ahead log to survive power-off) or VACUUM INTO (as far as I can tell, it doesn't survive power-off during writing, and might corrupt the existing file contents if the filename already exists)?

Re: What if OpenDocument used SQLite? (2014)

#25

Post by capableweb »

paradox460 wrote: post #16
capableweb wrote: post #7

Earlier quoted context omitted.

Hmm, me too, and Wikipedia says: > OpenDocument - Initial release: 1 May 2005; 18 years ago > SQLite - Initial release: 17 August 2000; 23 years ago Wonder what gives.

OpenDocument traces it's ancestry to OpenOffice XML format, which traces it's ancestry to StarOffice, which was xmlized around the time Sun bought it in 1999

Thanks for clarifying the somewhat messy history of the format!

Re: What if OpenDocument used SQLite? (2014)

#26

Post by tuyiown »

punnerud wrote: post #20
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…

Have you checked the Apple apps? Most of them use SQLite as storage format. iMovie, iPhoto, Voice recording… Same with Docker. Can’t be that wrong?

App using a format specific to their own and unique implementation, that ends up kind of proprietary is perfectly ok.

Using it for a open specification which target is cross implementation compatibility makes the move way more hazardous. Meaning, every implementation has to run on environment targetable and compatible wit sqlite or has to re-implement a compatibility layer on something complex enough that you only reliable definitive source of truth is the very famous sqlite test suite.

It the same reason why Web SQL has being abandoned: if sqlite is the sole api implementor, it takes precedence on any others specs, and you have no control on your standard.

I would be 100% for a specification on how to map open docs files to an relational structure, though, with a well know sqlite-backed implementation.

Re: What if OpenDocument used SQLite? (2014)

#27

Post by nextaccountic »

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…

This means that like a regular app, you lose data if the app crashes or there is a power loss.

It's much better to save after each operation in a temporary place (probably in ~/.local/share/application/yourapp, using XDG directories), and when the user clicks save, just copy the file into the desired location. That way, if there is a power loss and you reopen the app, it opens right back where it was doing (losing maybe he last few seconds of changes, but not all unsaved data)

Re: What if OpenDocument used SQLite? (2014)

#29

Post by jve »

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…

- Why not? https://www.sqlite.org/appfileformat.html

- Its size is less than a megabyte: https://sqlite.org/footprint.html

- 750KB if all features are enabled: https://www.sqlite.org/about.html

- Looks like fair amount of functionality can be left out when compiling sqlite and with options to influence/strip down query planner: https://www.sqlite.org/compile.html

- And "SQLite does not compete with client/server databases. SQLite competes with fopen()": https://www.sqlite.org/whentouse.html

In the end, you don't need a database, but a library that gives you database API and behavior.

Re: What if OpenDocument used SQLite? (2014)

#30

Post by m4rtink »

vetinari wrote: post #10
out_of_protocol wrote: post #2

Other example: raster map tiles (basically up to millions of tiny square pictures) Zip vs tar vs filesystem vs sqlite. Tested all these scenarios, and sqlite was the fastest and the smallest, even beating plain archives with no overhead

Many filesystems have an issue with tens of thousands or more files in a single directory, which is exactly what you can get with map tiles. No wonder sqlite is faster.

Yeah, that's why sqlite was adopted for this back then - many devices still used FAT32 on the storage volumes where tiles we often stored/cached and that had horrendous small file performance - a plain white 130 Byte PNG tile could result in 64 kB being used.
Post reply on HN