/**
* Get schema of existing table
*
* @param String $_tableName
*
* @return Setup_Backend_Schema_Table_Mysql
*/
public function getExistingSchema($_tableName)
{
// Get common table information
$select = $this->_db->select()->from('information_schema.tables')->where($this->_db->quoteIdentifier('TABLE_SCHEMA') . ' = ?', $this->_config->database->dbname)->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX . $_tableName);
$stmt = $select->query();
$tableInfo = $stmt->fetchObject();
//$existingTable = new Setup_Backend_Schema_Table($tableInfo);
$existingTable = Setup_Backend_Schema_Table_Factory::factory('Mysql', $tableInfo);
// get field informations
$select = $this->_db->select()->from('information_schema.COLUMNS')->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX . $_tableName);
$stmt = $select->query();
$tableColumns = $stmt->fetchAll();
foreach ($tableColumns as $tableColumn) {
$field = Setup_Backend_Schema_Field_Factory::factory('Mysql', $tableColumn);
$existingTable->addField($field);
if ($field->primary === 'true' || $field->unique === 'true' || $field->mul === 'true') {
$index = Setup_Backend_Schema_Index_Factory::factory('Mysql', $tableColumn);
// get foreign keys
$select = $this->_db->select()->from('information_schema.KEY_COLUMN_USAGE')->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX . $_tableName)->where($this->_db->quoteIdentifier('COLUMN_NAME') . ' = ?', $tableColumn['COLUMN_NAME']);
$stmt = $select->query();
$keyUsage = $stmt->fetchAll();
foreach ($keyUsage as $keyUse) {
if ($keyUse['REFERENCED_TABLE_NAME'] != NULL) {
$index->setForeignKey($keyUse);
}
}
$existingTable->addIndex($index);
}
}
return $existingTable;
}
public function testEqualsExistingSchema_004()
{
$string = "\n <field>\n <name>a</name>\n <type>datetime</type>\n </field>";
$field1 = Setup_Backend_Schema_Field_Factory::factory('Xml', $string);
$this->_backend->addCol($this->_table->name, $field1);
$field2 = $this->_getLastField();
$this->assertTrue($field1->equals($field2));
$this->assertTrue($field2->equals($field1));
}
public function testStringToFieldStatement_008()
{
$string = "\n <field>\n <name>account_id</name>\n <type>integer</type>\n <unsigned>true</unsigned>\n <notnull>false</notnull>\n </field>";
$statement = $this->_fixFieldDeclarationString("`account_id` int(11) unsigned ");
$field = Setup_Backend_Schema_Field_Factory::factory('Xml', $string);
$this->assertEquals($statement, $this->_backend->getFieldDeclarations($field));
$this->_backend->addCol($this->_table->name, $field);
$newColumn = $this->_getLastField();
$this->assertEquals('false', $newColumn->notnull);
$this->assertEquals('true', $newColumn->unsigned);
$this->assertEquals(0, $newColumn->default);
$this->assertTrue($newColumn->equals($field));
}
public function __construct($_tableDefinition = NULL)
{
if ($_tableDefinition !== NULL) {
if (!$_tableDefinition instanceof SimpleXMLElement) {
$_tableDefinition = new SimpleXMLElement($_tableDefinition);
}
$this->setName($_tableDefinition->name);
$this->comment = (string) $_tableDefinition->comment;
$this->version = (string) $_tableDefinition->version;
foreach ($_tableDefinition->declaration->field as $field) {
$this->addField(Setup_Backend_Schema_Field_Factory::factory('Xml', $field));
}
foreach ($_tableDefinition->declaration->index as $index) {
$this->addIndex(Setup_Backend_Schema_Index_Factory::factory('Xml', $index));
}
}
}
/**
* add column callerid
*
*/
public function update_5()
{
$fieldDefinition = '
<field>
<name>callerid</name>
<type>text</type>
<length>80</length>
<notnull>false</notnull>
</field>';
$column = Setup_Backend_Schema_Field_Factory::factory('String', $fieldDefinition);
$this->_backend->addCol('phone_callhistory', $column);
$this->setApplicationVersion('Phone', '0.6');
}
public function testStringToIndexStatement_003()
{
$fieldString = "\n <field>\n <name>group_id</name>\n <type>integer</type>\n </field>";
$field = Setup_Backend_Schema_Field_Factory::factory('Xml', $fieldString);
$this->_backend->addCol($this->_table->name, $field);
$fieldString = "\n <field>\n <name>account_id</name>\n <type>integer</type>\n </field>";
$field = Setup_Backend_Schema_Field_Factory::factory('Xml', $fieldString);
$this->_backend->addCol($this->_table->name, $field);
$string = "\n <index>\n <name>group_id-account_id</name>\n <unique>true</unique>\n <field>\n <name>group_id</name>\n </field>\n <field>\n <name>account_id</name>\n </field>\n </index> ";
$index = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$this->_backend->addIndex($this->_table->name, $index);
$db = Tinebase_Core::getDb();
$tableName = SQL_TABLE_PREFIX . $this->_table->name;
$db->insert($tableName, array('name' => 'test1', 'group_id' => 1, 'account_id' => 1));
$db->insert($tableName, array('name' => 'test2', 'group_id' => 1, 'account_id' => 2));
$db->insert($tableName, array('name' => 'test3', 'group_id' => 2, 'account_id' => 1));
$this->setExpectedException('Zend_Db_Statement_Exception');
//unique constraint violation
$db->insert($tableName, array('name' => 'test4', 'group_id' => 1, 'account_id' => 1));
}
public function testEquals_003()
{
$fieldString = "\n <field>\n <name>name</name>\n <type>integer</type>\n <length>8</length>\n </field>";
$field = Setup_Backend_Schema_Field_Factory::factory('Xml', $fieldString);
$this->_backend->alterCol($this->_table->name, $field, 'name');
$existingSchema = $this->_backend->getExistingSchema($this->_table->name);
$this->assertFalse($this->_table->equals($existingSchema));
//$this->assertFalse($existingSchema->equals($this->_table));
}
public function testStringToIndexStatement_004()
{
$fieldString = "\n <field>\n <name>account_id</name>\n <type>integer</type>\n </field>";
$field = Setup_Backend_Schema_Field_Factory::factory('Xml', $fieldString);
$this->_backend->addCol($this->_table->name, $field);
$string = "\n <index>\n <name>id-account_type-account_id</name>\n <field>\n <name>name</name>\n </field>\n <field>\n <name>account_id</name>\n </field>\n </index>";
$index = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$indexesBefore = $this->_backend->getIndexesForTable($this->_table->name);
$this->_backend->addIndex($this->_table->name, $index);
$indexesAfter = $this->_backend->getIndexesForTable($this->_table->name);
$this->assertEquals(count($indexesBefore) + 1, count($indexesAfter));
}
public function setFields($_fieldDefinitions)
{
foreach ($_fieldDefinitions as $fieldDefinition) {
$this->addField(Setup_Backend_Schema_Field_Factory::factory('Mysql', $fieldDefinition));
}
}
public function getExistingSchema($_tableName)
{
$tableInfo = $this->_getTableInfo($_tableName);
$existingTable = Setup_Backend_Schema_Table_Factory::factory('Oracle', $tableInfo);
foreach ($tableInfo as $index => $tableColumn) {
$field = Setup_Backend_Schema_Field_Factory::factory('Oracle', $tableColumn);
$existingTable->addField($field);
if ($field->primary === 'true' || $field->unique === 'true' || $field->mul === 'true') {
$index = Setup_Backend_Schema_Index_Factory::factory('Oracle', $tableColumn);
$existingTable->addIndex($index);
}
}
$foreignKeys = $this->getConstraintsForTable($_tableName, Setup_Backend_Oracle::CONSTRAINT_TYPE_FOREIGN, true);
foreach ($foreignKeys as $foreignKey) {
$index = Setup_Backend_Schema_Index_Factory::factory('Oracle', $tableColumn);
$index->setForeignKey($foreignKey);
$existingTable->addIndex($index);
}
return $existingTable;
}
public function testStringToFieldStatement()
{
$field = Setup_Backend_Schema_Field_Factory::factory('Xml', "\n <field>\n <name>account_id</name>\n <type>integer</type>\n <unsigned>true</unsigned>\n <notnull>false</notnull>\n </field>\n ");
$this->assertEquals(' "account_id" integer', $this->_backend->getFieldDeclarations($field));
}
public function setFields($_tableDefinition)
{
foreach ($_tableDefinition as $fieldDefinition) {
$this->addField(Setup_Backend_Schema_Field_Factory::factory('Oracle', $fieldDefinition));
}
}
