TYPO3 is centered around a RDB - a relational database. This database has historically been MySQL and until version 3.6.0 of TYPO3 MySQL calls were hardcoded into TYPO3.
Today you can use other databases thanks to a wrapper class in TYPO3, “t3lib_DB”. This class implements a simple database API plus mysql-wrapper function calls which gives us the following features:
Backwards compatibility with old extensions
Easy migration to database abstraction for old extensions
Offering the opportunity of applying a DBAL (DataBase Abstraction Layer) as an extension (thus offering connectivity to other databases / information sources)
A DBAL can simply implement storage in other RDBs
Or it could be a simulation of a RDB while actually storing information totally different, like in XML files.
Or you create a simulation of the “be_users” table while looking up information in LDAP instead.
Keeping a minimal overhead (in the range of 5%) for plain MySQL usage (which is probably what most TYPO3 based solutions is running anyway)
In other words; TYPO3 is optimized for MySQL but can perform with any information source for which someone will write a “driver”. Such drivers can easily be implemented as extensions thus offering other developers a chance to implement a full blown DBAL for TYPO3 in an extension - or for the local TYPO3 project this can offer improvised implementation of eg. XML database sources or whatever.
Despite TYPO3s API for database connectivity which allows you to store information in eg. XML files instead of MySQL there is still a basic principle in any case; for TYPO3 internally every “data source” is expected to work as a flat database table with a number of fields inside and upon which you can perform queries! In other words; The DBAL can hide the actual storage mode for TYPO3 totally but internally TYPO3 always expects to select, update, insert and delete the equivalent of database records stored in tables!
For the rest of this section I will refer to “tables”, “fields” and “records” as if the data storage truly is a Relational Database despite that it might be an XML file depending on your current DBAL.
When you want TYPO3 to manage a table for you there are certain requirements.
The table must be configured in the global array, $TCA (See “TYPO3 Core API” for details) - this will tell TYPO3 things like the table name, features you have configured, the fields of the table and how to render these in the backend, relations to other tables etc.
You must add at least these fields:
“uid” - an auto-incremented integer, PRIMARY key, for the table, containing the unique ID of the record in the table.
“pid” - an integer pointing to the “uid” of the page (record from “pages” table) to which the record belongs.
any other fields you like .... typically at least:
A title field holding the records title as seen in the backend
A tstamp field holding the last modification time of the record
A sorting order field if records are sorted manually
A “deleted” field which tells TYPO3 that the record is deleted (if set)
One table which has a special status is the “pages” table. This table is the backbone of TYPO3 as it provides the hierarchical page structure into which all other TYPO3 managed records are positioned.
You can understand the “pages” table as folders on a hard disc and all other records (configured in $TCA) as files which can belong to one of these folders. As a unique identification of any record, “pages” record or otherwise, the “uid” field contains an integer value. And for any record the “pid” field is like the “path” in the file system telling which “page” the record belongs to.
Thus records in the “pages” table has a “pid” value which points to their “parent page” - the page record they belong to.
If a page (or record from another table) is found in the “Root” they have the “pid” 0 (zero).
Only admin-users can access records in the root. Also records from tables can normally only be created on a real page or in the root (unless configured otherwise).
There are other tables in TYPO3 which are not subject to the uid/pid scheme as described above. But these tables are not possible to edit in TYPO3s standard interface (TCEforms/TCEmain). For instance such a table could be the “sys_log” table which is automatically written to each time you update something in TYPO3. Or the “be_sessions” table containing user sessions.
Generally:
If a table is configured in $TCA, then it must have the “uid” and “pid” fields as outlined above. Tables configured in $TCA can be edited in the backend of TYPO3 and organized in the page tree.
If a table is not configured in $TCA it means the table is “hidden” to the backend user - such tables are controlled by the application logic automatically in some way or another.
When you upgrade to newer versions of TYPO3 or any extension in TYPO3 the requirements to the database tables and fields might have changed. However TYPO3 handles this automatically. If a new field or table has been added or changed the Install Tool in TYPO3 will detect that and warn you. When you install extensions, you will be warned as a part of the process when the extension is installed. When you upgrade the TYPO3 core source code you will have to manually trigger the validation functions inside the Install Tool:
The step you should always take is to click the "COMPARE" link for "Update required tables" in the Install Tool. Now TYPO3 will search for the file "ext_tables.sql" in all installed extensions, add them together with the core requirements (t3lib/stddb/tables.sql) and take that as the complete expression of the database structure TYPO3 requires. Then TYPO3 will ask the database for the actual table / field structure and compare them. Any fields that has been added or changed will be shown and new tables can be created in the interface that pops up:
A single click on a button in the bottom of the screen will carry out these changes for you!
As you can also see you will be told if tables or fields are not used any more. You can also choose to delete those if you like but it is not vital for the system to function correctly.
If the database matches exactly with the combined requirements of core and extensions you will see this message:
The class that contains code for comparing SQL files with the database is "t3lib/class.t3lib_install.php".
Each extension might provide requirements for tables and/or fields in the database. This is done from the ext_tables.sql file. But the file is not (always) a valid SQL dump. In this case taken from the extension "TemplaVoila" you can see a full table definition at first. This can be piped to MySQL and a new table will be created.
But the second "CREATE TABLE" definition is incomplete. This is on purpose because it actually adds four new fields to the already existing table "tt_content".
When the Install Tool reads the "ext_tables.sql" files it will automatically read these four lines and add them to the previously defined requirements for the "tt_content" table.
#
# Table structure for table 'tx_templavoila_datastructure'
#
CREATE TABLE tx_templavoila_datastructure (
uid int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
pid int(11) unsigned DEFAULT '0' NOT NULL,
tstamp int(11) unsigned DEFAULT '0' NOT NULL,
crdate int(11) unsigned DEFAULT '0' NOT NULL,
cruser_id int(11) unsigned DEFAULT '0' NOT NULL,
deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
title varchar(60) DEFAULT '' NOT NULL,
dataprot mediumtext NOT NULL,
scope tinyint(4) unsigned DEFAULT '0' NOT NULL,
previewicon tinytext NOT NULL,
PRIMARY KEY (uid),
KEY parent (pid)
);
#
# Table structure for table 'tt_content'
#
CREATE TABLE tt_content (
tx_templavoila_ds varchar(100) DEFAULT '' NOT NULL,
tx_templavoila_to int(11) DEFAULT '0' NOT NULL,
tx_templavoila_flex mediumtext NOT NULL,
tx_templavoila_pito int(11) DEFAULT '0' NOT NULL
);
More information about the process of upgrading TYPO3 can be found in the document "Installing and Upgrading TYPO3".
