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.
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.
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', 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 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.
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.
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. ?>
