`database.xml`

database.xml lets packages declare database tables that QUIQQER creates or updates during setup and package updates.

Use it for package-owned tables and project-related tables. Keep schema changes small, explicit, and package-scoped.

Since quiqqer/utils 2.4.0, portable field types and field attributes are the recommended way to define new package tables. The importer maps database.xml declarations through Doctrine DBAL before creating or altering tables, which improves support for PostgreSQL and other database platforms.

The older SQL-like field syntax remains supported for existing packages and for packages that must also run on installations older than quiqqer/utils 2.4.0.

Basic Structure

Create database.xml in the package root.

xml

<?xml version="1.0" encoding="UTF-8"?>
<database>
    <global>
        <table name="example">
            <field type="integer" autoincrement="true" primary="true">id</field>
            <field type="string" length="255">title</field>
            <field type="text" nullable="true">description</field>

            <index>title</index>
        </table>
    </global>
</database>

The <field> text is the column name. In current package definitions, the type attribute contains a portable field type and additional attributes define column options.

QUIQQER imports the first <global> and the first <projects> section in the file. Keep the structure explicit and avoid duplicate root sections.

Portable Field Syntax

For new tables on installations with quiqqer/utils 2.4.0 or later, use portable field types and attributes:

xml

<field type="integer" autoincrement="true" primary="true">id</field>
<field type="string" length="255" default="">title</field>
<field type="text" nullable="true">description</field>
<field type="decimal" precision="10" scale="2" nullable="true">price</field>
<field type="datetime" nullable="true" default="NULL">publish_date</field>

This form avoids embedding one database dialect in the XML file.

Global Tables

Use <global> for tables that exist once per QUIQQER installation.

xml

<global>
    <table name="cron">
        <field type="integer" autoincrement="true" primary="true">id</field>
        <field type="boolean" default="0">active</field>
        <field type="string" length="1000">title</field>
    </table>
</global>

Global tables are useful for package state, queues, logs, cache metadata, configuration records, and package-owned domain records that are not tied to a single project language.

Project Tables

Use <projects> for project-related tables. QUIQQER creates these tables for project contexts.

xml

<projects>
    <table name="bricksCache" no-auto-update="1" no-site-reference="1">
        <field type="bigint">id</field>
        <field type="string" length="255">area</field>
        <field type="integer" nullable="true">brick</field>

        <index>id</index>
        <index>area</index>
    </table>
</projects>

Use project tables when data belongs to projects, languages, sites, or rendered project output.

Keys And Indexes

Use primary="true" for single-column primary keys or <primary> when the key needs a separate declaration. Use <index> for indexed columns.

xml

<field type="integer" autoincrement="true" primary="true">id</field>

<primary>project,lang,id</primary>
<index>project</index>
<index>lang</index>

Additional supported index nodes are:

<unique> for unique indexes.
<auto_increment> for auto-increment columns.
<fulltext> for full-text indexes.

Add indexes for columns used in lookups, filters, and joins. Avoid unnecessary indexes on columns that are rarely queried.

Fields

Since quiqqer/utils 2.4.0, fields should be declared with portable type and option attributes for new package tables.

xml

<field type="string" length="255">title</field>
<field type="integer" nullable="true">optional_id</field>
<field type="datetime" nullable="true" default="NULL">publish_date</field>

If type is omitted, QUIQQER treats the field as text. If the type does not already contain NULL or NOT NULL, fields are imported as NOT NULL unless null="1" or nullable="true" is set.

For packages that must support older installations, keep the legacy SQL-like syntax documented below.

Portable field types include:

Type	Use
`bigint`	Large integer values.
`integer`, `int`, `mediumint`, `tinyint`	Integer values.
`smallint`	Small integer values.
`boolean`, `bool`	Boolean values.
`string`, `varchar`, `char`	String values, usually with `length`.
`text`, `tinytext`, `mediumtext`, `longtext`	Long text values.
`datetime`, `timestamp`	Date and time values.
`date`	Date values.
`time`	Time values.
`decimal`	Fixed precision decimal values with `precision` and `scale`.
`float`, `double`	Floating point values.
`blob`, `binary`	Binary values.
`json`	JSON data.

Portable field attributes include:

