4 ms·

Let's say you want to extract an image file from an .odt file and you haven't got Writer installed. You can do that by extracting the file from the .odt using

Let's say you want to extract an image file from an .odt file and you haven't got Writer installed. You can do that by extracting the file from the .odt using unzip or similar. This article champions the idea that you should store the files as compressed binary BLOBs in SQLite. What does that mean you need to do? Connect to the file with SQLite, fetch the BLOB, hexdump it since it will return as a string, decompress it, and then save it to disk. Or save it to disk and then decompress it. You'd have to do something like this: https://stackoverflow.com/questions/15448373/how-to-dump-a-file-stored-in-a-sqlite-database-as-a-blob https://stackoverflow.com/questions/15448373/how-to-dump-a-f... That is what Open Document is supposed to end. This is not an excellent article. It's complaining that the Open Document Formats are bad because they don't use an optimized format. They're not supposed to be optimized. Documents aren't large enough to cause the types of problems that databases are intended to fix. They're supposed to be portable and easy to work with. They make it possible to access your data using the most basic computing tools: a ZIP archive tool and a text editor. That's the whole point.

asynchronous13 9y ago
> It's complaining that the Open Document Formats are bad because they don't use an optimized format. That's the opposite of what the article states. From the introduction of the article: > Note that this is only a thought experiment. We are not suggesting that OpenDocument be changed. Nor is this article a criticism of the current OpenDocument design.
- da_chicken 9y ago
  Yeah, and if you finish the quote, the next line is: > The point of this essay is to suggest ways to improve future file format designs. That makes it sound like, "No offense, but < extremely rude comment >." Just because they say they're not trying to be critical doesn't mean they're not actually being critical.
