SQLite Forum

Feature Request: Option to store sqlite db in plain text format (e.g. csv)
Login

Feature Request: Option to store sqlite db in plain text format (e.g. csv)

(1) By anonymous on 2020-03-22 10:59:05 [link] [source]

Since many developers are using sqlite as a dev database with many initial schema changes, it would be a good idea to have the option to store everything sqlite related as plain text [especially table schema and actual row values, other things can stay binary].

Many people would happily take the trade-off of more disk storage / slower execution time, for the ability to efficiently track the table in git and other version history tools, hence having the ability to revert back to any previous schema.

This would also offer totally new opportunities, since not only the schema can be tracked, but also every atomic row change, in case of very sensitive / precious data.

Of course this should just be optional, as the binary format is still ideal in any production app / distributed env.

(2) By Stephan Beal (stephan) on 2020-03-22 11:06:02 in reply to 1 [link] [source]

sqlite's tracing API effectively enables that:

https://www.sqlite.org/c3ref/trace_v2.html

With the tracing API you can capture all SQL as it is processed.

(3) By anonymous on 2020-03-22 12:07:46 in reply to 1 [link] [source]

The SQLite command line shell includes a command which will convert a SQLite database into a file of SQL commands, and another command which will read a text file of SQL commands and execute them to create a database. This allows free conversion between SQL's binary format and a human-readable text format.

Both these commands can be scripted fro your operating system's command line.

For the conversion commands, see the .read and .dump commands of https://sqlite.org/cli.html .

For scripting from the OS see https://sqlite.org/cli.html#using_sqlite3_in_a_shell_script .

Using the above facilities, instead of including a SQLite database in your project's source code, you can instead include a human-readable text file of SQL commands, and add a command to convert this file into the database into your compilation stage. This does introduce a dependency on the sqlite3 command-line tool into your project, but if you can't depend on SQLite, what can you depend on ?

(4) By Richard Hipp (drh) on 2020-03-22 12:21:07 in reply to 3 [source]

See also the dbdump.c extension. That file implements the facility of the ".dump" command from the command-line shell in a portable C-library. You can build that C-code into your application and break the dependency on the sqlite3 command-line shell.

(6) By Stephan Beal (stephan) on 2020-03-24 11:22:23 in reply to 4 [link] [source]

See also the dbdump.c extension.

There's a typo in the docs:

The parameter types for the first mention of xCallback are swapped:

void (*xCallback)(void*, const char*),

The definition in DState, and the order described in the docs, is:

int (*xCallback)(const char*,void*);

(5) By Karl (kbilleter) on 2020-03-24 10:32:44 in reply to 1 [link] [source]

If using git, setting up something like:

$GIT_DIR/info/attributes:

*.db diff=sqlite

and in git config an entry such as

[diff "sqlite"]
    textconv = echo .dump  | sqlite3

can be useful