.. include:: /Includes.rst.txt .. index:: File; EXT:{extkey}/ext_tables.sql .. _ext-tables-sql: ================ `ext_tables.sql` ================ .. typo3:file:: ext_tables.sql :scope: extension :regex: /^.*ext\_tables\.sql$/ :shortDescription: Holds additional SQL definition of database tables. The :file:`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 :ref:`auto-generated `. It is used for evaluation of the database structure and is applied to the database when an extension is enabled. .. contents:: Table of contents :depth: 2 .. _ext-tables-sql-fields: 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 :sql:`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: .. code-block:: sql 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 :guilabel:`Admin Tools > Maintenance > Analyze Database Structure` or the :ref:`CLI ` command :bash:`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 :file:`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 :file:`ext_tables.sql` files into a Doctrine DBAL object schema to define a virtual database scheme, enriched with :php-short:`\TYPO3\CMS\Core\Database\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 :file:`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 :abbr:`DBMS (Database Management System)`, such as MySQL, MariaDB, PostgreSQL, or SQLite. .. versionchanged:: 13.4 Settings defined at the column level in :file:`ext_tables.sql` are respected for MySQL and MariaDB. This allows specifying different encodings or collations for individual columns. Use this carefully, as mixing collations may require special handling during queries. See also: `Important: #106508 - Respect column CHARACTER SET and COLLATE in ext_tables.sql `_ .. _ext-tables-sql-types: Database types ============== The following database types require special consideration if you use them: .. _ext-tables-sql-types-fixed-length: `CHAR` and `BINARY` as fixed length columns ------------------------------------------- .. versionchanged:: 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 :sql:`CHAR` and :sql:`BINARY`. This resulted in the wrong creation of these columns as :sql:`VARCHAR` and :sql:`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. .. warning:: Using fixed-length :sql:`CHAR` and :sql:`BINARY` column types requires to carefully work with data being persisted and retrieved from the database due to differently behaviour specifically of PostgreSQL. .. tip:: :sql:`CHAR` and :sql:`BINARY` columns can be used (for storage or performance adjustments), but only when composed data and queries take care of database-system differences. Otherwise, the "safe bet" is to consistently utilize :sql:`VARCHAR` and :sql:`VARBINARY` columns types. .. _ext-tables-sql-types-char: Fixed-length SQL type `CHAR` ---------------------------- .. _ext-tables-sql-types-char-varchar-difference: Key Difference Between CHAR and VARCHAR ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The main difference between :sql:`CHAR` and :sql:`VARCHAR` is how the database stores character data in a database. :sql:`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 :sql:`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, :sql:`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 :sql:`VARCHAR(10)` column will only occupy 5 characters worth of space, leaving the remaining table row space available for other data. .. _ext-tables-sql-types-char-when: When to use `CHAR` columns ~~~~~~~~~~~~~~~~~~~~~~~~~~~ **Rule of thumb for fixed-length** :sql:`CHAR` **columns** * Only use with **ensured fixed-length values** (so that no padding occurs). * For 255 or more characters :sql:`VARCHAR` or :sql:`TEXT` must be used. .. _ext-tables-sql-types-char-hints: Hints on using fixed-length `CHAR` columns ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Ensure to write fixed-length values for :sql:`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 :sql:`WHERE`, :sql:`HAVING` or :sql:`SELECT` operations, when values are not guaranteed to contain fixed-length values. .. tip:: Helper :php:`\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder` expressions can be used, for example :php-short:`\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder->trim()` or :php-short:`\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder->rightPad()` to. * Usage of :sql:`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-db-structure: Auto-generated structure ======================== The database schema analyzer automatically creates TYPO3 "management"-related database columns by reading a table's TCA and checking the :ref:`t3tca:ctrl` section for table capabilities. Field definitions in :file:`ext_tables.sql` take precedence over automatically generated fields, so the TYPO3 Core never overrides a manually specified column definition from an :file:`ext_tables.sql` file. .. note:: .. versionadded:: 13.0 As column definitions are created automatically from the TCA configuration, the :file:`ext_tables.sql` file can end up with a table definition without columns, like: .. code-block:: sql CREATE TABLE tx_myextension_domain_model_table ( ); This would be invalid as such in most :abbr:`DBMS (Database Management System)`, since tables usually must have at least one column. However, it is a valid definition in the scope of :file:`ext_tables.sql` files when the TYPO3 Core enriches fields from TCA. Also, you can omit the :sql:`CREATE TABLE` statement without columns entirely. These columns below are automatically added if not defined in :file:`ext_tables.sql` for database tables that provide a :php:`$GLOBALS['TCA']` definition: :sql:`uid` and :sql:`PRIMARY KEY` If the :sql:`uid` field is not provided inside the :file:`ext_tables.sql` file, the :sql:`PRIMARY KEY` constraint **must** be omitted, too. :sql:`pid` and :sql:`KEY parent` The column :sql:`pid` is :php:`unsigned`, if the table is not :doc:`workspace `-aware, the default index :sql:`parent` includes :sql:`pid` and :sql:`hidden` as well as :sql:`deleted`, if the latter two are specified in TCA's :ref:`t3tca:ctrl`. The parent index creation is only applied, if the column :sql:`pid` is auto-generated, too. The following :ref:`$GLOBALS['TCA']['ctrl'] ` are considered for auto-generated fields, if they are not manually defined in the :file:`ext_tables.sql` file: :php:`['ctrl']['tstamp'] = 'my_field_name'` Often set to :sql:`tstamp` or :sql:`updatedon`. :php:`['ctrl']['crdate'] = 'my_field_name'` Often set to :sql:`crdate` or :sql:`createdon`. :php:`['ctrl']['delete'] = 'my_field_name'` Often set to :sql:`deleted`. :php:`['ctrl']['enablecolumns']['disabled'] = 'my_field_name'` Often set to :sql:`hidden` or :sql:`disabled`. :php:`['ctrl']['enablecolumns']['starttime'] = 'my_field_name'` Often set to :sql:`starttime`. :php:`['ctrl']['enablecolumns']['endtime'] = 'my_field_name'` Often set to :sql:`endtime`. :php:`['ctrl']['enablecolumns']['fe_group'] = 'my_field_name'` Often set to :sql:`fe_group`. :php:`['ctrl']['sortby'] = 'my_field_name'` Often set to :sql:`sorting`. :php:`['ctrl']['descriptionColumn'] = 'my_field_name'` Often set to :sql:`description`. :php:`['ctrl']['editlock'] = 'my_field_name'` Often set to :sql:`editlock`. :php:`['ctrl']['languageField'] = 'my_field_name'` Often set to :sql:`sys_language_uid`. :php:`['ctrl']['transOrigPointerField'] = 'my_field_name'` Often set to :sql:`l10n_parent`. :php:`['ctrl']['translationSource'] = 'my_field_name'` Often set to :sql:`l10n_source`. :sql:`l10n_state` Column added if :php:`['ctrl']['languageField']` and :php:`['ctrl']['transOrigPointerField']` are set. :php:`['ctrl']['origUid'] = 'my_field_name'` Often set to :sql:`t3_origuid`. :php:`['ctrl']['transOrigDiffSourceField'] = 'my_field_name'` Often set to :sql:`l10n_diffsource`. :php:`['ctrl']['versioningWS'] = true` and :sql:`t3ver_*` columns Columns that make a table :doc:`workspace `-aware. All those fields are prefixed with :sql:`t3ver_`, for example :sql:`t3ver_oid`. A default index named :sql:`t3ver_oid` to fields :sql:`t3ver_oid` and :sql:`t3ver_wsid` is added, too. The configuration in :ref:`$GLOBALS['TCA'][$table]['columns'][$field]['config']['MM'] ` is considered for auto-generating the intermediate table and fields for: * :ref:`TCA type "group" ` * :ref:`TCA type "inline" ` * :ref:`TCA type "select" ` The following types configured via :ref:`$GLOBALS['TCA'][$table]['columns'][$field]['config'] ` are considered for auto-generated fields, if they are not manually defined in the :file:`ext_tables.sql` file: * :ref:`TCA type "category" ` (since TYPO3 v12.0) * :ref:`TCA type "check" ` (since TYPO3 v13.0) * :ref:`TCA type "color" ` (since TYPO3 v13.0) * :ref:`TCA type "datetime" ` * :ref:`TCA type "email" ` (since TYPO3 v13.0) * :ref:`TCA type "file" ` (since TYPO3 v13.0) * :ref:`TCA type "flex" ` (since TYPO3 v13.0) * :ref:`TCA type "folder" ` (since TYPO3 v13.0) * :ref:`TCA type "group" ` (since TYPO3 v13.0) * :ref:`TCA type "imageManipulation" ` (since TYPO3 v13.0) * :ref:`TCA type "inline" ` (since TYPO3 v13.0) * :ref:`TCA type "json" ` * :ref:`TCA type "language" ` (since TYPO3 v13.0) * :ref:`TCA type "link" ` (since TYPO3 v13.0) * :ref:`TCA type "number" ` (since TYPO3 v13.0) * :ref:`TCA type "password" ` (since TYPO3 v13.0) * :ref:`TCA type "radio" ` (since TYPO3 v13.0) * :ref:`TCA type "select" ` (since TYPO3 v13.0) * :ref:`TCA type "slug" ` (since TYPO3 v12.0) * :ref:`TCA type "text" ` (since TYPO3 v13.0) * :ref:`TCA type "uuid" `