/**
* Returns an array with SQL DDL statements that creates the database definition in $dbSchema
*
* Converts the schema definition contained in $dbSchema to DDL SQL. This
* SQL can be used to create tables in an existing database according to
* the definition. The SQL queries are returned as an array.
*
* @param ezcDbSchema $dbSchema
* @return array(string)
*/
public function convertToDDL(ezcDbSchema $dbSchema)
{
$this->schema = $dbSchema->getSchema();
// reset queries
$this->queries = array();
$this->context = array();
$this->generateSchemaAsSql();
return $this->queries;
}
/**
* Saves the schema definition in $schema to the file $file.
* @todo throw exception when file can not be opened
*
* @param string $file
* @param ezcDbSchema $dbSchema
*/
public function saveToFile($file, ezcDbSchema $dbSchema)
{
$schema = $dbSchema->getSchema();
$data = $dbSchema->getData();
$fileData = '<?php return ' . var_export(array($schema, $data), true) . '; ?>';
if (!@file_put_contents($file, (string) $fileData)) {
throw new ezcBaseFilePermissionException($file, ezcBaseFileException::WRITE);
}
}
public function testPhpArrayUnwritableDir()
{
$fileName = $this->tempDir . '/bogus/php_array_write_result.php';
$schema = new ezcDbSchema(self::getSchema());
try {
$schema->writeToFile('array', $fileName);
$this->fail('Expected exception not thrown');
} catch (ezcBaseFilePermissionException $e) {
$this->assertEquals("The file '{$fileName}' can not be opened for writing.", $e->getMessage());
}
}
/**
* Writes the schema definition in $dbSchema to files located in $dir.
* This method dumps the given schema to PersistentObject definitions, which
* will be located in the given directory.
*
* @param string $dir The directory to store definitions in.
* @param ezcDbSchema $dbSchema The schema object to create defs for.
*
* @throws ezcBaseFileNotFoundException If the given directory could not be
* found.
* @throws ezcBaseFilePermissionException If the given directory is not
* writable.
*/
public function saveToFile($dir, ezcDbSchema $dbSchema)
{
if (!is_dir($dir)) {
throw new ezcBaseFileNotFoundException($dir, 'directory');
}
if (!is_writable($dir)) {
throw new ezcBaseFilePermissionException($dir, ezcBaseFileException::WRITE);
}
$schema = $dbSchema->getSchema();
foreach ($schema as $tableName => $table) {
$this->writeTable($dir, $tableName, $table);
}
}
/**
* Validates if all the types used in the $schema are supported.
*
* This method loops over all the fields in a table and checks whether the
* type that is used for each field is supported. It will return an array
* containing error strings for each non-supported type that it finds.
*
* @param ezcDbSchema $schema
* @return array(string)
*/
public static function validate(ezcDbSchema $schema)
{
$errors = array();
/* For each table we check all field's types. */
foreach ($schema->getSchema() as $tableName => $table) {
foreach ($table->fields as $fieldName => $field) {
if (!in_array($field->type, ezcDbSchema::$supportedTypes)) {
$errors[] = "Field '{$tableName}:{$fieldName}' uses the unsupported type '{$field->type}'.";
}
}
}
return $errors;
}
/**
* Compare two schemas loaded from different sources.
*
* Load schema #1 from a .php file, save it to mysql db.
* Then load schema #2 from the same db and save it to another .php file.
* (the .php files can be then compared manually)
* Then compare the schemas.
* There should be no differences.
*
* i.e.:
* php -> schema1 -> mydb -> schema2 -> php
*
*/
public function testCompareSchemas()
{
$db = ezcDbInstance::get();
$schema = new ezcDbSchema();
$schema->load($this->referenceFile, 'php-file', 'schema');
$schema->save($db, $db->getName() . '-db');
$schema2 = new ezcDbSchema();
$schema2->load($db, $db->getName() . '-db');
$schema2->save($this->generatedFile, 'php-file', 'schema');
$diff = $schema->compare($schema2);
$schema->saveDelta($diff, $this->deltaFile, $db->getName() . '-file');
$this->assertEquals(array(), $diff, 'Found differences in the schemas.');
}
/**
* Loads the schema from file into the database.
*/
public static function setupTable()
{
$db = ezcDbInstance::get();
// Load schema
$schema = ezcDbSchema::createFromFile('array', dirname(__FILE__) . '/persistent_test_object_no_auto_increment.dba');
$schema->writeToDb($db);
}
/**
* Loads the schema from file into the database.
*/
public static function setupTable()
{
$db = ezcDbInstance::get();
// Load schema
$schema = ezcDbSchema::createFromFile('array', dirname(__FILE__) . '/table.dba');
$schema->writeToDb($db);
}
protected function getEmptyDb()
{
$schema = ezcDbSchema::createFromFile('xml', TESTPATH . '../src/schema.xml');
// $db = ezcDbFactory::create("sqlite://:memory:");
$db = ezcDbFactory::create("sqlite:///home/ymc-toko/sqlite");
$schema->writeToDb($db);
return $db;
}
/**
* Validates if all the fields used in all indexes exist.
*
* This method loops over all the fields in the indexes of each table and
* checks whether the fields that is used in an index is also defined in
* the table definition. It will return an array containing error strings
* for each non-supported type that it finds.
*
* @param ezcDbSchema $schema
* @return array(string)
*/
public static function validate(ezcDbSchema $schema)
{
$errors = array();
/* For each table we first retrieve all the field names, and then check
* per index whether the fields it references exist */
foreach ($schema->getSchema() as $tableName => $table) {
$fields = array_keys($table->fields);
foreach ($table->indexes as $indexName => $index) {
foreach ($index->indexFields as $indexFieldName => $dummy) {
if (!in_array($indexFieldName, $fields)) {
$errors[] = "Index '{$tableName}:{$indexName}' references unknown field name '{$tableName}:{$indexFieldName}'.";
}
}
}
}
return $errors;
}
/**
* Validates if all the index names used are unique accross the schema.
*
* This method loops over all the indexes in all tables and checks whether
* they have been used before.
*
* @param ezcDbSchema $schema
* @return array(string)
*/
public static function validate(ezcDbSchema $schema)
{
$indexes = array();
$errors = array();
/* For each table we check all auto increment fields. */
foreach ($schema->getSchema() as $tableName => $table) {
foreach ($table->indexes as $indexName => $dummy) {
$indexes[$indexName][] = $tableName;
}
}
foreach ($indexes as $indexName => $tableList) {
if (count($tableList) > 1) {
$errors[] = "The index name '{$indexName}' is not unique. It exists for the tables: '" . join("', '", $tableList) . "'.";
}
}
return $errors;
}
protected function setUp()
{
$this->xmlSchema = ezcDbSchema::createFromFile('xml', dirname(__FILE__) . '/testfiles/bug8900.xml');
// get the tables schema from the database schema
// BY REFERENCE! - otherwise new/deleted tables are NOT updated
// in the schema
$this->schema =& $this->xmlSchema->getSchema();
}
public function testCreateFromFileNonExisting()
{
try {
ezcDbSchema::createFromFile('xml', 'testfiles/isnt-here.php');
self::fail("Expected exception not thrown");
} catch (Exception $e) {
self::assertEquals("The schema file 'testfiles/isnt-here.php' could not be found.", $e->getMessage());
}
}
/**
* Writes the given $schema to $dir using $template.
*
* Iterates through all tables in $schema, sends each of them to a {@link
* ezcTemplate} with $template and writes the result to $dir with the file
* name returned by the template.
*
* @param ezcDbSchema $schema
* @param string $template
* @param mixed $dir
*/
public function write(ezcDbSchema $schema, $template, $dir)
{
$tplConf = ezcTemplateConfiguration::getInstance();
$tplConf->templatePath = $this->properties['options']->templatePath;
$tplConf->compilePath = $this->properties['options']->templateCompilePath;
$tpl = new ezcTemplate();
$tpl->send->classPrefix = $this->properties['options']->classPrefix;
foreach ($schema->getSchema() as $tableName => $tableSchema) {
$tpl->send->schema = $tableSchema;
$tpl->send->tableName = $tableName;
$content = $tpl->process($template);
$fileName = $dir . '/' . $tpl->receive->fileName;
if (!$this->properties['options']->overwrite && file_exists($fileName)) {
throw new ezcPersistentObjectSchemaOverwriteException($fileName);
}
file_put_contents($fileName, $content);
}
}
public function testUppercaseDataTypes()
{
$path = dirname(__FILE__) . '/testfiles/bug13072.sqlite';
$db = ezcDbFactory::create("sqlite://{$path}");
$newSchema = ezcDbSchema::createFromDb($db);
$schema = $newSchema->getSchema();
self::assertEquals('integer', $schema['authors']->fields['id']->type);
self::assertEquals('text', $schema['authors']->fields['firstname']->type);
self::assertEquals('text', $schema['ownership']->fields['critique']->type);
}
public function testParsingTrueFalse()
{
$fileName = realpath($this->testFilesDir . 'bug10365.xml');
$schema = ezcDbSchema::createFromFile('xml', $fileName)->getSchema();
self::assertEquals($schema['bug10365']->fields['field_notnull']->notNull, true);
self::assertEquals($schema['bug10365']->fields['field_notnull']->autoIncrement, true);
self::assertEquals($schema['bug10365']->fields['field_notnull']->unsigned, true);
self::assertEquals($schema['bug10365']->fields['field_null']->notNull, false);
self::assertEquals($schema['bug10365']->fields['field_null']->autoIncrement, false);
self::assertEquals($schema['bug10365']->fields['field_null']->unsigned, false);
}
/**
* Loads the schema from file into the database.
*/
public static function setupTable()
{
$db = ezcDbInstance::get();
// Load schema
$schema = ezcDbSchema::createFromFile('array', dirname(__FILE__) . '/persistent_test_object.dba');
$schema->writeToDb($db);
// create sequence if it is a postgres database
if ($db->getName() == 'pgsql') {
$db->exec('CREATE SEQUENCE PO_test_seq START 5');
}
}
public function setUp()
{
$_GET = null;
$_SERVER = self::$server;
try {
$this->db = ezcDbInstance::get();
$schema = ezcDbSchema::createFromFile('array', dirname(__FILE__) . '/../../../docs/tutorial/openid_db_store_schema.dba');
$schema->writeToDb($this->db);
} catch (Exception $e) {
$this->markTestSkipped("You must provide a database to runtests.php: " . $e->getMessage());
}
}
protected function setUp()
{
parent::setUp();
try {
$this->db = ezcDbInstance::get();
$this->cleanupTables($this->db);
$schema = ezcDbSchema::createFromFile('array', dirname(__FILE__) . DIRECTORY_SEPARATOR . 'workflow.dba');
$schema->writeToDb($this->db);
$this->dbStorage = new ezcWorkflowDatabaseDefinitionStorage($this->db);
} catch (Exception $e) {
$this->markTestSkipped('No test database has been configured: ' . $e->getMessage());
}
}
public function setUp()
{
try {
$this->db = ezcDbInstance::get();
if ($this->db === false) {
$this->markTestSkipped("You must provide a database to runtests.php.");
}
$tables = array(self::$table => new ezcDbSchemaTable(array(self::$fieldId => new ezcDbSchemaField('integer', false, true, null, true), self::$fieldUser => new ezcDbSchemaField('text', 32, true), self::$fieldPassword => new ezcDbSchemaField('text', 64, true), self::$fieldName => new ezcDbSchemaField('text', 64, true), self::$fieldCountry => new ezcDbSchemaField('text', 32, true)), array(self::$fieldUser => new ezcDbSchemaIndex(array(self::$fieldUser => new ezcDbSchemaIndexField()), false, false))));
$schema = new ezcDbSchema($tables);
$schema->writeToDb($this->db);
} catch (Exception $e) {
// Oracle seems to skip every other test if the next line is enabled
// $this->markTestSkipped( "Cannot create test table '" . self::$table . "'. " . $e->getMessage() );
}
if (!isset($this->db)) {
$this->markTestSkipped("You must provide a database to runtests.php. Run runtests.php --help to see how to specify a database.");
}
try {
$query = new ezcQueryInsert($this->db);
$query->insertInto($this->db->quoteIdentifier(self::$table))->set($this->db->quoteIdentifier(self::$fieldId), $query->bindValue('1'))->set($this->db->quoteIdentifier(self::$fieldUser), $query->bindValue('jan.modaal'))->set($this->db->quoteIdentifier(self::$fieldPassword), $query->bindValue(sha1('qwerty')))->set($this->db->quoteIdentifier(self::$fieldName), $query->bindValue('Jan Modaal'))->set($this->db->quoteIdentifier(self::$fieldCountry), $query->bindValue('NL'));
$stmt = $query->prepare();
$stmt->execute();
$query = new ezcQueryInsert($this->db);
$query->insertInto($this->db->quoteIdentifier(self::$table))->set($this->db->quoteIdentifier(self::$fieldId), $query->bindValue('2'))->set($this->db->quoteIdentifier(self::$fieldUser), $query->bindValue('john.doe'))->set($this->db->quoteIdentifier(self::$fieldPassword), $query->bindValue(crypt('foobar', 'jo')))->set($this->db->quoteIdentifier(self::$fieldName), $query->bindValue('John Doe'))->set($this->db->quoteIdentifier(self::$fieldCountry), $query->bindValue('US'));
$stmt = $query->prepare();
$stmt->execute();
$query = new ezcQueryInsert($this->db);
$query->insertInto($this->db->quoteIdentifier(self::$table))->set($this->db->quoteIdentifier(self::$fieldId), $query->bindValue('3'))->set($this->db->quoteIdentifier(self::$fieldUser), $query->bindValue('zhang.san'))->set($this->db->quoteIdentifier(self::$fieldPassword), $query->bindValue(md5('asdfgh')))->set($this->db->quoteIdentifier(self::$fieldName), $query->bindValue('Zhang San'))->set($this->db->quoteIdentifier(self::$fieldCountry), $query->bindValue('CN'));
$stmt = $query->prepare();
$stmt->execute();
$query = new ezcQueryInsert($this->db);
$query->insertInto($this->db->quoteIdentifier(self::$table))->set($this->db->quoteIdentifier(self::$fieldId), $query->bindValue('4'))->set($this->db->quoteIdentifier(self::$fieldUser), $query->bindValue('hans.mustermann'))->set($this->db->quoteIdentifier(self::$fieldPassword), $query->bindValue('abcdef'))->set($this->db->quoteIdentifier(self::$fieldName), $query->bindValue('Hans Mustermann'))->set($this->db->quoteIdentifier(self::$fieldCountry), $query->bindValue('DE'));
$stmt = $query->prepare();
$stmt->execute();
} catch (Exception $e) {
$this->markTestSkipped("Cannot insert test values into table '" . self::$table . "'. " . $e->getMessage());
}
}
/**
* Validates if all the types used in the $schema are supported.
*
* This method loops over all the fields in a table and checks whether the
* type that is used for each field is supported. It will return an array
* containing error strings for each non-supported type that it finds.
*
* @param ezcDbSchema $schema
* @return array(string)
*/
public static function validate(ezcDbSchema $schema)
{
$errors = array();
/* For each table we check all auto increment fields. */
foreach ($schema->getSchema() as $tableName => $table) {
foreach ($table->fields as $fieldName => $field) {
if ($field->autoIncrement === true) {
$found = false;
// Loop over de indexes to see if there is a primary
foreach ($table->indexes as $indexName => $index) {
if ($index->primary === true) {
$found = true;
break;
}
}
if (!$found) {
$errors[] = "Field '{$tableName}:{$fieldName}' is auto increment but there is no primary index defined.";
}
}
}
}
return $errors;
}
/**
* Loops over all the table names in the array and extracts schema
* information.
*
* This method extracts information about a database's schema from the
* database itself and returns this schema as an ezcDbSchema object.
*
* @param array(string) $tables
* @return ezcDbSchema
*/
protected function processSchema(array $tables)
{
$schemaDefinition = array();
array_walk($tables, create_function('&$item,$key', '$item = $item[0];'));
// strip out the prefix and only return tables with the prefix set.
$prefix = ezcDbSchema::$options->tableNamePrefix;
foreach ($tables as $tableName) {
$tableNameWithoutPrefix = substr($tableName, strlen($prefix));
// Process table if there was no prefix, or when a prefix was
// found. In the latter case the prefix would be missing from
// $tableNameWithoutPrefix due to the substr() above, and hence,
// $tableName and $tableNameWithoutPrefix would be different.
if ($prefix === '' || $tableName !== $tableNameWithoutPrefix) {
$fields = $this->fetchTableFields($tableName);
$indexes = $this->fetchTableIndexes($tableName);
$schemaDefinition[$tableNameWithoutPrefix] = ezcDbSchema::createNewTable($fields, $indexes);
}
}
return $schemaDefinition;
}
public function testUnsupportedMySQLDbField()
{
$sql = <<<ENDL
CREATE TABLE `testexternal_musiclists` (
`id` int(10) unsigned NOT NULL auto_increment,
`external_id` varchar(256) NOT NULL,
`url` varchar(256) default NULL,
`title` varchar(256) NOT NULL,
`description` text,
`type` enum('iMix','iTunes') NOT NULL default 'iMix',
`last_updated` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `external_id` (`external_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='';
ENDL;
$this->db->query($sql);
try {
$schema = ezcDbSchema::createFromDb($this->db);
self::fail("Expected exception not thrown.");
} catch (ezcDbSchemaUnsupportedTypeException $e) {
self::assertEquals("The field type 'enum' is not supported with the 'MySQL' handler.", $e->getMessage());
}
}
protected function setUp()
{
$tables = array('user', 'cache_templates', 'cache_values');
// Get the DB instance
try {
$db = ezcDbInstance::get();
} catch (Exception $e) {
$this->markTestSkipped('No database handler defined');
}
$this->basePath = realpath(dirname(__FILE__)) . '/';
// Setup the template engine
$config = ezcTemplateConfiguration::getInstance();
$this->tempDir = $config->compilePath = $this->createTempDir("ezcTemplate_");
$config->templatePath = $this->basePath . 'templates/';
$config->disableCache = false;
$config->cacheManager = new DbCacheManager();
// Create tables.
foreach ($tables as $table) {
try {
$db->exec("DROP TABLE {$table}");
} catch (Exception $e) {
}
// eat
}
$schema = ezcDbSchema::createFromFile('xml', dirname(__FILE__) . '/cache-manager-schema.xml');
$schema->writeToDb($db);
// insert some data
$iq = $db->createInsertQuery();
$s = $iq->insertInto($db->quoteIdentifier('user'))->set($db->quoteIdentifier('id'), 1)->set($db->quoteIdentifier('name'), $iq->bindValue('Raymond'))->set($db->quoteIdentifier('nickname'), $iq->bindValue('sunRay'))->prepare();
$s->execute();
$iq = $db->createInsertQuery();
$s = $iq->insertInto($db->quoteIdentifier('user'))->set($db->quoteIdentifier('id'), 2)->set($db->quoteIdentifier('name'), $iq->bindValue('Derick'))->set($db->quoteIdentifier('nickname'), $iq->bindValue('Tiger'))->prepare();
$s->execute();
$iq = $db->createInsertQuery();
$s = $iq->insertInto($db->quoteIdentifier('user'))->set($db->quoteIdentifier('id'), 3)->set($db->quoteIdentifier('name'), $iq->bindValue('Jan'))->set($db->quoteIdentifier('nickname'), $iq->bindValue('Amos'))->prepare();
$s->execute();
}
public function getSchema()
{
$schema = ezcDbSchema::createFromFile('xml', $this->testFilesDir . '/webbuilder.schema.xml');
return $schema;
}
/**
* Extracts information about a table from the XML element $table
*
* @param SimpleXMLElement $table
*
* @return ezcDbSchemaTable or an inherited class
*/
private function parseTable(SimpleXMLElement $table)
{
$fields = array();
$indexes = array();
foreach ($table->declaration->field as $field) {
$fieldName = (string) $field->name;
$fields[$fieldName] = $this->parseField($field);
}
foreach ($table->declaration->index as $index) {
$indexName = (string) $index->name;
$indexes[$indexName] = $this->parseIndex($index);
}
return ezcDbSchema::createNewTable($fields, $indexes);
}
protected function setUpTables()
{
$schema = ezcDbSchema::createFromFile('xml', dirname(__FILE__) . '/data/schema.xml');
$schema->writeToDb($this->db);
}
<?php
/**
* @copyright Copyright (C) 2005-2009 eZ Systems AS. All rights reserved.
* @license http://ez.no/licenses/new_bsd New BSD License
* @version 1.3.1
* @filesource
* @package WorkflowDatabaseTiein
* @ignore
*/
require_once '../../../trunk/Base/src/base.php';
function __autoload($className)
{
ezcBase::autoload($className);
}
$db = ezcDbFactory::create('mysql://test@localhost/test');
$schema = ezcDbSchema::createFromDb($db);
$schema->writeToFile('array', '../tests/workflow.dba');
file_put_contents('../tests/workflow.dba', str_replace('<?php return array (', '<?php
return array (', file_get_contents('../tests/workflow.dba')));
public function testValidFromDb()
{
$type = ezcTestSettings::getInstance()->db->phptype;
$dsn = ezcTestSettings::getInstance()->db->dsn;
if ($dsn === null || $type === null || $dsn === "sqlite://:memory:") {
$this->markTestSkipped("DSN or database type not set or DSN not supported.");
}
// setup this test
$destination = $this->createTempDir("PersObjDatSchem");
$db = ezcDbFactory::create($dsn);
$fileSource = dirname(__FILE__) . "/data/webbuilder.schema.xml";
$schema = ezcDbSchema::createFromFile("xml", $fileSource);
$schema->writeToDb($db);
// real test
$res = `php PersistentObjectDatabaseSchemaTiein/src/rungenerator.php -f "{$type}" -s "{$dsn}" "{$destination}"`;
$this->assertEquals(1, preg_match('(PersistentObject\\sdefinition\\ssuccessfully\\swritten\\sto)s', $res), 'No success message found in generated output.');
foreach (glob(dirname(__FILE__) . "/data/definition_only/definitions/*.php") as $file) {
$this->assertEquals(file_get_contents($file), file_get_contents($destination . "/" . basename($file)), "Geneator generated an invalid persistent object definition file.");
}
$this->removeTempDir();
}
/**
* Loops over all the indexes in the table $table and extracts information.
*
* This method extracts information about the table $tableName's indexes
* from the database and returns this schema as an array of
* ezcDbSchemaIndex objects. The key in the array is the index' name.
*
* @param string $tableName
* @return array(string=>ezcDbSchemaIndex)
*/
protected function fetchTableIndexes($tableName)
{
$indexBuffer = array();
$resultArray = $this->db->query("SHOW INDEX FROM `{$tableName}`");
foreach ($resultArray as $row) {
$keyName = $row['key_name'];
if ($keyName == 'PRIMARY') {
$keyName = 'primary';
}
$indexBuffer[$keyName]['primary'] = false;
$indexBuffer[$keyName]['unique'] = true;
if ($keyName == 'primary') {
$indexBuffer[$keyName]['primary'] = true;
$indexBuffer[$keyName]['unique'] = true;
} else {
$indexBuffer[$keyName]['unique'] = $row['non_unique'] ? false : true;
}
$indexBuffer[$keyName]['fields'][$row['column_name']] = ezcDbSchema::createNewIndexField();
// if ( $row['sub_part'] )
// {
// $indexBuffer[$keyName]['options']['limitations'][$row['column_name']] = $row['sub_part'];
// }
}
$indexes = array();
foreach ($indexBuffer as $indexName => $indexInfo) {
$indexes[$indexName] = ezcDbSchema::createNewIndex($indexInfo['fields'], $indexInfo['primary'], $indexInfo['unique']);
}
return $indexes;
}
