Is there a way to add a calculated ( generated ) column in the database of a typo3 extension ext_tables.sql
No, this is not possible. TYPO3 has a own SQL parser to build a virtual schema, supporting "part syntax" which gets merged. The language of the
ext_tables.sql files is a MySQL(/MariaDB) sub-set, and was mainly written
at a time generated columns did not exists.
I have it on my personal list to check if this can be implemented, but did
not looked into it yet. The parser part would be the easiest on this, but the next things would be if Doctrine DBAL supports that with the schema classes.
But the major point is, that we need a strategy how to deal with it for
- having a non MySQL/MariaDB backend like SQLite or PostgreSQL - can this be completly translated and works the same ? Guess this get hard when MySQL specific code is used not beeing available on the other platforms - even if generated columns are possible in general. For example
CONCAT()
- Check TYPO3 API (DataHandler, Backend toolings, Extbase) special on the persisting part as generated columns must not be send for INSERT/UPDATE commands.
And other points - cross database support is a thing here. At least it must be implemented in a way it can be used safely - special when used within TYPO3 itself then.
Another way would be to ensure that the calcualted value is persisted when the record changes, as DataHandler hook or within your controler when using QueryBuilder. For Extbase persisting there are PSR-14 which may be used.
That means, adding a simple "combined" value field, but do the calculation when changing one or both of the fields which needs to be calculated.
CREATE TABLE tx_solr_indexqueue_item (
...
`changed` int(11) DEFAULT '0' NOT NULL,
`indexed` int(11) DEFAULT '0' NOT NULL,
`delta` int(11) DEFAULT '0' NOT NULL,
INDEX `idx_delta` (`delta`),
When updating the index item, calculate the detla - for example on update using QueryBuilder:
$queryBuilder
->update('tx_solr_indexqueue_item')
->where(
$queryBuilder->expr()->eq(
'uid',
$queryBuiler->createNamedPlaceholder($uid, Connection::PARAM_INT),
),
)
->set(
'changed',
sprintf(
'%s + 1',
$queryBuilder->quoteIdentifier('changed')
),
false,
)
->set(
'delta',
sprintf(
'%s - %s',
$queryBuilder->quoteIdentifier('indexed'),
$queryBuilder->quoteIdentifier('changed'),
),
false,
)
->executeStatement();
If you persists exact values / from a full record simply do the calcualation on the PHP side
$indexed = $row['indexed'];
$changed = $row['changed'] + 1;
$delta = $indexed - $changed;
$queryBuilder
->update('tx_solr_indexqueue_item')
->where(
$queryBuilder->expr()->eq(
'uid',
$queryBuiler->createNamedPlaceholder($uid, Connection::PARAM_INT),
),
)
->set('changed', $changed)
->set('delta', $delta)
->executeStatement();
Direct value setting (last example) is adoptable to be used within a DataHandler hook (if total and/or changed is changed and delta not, calculate it and add it). If extbase models are used (which does not make much sense in my eyes for performance critical tables like queue items) set the calculated detla directly to the model. Or do a recalculation of delta within the setIndexed() and setChanged() method (extbase itself does not set values based on setters anyway so it can set the delta read from database without doing the recalculation).
On item creation (INSERT) you can calculate the values directly and persist it (delta) as the values are static in this case - at least if you proide them and not using db defaults. Counts for all techniques.
