How To Compile And Use CEROD

Introduction

This document describes an extended version of SQLite that is able to read compressed and encrypted database files.

License

The core SQLite library is in the public domain. However, the extensions needed to read compressed and encrypted database files are licensed software. You should only be able to see this software if you have a license. If you do not have a valid license you should delete the source code in this folder at once.

Your license is perpetual. You have paid a one-time fee that allows you to use and modify the software forever. You can ship as many copied of the software to your customers as you want so long as you insure that only compiled binaries are shipped (you cannot distribute source code) and that your customers cannot make additional copies of the software to use for other purposes.

You can create multiple products that use this software as long as all products are developed and maintained by the same team. For the purposes of this paragraph, a "team" is a work unit where everybody knows each others names. If you are in a large company where this product is used by multiple teams, then each team should acquire their own separate license.

Overview

This extended version of SQLite is able to read and write ordinary SQLite database files just like the public domain version. But this extended version also supports the ability to read compressed and encrypted databases. If your application has a large fixed data set and you are trying to squeeze the database onto a memory-limited PDA or other gadget, or trying to enable the software to run directly from a CD-ROM or DVD, then a compressed database can help. The amount of compression varies depending on the kind of data that is in your database, but we typically see values of 50-70%. Thus, for example, a read-only database that is 1.0 to 1.5 gibibytes in size should easily fit onto a single CD-ROM after being compressed.

Accessing A Compressed Database

To read a compressed and encrypted database, all you have to do is prepend a special prefix to the filename when you open the database file. The prefix is

  
    :cerod:PASSWORD:

Change the PASSWORD in the prefix to your encryption password. Use this prefix on the file name when you open the database using the sqlite3_open() API or when you ATTACH the database using the ATTACH SQL command. If your database is unencrypted, then just leave the PASSWORD blank so that the prefix becomes ":cerod::".

Once opened, the compressed database works like any other database file with the exception that it is read-only.

Creating A Compressed Database

To build a compressed database, first construct a normal read/write database containing the desired data. Then run the "cerod_build" command-line tool as follows:

   cerod_build UNCOMPRESSED COMPRESSED PASSWORD

The cerod_build command-line tool takes two or three arguments. The first argument is the name of the uncompressed database. The second is the name of the compressed database that the tool will created. The optional third argument is the encryption password or passphrase. If the password is omitted, the resulting database will still be compressed but it will be unencrypted.

For best results, run the VACUUM command on your uncompressed database prior to running the cerod_build utility.

The source code for the cerod_build command-line tool is contained in a single source file named "cerod_build.c". A typical command-line to build the cerod_build executable is:

   gcc -o cerod_build cerod_build.c -lz

On Windows with MSVC, the "buildmsvc.bat" script can be run to build both the cerod_build.exe command-line tool and the CEROD-enabled SQLite library.

Creating a compressed database from an SEE database

If your original database file is encrypted using the AES-128-OFB algorithm of the SQLite Encryption Extension (SEE) then it can be converted directly into a compressed (and encrypted) CEROD database without going through an intermediate uncompressed step. For this to work, the cerod_build utility program must have been compiled with the -DSQLITE_ENABLE_CEROD=2 option. Simply add a command-line option to the cerod_build program to specify the encryption key used by the SEE database file:

   cerod_build -see-key SEE-KEY SEE-DATABASE CEROD-DATABASE CEROD-KEY

Building The Library

This folder contains all the files you need to build the extended SQLite library with compressed file support and the cerod_build compression tool. The only external dependency is the zlib compression library. You can get a copy of zlib from http://www.zlib.net/

To build the SQLite library, simply append the "cerod.c" source file to the end of any recent version of the SQLite amalgamation source file. A copy of the SQLite amalgamation (sqlite3.c) is included in the ZIP archive with this README file. Or you can obtain a copy directly from the SQLite website: http://www.sqlite.org/download

After appending cerod.c to the end of the amalgamation, compile the amalgamation as you normally would, except add one of the following two command-line options to your compiler:

   -DSQLITE_ENABLE_CEROD=1
   -DSQLITE_ENABLE_CEROD=2

Use the first command-line option to enable RC4 encryption. Use the second to enable AES-128-OFB encryption. AES-128-OFB is recommended for all new development. The RC4 encryption method is provided only for historical compatibility.

Any program that links against SQLite library compiled above will also need to link against the zlib library.

To build the cerod_build command-line tool, simply compile the "cerod_build.c" source file. You will need to specify the same SQLITE_ENABLE_CEROD command-line option that was used to compile SQLite and link against zlib.

To build a version of the "sqlite.exe" command-line access tool that is able to read compressed databases, compile the "shell.c" source file and link it against both zlib and the SQLite library.

See the main SQLite website for addition information on how to compile SQLite. The key thing to remember is that compiling CEROD is same as compiling public-domain SQLite except that you need to add the -DSQLITE_ENABLE_CEROD=N compile-time option and you need to link against zlib.

Building Using Separate Source Files

It is recommended that CEROD be built by appending the "cerod.c" source file onto the end of the "sqlite3.c" amalgmation as described in the previous section. However, CEROD can also be compiled separately and linked against a common library build of SQLite. For example, "cerod.c" can be converted into a separate library file using commands like this:

   gcc -o cerod.o -O6 cerod.c
   ar cr libcerod.a cerod.o

After compile CEROD as shown above the above (and perhaps also then moving "libcerod.a" to the "/usr/local/lib" directory), your application can link against "-lcerod". CEROD can also be compiled as a shared library or DLL.

You can also statically link the "cerod.c" source file into your application together with any other C-language files you are using and then link against either a common library version of SQLite or add the public-domain SQLite amalgamation file ("sqlite3.c") to your application sources.

Enabling CEROD

In order to activate the ability to read compressed database files, programs that link against the extended SQLite library must invoke a special API routine prior to opening any database. Simply call

   {
      extern void sqlite3_activate_cerod(const char*);
      sqlite3_activate_cerod("7bb07b8d471d642e");
   }

The argument is your product activation key. The activation key is available as plain-text in the source code so you can clearly see what it is. The purpose of the activation key is to prevent one of your customers from extracting the SQLite library and using it separately from your application. Without knowledge of the activation key, which only you should know, your users will be unable to access the encryption and compression features.

You can invoke the sqlite3_activate_cerod() as the first line in your main() routine if you like. Or anywhere else that seem convenient. Just be sure to call this routine once prior to opening a database using sqlite3_open().

If you are running SQLite from a language other than C/C++ using an interface adaptor, the activation key can be entered using a pragma. Simply bring up a connection to another database (perhaps a temporary in-memory database) and then run the SQL command:

    PRAGMA activate_extensions('cerod-7bb07b8d471d642e');

After running this command, CEROD will be activated and you can close the tmporary database connection used to run the pragma and then open a new database connection to your compressed database file.

Once you have enabled the compression extension, to open a compressed database file you just prepend the ":cerod:PASSWORD:" prefix to the filename when you invoke sqlite3_open(), as described above. Everything else will be handled automatically for you.

Improving Performance Using Larger Page Sizes

Databases tend to give better compression and speed if you use a larger page size. The default page size in SQLite is 1024 bytes which has been found experimentally to give good performance for an unencrypted and uncompressed database on Unix. But for a compressed and encrypted database, larger page sizes tend to work better. To change the page size of a database, run the following commands from the SQLite command-line shell:

   PRAGMA page_size=8192;
   VACUUM;

You can experiment with various page sizes to see what gives the best results for your application. SQLite supports page sizes that are powers of between 512 and 65536.