From 12c50d6f14e37e49e0e2d1a82a4e6e1e15d6b7d6 Mon Sep 17 00:00:00 2001
From: richvdh <richvdh@users.noreply.github.com>
Date: Fri, 11 Jun 2021 16:14:17 +0000
Subject: deploy: 13577aa55ebe6087e8b813c0643bbb53148e9510

---
 develop/development/database_schema.html | 36 ++++++++++++++++++++++++++++++++
 1 file changed, 36 insertions(+)

(limited to 'develop/development')

diff --git a/develop/development/database_schema.html b/develop/development/database_schema.html
index dccc6ecafa..8c7096a0b9 100644
--- a/develop/development/database_schema.html
+++ b/develop/development/database_schema.html
@@ -267,6 +267,42 @@ has had all background updates run.</p>
 </code></pre>
 <p>NB at the time of writing, this script predates the split into separate <code>state</code>/<code>main</code>
 databases so will require updates to handle that correctly.</p>
+<h2 id="boolean-columns"><a class="header" href="#boolean-columns">Boolean columns</a></h2>
+<p>Boolean columns require special treatment, since SQLite treats booleans the
+same as integers.</p>
+<p>There are three separate aspects to this:</p>
+<ul>
+<li>
+<p>Any new boolean column must be added to the <code>BOOLEAN_COLUMNS</code> list in
+<code>scripts/synapse_port_db</code>. This tells the port script to cast the integer
+value from SQLite to a boolean before writing the value to the postgres
+database.</p>
+</li>
+<li>
+<p>Before SQLite 3.23, <code>TRUE</code> and <code>FALSE</code> were not recognised as constants by
+SQLite, and the <code>IS [NOT] TRUE</code>/<code>IS [NOT] FALSE</code> operators were not
+supported. This makes it necessary to avoid using <code>TRUE</code> and <code>FALSE</code>
+constants in SQL commands.</p>
+<p>For example, to insert a <code>TRUE</code> value into the database, write:</p>
+<pre><code class="language-python">txn.execute(&quot;INSERT INTO tbl(col) VALUES (?)&quot;, (True, ))
+</code></pre>
+</li>
+<li>
+<p>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:</p>
+<pre><code class="language-sql"># in 00delta.sql.postgres:
+ALTER TABLE tbl ADD COLUMN col BOOLEAN DEFAULT FALSE;
+</code></pre>
+<pre><code class="language-sql"># in 00delta.sql.sqlite:
+ALTER TABLE tbl ADD COLUMN col BOOLEAN DEFAULT 0;
+</code></pre>
+<p>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 <strong>string</strong> <code>&quot;FALSE&quot;</code> - which, when cast back to a boolean
+in Python, evaluates to <code>True</code>.</p>
+</li>
+</ul>
 
                     </main>
 
-- 
cgit 1.5.1