[PostgreSQL] Issue error register new user

[caosdp-rs]

ErrorException
pg_query(): Query failed: ERROR: column "active" is of type smallint but expression is of type boolean
LINE 1: UPDATE "users" SET "active" = TRUE, "updated_at" = '2022-07-...

{PHP internal code} — CodeIgniter\Debug\Exceptions->errorHandler ( arguments )

SYSTEMPATH\Database\Postgre\Connection.php : 135 — pg_query()

SYSTEMPATH\Database\BaseConnection.php : 695 — CodeIgniter\Database\Postgre\Connection->execute ( arguments )

$sql
UPDATE "users" SET "active" = TRUE, "updated_at" = '2022-07-29 18:15:52'
WHERE "users"."id" IN ('1')

[MGatner]

We have some other discussions going about database boolean types; I can't find them right now on mobile but I'm fairly certain this is the same.

[kenjis]

This is because the column type is tinyint:

shield/src/Database/Migrations/2020-12-28-223112_create_auth_tables.php

Line 17 in 835bec6

'active' => ['type' => 'tinyint', 'constraint' => 1, 'null' => 0, 'default' => 0],

But the code set true:

shield/src/Models/UserModel.php

Line 210 in 835bec6

$user->active = true;

And

shield/src/Entities/User.php

Line 43 in 835bec6

'active' => 'boolean',

[kenjis]

Therefore $user->active is bool when reading, but we must set int when writing,
because of the current Entity behavior.

But It seems unreasonable to have to code like that.

[MGatner]

I still can't find the thread I've been looking for but here are some partially-related:

• Fix bug with handling boolean values in set() method CodeIgniter4#4907
• Extending entity [bool support in __set] CodeIgniter4#2563
• Bug: Entity::hasChanged() is unreliable for casts CodeIgniter4#5905 (comment)
• Bug: hasChanged() always returns true even if a field of boolean type is reassigned with the same value without change. CodeIgniter4#5678 (comment)

I think this is a framework issue. Many databases use 0/1 for false/true (even aliasing BOOL to TINYINT) so CodeIgniter gets away with vague type conversations on the inbound data. PostgreSQL seems to support real, distinct native booleans and strict mode won't translate between 0s and 1s.

[MGatner]

The fix for Shield might be to change the migration to use boolean instead of tinyint. It should not affect MySQL/SQLite because that will alias to tinyint anyways but it will allow the boolean inputs for PostgreSQL.

[kenjis]

to change the migration to use boolean instead of tinyint

It seems good idea. I guess at least this PostgreSQL error will be solved.

However, I believe that Entity needs to be modified for a fundamental solution to properly handle types.

[MGatner]

While Entity could work, my guess would be that this should be handled in the database layer, since it is a fundamental difference between database drivers. If we try to push it into Entity then our entity instances will need to have some understanding of the underlying store, which is a violation of the repository pattern.

[kenjis]

this should be handled in the database layer

It seems difficult. Because the database layer does not have column types
unless getting table metadata.

[MGatner]

It shouldn't need the column types, it just needs to know how it's database engine handles booleans. I think MySQL/SQLite3 will accept true and false even with strict mode; PostgreSQL would need to do whatever input conversion it requires, like:

foreach ($data as $i => $value) {
    if ($value === true) {
        $data[$i] = new RawSQL('true');
    }
...

That's obviously not the right code but hopefully you get my gist.

[MGatner]

Actually I think I just understood what you meant. We don't need column types for writing data but we would need it for reading data since MySQL (e.g.) will return 0/1 due to its storage as TINYINT. In other words this code won't work the same across different database drivers:

$user->active = true;
$id           = model(UserModel::class)->insert($user);
$user         = model(UserModel::class)->find($id);
$user->active = true;
var_dump($user->hasChanged());