Debian Codegraph @

www/affcase1.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>What If OpenDocument Used SQLite?</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120
121 <h1 align="center">
122 What If OpenDocument Used SQLite?</h1>
123
124 <h2>Introduction</h2>
125
126 <p>Suppose the
127 <a href="http://en.wikipedia.org/wiki/OpenDocument">OpenDocument</a> file format,
128 and specifically the "ODP" OpenDocument Presentation format, were
129 built around SQLite. Benefits would include:
130 <ul>
131 <li>Smaller documents
132 <li>Faster File/Save times
133 <li>Faster startup times
134 <li>Less memory used
135 <li>Document versioning
136 <li>A better user experience
137 </ul>
138
139 <p>
140 Note that this is only a thought experiment.
141 We are not suggesting that OpenDocument be changed.
142 Nor is this article a criticism of the current OpenDocument
143 design. The point of this essay is to suggest ways to improve
144 future file format designs.
145
146 <h2>About OpenDocument And OpenDocument Presentation</h2>
147
148 <p>
149 The OpenDocument file format is used for office applications:
150 word processors, spreadsheets, and presentations. It was originally
151 designed for the OpenOffice suite but has since been incorporated into
152 other desktop application suites. The OpenOffice application has been
153 forked and renamed a few times. This author's primary use for OpenDocument is
154 building slide presentations with either
155 <a href="https://www.neooffice.org/neojava/en/index.php">NeoOffice</a> on Mac, or
156 <a href="http://www.libreoffice.org/">LibreOffice</a> on Linux and Windows.
157
158 <p>
159 An OpenDocument Presentation or "ODP" file is a
160 <a href="http://en.wikipedia.org/wiki/Zip_%28file_format%29">ZIP archive</a> containing
161 XML files describing presentation slides and separate image files for the
162 various images that are included as part of the presentation.
163 (OpenDocument word processor and spreadsheet files are similarly
164 structured but are not considered by this article.) The reader can
165 easily see the content of an ODP file by using the "zip -l" command.
166 For example, the following is the "zip -l" output from a 49-slide presentation
167 about SQLite from the 2014
168 <a href="http://southeastlinuxfest.org/">SouthEast LinuxFest</a>
169 conference:
170
171 <blockquote><pre>
172 Archive: self2014.odp
173 Length Date Time Name
174 --------- ---------- ----- ----
175 47 2014-06-21 12:34 mimetype
176 0 2014-06-21 12:34 Configurations2/statusbar/
177 0 2014-06-21 12:34 Configurations2/accelerator/current.xml
178 0 2014-06-21 12:34 Configurations2/floater/
179 0 2014-06-21 12:34 Configurations2/popupmenu/
180 0 2014-06-21 12:34 Configurations2/progressbar/
181 0 2014-06-21 12:34 Configurations2/menubar/
182 0 2014-06-21 12:34 Configurations2/toolbar/
183 0 2014-06-21 12:34 Configurations2/images/Bitmaps/
184 54702 2014-06-21 12:34 Pictures/10000000000001F40000018C595A5A3D.png
185 46269 2014-06-21 12:34 Pictures/100000000000012C000000A8ED96BFD9.png
186 <i>... 58 other pictures omitted...</i>
187 13013 2014-06-21 12:34 Pictures/10000000000000EE0000004765E03BA8.png
188 1005059 2014-06-21 12:34 Pictures/10000000000004760000034223EACEFD.png
189 211831 2014-06-21 12:34 content.xml
190 46169 2014-06-21 12:34 styles.xml
191 1001 2014-06-21 12:34 meta.xml
192 9291 2014-06-21 12:34 Thumbnails/thumbnail.png
193 38705 2014-06-21 12:34 Thumbnails/thumbnail.pdf
194 9664 2014-06-21 12:34 settings.xml
195 9704 2014-06-21 12:34 META-INF/manifest.xml
196 --------- -------
197 10961006 78 files
198 </pre></blockquote>
199
200 <p>
201 The ODP ZIP archive contains four different XML files:
202 content.xml, styles.xml, meta.xml, and settings.xml. Those four files
203 define the slide layout, text content, and styling. This particular
204 presentation contains 62 images, ranging from full-screen pictures to
205 tiny icons, each stored as a separate file in the Pictures
206 folder. The "mimetype" file contains a single line of text that says:
207
208 <blockquote><pre>
209 application/vnd.oasis.opendocument.presentation
210 </pre></blockquote>
211
212 <p>The purpose of the other files and folders is presently
213 unknown to the author but is probably not difficult to figure out.
214
215 <h2>Limitations Of The OpenDocument Presentation Format</h2>
216
217 <p>
218 The use of a ZIP archive to encapsulate XML files plus resources is an
219 elegant approach to an application file format.
220 It is clearly superior to a custom binary file format.
221 But using an SQLite database as the
222 container, instead of ZIP, would be more elegant still.
223
224 <p>A ZIP archive is basically a key/value database, optimized for
225 the case of write-once/read-many and for a relatively small number
226 of distinct keys (a few hundred to a few thousand) each with a large BLOB
227 as its value. A ZIP archive can be viewed as a "pile-of-files"
228 database. This works, but it has some shortcomings relative to an
229 SQLite database, as follows:
230
231 <ol>
232 <li><p><b>Incremental update is hard.</b>
233 <p>
234 It is difficult to update individual entries in a ZIP archive.
235 It is especially difficult to update individual entries in a ZIP
236 archive in a way that does not destroy
237 the entire document if the computer loses power and/or crashes
238 in the middle of the update. It is not impossible to do this, but
239 it is sufficiently difficult that nobody actually does it. Instead, whenever
240 the user selects "File/Save", the entire ZIP archive is rewritten.
241 Hence, "File/Save" takes longer than it ought, especially on
242 older hardware. Newer machines are faster, but it is still bothersome
243 that changing a single character in a 50 megabyte presentation causes one
244 to burn through 50 megabytes of the finite write life on the SSD.
245
246 <li><p><b>Startup is slow.</b>
247 <p>
248 In keeping with the pile-of-files theme, OpenDocument stores all slide
249 content in a single big XML file named "content.xml".
250 LibreOffice reads and parses this entire file just to display
251 the first slide.
252 LibreOffice also seems to
253 read all images into memory as well, which makes sense seeing as when
254 the user does "File/Save" it is going to have to write them all back out
255 again, even though none of them changed. The net effect is that
256 start-up is slow. Double-clicking an OpenDocument file brings up a
257 progress bar rather than the first slide.
258 This results in a bad user experience.
259 The situation grows ever more annoying as
260 the document size increases.
261
262 <li><p><b>More memory is required.</b>
263 <p>
264 Because ZIP archives are optimized for storing big chunks of content, they
265 encourage a style of programming where the entire document is read into
266 memory at startup, all editing occurs in memory, then the entire document
267 is written to disk during "File/Save". OpenOffice and its descendants
268 embrace that pattern.
269
270 <p>
271 One might argue that it is ok, in this era of multi-gigabyte desktops, to
272 read the entire document into memory.
273 But it is not ok.
274 For one, the amount of memory used far exceeds the (compressed) file size
275 on disk. So a 50MB presentation might take 200MB or more RAM.
276 That still is not a problem if one only edits a single document at a time.
277 But when working on a talk, this author will typically have 10 or 15 different
278 presentations up all at the same
279 time (to facilitate copy/paste of slides from past presentations) and so
280 gigabytes of memory are required.
281 Add in an open web browser or two and a few other
282 desktop apps, and suddenly the disk is whirling and the machine is swapping.
283 And even having just a single document is a problem when working
284 on an inexpensive Chromebook retrofitted with Ubuntu.
285 Using less memory is always better.
286 </p>
287
288 <li><p><b>Crash recovery is difficult.</b>
289 <p>
290 The descendants of OpenOffice tend to segfault more often than commercial
291 competitors. Perhaps for this reason, the OpenOffice forks make
292 periodic backups of their in-memory documents so that users do not lose
293 all pending edits when the inevitable application crash does occur.
294 This causes frustrating pauses in the application for the few seconds
295 while each backup is being made.
296 After restarting from a crash, the user is presented with a dialog box
297 that walks them through the recovery process. Managing the crash
298 recovery this way involves lots of extra application logic and is
299 generally an annoyance to the user.
300
301 <li><p><b>Content is inaccessible.</b>
302 <p>
303 One cannot easily view, change, or extract the content of an
304 OpenDocument presentation using generic tools.
305 The only reasonable way to view or edit an OpenDocument document is to open
306 it up using an application that is specifically designed to read or write
307 OpenDocument (read: LibreOffice or one of its cousins). The situation
308 could be worse. One can extract and view individual images (say) from
309 a presentation using just the "zip" archiver tool. But it is not reasonable
310 try to extract the text from a slide. Remember that all content is stored
311 in a single "context.xml" file. That file is XML, so it is a text file.
312 But it is not a text file that can be managed with an ordinary text
313 editor. For the example presentation above, the content.xml file
314 consist of exactly two lines. The first line of the file is just:
315
316 <blockquote><pre>
317 &lt;?xml version="1.0" encoding="UTF-8"?&gt;
318 </pre></blockquote>
319
320 <p>The second line of the file contains 211792 characters of
321 impenetrable XML. Yes, 211792 characters all on one line.
322 This file is a good stress-test for a text editor.
323 Thankfully, the file is not some obscure
324 binary format, but in terms of accessibility, it might as well be
325 written in Klingon.
326 </ol>
327
328 <h2>First Improvement: Replace ZIP with SQLite</h2>
329
330 <p>
331 Let us suppose that instead of using a ZIP archive to store its files,
332 OpenDocument used a very simple SQLite database with the following
333 single-table schema:
334
335 <blockquote><pre>
336 CREATE TABLE OpenDocTree(
337 filename TEXT PRIMARY KEY, -- Name of file
338 filesize BIGINT, -- Size of file after decompression
339 content BLOB -- Compressed file content
340 );
341 </pre></blockquote>
342
343 <p>
344 For this first experiment, nothing else about the file format is changed.
345 The OpenDocument is still a pile-of-files, only now each file is a row
346 in an SQLite database rather than an entry in a ZIP archive.
347 This simple change does not use the power of a relational
348 database. Even so, this simple change shows some improvements.
349
350 <a name="smaller"></a>
351
352 <p>
353 Surprisingly, using SQLite in place of ZIP makes the presentation
354 file smaller. Really. One would think that a relational database file
355 would be larger than a ZIP archive, but at least in the case of NeoOffice
356 that is not so. The following is an actual screen-scrape showing
357 the sizes of the same NeoOffice presentation, both in its original
358 ZIP archive format as generated by NeoOffice (self2014.odp), and
359 as repacked as an SQLite database using the
360 <a href="https://sqlite.org/sqlar/doc/trunk/README.md">SQLAR</a> utility:
361
362 <blockquote><pre>
363 -rw-r--r-- 1 drh staff 10514994 Jun 8 14:32 self2014.odp
364 -rw-r--r-- 1 drh staff 10464256 Jun 8 14:37 self2014.sqlar
365 -rw-r--r-- 1 drh staff 10416644 Jun 8 14:40 zip.odp
366 </pre></blockquote>
367
368 <p>
369 The SQLite database file ("self2014.sqlar") is about a
370 half percent smaller than the equivalent ODP file! How can this be?
371 Apparently the ZIP archive generator logic in NeoOffice
372 is not as efficient as it could be, because when the same pile-of-files
373 is recompressed using the command-line "zip" utility, one gets a file
374 ("zip.odp") that is smaller still, by another half percent, as seen
375 in the third line above. So, a well-written ZIP archive
376 can be slightly smaller than the equivalent SQLite database, as one would
377 expect. But the difference is slight. The key take-away is that an
378 SQLite database is size-competitive with a ZIP archive.
379
380 <p>
381 The other advantage to using SQLite in place of
382 ZIP is that the document can now be updated incrementally, without risk
383 of corrupting the document if a power loss or other crash occurs in the
384 middle of the update. (Remember that writes to
385 <a href="atomiccommit.html">SQLite databases are atomic</a>.) True, all the
386 content is still kept in a single big XML file ("content.xml") which must
387 be completely rewritten if so much as a single character changes. But
388 with SQLite, only that one file needs to change. The other 77 files in the
389 repository can remain unaltered. They do not all have to be rewritten,
390 which in turn makes "File/Save" run much faster and saves wear on SSDs.
391
392 <h2>Second Improvement: Split content into smaller pieces</h2>
393
394 <p>
395 A pile-of-files encourages content to be stored in a few large chunks.
396 In the case of ODP, there are just four XML files that define the layout
397 of all slides in a presentation. An SQLite database allows storing
398 information in a few large chunks, but SQLite is also adept and efficient
399 at storing information in numerous smaller pieces.
400
401 <p>
402 So then, instead of storing all content for all slides in a single
403 oversized XML file ("content.xml"), suppose there was a separate table
404 for storing the content of each slide separately. The table schema
405 might look something like this:
406
407 <blockquote><pre>
408 CREATE TABLE slide(
409 pageNumber INTEGER, -- The slide page number
410 slideContent TEXT -- Slide content as XML or JSON
411 );
412 CREATE INDEX slide_pgnum ON slide(pageNumber); -- Optional
413 </pre></blockquote>
414
415 <p>The content of each slide could still be stored as compressed XML.
416 But now each page is stored separately. So when opening a new document,
417 the application could simply run:
418
419 <blockquote><pre>
420 SELECT slideContent FROM slide WHERE pageNumber=1;
421 </pre></blockquote>
422
423 <p>This query will quickly and efficiently return the content of the first
424 slide, which could then be speedily parsed and displayed to the user.
425 Only one page needs to be read and parsed in order to render the first screen,
426 which means that the first screen appears much faster and
427 there is no longer a need for an annoying progress bar.
428
429 <p>If the application wanted
430 to keep all content in memory, it could continue reading and parsing the
431 other pages using a background thread after drawing the first page. Or,
432 since reading from SQLite is so efficient, the application might
433 instead choose to reduce its memory footprint and only keep a single
434 slide in memory at a time. Or maybe it keeps the current slide and the
435 next slide in memory, to facilitate rapid transitions to the next slide.
436
437 <p>
438 Notice that dividing up the content into smaller pieces using an SQLite
439 table gives flexibility to the implementation. The application can choose
440 to read all content into memory at startup. Or it can read just a
441 few pages into memory and keep the rest on disk. Or it can read just a
442 single page into memory at a time. And different versions of the application
443 can make different choices without having to make any changes to the
444 file format. Such options are not available when all content is in
445 a single big XML file in a ZIP archive.
446
447 <p>
448 Splitting content into smaller pieces also helps File/Save operations
449 to go faster. Instead of having to write back the content of all pages
450 when doing a File/Save, the application only has to write back those
451 pages that have actually changed.
452
453 <p>
454 One minor downside of splitting content into smaller pieces is that
455 compression does not work as well on shorter texts and so the size of
456 the document might increase. But as the bulk of the document space
457 is used to store images, a small reduction in the compression efficiency
458 of the text content will hardly be noticeable, and is a small price
459 to pay for an improved user experience.
460
461 <h2>Third Improvement: Versioning</h2>
462
463 <p>
464 Once one is comfortable with the concept of storing each slide separately,
465 it is a small step to support versioning of the presentation. Consider
466 the following schema:
467
468 <blockquote><pre>
469 CREATE TABLE slide(
470 slideId INTEGER PRIMARY KEY,
471 derivedFrom INTEGER REFERENCES slide,
472 content TEXT -- XML or JSON or whatever
473 );
474 CREATE TABLE version(
475 versionId INTEGER PRIMARY KEY,
476 priorVersion INTEGER REFERENCES version,
477 checkinTime DATETIME, -- When this version was saved
478 comment TEXT, -- Description of this version
479 manifest TEXT -- List of integer slideIds
480 );
481 </pre></blockquote>
482
483 <p>
484 In this schema, instead of each slide having a page number that determines
485 its order within the presentation, each slide has a unique
486 integer identifier that is unrelated to where it occurs in sequence.
487 The order of slides in the presentation is determined by a list of
488 slideIds, stored as a text string in the MANIFEST column of the VERSION
489 table.
490 Since multiple entries are allowed in the VERSION table, that means that
491 multiple presentations can be stored in the same document.
492
493 <p>
494 On startup, the application first decides which version it
495 wants to display. Since the versionId will naturally increase in time
496 and one would normally want to see the latest version, an appropriate
497 query might be:
498
499 <blockquote><pre>
500 SELECT manifest, versionId FROM version ORDER BY versionId DESC LIMIT 1;
501 </pre></blockquote>
502
503 <p>
504 Or perhaps the application would rather use the
505 most recent checkinTime:
506
507 <blockquote><pre>
508 SELECT manifest, versionId, max(checkinTime) FROM version;
509 </pre></blockquote>
510
511 <p>
512 Using a single query such as the above, the application obtains a list
513 of the slideIds for all slides in the presentation. The application then
514 queries for the content of the first slide, and parses and displays that
515 content, as before.
516
517 <p>(Aside: Yes, that second query above that uses "max(checkinTime)"
518 really does work and really does return a well-defined answer in SQLite.
519 Such a query either returns an undefined answer or generates an error
520 in many other SQL database engines, but in SQLite it does what you would
521 expect: it returns the manifest and versionId of the entry that has the
522 maximum checkinTime.)
523
524 <p>When the user does a "File/Save", instead of overwriting the modified
525 slides, the application can now make new entries in the SLIDE table for
526 just those slides that have been added or altered. Then it creates a
527 new entry in the VERSION table containing the revised manifest.
528
529 <p>The VERSION table shown above has columns to record a check-in comment
530 (presumably supplied by the user) and the time and date at which the File/Save
531 action occurred. It also records the parent version to record the history
532 of changes. Perhaps the manifest could be stored as a delta from the
533 parent version, though typically the manifest will be small enough that
534 storing a delta might be more trouble than it is worth. The SLIDE table
535 also contains a derivedFrom column which could be used for delta encoding
536 if it is determined that saving the slide content as a delta from its
537 previous version is a worthwhile optimization.
538
539 <p>So with this simple change, the ODP file now stores not just the most
540 recent edit to the presentation, but a history of all historic edits. The
541 user would normally want to see just the most recent edition of the
542 presentation, but if desired, the user can now go backwards in time to
543 see historical versions of the same presentation.
544
545 <p>Or, multiple presentations could be stored within the same document.
546
547 <p>With such a schema, the application would no longer need to make
548 periodic backups of the unsaved changes to a separate file to avoid lost
549 work in the event of a crash. Instead, a special "pending" version could
550 be allocated and unsaved changes could be written into the pending version.
551 Because only changes would need to be written, not the entire document,
552 saving the pending changes would only involve writing a few kilobytes of
553 content, not multiple megabytes, and would take milliseconds instead of
554 seconds, and so it could be done frequently and silently in the background.
555 Then when a crash occurs and the user reboots, all (or almost all)
556 of their work is retained. If the user decides to discard unsaved changes,
557 they simply go back to the previous version.
558
559 <p>
560 There are details to fill in here.
561 Perhaps a screen can be provided that displays all historical changes
562 (perhaps with a graph) allowing the user to select which version they
563 want to view or edit. Perhaps some facility can be provided to merge
564 forks that might occur in the version history. And perhaps the
565 application should provide a means to purge old and unwanted versions.
566 The key point is that using an SQLite database to store the content,
567 rather than a ZIP archive, makes all of these features much, much easier
568 to implement, which increases the possibility that they will eventually
569 get implemented.
570
571 <h2>And So Forth...</h2>
572
573 <p>
574 In the previous sections, we have seen how moving from a key/value
575 store implemented as a ZIP archive to a simple SQLite database
576 with just three tables can add significant capabilities to an application
577 file format.
578 We could continue to enhance the schema with new tables, with indexes
579 added for performance, with triggers and views for programming convenience,
580 and constraints to enforce consistency of content even in the face of
581 programming errors. Further enhancement ideas include:
582 <ul>
583 <li> Store an <a href="undoredo.html">automated undo/redo stack</a> in a database table so that
584 Undo could go back into prior edit sessions.
585 <li> Add <a href="fts3.html#fts4">full text search</a> capabilities to the slide deck, or across
586 multiple slide decks.
587 <li> Decompose the "settings.xml" file into an SQL table that
588 is more easily viewed and edited by separate applications.
589 <li> Break out the "Presenter Notes" from each slide into a separate
590 table, for easier access from third-party applications and/or scripts.
591 <li> Enhance the presentation concept beyond the simple linear sequence of
592 slides to allow for side-tracks and excursions to be taken depending on
593 how the audience is responding.
594 </ul>
595
596 <p>
597 An SQLite database has a lot of capability, which
598 this essay has only begun to touch upon. But hopefully this quick glimpse
599 has convinced some readers that using an SQL database as an application
600 file format is worth a second look.
601
602 <p>
603 Some readers might resist using SQLite as an application
604 file format due to prior exposure to enterprise SQL databases and
605 the caveats and limitations of those other systems.
606 For example, many enterprise database
607 engines advise against storing large strings or BLOBs in the database
608 and instead suggest that large strings and BLOBs be stored as separate
609 files and the filename stored in the database. But SQLite
610 is not like that. Any column of an SQLite database can hold
611 a string or BLOB up to about a gigabyte in size. And for strings and
612 BLOBs of 100 kilobytes or less,
613 <a href="intern-v-extern-blob.html">I/O performance is better</a> than using separate
614 files.
615
616 <p>
617 Some readers might be reluctant to consider SQLite as an application
618 file format because they have been inculcated with the idea that all
619 SQL database schemas must be factored into
620 <a href="https://en.wikipedia.org/wiki/Third_normal_form">Third Normal Form (3NF)</a>
621 and store only small primitive data types such as strings and integers. Certainly
622 relational theory is important and designers should strive to understand
623 it. But, as demonstrated above, it is often quite acceptable to store
624 complex information as XML or JSON in text fields of a database.
625 Do what works, not what your database professor said you ought to do.
626
627 <h2>Review Of The Benefits Of Using SQLite</h2>
628
629 <p>
630 In summary,
631 the claim of this essay is that using SQLite as a container for an application
632 file format like OpenDocument
633 and storing lots of smaller objects in that container
634 works out much better than using a ZIP archive holding a few larger objects.
635 To wit:
636
637 <ol>
638 <li><p>
639 An SQLite database file is approximately the same size, and in some cases
640 smaller, than a ZIP archive holding the same information.
641
642 <li><p>
643 The <a href="atomiccommit.html">atomic update capabilities</a>
644 of SQLite allow small incremental changes
645 to be safely written into the document. This reduces total disk I/O
646 and improves File/Save performance, enhancing the user experience.
647
648 <li><p>
649 Startup time is reduced by allowing the application to read in only the
650 content shown for the initial screen. This largely eliminates the
651 need to show a progress bar when opening a new document. The document
652 just pops up immediately, further enhancing the user experience.
653
654 <li><p>
655 The memory footprint of the application can be dramatically reduced by
656 only loading content that is relevant to the current display and keeping
657 the bulk of the content on disk. The fast query capability of SQLite
658 make this a viable alternative to keeping all content in memory at all times.
659 And when applications use less memory, it makes the entire computer more
660 responsive, further enhancing the user experience.
661
662 <li><p>
663 The schema of an SQL database is able to represent information more directly
664 and succinctly than a key/value database such as a ZIP archive. This makes
665 the document content more accessible to third-party applications and scripts
666 and facilitates advanced features such as built-in document versioning, and
667 incremental saving of work in progress for recovery after a crash.
668 </ol>
669
670 <p>
671 These are just a few of the benefits of using SQLite as an application file
672 format &mdash; the benefits that seem most likely to improve the user
673 experience for applications like OpenOffice. Other applications might
674 benefit from SQLite in different ways. See the <a href="appfileformat.html">Application File Format</a>
675 document for additional ideas.
676
677 <p>
678 Finally, let us reiterate that this essay is a thought experiment.
679 The OpenDocument format is well-established and already well-designed.
680 Nobody really believes that OpenDocument should be changed to use SQLite
681 as its container instead of ZIP. Nor is this article a criticism of
682 OpenDocument for not choosing SQLite as its container since OpenDocument
683 predates SQLite. Rather, the point of this article is to use OpenDocument
684 as a concrete example of how SQLite can be used to build better
685 application file formats for future projects.
686 <p align="center"><small><i>This page was last updated on 2025-05-12 11:56:41Z </small></i></p>
687