SQLite Forum

How to get binary output from sqlite_dbpage()?

How to get binary output from sqlite_dbpage()?

(1) By User123 on 2023-08-12 12:37:24 [source]

The SQLITE_DBPAGE Virtual Table documentation states:

The SQLITE_DBPAGE table allows an application to view or replace the raw binary content of each page of the database file. No attempt is made to interpret the content of the page. Content is returned byte-for-byte as it appears on disk.

This is misleading because if I do a .binary on and select data from sqlite_dbpage() on my database, nothing is output on the terminal except for the SQLite3 header.

So the question is how to get the raw binary content of each page byte-for-byte as it appears on disk from the sqlite shell using sqlite_dbpage()?

I've tried turning on binary output, to no effect.

(2) By anonymous on 2023-08-12 14:32:37 in reply to 1 [link] [source]

The shell doesn't do binary input or output. There are various SQL functions to convert blob values to/from textual representations (hex/unhex, base64, base85).

The .backup command lets you make a block-level copy of an entire database file.

The .binary command just turns off end-of-line translation (LF to CR+LF) if you're running on Windows.

(4) By User123 on 2023-08-14 13:00:39 in reply to 2 [link] [source]

The .binary command just turns off end-of-line translation (LF to CR+LF) if you're running on Windows.

Nowhere in the official SQLite documentation states this. If that is the official effect of using this command then the help message should explicitly state that.

.binary on|off Turn binary output on or off. Default OFF

Well in that case the .binary command is entirely misleading, it should be .eol or .crlf on|off for that effect.

From an end user's POV one would think .binary on would turn on actual binary output and not be a dummy function on non-Windows platforms.

(5) By Gunter Hick (gunter_hick) on 2023-08-14 13:42:38 in reply to 4 [link] [source]

The text file encoding differs between *ux and windows; the former expects LF and the latter expects CR+LF. Some programs automatically convert TEXT files by adding the CR whenever a LF is encountered (or remove the CR if CR+LF is encountered, depending on the transalation direction). This can be counterproductive for non-text (BINARY) files.

Thus the ubiquitous use of BINARY/TEXT flags and modes in applications that may interact with either type of OS, like file transfer utilities.

(6) By Rowan Worth (sqweek) on 2023-08-18 07:04:52 in reply to 4 [link] [source]

You're acting like SQLite invented this nomenclature -- or have you submitted the same complaint about python's open(..., 'wb')?

The default is to use text mode, which may convert 'n' characters to a platform-specific representation on writing and back on reading. Thus, when opening a binary file, you should append 'b' to the mode value to open the file in binary mode, which will improve portability. (Appending 'b' is useful even on systems that don’t treat binary and text files differently, where it serves as documentation.) See below for more possible values of mode.

Or perhaps ios::binary from C++?

I'm not necessarily saying it's a good term, just that for a significant portion of end-users "binary mode" is already well understood to be a dummy function outside of windows.

(3) By Rowan Worth (sqweek) on 2023-08-14 03:47:21 in reply to 1 [link] [source]

If you use .mode insert then the sqlite shell emits binary blobs as hex strings like:

INSERT INTO table VALUES(X'1234567890abcdef')

But for this job it may be simpler to write the 50 lines of C that would be required to dump the binary content directly.

(7) By Spindrift (spindrift) on 2023-08-18 10:49:37 in reply to 1 [link] [source]

Might one ask why you are trying to do this - what goal are you ultimately wanting to achieve?

Your request and subsequent frustrations make this feel very much like an XY problem.

There are, for example, many easy ways of inserting binary data from a file directly into a blob in the database (and vice versa) using the CLI.

This isn't what you have asked for, of course, but it is strange that you are trying to get the CLI to return raw disc content as binary data to you - it's not obvious what problem you think this is the best solution for.

(8) By User123 on 2023-08-19 07:58:35 in reply to 7 [link] [source]

Well the aim is to use the sqlite shell to backup the individual pages in a large sqlite db into a read only filesystem such as squashfs,

using inspiration from earlier threads Hot backup database in WAL mode by coping and Use SQLITE_DBPAGE to backup .

But it looks like after this thread was made .binary was made deprecated, which is a step backwards and doesn't solve my backup issue.

(9) By Stephan Beal (stephan) on 2023-08-19 08:11:18 in reply to 8 [link] [source]

But it looks like after this thread was made .binary was made deprecated

It was replaced with .crnl, which performs the same function. Use .crnl off to disable CR/NL output conversion on Windows.

(10.2) By Spindrift (spindrift) on 2023-08-19 08:18:15 edited from 10.1 in reply to 8 [link] [source]

Ah, so you want to only overwrite/update changed blocks (pages) and [my assumption follows] you don't have enough scratch storage to copy the whole database (followed by a filesystem level backup of changed blocks)?

Or are you just trying to backup the entire database but onto a write-once filesystem (much easier).

Thank you, much clearer problem (though I'm afraid I don't have a clever solution at the moment).

NB - I don't think .binary ever did what you want, or think it used to do.

(11) By anonymous on 2023-08-19 18:23:46 in reply to 8 [link] [source]

The approach is quite feasible, but the SQLite shell is the wrong tool for it. You'll have to write some code of your own.

Another old thread you might have missed: s3zip.

(12) By anonymous on 2023-08-21 07:04:12 in reply to 11 [link] [source]

It seems I have to contradict myself.

If your POSIX-ish system has /dev/fd/*, you can trick the SQLite shell into dumping binary page data, but it's ugly. "Emergency last resort" level ugly.

sqlite3 dbfile "select '' from sqlite_dbpage() where writefile('/dev/fd/1',data)<0;" | insert-stdin-consuming-command-here

Don't say I didn't warn you!

(13) By User123 on 2023-08-29 08:40:52 in reply to 12 [link] [source]

Yes that definitely looks like some ugly black art wizardry, that I am tempted to use. ;-)