Feature: #103578 - Add database default value support for TEXT, BLOB and JSON field types¶

Description¶

Database default values for TEXT, JSON and BLOB fields could not be used in a cross-database, vendor-compatible manner, for example in ext_tables.sql, or as default database scheme generation for TCA-managed tables and types.

Direct default values are still unsupported, but since MySQL 8.0.13+ this is possible by using default value expressions, albeit in a slightly differing syntax.

Example¶

EXT:my_extension/ext_tables.sql

CREATE TABLE `tx_myextension_domain_model_entity` (
  `some_field` TEXT NOT NULL DEFAULT 'default-text',
  `json_field` JSON NOT NULL DEFAULT '{}'
);

Insert a new record using the defined default values

$connection = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getConnectionByName(ConnectionPool::DEFAULT_NAME);
$connection->insert(
    'tx_myextension_domain_model_entity',
    [
        'pid' => 123,
    ]
);

Advanced example with value quoting¶

EXT:my_extension/ext_tables.sql

CREATE TABLE a_textfield_test_table
(
    # JSON object default value containting single quote in json field
    field1 JSON NOT NULL DEFAULT '{"key1": "value1", "key2": 123, "key3": "value with a '' single quote"}',

    # JSON object default value containing double-quote in json field
    field2 JSON NOT NULL DEFAULT '{"key1": "value1", "key2": 123, "key3": "value with a \" double quote"}',
);

Impact¶

Database INSERT queries that do not provide values for fields with defined default values, and that do not use TCA-powered TYPO3 APIs, can now be used, and will receive default values defined at databaselevel. This also accounts for dedicated applications operating directly on the database table.

Note

TCA-unaware API will not consider different TCA or FormEngine default value overrides and settings. So it's good to provide the basic default both in TCA and at database level, if added manually.