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