Custom Database Tables

Edit

Improvements?

Let us know by posting here.

Creating Custom Database Tables with Packages

For custom database tables in Concrete CMS packages:

  1. Use the AXMLS format, available since the beginning of Concrete CMS.
  2. Employ the Doctrine XML format, compatible with the Doctrine Database Access Layer in Concrete.
  3. Utilize PHP Entity Classes and the Doctrine 2 ORM Library

Each method offers different levels of compatibility and functionality.

Legacy Method: db.xml in AXMLS Format

Developers experienced with Concrete CMS can use the AXMLS format for database tables. Create a db.xml file in the root directory with an XML description of the database tables. For details, see Creating and Working with db.xml Files.

Example

<?xml version="1.0"?>
<schema version="0.3">
    <table name="TestDatabaseTable1">
        <field name="cID" type="I" size="10">
            <KEY/>
            <DEFAULT value="0"/>
            <UNSIGNED/>
        </field>
        <field name="testBoolean" type="I1">
            <NOTNULL/>
            <DEFAULT value="0"/>
        </field>
    </table>

    <table name="TestDatabaseTable2">
        <field name="id" type="C" size="255">
            <KEY/>
            <NOTNULL/>
        </field>
        <field name="testField" type="C" size="255">
            <NOTNULL/>
        </field>
        <field name="records" type="I">
            <DEFAULT value="0"/>
            <UNSIGNED/>
        </field>
        <field name="data" type="X2">
        </field>
    </table>
</schema>

db.xml in Doctrine XML Format

Doctrine XML format, based on Doctrine DBAL,provides consistent types and nomenclature and replaces AXMLS codes like C with more descriptive types like string.

Advantages of Doctrine XML include:

  1. Supports foreign keys, comments, and features not as well covered in AXMLS.
  2. Easily applicable in other projects, installable via composer.

Doctrine XML Github Repository

Access the Doctrine XML library at https://github.com/concretecms/doctrine-xml.

Example

Example of a Users table in Doctrine XML:

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns="http://www.concrete5.org/doctrine-xml/0.5"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.concrete5.org/doctrine-xml/0.5 https://concretecms.github.io/doctrine-xml/doctrine-xml-0.5.xsd">

    <table name="Users">
        <!-- [Fields and configurations omitted for brevity] -->
    </table>
</schema>

Auto-Detection

Doctrine XML is recognized automatically when the XML schema includes xmlns="http://www.concrete5.org/doctrine-xml/0.5".

Converting from AXML Format

Convert from AXML to Doctrine XML at https://concretecms.github.io/doctrine-xml/.

Doctrine ORM Entities

Concrete now supports handling custom database tables using Doctrine ORM entities instead of direct database tables. Developers define these entities as PHP classes with annotations, which automatically generate database tables.

Requirements

Familiarity with Doctrine 2 and its ORM component is necessary.

Example

Creating a 'UserAgent' entity to store browser identifiers:

File: packages/statistics/src/Entity/UserAgent.php

<?php
namespace Concrete\Package\Statistics\Src\Entity;

class UserAgent {
    protected $id;
    protected $value;
    protected $hash;

    // [Methods omitted for brevity]
}

Annotate the PHP class to turn it into a Doctrine ORM entity:

<?php
namespace Concrete\Package\Statistics\Src\Entity;

/**
 * @Entity
 * @Table(name="StatisticsUserAgents")
 */
class UserAgent {
    /**
     * @Id @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @Column(type="string")
     */
    protected $value;

    /**
     * @Column(type="string")
     */
    protected $hash;

    // [Methods omitted for brevity]
}

Upon package installation, the table StatisticsUserAgents is created automatically. Use Doctrine ORM for data persistence and managing schema changes.

Including Doctrine Entities in Your Package

Requirements

Developers must be well-versed in Doctrine 2 and its ORM component.

Standard Behavior

Entities in packages typically use:

  • Annotation Driver for database info from PHP code annotations.
  • For legacy \Src namespace enabled packages, the packages/package_handle/src/ directory is scanned for entities.
  • Without legacy \Src namespace, packages/your_package/src/Concrete/Entity and $pkg->getPackageAutoloaderRegistries() specified locations are scanned.

