xref: /plugin/bez/action/migration.php (revision bbd438ce52d241ea42077a932c307ea787417c14)
1<?php
2/**
3 * DokuWiki Plugin bez (Action Component)
4 *
5 */
6
7// must be run within Dokuwiki
8
9if(!defined('DOKU_INC')) die();
10
11/**
12 * Class action_plugin_bez_migration
13 *
14 * Handle migrations that need more than just SQL
15 */
16class action_plugin_bez_migration extends DokuWiki_Action_Plugin {
17    /**
18     * @inheritDoc
19     */
20    public function register(Doku_Event_Handler $controller) {
21        $controller->register_hook('PLUGIN_SQLITE_DATABASE_UPGRADE', 'BEFORE', $this, 'handle_migrations');
22//        $controller->register_hook('PLUGIN_SQLITE_DATABASE_UPGRADE', 'AFTER', $this, 'handle_migrations_after');
23    }
24
25    /**
26     * Call our custom migrations when defined
27     *
28     * @param Doku_Event $event
29     * @param $param
30     */
31    public function handle_migrations(Doku_Event $event, $param) {
32        if ($event->data['sqlite']->getAdapter()->getDbName() !== 'b3p') {
33            return;
34        }
35        $to = $event->data['to'];
36
37        if(is_callable(array($this, "migration$to"))) {
38            $event->preventDefault();
39            $event->result = call_user_func(array($this, "migration$to"), $event->data);
40        }
41    }
42
43    /**
44     * Call our custom migrations when defined
45     *
46     * @param Doku_Event $event
47     * @param $param
48     */
49    public function handle_migrations_after(Doku_Event $event, $param) {
50        if ($event->data['sqlite']->getAdapter()->getDbName() !== 'b3p') {
51            return;
52        }
53        $to = $event->data['to'];
54
55        if(is_callable(array($this, "migration$to"))) {
56            $event->result = call_user_func(array($this, "migration$to"), $event->data);
57        }
58    }
59
60    protected function migration12($data) {
61        global $INFO;
62
63        $file = $data['file'];
64        /** @var helper_plugin_sqlite $sqlite */
65        $sqlite = $data['sqlite'];
66
67        $sql = file_get_contents($file);
68        if($sql === false) {
69            throw new Exception('cannot open file ' . $file);
70        }
71
72        $matches = array();
73        preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches);
74        $queries = $matches[0];
75
76        $db = $sqlite->getAdapter()->getPdo();
77
78//        $db->beginTransaction();   // translation already started
79        foreach($queries as $query) {
80            $res = $db->query($query);
81            if($res === false) {
82                $err = $db->errorInfo();
83                msg($err[0] . ' ' . $err[1] . ' ' . $err[2] . ':<br /><pre>' . hsc($query) . '</pre>', -1);
84                $db->rollBack();
85                return false;
86            }
87        }
88//        $db->commit();  // commit will be done inside SQLiteDB
89
90        return true;
91    }
92
93    protected function migration3($data) {
94        global $INFO;
95
96        $file = $data['file'];
97        /** @var helper_plugin_sqlite $sqlite */
98        $sqlite = $data['sqlite'];
99
100        $sql = file_get_contents($file);
101        if($sql === false) {
102            throw new Exception('cannot open file ' . $file);
103        }
104
105        $matches = array();
106        preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches);
107        $queries = $matches[0];
108
109        $db = $sqlite->getAdapter()->getPdo();
110
111//        $db->beginTransaction();  // translation already started
112        foreach($queries as $query) {
113            $res = $db->query($query);
114            if($res === false) {
115                $err = $db->errorInfo();
116                msg($err[0] . ' ' . $err[1] . ' ' . $err[2] . ':<br /><pre>' . hsc($query) . '</pre>', -1);
117                $db->rollBack();
118                return false;
119            }
120        }
121//        $db->commit();  // commit will be done inside SQLiteDB
122
123        return true;
124    }
125
126    /**
127     * Executes Migration 1
128     *
129     * Add a latest column to all existing multi tables
130     *
131     * @param helper_plugin_sqlite $sqlite
132     * @return bool
133     */
134    protected function migration1($data) {
135        global $INFO;
136
137        $file = $data['file'];
138        /** @var helper_plugin_sqlite $sqlite */
139        $sqlite = $data['sqlite'];
140
141        $sql = file_get_contents($file);
142        if ($sql === false) {
143            throw new Exception('cannot open file '.$file);
144        }
145
146        $matches = array();
147        preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches);
148        $queries = $matches[0];
149
150        $db = $sqlite->getAdapter()->getPdo();
151
152//        $db->beginTransaction(); // translation already started
153        foreach ($queries as $query) {
154            $res = $db->query($query);
155            if($res === false) {
156                $err = $db->errorInfo();
157                msg($err[0].' '.$err[1].' '.$err[2].':<br /><pre>'.hsc($query).'</pre>', -1);
158                $db->rollBack();
159                return false;
160            }
161        }
162
163        $bez_file = DOKU_INC . 'data/meta/bez.sqlite3';
164        if (!file_exists($bez_file)) {
165//        $db->commit();  // commit will be done inside SQLiteDB
166            return true;
167        }
168
169        //import from bez
170        $bez = new \PDO('sqlite:' . $bez_file);
171
172        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
173
174        $stmt = $bez->query('SELECT * FROM issuetypes');
175        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
176            $sqlite->storeEntry('label', array('id' => $row['id'],
177                                                            'name' => $row['pl'],
178                                                            'added_by' => $INFO['client'],
179                                                            'added_date' => date('c')));
180        }
181
182        $stmt = $bez->query('SELECT * FROM tasktypes');
183        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
184            $sqlite->storeEntry('task_program', array('id' => $row['id'],
185                                                             'name' => $row['pl'],
186                                                             'added_by' => $INFO['client'],
187                                                             'added_date' => date('c')));
188        }
189
190        $stmt = $bez->query('SELECT *, (SELECT COUNT(*) FROM tasks
191								WHERE tasks.cause = commcauses.id) AS task_count FROM commcauses');
192        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
193            if ($row['type'] == '0') {
194                $type = 'comment';
195            } elseif ($row['type'] == '1') {
196                $type = 'cause_real';
197            } elseif ($row['type'] == '2') {
198                $type = 'cause_potential';
199            }
200            $sqlite->storeEntry('thread_comment',
201                                       array('id' => $row['id'],
202                                             'thread_id' => $row['issue'],
203                                             'type' => $type,
204                                             'author' => $row['reporter'],
205                                             'create_date' => date('c', strtotime($row['datetime'])),
206                                             'last_modification_date' => date('c', strtotime($row['datetime'])),
207                                             'content' => $row['content'],
208                                             'content_html' => $row['content_cache'],
209                                             'task_count' => $row['task_count']));
210        }
211
212        $stmt = $bez->query('SELECT tasks.*, commcauses.type AS cause_type
213                                          FROM tasks
214                                          LEFT JOIN commcauses ON tasks.cause = commcauses.id');
215        //thread_id => array('user_id' => 'user_id')
216        $task_assignee = array();
217        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
218            if ($row['close_date'] != '') {
219                $last_mod = date('c', (int) $row['close_date']);
220            } else {
221                $last_mod = date('c', (int) $row['date']);
222            }
223
224            $data =  array('id' => $row['id'],
225                           'original_poster' => $row['reporter'],
226                           'assignee' => $row['executor'],
227                           'create_date' => date('c', (int) $row['date']),
228                           'last_activity_date' => $last_mod,
229                           'last_modification_date' => $last_mod,
230                           'plan_date' => $row['plan_date'],
231                           'all_day_event' => $row['all_day_event'],
232                           'start_time' => $row['start_time'],
233                           'finish_time' => $row['finish_time'],
234                           'content' => $row['task'],
235                           'content_html' => $row['task_cache'],
236                           'thread_id' => $row['issue'],
237                           'thread_comment_id' => $row['cause'],
238                           'task_program_id' => $row['tasktype']
239            );
240
241            if ($data['thread_id'] != '') {
242                if (!is_array($task_assignee[$data['thread_id']])) {
243                    $task_assignee[$data['thread_id']] = array();
244                }
245                $task_assignee[$data['thread_id']][$row['executor']] = $row['executor'];
246            }
247
248            if ($row['cost'] != '0') {
249                $data['cost'] = $row['cost'];
250            }
251
252            if ($row['issue'] == '') {
253                $data['type'] = 'program';
254            } elseif ($row['cause'] == '') {
255                $data['type'] = 'correction';
256            } elseif ($row['cause_type'] == '1') {
257                $data['type'] = 'corrective';
258            } else {
259                $data['type'] = 'preventive';
260            }
261
262            if ($row['state'] == '0') {
263                $data['state'] = 'opened';
264            } elseif ($row['state'] == '1' || $row['state'] == '2') {
265                $data['state'] = 'done';
266                $data['closed_by'] = $row['executor'];
267                $data['close_date'] = date('c', (int) $row['close_date']);
268
269                if ($row['reason'] != '') {
270                    $sqlite->storeEntry('task_comment',
271                                               array('task_id' => $row['id'],
272                                                     'author' => $row['executor'],
273                                                     'create_date' => $data['close_date'],
274                                                     'last_modification_date' => $data['close_date'],
275                                                     'content' => $row['reason'],
276                                                     'content_html' => $row['reason_cache']));
277                }
278            }
279
280            //user_id => array()
281            $participants = array();
282            $subscribents = explode(',', $row['subscribents']);
283            foreach ($subscribents as $user_id) {
284                $participants[$user_id] = array('user_id' => $user_id, 'subscribent' => '1');
285            }
286
287            $op = $data['original_poster'];
288            if (!isset($participants[$op])) {
289                $participants[$op] = array('user_id' => $op, 'original_poster' => '1');
290            } else {
291                $participants[$op]['original_poster'] = '1';
292            }
293
294            $as = $data['assignee'];
295            if (!isset($participants[$as])) {
296                $participants[$as] = array('user_id' => $as, 'assignee' => '1');
297            } else {
298                $participants[$as]['assignee'] = '1';
299            }
300
301            foreach($participants as $part) {
302                $part['task_id'] = $row['id'];
303                $part['added_by'] = $INFO['client'];
304                $part['added_date'] = date('c');
305
306                $res = $sqlite->storeEntry('task_participant', $part);
307                if ($res === false) {
308                    throw new Exception($db->errorInfo());
309                }
310            }
311
312            $sqlite->storeEntry('task', $data);
313        }
314
315
316
317        $stmt = $bez->query('SELECT *,
318                                (SELECT COUNT(*) FROM tasks
319								WHERE tasks.issue = issues.id) AS task_count,
320								(SELECT COUNT(*) FROM tasks
321								WHERE tasks.issue = issues.id AND tasks.state != 0) AS task_closed_count,
322								(SELECT SUM(cost) FROM tasks
323								WHERE tasks.issue = issues.id) AS task_sum_cost
324								FROM issues');
325
326        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
327
328            $data =  array('id' => $row['id'],
329                           'original_poster' => $row['reporter'],
330                           'coordinator' => $row['coordinator'],
331                           'create_date' => date('c', (int) $row['date']),
332                           'last_activity_date' => date('c', strtotime($row['last_activity'])),
333                           'last_modification_date' => date('c', (int) $row['last_mod']),
334                           'title' => $row['title'],
335                           'content' => $row['description'],
336                           'content_html' => $row['description_cache'],
337                           'task_count' => $row['task_count'],
338                           'task_count_closed' => $row['task_closed_count']
339            );
340
341            if ($row['task_sum_cost'] != '0') {
342                $data['task_sum_cost'] = $row['task_sum_cost'];
343            }
344
345            if ($row['coordinator'] == '') {
346                $data['state'] = 'proposal';
347            } elseif ($row['state'] == '1') {
348                $data['closed_by'] = $row['coordinator'];
349                $data['state'] = 'closed';
350                $data['close_date'] = $data['last_modification_date'];
351
352                $sqlite->storeEntry('thread_comment',
353                                           array('thread_id' => $row['id'],
354                                                 'type' => 'comment',
355                                                 'author' => $row['coordinator'],
356                                                 'create_date' => $data['close_date'],
357                                                 'last_modification_date' => $data['close_date'],
358                                                 'content' => $row['opinion'],
359                                                 'content_html' => $row['opinion_cache']));
360
361
362            } elseif ($row['state'] == '2') {
363                $data['closed_by'] = $row['coordinator'];
364                $data['state'] = 'rejected';
365                $data['close_date'] = $data['last_modification_date'];
366
367                if ($row['opinion'] != '') {
368                    $res = $sqlite->storeEntry('thread_comment',
369                                               array('thread_id' => $row['id'],
370                                                     'type' => 'comment',
371                                                     'author' => $row['coordinator'],
372                                                     'create_date' => $data['close_date'],
373                                                     'last_modification_date' => $data['close_date'],
374                                                     'content' => $row['opinion'],
375                                                     'content_html' => $row['opinion_cache']));
376                    if ($res === false) {
377                        throw new Exception($db->errorInfo());
378                    }
379                }
380            } else {
381                $data['state'] = 'opened';
382            }
383
384            $sqlite->storeEntry('thread', $data);
385
386            $sqlite->storeEntry('thread_label',
387                                       array('thread_id' => $row['id'],
388                                             'label_id' => $row['type']));
389
390            //participants
391            //user_id => array()
392            $participants = array();
393            $org_participants = array_filter(explode(',', $row['participants']));
394            foreach ($org_participants as $user_id) {
395                $participants[$user_id] = array('user_id' => $user_id);
396            }
397
398            $subscribents = array_filter(explode(',', $row['subscribents']));
399            foreach ($subscribents as $user_id) {
400                if (!isset($participants[$user_id])) {
401                    $participants[$user_id] = array('user_id' => $user_id);
402                }
403                $participants[$user_id]['subscribent'] = '1';
404            }
405
406            $stmt_i = $bez->query('SELECT reporter FROM commcauses WHERE issue=' . $row['id']);
407            while ($commcause = $stmt_i->fetch(\PDO::FETCH_ASSOC)) {
408                $user_id = $commcause['reporter'];
409                if (!isset($participants[$user_id])) {
410                    $participants[$user_id] = array('user_id' => $user_id);
411                }
412                $participants[$user_id]['commentator'] = '1';
413            }
414
415            if (is_array($task_assignee[$row['id']])) foreach ($task_assignee[$row['id']] as $user_id) {
416                if (!isset($participants[$user_id])) {
417                    $participants[$user_id] = array('user_id' => $user_id);
418                }
419                $participants[$user_id]['task_assignee'] = '1';
420            }
421
422            $op = $data['original_poster'];
423            if (!isset($participants[$op])) {
424                $participants[$op] = array('user_id' => $op, 'original_poster' => '1');
425            } else {
426                $participants[$op]['original_poster'] = '1';
427            }
428
429            $cor = $data['coordinator'];
430            if (!isset($participants[$cor])) {
431                $participants[$cor] = array('user_id' => $cor, 'coordinator' => '1');
432            } else {
433                $participants[$cor]['coordinator'] = '1';
434            }
435
436            foreach($participants as $part) {
437                $part['thread_id'] = $row['id'];
438                $part['added_by'] = $INFO['client'];
439                $part['added_date'] = date('c');
440
441                $sqlite->storeEntry('thread_participant', $part);
442            }
443
444        }
445
446//        $db->commit();  // commit will be done inside SQLiteDB
447
448        return true;
449    }
450
451    protected function migration8($data) {
452        /** @var helper_plugin_sqlite $sqlite */
453        $sqlite = $data['sqlite'];
454
455        $db = $sqlite->getAdapter()->getPdo();
456        $db->commit(); // stop current transatction
457        $db->query('PRAGMA journal_mode=WAL');
458        $db->beginTransaction(); // recreate transaction
459        return true;
460    }
461
462}
463