| 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 |
<?xml version="1.0" encoding="UTF-8"?> |
| 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 — 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 |
|