ext_tables.sql

ext_tables.sql

ext_tables.sql
Scope
extension
Path (Composer)
packages/my_extension/ext_tables.sql
Path (Classic)
typo3conf/ext/my_extension/ext_tables.sql

The ext_tables.sql file in the root folder of an extension holds additional SQL definition of database tables.

This file should contain a table-structure dump of the tables used by the extension which are not auto-generated. It is used for evaluation of the database structure and is applied to the database when an extension is enabled.

Table of contents

Adding additional fields to existing tables

If you add additional fields (or depend on certain fields) to existing tables you can also put them here. In this case insert a CREATE TABLE structure for that table, but remove all lines except the ones defining the fields you need. Here is an example adding a column to the pages table:

CREATE TABLE pages (
    tx_myextension_field int(11) DEFAULT '0' NOT NULL,
);
Copied!

TYPO3 will merge this table definition to the existing table definition when comparing expected and actual table definitions (for example, via the Admin Tools > Maintenance > Analyze Database Structure or the CLI command extension:setup. Partial definitions can also contain indexes and other directives. They can also change existing table fields - but that is not recommended, because it may create problems with the TYPO3 Core and/or other extensions.

The ext_tables.sql file may not necessarily be "dumpable" directly to a database (because of the semi-complete table definitions allowed that define only required fields). But the extension manager or admin tools can handle this.

TYPO3 parses ext_tables.sql files into a Doctrine DBAL object schema to define a virtual database scheme, enriched with \SchemaDefaultTcaSchema information for TCA-managed, auto-generated tables and fields.

Incorrect definitions may not be recognized by the TYPO3 SQL parser or may lead to SQL errors, when TYPO3 tries to apply them.

The ext_tables.sql file in TYPO3 contains SQL statements written in a TYPO3-specific format that is not directly valid for any database system. TYPO3 utilizes Doctrine DBAL to interpret and translate these statements into valid SQL for the specific target DBMS, such as MySQL, MariaDB, PostgreSQL, or SQLite.

Changed in version 13.4

Database types

The following database types require special consideration if you use them:

CHAR and BINARY as fixed length columns

Changed in version 13.4

Fixed and variable length variants have been parsed already in the past, but missed to flag the column as fixed for the fixed-length database field types CHAR and BINARY. This resulted in the wrong creation of these columns as VARCHAR and VARBINARY, which is now corrected.

Not all database systems (RDBMS) act the same way for fixed-length columns. Implementation differences need to be respected to ensure the same query/data behaviour across all supported database systems.

Fixed-length SQL type CHAR

Key Difference Between CHAR and VARCHAR

The main difference between CHAR and VARCHAR is how the database stores character data in a database. CHAR, which stands for character, is a fixed-length data type, meaning it always reserves a specific amount of storage space for each value, regardless of whether the actual data occupies that space entirely. For example, if a column is defined as CHAR(10) and the word apple is stored inside of it, it will still occupy 10 characters worth of space (not just 5). Unused characters are padded with extra spaces.

On the other hand, VARCHAR, short for variable character, is a variable-length data type. It only uses as much storage space as needed to store the actual data without padding. So, storing the word apple in a VARCHAR(10) column will only occupy 5 characters worth of space, leaving the remaining table row space available for other data.

When to use CHAR columns

Rule of thumb for fixed-length CHAR columns

  • Only use with ensured fixed-length values (so that no padding occurs).
  • For 255 or more characters VARCHAR or TEXT must be used.

Hints on using fixed-length CHAR columns

  • Ensure to write fixed-length values for CHAR (non-space characters), for example use hash algorithms which produce fixed-length hash identifier values.

  • Ensure to use query statements to trim OR rightPad the value within WHERE, HAVING or SELECT operations, when values are not guaranteed to contain fixed-length values.

  • Usage of CHAR must be avoided when using the column with the Extbase ORM, because fixed-value length cannot be ensured due to the lack of using trim/rightPad within the ORM generated queries. Only with ensured fixed-length values, it is usable with Extbase ORM.
  • Cover custom queries extensively with functional tests executed against all supported database platforms. Code within public extensions should ensure to test queries and their operations against all officially TYPO3-supported database platforms.

Extended examples about how to handle CHAR columns can be found in Important: #105310 - Create CHAR and BINARY as fixed-length columns

Auto-generated structure

The database schema analyzer automatically creates TYPO3 "management"-related database columns by reading a table's TCA and checking the Table properties (ctrl) section for table capabilities. Field definitions in ext_tables.sql take precedence over automatically generated fields, so the TYPO3 Core never overrides a manually specified column definition from an ext_tables.sql file.

These columns below are automatically added if not defined in ext_tables.sql for database tables that provide a $GLOBALS['TCA'] definition:

uid and PRIMARY KEY
If the uid field is not provided inside the ext_tables.sql file, the PRIMARY KEY constraint must be omitted, too.
pid and KEY parent
The column pid is unsigned, if the table is not workspace-aware, the default index parent includes pid and hidden as well as deleted, if the latter two are specified in TCA's Table properties (ctrl). The parent index creation is only applied, if the column pid is auto-generated, too.

The following $GLOBALS['TCA']['ctrl'] are considered for auto-generated fields, if they are not manually defined in the ext_tables.sql file:

['ctrl']['tstamp'] = 'my_field_name'
Often set to tstamp or updatedon.
['ctrl']['crdate'] = 'my_field_name'
Often set to crdate or createdon.
['ctrl']['delete'] = 'my_field_name'
Often set to deleted.
['ctrl']['enablecolumns']['disabled'] = 'my_field_name'
Often set to hidden or disabled.
['ctrl']['enablecolumns']['starttime'] = 'my_field_name'
Often set to starttime.
['ctrl']['enablecolumns']['endtime'] = 'my_field_name'
Often set to endtime.
['ctrl']['enablecolumns']['fe_group'] = 'my_field_name'
Often set to fe_group.
['ctrl']['sortby'] = 'my_field_name'
Often set to sorting.
['ctrl']['descriptionColumn'] = 'my_field_name'
Often set to description.
['ctrl']['editlock'] = 'my_field_name'
Often set to editlock.
['ctrl']['languageField'] = 'my_field_name'
Often set to sys_language_uid.
['ctrl']['transOrigPointerField'] = 'my_field_name'
Often set to l10n_parent.
['ctrl']['translationSource'] = 'my_field_name'
Often set to l10n_source.
l10n_state
Column added if ['ctrl']['languageField'] and ['ctrl']['transOrigPointerField'] are set.
['ctrl']['origUid'] = 'my_field_name'
Often set to t3_origuid.
['ctrl']['transOrigDiffSourceField'] = 'my_field_name'
Often set to l10n_diffsource.
['ctrl']['versioningWS'] = true and t3ver_* columns
Columns that make a table workspace-aware. All those fields are prefixed with t3ver_, for example t3ver_oid. A default index named t3ver_oid to fields t3ver_oid and t3ver_wsid is added, too.

The configuration in $GLOBALS['TCA'][$table]['columns'][$field]['config']['MM'] is considered for auto-generating the intermediate table and fields for:

The following types configured via $GLOBALS['TCA'][$table]['columns'][$field]['config'] are considered for auto-generated fields, if they are not manually defined in the ext_tables.sql file: