/**
* update from 6.0 -> 6.1
* - add division table
*
* @return void
*/
public function update_0()
{
$tableDefinition = '<table>
<name>sales_divisions</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>title</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_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
$this->setApplicationVersion('Sales', '6.1');
}
/**
* 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;
}
/**
* add the asterisk_peers table
*/
public function update_18()
{
$tableDefinition = "\n <table>\n <name>snom_phones_acl</name>\n <version>1</version>\n <declaration>\n <field>\n <name>id</name>\n <type>text</type>\n <length>40</length>\n <notnull>true</notnull>\n </field>\n <field>\n <name>snom_phone_id</name>\n <type>text</type>\n <length>40</length>\n <notnull>true</notnull>\n </field>\n <field>\n <name>account_type</name>\n <type>enum</type>\n <value>anyone</value>\n <value>user</value>\n <value>group</value>\n <notnull>true</notnull>\n </field>\n <field>\n <name>account_id</name>\n <type>integer</type>\n <notnull>true</notnull>\n </field>\n <field>\n <name>read_right</name>\n <type>boolean</type>\n <default>false</default>\n </field>\n <field>\n <name>write_right</name>\n <type>boolean</type>\n <default>false</default>\n </field>\n <field>\n <name>dial_right</name>\n <type>boolean</type>\n <default>false</default>\n </field> \n\n <index>\n <name>id</name>\n <primary>true</primary>\n <field>\n <name>id</name>\n </field>\n </index>\n <index>\n <unique>true</unique>\n <name>snom_phone_id-account_type-account_id</name>\n <field>\n <name>snom_phone_id</name>\n </field>\n <field>\n <name>account_type</name>\n </field>\n <field>\n <name>account_id</name>\n </field>\n </index>\n <index>\n <name>snom_phone_acl::snome_phone_id--snome_phones::id</name>\n <field>\n <name>snom_phone_id</name>\n </field>\n <foreign>true</foreign>\n <reference>\n <table>snom_phones</table>\n <field>id</field>\n <ondelete>cascade</ondelete> \n </reference>\n </index>\n </declaration>\n </table>";
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$this->setApplicationVersion('Voipmanager', '0.20');
}
/**
* add the asterisk_redirects table
*/
public function update_1()
{
$tableDefinition = '
<table>
<name>asterisk_redirects</name>
<engine>InnoDB</engine>
<charset>utf8</charset>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>sippeer_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>cfi_mode</name>
<type>enum</type>
<value>off</value>
<value>number</value>
<value>voicemail</value>
<notnull>true</notnull>
</field>
<field>
<name>cfi_number</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>cfb_mode</name>
<type>enum</type>
<value>off</value>
<value>number</value>
<value>voicemail</value>
<notnull>true</notnull>
</field>
<field>
<name>cfb_number</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>cfd_mode</name>
<type>enum</type>
<value>off</value>
<value>number</value>
<value>voicemail</value>
<notnull>true</notnull>
</field>
<field>
<name>cfd_number</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>cfd_time</name>
<type>integer</type>
<length>11</length>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>asterisk_redirects::sippeer_id--asterisk_sip_peers::id</name>
<field>
<name>sippeer_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>asterisk_sip_peers</table>
<field>id</field>
</reference>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$this->setApplicationVersion('Voipmanager', '2.2');
}
/**
* add addressbook_list_member_role table
*
* @return void
*/
public function update_3()
{
$table = Setup_Backend_Schema_Table_Factory::factory('String', '
<table>
<name>adb_list_m_role</name>
<engine>InnoDB</engine>
<charset>utf8</charset>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>list_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>list_role_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>contact_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<index>
<name>adb_list_m_role::list_id--addressbook_lists::id</name>
<field>
<name>list_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook_lists</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
<onupdate>CASCADE</onupdate>
</reference>
</index>
<index>
<name>adb_list_m_role::contact_id--addressbook::id</name>
<field>
<name>contact_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
<onupdate>CASCADE</onupdate>
</reference>
</index>
<index>
<name>adb_list_m_role::list_role_id--addressbook_list_role::id</name>
<field>
<name>list_role_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook_list_role</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
<onupdate>CASCADE</onupdate>
</reference>
</index>
</declaration>
</table>
');
$this->_backend->createTable($table, 'Addressbook', 'addressbook_list_member_role');
$this->setApplicationVersion('Addressbook', '9.4');
}
/**
* update to 1.8
* - add state data table
*/
public function update_7()
{
$tableDefinition = '
<table>
<name>state</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>user_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>data</name>
<type>text</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>user_id</name>
<unique>true</unique>
<field>
<name>user_id</name>
</field>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
Tinebase_Application::getInstance()->addApplicationTable(Tinebase_Application::getInstance()->getApplicationByName('Tinebase'), 'state', 1);
$this->setApplicationVersion('Tinebase', '1.8');
}
/**
* add asterisk_redirects table
*/
protected function _addAsteriskRedirects()
{
$tableDefinition = '
<table>
<name>asterisk_redirects</name>
<engine>InnoDB</engine>
<charset>utf8</charset>
<version>2</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>cfi_mode</name>
<type>text</type>
<length>32</length>
<default>off</default>
<notnull>true</notnull>
</field>
<field>
<name>cfi_number</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>cfb_mode</name>
<type>text</type>
<length>32</length>
<default>off</default>
<notnull>true</notnull>
</field>
<field>
<name>cfb_number</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>cfd_mode</name>
<type>text</type>
<length>32</length>
<default>off</default>
<notnull>true</notnull>
</field>
<field>
<name>cfd_number</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>cfd_time</name>
<type>integer</type>
<length>11</length>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>asterisk_redirect::id--asterisk_sip_peers::id</name>
<field>
<name>id</name>
</field>
<foreign>true</foreign>
<reference>
<table>asterisk_sip_peers</table>
<field>id</field>
</reference>
<ondelete>cascade</ondelete>
<onupdate>cascade</onupdate>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->createTable('asterisk_redirects', $table, 'Voipmanager', 2);
}
/**
* install given application
*
* @param SimpleXMLElement $_xml
* @param array | optional $_options
* @return void
* @throws Tinebase_Exception_Backend_Database
*/
protected function _installApplication(SimpleXMLElement $_xml, $_options = null)
{
if ($this->_backend === NULL) {
throw new Tinebase_Exception_Backend_Database('Need configured and working database backend for install.');
}
try {
if (Setup_Core::isLogLevel(Zend_Log::INFO)) {
Setup_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Installing application: ' . $_xml->name);
}
$createdTables = array();
// traditional xml declaration
if (isset($_xml->tables)) {
foreach ($_xml->tables[0] as $tableXML) {
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableXML);
$this->_createTable($table);
$createdTables[] = $table;
}
} else {
$application = Setup_Core::getApplicationInstance($_xml->name, '', true);
$models = $application->getModels(true);
if (count($models) > 0) {
// create tables using doctrine 2
Setup_SchemaTool::createSchema($_xml->name, $models);
// adopt to old workflow
foreach ($models as $model) {
$modelConfiguration = $model::getConfiguration();
$createdTables[] = (object) array('name' => Tinebase_Helper::array_value('name', $modelConfiguration->getTable()), 'version' => $modelConfiguration->getVersion());
}
}
}
$application = new Tinebase_Model_Application(array('name' => (string) $_xml->name, 'status' => $_xml->status ? (string) $_xml->status : Tinebase_Application::ENABLED, 'order' => $_xml->order ? (string) $_xml->order : 99, 'version' => (string) $_xml->version));
$application = Tinebase_Application::getInstance()->addApplication($application);
// keep track of tables belonging to this application
foreach ($createdTables as $table) {
Tinebase_Application::getInstance()->addApplicationTable($application, (string) $table->name, (int) $table->version);
}
// insert default records
if (isset($_xml->defaultRecords)) {
foreach ($_xml->defaultRecords[0] as $record) {
$this->_backend->execInsertStatement($record);
}
}
// look for import definitions and put them into the db
$this->createImportExportDefinitions($application);
Setup_Initialize::initialize($application, $_options);
} catch (Exception $e) {
Tinebase_Exception::log($e, false);
throw $e;
}
}
/**
* update to 2.8
* - add OpenId tables
*/
public function update_7()
{
$tableDefinition = '
<table>
<name>openid_assoc</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>macfunc</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>secret</name>
<type>text</type>
<length>254</length>
<notnull>true</notnull>
</field>
<field>
<name>expires</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
Tinebase_Application::getInstance()->addApplicationTable(Tinebase_Application::getInstance()->getApplicationByName('Tinebase'), 'openid_assoc', 1);
$tableDefinition = '
<table>
<name>openid_sites</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>user_identity</name>
<type>text</type>
<length>254</length>
<notnull>true</notnull>
</field>
<field>
<name>site</name>
<type>text</type>
<length>254</length>
<notnull>true</notnull>
</field>
<field>
<name>trusted</name>
<type>text</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>user_identity-site</name>
<unique>true</unique>
<length>40</length>
<field>
<name>user_identity</name>
</field>
<field>
<name>site</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
Tinebase_Application::getInstance()->addApplicationTable(Tinebase_Application::getInstance()->getApplicationByName('Tinebase'), 'openid_sites', 1);
$this->setApplicationVersion('Tinebase', '2.8');
}
public function testAddAndDropForeignKey()
{
$referencedTableName = 'phpunit_foreign';
$referencedTableXml = "\n <table>\n <name>{$referencedTableName}</name>\n <version>1</version>\n <declaration>\n <field>\n <name>id</name>\n <type>integer</type>\n <autoincrement>true</autoincrement>\n </field>\n <field>\n <name>name</name>\n <type>text</type>\n <length>128</length>\n <notnull>true</notnull>\n </field>\n <index>\n <name>id</name>\n <primary>true</primary>\n <field>\n <name>id</name>\n </field>\n </index>\n </declaration>\n </table>";
$referencedTable = Setup_Backend_Schema_Table_Factory::factory('Xml', $referencedTableXml);
$this->_tableNames[] = $referencedTableName;
$this->_backend->createTable($referencedTable);
$fieldString = "\n <field>\n <name>foreign_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 <field>\n <name>foreign_id</name>\n </field>\n <foreign>true</foreign>\n <reference>\n <table>{$referencedTableName}</table>\n <field>id</field>\n </reference>\n </index>";
$foreignKey = Setup_Backend_Schema_Index_Factory::factory('Xml', $string);
$this->_backend->addForeignKey($this->_table->name, $foreignKey);
$schema = $this->_backend->getExistingSchema($this->_table->name);
$this->assertEquals(2, count($schema->indices));
$index = $schema->indices[1];
$this->assertEquals('true', $index->foreign);
$this->assertEquals('false', $index->primary);
// $this->assertFalse(empty($index->referencetable));
// $this->assertFalse(empty($index->referencefield));
$db = Tinebase_Core::getDb();
$db->insert(SQL_TABLE_PREFIX . $referencedTableName, array('name' => 'test'));
$db->insert(SQL_TABLE_PREFIX . $this->_table->name, array('name' => 'test', 'foreign_id' => 1));
try {
$db->insert(SQL_TABLE_PREFIX . $this->_table->name, array('name' => 'test', 'foreign_id' => 999));
$this->fail('Expected Zend_Db_Statement_Exception not thrown');
} catch (Zend_Db_Statement_Exception $e) {
//we expected this exception, everything is alright
}
$this->_backend->dropForeignKey($this->_table->name, $index->name);
//now this should work without throwing an Exception
$db->insert(SQL_TABLE_PREFIX . $this->_table->name, array('name' => 'test', 'foreign_id' => 999));
}
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;
}
/**
* install given application
*
* @param SimpleXMLElement $_xml
* @param array | optional $_options
* @return void
* @throws Tinebase_Exception_Backend_Database
*/
protected function _installApplication(SimpleXMLElement $_xml, $_options = null)
{
if ($this->_backend === NULL) {
throw new Tinebase_Exception_Backend_Database('Need configured and working database backend for install.');
}
try {
if (Setup_Core::isLogLevel(Zend_Log::INFO)) {
Setup_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Installing application: ' . $_xml->name);
}
$createdTables = array();
if (isset($_xml->tables)) {
foreach ($_xml->tables[0] as $tableXML) {
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableXML);
$currentTable = $table->name;
if (Setup_Core::isLogLevel(Zend_Log::DEBUG)) {
Setup_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Creating table: ' . $currentTable);
}
try {
$this->_backend->createTable($table);
} catch (Zend_Db_Statement_Exception $zdse) {
throw new Tinebase_Exception_Backend_Database('Could not create table: ' . $zdse->getMessage());
} catch (Zend_Db_Adapter_Exception $zdae) {
throw new Tinebase_Exception_Backend_Database('Could not create table: ' . $zdae->getMessage());
}
$createdTables[] = $table;
}
}
$application = new Tinebase_Model_Application(array('name' => (string) $_xml->name, 'status' => $_xml->status ? (string) $_xml->status : Tinebase_Application::ENABLED, 'order' => $_xml->order ? (string) $_xml->order : 99, 'version' => (string) $_xml->version));
$application = Tinebase_Application::getInstance()->addApplication($application);
// keep track of tables belonging to this application
foreach ($createdTables as $table) {
Tinebase_Application::getInstance()->addApplicationTable($application, (string) $table->name, (int) $table->version);
}
// insert default records
if (isset($_xml->defaultRecords)) {
foreach ($_xml->defaultRecords[0] as $record) {
$this->_backend->execInsertStatement($record);
}
}
// look for import definitions and put them into the db
$this->createImportExportDefinitions($application);
Setup_Initialize::initialize($application, $_options);
} catch (Exception $e) {
Tinebase_Exception::log($e, false);
throw $e;
}
}
public function update_985()
{
if (!$this->_backend->tableExists('bill_batch_job')) {
$tableDefinition = '
<table>
<name>bill_batch_job</name>
<version>1</version>
<engine>InnoDB</engine>
<charset>utf8</charset>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
<notnull>true</notnull>
</field>
<field>
<name>job_id</name>
<type>integer</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>account_id</name>
<type>text</type>
<length>40</length>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>job_nr</name>
<type>text</type>
<length>16</length>
<notnull>false</notnull>
</field>
<field>
<name>job_name1</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>
<field>
<name>job_name2</name>
<type>text</type>
<length>128</length>
<notnull>false</notnull>
</field>
<field>
<name>job_category</name>
<type>enum</type>
<value>DTAEXPORT</value>
<value>PAYMENT</value>
<value>MANUALEXPORT</value>
<value>PREDEFINEDEXPORT</value>
<value>PRINT</value>
<value>DUETASKS</value>
</field>
<field>
<name>job_type</name>
<type>enum</type>
<value>RUNTIME</value>
<value>SCHEDULER</value>
</field>
<field>
<name>job_data</name>
<type>text</type>
<notnull>false</notnull>
</field>
<field>
<name>job_state</name>
<type>enum</type>
<value>TOBEPROCESSED</value>
<value>RUNNING</value>
<value>PROCESSED</value>
<value>ABANDONED</value>
<value>USERCANCELLED</value>
</field>
<field>
<name>job_result_state</name>
<type>enum</type>
<value>UNDEFINED</value>
<value>OK</value>
<value>PARTLYERROR</value>
<value>ERROR</value>
</field>
<field>
<name>on_error</name>
<type>enum</type>
<value>STOP</value>
<value>PROCEED</value>
</field>
<field>
<name>process_info</name>
<type>text</type>
<notnull>false</notnull>
</field>
<field>
<name>error_info</name>
<type>text</type>
<notnull>false</notnull>
</field>
<field>
<name>ok_count</name>
<type>integer</type>
<default>0</default>
<notnull>true</notnull>
</field>
<field>
<name>error_count</name>
<type>integer</type>
<default>0</default>
<notnull>true</notnull>
</field>
<field>
<name>create_datetime</name>
<type>datetime</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>schedule_datetime</name>
<type>datetime</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>start_datetime</name>
<type>datetime</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>end_datetime</name>
<type>datetime</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>process_percentage</name>
<type>integer</type>
<length>4</length>
<default>0</default>
</field>
<field>
<name>task_count</name>
<type>integer</type>
<length>11</length>
<default>0</default>
</field>
<field>
<name>tasks_done</name>
<type>integer</type>
<length>11</length>
<default>0</default>
</field>
<field>
<name>modified_datetime</name>
<type>datetime</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>skip_count</name>
<type>integer</type>
<length>11</length>
<default>0</default>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
}
if (!$this->_backend->tableExists('bill_batch_dta')) {
$tableDefinition = '
<table>
<name>bill_batch_dta</name>
<version>1</version>
<engine>InnoDB</engine>
<charset>utf8</charset>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
<notnull>true</notnull>
</field>
<field>
<name>job_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>contact_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>debitor_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>bank_valid</name>
<type>enum</type>
<value>YES</value>
<value>UNKNOWN</value>
<value>NO</value>
<default>UNKNOWN</default>
</field>
<field>
<name>bank_account_number</name>
<type>text</type>
<length>12</length>
</field>
<field>
<name>bank_code</name>
<type>text</type>
<length>8</length>
</field>
<field>
<name>bank_account_name</name>
<type>text</type>
<length>64</length>
</field>
<field>
<name>bank_name</name>
<type>text</type>
<length>64</length>
</field>
<field>
<name>total_sum</name>
<type>float</type>
<unsigned>false</unsigned>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>count_pos</name>
<type>integer</type>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>skip</name>
<type>boolean</type>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>action_text</name>
<type>text</type>
<length>2048</length>
<notnull>false</notnull>
</field>
<field>
<name>action_data</name>
<type>clob</type>
<notnull>false</notnull>
</field>
<field>
<name>action_type</name>
<type>enum</type>
<value>DRYRUN</value>
<value>REAL</value>
<default>DRYRUN</default>
</field>
<field>
<name>action_state</name>
<type>enum</type>
<value>OPEN</value>
<value>DONE</value>
<value>ERROR</value>
<default>DONE</default>
</field>
<field>
<name>error_info</name>
<type>text</type>
<notnull>false</notnull>
</field>
<field>
<name>created_datetime</name>
<type>datetime</type>
<notnull>true</notnull>
</field>
<field>
<name>valid_datetime</name>
<type>datetime</type>
<notnull>true</notnull>
</field>
<field>
<name>to_process_datetime</name>
<type>datetime</type>
<notnull>true</notnull>
</field>
<field>
<name>process_datetime</name>
<type>datetime</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>created_by_user</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>processed_by_user</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>usage</name>
<type>text</type>
<notnull>false</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
}
if (!$this->_backend->tableExists('bill_batch_dta_item')) {
$tableDefinition = '
<table>
<name>bill_batch_dta_item</name>
<version>1</version>
<engine>InnoDB</engine>
<charset>utf8</charset>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
<notnull>true</notnull>
</field>
<field>
<name>batch_dta_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>erp_context_id</name>
<type>text</type>
<length>24</length>
<notnull>true</notnull>
<default>ERP</default>
</field>
<field>
<name>skip</name>
<type>boolean</type>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>total_sum</name>
<type>float</type>
<unsigned>false</unsigned>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>usage</name>
<type>text</type>
<notnull>false</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
}
$this->setApplicationVersion('Billing', '1.986');
}
/**
* update to 8.26
*
* - add sales_suppliers table
*/
public function update_25()
{
$tableDefinition = '<table>
<name>sales_purchase_invoices</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>number</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<field>
<name>description</name>
<type>text</type>
<length>256</length>
<notnull>true</notnull>
</field>
<field>
<name>date</name>
<type>date</type>
</field>
<field>
<name>due_in</name>
<type>integer</type>
<notnull>true</notnull>
<length>10</length>
</field>
<field>
<name>due_at</name>
<type>date</type>
<notnull>true</notnull>
</field>
<field>
<name>pay_at</name>
<type>date</type>
</field>
<field>
<name>overdue_at</name>
<type>date</type>
</field>
<field>
<name>is_payed</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>payed_at</name>
<type>datetime</type>
</field>
<field>
<name>dunned_at</name>
<type>datetime</type>
</field>
<field>
<name>payment_method</name>
<type>text</type>
<length>254</length>
</field>
<field>
<name>discount</name>
<type>integer</type>
<notnull>true</notnull>
<length>10</length>
</field>
<field>
<name>discount_until</name>
<type>date</type>
</field>
<field>
<name>is_approved</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>price_net</name>
<type>float</type>
<notnull>false</notnull>
</field>
<field>
<name>price_gross</name>
<type>float</type>
<notnull>false</notnull>
</field>
<field>
<name>price_tax</name>
<type>float</type>
<notnull>false</notnull>
</field>
<field>
<name>sales_tax</name>
<type>float</type>
<notnull>false</notnull>
</field>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<field>
<name>seq</name>
<type>integer</type>
<notnull>true</notnull>
<default>0</default>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>';
$this->createTable('sales_purchase_invoices', Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition), 'Sales');
// create keyfield config
$cb = new Tinebase_Backend_Sql(array('modelName' => 'Tinebase_Model_Config', 'tableName' => 'config'));
$appId = Tinebase_Application::getInstance()->getApplicationByName('Sales')->getId();
// create payment types config
$tc = array('name' => Sales_Config::PAYMENT_METHODS, 'records' => array(array('id' => 'BANK TRANSFER', 'value' => 'Bank transfer', 'system' => true), array('id' => 'DIRECT DEBIT', 'value' => 'Direct debit', 'system' => true)));
$cb->create(new Tinebase_Model_Config(array('application_id' => $appId, 'name' => Sales_Config::PAYMENT_METHODS, 'value' => json_encode($tc))));
self::createDefaultFavoritesForSub24();
Setup_Controller::getInstance()->createImportExportDefinitions(Tinebase_Application::getInstance()->getApplicationByName('Sales'));
$this->setApplicationVersion('Sales', '8.26');
}
/**
* update to 0.6
* add Expressomail domain config file parameter ENABLEMAILDIREXPORT
* add Expressomail table 'expressomail_maildirexport_queue'
* insert Expressomail applications table new record
*
* @return void
* @throws Tinebase_Exception_NotFound|Tinebase_Exception_Backend_Database|Tinebase_Exception
*/
public function update_5()
{
//Add new setup entry at domain setup configuration file
$settings = Expressomail_Config::getInstance()->get(Expressomail_Config::EXPRESSOMAIL_SETTINGS);
if (!array_key_exists(Expressomail_Config::ENABLEMAILDIREXPORT, $settings)) {
try {
$properties = Expressomail_Config::getProperties();
$property = $properties[Expressomail_Config::ENABLEMAILDIREXPORT];
$defaultValue = $property['default'];
$settings[Expressomail_Config::ENABLEMAILDIREXPORT] = $defaultValue;
Expressomail_Controller::getInstance()->saveConfigSettings($settings);
Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 successfully added new entry at domain configuration file: "' . $settings[Expressomail_Config::ENABLEMAILDIREXPORT] . '"');
} catch (Tinebase_Exception_NotFound $tenf) {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 fails to add new domain entry: "' . $settings[Expressomail_Config::ENABLEMAILDIREXPORT] . '"');
}
} else {
Tinebase_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 attempted to created a new domain config file entry, but it already exists! "' . Expressomail_Config::ENABLEMAILDIREXPORT . '"');
}
//Add table used by queue script for export mail dir data
$newTable = 'expressomail_backup_scheduler';
if (!$this->_backend->tableExists($newTable)) {
try {
$table = Setup_Backend_Schema_Table_Factory::factory('String', '
<table>
<name>' . $newTable . '</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>account_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>folder</name>
<type>text</type>
<length>250</length>
<notnull>true</notnull>
</field>
<field>
<name>scheduler_time</name>
<type>datetime</type>
<notnull>true</notnull>
</field>
<field>
<name>start_time</name>
<type>datetime</type>
</field>
<field>
<name>end_time</name>
<type>datetime</type>
</field>
<field>
<name>status</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
<default>PENDING</default>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>priority</name>
<type>integer</type>
<notnull>true</notnull>
<default>5</default>
</field>
<field>
<name>expunged_time</name>
<type>datetime</type>
</field>
<index>
<name>id</name>
<field>
<name>id</name>
</field>
</index>
<index>
<name>account_id</name>
<field>
<name>account_id</name>
</field>
</index>
<index>
<name>folder</name>
<field>
<name>folder</name>
</field>
</index>
<index>
<name>status</name>
<field>
<name>status</name>
</field>
</index>
<index>
<name>scheduler_time</name>
<field>
<name>scheduler_time</name>
</field>
</index>
<index>
<name>is_deleted</name>
<field>
<name>is_deleted</name>
</field>
</index>
<index>
<name>priority</name>
<field>
<name>priority</name>
</field>
</index>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>account_id--folder--status--is_deleted</name>
<unique>true</unique>
<field>
<name>account_id</name>
</field>
<field>
<name>folder</name>
</field>
<field>
<name>status</name>
</field>
<field>
<name>is_deleted</name>
</field>
</index>
<index>
<name>' . $newTable . '::account_id--accounts::id</name>
<field>
<name>account_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>accounts</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
</reference>
</index>
</declaration>
</table>
');
$this->_backend->createTable($table);
Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade 0.6 successfully created a new table at data base schema: "' . $newTable . '"');
//Insert new Expressomail application table record
try {
//Fetch application id to perform insert operation
$selectId = $this->_db->select()->from(SQL_TABLE_PREFIX . 'applications', 'id')->where($this->_db->quoteIdentifier('name') . ' = ?', 'Expressomail');
$resultId = $this->_db->fetchAll($selectId);
$appId = $resultId[0]["id"];
//Check basic data consistence at table application
if (count($resultId) != 1) {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 inconsistent data at applications table for appName "ExpressoMail"');
throw new Tinebase_Exception_Backend_Database("Application 'Expressomail' inconsistent data at applications table!");
}
//Try to find out if some corrupted event have already iserted this data before
$selectAppTable = $this->_db->select()->from(SQL_TABLE_PREFIX . 'application_tables', 'name')->where($this->_db->quoteIdentifier('name') . ' = ?', $newTable);
$resultAppTable = $this->_db->fetchAll($selectAppTable);
if (count($resultAppTable) != 0) {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 inconsistent data at applications table for appName "ExpressoMail": the new table "' . $newTable . '" already is there!');
throw new Tinebase_Exception_Backend_Database("Application 'Expressomail' inconsistent data at applications table: new table '{$newTable}' relationship already exists at 'application_tables'!");
}
//Follows to add new record
$appRecord = new SimpleXMLElement("\n <record>\n <table>\n <name>application_tables</name>\n </table>\n <field>\n <name>application_id</name>\n <value>{$appId}</value>\n </field>\n <field>\n <name>name</name>\n <value>{$newTable}</value>\n </field>\n <field>\n <name>version</name>\n <value>1</value>\n </field>\n </record>\n ");
//Performs insert new record
$this->_backend->execInsertStatement($appRecord);
Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 successfully inserted a new record at "application_tables" table: "' . $newTable . '"');
try {
//Fetch update action
$this->setApplicationVersion('Expressomail', '0.6');
Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 successfully finished.');
} catch (Tinebase_Exception $updateException) {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 fails to be up to dated: ' . $updateException->getMessage());
throw new Tinebase_Exception_Backend_Database("Application 'Expressomail' fails to update to 0.6 at 'setApplicationVersion':" . $updateException->getMessage());
}
} catch (Tinebase_Exception_Backend_Database $insertAppException) {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 fails to insert new applications table: ' . $insertAppException->getMessage());
throw new Tinebase_Exception_Backend_Database("Application 'Expressomail' fails to update to 0.6 at insert applicatons table new record:" . $insertAppException->getMessage());
}
} catch (Tinebase_Exception_Backend_Database $createException) {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 fails to create new table: ' . $createException->getMessage());
throw new Tinebase_Exception_Backend_Database("Application 'Expressomail' fails to update to 0.6 at create new table:" . $createException->getMessage());
}
} else {
Tinebase_Core::getLogger()->error(__METHOD__ . '::' . __LINE__ . ' ExpressoMail upgrade to 0.6 fails to process new table creation: table "' . $newTable . '" already exists at application schema database!');
throw new Tinebase_Exception_Backend_Database("Application 'Expressomail' fails to update to 0.6: base table '{$newTable}' already exists at database schema!");
}
}
/**
* update to 3.7
* - create table addressbook_lists and addressbook_list_members
*/
public function update_6()
{
$table = Setup_Backend_Schema_Table_Factory::factory('String', '
<table>
<name>addressbook_lists</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>container_id</name>
<type>integer</type>
<notnull>false</notnull>
</field>
<field>
<name>name</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
</field>
<field>
<name>description</name>
<type>text</type>
<length>255</length>
<notnull>false</notnull>
</field>
<field>
<name>email</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>
<field>
<name>type</name>
<type>enum</type>
<value>list</value>
<value>group</value>
<notnull>true</notnull>
<default>list</default>
</field>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>name</name>
<unique>true</unique>
<field>
<name>name</name>
</field>
</index>
</declaration>
</table>
');
$this->_backend->createTable($table);
$table = Setup_Backend_Schema_Table_Factory::factory('String', '
<table>
<name>addressbook_list_members</name>
<version>1</version>
<declaration>
<field>
<name>list_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>contact_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<index>
<name>list_id-contact_id</name>
<primary>true</primary>
<field>
<name>list_id</name>
</field>
<field>
<name>contact_id</name>
</field>
</index>
<index>
<name>contact_id</name>
<field>
<name>contact_id</name>
</field>
</index>
<index>
<name>adressbook_list_members::list_id--addressbook_lists::id</name>
<field>
<name>list_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook_lists</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
<onupdate>CASCADE</onupdate>
</reference>
</index>
<index>
<name>addressbook_list_members::contact_id--addressbook::id</name>
<field>
<name>contact_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
<onupdate>CASCADE</onupdate>
</reference>
</index>
</declaration>
</table>
');
$this->_backend->createTable($table);
Tinebase_Application::getInstance()->addApplicationTable(Tinebase_Application::getInstance()->getApplicationByName('Addressbook'), 'addressbook_lists', 1);
Tinebase_Application::getInstance()->addApplicationTable(Tinebase_Application::getInstance()->getApplicationByName('Addressbook'), 'addressbook_list_members', 1);
$this->setApplicationVersion('Addressbook', '3.7');
}
/**
* add salutation_id field and table
*
*/
public function update_4()
{
$declaration = new Setup_Backend_Schema_Field_Xml('
<field>
<name>salutation_id</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>');
try {
$this->_backend->addCol('addressbook', $declaration);
} catch (Exception $e) {
echo "salutation_id already exists.\n";
}
$tableDefinition = '
<table>
<name>addressbook_salutations</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>name</name>
<type>text</type>
<length>32</length>
<notnull>true</notnull>
</field>
<field>
<name>gender</name>
<type>enum</type>
<value>male</value>
<value>female</value>
<value>other</value>
<notnull>true</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<unique>true</unique>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
try {
$this->_backend->createTable($table);
} catch (Exception $e) {
echo "salutation table already exists.\n";
}
// add initial values
$backend = new Addressbook_Backend_Salutation();
$maleSalutation = new Addressbook_Model_Salutation(array('id' => 1, 'name' => 'Mr', 'gender' => Addressbook_Model_Salutation::GENDER_MALE));
$backend->create($maleSalutation);
$femaleSalutation = new Addressbook_Model_Salutation(array('id' => 2, 'name' => 'Ms', 'gender' => Addressbook_Model_Salutation::GENDER_FEMALE));
$backend->create($femaleSalutation);
$companySalutation = new Addressbook_Model_Salutation(array('id' => 3, 'name' => 'Company', 'gender' => Addressbook_Model_Salutation::GENDER_OTHER));
$backend->create($companySalutation);
$this->setApplicationVersion('Addressbook', '0.5');
}
/**
* add table for folders sent to client(foldersync command)
*
*/
public function update_4()
{
$tableDefinition = '
<table>
<name>acsync_folder</name>
<engine>InnoDB</engine>
<charset>utf8</charset>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>device_id</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>
<field>
<name>class</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>
<field>
<name>folderid</name>
<type>text</type>
<length>254</length>
<notnull>false</notnull>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>device_id--class--folderid</name>
<unique>true</unique>
<field>
<name>device_id</name>
</field>
<field>
<name>class</name>
</field>
<field>
<name>folderid</name>
</field>
</index>
<index>
<name>acsync_folder::device_id--acsync_device::id</name>
<field>
<name>device_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>acsync_device</table>
<field>id</field>
</reference>
<ondelete>cascade</ondelete>
<onupdate>cascade</onupdate>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
$this->setApplicationVersion('ActiveSync', '0.5');
}
/**
* update to 0.28
* - repair db charset for users with non default utf8 client charset
*/
public function update_27()
{
$config = Setup_Core::get(Setup_Core::CONFIG);
$tableprefix = $config->database->tableprefix;
// have a second db connection with default charset
$orgDb = Zend_Db::factory('Pdo_Mysql', $config->database->toArray());
// fix for signed / unsigned problem
$declaration = new Setup_Backend_Schema_Field_Xml('
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
</field>
');
$this->_backend->alterCol('addressbook', $declaration);
/** addressbook: store image in separate table **/
$tableDefinition = '
<table>
<name>addressbook_image</name>
<version>1</version>
<declaration>
<field>
<name>contact_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>image</name>
<type>blob</type>
</field>
<index>
<name>contact_id</name>
<primary>true</primary>
<field>
<name>contact_id</name>
</field>
</index>
<index>
<name>addressbook_image::contact_id-addressbook::id</name>
<field>
<name>contact_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook</table>
<field>id</field>
<ondelete>CASCADE</ondelete>
</reference>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$select = $orgDb->select()->from("{$tableprefix}addressbook", array('id'))->where($orgDb->quoteIdentifier('jpegphoto') . " IS NOT NULL");
$contactIds = $orgDb->fetchAll($select);
foreach ($contactIds as $contactId) {
$contactId = $contactId['id'];
$select = $orgDb->select()->from("{$tableprefix}addressbook", array('id', 'jpegphoto'))->where($orgDb->quoteInto($orgDb->quoteIdentifier('id') . ' = ?', $contactId));
$imageData = $orgDb->fetchRow($select);
$orgDb->insert("{$tableprefix}addressbook_image", array('contact_id' => $imageData['id'], 'image' => base64_encode($imageData['jpegphoto'])));
}
$this->_backend->dropCol('addressbook', 'jpegphoto');
/** convert serialized object into json objects **/
$select = $orgDb->select()->from("{$tableprefix}filter", array('id', 'filters'));
$filters = $orgDb->fetchAll($select);
foreach ($filters as $filter) {
$filterObject = unserialize($filter['filters']);
$orgDb->update("{$tableprefix}filter", array('filters' => Zend_Json::encode($filterObject)), $orgDb->quoteInto($orgDb->quoteIdentifier('id') . ' = ?', $filter['id']));
}
/** convert db contenets for installations which had a clientcharset != utf8 **/
$originalCharset = Tinebase_Helper::array_value('Value', Tinebase_Helper::array_value(0, $orgDb->query("SHOW VARIABLES LIKE 'character_set_client'")->fetchAll()));
if (strtolower($originalCharset) != 'utf8') {
$this->_db->query("SET FOREIGN_KEY_CHECKS=0");
$orgDb->query("SET FOREIGN_KEY_CHECKS=0");
// build the list of tables to convert
$tables = array();
$rawTables = $this->_db->query("SHOW TABLES")->fetchAll();
foreach ($rawTables as $rawTable) {
$tableName = array_values($rawTable);
$tableName = $tableName[0];
if (preg_match("/^{$tableprefix}/", $tableName) && $tableName != "{$tableprefix}addressbook_image") {
$tables[] = $tableName;
}
}
// the actual charset conversion is done by the db.
foreach ($tables as $tableName) {
Setup_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Converting table ' . $tableName);
//$this->_db->query("SET character_set_client = '$originalCharset'");
$select = $orgDb->select()->from($tableName);
$result = $orgDb->fetchAll($select);
$orgDb->query("TRUNCATE TABLE " . $this->_db->quoteIdentifier($tableName));
//$this->_db->query("SET character_set_client = 'utf8'");
foreach ($result as $row) {
try {
$this->_db->insert($tableName, $row);
} catch (Zend_Db_Statement_Exception $zdse) {
Setup_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' ' . $zdse->getMessage());
// try to convert strings if failure
if (preg_match('/(description|title|note|old_value|org_name|adr_one_street)/', $zdse->getMessage(), $match)) {
$field = $match[1];
Setup_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' Converting field ' . $field . (isset($row['id']) || array_key_exists('id', $row) ? ' of record ' . $row['id'] : ''));
$row[$field] = utf8_encode($row[$field]);
$this->_db->insert($tableName, $row);
} else {
Setup_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' Could not convert field');
throw $zdse;
}
}
}
}
$this->_db->query("SET FOREIGN_KEY_CHECKS=1");
}
$orgDb->closeConnection();
$this->setApplicationVersion('Tinebase', '0.28');
}
/**
* adds import table
*/
public function update_5()
{
$tableDefinition = '
<table>
<name>import</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>80</length>
<notnull>true</notnull>
</field>
<field>
<name>timestamp</name>
<type>datetime</type>
</field>
<field>
<name>user_id</name>
<type>text</type>
<length>80</length>
<notnull>true</notnull>
</field>
<field>
<name>model</name>
<type>text</type>
<length>80</length>
<notnull>true</notnull>
</field>
<field>
<name>application_id</name>
<type>text</type>
<length>80</length>
<notnull>true</notnull>
</field>
<field>
<name>synctoken</name>
<type>text</type>
<length>80</length>
</field>
<field>
<name>container_id</name>
<length>80</length>
<type>text</type>
</field>
<field>
<name>sourcetype</name>
<type>text</type>
<notnull>true</notnull>
</field>
<field>
<name>interval</name>
<type>text</type>
</field>
<field>
<name>source</name>
<type>text</type>
</field>
<field>
<name>options</name>
<type>text</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<notnull>true</notnull>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<field>
<name>seq</name>
<type>integer</type>
<notnull>true</notnull>
<default>0</default>
</field>
<index>
<name>import::application_id--applications::id</name>
<field>
<name>application_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>applications</table>
<field>id</field>
</reference>
</index>
<index>
<name>import::user_id--accounts::id</name>
<field>
<name>user_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>accounts</table>
<field>id</field>
</reference>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$scheduler = Tinebase_Core::getScheduler();
Tinebase_Scheduler_Task::addImportTask($scheduler);
$this->setApplicationVersion('Tinebase', '8.6');
}
/**
* update to version 2.0
* - add resources table
*/
public function update_3()
{
$tableDefinition = '
<table>
<name>cal_resources</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<field>
<name>seq</name>
<type>integer</type>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>name</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
</field>
<field>
<name>description</name>
<type>text</type>
</field>
<field>
<name>email</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
</field>
<field>
<name>is_location</name>
<type>boolean</type>
<default>false</default>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>name</name>
<field>
<name>name</name>
</field>
</index>
<index>
<name>email</name>
<field>
<name>email</name>
</field>
</index>
<index>
<name>is_location</name>
<field>
<name>is_location</name>
</field>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$calendarApp = Tinebase_Application::getInstance()->getApplicationByName('Calendar');
Tinebase_Application::getInstance()->addApplicationTable($calendarApp, 'cal_resources', 1);
$this->setApplicationVersion('Calendar', '2.0');
}
/**
* Test length name for table name and column name (Oracle Database limitation)
* Table name is less than 30 at least since Oracle 7
*
* @see 0007452: use json encoded array for saving of policy settings
*/
public function testSetupXML()
{
$applications = Tinebase_Application::getInstance()->getApplications();
foreach ($applications->name as $applicationName) {
// skip ActiveSync
// @todo remove that when #7452 is resolved
if ($applicationName === 'ActiveSync') {
continue;
}
$xml = Setup_Controller::getInstance()->getSetupXml($applicationName);
if (isset($xml->tables)) {
foreach ($xml->tables[0] as $tableXML) {
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableXML);
$currentTable = $table->name;
$this->assertLessThan(30, strlen($currentTable), $applicationName . " -> " . $table->name . " (" . strlen($currentTable) . ")");
foreach ($table->fields as $field) {
$this->assertLessThan(31, strlen($field->name), $applicationName . " -> " . $table->name . " (" . strlen($field->name) . ")");
}
}
}
}
}
/**
* update from 5.5 -> 5.6
* - added table cost_centers
*
* @return void
*/
public function update_5()
{
$tableDefinition = '
<table>
<name>sales_cost_centers</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>number</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<field>
<name>remark</name>
<type>text</type>
<length>255</length>
<notnull>false</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>number</name>
<unique>true</unique>
<field>
<name>number</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table, 'Sales', 'sales_cost_centers');
$this->setApplicationVersion('Sales', '5.6');
}
/**
* - create sales product table
* - copy products from metacrm_products to new table
*
*/
public function update_1()
{
$tableDefinition = '
<table>
<name>sales_products</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>name</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
</field>
<field>
<name>description</name>
<type>text</type>
<notnull>false</notnull>
</field>
<field>
<name>price</name>
<type>float</type>
<notnull>false</notnull>
</field>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
Tinebase_Application::getInstance()->addApplicationTable(Tinebase_Application::getInstance()->getApplicationByName('Sales'), 'sales_products', 1);
// check if crm is installed first
if (Setup_Controller::getInstance()->isInstalled('Crm')) {
// add products from crm
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'metacrm_products');
$stmt = $this->_db->query($select);
$queryResult = $stmt->fetchAll();
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . print_r($queryResult, TRUE));
}
// insert values into products table
$productsBackend = new Tinebase_Backend_Sql(array('modelName' => 'Sales_Model_Product', 'tableName' => 'sales_products'));
foreach ($queryResult as $row) {
$products = new Sales_Model_Product(array('id' => $row['id'], 'name' => $row['productsource'], 'price' => $row['price']));
$productsBackend->create($products);
}
}
$this->setApplicationVersion('Sales', '2.2');
}
/**
* add new table for phone history
*
*/
public function update_3()
{
$tableDefinition = '
<table>
<name>phone_callhistory</name>
<version>1</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>line_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>phone_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>call_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>start</name>
<type>datetime</type>
</field>
<field>
<name>connected</name>
<type>datetime</type>
</field>
<field>
<name>disconnected</name>
<type>datetime</type>
</field>
<field>
<name>duration</name>
<type>integer</type>
</field>
<field>
<name>ringing</name>
<type>integer</type>
</field>
<field>
<name>direction</name>
<type>enum</type>
<value>in</value>
<value>out</value>
<default>in</default>
<notnull>true</notnull>
</field>
<field>
<name>source</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<field>
<name>destination</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>call_id-phone_id</name>
<unique>true</unique>
<field>
<name>call_id</name>
</field>
<field>
<name>phone_id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
$this->setApplicationVersion('Phone', '0.4');
}
public function testLongTableName()
{
//Tests table without sequence
$tableXml = '
<table>
<name>long_name_0123456789_0123456789</name>
<version>1</version>
<declaration>
<field>
<name>name</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
</field>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableXml);
$this->_tableNames[] = $table->name;
$this->setExpectedException('Zend_Db_Statement_Exception', '972');
//oracle identifiers cannot be longer than 30 characters
$this->_backend->createTable($table);
}
/**
* creates the new tables
*/
public function update_4()
{
$tableDefinition = '<table>
<name>sipgate_account</name>
<version>0</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>accounttype</name>
<type>text</type>
<length>10</length>
<notnull>true</notnull>
</field>
<field>
<name>type</name>
<type>text</type>
<length>10</length>
<notnull>true</notnull>
<default>shared</default>
</field>
<field>
<name>credential_id</name>
<type>text</type>
<length>40</length>
<notnull>false</notnull>
</field>
<field>
<name>description</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<field>
<name>username</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>
<field>
<name>password</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
</field>
<field>
<name>is_valid</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<field>
<name>mobile_number</name>
<type>text</type>
<length>64</length>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
$tableDefinition = '<table>
<name>sipgate_line</name>
<version>0</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>account_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>user_id</name>
<type>text</type>
<length>40</length>
<notnull>false</notnull>
</field>
<field>
<name>uri_alias</name>
<type>text</type>
<length>256</length>
</field>
<field>
<name>sip_uri</name>
<type>text</type>
<length>256</length>
</field>
<field>
<name>e164_out</name>
<type>text</type>
<length>256</length>
</field>
<field>
<name>e164_in</name>
<type>text</type>
<length>256</length>
</field>
<field>
<name>tos</name>
<type>text</type>
<length>10</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_sync</name>
<type>datetime</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>sipgate_line::account_id--sipgate_account::id</name>
<field>
<name>account_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>sipgate_account</table>
<field>id</field>
</reference>
</index>
<index>
<name>sipgate_line::user_id--sipgate_user::id</name>
<field>
<name>user_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>accounts</table>
<field>id</field>
</reference>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
$tableDefinition = '<table>
<name>sipgate_connection</name>
<version>0</version>
<declaration>
<field>
<name>id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>entry_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
</field>
<field>
<name>tos</name>
<type>text</type>
<length>10</length>
<notnull>true</notnull>
</field>
<field>
<name>local_uri</name>
<type>text</type>
<length>256</length>
<notnull>true</notnull>
</field>
<field>
<name>remote_uri</name>
<type>text</type>
<length>256</length>
<notnull>true</notnull>
</field>
<field>
<name>local_number</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
</field>
<field>
<name>remote_number</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
</field>
<field>
<name>status</name>
<type>text</type>
<length>8</length>
<notnull>true</notnull>
</field>
<field>
<name>line_id</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>timestamp</name>
<type>datetime</type>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>tarif</name>
<type>text</type>
<length>256</length>
</field>
<field>
<name>duration</name>
<type>integer</type>
</field>
<field>
<name>units_charged</name>
<type>integer</type>
</field>
<field>
<name>price_unit</name>
<type>float</type>
</field>
<field>
<name>price_total</name>
<type>float</type>
</field>
<field>
<name>ticks_a</name>
<type>integer</type>
<length>3</length>
</field>
<field>
<name>ticks_b</name>
<type>integer</type>
<length>3</length>
</field>
<field>
<name>contact_id</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>contact_name</name>
<type>text</type>
<length>128</length>
<notnull>false</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
<index>
<name>entry_id</name>
<field>
<name>entry_id</name>
</field>
</index>
<index>
<name>creation_time</name>
<field>
<name>creation_time</name>
</field>
</index>
<index>
<name>timestamp</name>
<field>
<name>timestamp</name>
</field>
</index>
<index>
<name>connection::contact_id--contact::id</name>
<field>
<name>contact_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>addressbook</table>
<field>id</field>
</reference>
</index>
<index>
<name>connection::line_id--line::id</name>
<field>
<name>line_id</name>
</field>
<foreign>true</foreign>
<reference>
<table>sipgate_line</table>
<field>id</field>
</reference>
</index>
</declaration>
</table>';
$table = Setup_Backend_Schema_Table_Factory::factory('Xml', $tableDefinition);
$this->_backend->createTable($table);
$this->setApplicationVersion('Sipgate', '1.5');
}
public function update_18()
{
$this->_backend->dropCol('bill_bank_account', 'has_sepa_mandate');
$this->_backend->dropCol('bill_bank_account', 'sepa_mandate_from');
$this->_backend->dropCol('bill_bank_account', 'sepa_mandate_until');
$this->_backend->dropCol('bill_bank_account', 'sepa_last_usage_date');
// keep iban nullable as long there are bank accounts allowed
// to have the old declaration number, bank_code
$declaration = new Setup_Backend_Schema_Field_Xml('
<field>
<name>iban</name>
<type>text</type>
<length>64</length>
<notnull>false</notnull>
<default>null</default>
</field> ');
$this->_backend->alterCol('bill_bank_account', $declaration);
$declaration = new Setup_Backend_Schema_Field_Xml('
<field>
<name>is_iban_improved</name>
<type>boolean</type>
<default>false</default>
</field> ');
$this->_backend->addCol('bill_bank_account', $declaration);
if (!$this->_backend->tableExists('bill_sepa_mandate')) {
$tableDefinition = '
<table>
<name>bill_sepa_mandate</name>
<version>1</version>
<engine>InnoDB</engine>
<charset>utf8</charset>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
</field>
<field>
<name>contact_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>bank_account_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>sepa_creditor_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>mandate_ident</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<field>
<name>signature_date</name>
<type>date</type>
<notnull>true</notnull>
</field>
<field>
<name>last_usage_date</name>
<type>date</type>
<notnull>false</notnull>
<default>null</default>
</field>
<field>
<name>is_single</name>
<type>boolean</type>
<notnull>true</notnull>
<default>true</default>
</field>
<field>
<name>is_last</name>
<type>boolean</type>
<notnull>true</notnull>
<default>false</default>
</field>
<field>
<name>is_valid</name>
<type>boolean</type>
<notnull>true</notnull>
<default>false</default>
</field>
<field>
<name>created_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>creation_time</name>
<type>datetime</type>
</field>
<field>
<name>last_modified_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>last_modified_time</name>
<type>datetime</type>
</field>
<field>
<name>is_deleted</name>
<type>boolean</type>
<default>false</default>
</field>
<field>
<name>deleted_by</name>
<type>text</type>
<length>40</length>
</field>
<field>
<name>deleted_time</name>
<type>datetime</type>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
}
if (!$this->_backend->tableExists('bill_sepa_creditor')) {
$tableDefinition = '
<table>
<name>bill_sepa_creditor</name>
<version>1</version>
<engine>InnoDB</engine>
<charset>utf8</charset>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<autoincrement>true</autoincrement>
</field>
<field>
<name>contact_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>bank_account_id</name>
<type>integer</type>
<notnull>true</notnull>
</field>
<field>
<name>sepa_creditor_ident</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<field>
<name>creditor_name</name>
<type>text</type>
<length>64</length>
<notnull>true</notnull>
</field>
<index>
<name>id</name>
<primary>true</primary>
<field>
<name>id</name>
</field>
</index>
</declaration>
</table>
';
$table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
$this->_backend->createTable($table);
}
$this->setApplicationVersion('Billing', '2.19');
}
