SQLite Forum

Reading/writing record format

Reading/writing record format

(1) By anonymous on 2020-03-20 21:33:58 [link] [source]

In order to avoid duplicating the code to read/write the record format (used in the database file) if the program uses it, I can suggest to add some API to do this. One possibility would be new pragmas (with the number of columns as its argument) which prepare a VDBE program involving Variable and OpenPseudo and Column (to extract data) or involving Variable and MakeRecord (to make the record format). (The reason I suggested pragmas is because I looked at the source code; this is implemented in vdbe.c, and I am guessing this would probably require the fewest changes to the rest of the code and won't interfere with it, as far as I can tell. However, if extracting data, I think it would need to ensure that ?1 is actually a blob, possibly by using Cast and HaltIfNull.) Or possibly a different way than pragmas is better, I don't know.

(2) By Simon Slavin (slavin) on 2020-03-21 13:47:05 in reply to 1 [link] [source]

What are you trying to do ? Are you trying to create your own VDBE programs ? Or write your own VDBE programs without doing it via SQL commands ? Or something else ?

(3) By anonymous on 2020-03-22 04:30:37 in reply to 2 [link] [source]

I thought I already explained, although maybe I wasn't clear enough.

What I want is to read/write the record format (as used in the file). I have written the code to do this before, but then it must be duplicated, which is inefficient, rather than using the function included in SQLite.

Although I described how the VDBE program would be done in order to implement this, it does not require that the ability to write VDBE programs is exposed to application programs (there is a valid reason why it isn't, anyways).

(4) By Stephan Beal (stephan) on 2020-03-22 04:46:26 in reply to 3 [source]

The sqlite3 on-disk record format is not only opaque to applications, but is subject to change in any given release, barring any backward compatibility constraints of doing so. If the format were publicized, sqlite3 would, for compatibility reasons, be constrained to never modify it, which would completely rule out, or greatly complicate, any number of future improvements.

OpenDocument is a public, well-defined on-disk format which can be relied upon at the application level. sqlite3 databases are not.

(5) By anonymous on 2020-03-22 05:02:49 in reply to 4 [link] [source]

The sqlite3 on-disk record format is not only opaque to applications, but is subject to change in any given release...

I don't believe you. The on-disk format is well-defined. New features are sometimes added such as additional header fields, additional schema options, etc (and there is stuff such as WAL where the data may be in other files; some of these things may change the read version, write version, and/or schema format), but I think the format of the b-trees and records doesn't change. (Type numbers 10 and 11 are for internal use of SQLite only, and their meaning may change in versions of SQLite, and are not used in the database file. I don't know about future, but even if so, they would presumably not be used for existing types if they are made public.) (The way the file format works is also helpful when using writable schema mode, in order to avoid corrupting the database.)

Anyways, the reason I mainly wanted it (although other people may have different reasons for wanting it) is to be able to serialize/deserialize a single record of SQL values as a byte string, and not for touching the database file directly.

(6) By Stephan Beal (stephan) on 2020-03-22 05:10:10 in reply to 5 [link] [source]

I don't believe you.

The proof is in the API. If it was intended to be used by client-level code, there would be public APIs for doing so.

(7) By Richard Hipp (drh) on 2020-03-22 11:21:13 in reply to 4 [link] [source]

The on-disk file format is fixed and stable. It is well-defined at https://sqlite.org/fileformat.html. This is important. It means that SQLite version 3.31.1 can read and write a database created by 3.0.0. And SQLite version 3.0.0 can read and write a database created by 3.31.1, as long as the 3.31.1-created database does not contain any new features that were added since 3.0.0 was released.

Part of the test suite verifies that new releases of SQLite can understand legacy database files.

The parts that people often want access to that are subject to change from one release to the next are:

  • The byte code
  • The abstract syntax tree
  • The interface between the byte code engine and the b-tree layer

(8) By Richard Hipp (drh) on 2020-03-22 11:29:32 in reply to 3 [link] [source]

... the function included in SQLite.

The record encoder/decoder is one of the most performance critical parts of SQLite. Profiling shows that SQLite spends a large percentage of the CPU cycles it uses encoding and decoding records. For that reason, the record encoder/decoder is highly, highly optimized. And part of that optimization is keeping the code in-line. That code is not contained in a separate function. Moving that code into a separate function that could be called externally would result in a measurable drop in performance.

(9) By anonymous on 2020-03-22 16:37:31 in reply to 8 [link] [source]

Yes, I know it is not a separate function (because I checked); by "the function included in SQLite" I did not mean "function" in that way. (Perhaps I should not write so unclearly.)

That is why I did not suggest moving it to a separate function that can be called externally, but instead defining a new SQL pragma for this purpose, since it requires changing nothing except adding a new pragma, and therefore should not affect performance.

(10) By Keith Medcalf (kmedcalf) on 2020-03-22 20:23:45 in reply to 3 [link] [source]

So your answer to "why do you want to read/write the SQLite3 record format" is because "I want to read/write the SQLite3 record format" and I wrote code to do that but it has (various reasons why you want someone else to do the work).

This is called a circular answer. I want to blow my nose because I want to blow my nose. I don't want to blow my nose because it is runny and dripping and plugged or whatnot, but rather simply just because, you know, the whim came over me when I was drinking a coke. There is really no reason whatsoever for blowing my nose, I just want to because I saw someone else blow their nose once and I would like to do it as well because, well, you know, just because I feel like it.