Document limitation on not null columns?

[tylerbrandt]

Hi, can we please document this limitation on https://vlcn.io/cr-sqlite/constraints?

cr-sqlite/core/src/tableinfo.c

Lines 476 to 484 in e82ac95

	*errmsg = sqlite3_mprintf(
	"Table %s has a NOT NULL column without a DEFAULT VALUE. This "
	"is not "
	"allowed as it prevents forwards and backwards compatability "
	"between "
	"schema versions. Make the column nullable or assign a default "
	"value "
	"to it.",
	tblName);

I was unlucky to be testing this out in between when the "not null" constraint was relaxed in 5aebb64 and when it was re-imposed in 11208d0, and it was quite confusing when my change inserts were failing with this opaque error.

Failed running INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?) Error: Failed inserting changeset

I did eventually track it down to the not null columns, and it makes sense why the limitation exists: SQLite requires the database
"state" to be consistent after each operation is applied, not only when the transaction commits, and since each "change" might not specify a value for all the not null columns, you can't guarantee that invariant holds. But I think the explanation slightly misleads, because you can't even use not null columns in a newly created, empty table, which would otherwise be ok since there can't possibly be any data that needs to be migrated.

[tantaman]

Sorry about that. I'll be sure to announce any future breaking API changes.

But I think the explanation slightly misleads, because you can't even use not null columns in a newly created, empty table, which would otherwise be ok since there can't possibly be any data that needs to be migrated.

You're right. Will make some adjustments.

[tantaman]

Looks like I can enable two-phase commit on crsql_changes to be able to grab all updates made in a given transaction and aggregate them into full rows.

Hopefully this means we can get rid of this limitation in the near future.

[tylerbrandt]

Looks like I can enable two-phase commit on crsql_changes to be able to grab all updates made in a given transaction and aggregate them into full rows.

Hopefully this means we can get rid of this limitation in the near future.

That would be even better 😃

[tantaman]

I'm going to start by creating a separate virtual table for those that need to sync whole rows -- #294

This separate table will always return whole rows if any item in the row changed and always insert whole rows.