Feature: #103578 - Add database default value support for TEXT, BLOB and JSON field types
See forge#103578
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_
, 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
CREATE TABLE `tx_myextension_domain_model_entity` (
`some_field` TEXT NOT NULL DEFAULT 'default-text',
`json_field` JSON NOT NULL DEFAULT '{}'
);
$connection = GeneralUtility::makeInstance(ConnectionPool::class)
->getConnectionByName(ConnectionPool::DEFAULT_NAME);
$connection->insert(
'tx_myextension_domain_model_entity',
[
'pid' => 123,
]
);
Advanced example with value quoting
CREATE TABLE a_textfield_test_table
(
# JSON object default value containing 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.