Path

ez components / documentation / api reference / 2007.1.1 / database


eZ Components 2007.1.1

DatabaseSchema

[ Tutorial ] [ Class tree ] [ Element index ] [ ChangeLog ] [ Credits ]

Introduction

The DatabaseSchema component provides a unified data structure to describe relational databases. DatabaseSchema can read and write the structure from and to several databases (such as MySQL, SQLite and Oracle) and also read and write in two web language files (XML and PHP).

DatabaseSchema can also generate PersistentObject definition files. For further information, please refer to the API documentation of ezcPersistentObjectDefinition and ezcPersistentObjectSchemaGenerator.

Class overview

This section gives you an overview of the main classes of the DatabaseSchema component.

ezcDbSchema
This contains the structure of a database. A ezcDbSchema object can be created from files containing a description or directly from the database. A ezcDbSchema object can also be written to a file in different formats and converted to SQL DDL. It is also possible to create a database structure directly from an ezcDbSchema object.
ezcDbSchemaComparator
This class provides an interface for comparing two database schema definitions that are stored in the ezcDbSchema class.
ezcDbSchemaDiff
The ezcDbSchemaDiff::compareSchemas() method returns an object of this class. The object can be converted to SQL DDL, stored in a file or directly applied to a database.
ezcDbSchemaHandlerManager
This class can be used to register different handlers for reading and writing ezcDbSchema and ezcDbSchemaDiff objects. Its functions to create a reader or writer are used by the ezcDbSchema and ezcDbSchemaDiff classes, where the actual work occurs.
ezcDbSchemaValidator
This provides one method to validate a ezcDbSchema class. It checks whether indexes are on existing columns and if the types used are correct. It provides an easy-to-use API so that other validation checks can be added.

Usage

Creating a database schema definition

Databases can be generated from two different types of sources. The first source is the file, where there are multiple format handlers available. There is a format for reading and writing XML files, and one for reading and writing files that store the ezcDbSchema structure in a PHP array.

The second source is the database. With the ezcDbSchema::createFromDb() method, you can create an ezcDbSchema object directly from an existing database connection.

  1. <?php
  2. require 'tutorial_autoload.php';
  3. 
  4. // create a database schema from an XML file:
  5. $xmlSchema ezcDbSchema::createFromFile'xml''wanted-schema.xml' );
  6. 
  7. // create a database schema from a database connection:
  8. $db ezcDbFactory::create'mysql://user:password@host/database' );
  9. $dbSchema ezcDbSchema::createFromDb$db );
 10. 
 11. ?>

This example shows how to create a database schema from the two different sources. With the first parameter in ezcDbSchema::createFromFile(), you define which type to use ('xml' or 'array'). The second parameter is the file that you are reading the schema definition from.

The ezcDbSchema::createFromDb() method has only one parameter. This parameter is an ezcDbHandler object that was created with ezcDbFactory.

In both cases, the methods will return an object of the class ezcDbSchema. The documentation for ezcDbSchemaHandlerManager lists the supported types of readers and writers. The ezcDbSchema->getSchema() method returns the schema as an array of ezcDbSchemaTable objects. The ezcDbSchemaTable object then contains, in two member variables, an array of ezcDbSchemaField objects to describe the fields and an array of ezcDbSchemaIndex objects to describe the indices.

Modifying a database schema definition

Once a ezcDbSchema object has been created, its tables and fields can be manipulated in different ways. Tables can be created, edited and deleted. Fields can be added to tables, edited and deleted. See the example below:

  1. <?php
  2. require 'tutorial_autoload.php';
  3. 
  4. // create a database schema from an XML file
  5. $xmlSchema ezcDbSchema::createFromFile'xml''enterprise.xml' );
  6. 
  7. // get the tables schema from the database schema
  8. // BY REFERENCE! - otherwise new/deleted tables are NOT updated in the schema
  9. $schema =& $xmlSchema->getSchema();
 10. 
 11. // add a new table (employees) to the database
 12. $schema['employees'] = new ezcDbSchemaTable(
 13.     array(
 14.         'id' => new ezcDbSchemaField'integer'falsetruenulltrue ),
 15.     ),
 16.     array(
 17.         'primary' => new ezcDbSchemaIndex( array( 'id' => new ezcDbSchemaIndexField() ), true ),
 18.     )
 19. );
 20. 
 21. // copy the schema of table employees to table persons
 22. $schema['persons'] = clone $schema['employees'];
 23. 
 24. // delete the table table2
 25. unset( $schema['table2'] );
 26. 
 27. // add the fields birthday and salary to the table employees
 28. $schema['employees']->fields['birthday'] = new ezcDbSchemaField'date' );
 29. $schema['employees']->fields['salary'] = new ezcDbSchemaField'integer' );
 30. 
 31. // modify the type of salary field to be float
 32. $schema['employees']->fields['salary']->type 'float';
 33. 
 34. // delete the field salary
 35. unset( $schema['employees']->fields['salary'] );
 36. 
 37. ?>

