HomePhabricator
Diffusion Comm 4d8cce305d5a

[native/sqlite] add `NOT NULL` constraint to `metadata` table

Description

[native/sqlite] add NOT NULL constraint to metadata table

Summary:
Motivation

  1. Purpose is to enhance DB schema and add NOT NULL everywhere where NULL is not expected since SQLite by default accepts null values. task
  2. Instead of running all migrations to create database even on a fresh install (task) there is possibility to infer model from sqlite_orm, while orm itself deduce where types are not nullish there will be inconsistency (orm will generate NOT NULL clause in places where it should be).

Implementation
There is no ALTER COLUMN or any more straightforward method to do it, this solution is suggested by SQLite docs: https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes.

Why this change

  1. data field is std::string and as a result it can not be assigned to null (it's not the same as empty string), so NULL should not appear in DB.
  2. In CommCoreModule while adding something do metadata table we use utf() method which should return c++ string.

Test Plan:

  1. Use decoded SQLite (e.g. comment encryption code and re-install app)
  2. Connect to database (path should be logged) by sqlite3 and check data in metadata table.
  3. Run this code and restart app to run migration
  4. Connect to database and run .schema metadata to check if the constraint is added and data is unchanged.

Reviewers: tomek, jon, atul, karol, marcin

Reviewed By: tomek

Subscribers: ashoat, atul, abosh

Differential Revision: https://phab.comm.dev/D5146

Details

Provenance
kamilAuthored on Sep 15 2022, 6:46 AM
Reviewer
tomek
Differential Revision
D5146: [native/sqlite] add `NOT NULL` constraint to `metadata` table
Parents
rCOMM9fb5c0c2f74c: [native/sqlite] add `NOT NULL` constraint to `drafts` table
Branches
Unknown
Tags
Unknown