This is managed by Concrete\Core\Database\EntityManager\Provider\DefaultPackageProvider.

Annotation Driver Functionality

For packages requiring Concrete CMS version 8 or above with annotated entity classes:

Before:

namespace Concrete\Package\Statistics\Src\Entity;

/**
 * @Entity
 * @Table(name="StatisticsUserAgents")
 */
class UserAgent {
    // [Attributes with basic annotations]
}

After:

namespace Concrete\Package\Statistics\Src\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="StatisticsUserAgents")
 */
class UserAgent {
    // [Attributes with @ORM prefixed annotations]
}

This change supports advanced features like Doctrine Extensions.

Customizable Behavior

To tailor package entity manager configuration:

  1. Use a custom package provider for specific entity locations.
  2. Integrate additional Doctrine types or extensions.
  3. Utilize alternate formats like XML or YAML for entity definitions.

Implement one of these in your package's controller:

  1. Concrete\Core\Database\EntityManager\Provider\ProviderAggregateInterface with getEntityManagerProvider() returning ProviderInterface.
  2. Directly implement ProviderInterface, returning DriverInterface objects.

Example

For a package with entities in Concrete\Package\YourPackage\Entity and PortlandLabs\Testing\Entity:

  1. Implement ProviderAggregateInterface in the package controller.

  2. Use StandardPackageProvider for the desired locations:

    public function getEntityManagerProvider() {
    return new StandardPackageProvider($this->app, $this, [
        'src/Concrete/Entity' => 'Concrete\Package\YourPackage\Entity',
        'src/Testing/Entity' => 'PortlandLabs\Testing\Entity'
    ]);
}

Other File Formats

For YAML or XML formats, explore Concrete\Core\Database\EntityManager\Provider\YamlProvider and XmlProvider.

Doctrine Entities in a Custom Database

Exclude Entities from Concrete's Management

In your package controller.php, after the namespace declaration, implement ProviderInterface to prevent Concrete from managing your entities:

use Concrete\Core\Database\EntityManager\Provider\ProviderInterface;

class Controller extends Package implements ProviderInterface

public function getDrivers() {
    return [];
}

For entities managed by Concrete, separate them in a different directory, like src/Entities/Standard, and modify getDrivers accordingly.

Establish a Separate Database Connection

Set up a connection to your entity database. Connection setup guide. Assume the connection is named my-connection.

Implement a Custom Entity Manager

Create your own entity manager in your package's src directory:

<?php

namespace MyNamespace;

use Concrete\Core\Database\DatabaseStructureManager;
use Doctrine\ORM\EntityManager;

class MyEntityManager extends EntityManager {
    public function refreshProxyClasses() {
        $manager = new DatabaseStructureManager($this);
        $manager->clearCacheAndProxies();
    }
}

Initialize the Custom Entity Manager

In your package controller.php, add:

private function registerEntityManager() {
    $this->app->singleton(\MyNamespace\MyEntityManager::class, function ($app) {
        // Configuration for custom entity manager
    });
}

public function on_start() {
    $this->registerEntityManager();
}

The custom entity manager uses annotation driver and entities in src/Entity.

Handle Proxy Class Generation

Add methods to your package controller for install and upgrade operations:

public function install() {
    $this->registerEntityManager();
    $this->refreshProxyClasses();
}

public function upgrade() {
    $this->registerEntityManager();
}

private function registerEntityManager() {
    $this->app->make(\MyNamespace\MyEntityManager::class)->refreshProxyClasses();
}

Integrate with Concrete

Add integration for listing and refreshing entities:

private function registerEntityEvents() {
    $app = $this->app;
    $director = $app->make(\Symfony\Component\EventDispatcher\EventDispatcher::class);
    $director->addListener('on_list_package_entities', function ($event) use ($app) {
        $event->addEntityManager($app->make(\MyNamespace\MyEntityManager::class));
    });
    $director->addListener('on_refresh_package_entities', function ($event) use ($app) {
        $app->make(\MyNamespace\MyEntityManager::class)->refreshProxyClasses();
    });
}