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