SQLite 9y ago
If you have an image stored in an SQLite database file, you can extract it using the "sqlite3" command-line tool as described at https://www.sqlite.org/cli.html#file_i_o_functions https://www.sqlite.org/cli.html#file_i_o_functions Or, you could do it several dozen other ways. For example, maybe you have an archiver tool for SQLite that works like "zip" - https://www.sqlite.org/sqlar/doc/trunk/README.md https://www.sqlite.org/sqlar/doc/trunk/README.md About this much we agree: The objective is to make the content of the document more accessible and easier to use. The article intended to show that using an SQLite database rather than a pile-of-files in a ZIP archive achieves that goal. If my presentation were stored in an SQLite database, I might (depending on how the schema was structured of course) be able to ask interesting questions of the presentation, such as: * How many pages are in this presentation? * Which pages contain the word "discombobulated"? * How many images are in the presentation? * Does this presentation use a prepackaged background? * Does the presentation begin with my companies standard "start" slide? Can you write (say) a Python program that will answer any of the above about an OpenOffice .odp file? All I'm trying to say is that if OpenOffice .odp files were SQLite databases with a sensible schema, then probably you could answer any of the questions above with perhaps 5 lines line of code or less. Or just a shell script that uses the "sqlite3" command-line tool. As it stands now, if you want low-level information about an OpenOffice presentation, you have to open manually open the document on your disktop and click around - it cannot be (easily) automated. An ".odp" file is essentially an opaque BLOB that is not useful to me. Only the OpenOffice program (or its various forks) can access it. I have 104 historical presentations in a folder and I want to find some slide I wrote years ago. Right now, I have to laboriously open each presentation and manually search for the slide I want. If the format were SQLite, I could perhaps do the same with a query, which if run from a shell script, would quickly search all 104 presentations for me. So, Yes, the whole point is to make the content more easily accessible and usable. Relative to a pile-of-files in a ZIP archive, SQLite does exactly that.
- da_chicken 9y ago
  > Can you write (say) a Python program that will answer any of the above about an OpenOffice .odp file? Using the libraries or packages available from the OpenDocument home page? (http://opendocumentformat.org/developers/ http://opendocumentformat.org/developers/) Probably, yes. Four of your five questions questions are just: "Is there an API?" The answer is: Yes, yes there is. Did you try searching for an Open Document API? For the image file one, however, all you really need is ZipFile.infolist() because ZIP files already have an API. > All I'm trying to say is that if OpenOffice .odp files were SQLite databases with a sensible schema, then probably you could answer any of the questions above with perhaps 5 lines line of code or less. Most of the questions involve an XQuery against content.xml. You could iterate through the files, extract content.xml, and apply the XQuery. No, it's not easy to write the correct XQuery because XQuery sucks and the schema is complex, but it can definitely be done. I don't know why you think it's going to be magically simplified by changing the storage method. You'd either have compressed XML BLOBs like the article's first suggestion -- which puts you right back where you already are -- or you'll have dozens of tables with a ton of metadata and document structure to dig through. Either way you will need a schema reference to understand what's going on. An RDBMS isn't going to make the content simpler. The schemas are complex because the documents are potentially very complex.
justinclift 9y ago
> What does that mean you need to do? Connect to the file with SQLite, fetch the BLOB, hexdump it since it will return as a string, decompress it, and then save it to disk. Or you could just use a SQLite GUI (eg sqlitebrowser.org). Select the field/BLOB you're interested in, then Export (saves it to a file). Done. :)

asynchronous13 9y ago

> It's complaining that the Open Document Formats are bad because they don't use an optimized format. That's the opposite of what the article states. From the introduction of the article: > Note that this is only a thought experiment. We are not suggesting that OpenDocument be changed. Nor is this article a criticism of the current OpenDocument design.

da_chicken 9y ago
Yeah, and if you finish the quote, the next line is: > The point of this essay is to suggest ways to improve future file format designs. That makes it sound like, "No offense, but < extremely rude comment >." Just because they say they're not trying to be critical doesn't mean they're not actually being critical.

SQLite 9y ago

If you have an image stored in an SQLite database file, you can extract it using the "sqlite3" command-line tool as described at https://www.sqlite.org/cli.html#file_i_o_functions https://www.sqlite.org/cli.html#file_i_o_functions Or, you could do it several dozen other ways. For example, maybe you have an archiver tool for SQLite that works like "zip" - https://www.sqlite.org/sqlar/doc/trunk/README.md https://www.sqlite.org/sqlar/doc/trunk/README.md About this much we agree: The objective is to make the content of the document more accessible and easier to use. The article intended to show that using an SQLite database rather than a pile-of-files in a ZIP archive achieves that goal. If my presentation were stored in an SQLite database, I might (depending on how the schema was structured of course) be able to ask interesting questions of the presentation, such as: * How many pages are in this presentation? * Which pages contain the word "discombobulated"? * How many images are in the presentation? * Does this presentation use a prepackaged background? * Does the presentation begin with my companies standard "start" slide? Can you write (say) a Python program that will answer any of the above about an OpenOffice .odp file? All I'm trying to say is that if OpenOffice .odp files were SQLite databases with a sensible schema, then probably you could answer any of the questions above with perhaps 5 lines line of code or less. Or just a shell script that uses the "sqlite3" command-line tool. As it stands now, if you want low-level information about an OpenOffice presentation, you have to open manually open the document on your disktop and click around - it cannot be (easily) automated. An ".odp" file is essentially an opaque BLOB that is not useful to me. Only the OpenOffice program (or its various forks) can access it. I have 104 historical presentations in a folder and I want to find some slide I wrote years ago. Right now, I have to laboriously open each presentation and manually search for the slide I want. If the format were SQLite, I could perhaps do the same with a query, which if run from a shell script, would quickly search all 104 presentations for me. So, Yes, the whole point is to make the content more easily accessible and usable. Relative to a pile-of-files in a ZIP archive, SQLite does exactly that.

da_chicken 9y ago
> Can you write (say) a Python program that will answer any of the above about an OpenOffice .odp file? Using the libraries or packages available from the OpenDocument home page? (http://opendocumentformat.org/developers/ http://opendocumentformat.org/developers/) Probably, yes. Four of your five questions questions are just: "Is there an API?" The answer is: Yes, yes there is. Did you try searching for an Open Document API? For the image file one, however, all you really need is ZipFile.infolist() because ZIP files already have an API. > All I'm trying to say is that if OpenOffice .odp files were SQLite databases with a sensible schema, then probably you could answer any of the questions above with perhaps 5 lines line of code or less. Most of the questions involve an XQuery against content.xml. You could iterate through the files, extract content.xml, and apply the XQuery. No, it's not easy to write the correct XQuery because XQuery sucks and the schema is complex, but it can definitely be done. I don't know why you think it's going to be magically simplified by changing the storage method. You'd either have compressed XML BLOBs like the article's first suggestion -- which puts you right back where you already are -- or you'll have dozens of tables with a ton of metadata and document structure to dig through. Either way you will need a schema reference to understand what's going on. An RDBMS isn't going to make the content simpler. The schemas are complex because the documents are potentially very complex.

justinclift 9y ago

> What does that mean you need to do? Connect to the file with SQLite, fetch the BLOB, hexdump it since it will return as a string, decompress it, and then save it to disk. Or you could just use a SQLite GUI (eg sqlitebrowser.org). Select the field/BLOB you're interested in, then Export (saves it to a file). Done. :)