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