Project

General

Profile

Actions
Copy link

Feature #81234

closed

TCA Schema Service

Added by Armin Vieweg almost 7 years ago. Updated about 4 years ago.

Status:
Closed
Priority:
Should have
Assignee:
Armin Vieweg
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2017-05-16
Due date:
% Done:

0%

Estimated time:
PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

Introducing new TCA Schema Service which creates system columns in database (like "uid" or "hidden", etc.) automatically, based on given TCA configurations.
The feature uses the tablesDefinitionIsBeingBuilt signal to modify expected SQL structure during database compare.

TCA Schema Service has these components:

  1. Slot for tablesDefinitionIsBeingBuilt signal
  2. TcaSqlBuilder used in Slot
  3. Rules defining additional SQL, based on TCA configuration
Currently these rules are existing:
  • Ctrl\BaseRule
  • Ctrl\EnableColumnsRule
  • Ctrl\LocalizationsRule
  • Ctrl\VersioningWorkspaceRule

They get currently registred hardcoded in \TYPO3\CMS\Core\DataHandling\TcaSchemaService\TcaSqlBuilder.
TODO: Possibility to extend TCA rules (maybe with a new Signal?)

Related issues 1 (0 open — 1 closed)

Related to TYPO3 Core - Feature #85160: Auto create management DB fields from TCA ctrlClosed2018-06-05

Actions
Actions
Copy link
 #1

Updated by Armin Vieweg almost 7 years ago

I've discovered the following inconsistencies in SQL schemas, which will pop up in database compare after
applying new TCA Schema Service:

Remove unused field

  • ALTER TABLE sys_file_reference CHANGE t3_origuid `zzz_deleted_t3_origuid` INT DEFAULT 0 NOT NULL