Attribute	Use
`length`	Length for string fields.
`nullable="true"`	Allow `NULL`.
`null="1"`	Legacy form for allowing `NULL`.
`notnull="true"`	Require `NOT NULL`.
`default`	Default value. `NULL`, `NOW()`, and `CURRENT_TIMESTAMP()` are normalized.
`precision`	Precision for decimal fields.
`scale`	Scale for decimal fields.
`autoincrement="true"`	Mark the column as auto-incrementing.
`auto_increment="true"`	Alternative auto-increment attribute.
`primary="true"`	Mark the column as a primary key.
`unsigned="true"`	Mark numeric columns as unsigned where the database supports it.
`comment`	Column comment.

Compatibility With Legacy Syntax

The SQL-like field syntax remains supported and is still valid for existing package tables:

xml

<field type="INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY">id</field>
<field type="VARCHAR(1000) NOT NULL">title</field>
<field type="DATETIME NULL DEFAULT NULL">publish_date</field>

Since quiqqer/utils 2.4.0, these declarations are also parsed into database abstraction layer types during import. Common MySQL-style declarations are recognized for integer, string, text, date/time, decimal, floating point, binary, and JSON fields.

Keep this form when maintaining existing schemas or when a package must run on installations older than quiqqer/utils 2.4.0.

Table Attributes

Current package examples use these table attributes:

Attribute	Use
`engine`	Select a storage engine for the table.
`no-auto-update="1"`	Prevent automatic update behavior for project table data.
`no-project-lang="1"`	Create a project table without automatic project-language handling.
`no-site-reference="1"`	Create a project table without automatic site reference handling.
`site-types="type-a,type-b"`	Restrict project table behavior to selected site types.

Use these attributes only when the package manages the table data itself.

Tables can also provide a short comment:

xml

<table name="example">
    <comment>Stores package example records.</comment>
</table>

Execute Setup Code

A package can attach setup code to <global> with execute.

xml

<database>
    <global execute="\QUI\Countries\Setup::setup"></global>
</database>

Use this for setup routines that cannot be expressed as table declarations alone. Keep the method idempotent because setup and update workflows may run more than once.

Real Package Shape

quiqqer/backendsearch uses the portable field syntax introduced with quiqqer/utils 2.4.0:

xml

<database>
    <global>
        <table name="quiqqerBackendSearch">
            <field type="integer" autoincrement="true" primary="true">id</field>
            <field type="text" nullable="true">title</field>
            <field type="text" nullable="true">description</field>
            <field type="string" length="255" default="">icon</field>
            <field type="text" nullable="true">search</field>
            <field type="string" length="255">filterGroup</field>
            <field type="string" length="255">group</field>
            <field type="text" nullable="true">groupLabel</field>
            <field type="text">searchdata</field>
            <field type="string" length="10">lang</field>
        </table>
    </global>
</database>

quiqqer/bricks shows the supported legacy SQL-like syntax in an existing package:

xml

<database>
    <global>
        <table name="bricks">
            <field type="INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY">id</field>
            <field type="VARCHAR(255)">project</field>
            <field type="VARCHAR(2)">lang</field>
            <field type="VARCHAR(255) NOT NULL">title</field>

            <primary>id</primary>
            <index>project</index>
            <index>lang</index>
        </table>
    </global>

    <projects>
        <table name="bricksCache" no-auto-update="1" no-site-reference="1">
            <field type="BIGINT(20) NOT NULL">id</field>
            <field type="VARCHAR(255) NOT NULL">area</field>
            <field type="INT NULL">brick</field>

            <index>id</index>
            <index>area</index>
        </table>
    </projects>
</database>

Practical Checklist

Before adding database.xml:

Choose <global> or <projects> based on the data ownership.
Keep table names package-scoped enough to avoid collisions.
Define explicit primary keys.
Add indexes for columns used in frequent lookups.
Use project table attributes only when needed.
Use portable field types and explicit nullability for new tables.
Keep setup methods idempotent when using execute.
Verify package code handles existing data during updates.

Package Development

Package APIs

`database.xml`

Basic Structure

Portable Field Syntax

Global Tables

Project Tables

Keys And Indexes

Fields

Compatibility With Legacy Syntax

Table Attributes

Execute Setup Code

Real Package Shape

Practical Checklist

database.xml ​

Basic Structure ​

Portable Field Syntax ​

Global Tables ​

Project Tables ​

Keys And Indexes ​

Fields ​

Compatibility With Legacy Syntax ​

Table Attributes ​

Execute Setup Code ​

Real Package Shape ​

Practical Checklist ​

`database.xml`

Basic Structure

Portable Field Syntax

Global Tables

Project Tables

Keys And Indexes

Fields

Compatibility With Legacy Syntax

Table Attributes

Execute Setup Code

Real Package Shape

Practical Checklist