summary refs log tree commit diff
path: root/docs/development/database_schema.md
diff options
context:
space:
mode:
authorRichard van der Hoff <1389908+richvdh@users.noreply.github.com>2021-06-11 17:13:56 +0100
committerGitHub <noreply@github.com>2021-06-11 17:13:56 +0100
commit13577aa55ebe6087e8b813c0643bbb53148e9510 (patch)
treed9244c52670e1677b7c547d1fac55f50c71294fe /docs/development/database_schema.md
parentMerge tag 'v1.36.0rc2' into develop (diff)
downloadsynapse-13577aa55ebe6087e8b813c0643bbb53148e9510.tar.xz
Notes on boolean columns in database schemas (#10164)
Diffstat (limited to 'docs/development/database_schema.md')
-rw-r--r--docs/development/database_schema.md42
1 files changed, 42 insertions, 0 deletions
diff --git a/docs/development/database_schema.md b/docs/development/database_schema.md
index 7fe8ec63e1..20740cf5ac 100644
--- a/docs/development/database_schema.md
+++ b/docs/development/database_schema.md
@@ -93,3 +93,45 @@ Ensure postgres is installed, then run:
 
 NB at the time of writing, this script predates the split into separate `state`/`main`
 databases so will require updates to handle that correctly.
+
+## Boolean columns
+
+Boolean columns require special treatment, since SQLite treats booleans the
+same as integers.
+
+There are three separate aspects to this:
+
+ * Any new boolean column must be added to the `BOOLEAN_COLUMNS` list in
+   `scripts/synapse_port_db`. This tells the port script to cast the integer
+   value from SQLite to a boolean before writing the value to the postgres
+   database.
+
+ * Before SQLite 3.23, `TRUE` and `FALSE` were not recognised as constants by
+   SQLite, and the `IS [NOT] TRUE`/`IS [NOT] FALSE` operators were not
+   supported. This makes it necessary to avoid using `TRUE` and `FALSE`
+   constants in SQL commands.
+
+   For example, to insert a `TRUE` value into the database, write:
+
+   ```python
+   txn.execute("INSERT INTO tbl(col) VALUES (?)", (True, ))
+   ```
+
+ * Default values for new boolean columns present a particular
+   difficulty. Generally it is best to create separate schema files for
+   Postgres and SQLite. For example:
+
+   ```sql
+   # in 00delta.sql.postgres:
+   ALTER TABLE tbl ADD COLUMN col BOOLEAN DEFAULT FALSE;
+   ```
+
+   ```sql
+   # in 00delta.sql.sqlite:
+   ALTER TABLE tbl ADD COLUMN col BOOLEAN DEFAULT 0;
+   ```
+
+   Note that there is a particularly insidious failure mode here: the Postgres
+   flavour will be accepted by SQLite 3.22, but will give a column whose
+   default value is the **string** `"FALSE"` - which, when cast back to a boolean
+   in Python, evaluates to `True`.