Change fields

  • ALTER TABLE pages CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE pages CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE pages CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_storage CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_file_storage CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_storage CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_storage CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_storage CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_storage CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_file CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_metadata CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_file_metadata CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_metadata CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_metadata CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_metadata CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_metadata CHANGE t3ver_label `t3ver_label` VARCHAR(255) DEFAULT '' NOT NULL
    • Current value: t3ver_label VARCHAR(30) DEFAULT '' NOT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_file_metadata CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_file_reference CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE sorting `sorting` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: sorting INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE hidden `hidden` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: hidden SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_reference CHANGE t3ver_label `t3ver_label` VARCHAR(255) DEFAULT '' NOT NULL
    • Current value: t3ver_label VARCHAR(30) DEFAULT '' NOT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_file_collection CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_file_collection CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE t3ver_label `t3ver_label` VARCHAR(255) DEFAULT '' NOT NULL
    • Current value: t3ver_label VARCHAR(30) DEFAULT '' NOT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_file_collection CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE l10n_diffsource `l10n_diffsource` MEDIUMBLOB NOT NULL
    • Current value: l10n_diffsource MEDIUMTEXT DEFAULT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_file_collection CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE hidden `hidden` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: hidden SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE starttime `starttime` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: starttime INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_file_collection CHANGE endtime `endtime` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: endtime INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_collection CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE t3ver_label `t3ver_label` VARCHAR(255) DEFAULT '' NOT NULL
    • Current value: t3ver_label VARCHAR(30) DEFAULT '' NOT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_collection CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE l10n_diffsource `l10n_diffsource` MEDIUMBLOB NOT NULL
    • Current value: l10n_diffsource MEDIUMTEXT DEFAULT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_collection CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE hidden `hidden` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: hidden SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE starttime `starttime` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: starttime INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_collection CHANGE endtime `endtime` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: endtime INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_history CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_category CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE hidden `hidden` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: hidden SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_category CHANGE t3ver_label `t3ver_label` VARCHAR(255) DEFAULT '' NOT NULL
    • Current value: t3ver_label VARCHAR(30) DEFAULT '' NOT NULL COLLATE utf8_unicode_ci
  • ALTER TABLE sys_category CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE pages_language_overlay CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE pages_language_overlay CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE pages_language_overlay CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE pages_language_overlay CHANGE sys_language_uid `sys_language_uid` INT DEFAULT 0 NOT NULL
    • Current value: sys_language_uid INT UNSIGNED DEFAULT 0 NOT NULL
  • ALTER TABLE pages_language_overlay CHANGE l18n_diffsource `l18n_diffsource` MEDIUMBLOB NOT NULL
    • Current value: l18n_diffsource MEDIUMBLOB DEFAULT NULL
  • ALTER TABLE sys_template CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_template CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_template CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE tt_content CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE tt_content CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE tt_content CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE tt_content CHANGE l18n_diffsource `l18n_diffsource` MEDIUMBLOB NOT NULL
    • Current value: l18n_diffsource MEDIUMBLOB DEFAULT NULL
  • ALTER TABLE backend_layout CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE backend_layout CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE backend_layout CHANGE t3_origuid `t3_origuid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: t3_origuid INT DEFAULT 0 NOT NULL
  • ALTER TABLE backend_layout CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE tx_extensionmanager_domain_model_extension CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_workspace CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_workspace CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_workspace CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_workspace CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_workspace_stage CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE sys_workspace_stage CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_workspace_stage CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_workspace_stage CHANGE deleted `deleted` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: deleted SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE index_config CHANGE uid `uid` INT UNSIGNED AUTO_INCREMENT NOT NULL
    • Current value: uid INT AUTO_INCREMENT NOT NULL
  • ALTER TABLE index_config CHANGE pid `pid` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: pid INT DEFAULT 0 NOT NULL
  • ALTER TABLE index_config CHANGE tstamp `tstamp` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: tstamp INT DEFAULT 0 NOT NULL
  • ALTER TABLE index_config CHANGE crdate `crdate` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: crdate INT DEFAULT 0 NOT NULL
  • ALTER TABLE index_config CHANGE cruser_id `cruser_id` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: cruser_id INT DEFAULT 0 NOT NULL
  • ALTER TABLE index_config CHANGE hidden `hidden` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: hidden SMALLINT DEFAULT 0 NOT NULL
  • ALTER TABLE index_config CHANGE starttime `starttime` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: starttime INT DEFAULT 0 NOT NULL
  • ALTER TABLE sys_note CHANGE sorting `sorting` INT UNSIGNED DEFAULT 0 NOT NULL
    • Current value: sorting INT DEFAULT 0 NOT NULL
Actions
Copy link
 #3

Updated by Susanne Moog about 6 years ago

  • Category set to Database API (Doctrine DBAL)
Actions
Copy link
 #4

Updated by Armin Vieweg almost 6 years ago

  • Related to Feature #85160: Auto create management DB fields from TCA ctrl added
Actions
Copy link
 #5

Updated by Benni Mack about 5 years ago

  • Status changed from New to Needs Feedback

Hey Armin,

Christian built this while adding SQLite support in v9, using exactly the hook you mentioned and adding native Doctrine statements. does that fit for what you suggested?

Actions
Copy link
 #6

Updated by Benni Mack about 4 years ago

  • Status changed from Needs Feedback to Closed

Will close this issue now due to lack of feedback. If I should re-open the issue, let me know.

Actions
Copy link
 #7

Updated by Armin Vieweg about 4 years ago

Well, it was the issue you asked me on Dev Days to work on. After I did the proof of concept, core team members told me that the concept of generating DB scheme based on TCA is dirty and should not be implemented.

A few month later, another implementation of this feature has been merged with core. Because of this, I stopped contributing to TYPO3 core at all. I don't like my time to get wasted, like this.

Actions
Copy link
 #8

Updated by Benni Mack about 4 years ago

Armin Vieweg wrote:

Well, it was the issue you asked me on Dev Days to work on. After I did the proof of concept, core team members told me that the concept of generating DB scheme based on TCA is dirty and should not be implemented.

A few month later, another implementation of this feature has been merged with core. Because of this, I stopped contributing to TYPO3 core at all. I don't like my time to get wasted, like this.

Armin, I wasn't aware (anymore) of this situation. And I'm very truly sorry about this, and want to apologize for the mistake I've done. This really breaks my heart to read this but also thanks for sharing, because that is and was the last thing I ever wanted for you (or anyone) to see! Really really sorry about this. I can fully understand that you don't want to waste your time on Core like that. I hope you can take my apology as sincere. I've made mistakes in the past, but I want to get better at this and want to learn from my mistakes in the past. However, "code-wise" this thing is solved, but I will take it into sincere consideration on how to not let this kind of situation happen again!

Actions
Copy link

Also available in: Atom PDF