User admin API returns integers instead of booleans on SQLite, or the documentation is incorrect

[squahtx]

Similar to #13505.
Originally reported by @jplatte in #13509 (comment):

Description

The first example on https://matrix-org.github.io/synapse/develop/admin_api/user_admin_api.html shows this:

    "is_guest": 0,
    "admin": 0,
    "deactivated": 0,
    "shadow_banned": 0,

but some of these fields are mentioned as being bool further down.

[dklimpel]

IMO, in this case, it has not the reason in SQLite, but the database layout:

synapse/synapse/storage/schema/main/full_schemas/54/full.sql.postgres

Lines 1091 to 1102 in d4d3249

	CREATE TABLE users (
	name text,
	password_hash text,
	creation_ts bigint,
	admin smallint DEFAULT 0 NOT NULL,
	upgrade_ts bigint,
	is_guest smallint DEFAULT 0 NOT NULL,
	appservice_id text,
	consent_version text,
	consent_server_notice_sent text,
	user_type text
	);

synapse/synapse/storage/schema/main/delta/58/09shadow_ban.sql

Line 18 in 25f43fa

ALTER TABLE users ADD COLUMN shadow_banned BOOLEAN;

For some reason most of them are not boolean but smallint.

As a result, it is not consistent that rooms use booleanand users use int.

[DMRobertson]

I'd guess we chose smallint because it's something small that both sqlite and postgres support, unlike boolean.

[dklimpel]

I'd guess we chose smallint because it's something small that both sqlite and postgres support, unlike boolean.

Thats right, sqlite does not have a boolean. I am confused now if I see that (is_admin BOOLEAN):

synapse/synapse/storage/schema/main/full_schemas/54/full.sql.sqlite

Line 153 in d4d3249

CREATE TABLE group_users ( group_id TEXT NOT NULL, user_id TEXT NOT NULL, is_admin BOOLEAN NOT NULL, is_public BOOLEAN NOT NULL );

and (admin SMALLINT):

synapse/synapse/storage/schema/main/full_schemas/54/full.sql.sqlite

Line 6 in d4d3249

CREATE TABLE users( name TEXT, password_hash TEXT, creation_ts BIGINT, admin SMALLINT DEFAULT 0 NOT NULL, upgrade_ts BIGINT, is_guest SMALLINT DEFAULT 0 NOT NULL, appservice_id TEXT, consent_version TEXT, consent_server_notice_sent TEXT, user_type TEXT DEFAULT NULL, UNIQUE(name) );

And SQLite uses boolean in an example:

CREATE TABLE person(
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);

Presumably the inconsistent and non boolean values are then part of the past. Is it a task to convert all these columns to boolean? But ALTER TABLE is not easy in SQLite.

[clokep]

Presumably the inconsistent and non boolean values are then part of the past.

I believe old SQLites didn't support booleans and there might still be some issues using them directly in queries (if you pass them in as parameters than it works OK though).

Not sure how hard it would be to convert, but is likely doable.

[DMRobertson]

I believe old SQLites didn't support booleans and there might still be some issues using them directly in queries (if you pass them in as parameters than it works OK though).

My understanding, reading https://www.sqlite.org/datatype3.html#boolean_datatype and the preceeding section is:

• there is no dedicated boolean type in SQLite, though there are FALSE and TRUE literals as of 3.23.0.
• booleans are stored as integers by sqlite
• any column other than INTEGER PRIMARY KEY can contain values of any type ("storage class") whatsoever

And SQLite uses boolean in an example:

Given the last bullet above, I would guess that a) this is just a hint to the reader and b) BOOLEAN treated as a synonym for INTEGER by SQLite. (But I really am just guessing.)