public function testXmlRoundTripWithDbName()
{
$fileNameOrig = realpath($this->testFilesDir . 'webbuilder.schema.xml');
$schema = ezcDbSchema::createFromFile('xml', $fileNameOrig);
$schema->writeToDb($this->db);
$newSchema = ezcDbSchema::createFromDb($this->db);
$newDDL1 = $newSchema->convertToDDL($this->db->getName());
// setup an empty schema to wipe out the db
$emptySchema = new ezcDbSchema(array());
$diffToEmptySchema = ezcDbSchemaComparator::compareSchemas($newSchema, $emptySchema);
$diffToEmptySchema->applyToDb($this->db);
$newSchema->writeToDb($this->db);
$newSchema = ezcDbSchema::createFromDb($this->db);
$newDDL2 = $newSchema->convertToDDL($this->db->getName());
self::assertEquals($newDDL1, $newDDL2);
}
/**
* Returns the difference between the tables $table1 and $table2.
*
* If there are no differences this method returns the boolean false.
*
* @param ezcDbSchemaTable $table1
* @param ezcDbSchemaTable $table2
*
* @return bool|ezcDbSchemaTableDiff
*/
private static final function diffTable(ezcDbSchemaTable $table1, ezcDbSchemaTable $table2)
{
$changes = 0;
$tableDifferences = new ezcDbSchemaTableDiff();
/* See if all the fields in table 1 exist in table 2 */
foreach ($table2->fields as $fieldName => $fieldDefinition) {
if (!isset($table1->fields[$fieldName])) {
$tableDifferences->addedFields[$fieldName] = $fieldDefinition;
$changes++;
}
}
/* See if there are any removed fields in table 2 */
foreach ($table1->fields as $fieldName => $fieldDefinition) {
if (!isset($table2->fields[$fieldName])) {
$tableDifferences->removedFields[$fieldName] = true;
$changes++;
}
}
/* See if there are any changed fieldDefinitioninitions */
foreach ($table1->fields as $fieldName => $fieldDefinition) {
if (isset($table2->fields[$fieldName])) {
$fieldDifferences = ezcDbSchemaComparator::diffField($fieldDefinition, $table2->fields[$fieldName]);
if ($fieldDifferences) {
$tableDifferences->changedFields[$fieldName] = $fieldDifferences;
$changes++;
}
}
}
$table1Indexes = $table1->indexes;
$table2Indexes = $table2->indexes;
/* See if all the indexes in table 1 exist in table 2 */
foreach ($table2Indexes as $indexName => $indexDefinition) {
if (!isset($table1Indexes[$indexName])) {
$tableDifferences->addedIndexes[$indexName] = $indexDefinition;
$changes++;
}
}
/* See if there are any removed indexes in table 2 */
foreach ($table1Indexes as $indexName => $indexDefinition) {
if (!isset($table2Indexes[$indexName])) {
$tableDifferences->removedIndexes[$indexName] = true;
$changes++;
}
}
/* See if there are any changed indexDefinitions */
foreach ($table1Indexes as $indexName => $indexDefinition) {
if (isset($table2Indexes[$indexName])) {
$indexDifferences = ezcDbSchemaComparator::diffIndex($indexDefinition, $table2Indexes[$indexName]);
if ($indexDifferences) {
$tableDifferences->changedIndexes[$indexName] = $indexDifferences;
$changes++;
}
}
}
return $changes ? $tableDifferences : false;
}
private static function getSchemaDiff()
{
$schema1 = new ezcDbSchema(array('bugdb' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'))), 'bugdb_deleted' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'))), 'bugdb_change' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'), 'integerfield3' => new ezcDbSchemaField('integer')), array('primary' => new ezcDbSchemaIndex(array('integerfield1' => new ezcDbSchemaIndexField()), true), 'tertiary' => new ezcDbSchemaIndex(array('integerfield3' => new ezcDbSchemaIndexField()), true)))));
$schema2 = new ezcDbSchema(array('bugdb' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'))), 'bugdb_added' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'))), 'bugdb_change' => new ezcDbSchemaTable(array('integerfield2' => new ezcDbSchemaField('integer'), 'integerfield3' => new ezcDbSchemaField('char', 64)), array('secondary' => new ezcDbSchemaIndex(array('integerfield1' => new ezcDbSchemaIndexField(), 'integerfield3' => new ezcDbSchemaIndexField()), true), 'tertiary' => new ezcDbSchemaIndex(array('integerfield2' => new ezcDbSchemaIndexField()), true)))));
return ezcDbSchemaComparator::compareSchemas($schema1, $schema2);
}
public function testAddingAutoIncrementField()
{
$dbh = $this->db;
$schema1 = new ezcDbSchema(array('table10801' => new ezcDbSchemaTable(array('id' => ezcDbSchemaField::__set_state(array('type' => 'integer', 'length' => false, 'notNull' => false, 'default' => 0, 'autoIncrement' => false, 'unsigned' => false)), 'text' => new ezcDbSchemaField('text')))));
$schema2 = new ezcDbSchema(array('table10801' => new ezcDbSchemaTable(array('id' => ezcDbSchemaField::__set_state(array('type' => 'integer', 'length' => false, 'notNull' => true, 'default' => null, 'autoIncrement' => true, 'unsigned' => false)), 'text' => new ezcDbSchemaField('text')))));
$schema1->writeToDb($dbh);
$diff = ezcDbSchemaComparator::compareSchemas($schema1, $schema2);
$diff->applyToDb($dbh);
$q = $dbh->createInsertQuery();
$stmt = $q->insertInto($dbh->quoteIdentifier('table10801'))->set($dbh->quoteIdentifier('text'), $q->bindValue('text'))->prepare();
$stmt->execute();
$q = $dbh->createSelectQuery();
$stmt = $q->select('*')->from($dbh->quoteIdentifier('table10801'))->prepare();
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$this->assertEquals(1, $result[0]['id']);
}
public function testCompareChangedIndex()
{
$schema1 = new ezcDbSchema(array('bugdb' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'), 'integerfield2' => new ezcDbSchemaField('integer')), array('primary' => new ezcDbSchemaIndex(array('integerfield1' => new ezcDbSchemaIndexField()), true)))));
$schema2 = new ezcDbSchema(array('bugdb' => new ezcDbSchemaTable(array('integerfield1' => new ezcDbSchemaField('integer'), 'integerfield2' => new ezcDbSchemaField('integer')), array('primary' => new ezcDbSchemaIndex(array('integerfield1' => new ezcDbSchemaIndexField(), 'integerfield2' => new ezcDbSchemaIndexField()), true)))));
$expected = new ezcDbSchemaDiff(array(), array('bugdb' => new ezcDbSchemaTableDiff(array(), array(), array(), array(), array('primary' => new ezcDbSchemaIndex(array('integerfield1' => new ezcDbSchemaIndexField(), 'integerfield2' => new ezcDbSchemaIndexField()), true)))));
self::assertEquals($expected, ezcDbSchemaComparator::compareSchemas($schema1, $schema2));
}
<?php
require 'tutorial_autoload.php';
// create the two ezcDbSchema objects:
$xmlSchema = ezcDbSchema::createFromFile('xml', 'wanted-schema.xml');
$db = ezcDbFactory::create('mysql://*****:*****@host/database');
$dbSchema = ezcDbSchema::createFromDb($db);
// compare the schemas:
$diffSchema = ezcDbSchemaComparator::compareSchemas($dbSchema, $xmlSchema);
// return an array containing the differences as SQL DDL to upgrade $dbSchema
// to $xmlSchema:
$sqlArray = $diffSchema->convertToDDL($db);
// write the differences to a file:
$diffSchema->writeToFile('array', 'differences.php');
// apply the differences to the database:
$diffSchema->applyToDB($db);
<?php
require 'ezc-setup.php';
// Create the schema objects
$db = ezcDbFactory::create('mysql://root@localhost/geolocation');
$dbSchema = ezcDbSchema::createFromDb($db);
$wantedSchema = ezcDbSchema::createFromFile('xml', dirname(__FILE__) . '/geoschema.xml');
// Compare the schemas
$differences = ezcDbSchemaComparator::compareSchemas($dbSchema, $wantedSchema);
// Show the differences
foreach ($differences->convertToDDL($db) as $query) {
echo $query, ";<br/>\n";
}
