[3.0]: Add bool(ean) type to database

[jdarwood007]

We should support bools in our queries

			$request = Db::$db->query(
				'SELECT *
				FROM {db_prefix}myTable
				WHERE is_enabled = {bool:enabled}',
				[
					'enabled' => true,
				],
			);

The big question comes down to database support. We need to ensure that we either send a 1/0 in case the database type is a tinyint or send it as a real bool.

MySQL seems (at least in 8.0) that PMA sets the boolean as a tinyint(1), however running the query as is_eanbled = true seems to work.

Should we support this for table/column creating/updating?

@albertlast and @Oldiesmann any inputs from PostgreSQL?

[albertlast]

In postgres boolean is a own Datatyp and Numbers get rejected

[jdarwood007]

To add, MySQL bool data type is a alias to tinyint. It does support a bit datatype
https://stackoverflow.com/questions/289727/which-mysql-data-type-to-use-for-storing-boolean-values

@albertlast It sounds like if we did it, then we would just enforce a bool cast or error. I'm in favor of forcing it to be bool all the way until we handle it in the database logic itself.
There isn't any special handling from what I can read with Postgresql, but it looks like they return as t/f. Which is not standard in my experience from various languages I've worked with, so bringing it back out would require some logic on reading to handle.
https://www.postgresql.org/docs/current/datatype-boolean.html

[albertlast]

Well MySQL allow to write any number for exp 2 in a boolean field, dunno in which language is that Common?
So mostly the issue ist MySQL didn't have any boolean, Something similiar is ip Datatyp where we do stuff do workaround the issue in MySQL.

[Sesquipedalian]

The problem is that if we save a boolean to the database, we won't consistently get back a boolean upon retrieval. PostgreSQL would give us back a boolean, but MySQL would give us back an integer. PHP's loose typing might let us get away with that much of the time, but sooner or later the inconsistency would cause problems.

[jdarwood007]

Actually, for MySQL, how it handles it makes it fairly easy. A simple !empty($row['is_enabled']) works for "0", 0, "False", False, "", null (and some more) values returning true on the empty check and false for anything else. Whereas if you ask PHP empty("f"), then it would return false. Unless I am misreading how PostgreSQL would return the information on a select.