using-sql-schemas.markdown

layout	title
documentation	Using SQL Schemas

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.

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:

{% highlight xml %}

{% endhighlight %}

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

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

{% highlight xml %}

{% endhighlight %}

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.

{% highlight xml %}

{% endhighlight %}

Schemas in Generated SQL

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

{% highlight sql %} CREATE TABLE bookstore.book ( id INTEGER NOT NULL AUTO_INCREMENT, title VARCHAR(255), PRIMARY KEY (id) ) {% endhighlight %}

Tip
Propel 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.

Tip
in 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 PHP5.3 namespaces for generated Model classes (see PHP 5.3 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 build.properties:

{% highlight ini %} propel.schema.autoPackage = true propel.schema.autoNamespace = true {% endhighlight %}

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

If you're stuck with PHP 5.2, you probably need to use the schema name as a class prefix rather than a namespace. That's what the autoPrefix setting is for:

{% highlight ini %} propel.schema.autoPrefix = true {% endhighlight %}

With such a configuration, a book table assigned to the bookstore schema will generate a BookstoreBook ActiveRecord class.