0011398: add own_id index to relations table
[tine20] / tine20 / Tinebase / Setup / Update / Release8.php
1 <?php
2 /**
3  * Tine 2.0
4  *
5  * @package     Tinebase
6  * @subpackage  Setup
7  * @license     http://www.gnu.org/licenses/agpl.html AGPL3
8  * @copyright   Copyright (c) 2013-2014 Metaways Infosystems GmbH (http://www.metaways.de)
9  * @author      Philipp Schüle <p.schuele@metaways.de>
10  */
11 class Tinebase_Setup_Update_Release8 extends Setup_Update_Abstract
12 {
13     /**
14      * update to 8.1
15      * 
16      * @see 0009152: saving of record fails because of too many relations
17      */
18     public function update_0()
19     {
20         $valueFields = array('old_value', 'new_value');
21         foreach ($valueFields as $field) {
22             
23             // check schema, only change if type == text
24             $typeMapping = $this->_backend->getTypeMapping('text');
25             $schema = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . 'timemachine_modlog', $this->_backend->getDb());
26             
27             if ($schema[$field]['DATA_TYPE'] === $typeMapping['defaultType']) {
28                 if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__
29                     . ' Old column type (' . $schema[$field]['DATA_TYPE'] . ') is going to be altered to clob');
30                 
31                 $declaration = new Setup_Backend_Schema_Field_Xml('
32                     <field>
33                         <name>' . $field . '</name>
34                         <type>clob</type>
35                     </field>
36                 ');
37             
38                 $this->_backend->alterCol('timemachine_modlog', $declaration);
39             }
40         }
41         $this->setTableVersion('timemachine_modlog', '3');
42         $this->setApplicationVersion('Tinebase', '8.1');
43     }
44
45     /**
46      * update to 8.2
47      * 
48      * @see 0009644: remove user registration
49      */
50     public function update_1()
51     {
52         if ($this->_backend->tableExists('registrations')) {
53             $this->dropTable('registrations');
54         }
55         
56         if ($this->_backend->tableExists('registration_invitation')) {
57             $this->dropTable('registration_invitation');
58         }
59         
60         $this->setApplicationVersion('Tinebase', '8.2');
61     }
62     
63     /**
64      * update to 8.3
65      * - update 256 char fields
66      * 
67      * @see 0008070: check index lengths
68      */
69     public function update_2()
70     {
71         $columns = array("container" => array(
72                             "name" => 'true'
73                             ),
74                         "note_types" => array(
75                             "icon" => 'true',
76                             "description" => 'null'
77                             ),
78                         "tags" => array(
79                             "name" => 'null',
80                             "description" => 'null'
81                             ),
82                         "accounts" => array(
83                             "home_dir" => 'false'
84                             )
85                         );
86         $this->truncateTextColumn($columns, 255);
87         $this->setTableVersion('container', '9');
88         $this->setTableVersion('note_types', '3');
89         $this->setTableVersion('tags', '7');
90         $this->setTableVersion('accounts', '10');
91         $this->setApplicationVersion('Tinebase', '8.3');
92     }
93     
94     /**
95      * - add filter acl
96      * - update current filter (add default grants: user for personal favorites, Admin group for shared favorites)
97      * 
98      * @see 0009610: shared favorites acl
99      */
100     public function update_3()
101     {
102         $this->_addFilterAclTable();
103         $this->_addGrantsToExistingFilters();
104         $this->setApplicationVersion('Tinebase', '8.4');
105     }
106     
107     /**
108      * add filter acl table
109      */
110     protected function _addFilterAclTable()
111     {
112         $xml = $declaration = new Setup_Backend_Schema_Table_Xml('<table>
113             <name>filter_acl</name>
114             <version>1</version>
115             <declaration>
116                 <field>
117                     <name>id</name>
118                     <type>text</type>
119                     <length>40</length>
120                     <notnull>true</notnull>
121                 </field>
122                 <field>
123                     <name>record_id</name>
124                     <type>text</type>
125                     <length>40</length>
126                     <notnull>true</notnull>
127                 </field>
128                 <field>
129                     <name>account_type</name>
130                     <type>text</type>
131                     <length>32</length>
132                     <default>user</default>
133                     <notnull>true</notnull>
134                 </field>
135                 <field>
136                     <name>account_id</name>
137                     <type>text</type>
138                     <length>40</length>
139                     <notnull>true</notnull>
140                 </field>
141                 <field>
142                     <name>account_grant</name>
143                     <type>text</type>
144                     <length>40</length>
145                     <notnull>true</notnull>
146                 </field>
147                 <index>
148                     <name>record_id-account-type-account_id-account_grant</name>
149                     <primary>true</primary>
150                     <field>
151                         <name>id</name>
152                     </field>
153                     <field>
154                         <name>record_id</name>
155                     </field>
156                     <field>
157                         <name>account_type</name>
158                     </field>
159                     <field>
160                         <name>account_id</name>
161                     </field>
162                     <field>
163                         <name>account_grant</name>
164                     </field>
165                 </index>
166                 <index>
167                     <name>id-account_type-account_id</name>
168                     <field>
169                         <name>record_id</name>
170                     </field>
171                     <field>
172                         <name>account_type</name>
173                     </field>
174                     <field>
175                         <name>account_id</name>
176                     </field>
177                 </index>
178                 <index>
179                     <name>filter_acl::record_id--filter::id</name>
180                     <field>
181                         <name>record_id</name>
182                     </field>
183                     <foreign>true</foreign>
184                     <reference>
185                         <table>filter</table>
186                         <field>id</field>
187                         <ondelete>cascade</ondelete>
188                     </reference>
189                 </index>
190             </declaration>
191         </table>');
192         
193         $this->createTable('filter_acl', $declaration);
194     }
195     
196     /**
197      * add default grants to existing filters
198      */
199     protected function _addGrantsToExistingFilters()
200     {
201         $pfBackend = new Tinebase_PersistentFilter_Backend_Sql();
202         $filters = $pfBackend->getAll();
203         $pfGrantsBackend = new Tinebase_Backend_Sql_Grants(array(
204             'modelName' => 'Tinebase_Model_PersistentFilterGrant',
205             'tableName' => 'filter_acl'
206         ));
207         $pfGrantsBackend->getGrantsForRecords($filters);
208     
209         foreach ($filters as $filter) {
210             if (count($filter->grants) > 0) {
211                 if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__
212                         . ' Filter ' . $filter->name . ' already has grants.');
213                 continue;
214             }
215             $grant = new Tinebase_Model_PersistentFilterGrant(array(
216                 'account_type' => $filter->isPersonal() ? Tinebase_Acl_Rights::ACCOUNT_TYPE_USER : Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE,
217                 'account_id'   => $filter->account_id,
218                 'record_id'    => $filter->getId(),
219             ));
220     
221             $grant->sanitizeAccountIdAndFillWithAllGrants();
222     
223             $filter->grants = new Tinebase_Record_RecordSet('Tinebase_Model_PersistentFilterGrant');
224             $filter->grants->addRecord($grant);
225     
226             if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__
227                     . ' Updating filter "' . $filter->name . '" with grant: ' . print_r($grant->toArray(), true));
228     
229             Tinebase_PersistentFilter::getInstance()->setGrants($filter);
230         }
231     }
232     
233     /**
234      * adds a label property to hold a humanreadable text
235      */
236     protected function _addImportExportDefinitionLabel()
237     {
238         $declaration = new Setup_Backend_Schema_Field_Xml('<field>
239             <name>label</name>
240             <type>text</type>
241             <length>128</length>
242             <notnull>false</notnull>
243         </field>');
244         
245         $this->_backend->addCol('importexport_definition', $declaration);
246         
247         $this->setTableVersion('importexport_definition', '8');
248     }
249     
250     /**
251      * updates import export definitions
252      * adds a label property to hold a humanreadable text if not exists
253      */
254     public function update_4()
255     {
256
257         if (! $this->_backend->columnExists('label', 'importexport_definition')) {
258             $this->_addImportExportDefinitionLabel();
259         }
260         
261         Setup_Controller::getInstance()->createImportExportDefinitions(Tinebase_Application::getInstance()->getApplicationByName('Addressbook'));
262         
263         $this->setApplicationVersion('Tinebase', '8.5');
264     }
265     
266     /**
267      * adds import table
268      */
269     public function update_5()
270     {
271         $tableDefinition = '
272             <table>
273                 <name>import</name>
274                 <version>1</version>
275                 <declaration>
276                     <field>
277                         <name>id</name>
278                         <type>text</type>
279                         <length>80</length>
280                         <notnull>true</notnull>
281                     </field>
282                     <field>
283                         <name>timestamp</name>
284                         <type>datetime</type>
285                     </field>
286                     <field>
287                         <name>user_id</name>
288                         <type>text</type>
289                         <length>80</length>
290                         <notnull>true</notnull>
291                     </field>
292                     <field>
293                         <name>model</name>
294                         <type>text</type>
295                         <length>80</length>
296                         <notnull>true</notnull>
297                     </field>
298                     <field>
299                         <name>application_id</name>
300                         <type>text</type>
301                         <length>80</length>
302                         <notnull>true</notnull>
303                     </field>
304                     <field>
305                         <name>synctoken</name>
306                         <type>text</type>
307                         <length>80</length>
308                     </field>
309                     <field>
310                         <name>container_id</name>
311                         <length>80</length>
312                         <type>text</type>
313                     </field>
314                     <field>
315                         <name>sourcetype</name>
316                         <type>text</type>
317                         <notnull>true</notnull>
318                     </field>
319                     <field>
320                         <name>interval</name>
321                         <type>text</type>
322                     </field>
323                     <field>
324                         <name>source</name>
325                         <type>text</type>
326                     </field>
327                     <field>
328                         <name>options</name>
329                         <type>text</type>
330                     </field>
331                     <index>
332                         <name>id</name>
333                         <primary>true</primary>
334                         <field>
335                             <name>id</name>
336                         </field>
337                     </index>
338                     <field>
339                         <name>created_by</name>
340                         <type>text</type>
341                         <length>40</length>
342                     </field>
343                     <field>
344                         <name>creation_time</name>
345                         <type>datetime</type>
346                     </field>
347                     <field>
348                         <name>last_modified_by</name>
349                         <type>text</type>
350                         <length>40</length>
351                     </field>
352                     <field>
353                         <name>last_modified_time</name>
354                         <type>datetime</type>
355                     </field>
356                     <field>
357                         <name>is_deleted</name>
358                         <type>boolean</type>
359                         <notnull>true</notnull>
360                         <default>false</default>
361                     </field>
362                     <field>
363                         <name>deleted_by</name>
364                         <type>text</type>
365                         <length>40</length>
366                     </field>
367                     <field>
368                         <name>deleted_time</name>
369                         <type>datetime</type>
370                     </field>
371                     <field>
372                         <name>seq</name>
373                         <type>integer</type>
374                         <notnull>true</notnull>
375                         <default>0</default>
376                     </field>
377                     <index>
378                         <name>import::application_id--applications::id</name>
379                         <field>
380                             <name>application_id</name>
381                         </field>
382                         <foreign>true</foreign>
383                         <reference>
384                             <table>applications</table>
385                             <field>id</field>
386                         </reference>
387                     </index>
388                     <index>
389                         <name>import::user_id--accounts::id</name>
390                         <field>
391                             <name>user_id</name>
392                         </field>
393                         <foreign>true</foreign>
394                         <reference>
395                             <table>accounts</table>
396                             <field>id</field>
397                         </reference>
398                     </index>
399                 </declaration>
400             </table>';
401
402
403         $table = Setup_Backend_Schema_Table_Factory::factory('String', $tableDefinition);
404         $this->_backend->createTable($table);
405         
406         $scheduler = Tinebase_Core::getScheduler();
407         Tinebase_Scheduler_Task::addImportTask($scheduler);
408         
409         $this->setApplicationVersion('Tinebase', '8.6');
410     }
411
412     /**
413      * - add filter acl (check if table already exists)
414      * 
415      * @see 0009610: shared favorites acl
416      */
417     public function update_6()
418     {
419         if (! $this->_backend->tableExists('filter_acl')) {
420             $this->_addFilterAclTable();
421             $this->_addGrantsToExistingFilters();
422         }
423         $this->setApplicationVersion('Tinebase', '8.7');
424     }
425     
426     /**
427      * adds and updates access_log columns
428      */
429     public function update_7()
430     {
431         $declaration = new Setup_Backend_Schema_Field_Xml('<field>
432             <name>id</name>
433             <type>text</type>
434             <length>40</length>
435             <notnull>true</notnull>
436         </field>');
437         
438         $this->_backend->alterCol('access_log', $declaration);
439         
440         if (! $this->_backend->columnExists('user_agent', 'access_log')) {
441             $declaration = new Setup_Backend_Schema_Field_Xml('<field>
442                 <name>user_agent</name>
443                 <type>text</type>
444                 <length>255</length>
445             </field>');
446             $this->_backend->addCol('access_log', $declaration);
447         }
448         
449         $declaration = new Setup_Backend_Schema_Index_Xml('<index>
450             <name>account_id-ip</name>
451             <field>
452                 <name>account_id</name>
453             </field>
454             <field>
455                 <name>ip</name>
456             </field>
457         </index>');
458         
459         $this->_backend->addIndex('access_log', $declaration);
460         
461         $this->setTableVersion('access_log', 5);
462         
463         $this->setApplicationVersion('Tinebase', '8.8');
464     }
465     
466     /**
467      * update 8 -> adds index for id column of table container_content
468      */
469     public function update_8()
470     {
471         $tableVersion = $this->getTableVersion('container_content');
472         
473         if ($tableVersion < 2) {
474             $declaration = new Setup_Backend_Schema_Index_Xml('
475                 <index>
476                     <name>id</name>
477                     <field>
478                         <name>id</name>
479                     </field>
480                 </index>
481             ');
482             
483             $this->_backend->addIndex('container_content', $declaration);
484             
485             $this->setTableVersion('container_content', '2');
486         }
487         
488         $this->setApplicationVersion('Tinebase', '8.9');
489     }
490
491     /**
492      * update 9 -> adds modlog to users and groups
493      */
494     public function update_9()
495     {
496         $this->_addModlogFields('accounts');
497         $this->setTableVersion('accounts', '11');
498         $this->_addModlogFields('groups');
499         $this->setTableVersion('groups', '5');
500         $this->setApplicationVersion('Tinebase', '8.10');
501     }
502
503     /**
504      * update 10 -> adds index to relations
505      */
506     public function update_10()
507     {
508         $tableVersion = $this->getTableVersion('relations');
509
510         if ($tableVersion < 8) {
511             $declaration = new Setup_Backend_Schema_Index_Xml('
512                     <index>
513                         <name>own_id</name>
514                         <field>
515                             <name>own_id</name>
516                         </field>
517                     </index>
518                 ');
519
520             $this->_backend->addIndex('relations', $declaration);
521             $this->setTableVersion('relations', '8');
522         }
523
524         $this->setApplicationVersion('Tinebase', '8.11');
525     }
526 }