Page MenuHomePhabricator

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

Authored by kamil on Sep 15 2022, 5:37 AM.
Tags
None
Referenced Files
Unknown Object (File)
Sat, Nov 2, 11:40 PM
Unknown Object (File)
Sat, Nov 2, 11:40 PM
Unknown Object (File)
Sat, Nov 2, 11:40 PM
Unknown Object (File)
Sat, Nov 2, 11:39 PM
Unknown Object (File)
Sat, Nov 2, 11:38 PM
Unknown Object (File)
Oct 9 2024, 3:20 AM
Unknown Object (File)
Oct 8 2024, 1:16 AM
Unknown Object (File)
Oct 8 2024, 1:16 AM

Details

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. key and text fields are std::string and as a result they 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 drafts table we use asString method which will return string or throw error.
Test Plan
  1. Use decoded SQLite (e.g. comment encryption code and re-install app)
  2. Add some drafts
  3. Run this code and restart app to run migration
  4. Connect to database (path should be logged) by sqlite3 and run .schema drafts to check if the constraint is added.
  5. Check if created drafts are correctly saved.

Diff Detail

Repository
rCOMM Comm
Lint
Lint Not Applicable
Unit
Tests Not Applicable

Event Timeline

kamil held this revision as a draft.
kamil edited the test plan for this revision. (Show Details)
kamil added reviewers: atul, karol.
kamil published this revision for review.Sep 15 2022, 8:44 AM
tomek requested changes to this revision.Sep 16 2022, 10:35 AM
tomek added inline comments.
native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp
272 ↗(On Diff #16692)

Should we add a where clause to filter out possible null values? According to our logic, they should not be there, but the schema allows them. If, for some reason, there are such values, we would probably end up with failed insert (we can test the behavior).

This revision now requires changes to proceed.Sep 16 2022, 10:35 AM
native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp
272 ↗(On Diff #16692)

Good call, I've added manually NULL in text column to test and migration failed. Additional check added and should work now.

This revision is now accepted and ready to land.Sep 19 2022, 6:21 AM