Can anyone clear up a point for me about sqlite3_deserialize()?

My assumptions:

• The buffer is an image of a db disk file, either read in from the file system or produced by sqlite3_serialize().
• A db file doesn't have any attached databases. Those are only per-connection at runtime.
• Any "main" alias assigned with sqlite3_dbconfig() only perists for the duration of a connection, and is not present in a db file.
• You can't deserialize into the "temp" schema.

Here's the onion:

Then, under what circumstances would the zSchema argument to sqlite3_deserialize() ever be anything other than "main"?

If I serialize an attached schema (attached as "patch" for the sake of discussion), I cannot deserialize into "patch", I have to use "main", unless I use the same connection that was used to do the serialization.

But, in that same situation, although I can use "patch", I can also use "main".

Backstory:

I'm wrapping the sqlite3_serialize() and sqlite3_deserialize() functions in C#, and everything is working. I think I can simplify the interface by not requiring client code to specify a schema name on deserialization, if "main" will work in every case. I've got about a dozen tests at this point, but I haven't been able to come up with a situation where "main" doesn't work.

If I serialize an attached schema (attached as "patch" for the sake of discussion), I cannot deserialize into "patch", I have to use "main", unless I use the same connection that was used to do the serialization.

Says who?

Using an unrelated connection, execute this SQL:

attach ':memory:' as patch;

Now you have an empty patch schema you can override with

sqlite3_deserialize(db,"patch",...);

You misunderstand.

int main(int argc, char** argv) {
  sqlite3* pDb;
  sqlite3_stmt* pStmt;
  size_t cb;

  int rc = sqlite3_open_v2(":memory:", &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_EXRESCODE, 0);
  sqlite3_exec(pDb, "ATTACH 'books.db' AS 'patch';", 0, 0, 0);

  /***** Get a serialization of 'patch' *****/
  unsigned char* buffer = sqlite3_serialize(pDb, "patch", &cb, 0);

  /***** deserializing into 'patch' on the same connection works *****/
  rc = sqlite3_deserialize(pDb, "patch", buffer, cb, cb, SQLITE_DESERIALIZE_READONLY);
  assert(rc == 0);

  /***** "main" also works *****/
  rc = sqlite3_deserialize(pDb, "main", buffer, cb, cb, SQLITE_DESERIALIZE_READONLY);
  assert(rc == 0);

  rc = sqlite3_close(pDb);
  rc = sqlite3_open_v2(":memory:", &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_EXRESCODE, 0);

  /***** deserializing into 'patch' from a different connection does not work *****/
  rc = sqlite3_deserialize(pDb, "patch", buffer, cb, cb, 0);
  assert(rc == 1);

  /***** deserializing into 'main' works *****/
  rc = sqlite3_deserialize(pDb, "main", buffer, cb, cb, 0);
  assert(rc == 0);
}

So again, my question is -- Are there any circumstances where "main" will not work?

Are there any circumstances where "main" will not work?

A potential one which comes to mind is if you use sqlite3_db_config() with SQLITE_DBCONFIG_MAINDBNAME to rename "main" to something else.

I mention that in my initial assumptions; that alias won't exist in a serialized image, will it?

that alias won't exist in a serialized image, will it?

Just confirmed with the project lead: such aliases are never persisted in any way.

The documentation doesn't say so, but it's wrong. (The sqlite3_serialize documentation does say so.)

It does now! Thank you for pointing it out.

You misunderstand.

int main(int argc, char** argv) {
  sqlite3* pDb;
  sqlite3_int64 cb;

  int rc = sqlite3_open_v2(":memory:", &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_EXRESCODE, 0);
  sqlite3_exec(pDb, "ATTACH 'books.db' AS 'patch';", 0, 0, 0);

  /***** Get a serialization of 'patch' *****/
  unsigned char* buffer = sqlite3_serialize(pDb, "patch", &cb, 0);

  /***** deserializing into 'patch' on the same connection works *****/
  rc = sqlite3_deserialize(pDb, "patch", buffer, cb, cb, SQLITE_DESERIALIZE_READONLY);
  assert(rc == 0);

  /***** "main" also works *****/
  rc = sqlite3_deserialize(pDb, "main", buffer, cb, cb, SQLITE_DESERIALIZE_READONLY);
  assert(rc == 0);

  rc = sqlite3_close(pDb);
  rc = sqlite3_open_v2(":memory:", &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_EXRESCODE, 0);

  sqlite3_exec(pDb, "ATTACH ':memory:' as patch;", 0, 0, 0);

  /***** deserializing into 'patch' from a different connection works *****/
  rc = sqlite3_deserialize(pDb, "patch", buffer, cb, cb, 0);
  assert(rc == 0);

  /***** deserializing into 'main' works *****/
  rc = sqlite3_deserialize(pDb, "main", buffer, cb, cb, 0);
  assert(rc == 0);
}

My question is: why would you want to write a limited wrapper that doesn't expose the full functionality of sqlite3_deserialize?

What functionality would be missing, exactly?

Deserialising into schemas other than main.

That's the whole point of this thread:

"main" always works (so far). There is only ever 1 (one) schema in a serialized image.

I would really appreciate it if you could demonstrate a case where "main" doesn't work.

When you want to have more than one of these open at the same time.

If I open a connection and attach 2 other databases, then serialize each schema (yielding 3 separate buffers), I can deserialize each buffer (yielding 3 different connections) by using "main". I can also deserialize each buffer in turn, using a single connection, by using "main".

Perhaps you could demonstrate with a short piece of code?

I can deserialize each buffer (yielding 3 different connections) by using "main".

But then they are in different connections and you can't run cross-schema queries.

I can also deserialize each buffer in turn, using a single connection, by using "main".

But then they are not available at the same time and you can't run cross-schema queries.

Well, clearly we're not talking about the same thing, which would be even more clear if you would post a short piece of code.

Suppose you have two snapshots of the same database taken at different times. You want to use SQL to analyse the differences between them, so you deserialise them into different schemas:

sqlite3_exec(db,"attach ':memory:' as older;",0,NULL,NULL);
sqlite3_deserialize(db,"older",olderdata,oldersize,oldersize,SQLITE_DESERIALIZE_READONLY);
sqlite3_exec(db,"attach ':memory:' as newer;",0,NULL,NULL);
sqlite3_deserialize(db,"newer",newerdata,newersize,newersize,SQLITE_DESERIALIZE_READONLY);

Now you can do things like count unchanged table rows:

select count(*) from
    (select * from older.data
     intersect select * from newer.data);

That's perfect. Thank you.