vsql_boolean extension for MySQL

MySQL has no boolean. BOOL is an alias for TINYINT(1), which accepts 5 and -1 as happily as 1, and gives them back unchanged. vsql_boolean adds a STRICTBOOL column type that stores one byte and holds two values.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-boolean
License	GPL-2.0

Install

vsql_boolean.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_boolean;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_boolean';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_boolean   |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

The STRICTBOOL type

A STRICTBOOL column takes the strings 'true'/'false', 't'/'f', 'yes'/'no', 'on'/'off' and '1'/'0', in any letter case. Pass them as strings: a bare number is refused. It reads back as true or false whichever form went in, and it occupies one byte.

CREATE TABLE flags (
    id     INT PRIMARY KEY,
    name   VARCHAR(16),
    active STRICTBOOL
);

Functions

Function	Returns	What it does
`boolean_to_int(b)`	int	1 for true, 0 for false
`boolean_sum(b)`	int	Aggregate: how many rows in the group are true
`boolean_avg(b)`	real	Aggregate: the fraction that are true, ignoring NULLs

Example

INSERT INTO flags VALUES
    (1, 'alpha', 'true'),
    (2, 'beta',  'no'),
    (3, 'gamma', 'on'),
    (4, 'delta', '0');

SELECT name, active FROM flags;

+-------+--------+
| name  | active |
+-------+--------+
| alpha | true   |
| beta  | false  |
| gamma | true   |
| delta | false  |
+-------+--------+

Four spellings went in, and two values came back out. Count them:

SELECT boolean_sum(active) AS enabled, ROUND(boolean_avg(active), 2) AS fraction
FROM flags;

+---------+----------+
| enabled | fraction |
+---------+----------+
|       2 |      0.5 |
+---------+----------+

Anything outside the accepted spellings is refused at insert time: INSERT INTO flags VALUES (5, 'eps', 'maybe') fails with ERROR 1366 (HY000): Incorrect STRICTBOOL value: 'maybe' for column 'active' at row 1. So does an unquoted 1, with ERROR 3219 (HY000): Incorrect STRICTBOOL value: '1' for column 'active' at row 1.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

The STRICTBOOL type

Functions

Example

See also

​Install

​What it adds

​The STRICTBOOL type

​Functions

​Example

​See also

Install

What it adds

The STRICTBOOL type

Functions

Example

See also