ext_tables.sql
¶
-- optional
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.
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,
);
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. TYPO3 expects that all
table definitions in this file look like the ones produced by the
mysqldump
utility. Incorrect definitions may not be recognized
by the TYPO3 SQL parser or may lead to SQL errors, when TYPO3 tries
to apply them. If TYPO3 is not running on MySQL or a directly compatible
other DBMS like MariaDB, the system will
parse the file towards the target DBMS like PostgreSQL or SQLite.
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
andPRIMARY KEY
If the
uid
field is not provided inside theext_tables.sql
file, thePRIMARY KEY
constraint must be omitted, too.pid
andKEY parent
The column
pid
isunsigned
, if the table is not workspace-aware, the default indexparent
includespid
andhidden
as well asdeleted
, if the latter two are specified in TCA's Table properties (ctrl). The parent index creation is only applied, if the columnpid
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
orupdatedon
.['ctrl']['crdate'] = 'my_field_name'
Often set to
crdate
orcreatedon
.['ctrl']['delete'] = 'my_field_name'
Often set to
deleted
.['ctrl']['enablecolumns']['disabled'] = 'my_field_name'
Often set to
hidden
ordisabled
.['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
andt3ver_*
columnsColumns that make a table workspace-aware. All those fields are prefixed with
t3ver_
, for examplet3ver_oid
. A default index namedt3ver_oid
to fieldst3ver_oid
andt3ver_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:
TCA type "category" (since TYPO3 v12.0)
TCA type "slug" (since TYPO3 v12.0)