Fix default value for boolean type in SQLite getting columns schema #18973

batopa · 2025-10-14T10:37:00Z

This PR fixes an issue with default value of boolean type columns for SQLite database.

Building a table with TableSchema for example:

$s = new TableSchema('examples');
$s->addColumn('field1', [
    'type' => 'boolean',
    'null' => true,
    'default' => true,
]);

will produce

CREATE TABLE "examples" ("field1" BOOLEAN DEFAULT TRUE)

Once the table was created reading the schema we obtain an inconsistency columns for default value.

$schema = new SchemaCollection($connection);
$result = $schema->describe('examples');
debug($result->getColumn('field1'));

output

########## DEBUG ##########
[
  'type' => 'boolean',
  'length' => null,
  'null' => true,
  'default' => 'TRUE',
  'comment' => null,
  'precision' => null
]
###########################

The default is a string 'TRUE' but I expect a boolean true instead.

The same happens defining default as INT, for example 0. The results in this case will be 'default' => '0'.

Side note

To change code just in one place I modified the protected function _defaultValue signature. I think that it shouldn't have serious impact but let me know in case.

dereuromark · 2025-10-14T11:06:59Z

tests/TestCase/Database/Schema/SqliteSchemaDialectTest.php

 author_id INT(11) NOT NULL,
 unique_id UNSIGNED INTEGER NOT NULL,
 published BOOLEAN DEFAULT 0,
+reviewed BOOLEAN DEFAULT TRUE,


Why do we need this? Wouldnt it suffice to just map the other ones to numeric 0 as well?

Or maybe we just switch to using TRUE/FALSE then explicitly (in the future)?

🤔 I think that it is a more complete test case because \Cake\Database\Schema\SqliteSchemaDialect::columnSql():

passing 'default' => true produces BOOLEAN DEFAULT TRUE

passing 'default' => false produces BOOLEAN DEFAULT FALSE

passing 'default' => 1 produces BOOLEAN DEFAULT 1

passing 'default' => 0 produces BOOLEAN DEFAULT 0

so you can deal with both cases.

Anyway if you think it is unnecessary I can remove it.

Based on the other code changes above I think it should for now always produce only 0 and 1.

For reference, SQLite doesn't have a dedicated boolean type

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

SQLite recognizes the keywords "TRUE" and "FALSE", as of version 3.23.0 (2018-04-02) but those keywords are really just alternative spellings for the integer literals 1 and 0 respectively.

https://sqlite.org/datatype3.html

Our other drivers use 0/1 in their schema reflection boolean logic, and it would be good to be consistent here.

src/Database/Schema/SqliteSchemaDialect.php

dereuromark · 2025-10-14T11:10:33Z

I guess the one fail is probably an invalid test (data):

1) Cake\Test\TestCase\Database\Schema\SqliteSchemaDialectTest::testConvertColumn with data set #3 ('BOOLEAN', ['boolean', null])
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
Array &0 [
    'comment' => null,
-    'default' => 'Default value',
+    'default' => false,
    'length' => null,
    'null' => true,
    'type' => 'boolean',
]

Since it is "not empty" I wonder if this should map to true instead?

dereuromark · 2025-10-14T11:12:21Z

src/Database/Schema/SqliteSchemaDialect.php

+     * @return string|int|bool|null
     */
-    protected function _defaultValue(string|int|null $default): string|int|null
+    protected function _defaultValue(string|int|null $default, ?string $type = null): string|int|bool|null


How about as bugfix for 5.x:

You dont change the signature and map it to 0/1

Then in 5.next:

We can adjust the internal protected method to always return true/false for booleans from now on.

This is a bit more BC safe for people and consistent in the output then.

Do you mean to leave the second parameter ?string $type = null and change the return value for type boolean always to int?

Minimal changeset that makes the code work, yes, for a bugfix release.

I don't think we need to worry too much about BC here. I doubt that there are userland extensions to the sqlite schema dialect.

I still think it would be cleaner if we had one single way of interpretation.

Our other drivers use 0/1 in their schema reflection boolean logic, and it would be good to be consistent here.

So its settled then.

Co-authored-by: Mark Scherer <[email protected]>

batopa · 2025-10-14T14:43:37Z

I guess the one fail is probably an invalid test (data):

1) Cake\Test\TestCase\Database\Schema\SqliteSchemaDialectTest::testConvertColumn with data set #3 ('BOOLEAN', ['boolean', null])
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
Array &0 [
    'comment' => null,
-    'default' => 'Default value',
+    'default' => false,
    'length' => null,
    'null' => true,
    'type' => 'boolean',
]

Since it is "not empty" I wonder if this should map to true instead?

Uhm... I'm not sure how to handle this situation.
The issue is that filter_var($default, FILTER_VALIDATE_BOOLEAN) return false trying to validate 'Default value'.
We could add the flag FILTER_NULL_ON_FAILURE to trace that the default is not valid and take a decision on top of that.

But what is the action to take in this scenario? SQLite allows to set any default i.e. CREATE table examples (field BOOLEAN DEFAULT not_a_boolean); won't throw any error.

Any suggestion?

dereuromark · 2025-10-14T15:05:16Z

But what is the action to take in this scenario? SQLite allows to set any default i.e. CREATE table examples (field BOOLEAN DEFAULT not_a_boolean); won't throw any error.

What does it convert it to? 0 or 1? whatever the real result, thats your answer IMO.

- Fixed failing tests - Aligned on int cast for boolean column default values. - Made validation stricter by not handling arbitrary string-integers

markstory · 2025-10-15T03:17:23Z

I pushed up a commit with my suggestions and fixed failing tests.

src/Database/Schema/SqliteSchemaDialect.php

Co-authored-by: ADmad <[email protected]>

Fix default value getting columns schema in sqlite

7f55ff0

dereuromark added this to the 5.2.9 milestone Oct 14, 2025

dereuromark added the defect label Oct 14, 2025

dereuromark reviewed Oct 14, 2025

View reviewed changes

src/Database/Schema/SqliteSchemaDialect.php Outdated Show resolved Hide resolved

dereuromark reviewed Oct 14, 2025

View reviewed changes

markstory self-assigned this Oct 14, 2025

fix as suggested

0eedde9

Co-authored-by: Mark Scherer <[email protected]>

Add my suggestions to 18973

df51f7a

- Fixed failing tests - Aligned on int cast for boolean column default values. - Made validation stricter by not handling arbitrary string-integers

ADmad reviewed Oct 15, 2025

View reviewed changes

src/Database/Schema/SqliteSchemaDialect.php Outdated Show resolved Hide resolved

update @return signature

ee5c599

Co-authored-by: ADmad <[email protected]>

ADmad added the needs squashing The pull request should be squashed before merging label Oct 15, 2025

dereuromark merged commit dd8e460 into cakephp:5.x Oct 15, 2025
15 checks passed

batopa deleted the fix/sqlite-boolean-default branch October 15, 2025 13:23

batopa mentioned this pull request Oct 17, 2025

Fix SQLite defaults for boolean type cakephp/migrations#922

Merged

Fix default value for boolean type in SQLite getting columns schema #18973

Fix default value for boolean type in SQLite getting columns schema #18973

Uh oh!

Conversation

batopa commented Oct 14, 2025

Uh oh!

dereuromark Oct 14, 2025

Choose a reason for hiding this comment

Uh oh!

dereuromark Oct 14, 2025

Choose a reason for hiding this comment

Uh oh!

batopa Oct 14, 2025

Choose a reason for hiding this comment

Uh oh!

dereuromark Oct 14, 2025

Choose a reason for hiding this comment

Uh oh!

markstory Oct 15, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

dereuromark commented Oct 14, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

dereuromark Oct 14, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

batopa Oct 14, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

dereuromark Oct 14, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

markstory Oct 14, 2025

Choose a reason for hiding this comment

Uh oh!

dereuromark Oct 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

batopa commented Oct 14, 2025

Uh oh!

dereuromark commented Oct 14, 2025

Uh oh!

markstory commented Oct 15, 2025

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

dereuromark commented Oct 14, 2025 •

edited

Loading

dereuromark Oct 14, 2025 •

edited

Loading

batopa Oct 14, 2025 •

edited

Loading

dereuromark Oct 14, 2025 •

edited

Loading

dereuromark Oct 15, 2025 •

edited

Loading