Database Model
Pimcore tries to keep a clean and optimized database model for managing the data. Nevertheless, there are quite a lot of tables around and finding the correct data might be a bit challenging at the beginning.
Basically, there are two types of tables
- Default tables with are created during install - for all sorts of data like assets, documents logs, versions, ...
- Dynamically created tables during object data model configuration - mainly for all object related data.
Default Tables
These tables are created during Pimcore install and are always the same.
Table | Description |
---|---|
application_logs | Contains all application logs. For more information see Application Logger. Additionally, there might be application_logs_archive* tables for old logging entries. |
assets | Assets (Images, etc.), with system metadata |
assets_metadata | Additional user metadata (Metadata tab in the asset panel) |
classes | List of all object classes with ID and name. Everything else is stored in PHP configuration files. |
classificationstore_collectionrelations | Relation Collection - Group for Classification Store |
classificationstore_collections | Collections of Classification Store |
classificationstore_groups | Groups of Classification Store |
classificationstore_keys | Keys of Classification Store |
classificationstore_relations | Relation Group - Key for Classification Store |
classificationstore_stores | Stores of Classification Store |
custom_layouts | Definition of the custom layouts for object classes |
dependencies | Stores dependencies between elements such as objects, assets, documents |
documents | List of all documents, folders, links, hardlinks, emails and snippets of the document area with meta- and config-data, relations |
documents_editables | Editables of documents (data), in a serialized form |
documents_email | Extra config data |
documents_hardlink | Extra config data |
documents_link | Extra config data |
documents_page | Extra config data |
documents_printpage | Extra config data |
documents_snippet | Extra config data |
documents_translations | Relation between same documents for different languages |
edit_lock | Tracks which user opened which resource in the backend |
element_workflow_state | Keeps track of workflow state for all elements |
email_blocklist | Blocklist for eMail-addresses |
email_log | Log for sent emails |
glossary | Words to auto-link in texts. See Glossary |
http_error_log | HTTP error log |
lock_keys | Store for Locking functionality |
notes | Notes for elements |
notes_data | Additional data for notes |
objects | List of all objects with metadata like id, class name, path, parent, ... |
object_url_slugs | URL Slug data |
properties | Data from the properties tab |
quantityvalue_units | Available quantities for quantity value object data type |
recyclebin | Stores metadata of deleted elements |
redirects | Stores redirects |
schedule_tasks | Stores scheduled tasks |
search_backend_data | Stores the index for the backend search - is a InnoDb Table with fulltext capabilities |
sites | Stores sites |
tags | Stores available tags |
tags_assignment | Stores assignment of tags to elements |
tmp_store | Pimcore internal tmp store |
tracking_events | |
translations_admin | Backend translations |
translations_messages | Frontend translations |
tree_locks | Locks in the tree of Pimcore backend interface |
users | Backend users |
users_permission_definitions | List of globally assignable user permissions |
users_workspaces_asset | Stores user access permissions for asset folder |
users_workspaces_document | Stores user access permissions for document folders |
users_workspaces_object | Stores user access permissions for object folders |
uuids | stores Unique Identifiers - if enabled |
versions | List of object/asset/document versions. Actual data is serialized and written to disk |
Object Tables
These tables are created and modified dynamically during the configuration of the object data model. As a result, they look different on an every Pimcore installation depending on the data model.
Objects
As soon as a new object class is created in Pimcore, at least three tables are
added to the database. The tables have a numerical suffix, denoting the number
(id) of the object class: object_query_(id)
, object_relations_(id)
,
object_store_(id)
and an additional database view object_(id)
which is a combination of
object_query_(id)
and objects
.
Table / View | Description |
---|---|
object_(id) View | Database view joining object_query_(id) and objects table |
object_query_(id) Table | Use this table to retrieve data incl. inherited data. Data types with relations are usually stored in a serialized form here, too. Pimcore Object-Lists work with this table. |
object_relations_(id) Table | Contains data of fields with relations to objects, assets, etc. |
object_store_(id) Table | This is the main data storage table of an object class. It contains all "flat" data without any relations or external dependencies. |
objects Table | Contains an entry for each and every object in the system. The id field is an auto_increment and the source of the primary key for an object. Metadata about an object is stored in this table, too. |
When restore of query tables is necessary (for what ever reason) calling
DataObject\Concrete::disableDirtyDetection();
and saving all data objects of class will do the trick. When not disabling dirty detection, there might be data missing in query table.
Simple Data Field Types
Following is an overview of how different object data types are stored in the database. This overview might not be complete. This overview might be a useful starting point when querying object data with object lists.
Text
Table: object_store_(id)
Name | Data Type | Default | Comment |
---|---|---|---|
Input | varchar(255) | NULL | / |
Textarea | longtext | NULL | / |
wysiwyg | longtext | NULL | Text with HTML-tags |
password | varchar(255) | NULL | Password - as hash |
Number
Table: object_store_(id)
Name | Data Type | Default | Comment |
---|---|---|---|
Number | double/decimal(64,3) | NULL | Datatype depends on selected precision |
Slider | double | NULL | / |
Date
Table: object_store_(id)
Name | Data Type | Default | Comment |
---|---|---|---|
Date | bigint(20) | NULL | < 1970 = negative Timestamp |
Date & Time | bigint(20) | NULL | < 1970 = negative Timestamp |
Time | varchar(5) | NULL | String - e.g.: "12:00" |
Select
Table: object_store_(id)
Name | Data Type | Default | Comment |
---|---|---|---|
Select | varchar(255) | NULL | Selected value |
User | varchar(255) | NULL | Pimcore User-ID |
Country | varchar(255) | NULL | Country code |
Language | varchar(255) | NULL | Language code |
Multiselection | text | NULL | String, selected values, separated by "," |
Countries (Multiselect) | text | NULL | String, selected language-codes, separated by "," |
Languages (Multiselect) | text | NULL | String, selected language-codes, separated by "," |
Relations
Table: object_relations_(id) & object_meta_data_(id)
- Data fields of relation types are stored in extra tables
- Data fields are not stored in distinct columns, but as rows, whereas the field name is in an extra column
fieldname
- The column
type
specifies the type of the linked resource (Object, Document, Asset) - The columns
src_id
anddest_id
define the relation / the link between the objects. - Column
index
is used to specify the order of the relations - Columns
ownertype
,ownername
andposition
are used when relations are within field collections, localized fields, object bricks, etc. - The data type
Advanced Many-To-One Object Relation
stores the extra data in a tableobject_meta_data_(id)
- the columncolumn
specifies the name of the meta item anddata
stores the value
Structured
Name | Comment |
---|---|
Table | Table data is stored as a string - serialized. |
Structured Table | Each table cell is stored distinctively; schema: (fieldname)__(row key)#(column key) |
Field-Collections | see special data fields later |
Objectbricks | see special data fields later |
Localized Fields | see special data fields later |
Geographic
Table: object_store_(id)
Name | Data Type | Default | Comment |
---|---|---|---|
Geographic Point | double | NULL | Creates two columns: ‘(name)__longitude’ and ‘(name)__latitude’ |
Geographic Bounds | double | NULL | Creates four columns: ‘(name)__NElongitude ’, ‘(name)__NElatitude’, ‘(name)__SWlongitude’ und ‘(name)__SWlatitude’ |
Geographic Polygon | longtext | NULL | Serialized geo-data |
Other
Name | Data Type | Default | Comment |
---|---|---|---|
Image | int(11) | NULL | ID of the image asset |
Image Advanced | int(11), text | NULL | Creates a column (name)__image (int) for the image assets id and the column (name)__hotspots (text). Hotspots are stored serialized. |
Video | text | NULL | Serialized data |
Checkbox | tinyint(1) | NULL | Boolean value (1 = true) |
Link | text | NULL | Serialized data |
Special Data Fields
Objectbricks
Table/View | Purpose |
---|---|
object_brick_query_(id) Table | Analog to object_query_(id) |
object_brick_store_(id) Table | Main data storage |
Localized fields
Table/View | Purpose |
---|---|
object_localized_(id)_(language-code) View | A database view per language, combining regular and localized data fields |
object_localized_data_(id) Table | Stores localized field data |
object_localized_query_(id)_(language-code) Table | Analog to object_query_(id) |
Field Collections
Table/View | Purpose |
---|---|
object_collection_(collection-name)_(object-id) | Stores data of the field collections fields and the order (index) |