The DatabaseSchema components provides a unified datasctructure to describe the
structure of relational databases. DatabaseSchema can read and write these
structure from and to several databases (like MySQL, SQLite, Oracle,...) and
also read and write it to disc in several formats (XML, PHP,...).
Beside that, DatabaseSchema allows you to generate PersistentObject definition
files. For further information, please refer to the documentation of
ezcPersistentObjectDefinition and ezcPersistentObjectSchemaGenerator.
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/writing XML files, and one for reading/writing files
that store the ezcDbSchema structure in a PHP array.
The second source are databases directly. With the ezcDbSchema::createFromDb()
method you can create an ezcDbSchema object directly from an already 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 to ezcDbSchema::createFromFile() you tell the
method 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 the ezcDbFactory.
In both cases the methods will return an object of the class ezcDbSchema. The
documentation for ezcDbSchemaHandlerManager documents 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.
Once a ezcDbSchema 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, can be edited and deleted. See 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', false, true, null, true ),
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. ?>
Once a ezcDbSchema has been created it can be manipulated in whatever way you
prefer. After manipulation there are different possibilities to save the
database schema. The ezcDbSchema->writeToFile() method stores an ezcDbSchema
object to a file on disk. Again you can use the xml and array formats. This you
can see 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 with a database directly. The second
method in the example uses the database connection $db for creating a schema.
The method will not touch any other table besides the ones 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 while creating the SQL that can be used to create the schema in
the database.
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.
Again there are multiple ways of doing something "useful" with the generated
differences. These three methods are explained with the inline comments in the
above example.
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 eventual problems with the
schema. The next example shows this:
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. ?>
