[HN Gopher] What if OpenDocument used SQLite? (2014)
___________________________________________________________________
What if OpenDocument used SQLite? (2014)
Author : weeber
Score : 418 points
Date : 2023-09-18 08:11 UTC (14 hours ago)
(HTM) web link (www.sqlite.org)
(TXT) w3m dump (www.sqlite.org)
| samjohnation111 wrote:
| [dead]
| nuc1e0n wrote:
| It's somewhat off topic I know, but is there something like
| sqlite but tailored for hierachical data? Like a xml document
| store rather than for relational data like sqlite is.
| layer8 wrote:
| There's ASN.1 for hierarchical data with a schema. It doesn't
| provide a query language though.
| dfox wrote:
| ASN.1 in itself is a schema syntax. That schema can be
| serialized into various related forms, but all of them are
| more or less a transport formats that cannot be reasonably
| used for random access.
|
| There are some more or less general hierarchical formats with
| support for random access, but most of them are tightly
| related with particular technology stack (ie. MS's COM
| Compound Document) or with particular usage area (there is
| HDF5 for scientific data and many multimedia containers are
| in fact a hierarchical databases, with both the various IFF
| variants and EBML being explicitly designed as reusable
| formats for arbitrary data). And then there are formats that
| implicitly contain some kind of hierarchical container
| mechanism (PDF, TIFF, DICOM, FPS game map files...).
| gwbas1c wrote:
| 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.
| Tempest1981 wrote:
| I'm unclear on how SQLite (native format, no zip) is achieving
| sizes similar to XML + Zip. Are SQLite TEXT or BLOB fields
| compressed? Or are they assuming the caller is compressing
| BLOBs before writing?
| tannhaeuser wrote:
| Yeah what if? Then they haven't really understood the purpose of
| markup languages as plain text files for viewing/editing using
| generic text editors. There was no lack of proprietary formats
| such as MS Structured Format (used by MSO) and it was considered
| a big success when customers demanded open formats such as
| SGML/XML-based ones in late 90s/00's. The alternatives aren't
| even sequential (have fragments and cross pointers, etc). Yes
| they might be faster because they're closer to the in-memory
| representations as used by the original/historic app or even
| primitive memory dumps; marginal speed or size improvements were
| never a consideration though. And if anything, SQL (almost as old
| as SGML btw) is a joke as document query language compared to
| basically any alternative specifically designed for the job (ISO
| topic maps query language ie. Datalog, XPath and co, SPARQL,
| DSSSL/Scheme, ...) because of SQL's COBOLness, non-
| schemalessness, lock semantics/granularity a really bad fit,
| etc.).
| roywashere wrote:
| (2014)
| Freak_NL wrote:
| Good article. Although one thing I do like about OpenDocument
| being just a bunch of XML files in a ZIP archive is that it is
| fairly easy to generate documents like spreadsheets without using
| a (potentially hefty) library which knows about the document
| format.
|
| I have a use case where users of a web service want to use data
| exported as a bunch of rows in a table in a variety of tools.
| Now, CSV with UTF-8 encoding is of course, totally open,
| conventional, and workable, but anyone who has ever offered CSV
| files to end users will know the pain of these users getting
| stuck when they want to use these files in a spreadsheet
| application1. So I saved a sample spreadsheet in OpenDocument's
| ODS and another in that Microsoft XML abomination called OOXML as
| XLSX, and just figured out the basics of those XML formats. I
| trimmed the ZIP archives down to the essentials, marked the
| places where content goes, and just build a new spreadsheet file
| whenever data is requested in that format. Now I can output CSV,
| ODS, and XLSX (and JSON thrown in for good measure) of the same
| data.
|
| Doing this with SQLite would be possible of course, just a tad
| more complex and with a lower development speed. Being able to
| fire up the office suite, create a template document, and just
| dig into its XML files in the saved file is a nice feature
| (although admittedly of niche interest).
|
| 1: More specifically, users who use Excel in a locale like nl_NL,
| where CSV files are, hardcoded, assumed to have their columns
| separated by semicolons, because Microsoft once notoriously
| decided that the Dutch did not use comma's in a comma separated
| values file.
| dfox wrote:
| As for [1], it is not really hardcoded, but depends on what is
| the value of localeconv()->decimal_point, if it is ",", excel
| uses semicolons both in CSV files and formula expression
| language.
|
| This used to be configurable when opening CSV/TXT file in excel
| (and still is in LibreOffice) but as a part of the overall UI
| dumbification was moved somewhere under the "Data" menu/ribbon
| tab (so you have to open new workbook and find the right
| option, or well, use LibreOffice if you value your time).
| Freak_NL wrote:
| > decimal_point
|
| Are you sure that affects it? The decimal point parameter
| sounds like it decides how to write out 51/2 (i.e., 5.5
| (English style) or 5,5 (Dutch style)) surely? Although on the
| topic of this particular bete noire I would not be surprised.
| Lockal wrote:
| Sadly they did not include bad sides:
|
| 1) Vulnerabilities: not only in SQLite, but also in wrappers like
| https://nvd.nist.gov/vuln/detail/CVE-2023-32697
|
| 2) Lack of transparency: zip with xml's contains only xml's;
| meanwhile SQLite contains by design all kinds of traces with
| sensitive information or empty blocks. Attempts to fix these
| issues removes benefits that were mentioned.
|
| 3) Lack of implementer support. It was one of the reasons for
| WebSQL deprecation many years ago.
|
| 4) Lack of standardization for file format. SQLite does not even
| promise forward compatibility, only backward one. Which means
| that new documents might not open in old software, or vendor
| should fork SQLite and only backport security patches.
| jmull wrote:
| 4) is enough for me, so I agree with your general point, but 1)
| 2) and 3) aren't really cons for SQLite.
|
| 1) Makes sense only if the average XML parsers and zip
| libraries in use have fewer vulnerabilities and are actively
| maintained as well.
|
| 2) You can store sensitive data in a SQLite database or XML
| file, there's no real difference. You can clean up a SQLite
| database pretty easily if you want and that doesn't take away
| all the benefits.
|
| 3) What does implementer support even mean? I believe they are
| open to custom work... WebSQL died because it doesn't make
| sense to pretend SQLite is some kind of standard -- that brings
| us back to 4), which is the valid reason to avoid SQLite.
|
| Actually, your 4) is worded too strongly. They say they're
| committed to forward compatibility as long as you don't use the
| new features. That makes forward compatibility the decision of
| the app: an app can have forward compatibility and not use
| newer features OR lose forward compatibility and use newer
| features.
| internetter wrote:
| > Vulnerabilities: not only in SQLite, but also in wrappers
| like
|
| Yes, parsing encoded files tends to introduce vulnerabilities.
| ZIP parsers have had plenty of vulnerabilities. This is not
| exclusive to SQLite.
|
| > Lack of transparency: zip with xml's contains only xml's
|
| Both zips and sqlite cannot be read with a text editor. Both
| are open formats with widely available tools to read them. The
| sqlite binary might, in fact, be more widely available than
| unzipping tools.
|
| > meanwhile SQLite contains by design all kinds of traces with
| sensitive information or empty blocks.
|
| Elaborate?
|
| > Lack of implementer support. It was one of the reasons for
| WebSQL deprecation many years ago.
|
| I don't understand how this is relevant?
|
| > SQLite does not even promise forward compatibility, only
| backward one. Which means that new documents might not open in
| old software
|
| Neither does OpenDocument. SQLite is actually more solid in
| this regard - forwards compatibility is still a thing unless
| new features are used.
| naniwaduni wrote:
| > Both zips and sqlite cannot be read with a text editor.
| Both are open formats with widely available tools to read
| them.
|
| Well, that's why your archive format of choice should be
| cpio, which is almost a text file except that modern
| implementations tend to 0-terminate the filename!
|
| Jokes aside, there are widely-distributed tools that can take
| in an almost-arbitrary zip file and account for every byte in
| it. The format is straightforward enough that, were you so
| inclined, you can do most of it (other than, like,
| decompression and crc-checking) manually in a text editor.
| The SQLite format is not like this. There is one
| implementation, and relatively easy to "hide" data in a
| database file that its tooling will not reveal.
| mcpackieh wrote:
| > > _meanwhile SQLite contains by design all kinds of traces
| with sensitive information or empty blocks._
|
| > _Elaborate?_
|
| When you delete something from a SQLite database, it isn't
| necessarily _actually removed_ from the file unless you
| VACUUM or have the secure_delete PRAMGA turned on. Either of
| these should solve the problem.
|
| VACUUM INTO is a good way to export sqlite databases from an
| application for this reason.
| Lockal wrote:
| > parsing encoded files tends to introduce vulnerabilities
|
| If we are talking about binary formats, now there are
| systematic solutions like https://github.com/google/wuffs
| that protect against vulnerabilities. But SQLite is not just
| a format - it's an evolving ecosystem with constantly added
| features. And the most prominent issue was not even in core,
| it was in FTS3. What will SQLite add next? More json-related
| functions? Maybe BSON? It is useful, but does not help in
| this situation.
|
| Regarding traces, there are many forensics tools and even
| books about forensic analysis of SQLite databases. In well-
| designed format such tools should not exist in the first
| place. This is hard requirement: if it requires rewriting the
| whole file - then so be it.
| CodeCompost wrote:
| There really should be a "NoSQLite" or something equivalent to
| store hierarchical data instead of normalized data.
| remram wrote:
| You can probably use SQLite for that, with a single key-value
| table.
| ttyprintk wrote:
| The json* family of tree and table functions are nowadays
| built in.
| OliverJones wrote:
| It's trivial to implement hierarchical data with recursive
| common table expressions. https://www.sqlite.org/lang_with.html
| littlecranky67 wrote:
| deleted.
| littlestymaar wrote:
| > Nobody really believes that OpenDocument should be changed to
| use SQLite as its container instead of ZIP. [...] Rather, the
| point of this article is to use OpenDocument as a concrete
| example of how SQLite can be used to build better application
| file formats for future projects.
| ongytenes wrote:
| Would be interesting to see a fork implementing SQLite. Time
| would tell how well it would compete with the standard.
| kgeist wrote:
| Sqlite-based file formats are also very easy to debug, which
| saves a lot of dev time. After my app writes to a file and
| loading back doesn't work, I can just open it in Sqlite and
| inspect it in any way I wish because I have the full power of SQL
| at my fingertips.
| 3cats-in-a-coat wrote:
| 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
| need to be the transport format. In fact SQLite can easily get
| bigger than the current format, SQLite is full of unused space
| when you mutate it around, it can get fragmented and sparse. And
| if you need to optimize it every time, then the "fast save" etc.
| benefit goes away.
|
| There are formats which do need delta updates and quick indexed
| look-ups without fully loading the file in RAM, and this is why
| so many apps do use SQLite as a file format. I just feel
| OpenDocument was a bad pick to use SQLite for in this
| hypothetical scenario.
| kortex wrote:
| 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).
| 3cats-in-a-coat wrote:
| Incremental updates don't matter in a transport format.
|
| The claim "it's basically free" isn't right, as for transport
| you need to VACUUM. And possibly COMPRESS too. And if you do
| that... might as well use the existing format. VACUUM
| completely rewrites the file from scratch. You can't do
| incremental updates in a VACUUMed file as it stops being
| VACUUMed, so you need to VACUUM it again to ensure minimal
| file size. Nothing is free.
|
| ZIP also can be incrementally updated (file by file) by the
| way, I think MS Word uses this feature in some saves. But
| that's beside the point. You simply do not need incremental
| updates in a transport format.
|
| I'm not sure what "hiccups mid-write" you're referring to.
| Any such hiccup that would damage an XML or ZIP file would
| also damage an SQLite file.
|
| The distinction between a working disk file and a transport
| format are important. The working disk file is large, binary,
| messy, complex, optimized for quick look-ups and quick
| partial updates. If your word processor crashes, it can
| restore state from the working disk format in no time.
|
| But the transport format needs to be small, readable,
| debuggable, simple, stable. And SQLite simply doesn't offer
| anything significantly superior in that department compared
| to the existing format. Especially nothing to justify the
| additional effort of changing an already working solution.
|
| There's a reason "serialization" is called that, it's just
| serial data. No random access structures, no indices, single
| representation, often text-based. Throughout the decades,
| we've learned this is the best way to transport data of any
| kind. The
| messy/partial/polymorphic/cryptic/hyperoptimized/indexed
| formats are not for transport. They're intended to do work
| in, locally.
| [deleted]
| michalc wrote:
| Shameless plug of a couple of Python libraries I've been involved
| with that work around memory issues of ODS files (for very
| specific use cases):
|
| https://github.com/uktrade/stream-read-ods
| https://github.com/uktrade/stream-write-ods
| miki123211 wrote:
| 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.
| justin66 wrote:
| > 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.
| nelgaard wrote:
| 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 not work.
| justin66 wrote:
| It is true that it requires effort for the developers of a
| software program to support a given file format. Beyond
| that I'm not sure what your point is.
| galangalalgol wrote:
| Not the op, but one point would be, why did we even pick
| xml, when we had latex and html? Why is a relational
| database the right tool for a document format?
| toast0 wrote:
| > 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.
| jimbokun wrote:
| Just define the schema and the semantics of each column for
| each table.
| hot_gril wrote:
| I've never seen this as a problem, since plenty of random
| things are distributed as sqlite files. All the remaining
| questions for ODF would be about the schema design.
| coliveira wrote:
| 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).
| marcinzm wrote:
| >at least the parts that matter
|
| In my experience every part matters in non-trivial use cases
| since someone somewhere will use that part.
| orra wrote:
| But other databases cannot access sqlite databases, because
| the file format is internal...
| nojvek wrote:
| 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
| avinassh wrote:
| > In some universities it is an assignment to directly
| read and write sqllite files from disk and understand the
| paged and blocks structure.
|
| do you have any links?
| dunham wrote:
| 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
| hot_gril wrote:
| They can if they want to, using the standard SQLite lib or
| their own implementation.
| bane wrote:
| https://github.com/pgspider/sqlite_fdw
| orra wrote:
| 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.
| sethev wrote:
| You seem to be mixing up the file format with how it's used. An
| application that uses SQLite's file format would use SQLite's
| library as part of the application. Yes, it would be quite a
| lot of work to replicate that library but in the same way that
| replicating the code that uses OpenDocument's file format would
| be.
|
| The file format itself is pretty straightforward.
| gwd wrote:
| > 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.
|
| Just to clarify: You don't actually need to implement all that
| for it to be a standardized file format, any more than you need
| to implement all the spreadsheet functionality to be able to
| read a LibreOffice spreadsheet. All you need to do is to be
| able to reconstruct the tables. There's no reason, having
| reconstructed the tables, you couldn't write your own
| imperative code in the language of your choice to go over them
| and get whatever information you wanted.
| bane wrote:
| 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
| most ISO standards shoot for. It's designed to be used in other
| software and has bindings for pretty much every major language.
|
| c) Some notional OpenDocument stored in a SQLite file that's
| really just waiting for somebody to make and document.
|
| ISO standards are great, but if we had to wait for ISO to
| define a file format we'd have pitifully little to work with.
| btilly wrote:
| 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.
| lucideer wrote:
| That page makes the argument for zlib & sqlite, but Daniel
| Stenberg makes some good points here[0].
|
| My guess would be zlib is still number 1 though, even
| accounting for Daniel's considerations.
|
| [0] https://daniel.haxx.se/blog/2021/10/21/the-most-used-
| softwar...
| joshspankit wrote:
| Sounds like a solution is to use the C implementation to
| define the standard and have it canonized in to an ISO.
| belenos46 wrote:
| Yeah, a solution in search of a problem.
| cornstalks wrote:
| That's what Opus did. The RFC[1] has a base-64 encoded
| libopus.tar.gz appendix (Appendix A), which is the "primary
| normative part of this [Opus] specification." If the prose
| and source code disagree, the source code takes priority
| and "wins" when it comes to which is normative.
|
| I have a love-hate relationship with this approach.
|
| [1]: https://datatracker.ietf.org/doc/html/rfc6716
| Eduard wrote:
| funny, the RFC even includes a shell command pipeline to
| extract the base64 out of the awkward RFC formatting.
|
| Using the C source code still leaves room for ambiguities
| / under-specification, no? After all, the semantics rely
| on the particular gcc release used for compiling the
| code.
| cornstalks wrote:
| There is still the possibility of a bug or under-
| specification, but that's always the case in any spec. At
| least with Opus they document what implementation-defined
| behavior they require, so assuming there aren't any
| hidden bugs then you should get consistent output across
| compilers.
| dfox wrote:
| That is common for codec standards, the normative part of
| many MPEG specifications is the parser/decoder in C-like
| pseudo-code. What is somewhat unique for Xiph is that
| their normative reference decoders are actually usable.
| zie wrote:
| 100% agree and the Library of Congress loves it: https://www.
| loc.gov/preservation/digital/formats/fdd/fdd0004... and
| https://sqlite.org/locrsf.html
| sdeframond wrote:
| I think this has been discussed before about WebSQL.
|
| > The [WebSQL] specification reached an impasse: all
| interested implementors have used the same SQL backend
| (Sqlite), but we need multiple independent implementations to
| proceed along a standardisation path.
|
| https://www.w3.org/TR/webdatabase/
| sdeframond wrote:
| The Chrome blog post about deprecating sqlite-based WebSQL
| makes an interesing point. I believe it applies to
| OpenDocument as well.
|
| > The Web SQL specification cannot be implemented
| sustainably, which limits innovation and new functionality.
| The last version of the standard literally states "User
| agents must implement the SQL dialect supported by Sqlite
| 3.6.19". SQLite was not initially designed to run malicious
| SQL statements, yet implementing Web SQL means browsers
| have to do exactly this. The need to keep up with security
| and stability fixes dictates updating SQLite in Chromium.
| This comes in direct conflict with Web SQL's requirement of
| behaving exactly as SQLite 3.6.19.
|
| https://developer.chrome.com/blog/deprecating-web-sql/
| cryptonector wrote:
| This could have been used as an opportunity to standardize the
| SQLite3 DB file format.
| paulddraper wrote:
| > less capable engine
|
| There wouldn't be another engine.
|
| It would be SQLite. Period.
| patapong wrote:
| 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.
| bb010g wrote:
| You can still use <https://github.com/jlongster/absurd-sql>.
| <https://jlongster.com/future-sql-web>
| orf wrote:
| 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 there is a better file format we can use, but it would be
| better if it was decoupled from SQLite.
| hot_gril wrote:
| You do need all these things for these applications.
| Efficiently and safely querying and writing data is central to
| any document format; you'll leverage both the file structure
| and in-memory structs to do this. SQL would probably work for
| this, in fact it's especially natural for spreadsheets (rows x
| cols).
| orf wrote:
| I mean, clearly you don't: OpenDocument works just fine
| without it.
|
| You need key/value lookup, a way to list/paginate, and
| transaction semantics for updates.
|
| AKA: a zip file with entries as keys, and XML documents or
| attachment blobs for values. What's lacking and causes issues
| is the update semantics.
|
| You can wack a SQL query language over those 3 operations if
| you'd like. Or don't. Up to you, because the _format_ is
| defined and can be reimplemented rather than the _large,
| complex library api_.
| hot_gril wrote:
| OpenDocument also leverages the file/dir structure for
| efficient querying like we're both saying. If you mean that
| you don't need SQLite over ODF, well yeah, ODF works too. I
| just wouldn't prefer it.
|
| What's the issue with the update semantics, though?
| orf wrote:
| The issue is incremental updates, and this is where
| things get complex. If you have a file embedded in the
| middle of a zip file that is 100 bytes, and you want to
| resize it to 150 bytes, how do you do that?
|
| You can't squeeze it in without moving everything else
| about, which disrupts other readers. You could append it
| to the end maybe, but you need to handle concurrent
| writers. Compression also is an issue here - I expect zip
| compression is applied to multiple files at once, rather
| than per file? So now you might need to update multiple
| seemingly unrelated files.
|
| You need to step down from the concept of a whole file as
| a unit and move towards pages of data that can be
| incrementally updated/reused/freed, where each page might
| contain one, many or even only a part of a "unit"
| (file/row/whatever)
|
| This makes things more complex for sure
| hot_gril wrote:
| So basically ODF loads everything into memory, relies
| heavily on in-memory structs for quick unsaved updates,
| and is crash-safe by writing the whole zip to a temp
| location during saves. Kinda similar to MS Office. The
| file structure also helps a little. This is good enough
| for small docs.
|
| Many times have I encountered large docs, often
| spreadsheets, that push the limits here and become
| noticeably slow. If you want to get more sophisticated
| with the indexing and paging, SQLite is a very natural
| path. Anything else would be reinventing the same wheels
| SQLite has spent decades refining.
| orf wrote:
| > Anything else would be reinventing the same wheels
| SQLite has spent decades refining.
|
| Which is exactly the problem. They (I.e one dude?), and
| they alone have spent decades refining a single
| implementation.
|
| Before we go and lock the entirety of the worlds
| documents into what's essentially a proprietary format
| specific to a single implementation of a single library
| written by a single dude... we should double check if
| that's a good idea or not, and if we can, collectively,
| solve some of these issues _without_ reimplementing the
| _whole_ of SQLite.
|
| Because that's complex. Perhaps more complex than it
| needs to be for most applications, which would benefit
| from the storage part more than the query part. And then
| we are back at the start of our discussion?
| jve wrote:
| - 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.
| Ensorceled wrote:
| The complaint is not "it isn't good" but rather "it is not
| replaceable". Since SQLite is so powerful, once you specify
| it as a format, you are stuck with SQLite forever.
| ncruces wrote:
| Which is also "not a big issue", since it's a recommended
| Library of Congress storage format, and supported long
| term:
|
| https://www.sqlite.org/locrsf.html
|
| https://www.sqlite.org/lts.html
| 5e92cb50239222b wrote:
| It is somewhat of a problem: the development team is very
| small, they don't take outside contributions (so nobody
| outside the core team really builds up expertise over
| time), and the vast majority of tests are proprietary. I
| hope they have a contingency plan just in case (some sort
| of a dead man's switch that publishes the test suite
| under a permissible license) as it would probably be
| quite difficult for others to maintain the same quality
| without those tests, or re-implement them in a reasonable
| time frame.
| floppydiscen wrote:
| I'm pretty sure this is why libsql was created
| https://github.com/libsql/libsql
| fweimer wrote:
| But that equally applies to getting critical bug fixes
| for your particular usage scenario of SQLite. It's not
| just about the viability as a storage format.
|
| For the latter, because the stored data has such a simple
| format and the implementation has so few dependencies, I
| expect it will be very easy to get your data out for a
| long time to come. It's going to be tougher if you have
| business logic in views or other SQL expressions, of
| course, and if you rely on SQLite's particular approach
| to data types (as in "values have types", but not much
| more).
| Ensorceled wrote:
| Which is why I was clarifying the original complaint and
| not supporting the original complaint.
| hot_gril wrote:
| Once you pick ODF as a format, you're stuck with it
| forever... except I wouldn't categorize it as powerful.
| [deleted]
| orf wrote:
| > In the end, you don't need a database, but a library that
| gives you database API and behavior.
|
| Why do you need a single library that gives you a database
| API _and_ behaviour?
|
| Wouldn't it be better to decouple those: provide an open,
| standard format that _enables_ compact, fast, structured
| storage that is built to allow transaction /atomic updates.
|
| If that exists then you can plug sqlite on top of that, or
| something else. Because you don't _need_ any of SQL, or
| really sqlite to improve the OpenDocument format. You _need_
| the storage format.
|
| OpenDocument is very different from the pretty
| scientific/niche/highly-vendor-locked examples given in
| replies by others here. Locking this into a format developed
| by essentially a single person with a single implementation
| is absolutely mad.
|
| But... it's less mad if the file format wasn't coupled to
| sqlite.
| colonwqbang wrote:
| The Sqlite format is open and the spec is here:
| https://www.sqlite.org/fileformat2.html
|
| I haven't studied the spec in detail but it seems
| comprehensive.
|
| The fact that there also exists a high-quality, stable,
| public domain reference implementation can't really be
| counted against the format, can it?
| hot_gril wrote:
| > Wouldn't it be better to decouple those: provide an open,
| standard format that enables compact, fast, structured
| storage that is built to allow transaction/atomic updates.
|
| The high-level software abstraction approach doesn't hold
| up when it comes to databases. This is such a wide and
| performance-critical interface that any abstractions are
| gonna leak badly. Even the SQL standard has all these impl-
| specific flavors. Many have tried to build layers on top
| that'll work with multiple DBMSes, and it's never worth.
| Anyone writing an app backend is just gonna marry a
| particular DBMS for the performance benefits (puns
| intended).
|
| If for some reason an alternative implementation really
| needs to exist, SQLite is simple and open enough that
| someone can do it.
| orf wrote:
| You're totally right, but there are a few things missing:
| this isn't a DBMS, really, and the files are not going to
| be huge.
|
| You need fast listing/pagination, key value get/set, and
| transactional updates. Basically DynamoDB, but for a
| single file. Build a query layer on top of that, sure.
| Use those primitive to build persistent indexes if you
| want.
|
| Or just iterate through the keys in a for loop. It fits
| in memory anyway.
|
| You don't _need_ a fully fledged DBMS for a word
| document. And if you're shuffling around lots of data in
| a structured format with no updates needed, you probably
| want arrow /parquet rather than sqlite because the read
| performance is going to crush SQLite.
| hot_gril wrote:
| I don't know, probably a lot of us have dealt with large
| docs that become noticeably slow to edit and scary to
| save, mostly spreadsheets.
| nurbl wrote:
| A great thing about just using sqlite as the format is that
| you get lots of potential features. Sure most applications
| don't need full SQL power just to save and load data. But
| then at some point you might want more advanced
| functionality, or migrate to a new structure. And both you
| and your users get tools for free, e.g. to extract data or
| fix problems, or just look around. Other applications can
| quite easily read your files, without you needing to write
| various language libraries. Very few projects get around to
| building that kind of tools for their made up format.
|
| I could agree about the single implementation, but if the
| alternative is making something new up I am not sure in
| what way that would be better.
| amiga386 wrote:
| 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 patents that might
| affect it, and show me the multiple compatible implementations
| of SQLite that _all_ have these touted advantages, and _then_
| we can talk about prosletizing it as a file format. If they
| don't, they're saying "take a hard dependency on a single-
| source implementation, and make all your users take it too".
|
| XML is a formal standard. ASN.1 is a formal standard. JFIF is a
| formal standard. Even ZIP is a formal standard (adopted as part
| of standardising OpenDocument: ISO/IEC 21320-1:2015)
|
| The most important thing about a document is that everyone
| _else_ can read it. Saving time on writing updates to disk is
| an irrelevant sideshow. Did we learn nothing from Microsoft
| perverting the standards bodies to try and keep its lock-in?
|
| https://arstechnica.com/uncategorized/2008/10/norwegian-stan...
|
| > A letter of resignation written by the departing members and
| made public by The Inquirer accuses the standards body of
| folding to pressure from Microsoft, violating its own
| procedural rules, and ignoring the analysis of the technical
| committee tasked with evaluating OOXML.
| indymike wrote:
| > 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.
|
| From TFA:
|
| _Note that this is only a thought experiment. We are not
| suggesting that OpenDocument be changed._
| hot_gril wrote:
| I think SQL, a formal standard, has shown that formal
| standards fail to define a good way to interact with a
| database. The only real implementations all broke the
| standard. And an editable document isn't far from a database.
| numeromancer wrote:
| 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?
| tl wrote:
| Performance matters and is sufficiently captured via working
| incremental updates. The single largest upside of a proposal
| like this is captured by using SQLAR over ZIP. That's what
| the Library of Congress does when SQLite claims them as a
| proponent. It's what Fossil does as others in this thread
| have pointed out. It's suggested as "first improvement" in
| the linked article. It's also the _only_ part that should
| actually be considered for implementation.
|
| You are right to point out the folly of deeper
| implementations like having and needing to understand table
| structures for things like slides. However, the current
| status quo involves Microsoft implementing a fairly esoteric
| "update the XML file's bytes as they would be encoded in a
| ZIP file" in their proprietary tool (where they have enough
| money to invest the engineering time) and all other tools use
| the slower "whole file in memory" approach.
|
| User visible features like incremental fast saves (and shared
| editing) keep people on closed systems and give Microsoft the
| leverage to do the things you warn against. SQLite as a
| container format could have prevented that by giving everyone
| a shot at a lower cost but still fast implementation.
| rakoo wrote:
| 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.
| livrem wrote:
| 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.
| rakoo wrote:
| 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
| punnerud wrote:
| 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?
| JimDabell wrote:
| The Apple apps are using Core Data, which uses SQLite as its
| persistent store by default. So Apple could in theory migrate
| away from SQLite by changing Core Data's behaviour without
| any application-level impact. So in a way, these applications
| are already decoupled from SQLite in the way the parent
| comment suggests.
| tuyiown wrote:
| 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.
| bawolff wrote:
| > Meaning, every implementation has to run on environment
| targetable and compatible wit sqlite
|
| Well i get what you are saying, sqlite has been ported all
| over the place. It probably wouldn't be the limiting factor
| portability wise.
| tuyiown wrote:
| Yes, right now, no problem, and there little foreseeable
| future where a sqlite ported to anything would pose a
| problem. Still, decisions with no way back like this one
| requires extra cautions.
| severak_cz wrote:
| SQLite is already used for exactly this purpose. It's used as
| OGC GeoPackage and Mabox/Maptiler datasets use this.
| fweimer wrote:
| I found the transactional aspect surprisingly difficult,
| especially with concurrent file access. SQLITE_BUSY handling
| was quite hard at the time. I know that serialization failures
| are expected in transaction processing, but for SQLite it was
| very difficult to tell persistent failures (say, due to self-
| deadlock) apart from transient concurrent update problems. For
| transient failure, you can re-execute the closure defining the
| transactional operation, but for persistent failure, that's of
| course pointless.
|
| Part of the problem is that sqlite3_stmt combines aspects of
| both prepared statements and result sets. There is a tendency
| to keep them around to cache the compiled bytecode (prepared
| statement), but your might code might stop mid-iteration
| (result set), maybe holding a lock at this point. This can lead
| to surprising lock-upgrade failures. In the end, I wrote
| extensive error reporting using sqlite3_next_stmt,
| sqlite3_stmt_busy, sqlite3_sql, just to weed out those issues.
| The entire transaction retry code I wrote is full of optional
| logging and many comments, even though it was just for my own
| personal use. Before that, I wrote transaction retry logic for
| PostgreSQL, and that was so much easier (but it was before
| fully SERIALIZABLE transactions arrived).
|
| The other surprise is that " A transaction committed in WAL
| mode with synchronous=NORMAL might roll back following a power
| loss or system crash."
| (https://sqlite.org/pragma.html#pragma_synchronous), but that
| wasn't relevant to my application.
| HelloNurse wrote:
| If you retry your write several times and it doesn't succeed
| you can tell the user it is a persistent failure without
| agonizing too much over the diagnosis: it is persistent
| enough to be a significant problem, even without proof that
| it is an application bug.
|
| Who would attempt to make concurrent writes to an application
| document format? And how wouldn't such an attempt be a user
| mistake? Failing to write is the solution, not the problem.
| fweimer wrote:
| These concurrency failures in transaction processing can be
| quite rare, but you have to fix them if you want 24/7
| unattended operation. SQLite only has timeout-based
| conflict detection, so you basically have to decide whether
| you want to wait 60 seconds (or so, depends on how glitchy
| your storage is) before reporting a potential self-
| deadlock, which isn't great for development, or risk
| failing unnecessarily when actually running the job. I had
| no idea what the right timeout was, which is why I wrote
| some of the custom self-deadlock detection logic. I think I
| got to run it completely reliable in the end (no false
| aborts even under load), but as I said, it was surprisingly
| hard.
|
| By the way, concurrent read/writes on locally stored
| documents happen, even on single-user machines. If the
| reading process uses the SQLite structure (say a document
| indexer that knows about the format), it has to take some
| locks and may also need to flush data from the WAL log
| (depending on implementation details). At this point you
| have to deal with concurrency issues in the application,
| too. Unless you rewrite the entire document from scratch on
| every save and put it in place with an atomic rename (which
| I ended up doing for a different application, not the
| transaction-processing one). But that loses some of the
| advantages of SQLite.
| HelloNurse wrote:
| You are clearly discussing a shared database for
| concurrent transaction processing ("you want 24/7
| unattended operation"), not people editing application
| document files.
|
| Setting aside technological details, multiple clients
| operating on independent rows of the same table can only,
| at worst, waste time by retrying a transaction, while
| multiple concurrent users attempting to modify the same
| document are asking for trouble, and if they succeed they
| probably succeed at corrupting the document.
|
| Even without lock contention the aggregate document state
| can be incoherent (for example, Alice and Bruno edit a
| text, but they accidentally modify the same section and
| the latest save prevails and nobody notices).
| skybrian wrote:
| 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 useful history.
|
| If you then check the file into git, you are storing one source
| control system into another one, and older versions appear in two
| different histories. To be git friendly, you don't want to save
| anything other than the current version, and then let git do its
| thing.
|
| Possibly the answer is "don't use git, we have it covered," but
| then the app developer should realize that they are implementing
| something like a source control system. How do people share
| drafts, review them, and merge changes? How do you publish a
| release that only includes the version you wanted to release?
|
| And it does seem relevant that the developer of Sqlite actually
| did implement their own source control system [1]. Maybe they
| could have warned people about what they're getting themselves
| into if they go down this route?
|
| I wonder how terrible it would be to either use a git repo as
| your file format, or to build in git compatibility into your app
| somehow so you could push and pull?
|
| [1] https://en.m.wikipedia.org/wiki/Fossil_(software)
| mixmastamyk wrote:
| 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.
| EricRiese wrote:
| Yes, in LibreOffice you can save as FODT: flat ODT, which is
| a single unzipped XML. That's what I use to store my resume
| in git.
| vxNsr wrote:
| I'm curious to know what a gsheet/doc/slide file actually is
| under the hood. I as the user am only ever presented with a link,
| there's no way to download a gsheet in its native format.
| throwaway894345 wrote:
| Is SQLite's disk format an open, versioned standard? Or is it
| just "however SQLite saves data to disk"?
| SQLite wrote:
| SQLite file format spec:
| https://www.sqlite.org/fileformat2.html
|
| Complete version history:
| https://sqlite.org/docsrc/finfo/pages/fileformat2.in
|
| Note that there have been no breaking changes since the file
| format was designed in 2004. The changes shows in the version
| history above have all be one of (1) typo fixes, (2)
| clarifications, or (3) filling in the "reserved for future
| extensions" bits with descriptions of those extensions as they
| occurred.
| dang wrote:
| Related:
|
| _What If OpenDocument Used SQLite? (2014)_ -
| https://news.ycombinator.com/item?id=25462814 - Dec 2020 (194
| comments)
|
| _What If OpenDocument Used SQLite?_ -
| https://news.ycombinator.com/item?id=15607316 - Nov 2017 (190
| comments)
| kunley wrote:
| Love the vibe of artivles, which present let's say reason-driven
| development vs habit-driven.
|
| Why habit? Well, I can imagine back at the time OpenOffice was a
| fresh project, it went like this: "XML is going to stay forever
| and everybody uses XML, so ofc we use one... oh, it is so big!
| And there are many files, so we just zip'em"...
|
| To be fair, the author of this excellent article doesn't even say
| about getting rid of XML in this format- but that could also be
| achieved by storing stuff in a SQLite file. Usage of XML was
| habitual thinking there- and not very visionary, as the format is
| dead now...
| tpm wrote:
| > Well, I can imagine back at the time OpenOffice was a fresh
| project
|
| OpenOffice was born when Sun bought StarOffice, which was
| initially released in 1985 (on Z80 and certainly without any
| XML). So the project itself was far from fresh. OpenDocument
| was developed from OpenOffice.org XML format which was
| developed after Sun bought StarOffice in 1999. At the time XML
| was not used everywhere, but it was very much in vogue,
| certainly at Sun where the official line was that Java (created
| at Sun) and XML are going to conquer the world.
| galangalalgol wrote:
| Could you clarify the "XML is dead" comment? Don't all the
| major document formats still use zipped xml? I had to interface
| with an xml format recently, and that isn't something I ever
| did, and when I went looking for a crate that parses an xml
| schema I kept running across this whole xml is dead thing. But
| it still seems to be everywhere.
| kortex wrote:
| Not GP, but I believe the "XML is dead" sentiment stems from
| the observation that very few greenfield applications are
| deliberately choosing xml. Sure you have legacy giants like
| (X)HTML, SVG, office formats, etc, but you'd be hard-pressed
| to convince developers (especially a younger crowd) to select
| it as a data format. It's seen as warty, cumbersome,
| unwieldy, verbose.
| out_of_protocol wrote:
| 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
| vetinari wrote:
| 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.
| m4rtink wrote:
| 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.
| vetinari wrote:
| It is not just fat32 and overhead up to cluster size; once
| I had 800k tiles in a single directory on ntfs.
|
| It was unusable. The only thing that I was able to do is to
| tar it up and move to a machine with xfs, where I was able
| to sort it up into more balanced subdirs and then move it
| back (for processing using windows-only tool). Just tarring
| that single directory up took several days.
| 5e92cb50239222b wrote:
| It's not just ntfs. I tested this a few months ago in a
| pretty unscientific manner using ~50 million files in one
| directory.
|
| btrfs was unusable (not only that particular directory,
| but the whole filesystem became noticeably slower).
|
| ext4 was ok. xfs didn't break a sweat. I don't recall any
| practical difference when compared against a nested tree
| like +-- aa | +-- aa | |
| +-- aaaaf3ee5e6b4b0d3255bfef95601890afd80709 |
| +-- ab | +-- ac +-- ab +-- aa
| +-- ab +-- ac
| [deleted]
| Tigress8780 wrote:
| Once we had to ship millions of extremely small files to
| our customer, we ended up throwing them into a MongoDB and
| serve them with a web server. It worked great.
|
| We tried to use an image of traditional filesystems (ext4
| and fat32), but with most files being under 1 KiB, it was
| super wasteful.
| nxobject wrote:
| This is an _extremely_ low quality comment, and I accept
| any downvotes, but I can 't resist: would you say that
| MongoDB was web scale?
| Tigress8780 wrote:
| The way our "MongoFS" was organized is actually also low
| quality and probably would fall apart pretty quickly if
| used as a serious web service. However, it works if you
| try to quickly deploy and serve millions of files to a
| small number of clients.
|
| Our use case was a Maven mirror for disconnected
| environment that only contains metadata (i.e. lots of
| small XML files, without the actual jar). We already had
| a MongoDB service for some other JSON data, so here we
| are.
| liuliu wrote:
| If SQLite is faster, the problem is the zip library you use.
|
| SQLite has a major draw back (and yes, I love SQLite and built
| a lot of things around it over the years): the blob you get
| from the DB cannot be mmap and you have to copy it to somewhere
| else. For zip files, as long as the file is not compressed, you
| can mmap it (or it is compressed using some exotic encoding
| such as PVRTC) just fine.
| indymike wrote:
| > 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.
| didntcheck wrote:
| Just a heads up that it looks like you meant to reply to
| miki123211, but you've posted a top-level comment instead :)
| [deleted]
| [deleted]
| robertlagrant wrote:
| > since OpenDocument predates SQLite
|
| This shocked me. Impressive how far SQLite's come in such a short
| space of time.
| capableweb wrote:
| 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.
| robertlagrant wrote:
| Hah - that tallies with my instinct on ODF at least. I'm
| confused too, then.
| paradox460 wrote:
| 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
| ReactiveJelly wrote:
| That's fair, I wouldn't standardize on a 1-year-old
| database.
| filmor wrote:
| Not to be confused with Office Open XML (OOXML),
| Microsoft's "standard".
| capableweb wrote:
| Thanks for clarifying the somewhat messy history of the
| format!
| [deleted]
| [deleted]
| nyanpasu64 wrote:
| 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, creating spurious Git diffs if you check Audacity
| projects into Git as binary blobs. And when you save the file,
| old and deleted data is left in the SQLite file until you close
| the project's window (unlike saving a file in a text editor), and
| you can accidentally commit that into a Git repo if you don't
| close the window before committing. (I recall at one point that
| you had to manually vacuum the .aup3 file, but now closing the
| window is sufficient.) I'm getting Word 2003 Fast Save vibes.
| chrismorgan wrote:
| It's also a bit of a bother if Audacity crashes (or is
| otherwise terminated abnormally), as the cleanup just doesn't
| happen _at all_ then, whereas in the past the recovery process
| would mention the presence of orphaned blocks and allow you to
| choose to keep or delete them. But when I had a several-
| gigabyte project that should have only been a couple of hundred
| megabytes, and needed to save disk space, I finally found a
| solution suitable for my simple single-track stuff: Mix and
| Render. Doesn't change the audio, but allowed it to clean up
| the detritus on save and exit. But all up, this is clearly an
| _application_ -level problem, not something inherent to SQLite.
|
| Hmm... I think I vaguely recall that Audacity 2 had the concept
| of a temporary working space, whereas it seems that Audacity 3
| just uses the .aup3 file as its working space? Some advantages,
| some disadvantages.
|
| Mildly less on-topic: I looked into Audacity 3's format, and
| was utterly baffled by what they've done with the project data
| (what used to be the .aup file). They still encode it as XML,
| storing it in a single-row table, but instead of just writing
| it as text, they use a simplistic _dictionary coder_ on it.
| Just... _why_? Why did someone go to all the trouble of writing
| that code? It makes interoperability and inspection much
| harder, surely harms performance (even if by a trivial amount),
| and the space saving will be rounding error in every plausible
| case (like, _maybe_ as much as a few kilobytes out of _hundreds
| of megabytes_ of audio files).
| rini17 wrote:
| 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.
| bawolff wrote:
| > Yes it should replicate the functionality user expects
|
| Do users really expect this now a days? Most users use cloud
| apps, and almost all of those save after every operation
| automatically.
| raxxorraxor wrote:
| Which is a compromise for using browsers really. It isn't a
| good solution and no user really understands this and I
| believe it is the most hated feature of the new cloud
| world. Yes, leaving the page open for multiple hours might
| now allow you to save because your access token expired.
| No, communication in the background is unreliable too.
| Autosave is a bad band aid for a bad solution.
|
| Doing periodic and automatic saves is good. Doing so on a
| document "in production" is majorly stupid. Not that I want
| to accidentally validate the busy work dev ops puts us
| through.
| bawolff wrote:
| Its pretty easy to make a cloud app that emulates the
| traditional working draft/save workflow. Browsers all
| have pretty reliable local storage technology now a days
| if your network is unstable. I don't think this design
| choice is a compromise of the medium. If anything it
| seems like if you were going to have to compromise for
| web you would do it in the other direction so apps are
| more usable during poor network conditions.
|
| I would say the traditional model is a compromise from
| back when disks were unacceptably slow to be saving
| constantly.
| gwd wrote:
| > 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.
|
| The problem I'd predict here is that then people would expect
| to be able to do three-way merges. It might even work
| correctly a lot of the time, depending on the exact pattern
| of changes. But my gut feel is that unless the schema were
| designed just right, there would be _possible_ merges that
| would result in a database that was valid from SQLite 's
| point of view but insane from the application's point of view
| (broke expected variants, etc).
| bawolff wrote:
| If you want to use flat files you should just use flat
| files. There are plenty of unix tools to treat them like
| DB.
|
| You're not going to have a sensible text version of a btree
| that is reasonably editable by a text editor.
| massysett wrote:
| I set up my Git to use the SQLite dump on SQLite files when
| using "git diff". This at least shows me the changes row-by-
| row, or shows nothing if no changes.
|
| I don't expect to be able to merge though.
| mcpackieh wrote:
| 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...
| anonzzzies wrote:
| My wife uses Audacity all day and every few days there is a
| corrupt sqlite file (duplicate key) which cannot be (as far as
| we know) repaired/reimported etc from Audacity. I can fix it
| manually if it's important, but usually just throw the file
| away and things work again.
| justinclift wrote:
| Duplicate keys in a SQLite file sounds like an audacity bug.
| :(
| sgarland wrote:
| Maybe. The SQLite list of gotchas [0] is quite something.
| NULLs in the PK? Sure. FKs don't actually do anything
| unless you pass a PRAGMA? Why not? Etc. I could easily see
| someone not fully grasping just how much SQLite lets pass
| by default, and thus not having a test catch it.
|
| [0]: https://www.sqlite.org/quirks.html
| [deleted]
| anonzzzies wrote:
| Yeah, it definitely is. And it's fixable manually. Kind of
| the advantage to an open file format with nice tooling.
| regularfry wrote:
| It also sounds like something that could be manually
| prevented ahead of time. If you can crack open the file on
| first save and add the right uniqueness constraint, that
| should make Audacity crash when it tries to corrupt the
| data.
| justinclift wrote:
| > ... that should make Audacity crash when it tries to
| corrupt the data.
|
| That'd be fairly non-optimal behaviour. ;)
|
| When the application tries to add wrong data (eg
| duplicate key violating uniqueness constraint), SQLite
| will return an error.
|
| The application should handle things better than by
| crashing. In theory anyway. :)
| regularfry wrote:
| If your choices are a crash or corruption, choose the
| crash.
| im3w1l wrote:
| I don't want people to read my drafts. That could be highly
| embarassing, and they should not make it into the final saved
| document.
|
| Past version and undo history should be stored separately from
| the document. They should be stored out of tree where they wont
| be commited into some git repository or be automatically synced
| or anything like that.
| regularfry wrote:
| _I_ want to be able to read my drafts, until I decide to bake a
| publication version.
| im3w1l wrote:
| Did you read the other part of my comment? Where I said to
| store the draft, but not in the document itself?
| regularfry wrote:
| I did.
| eviks wrote:
| 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?
| pornel wrote:
| 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.
| eviks wrote:
| 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
| stareatgoats wrote:
| 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 you would expect: it returns
| the manifest and versionId of the entry that has the maximum
| checkinTime.)"
| mwexler wrote:
| It's not really what one would expect in SQL, but SQLite often
| defies expectation. In this case, handy, but non-standard.
| cryptonector wrote:
| That's basically short-hand for SELECT
| manifest, versionId, max(checkinTime) FROM version
| GROUP BY manifest, versionId ORDER BY 3 DESC LIMIT 1;
|
| or WITH m AS (SELECT max(checkinTime) AS
| checkinTime FROM version) SELECT v.manifest, v.versionId,
| v.checkinTime FROM version v JOIN m m USING
| (checkinTime) LIMIT 1;
|
| It's a bit of a footgun though because there is some randomness
| here if multiple rows have the same max checkinTime, so I try
| not to use this SQLite3-ism. You want to also do something to
| deterministically pick a "best" row, but for that you need to
| do something like the above.
| mrighele wrote:
| > 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.
| tannhaeuser wrote:
| I agree, that doesn't make sense to me either. What about
| select versionId, max(checkinTime), min(checkinTime)? Can as
| well query SqlGPT. And above all, it's not what the SQL
| standard says when that's the entire point of using a
| standard in the first place.
| OskarS wrote:
| Well, it doesn't error out! In this example, it seems like
| it picks the result from whatever matches the last column,
| but not sure if this is determinstic:
| sqlite> create table x(c1, c2); sqlite> insert into
| x values ("a", 1); sqlite> insert into x values
| ("b", 2); sqlite> insert into x values ("c", 3);
| sqlite> select c1, max(c2) from x; c|3
| sqlite> select c1, max(c2), min(c2) from x; a|3|1
| sqlite> select c1, min(c2), max(c2) from x; c|1|3
|
| (note: since SQLite is dynamically typed, no need to
| specify column types for simple examples like this).
| deathanatos wrote:
| > _or something like that_
|
| That query isn't guaranteed to produce a well-defined result
| in most SQL engines. (For pretty much the same reason the
| original doesn't/can't/shouldn't...) In the simple case of
| two rows with the same `checkinTime`, many engines permit the
| results to be ordered arbitrarily.
| globular-toast wrote:
| 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...
| paulddraper wrote:
| MySQL allows the query, but the non aggregate fields are
| selected randomly
| wongarsu wrote:
| Following MySQL's longstanding tradition of just doing
| whatever instead of showing an error message, no matter how
| unreasonable the result.
| paulddraper wrote:
| MySQL + PHP, name a more iconic match
| [deleted]
| asddubs wrote:
| 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)
| sgarland wrote:
| 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
| paulddraper wrote:
| 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.
| kevincox wrote:
| 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.
| globular-toast wrote:
| 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.
| ealexhudson wrote:
| I think if you have a set that you want the latest value
| from, in all engines you can do something explicit like:
|
| > SELECT manifest, versionId, checkinTime FROM version ORDER
| BY checkinTime DESC LIMIT 1
|
| The problem with putting aggregation functions in the select
| output is that you're being unclear about what is
| aggregating; that pattern begins to break down once you have
| e.g. multiple documents in the same schema. Or if versionId
| somehow wasn't linear (e.g. branches of changes).
| deathanatos wrote:
| That's um... _quite_ the aside. How can it possibly claim that
| to be well-defined, given that `manifest` and `versionId` are
| not functionally dependent1 on `max(checkinTime)`?
|
| 1e.g., there could be two rows with the same checkinTime, whose
| value happens to then be the max such.
| isoprophlex wrote:
| Man do I love SQLite.
|
| Over the past 1.5 yrs I've build a computer vision tool from
| recording hardware/software, to derp learning pipelines, to
| front-end; we had some requirements on the recording side that
| were difficult to solve with existing solutions (storing exactly
| timestamped camera frames, gps data, car telemetry and other
| metadata).
|
| Using a SQLite-backed data format for the video recordings made
| implementing things by ourselves super straightforward.
| sgu999 wrote:
| I'm working on a similar problem and I've been struggling to
| convince all my colleagues that we should sqlite most things.
| By any chance do you have some public code, or blog posts to
| share?
| isoprophlex wrote:
| Not in public repos, but sure. Drop me a line, hn at rombouts
| dot email.
| regularfry wrote:
| > derp learning pipelines
|
| This accurately describes the majority of my efforts, too.
| isoprophlex wrote:
| Honest to god this was an unintentional typo, but I decided
| to leave it in as it was just too juicy
| vmfunction wrote:
| 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.
| [deleted]
| quickthrower2 wrote:
| 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.
| OliverJones wrote:
| XML and JSON are for serialization. zip and sqlite, and file-
| system files, are for lossless persistence. They're separate
| issues.
|
| An app can go bananas with serialization and use, I dunno,
| binary JSON or Matroska / ebml or .mp4 containers or
| whatever, and still serialize any way it wants.
| eastern wrote:
| In sqlite the on-disk file format does not matter.
|
| All that matters is that you should be able to issue sql to
| the sqlite embedded library and get back the results.
|
| Freeing you from the overhead of owning (thus inventing and
| then maintaining) your own file format is almost the entire
| point of using sqlite in this manner.
| quickthrower2 wrote:
| It matters for 2 reasons. One, the expectation that the
| file changes only when you click Save is broken (as
| mentioned in another comment), and Two, unless you pin the
| version of sqlite forever then the file format may have
| braking changes or your need to deal with migrations.
| eastern wrote:
| > expectation that the file changes only when you click
| Save is broken
|
| This has nothing to do with sqlite. You can have (or not
| have) gradual saves in any file format. It's a choice
| that the developers of that app made.
|
| > file format may have braking changes
|
| The sqlite file format is unchanged for 19 years now. A
| world of features and capabilities have been added since.
| Don't hold your breath waiting for the sqlite format to
| change.
| quickthrower2 wrote:
| This is not gradual saves. File changes when nothing has
| saved even, according to that comment.
|
| Fair enough about the history of it not changing and you
| can always embed a frozen copy if it does. But this is a
| pragmatic assumption not a guarantee.
| constantly wrote:
| Any text editor in the world, even the ones that ship with the
| most barebones shells, can open json and xml and present their
| data to the user.
|
| SQLite files require opening in a DB terminal or using special
| software to even get to the point where one can see what's
| there at all. Further the entire internet basically natively
| supports XML and JSON.
| vmfunction wrote:
| That is a good argument, however many people like some big
| game development company start to ship with GB of json file,
| at that point just use SQLite. It will be faster to query
| load. Also if you look at how DB such as Mongo (Not promoting
| them in any way), but when Maildir is used aginst Mongo for
| file storage, Mongo saves a lot of disk space. Again, it is
| about how we want to store files? NixOS is a quite a way to
| think about having a file system or db/store.
| eviks wrote:
| Outside of simple cases xml is too verbose and ugly (and in
| these cases usually zipped), so it's not suitable for a poor
| human with a plain text editor, so that doesn't give you much
| of a leg.
|
| (Json has a higher threshold of complexity before it
| succumbs)
| swiftcoder wrote:
| > 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.
| simonw wrote:
| Why do you disagree?
| iefbr14 wrote:
| Why only documents? How about a SQLitefs?
| pgeorgi wrote:
| WinFS (https://en.wikipedia.org/wiki/WinFS) without the mssql
| Engine?
| iefbr14 wrote:
| Or this: https://github.com/narumatt/sqlitefs
| euroderf wrote:
| 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
| euroderf wrote:
| XSLT processors work by accessing the file system. Would
| this sqlitefs be a way to run XSLT against an SQLite
| database? Or is there maybe some other way to run a file
| oriented XSLT processor against an SQLite database in the
| SQLAR format?
| p4bl0 wrote:
| 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 other than its database format. And to save
| the document I VACUUM [2] it back to the database file. It works
| quite well, at least for reasonably sized file (which is always
| the case for my app) :).
|
| [1] https://www.sqlite.org/inmemorydb.html
|
| [2] https://www.sqlite.org/lang_vacuum.html
| rakoo wrote:
| 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. That way a crash is an expected situation that you
| actually account for, not a special case that you might forget
|
| - there is only one database. Less code, fewer bugs.
|
| - it is safe. A write to SQLite works or doesn't work, there is
| no in-between. As said in the VACUUM doc you point to:
| "However, if the VACUUM INTO command is interrupted by an
| unplanned shutdown or power lose, then the generated output
| database might be incomplete and corrupt"
|
| - it is how SQLite was intended to work. And because of that,
| you won't have to think about it for the lifetime of SQLite
| p4bl0 wrote:
| > 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 than of
| the user. For example, your music library in your jukebox
| application.
|
| On the contrary, when the user have to open the "thing" with
| your application and can choose between many of their files
| that can be edited with your application, users do not expect
| _their_ files to be automatically modified at all. For
| example users may start doing some heavy editing and then at
| the moment of saving their work, they might make a backup of
| the previous state file before saving, or choose to "save
| as..." in order to keep the old version just in case.
|
| Crashes are not something that happen that often. It can
| become an actual problem when you have tens of thousands of
| users and rare-events do happen, but in the particular case
| of the application I working on, I do not actually have to
| worry about that (on the contrary, any solution would have
| downsides that are worst in the particular case of this
| application than having to do some work again because of a
| crash if it ever happens).
| rakoo wrote:
| 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.
| p4bl0 wrote:
| I could, but as you said in your previous comment
| (emphase is mine):
|
| > _Less code, fewer bugs_.
| infogulch wrote:
| Another option is to explicitly start in read-only mode
| (modification buttons hidden / grayed out, some distinct
| mode indicator "Viewing Document" next to a button to
| "Start Editing", etc), and when the user chooses then
| switch into autosave mode. At this point many users are
| used to autosave and don't pay due attention to the
| document saved state. With Microsoft Windows' habit of
| rebooting your system overnight without your explicit
| permission, I'm concerned that this might lead to a lot of
| lost work.
| fluidcruft wrote:
| 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
| presentation state i. e. window positions, last page viewed,
| zoom level, ...)
|
| For example in many regulated domains such as human subjects
| research files must be approved and only approved files may
| be used. "Is this version of the consent document the version
| that the IRB approved?" Well let's see... (1) file
| modification date is after the approval date and (2)
| checksums do not match.
|
| Not to mention that writing a single byte of content to a
| filesystems marks the entire blob as needing backup.
|
| The fact is the filesystem is the user's database, save is
| commit, and it should be under the users control because
| application developers do not have the faintest idea about
| user context.
| wongarsu wrote:
| Word has a fairly simple solution to this: there's a big
| slider labeled "Autosave" in the title bar, right next to
| the save button, allowing you to turn this behavior on and
| off at any time.
|
| 95% of the time I want changes persisted immediately, but
| it's nice to be able to turn it off when I don't.
| prewett wrote:
| Depends a little on the type of file. A prose document,
| sure, probably want autosave by default. A vector
| graphics file? I want autosave when I'm creating it, but
| I do NOT want autosave when I'm copying out a piece
| buried several groups in and behind some things I need to
| delete/move out of the way. I also don't want to have to
| think about whether I need it or not.
|
| But generally the way autosave works is to save a copy
| that can be recovered on a crash, and only overwrite the
| original if directed by the user. That works for both use
| cases. (Haven't used Word in years, so I'm not sure if
| they have a different behavior now.)
| afiori wrote:
| For an application working with reasonably sized files
| sqlite files it would be reasonable to
|
| 1. on opening a file clone it to a temporary folder
|
| 2. edit the temporary file there on disk
|
| 3. on save mv/cp the temporary file over the destination
|
| I am probably missing a lot of use cases, but it migth be a
| good idea for a game like Factorio where you are expected
| to have multiple on disk saves of the same run at different
| times.
| fluidcruft wrote:
| In the sqlite case, I think it actually can save
| uncommited edits to a separate journal file until
| committed. At least, one of the systems I am familiar
| with that uses sqlite as a container format (MRI scanner)
| seems to do this, so I suspect sqlite supports that mode
| natively.
|
| I'm just pushing back against the idea that its a good or
| helpful idea to "help the users" by taking the deliberate
| "save" action away from them.
|
| As an aside, one of the things that has been learned from
| this class of MRI scanners is that users need to feel "in
| control" of the machines they're using. The "look how
| smart this machine is by doing all these magical things
| you used to do yourself!" attitude works well in sales
| but really does not go over well in the field because
| users encounter the fuckups and are held responsible for
| them. So they quickly start to distrust the machines.
| didntcheck wrote:
| > 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 write, or _be_ the filesystem (e.g.
| ZFS snapshots, which can be maximally efficient since it
| knows exactly which blocks it 's modified)
|
| Of course this has its own downsides. While those writes
| may have been "unimportant", the fact is that your backups
| are now flawed. And if the application has had the
| foresight to distinguish unimportant writes, and preserve
| the mtime, I'd rather they just not make those writes in
| the first place
| fluidcruft wrote:
| 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.
| mixmastamyk wrote:
| 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.
| rakoo wrote:
| 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
| fluidcruft wrote:
| I see what your are saying, but in other use cases the
| presentation state needs to be considered as part of the
| document. This is one of the reasons zip/jar containers
| work somewhat well. You can audit different chunks of
| data separately and cryptographically sign them. sqlite
| actually has an archive format[1] that is interesting to
| think about and I have pondered using it for some
| applications (store the files and also store tables of
| metadata/analysis)
|
| [1] https://sqlite.org/sqlar.html
| atoav wrote:
| 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.
| asalahli wrote:
| > I like taking the approach of CouchDB where the only
| correct way to close the system is to crash it.
|
| The term you're looking for is (aptly named) crash-only
| software.[0]
|
| 0. https://en.m.wikipedia.org/wiki/Crash-only_software
| knome wrote:
| a save button is still good, as it allows you to keep
| specific checkpoints.
|
| but the save button could simply tag specific save points in
| a larger table.
|
| if the format can roll up changes to compress them, they also
| indicate where which variants need to be kept indefinitely.
| theamk wrote:
| Auto-save is nice, but I think it works much better when
| it's a separate file.
|
| This way "main" document file (which might be checked in to
| git, or shared via dropbox or read by some document) only
| contains nice, clean, saved version.
|
| And yet if your computer crashes for whatever reason, the
| data is still not lost and can be trivially recovered.
| rakoo wrote:
| In this view the save action is more like a commit, where
| the user manually checkpoints and also offers a simple
| description of why this is an important point. But in my
| view all intermediary points also need to be saved, because
| the user might have forgotten to explicitly checkpoint, and
| might still want some undo/redo capability that is more
| granular than just checkpoints.
| [deleted]
| rewmie wrote:
| > 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?
| p4bl0 wrote:
| 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.)
| socksy wrote:
| I suppose this is in the context where you will be syncing up
| the changes to a backend server which will also be storing
| the document in an SQL database. Normally, you might expect
| that data format on the client to be JSON/XML/something else,
| and you'd need to maintain logic that marshalls the document
| representation SQL <-> In-memory
| representation <-> Disk format.
|
| With SQL on the client, in theory you only now need to
| maintain SQL <-> In-memory representation
|
| Obviously I'm skirting over the format you would use to send
| either entire documents or partial updates of documents over
| the wire.
| torstenvl wrote:
| An in-memory data model often differs from the serialized
| data as it exists on disk. For example, emacs uses a gap
| buffer for text files; but it outputs plain linear text to
| disk.
|
| Programmers often have to make software design decisions
| around how to represent a file in memory in order to
| manipulate it. For example, if I'm writing an HTML editor,
| should I mostly treat it like a text file (maybe a gap
| buffer) with syntax highlighting and auto indentation as an
| afterthought? Or should I maybe load the whole thing into a
| tree? What are the robustness and performance characteristics
| of each?
|
| The commenter above was saying that using SQLite made that
| decision easy. He could keep traditional (or "atavistic" per
| the commenter upthread, depending on your perspective)
| load/save semantics while also making the data model easy to
| work with.
| remram wrote:
| Why not use a transaction?
| p4bl0 wrote:
| A single transaction for the whole user session? That seems a
| bad idea. Also I'm not sure you can do transactions during
| another transaction, and I need them for other purpose, i.e.,
| for what they were designed to do (doing changes in multiple
| tables that need to stay consistent).
| subleq wrote:
| It's exactly what transactions are for. A nested
| transaction is called a savepoint, which sqlite does
| support.
| ilyt wrote:
| 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
| liuliu wrote:
| Maybe simpler? When open the DB, change it to WAL mode, turn
| off the automatic checkpoint
| https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint
|
| When user saves, you just checkpointing the file, merging it
| back into the main database.
| nyanpasu64 wrote:
| > 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)?
| justsomehnguy wrote:
| >> The file named by the INTO clause must not previously
| exist, or else it must be an empty file, or the VACUUM INTO
| command will fail with an error.
|
| EDIT: there is no difference between VACUUM/VACUUM INTO -
| they both write to a new file (COW) it's just VACUUM [NOT
| INTO] does mv temp.sqlite originalfile.sqlite after that,
| while VACUUM INTO does not.
| nextaccountic wrote:
| 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)
| scherlock wrote:
| If you have a db, why not just model it as unsaved data? I.e.
| all changes get stored to the db, but have a flag of unsaved.
| If you open up a file and there are unsaved changes, you can
| prompt the user to either make them saved or discard them.
| mort96 wrote:
| That feels like it requires the data model to be very
| different? The file format would essentially need to be a
| list of changes, with a "committed" flag.
|
| Like, if someone changes some text in a paragraph, you
| can't just model that as "this paragraph now contains this
| new text". You have to model it as "this paragraph used to
| contain this text, but an uncommitted change changed it to
| this other text". User deletes an image? You have to still
| store the image and all the references to it, but with an
| uncommitted change to delete the image and remove the
| references to it.
|
| And maybe that's a good thing, maybe a git-like system
| where the history of every change is tracked is what you
| want. But it certainly doesn't feel like it'd be
| appropriate for every application and file format.
| julesnp wrote:
| You wouldn't necessarily need to track every change, you
| could just have 2 tables, one which contains the last
| "saved" version of the document, and one which contains
| the last modified version of the document. Upon opening
| after a crash, if there is a more recent modified
| version, the program will ask if you want to load that
| version.
| mixmastamyk wrote:
| Databases handle all this natively with transactions and
| WALs. i.e. Don't need to build a Flintstones version
| yourself.
|
| Also binary documents are a lousy fit with git, smashing
| square peg into round hole makes little sense.
| Someone wrote:
| > and when the user clicks save, just copy the file into the
| desired location.
|
| To be perfectly safe, you want to _rename_ it, not _copy_ it.
| If there's a power loss during copying, you may endcup with
| corrupted data.
|
| Renaming is, to coin a phrase, "more atomic" than copying (on
| Linux, the OS says it is atomic. ISO C says it, too, but
| POSIX doesn't (https://pubs.opengroup.org/onlinepubs/00009539
| 9/functions/re...: _"This rename() function is equivalent for
| regular files to that defined by the ISO C standard. Its
| inclusion here expands that definition to include actions on
| directories and specifies behavior when the new parameter
| names a file that already exists. That specification requires
| that the action of the function be atomic"_ )
|
| Also, filesystems may have bugs, hardware may lie about
| syncing to disk, and network shares can be finicky.
|
| Doing this properly isn't as easy as one would think. You've
| to make sure to sync the file to be written and you'll have
| to handle the case where the save location is on a different
| file system than your temporary file. If so, you'll have to
| create a copy on that file system first.
|
| I think many tools do not check whether they need to work
| cross filesystem and just write their scratch files to the
| save directory with a different name and then rename them.
|
| Of course, that means you always need twice the disk space on
| the target disk to do a save. That used to be a problem
| almost everywhere, but nowadays mostly is restricted to
| embedded systems and USB sticks.
|
| In this case, however, SQLite will do a lot for you, and
| probably better than you would do it. It claims (https://www.
| sqlite.org/atomiccommit.html#_multi_file_commit):
|
| _"SQLite allows a single database connection to talk to two
| or more database files simultaneously through the use of the
| ATTACH DATABASE command. When multiple database files are
| modified within a single transaction, all files are updated
| atomically. In other words, either all of the database files
| are updated or else none of them are. Achieving an atomic
| commit across multiple database files is more complex that
| doing so for a single file. This section describes how SQLite
| works that bit of magic."_
|
| However, about _VACUUM INTO_ , it says
| (https://www.sqlite.org/lang_vacuum.html):
|
| _"The VACUUM INTO command is transactional in the sense that
| the generated output database is a consistent snapshot of the
| original database. However, if the VACUUM INTO command is
| interrupted by an unplanned shutdown or power lose, then the
| generated output database might be incomplete and corrupt.
| Also, SQLite does not invoke fsync() or FlushFileBuffers() on
| the generated database to ensure that it has reached non-
| volatile storage before completing."_
|
| So, I don't think doing "VACUUM INTO" is sufficient to
| guarantee that you get a good copy of your data on disk.
| cduzz wrote:
| Well, copying is simply not atomic in linux; directory
| entry operations (rename, link, unlink) are atomic. There
| is a definition somewhere that says how many bytes may be
| written atomically; that's it -- past that writes are not
| atomic.
|
| The prior comment of "model user interactions in the
| database" seems spot on -- just keep track of what the
| user's doing as unsaved data in the database and commit it
| (in the appropriate way) to the DB as it happens; save is
| just another user action.
|
| Presumably sqlite has figured out how to write to the
| filesystem without corrupting itself even in a variety of
| adverse scenarios?
|
| If not, commit to a temporary copy of the DB that gets
| renamed to the "main" name periodically or when the app
| closes. There's an xzzzbit meme there somewhere, yo.
| naniwaduni wrote:
| > The prior comment of "model user interactions in the
| database" seems spot on -- just keep track of what the
| user's doing as unsaved data in the database and commit
| it (in the appropriate way) to the DB as it happens; save
| is just another user action.
|
| The trouble is that often in the wild, _the content of
| the file on the filesystem is a user-facing interface_.
| Users will copy it around and attach the whole document
| onto emails. When they do, they _do not_ expect the file
| to contain data that they didn 't want to save.
|
| (Yes, they sometimes also expect the file to contain data
| that they _wanted_ to but didn 't explicitly save. This
| is not a contradiction.)
| jrockway wrote:
| > they do not expect the file to contain data that they
| didn't want to save.
|
| I think that's fine. Just remove "save" from the UI, and
| save after every keystroke. This may sound crazy in 1970,
| but it's how nearly everything works today. It's really
| only us weirdos that started using computers before "the
| cloud" that think "save" is an operation that does
| something, and we're dwindling in numbers!
| cduzz wrote:
| That's certainly true, and also the case in many modern
| file formats.
|
| For example there's the story of the academic studies
| with falsified data [1] where forensics on the included
| excel documents showed they'd gone through and replaced
| data with "randomized" data (if I remember correctly);
| there are tons of examples of "redacted" data in pdf docs
| being visible under the blacked-out rectangles.
|
| I'm not disagreeing with you, btw, such actions are
| certainly problematic, but hopefully kids will grow up
| knowing they need to run an export to sanitize their data
| if they don't want to show the whole world their
| transaction logs...
|
| [1] https://www.npr.org/transcripts/1190568472
| naniwaduni wrote:
| While "knowing they need to run an export to sanitize
| their data" is (unfortunately!) a thing user just Have to
| Know, a wider issue imo is that we also shouldn't really
| be encouraging developers to casually assume that users
| will only interact with their software through their own
| ordained interfaces.
| NavinF wrote:
| > Linux, the OS says it is atomic
|
| nitpick: At least on some filesystems if you rename a.txt
| to overwrite b.txt and the machine crashes, you might end
| up both a.txt and b.txt hardlinked so they contain the same
| data.
|
| Of course this is no big deal since b.txt is still updated
| atomically so it contains the new data (assuming a.txt was
| fsynced) or the old data. I assume nobody depends on a.txt
| being deleted simultaneously.
| SanderNL wrote:
| You are right and like you explained this is trivially easily
| fixed by autosaving regularly.
|
| What I have trouble imagining is people working with
| documents on computers for more than a few years yet somehow
| failing to develop the Always Save Instinct. I regularly
| catch myself saving unreasonably often.
| dsego wrote:
| That may have been true years ago in win 95 or xp days. The
| modern paradigm starting with google docs is that things
| are automatically saved and even always sharable through
| the cloud, making manual saving actually an atavistic
| leftover of a bygone era.
| iggldiggl wrote:
| What if I actually _don 't want_ any changes saved
| because I've only opened a document for reference
| purposes?
| NavinF wrote:
| Modern apps like gdocs have a toggle for switching
| between read-only, suggest changes, and editing. If you
| forgot to toggle, you can just open version history and
| revert. MS Office also had version history for nearly a
| decade.
| ghkbrew wrote:
| Then you proactively prevent changes. Either "open a
| copy" or "open in readonly mode".
|
| If you make saving the default you have to manually not
| save. It's a trade off versus default no saves with
| manual saves
| ReactiveJelly wrote:
| Have a "Read-only" checkbox. For the love of God, have a
| "read-only" checkbox.
| josephg wrote:
| Users shouldn't ever need to adapt to computer crashes like
| this. Software should always auto save or have recovery
| files or something. As a principle, software should hold
| anything a user inputs with reverence.
| mixmastamyk wrote:
| Yes, however you have to be sure every single program
| across decades is written with those rules in mind. Hard
| on any OS, but a lost cause on */Linux.
| SanderNL wrote:
| I agree. Maybe as a dev I've become cynical and don't
| trust anything. Least of all some app holding my
| document.
|
| Makes me think of the "Voting software" xkcd:
| https://xkcd.com/2030
|
| "I don't quite know how to put this, but our entire field
| is bad at what we do, and if you rely on us, everyone
| will die."
| belenos46 wrote:
| You think that's cynical? I must be some avatar
| curmudgeonliness then.
|
| I'm pretty sure that what's actually going on is that
| everything we think of as a 'profession' is that way, and
| that people are in fact dying because of it.
|
| The difference is that devs are honest about it.
| chrisshroba wrote:
| I used to have that instinct but lost it in the age of auto
| save. The applications (web or native) I use most often all
| do it for me: Google docs, Dropbox paper, notion, vscode. I
| don't think I'm alone in this!
| dsego wrote:
| I actually tried using open/libre docs a few years ago
| just because of it being open source. I was trying to
| make a point of using locally installed software and
| avoid google products. Then the thing crashed and I lost
| an hour of work because it didn't save a temporary
| version. That's when I gave up on it for good.
| olddustytrail wrote:
| Libre office does keep a temporary version that allows
| you to recover, so you're talking crap.
| dsego wrote:
| It might very well keep it, but either that behavior
| wasn't turned on by default or it crashed in way where it
| wasn't recoverable. I know I lost work.
| mixmastamyk wrote:
| Doesn't need to be turned on. However it's always
| possible you mistakenly pressed Esc/Close, didn't read
| the dialog, or hit a very obscure bug?
|
| However this has worked well for twenty years, so PEBKAC
| is a reasonable conclusion.
| p4bl0 wrote:
| Yes, I am aware of that, and you are right about this in
| general. In my particular case however, it is preferable to
| loose some work in the rare cases were a crash occurs than to
| have a copy of the file in some place that the users are not
| aware of. Of course if crashes were frequent the trade-off
| would be different.
| avereveard wrote:
| Good ole .filename.swp
| ealexhudson wrote:
| ODT was designed to be standardised: while the predecessor format
| was very similar too, it relies very heavily on XHTML, SVG, and
| CSS, to name but three (there's a lot more).
|
| Without being able to call out to existing standards, the ODT
| spec itself would suddenly become massive. The effort to update
| the standards appears to be significant and hasn't progressed
| much in recent years already :/
|
| I think realistically, an Sqlite format could be offered as an
| option, but the office doc ship has really sailed.
|
| Good argument to formalise the spec of Sqlite as a standard
| though...
| dfox wrote:
| The specification is massive (840 pages) even though it is
| written in very terse way that does not really specify the
| effects and behavior, only the syntax.
|
| On the other hand if one ignores few warts (explosion of local
| styles and text spans due to ooo:rsid attribute, non-sparse
| spreedsheets and weird mechanism for styling tables as a few
| examples) it is really well designed markup for this kind of
| document data that strikes right balance between it being
| semantic markup and representing the kinds of stuff users want
| to do. Compare that with Office OpenXML with stateful
| formatting empty tags (yes, really, in DOCX <b/> _TOGGLES_
| whether following text is bold).
| chadcmulligan wrote:
| AutoCAD uses a database as its file format, it is fairly slow.
| cm2187 wrote:
| 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
| file size, memory consumption and therefore increase performance
| (less I/O). Is there a technical reason why this would not be
| desirable?
| Etheryte wrote:
| My first guess is that if you always store the full string you
| don't need to scan the database to see if you already have the
| same string. Essentially you choose to use more space but
| reduce load. Regardless of whether you do the string deduping
| on inserts or async later on, you have to do it at some point
| and the unpredictable performance overhead might be
| undesirable.
| cm2187 wrote:
| Well it should be a dictionary lookup, it should be pretty
| fast and predictable. And for freeing it up, it should be a
| good candidate for reference counting.
| The_Colonel wrote:
| If you have the same (long-ish) string repeating many times in
| a database, it points to a DB schema needing normalization.
| cm2187 wrote:
| I guess it depends on the use case. If you load a csv file
| into a sqlite database, normalisation isn't the first thing
| you do.
| kortex wrote:
| 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.
| MrResearcher wrote:
| BLOBs in sqlite can be up to 2GB or less, depending on the
| compilation flags. If you store 2GB and the other application
| uses sqlite compiled with support for less than 2GB BLOB size,
| good luck on getting them to work... If you want to store content
| larger than 2GB in sqlite, you have to chunk them, manage the
| chunk sequences, etc. And you can't overwrite a fixed size 2KB
| portion at the specified offset, you'll have to rewrite the
| entire 2GB chunk.
___________________________________________________________________
(page generated 2023-09-18 23:01 UTC)