/**
* update function 0
* has no real functionality, only shows how its done
*
*/
public function update_0()
{
// just show how it works
$tableDefinition = '<table>
<name>test_table</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
</field>
<field>
<name>name</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Index_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$this->_backend->dropTable('test_table');
}
/**
* 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 testStringToIndexStatement_004()
{
$string = "\n <index>\n <name>id-account_type-account_id</name>\n <field>\n <name>container_id</name>\n </field>\n <field>\n <name>account_type</name>\n </field>\n <field>\n <name>account_id</name>\n </field>\n </index>";
$statement = $this->_fixIndexDeclarationString(" KEY `id-account_type-account_id` (`container_id`,`account_type`,`account_id`) ");
$index = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$this->assertEquals($statement, $this->_backend->getIndexDeclarations($index));
$this->setExpectedException('Zend_Db_Statement_Exception', '42000');
//42000: group_id and account_id fields missing - expecting Exception
$this->_backend->addIndex($this->_table->name, $index);
}
public function testIsValid()
{
$string = "\n <index>\n <unique>true</unique>\n <field>\n <name>a</name>\n </field>\n </index>";
$index = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$this->assertTrue($index->isValid(), 'Test if a valid field is correctly marked as valid');
$this->assertTrue($index->isValid(true), 'Test if no Exception is thrown on validating a valid field is correctly marked as valid');
$string = "\n <index>\n <unique>true</unique>\n <field>\n <name>" . str_pad('A', 23, 'a') . "</name>\n </field>\n </index>";
$index = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$this->asserttrue($index->isValid(), 'Test if the maximum field name length is still valid');
$string = "\n <index>\n <unique>true</unique>\n <field>\n <name>a</name>\n </field>\n <field>\n <name>" . str_pad('A', 23, 'a') . "</name>\n </field>\n </index>";
$index = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$this->assertFalse($index->isValid(), 'Test if a too long field name is invalid');
$this->setExpectedException('Setup_Exception_InvalidSchema');
$index->isValid(true);
//Test if the parameter throwException works as expected
}
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));
}
}
}
/**
* remove no longer needed field
*
*/
public function update_4()
{
$this->_backend->dropPrimaryKey('phone_callhistory');
$this->_backend->dropIndex('phone_callhistory', 'call_id-phone_id');
$this->_backend->dropCol('phone_callhistory', 'call_id');
$indexDefinition = '
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
<field>
<name>phone_id</name>
</field>
</index>';
$index = Setup_Backend_Schema_Index_Factory::factory('String', $indexDefinition);
$this->_backend->addPrimaryKey('phone_callhistory', $index);
$this->setApplicationVersion('Phone', '0.5');
}
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 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 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;
}
