Using SQL Schemas

Some database vendors support “schemas”, a.k.a. namespaces of collections of database objects (tables, views, etc.). MSSQL, PostgreSQL, and to a lesser extent MySQL, all provide the ability to group and organize tables into schemas. Propel supports tables organized into schemas, and works seamlessly in this context. For SQLite we emulate schema support.

Schema Definition

Assigning a Table to a Schema

In a XML schema, you can assign all the tables included into a <database> tag to a given schema by setting the schema attribute on the <database> tag:

<database name="bookstore" schema="bookstore">
  <table name="book">
    <column name="id" required="true" primaryKey="true" autoIncrement="true" type="integer" />
    <column name="title" type="varchar" required="true" />
  </table>
</database>

TipOn RDBMS that do not support SQL schemas (Oracle), the schema attribute is ignored.

You can also assign a table to a given schema individually ; this overrides the schema of the parent <database>:

<table name="book" schema="bookstore1">
  <column name="id" required="true" primaryKey="true" autoIncrement="true" type="integer" />
  <column name="title" type="varchar" required="true" />
</table>

Foreign Keys Between Schemas

You can create foreign keys between tables assigned to different schemas, provided you set the foreignSchema attribute in the <foreign-key> tag.

<table name="book" schema="bookstore">
  <column name="id" required="true" primaryKey="true" autoIncrement="true" type="integer" />
  <column name="title" type="varchar" required="true" />
  <column name="author_id" type="integer" />
  <foreign-key foreignTable="author" foreignSchema="people" onDelete="setnull" onUpdate="cascade">
    <reference local="author_id" foreign="id" />
  </foreign-key>
</table>
<table name="author" schema="people">
  <column name="id" required="true" primaryKey="true" autoIncrement="true" type="integer" />
  <column name="name" type="varchar" required="true" />
</table>

Schemas in Generated SQL

When generating the SQL for table creation, Propel correctly adds the schema prefix (example for MySQL):

CREATE TABLE `bookstore`.`book`
(
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255),
  PRIMARY KEY (`id`)
)

TipPropel does not take care of creating the schema. The target database must already contain the required schemas, and the user credentials must allow Propel to access this schema.

Schemas in PHP Code

Just like actual table names, SQL schemas don’t appear in the PHP code. For the PHP developer, who manipulates phpNames, it’s as if schemas didn’t existed.

Of course, you can make queries spanning across several schemas.

Tipin Mysql, “SCHEMA” and “DATABASE” are synonyms. Therefore, the ability to define another schema for a given table actually allows cross-database queries.

Using the Schema As Base for PHP code Organization

Propel provides other features to organize your model:

• Packages are subdirectories in which Model classes get generated (see Multi-Component Data Model)
• Namespaces are actual PHP namespaces for generated Model classes (see How to Use Namespaces)

You can easily tell Propel to copy the schema attribute to both the package and the namespace attributes, in order to reproduce the SQL organization at the PHP level. To that extent, modify the following settings in your configuration file:

propel:
  generator:
      schema:
          autoPackage: true
          autoNamespace: true

<?php

return [
    'propel' => [
        'generator' => [
            'schema' => [
                'autoPackage'   => true,
                'autoNamespace' => true,
            ]
        ]
    ]
];

{
    "propel": {
        "generator": {
            "autoPackage": true,
            "autoNamespace": true
        }
    }
}

[propel]
;
; Generator section
;
generator.schema.autoPackage = true
generator.schema.autoNamespace = true

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <propel>
        <generator>
            <schema>
                <autoPackage>true</autoPackage>
                <autoNamespace>true</autoNamespace>
            </schema>
        </generator>
    </propel>
</config>

With such a configuration, a book table assigned to the bookstore schema will generate a Bookstore\Book ActiveRecord class under the bookstore/ subdirectory.