DatabaseSchema/docs/tutorial.txt - zetacomponents - Git at Google

 eZ Components - DatabaseSchema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 .. contents:: Table of Contents

 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).

 The main intention of the component is to extract an already existing schema
 from a database, and compare it with either a predefined schema to check for
 any changes. Although the component does allow you to create the schema
 manually through the API, it is often easier to create the schema yourself in a
 database, and then extract the data from it.

 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.

 .. include:: tutorial_example_01.php
    :literal:

 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:

 .. include:: tutorial_example_05.php
    :literal:

 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:

 .. include:: tutorial_example_02.php
    :literal:

 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:

 .. include:: tutorial_example_03.php
    :literal:

 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:

 .. include:: tutorial_example_04.php
    :literal:

 Restrictions
 ============

 To keep the compability between different relational database management
 systems, there are restrictions you have to cope with:

 - Lowercase column names

   As some RDBMS always do not store column names case sensitive, but return and
   store them completely as upper case letters, ezcDatabase converts all column
   names to lower case to offer a uniform interface.

 - Field types

   You are limited to an amount of field types common to all RDBMS, or for which
   at least some kind of mapping exists for all RDBMS.

 Data Types
 ==========

 The DatabaseSchema component supports a limited set of data types in order to
 be sure that they are supported in all databases that the component supports.
 Below you can find a list of all the supported types with comments.

 integer
     Used for numerical values.
 boolean
     Used for boolean values - use only with *true* and *false*.
 decimal
     Numerical values with an optional fraction, fixed fraction size.
 float
     Numerical values with an optional fraction.
 date
     A database-specific date field.
 timestamp
     A database-independent date field that stores Unix timestamps.
 text
     Text strings, usually limited in field size.
 blob
     Binary data.
 clob
     Character data, not limited in field size.


 ..
    Local Variables:
    mode: rst
    fill-column: 79
    End:
    vim: et syn=rst tw=79
	eZ Components - DatabaseSchema
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	.. contents:: Table of Contents

	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).

	The main intention of the component is to extract an already existing schema
	from a database, and compare it with either a predefined schema to check for
	any changes. Although the component does allow you to create the schema
	manually through the API, it is often easier to create the schema yourself in a
	database, and then extract the data from it.

	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.

	.. include:: tutorial_example_01.php
	:literal:

	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:

	.. include:: tutorial_example_05.php
	:literal:

	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:

	.. include:: tutorial_example_02.php
	:literal:

	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:

	.. include:: tutorial_example_03.php
	:literal:

	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:

	.. include:: tutorial_example_04.php
	:literal:

	Restrictions
	============

	To keep the compability between different relational database management
	systems, there are restrictions you have to cope with:

	- Lowercase column names

	As some RDBMS always do not store column names case sensitive, but return and
	store them completely as upper case letters, ezcDatabase converts all column
	names to lower case to offer a uniform interface.

	- Field types

	You are limited to an amount of field types common to all RDBMS, or for which
	at least some kind of mapping exists for all RDBMS.

	Data Types
	==========

	The DatabaseSchema component supports a limited set of data types in order to
	be sure that they are supported in all databases that the component supports.
	Below you can find a list of all the supported types with comments.

	integer
	Used for numerical values.
	boolean
	Used for boolean values - use only with true and false.
	decimal
	Numerical values with an optional fraction, fixed fraction size.
	float
	Numerical values with an optional fraction.
	date
	A database-specific date field.
	timestamp
	A database-independent date field that stores Unix timestamps.
	text
	Text strings, usually limited in field size.
	blob
	Binary data.
	clob
	Character data, not limited in field size.



	..
	Local Variables:
	mode: rst
	fill-column: 79
	End:
	vim: et syn=rst tw=79