ext_tables.sql
¶
-- optional
SQL definition of database tables.
This file should contain a table-structure dump of the tables used by the extension. 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 that 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_myext_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. Partial definitions can also contain indexes and other directives. They can also change existing table fields though 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 MySQL (because of the semi-complete table definitions allowed
defining only required fields). But the Extension Manager or Install Tool 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 MySQL errors, when TYPO3 tries
to apply them. If TYPO3 is not running on MySQL or directly compatible
other DBMS like MariaDB, the system will parse the file towards the
target DBMS like PostgreSQL.
Auto generated structure¶
The database schema analyzer automatically creates TYPO3 "management" related
database columns by reading a tables 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 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 uid field is not provided inside
ext_tables.sql
, thePRIMARY KEY
must be omitted, too.pid
andKEY parent
Column pid is
unsigned
if the table is not workspace aware, the default indexparent
includespid
andhidden
as well asdeleted
if the latter two are specified inTCA
Table properties (ctrl). The parent index creation is only applied if columnpid
is auto generated, too.['ctrl']['tstamp'] = 'fieldName'
Often set to
tstamp
orupdatedon
['ctrl']['crdate'] = 'fieldName'
Often set to
crdate
orcreatedon
['ctrl']['cruser_id'] = 'fieldName'
Often set to
cruser
orcreatedby
['ctrl']['delete'] = 'fieldName'
Often set to
deleted
['ctrl']['enablecolumns']['disabled'] = 'fieldName'
Often set to
hidden
ordisabled
['ctrl']['enablecolumns']['starttime'] = 'fieldName'
Often set to
starttime
['ctrl']['enablecolumns']['endtime'] = 'fieldName'
Often set to
endtime
['ctrl']['enablecolumns']['fe_group'] = 'fieldName'
Often set to
fe_group
['ctrl']['sortby'] = 'fieldName'
Often set to
sorting
['ctrl']['descriptionColumn'] = 'fieldName'
Often set to
description
['ctrl']['editlock'] = 'fieldName'
Often set to
editlock
['ctrl']['languageField'] = 'fieldName'
Often set to
sys_language_uid
['ctrl']['transOrigPointerField'] = 'fieldName'
Often set to
l10n_parent
['ctrl']['translationSource'] = 'fieldName'
Often set to
l10n_source
l10n_state
Column added if
languageField
andtransOrigPointerField
are set['ctrl']['origUid'] = 'fieldName'
Often set to
t3_origuid
['ctrl']['transOrigDiffSourceField'] = 'fieldName'
Often set to
l10n_diffsource
['ctrl']['versioningWS'] = true
-t3ver_*
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.