/** * 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'); }