Saving a database schema definition

Once a ezcDbSchema object has been created, it can be manipulated in any way. Then, there are different possible ways to save the database schema. The ezcDbSchema->writeToFile() method stores an ezcDbSchema object to a file on disk. As stated, you can use the XML and PHP array formats. This is shown in the first part of this example:

  1. <?php
  2. require 'tutorial_autoload.php';
  3. 
  4. // save a database schema to an XML file:
  5. $schema->writeToFile'array''saved-schema.php' );
  6. 
  7. // create a database from a database schema:
  8. $db ezcDbFactory::create'mysql://user:password@host/database' );
  9. $schema->writeToDb$db );
 10. 
 11. // create SQL DDL for a specific database and echo it:
 12. $db ezcDbFactory::create'mysql://user:password@host/database' );
 13. foreach ( $schema->convertToDDL$db ) as $sqlStatement )
 14. {
 15.     echo $sqlStatement"\n";
 16. }
 17. 
 18. ?>

The second and third methods both deal directly with a database. The second method in the example uses the database connection $db for creating a schema. This method only saves to the tables in the schema definition. The schemas that are defined will be overwritten.

The third method merely uses the database connection to determine which SQL DLL dialect to use for the query to create the schema in the database.

Comparing database schemas

The ezcDbSchemaComparator class provides the ezcDbSchemaComparator::compareSchemas() method to compare two different ezcDbSchema objects. The result of this operation is an ezcDbSchemaDiff object. The next example illustrates this:

  1. <?php
  2. require 'tutorial_autoload.php';
  3. 
  4. // create the two ezcDbSchema objects:
  5. $xmlSchema ezcDbSchema::createFromFile'xml''wanted-schema.xml' );
  6. $db ezcDbFactory::create'mysql://user:password@host/database' );
  7. $dbSchema ezcDbSchema::createFromDb$db );
  8. 
  9. // compare the schemas:
 10. $diffSchema ezcDbSchemaComparator::compareSchemas$dbSchema$xmlSchema );
 11. 
 12. // return an array containing the differences as SQL DDL to upgrade $dbSchema
 13. // to $xmlSchema:
 14. $sqlArray $diffSchema->convertToDDL$db );
 15. 
 16. // write the differences to a file:
 17. $diffSchema->writeToFile'array''differences.php' );
 18. 
 19. // apply the differences to the database:
 20. $diffSchema->applyToDB$db );
 21. 
 22. ?>

First, we create the two different schemas: one from the database, and one from a file. In line 10, we utilize the ezcDbSchemaComparator::compareSchemas() function to compare the two schemas. This process returns the differences as the $diffSchema variable.

You can perform multiple actions with the generated differences. These three methods are explained in the inline comments in the above example.

Validating schemas

The last feature that the DatabaseSchema component offers is the validation of schemas. The ezcDbSchemaValidator::validate() method accepts an ezcDbSchema object and returns an array of strings describing potential problems with the schema. This is shown in the following example:

  1. <?php
  2. require 'tutorial_autoload.php';
  3. 
  4. $xmlSchema ezcDbSchema::createFromFile'xml''wanted-schema.xml' );
  5. $messages ezcDbSchemaValidator::validate$xmlSchema );
  6. foreach ( $messages as $message )
  7. {
  8.     echo $message"\n";
  9. }
 10. ?>
Last updated: Wed, 28 Nov 2007