SQLite Forum

State of backup destination database if backup is abandoned
Login

State of backup destination database if backup is abandoned

(1) By Michael Allman (msa) on 2021-12-08 18:03:56 [link] [source]

If sqlite3_backup_finish() is called before sqlite3_backup_step() returns SQLITE_DONE, are there any guarantees that can be made about the destination database?

For example, if the destination database had a table named "items" before the call to sqlite3_backup_init() and the backup is abandoned, does the destination table still have a table named "items" with the same contents as before? Does it matter whether any calls to sqlite3_backup_step() (none of which return SQLITE_DONE) have occurred?

What if both source and destination databases have tables named "items" with different contents?

What if both source and destination databases have tables named "items" with different schema?

What if the source database contains a table the destination database does not or vice-versa?

I appreciate the help.

Michael

(2.1) By Larry Brasfield (larrybr) on 2021-12-08 18:34:51 edited from 2.0 in reply to 1 [link] [source]

(Edited to note response is partial.)

On this backup API page, can be found an assertion: "SQLite holds a write transaction open on the destination database file for the duration of the backup operation." Later, on sqlite_3_backup_finish(): "If sqlite3_backup_step() has not yet returned SQLITE_DONE, then any active write-transaction on the destination database is rolled back."

Together, these facts mean that the destination will be logically unaffected given the 1st usage pattern you posit. And because this conclusion rests upon documented API behavior (to which guarantees attach), it too is guaranteed.

(4) By Michael Allman (msa) on 2021-12-08 19:53:51 in reply to 2.1 [link] [source]

Thank you, Larry. I should have mentioned I saw that in the documentation and wanted to validate my understanding of it. Seeking further clarification, the documentation page SQLite is Transactional states that

All changes within a single transaction in SQLite either occur completely or not at all

What I haven't found in the documentation is the definition of "change". I'm confident an "INSERT", "UPDATE" or "DELETE" constitute changes in this context. Also, the page on Transactions refer to "CREATE" and "DROP". However, I'm wondering if this applies to all manner of "CREATE" and "DROP" statements. And does it apply to "ALTER" statements as well?

One reason I ask is that in some RDBMS (such as MySQL and Oracle), schema DDL statements cannot be a part of an open transaction. Do you see documentation about this for SQLite?

Thank you.

(6) By Larry Brasfield (larrybr) on 2021-12-08 20:13:03 in reply to 4 [source]

One reason I ask is that in some RDBMS (such as MySQL and Oracle), schema DDL statements cannot be a part of an open transaction. Do you see documentation about this for SQLite?

In SQLite, (just as in PostgreSQL), DDL is done within transactions which are either implicit (for each statement not in a transaction) or explicitly begun and completed. DML and queries may accompany the DDL within a transaction.

I cannot cite where this is said in so many words, but such dramatic behavior as Oracle has (where DDL closes open transactions and begins another to be closed when the DDL statement finishes) would be carefully documented, and there is no such language anywhere in the SQLite docs. (Further, the Oracle behavior seems like something that would necessitate very klutzy work-arounds, something to be assiduously avoided when the PG model is so clearly superior.)

The docs on transactions, because they mention no qualification or limitation related to DDL, can be taken as a guarantee that DDL is fine in (SQLite) transactions.

(7) By Michael Allman (msa) on 2021-12-08 20:34:49 in reply to 6 [link] [source]

The docs on transactions, because they mention no qualification or limitation related to DDL, can be taken as a guarantee that DDL is fine in (SQLite) transactions.

Thank you, Larry!

(3) By Larry Brasfield (larrybr) on 2021-12-08 19:45:42 in reply to 1 [link] [source]

What if both source and destination databases have tables named "items" with different contents?
What if both source and destination databases have tables named "items" with different schema?
What if the source database contains a table the destination database does not or vice-versa?

When the backup API completes normally (and not as in your "abandoned" scenario), the destination DB is left in the same logical state as the source DB at the time a read transaction is begun on the source (during the first sqlite3_backeup_step() call.) This means identical schemas and table contents.

This can be inferred from three facts:
(1) The doc says, "The backup API copies the content of one database into another. It is useful either for creating backups of databases or for copying in-memory databases to or from persistent files.";
(2) It is called "the backup API", not "the try to blend databases API";
(3) There are words in the backup API docs about a resumable page copying process, which would make no sense if portions of pages representing row content were being blended into an inconsistent schema.

I'm torn between thinking the doc should be clearer on this and thinking the API name and "copies" claim say it all. I'm open to others' thoughts on this.

(5) By Michael Allman (msa) on 2021-12-08 19:56:49 in reply to 3 [link] [source]

FWIW, from what I've read, I think the fact that the destination database is logically identical to the source database on successful completion of backup is apparent. No "merging" has occurred.