Hi, I would like to create a SQLite database of pre-computed values and ship it with my distributed executable for the sake of convenience/efficiency.

My thought is, I can create a function in a .c file with the database bytes stored in a char[] that will write the bytes to disk on first execution of the app. Then, the database will be available whenever the app is run in the future. So, the app will always have access to my pre-computed values.

A few questions on this:

1. how should I store the database in the .c file? For example, if I just read the raw bytes and store those in a char[], I think that will fail if endian-ness of the architecture changes? Maybe it would be better to store the database as SQL statements that can be executed to re-create the database?

2. If I am to store SQL statements in my .c file, how should I generate these? In the standalone SQLite executable, I can use the builtin function ".dump" to generate these statements, but how would I do this while using SQLite as a C library?

3. How should I write the database to disk? Does sqlite3_exec accept arbitrarily long strings as its argument for SQL statements? Or, is there another way that would be more convenient to re-create the database from the app (on disk), after it is compiled with the database built in?

Use the backup API.

Thanks, but I don't understand exactly how I can use that for my use-case.

It seems that the backup API is helpful for creating a backup file, but how should I store that in a .c file for compilation into a distributable app?

I would like to create a SQLite database of pre-computed values and ship it with my distributed executable...

The "append" VFS can do the job for you, appending a db directly to the binary, but my memory of how to use it has long since faded so we'll need to wait on someone else for an example of how to apply it.

Indeed, this looks like a great option.

If anyone else does want to chime in with how to use it, I am specifically wondering, how can I make an executable (from C source) use an appended database?

That is, how can I use the C interface to open a database that has previously been appended to an executable?

For actually appending the database, I can use the CLI, as larrybr suggests below. Thanks!

how can I use the C interface to open a database that has previously been appended to an executable?

In my example, I used the URI file specifier for the DB. This can also be done from C code, provided that the append VFS has been made known to the SQLite3 library via sqlite3_vfs_register(...) before the sqlite3_open call.

One big issue to watch out for, is that for security, executables are often made no-writable by normal processes, so while you might have a read-only database in the executable, it should not be a database that is written to.

Questions answered in order posed.

1. how should I store ...

You could store the SQL DDL and DML statements necessary to create the DB in your C source. I would use an array of C-string literals for this, one per statement.

Or, you could use a const char array, initialized with the content of a pre-built database. This will not pose endian woes because SQLite3 DB files are created and modified in a platform-independent manner.

2. how should I generate [SQL statements to be stored]?

See the CLI's .dump command.

3. How should I write the database to disk? ... long strings ...?

I would do as stated in answer to #1. This would promote more sane version control and obviate the worry over long strings.

Another option is to forget about getting the DB into the C source and instead modify your build process to use the append VFS, as Stephan suggested, to use a database that is simply appended to your executable. Then, either the backup API or a "VACUUM INTO ..." statement can be used to create a copy of the appended DB at will if needed. If the DB need not be modified, it can be opened read-only in its place past the end of your executable. It could be modified there if that does not present permission problems.

You can peruse the SQLite project's (generated) shell.c to see how the append VFS is used in C code.

As a non-C example of using the append VFS, consider this: #! /usr/bin/bash sqlite3 << END_HERE .open file:$0?vfs=apndvfs CREATE TABLE IF NOT EXISTS SomeStuff(id INTEGER PRIMARY KEY, stuff TEXT); .schema SELECT * FROM SomeStuff; END_HERE exit # DB follows.

Will a self-extracting ZIP file deliver what your are after?

... ship it with my distributed executable ...

ZIP files are your friend! The zip file format puts the table of contents at the end of the file, and the vast majority of zip libraries interpret offsets relative to the table of contents. (There are zip libraries on most platforms and for almost all programming languages.)

That means you can just append a zip file to the end of your executable, and then open the executable from your code using the zip library and it will behave like a normal zip file. This makes it easy to have more files, and you get compression for free.

If your product is commercial it may also be worth using the ZIPVFS from the SQLite team. I can't tell from the documentation if it supports zip files appended to other files, but it most likely does.