<title>SQLite As An Application File Format</title> <h2>What Is An Application File Format?</h2> <p> An "application file format" is the schema and/or low-level byte format used to persist appliation state to disk or to exchange information between programs. Some examples of application file formats include: <ul> <li>"*.doc" - Word Perfect and Microsoft Office documents <li>"*.dwg" - AutoCAD drawings <li>"*.pdf" - Portable Document Format from Adobe <li>"*.xls" - Microsoft Excel Spreadsheet <li>".git" - Git source code repository <li>"*.epub" - The Eletronic Publication format used by non-Kindle eBooks <li>"*.odt" - The Open Document format used by OpenOffice and others <li>"*.ppt" - Microsoft PowerPoint presentations <li>"*.odp" - The Open Document presentation format used by OpenOffice and others </ul> <p>The application file formats listed above can be divided into several categories: <ol> <li><p><b>Fully Custom Formats.</b> Custom formats are specifically designed for a single application. DOC, DWG, PDF, XLS, and PPT are examples of custom formats. Custom formats are usually contained within a single file, for ease of transport. They are also usually binary, though the DWG format is a notable exception. Custom file formats require specialized application code to read and write and are not normally accessible from commonly available tools such as unix command-line programs. Generally speaking, to access the content of a custom application file format, you have to have a tool that is specifically engineered to read and/or write that format. <li><p><b>Pile-of-Files Formats.</b> Sometimes the application state is stored in a directory hierarchy of files. Git is a prime example of this, though the phenomenon occurs frequently one-off and bespoke applications. A pile-of-files format essentially uses the filesystem as a key/value database and small chunks of information into separate files. This gives the advantage of making the content more accessible to common utility programs. But even if many of the files in a pile-of-files format are easily readable, there are usually some files that have their own custom format, such as Git "Packfiles", and are hence not readable or writable without specialized tools. It is much less convenient to move a pile-of-files from one place or machine to another, than it is to move a single file. It is hard to make a pile-of-files document into an email attachment, for example. Finally, a pile-of-files format breaks the "document metaphor": there is no one file that a user can point to that is the "document". <li><p><b>ZIP-ed Pile-of-Files Formats.</b> Some applications use a Pile-of-Files that is then encapsulated into a ZIP archive. EPUB, ODT,and ODP are examples of this approach. An EPUB book is really just a ZIP archive that contains various XML files for the text of book chapters, GIF and JPEG images for the artwork, and a specialized catalog file that tells the eBook reader how all the XML and image files fit together. OpenOffice documents (ODT and ODP) are also ZIP archives containing XML and images that represent their content as well as "catalog" files that show the interrelationships between the component parts. A ZIP-ed pile-of-files format is not quite as accessible as a pure pile-of-files format, since the component parts are not immediately visible. But a knowledgeable user can easily get to the component parts using any ZIP archiver. And ZIP-ed pile-of-files formats are not as easy to edit, since one most normally rewrite the entire file just to change one small component part. On the other hand, ZIP-ed pile-of-files formats have the advantage of being contained in a single disk file, which makes them easier to copy and attach and gives a more convenient metaphor for the "document". </ol> <h2>SQLite As The Application File Format</h2> <p> Designers of new applications are encouraged to consider using an SQLite database as their application file format. The use of SQLite as the application file format has a number of advantages over custom file formats, pile-of-file formats, and ZIP-ed pile-of-file formats. </p> <p><b>Ease Of Development.</b> No code is needed for reading or writing the application file. One has merely to link against the SQLite library, or include the [amalgamation | single "sqlite3.c" source file] with the rest of the application C code, and SQLite will take care of all of the application file I/O. This can save having to write, debug, and maintain thousands and thousands of lines of code. <p><b>Single-File Documents.</b> An SQLite database is contained in a single file, which is easily copied or moved or attached. The "document" metaphor is preserved. furthermore, SQLite does not have any database file naming requirements and so the application can use any custom file suffix that it wants. <p><b>High-level Query Language.</b> SQLite is a complete relational database engine, which means that the application can access content using high-level queries. Application developers need not spend time thinking about "how" to retrieve the information they need from a document. Developers write SQL that expresses "what" information they want and leave it to the database engine to figure out how to best retrieve that content. This helps developers to operate "heads up" and remain focused on solving the user's problem, and avoid time spent "heads down" fiddling with low-level file formatting details. <p><b>Accessible Content.</b> Information held in an SQLite database file is accessible using commonly available command-line tools - tools that are installed by default on Mac and Linux systems and that are freely available as a self-contained EXE file on Windows. Unlike custom file formats, application-specific programs are <u>not</u> required to read or write content in an SQLite database. An SQLite database is uses a [file format | well-defined and well-documented] file format that is in widespread use by literally hundreds of thousands of applications and is backwards compatible to its inception in 2003 and which promises to continue to be compatible in years to come. The longevity of SQLite database files is particularly important to bespoke applications, since it allows the document content to be accessed years or decades in the future, long after all traces of the original application have been lost. <p><b>Cross-Platform.</b> SQLite database files are portable between 32-bit and 64-bit machines and between big-endian and little-endian architectures and between windows and unix in all of their various flavors and incarnations. The application need not worry over the byte-order of integers or floating point numbers. Text content can be read or written as UTF-8, UTF-16LE, or UTF-16BE and SQLite will automatically perform any necessary translations on-the-fly. <p><b>Atomic Updates.</b> Writes to an SQLite database are atomic. They either happen completely or not at all, even in the face of system crashes or power failures. So there is no danger of corrupting a document just because the power happened to go out at the same instant that a change was being written to disk. <p><b>Incremental Updates.</b> When writing to an SQLite database file, only those parts of the file that actually change are written out to disk. This makes the writing happen faster and saves wear on SSDs. This is an enormous advantage of SQLite over custom and ZIP-ed pile-of-files formats, both of which must completely rewritten the entire document in order to change a single byte. Simple pile-of-files formats can also do incremental updates to some extent, though granularity of writes is larger with pile-of-file formats (a single file) than with SQLite (a single page). <p><b>Extensible.</b> As an application grows and evolves, new features can be added to an SQLite application file format simply by adding new tables to the schema or by adding new columns to existing tables. With a modicum of care, backwards compatibility of the application is preserved. It is also possible to create an extensible custom or pile-of-files format too, of course, but doing so requires more work and more design discipline. <p><b>Performance.</b> In many cases, an SQLite application file format can be faster than a custom or pile-of-files format. In the case of a custom format, SQLite often dramatically improves start-up times because instead of having to read and parse the entire document into memory, the application can do queries to extract only the information needed for the initial screen display. In the case of a pile-of-files format, it might be surprising, but SQLite can read and write smaller BLOBs from its database faster than they can be read or written as separate files. (See [Internal Versus External BLOBs] for further information.) <p><b>Safe For Concurrent Use By Multiple Processes.</b> SQLite automatically coordinates concurrent access to the same document from multiple threads and/or processes. Two or more applications can connect and read from the same document at the same time. Writes are serialized, but as writes normally only take milliseconds, usually applications simply take turns writing. SQLite automatically ensures that the low-level format of the document is uncorrupted. Accomplishing the same with a custom or pile-of-files format, in contrast, requires extensive support in the application. And the application code used to coordinate concurrent is a notoriously bug-magnet. <p><b>Works With Multiple Programming Languages.</b> Though SQLite is itself written in ANSI-C, interfaces exist for just about every other programming language you can think of: C++, C#, Objective-C, Java, Tcl, Perl, Python, Ruby, Erlang, JavaScript, and so forth. So programmers can develop in whatever language they are most comfortable with and which best matches the application. An SQLite application file format is a great choice in cases where there is a collection or "federation" of separate programs, often written in different langauges and by different development teams, that need to cooperate through the use of a common document format. <p><b>Built-In Support For Application Identification.</b> SQLite database file contain a 4-byte [Application ID] in their headers that can be set to an application-defined value and then used to identify the "type" of the file for utility programs such as [http://linux.die.net/man/1/file | file(1)]. <h2>Case Studies</h2> <h2>Summary</h2>