1<?php 2 3namespace IMSGlobal\LTI\ToolProvider\DataConnector; 4 5use IMSGlobal\LTI\ToolProvider; 6use IMSGlobal\LTI\ToolProvider\ConsumerNonce; 7use IMSGlobal\LTI\ToolProvider\Context; 8use IMSGlobal\LTI\ToolProvider\ResourceLink; 9use IMSGlobal\LTI\ToolProvider\ResourceLinkShareKey; 10use IMSGlobal\LTI\ToolProvider\ToolConsumer; 11use IMSGlobal\LTI\ToolProvider\User; 12use PDO; 13 14/** 15 * Class to represent an LTI Data Connector for PDO connections 16 * 17 * @author Stephen P Vickers <svickers@imsglobal.org> 18 * @copyright IMS Global Learning Consortium Inc 19 * @date 2016 20 * @version 3.0.0 21 * @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0 22 */ 23 24 25class DataConnector_pdo extends DataConnector 26{ 27 28/** 29 * Class constructor 30 * 31 * @param object $db Database connection object 32 * @param string $dbTableNamePrefix Prefix for database table names (optional, default is none) 33 */ 34 public function __construct($db, $dbTableNamePrefix = '') 35 { 36 37 parent::__construct($db, $dbTableNamePrefix); 38 if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'oci') { 39 $this->date_format = 'd-M-Y'; 40 } 41 42 } 43 44### 45### ToolConsumer methods 46### 47 48/** 49 * Load tool consumer object. 50 * 51 * @param ToolConsumer $consumer ToolConsumer object 52 * 53 * @return boolean True if the tool consumer object was successfully loaded 54 */ 55 public function loadToolConsumer($consumer) 56 { 57 58 $ok = false; 59 if (!empty($consumer->getRecordId())) { 60 $sql = 'SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' . 61 'consumer_name, consumer_version, consumer_guid, ' . 62 'profile, tool_proxy, settings, protected, enabled, ' . 63 'enable_from, enable_until, last_access, created, updated ' . 64 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' . 65 'WHERE consumer_pk = :id'; 66 $query = $this->db->prepare($sql); 67 $id = $consumer->getRecordId(); 68 $query->bindValue('id', $id, PDO::PARAM_INT); 69 } else { 70 $sql = 'SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' . 71 'consumer_name, consumer_version, consumer_guid, ' . 72 'profile, tool_proxy, settings, protected, enabled, ' . 73 'enable_from, enable_until, last_access, created, updated ' . 74 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' . 75 'WHERE consumer_key256 = :key256'; 76 $query = $this->db->prepare($sql); 77 $key256 = DataConnector::getConsumerKey($consumer->getKey()); 78 $query->bindValue('key256', $key256, PDO::PARAM_STR); 79 } 80 81 if ($query->execute()) { 82 while ($row = $query->fetch(PDO::FETCH_ASSOC)) { 83 $row = array_change_key_case($row); 84 if (empty($key256) || empty($row['consumer_key']) || ($consumer->getKey() === $row['consumer_key'])) { 85 $consumer->setRecordId(intval($row['consumer_pk'])); 86 $consumer->name = $row['name']; 87 $consumer->setkey(empty($row['consumer_key']) ? $row['consumer_key256'] : $row['consumer_key']); 88 $consumer->secret = $row['secret']; 89 $consumer->ltiVersion = $row['lti_version']; 90 $consumer->consumerName = $row['consumer_name']; 91 $consumer->consumerVersion = $row['consumer_version']; 92 $consumer->consumerGuid = $row['consumer_guid']; 93 $consumer->profile = json_decode($row['profile']); 94 $consumer->toolProxy = $row['tool_proxy']; 95 $settings = unserialize($row['settings']); 96 if (!is_array($settings)) { 97 $settings = array(); 98 } 99 $consumer->setSettings($settings); 100 $consumer->protected = (intval($row['protected']) === 1); 101 $consumer->enabled = (intval($row['enabled']) === 1); 102 $consumer->enableFrom = null; 103 if (!is_null($row['enable_from'])) { 104 $consumer->enableFrom = strtotime($row['enable_from']); 105 } 106 $consumer->enableUntil = null; 107 if (!is_null($row['enable_until'])) { 108 $consumer->enableUntil = strtotime($row['enable_until']); 109 } 110 $consumer->lastAccess = null; 111 if (!is_null($row['last_access'])) { 112 $consumer->lastAccess = strtotime($row['last_access']); 113 } 114 $consumer->created = strtotime($row['created']); 115 $consumer->updated = strtotime($row['updated']); 116 $ok = true; 117 break; 118 } 119 } 120 } 121 122 return $ok; 123 124 } 125 126/** 127 * Save tool consumer object. 128 * 129 * @param ToolConsumer $consumer Consumer object 130 * 131 * @return boolean True if the tool consumer object was successfully saved 132 */ 133 public function saveToolConsumer($consumer) 134 { 135 136 $id = $consumer->getRecordId(); 137 $key = $consumer->getKey(); 138 $key256 = $this->getConsumerKey($key); 139 if ($key === $key256) { 140 $key = null; 141 } 142 $protected = ($consumer->protected) ? 1 : 0; 143 $enabled = ($consumer->enabled)? 1 : 0; 144 $profile = (!empty($consumer->profile)) ? json_encode($consumer->profile) : null; 145 $settingsValue = serialize($consumer->getSettings()); 146 $time = time(); 147 $now = date("{$this->dateFormat} {$this->timeFormat}", $time); 148 $from = null; 149 if (!is_null($consumer->enableFrom)) { 150 $from = date("{$this->dateFormat} {$this->timeFormat}", $consumer->enableFrom); 151 } 152 $until = null; 153 if (!is_null($consumer->enableUntil)) { 154 $until = date("{$this->dateFormat} {$this->timeFormat}", $consumer->enableUntil); 155 } 156 $last = null; 157 if (!is_null($consumer->lastAccess)) { 158 $last = date($this->dateFormat, $consumer->lastAccess); 159 } 160 if (empty($id)) { 161 $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' (consumer_key256, consumer_key, name, ' . 162 'secret, lti_version, consumer_name, consumer_version, consumer_guid, profile, tool_proxy, settings, protected, enabled, ' . 163 'enable_from, enable_until, last_access, created, updated) ' . 164 'VALUES (:key256, :key, :name, :secret, :lti_version, :consumer_name, :consumer_version, :consumer_guid, :profile, :tool_proxy, :settings, ' . 165 ':protected, :enabled, :enable_from, :enable_until, :last_access, :created, :updated)'; 166 $query = $this->db->prepare($sql); 167 $query->bindValue('key256', $key256, PDO::PARAM_STR); 168 $query->bindValue('key', $key, PDO::PARAM_STR); 169 $query->bindValue('name', $consumer->name, PDO::PARAM_STR); 170 $query->bindValue('secret', $consumer->secret, PDO::PARAM_STR); 171 $query->bindValue('lti_version', $consumer->ltiVersion, PDO::PARAM_STR); 172 $query->bindValue('consumer_name', $consumer->consumerName, PDO::PARAM_STR); 173 $query->bindValue('consumer_version', $consumer->consumerVersion, PDO::PARAM_STR); 174 $query->bindValue('consumer_guid', $consumer->consumerGuid, PDO::PARAM_STR); 175 $query->bindValue('profile', $profile, PDO::PARAM_STR); 176 $query->bindValue('tool_proxy', $consumer->toolProxy, PDO::PARAM_STR); 177 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 178 $query->bindValue('protected', $protected, PDO::PARAM_INT); 179 $query->bindValue('enabled', $enabled, PDO::PARAM_INT); 180 $query->bindValue('enable_from', $from, PDO::PARAM_STR); 181 $query->bindValue('enable_until', $until, PDO::PARAM_STR); 182 $query->bindValue('last_access', $last, PDO::PARAM_STR); 183 $query->bindValue('created', $now, PDO::PARAM_STR); 184 $query->bindValue('updated', $now, PDO::PARAM_STR); 185 } else { 186 $sql = 'UPDATE ' . $this->dbTableNamePrefix . DataConnector::CONSUMER_TABLE_NAME . ' ' . 187 'SET consumer_key256 = :key256, consumer_key = :key, name = :name, secret = :secret, lti_version = :lti_version, ' . 188 'consumer_name = :consumer_name, consumer_version = :consumer_version, consumer_guid = :consumer_guid, ' . 189 'profile = :profile, tool_proxy = :tool_proxy, settings = :settings, ' . 190 'protected = :protected, enabled = :enabled, enable_from = :enable_from, enable_until = :enable_until, last_access = :last_access, updated = :updated ' . 191 'WHERE consumer_pk = :id'; 192 $query = $this->db->prepare($sql); 193 $query->bindValue('key256', $key256, PDO::PARAM_STR); 194 $query->bindValue('key', $key, PDO::PARAM_STR); 195 $query->bindValue('name', $consumer->name, PDO::PARAM_STR); 196 $query->bindValue('secret', $consumer->secret, PDO::PARAM_STR); 197 $query->bindValue('lti_version', $consumer->ltiVersion, PDO::PARAM_STR); 198 $query->bindValue('consumer_name', $consumer->consumerName, PDO::PARAM_STR); 199 $query->bindValue('consumer_version', $consumer->consumerVersion, PDO::PARAM_STR); 200 $query->bindValue('consumer_guid', $consumer->consumerGuid, PDO::PARAM_STR); 201 $query->bindValue('profile', $profile, PDO::PARAM_STR); 202 $query->bindValue('tool_proxy', $consumer->toolProxy, PDO::PARAM_STR); 203 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 204 $query->bindValue('protected', $protected, PDO::PARAM_INT); 205 $query->bindValue('enabled', $enabled, PDO::PARAM_INT); 206 $query->bindValue('enable_from', $from, PDO::PARAM_STR); 207 $query->bindValue('enable_until', $until, PDO::PARAM_STR); 208 $query->bindValue('last_access', $last, PDO::PARAM_STR); 209 $query->bindValue('updated', $now, PDO::PARAM_STR); 210 $query->bindValue('id', $id, PDO::PARAM_INT); 211 } 212 $ok = $query->execute(); 213 if ($ok) { 214 if (empty($id)) { 215 $consumer->setRecordId(intval($this->db->lastInsertId())); 216 $consumer->created = $time; 217 } 218 $consumer->updated = $time; 219 } 220 221 return $ok; 222 223 } 224 225/** 226 * Delete tool consumer object. 227 * 228 * @param ToolConsumer $consumer Consumer object 229 * 230 * @return boolean True if the tool consumer object was successfully deleted 231 */ 232 public function deleteToolConsumer($consumer) 233 { 234 235 $id = $consumer->getRecordId(); 236 237// Delete any nonce values for this consumer 238 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE consumer_pk = :id'; 239 $query = $this->db->prepare($sql); 240 $query->bindValue('id', $id, PDO::PARAM_INT); 241 $query->execute(); 242 243// Delete any outstanding share keys for resource links for this consumer 244 $sql = 'DELETE sk ' . 245 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' . 246 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' . 247 'WHERE rl.consumer_pk = :id'; 248 $query = $this->db->prepare($sql); 249 $query->bindValue('id', $id, PDO::PARAM_INT); 250 $query->execute(); 251 252// Delete any outstanding share keys for resource links for contexts in this consumer 253 $sql = 'DELETE sk ' . 254 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' . 255 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' . 256 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' . 257 'WHERE c.consumer_pk = :id'; 258 $query = $this->db->prepare($sql); 259 $query->bindValue('id', $id, PDO::PARAM_INT); 260 $query->execute(); 261 262// Delete any users in resource links for this consumer 263 $sql = 'DELETE u ' . 264 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' . 265 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' . 266 'WHERE rl.consumer_pk = :id'; 267 $query = $this->db->prepare($sql); 268 $query->bindValue('id', $id, PDO::PARAM_INT); 269 $query->execute(); 270 271// Delete any users in resource links for contexts in this consumer 272 $sql = 'DELETE u ' . 273 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' . 274 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' . 275 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' . 276 'WHERE c.consumer_pk = :id'; 277 $query = $this->db->prepare($sql); 278 $query->bindValue('id', $id, PDO::PARAM_INT); 279 $query->execute(); 280 281// Update any resource links for which this consumer is acting as a primary resource link 282 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' . 283 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' . 284 'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' . 285 'WHERE rl.consumer_pk = :id'; 286 $query = $this->db->prepare($sql); 287 $query->bindValue('id', $id, PDO::PARAM_INT); 288 $query->execute(); 289 290// Update any resource links for contexts in which this consumer is acting as a primary resource link 291 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' . 292 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' . 293 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' . 294 'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' . 295 'WHERE c.consumer_pk = :id'; 296 $query = $this->db->prepare($sql); 297 $query->bindValue('id', $id, PDO::PARAM_INT); 298 $query->execute(); 299 300// Delete any resource links for this consumer 301 $sql = 'DELETE rl ' . 302 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' . 303 'WHERE rl.consumer_pk = :id'; 304 $query = $this->db->prepare($sql); 305 $query->bindValue('id', $id, PDO::PARAM_INT); 306 $query->execute(); 307 308// Delete any resource links for contexts in this consumer 309 $sql = 'DELETE rl ' . 310 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' . 311 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' . 312 'WHERE c.consumer_pk = :id'; 313 $query = $this->db->prepare($sql); 314 $query->bindValue('id', $id, PDO::PARAM_INT); 315 $query->execute(); 316 317// Delete any contexts for this consumer 318 $sql = 'DELETE c ' . 319 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ' . 320 'WHERE c.consumer_pk = :id'; 321 $query = $this->db->prepare($sql); 322 $query->bindValue('id', $id, PDO::PARAM_INT); 323 $query->execute(); 324 325// Delete consumer 326 $sql = 'DELETE c ' . 327 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' c ' . 328 'WHERE c.consumer_pk = :id'; 329 $query = $this->db->prepare($sql); 330 $query->bindValue('id', $id, PDO::PARAM_INT); 331 $ok = $query->execute(); 332 333 if ($ok) { 334 $consumer->initialize(); 335 } 336 337 return $ok; 338 339 } 340 341### 342# Load all tool consumers from the database 343### 344 public function getToolConsumers() 345 { 346 347 $consumers = array(); 348 349 $sql = 'SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' . 350 'consumer_name, consumer_version, consumer_guid, ' . 351 'profile, tool_proxy, settings, protected, enabled, ' . 352 'enable_from, enable_until, last_access, created, updated ' . 353 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' . 354 'ORDER BY name'; 355 $query = $this->db->prepare($sql); 356 $ok = ($query !== FALSE); 357 358 if ($ok) { 359 $ok = $query->execute(); 360 } 361 362 if ($ok) { 363 while ($row = $query->fetch(PDO::FETCH_ASSOC)) { 364 $row = array_change_key_case($row); 365 $key = empty($row['consumer_key']) ? $row['consumer_key256'] : $row['consumer_key']; 366 $consumer = new ToolProvider\ToolConsumer($key, $this); 367 $consumer->setRecordId(intval($row['consumer_pk'])); 368 $consumer->name = $row['name']; 369 $consumer->secret = $row['secret']; 370 $consumer->ltiVersion = $row['lti_version']; 371 $consumer->consumerName = $row['consumer_name']; 372 $consumer->consumerVersion = $row['consumer_version']; 373 $consumer->consumerGuid = $row['consumer_guid']; 374 $consumer->profile = json_decode($row['profile']); 375 $consumer->toolProxy = $row['tool_proxy']; 376 $settings = unserialize($row['settings']); 377 if (!is_array($settings)) { 378 $settings = array(); 379 } 380 $consumer->setSettings($settings); 381 $consumer->protected = (intval($row['protected']) === 1); 382 $consumer->enabled = (intval($row['enabled']) === 1); 383 $consumer->enableFrom = null; 384 if (!is_null($row['enable_from'])) { 385 $consumer->enableFrom = strtotime($row['enable_from']); 386 } 387 $consumer->enableUntil = null; 388 if (!is_null($row['enable_until'])) { 389 $consumer->enableUntil = strtotime($row['enable_until']); 390 } 391 $consumer->lastAccess = null; 392 if (!is_null($row['last_access'])) { 393 $consumer->lastAccess = strtotime($row['last_access']); 394 } 395 $consumer->created = strtotime($row['created']); 396 $consumer->updated = strtotime($row['updated']); 397 $consumers[] = $consumer; 398 } 399 } 400 401 return $consumers; 402 403 } 404 405### 406### ToolProxy methods 407### 408 409### 410# Load the tool proxy from the database 411### 412 public function loadToolProxy($toolProxy) 413 { 414 415 return false; 416 417 } 418 419### 420# Save the tool proxy to the database 421### 422 public function saveToolProxy($toolProxy) 423 { 424 425 return false; 426 427 } 428 429### 430# Delete the tool proxy from the database 431### 432 public function deleteToolProxy($toolProxy) 433 { 434 435 return false; 436 437 } 438 439### 440### Context methods 441### 442 443/** 444 * Load context object. 445 * 446 * @param Context $context Context object 447 * 448 * @return boolean True if the context object was successfully loaded 449 */ 450 public function loadContext($context) 451 { 452 453 $ok = false; 454 if (!empty($context->getRecordId())) { 455 $sql = 'SELECT context_pk, consumer_pk, lti_context_id, settings, created, updated ' . 456 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' ' . 457 'WHERE (context_pk = :id)'; 458 $query = $this->db->prepare($sql); 459 $query->bindValue('id', $context->getRecordId(), PDO::PARAM_INT); 460 } else { 461 $sql = 'SELECT context_pk, consumer_pk, lti_context_id, settings, created, updated ' . 462 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' ' . 463 'WHERE (consumer_pk = :cid) AND (lti_context_id = :ctx)'; 464 $query = $this->db->prepare($sql); 465 $query->bindValue('cid', $context->getConsumer()->getRecordId(), PDO::PARAM_INT); 466 $query->bindValue('ctx', $context->ltiContextId, PDO::PARAM_STR); 467 } 468 $ok = $query->execute(); 469 if ($ok) { 470 $row = $query->fetch(PDO::FETCH_ASSOC); 471 $ok = ($row !== FALSE); 472 } 473 if ($ok) { 474 $row = array_change_key_case($row); 475 $context->setRecordId(intval($row['context_pk'])); 476 $context->setConsumerId(intval($row['consumer_pk'])); 477 $context->ltiContextId = $row['lti_context_id']; 478 $settings = unserialize($row['settings']); 479 if (!is_array($settings)) { 480 $settings = array(); 481 } 482 $context->setSettings($settings); 483 $context->created = strtotime($row['created']); 484 $context->updated = strtotime($row['updated']); 485 } 486 487 return $ok; 488 489 } 490 491/** 492 * Save context object. 493 * 494 * @param Context $context Context object 495 * 496 * @return boolean True if the context object was successfully saved 497 */ 498 public function saveContext($context) 499 { 500 501 $time = time(); 502 $now = date("{$this->dateFormat} {$this->timeFormat}", $time); 503 $settingsValue = serialize($context->getSettings()); 504 $id = $context->getRecordId(); 505 $consumer_pk = $context->getConsumer()->getRecordId(); 506 if (empty($id)) { 507 $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' (consumer_pk, lti_context_id, ' . 508 'settings, created, updated) ' . 509 'VALUES (:cid, :ctx, :settings, :created, :updated)'; 510 $query = $this->db->prepare($sql); 511 $query->bindValue('cid', $consumer_pk, PDO::PARAM_INT); 512 $query->bindValue('ctx', $context->ltiContextId, PDO::PARAM_STR); 513 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 514 $query->bindValue('created', $now, PDO::PARAM_STR); 515 $query->bindValue('updated', $now, PDO::PARAM_STR); 516 } else { 517 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' SET ' . 518 'lti_context_id = :ctx, settings = :settings, '. 519 'updated = :updated ' . 520 'WHERE (consumer_pk = :cid) AND (context_pk = :ctxid)'; 521 $query = $this->db->prepare($sql); 522 $query->bindValue('ctx', $context->ltiContextId, PDO::PARAM_STR); 523 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 524 $query->bindValue('updated', $now, PDO::PARAM_STR); 525 $query->bindValue('cid', $consumer_pk, PDO::PARAM_INT); 526 $query->bindValue('ctxid', $id, PDO::PARAM_INT); 527 } 528 $ok = $query->execute(); 529 if ($ok) { 530 if (empty($id)) { 531 $context->setRecordId(intval($this->db->lastInsertId())); 532 $context->created = $time; 533 } 534 $context->updated = $time; 535 } 536 537 return $ok; 538 539 } 540 541/** 542 * Delete context object. 543 * 544 * @param Context $context Context object 545 * 546 * @return boolean True if the Context object was successfully deleted 547 */ 548 public function deleteContext($context) 549 { 550 551 $id = $context->getRecordId(); 552 553// Delete any outstanding share keys for resource links for this context 554 $sql = 'DELETE sk ' . 555 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' . 556 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' . 557 'WHERE rl.context_pk = :id'; 558 $query = $this->db->prepare($sql); 559 $query->bindValue('id', $id, PDO::PARAM_INT); 560 $query->execute(); 561 562// Delete any users in resource links for this context 563 $sql = 'DELETE u ' . 564 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' . 565 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' . 566 'WHERE rl.context_pk = :id'; 567 $query = $this->db->prepare($sql); 568 $query->bindValue('id', $id, PDO::PARAM_INT); 569 $query->execute(); 570 571// Update any resource links for which this consumer is acting as a primary resource link 572 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' . 573 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' . 574 'SET prl.primary_resource_link_pk = null, prl.share_approved = null ' . 575 'WHERE rl.context_pk = :id'; 576 $query = $this->db->prepare($sql); 577 $query->bindValue('id', $id, PDO::PARAM_INT); 578 $query->execute(); 579 580// Delete any resource links for this consumer 581 $sql = 'DELETE rl ' . 582 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' . 583 'WHERE rl.context_pk = :id'; 584 $query = $this->db->prepare($sql); 585 $query->bindValue('id', $id, PDO::PARAM_INT); 586 $query->execute(); 587 588// Delete context 589 $sql = 'DELETE c ' . 590 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ' . 591 'WHERE c.context_pk = :id'; 592 $query = $this->db->prepare($sql); 593 $query->bindValue('id', $id, PDO::PARAM_INT); 594 $ok = $query->execute(); 595 596 if ($ok) { 597 $context->initialize(); 598 } 599 600 return $ok; 601 602 } 603 604### 605### ResourceLink methods 606### 607 608/** 609 * Load resource link object. 610 * 611 * @param ResourceLink $resourceLink Resource_Link object 612 * 613 * @return boolean True if the resource link object was successfully loaded 614 */ 615 public function loadResourceLink($resourceLink) 616 { 617 618 if (!empty($resourceLink->getRecordId())) { 619 $sql = 'SELECT resource_link_pk, context_pk, consumer_pk, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' . 620 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' . 621 'WHERE (resource_link_pk = :id)'; 622 $query = $this->db->prepare($sql); 623 $query->bindValue('id', $resourceLink->getRecordId(), PDO::PARAM_INT); 624 } else if (!empty($resourceLink->getContext())) { 625 $sql = 'SELECT resource_link_pk, context_pk, consumer_pk, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' . 626 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' . 627 'WHERE (context_pk = :id) AND (lti_resource_link_id = :rlid)'; 628 $query = $this->db->prepare($sql); 629 $query->bindValue('id', $resourceLink->getContext()->getRecordId(), PDO::PARAM_INT); 630 $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR); 631 } else { 632 $sql = 'SELECT r.resource_link_pk, r.context_pk, r.consumer_pk, r.lti_resource_link_id, r.settings, r.primary_resource_link_pk, r.share_approved, r.created, r.updated ' . 633 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' r LEFT OUTER JOIN ' . 634 $this->dbTableNamePrefix . DataConnector::CONTEXT_TABLE_NAME . ' c ON r.context_pk = c.context_pk ' . 635 ' WHERE ((r.consumer_pk = :id1) OR (c.consumer_pk = :id2)) AND (lti_resource_link_id = :rlid)'; 636 $query = $this->db->prepare($sql); 637 $query->bindValue('id1', $resourceLink->getConsumer()->getRecordId(), PDO::PARAM_INT); 638 $query->bindValue('id2', $resourceLink->getConsumer()->getRecordId(), PDO::PARAM_INT); 639 $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR); 640 } 641 $ok = $query->execute(); 642 if ($ok) { 643 $row = $query->fetch(PDO::FETCH_ASSOC); 644 $ok = ($row !== FALSE); 645 } 646 647 if ($ok) { 648 $row = array_change_key_case($row); 649 $resourceLink->setRecordId(intval($row['resource_link_pk'])); 650 if (!is_null($row['context_pk'])) { 651 $resourceLink->setContextId(intval($row['context_pk'])); 652 } else { 653 $resourceLink->setContextId(null); 654 } 655 if (!is_null($row['consumer_pk'])) { 656 $resourceLink->setConsumerId(intval($row['consumer_pk'])); 657 } else { 658 $resourceLink->setConsumerId(null); 659 } 660 $resourceLink->ltiResourceLinkId = $row['lti_resource_link_id']; 661 $settings = unserialize($row['settings']); 662 if (!is_array($settings)) { 663 $settings = array(); 664 } 665 $resourceLink->setSettings($settings); 666 if (!is_null($row['primary_resource_link_pk'])) { 667 $resourceLink->primaryResourceLinkId = intval($row['primary_resource_link_pk']); 668 } else { 669 $resourceLink->primaryResourceLinkId = null; 670 } 671 $resourceLink->shareApproved = (is_null($row['share_approved'])) ? null : (intval($row['share_approved']) === 1); 672 $resourceLink->created = strtotime($row['created']); 673 $resourceLink->updated = strtotime($row['updated']); 674 } 675 676 return $ok; 677 678 } 679 680/** 681 * Save resource link object. 682 * 683 * @param ResourceLink $resourceLink Resource_Link object 684 * 685 * @return boolean True if the resource link object was successfully saved 686 */ 687 public function saveResourceLink($resourceLink) { 688 689 $time = time(); 690 $now = date("{$this->dateFormat} {$this->timeFormat}", $time); 691 $settingsValue = serialize($resourceLink->getSettings()); 692 if (!empty($resourceLink->getContext())) { 693 $consumerId = null; 694 $contextId = strval($resourceLink->getContext()->getRecordId()); 695 } else if (!empty($resourceLink->getContextId())) { 696 $consumerId = null; 697 $contextId = strval($resourceLink->getContextId()); 698 } else { 699 $consumerId = strval($resourceLink->getConsumer()->getRecordId()); 700 $contextId = null; 701 } 702 if (empty($resourceLink->primaryResourceLinkId)) { 703 $primaryResourceLinkId = null; 704 } else { 705 $primaryResourceLinkId = $resourceLink->primaryResourceLinkId; 706 } 707 $id = $resourceLink->getRecordId(); 708 if (empty($id)) { 709 $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' (consumer_pk, context_pk, ' . 710 'lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated) ' . 711 'VALUES (:cid, :ctx, :rlid, :settings, :prlid, :share_approved, :created, :updated)'; 712 $query = $this->db->prepare($sql); 713 $query->bindValue('cid', $consumerId, PDO::PARAM_INT); 714 $query->bindValue('ctx', $contextId, PDO::PARAM_INT); 715 $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR); 716 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 717 $query->bindValue('prlid', $primaryResourceLinkId, PDO::PARAM_INT); 718 $query->bindValue('share_approved', $resourceLink->shareApproved, PDO::PARAM_INT); 719 $query->bindValue('created', $now, PDO::PARAM_STR); 720 $query->bindValue('updated', $now, PDO::PARAM_STR); 721 } else if (!is_null($contextId)) { 722 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' SET ' . 723 'consumer_pk = NULL, context_pk = :ctx, lti_resource_link_id = :rlid, settings = :settings, '. 724 'primary_resource_link_pk = :prlid, share_approved = :share_approved, updated = :updated ' . 725 'WHERE (resource_link_pk = :id)'; 726 $query = $this->db->prepare($sql); 727 $query->bindValue('ctx', $contextId, PDO::PARAM_INT); 728 $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR); 729 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 730 $query->bindValue('prlid', $primaryResourceLinkId, PDO::PARAM_INT); 731 $query->bindValue('share_approved', $resourceLink->shareApproved, PDO::PARAM_INT); 732 $query->bindValue('updated', $now, PDO::PARAM_STR); 733 $query->bindValue('id', $id, PDO::PARAM_INT); 734 } else { 735 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' SET ' . 736 'context_pk = :ctx, lti_resource_link_id = :rlid, settings = :settings, '. 737 'primary_resource_link_pk = :prlid, share_approved = :share_approved, updated = :updated ' . 738 'WHERE (consumer_pk = :cid) AND (resource_link_pk = :id)'; 739 $query = $this->db->prepare($sql); 740 $query->bindValue('ctx', $contextId, PDO::PARAM_INT); 741 $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR); 742 $query->bindValue('settings', $settingsValue, PDO::PARAM_STR); 743 $query->bindValue('prlid', $primaryResourceLinkId, PDO::PARAM_INT); 744 $query->bindValue('share_approved', $resourceLink->shareApproved, PDO::PARAM_INT); 745 $query->bindValue('updated', $now, PDO::PARAM_STR); 746 $query->bindValue('cid', $consumerId, PDO::PARAM_INT); 747 $query->bindValue('id', $id, PDO::PARAM_INT); 748 } 749 $ok = $query->execute(); 750 if ($ok) { 751 if (empty($id)) { 752 $resourceLink->setRecordId(intval($this->db->lastInsertId())); 753 $resourceLink->created = $time; 754 } 755 $resourceLink->updated = $time; 756 } 757 758 return $ok; 759 760 } 761 762/** 763 * Delete resource link object. 764 * 765 * @param ResourceLink $resourceLink Resource_Link object 766 * 767 * @return boolean True if the resource link object was successfully deleted 768 */ 769 public function deleteResourceLink($resourceLink) 770 { 771 772 $id = $resourceLink->getRecordId(); 773 774// Delete any outstanding share keys for resource links for this consumer 775 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' . 776 'WHERE (resource_link_pk = :id)'; 777 $query = $this->db->prepare($sql); 778 $query->bindValue('id', $id, PDO::PARAM_INT); 779 $ok = $query->execute(); 780 781// Delete users 782 if ($ok) { 783 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' . 784 'WHERE (resource_link_pk = :id)'; 785 $query = $this->db->prepare($sql); 786 $query->bindValue('id', $id, PDO::PARAM_INT); 787 $ok = $query->execute(); 788 } 789 790// Update any resource links for which this is the primary resource link 791 if ($ok) { 792 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' . 793 'SET primary_resource_link_pk = NULL ' . 794 'WHERE (primary_resource_link_pk = :id)'; 795 $query = $this->db->prepare($sql); 796 $query->bindValue('id', $id, PDO::PARAM_INT); 797 $ok = $query->execute(); 798 } 799 800// Delete resource link 801 if ($ok) { 802 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' . 803 'WHERE (resource_link_pk = :id)'; 804 $query = $this->db->prepare($sql); 805 $query->bindValue('id', $id, PDO::PARAM_INT); 806 $ok = $query->execute(); 807 } 808 809 if ($ok) { 810 $resourceLink->initialize(); 811 } 812 813 return $ok; 814 815 } 816 817/** 818 * Get array of user objects. 819 * 820 * Obtain an array of User objects for users with a result sourcedId. The array may include users from other 821 * resource links which are sharing this resource link. It may also be optionally indexed by the user ID of a specified scope. 822 * 823 * @param ResourceLink $resourceLink Resource link object 824 * @param boolean $localOnly True if only users within the resource link are to be returned (excluding users sharing this resource link) 825 * @param int $idScope Scope value to use for user IDs 826 * 827 * @return array Array of User objects 828 */ 829 public function getUserResultSourcedIDsResourceLink($resourceLink, $localOnly, $idScope) 830 { 831 832 $id = $resourceLink->getRecordId(); 833 $users = array(); 834 835 if ($localOnly) { 836 $sql = 'SELECT u.user_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' . 837 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' AS u ' . 838 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' AS rl ' . 839 'ON u.resource_link_pk = rl.resource_link_pk ' . 840 'WHERE (rl.resource_link_pk = :id) AND (rl.primary_resource_link_pk IS NULL)'; 841 $query = $this->db->prepare($sql); 842 $query->bindValue('id', $id, PDO::PARAM_INT); 843 } else { 844 $sql = 'SELECT u.user_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' . 845 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' AS u ' . 846 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' AS rl ' . 847 'ON u.resource_link_pk = rl.resource_link_pk ' . 848 'WHERE ((rl.resource_link_pk = :id) AND (rl.primary_resource_link_pk IS NULL)) OR ' . 849 '((rl.primary_resource_link_pk = :pid) AND (share_approved = 1))'; 850 $query = $this->db->prepare($sql); 851 $query->bindValue('id', $id, PDO::PARAM_INT); 852 $query->bindValue('pid', $id, PDO::PARAM_INT); 853 } 854 if ($query->execute()) { 855 while ($row = $query->fetch(PDO::FETCH_ASSOC)) { 856 $row = array_change_key_case($row); 857 $user = ToolProvider\User::fromRecordId($row['user_pk'], $resourceLink->getDataConnector()); 858 if (is_null($idScope)) { 859 $users[] = $user; 860 } else { 861 $users[$user->getId($idScope)] = $user; 862 } 863 } 864 } 865 866 return $users; 867 868 } 869 870/** 871 * Get array of shares defined for this resource link. 872 * 873 * @param ResourceLink $resourceLink Resource_Link object 874 * 875 * @return array Array of ResourceLinkShare objects 876 */ 877 public function getSharesResourceLink($resourceLink) 878 { 879 880 $id = $resourceLink->getRecordId(); 881 882 $shares = array(); 883 884 $sql = 'SELECT consumer_pk, resource_link_pk, share_approved ' . 885 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' . 886 'WHERE (primary_resource_link_pk = :id) ' . 887 'ORDER BY consumer_pk'; 888 $query = $this->db->prepare($sql); 889 $query->bindValue('id', $id, PDO::PARAM_INT); 890 if ($query->execute()) { 891 while ($row = $query->fetch(PDO::FETCH_ASSOC)) { 892 $row = array_change_key_case($row); 893 $share = new ToolProvider\ResourceLinkShare(); 894 $share->resourceLinkId = intval($row['resource_link_pk']); 895 $share->approved = (intval($row['share_approved']) === 1); 896 $shares[] = $share; 897 } 898 } 899 900 return $shares; 901 902 } 903 904 905### 906### ConsumerNonce methods 907### 908 909/** 910 * Load nonce object. 911 * 912 * @param ConsumerNonce $nonce Nonce object 913 * 914 * @return boolean True if the nonce object was successfully loaded 915 */ 916 public function loadConsumerNonce($nonce) 917 { 918 919 $ok = true; 920 921// Delete any expired nonce values 922 $now = date("{$this->dateFormat} {$this->timeFormat}", time()); 923 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE expires <= :now'; 924 $query = $this->db->prepare($sql); 925 $query->bindValue('now', $now, PDO::PARAM_STR); 926 $query->execute(); 927 928// Load the nonce 929 $id = $nonce->getConsumer()->getRecordId(); 930 $value = $nonce->getValue(); 931 $sql = "SELECT value T FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE (consumer_pk = :id) AND (value = :value)'; 932 $query = $this->db->prepare($sql); 933 $query->bindValue('id', $id, PDO::PARAM_INT); 934 $query->bindValue('value', $value, PDO::PARAM_STR); 935 $ok = $query->execute(); 936 if ($ok) { 937 $row = $query->fetch(PDO::FETCH_ASSOC); 938 if ($row === false) { 939 $ok = false; 940 } 941 } 942 943 return $ok; 944 945 } 946 947/** 948 * Save nonce object. 949 * 950 * @param ConsumerNonce $nonce Nonce object 951 * 952 * @return boolean True if the nonce object was successfully saved 953 */ 954 public function saveConsumerNonce($nonce) 955 { 956 957 $id = $nonce->getConsumer()->getRecordId(); 958 $value = $nonce->getValue(); 959 $expires = date("{$this->dateFormat} {$this->timeFormat}", $nonce->expires); 960 $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' (consumer_pk, value, expires) VALUES (:id, :value, :expires)'; 961 $query = $this->db->prepare($sql); 962 $query->bindValue('id', $id, PDO::PARAM_INT); 963 $query->bindValue('value', $value, PDO::PARAM_STR); 964 $query->bindValue('expires', $expires, PDO::PARAM_STR); 965 $ok = $query->execute(); 966 967 return $ok; 968 969 } 970 971 972### 973### ResourceLinkShareKey methods 974### 975 976/** 977 * Load resource link share key object. 978 * 979 * @param ResourceLinkShareKey $shareKey Resource_Link share key object 980 * 981 * @return boolean True if the resource link share key object was successfully loaded 982 */ 983 public function loadResourceLinkShareKey($shareKey) 984 { 985 986 $ok = false; 987 988// Clear expired share keys 989 $now = date("{$this->dateFormat} {$this->timeFormat}", time()); 990 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' WHERE expires <= :now'; 991 $query = $this->db->prepare($sql); 992 $query->bindValue('now', $now, PDO::PARAM_STR); 993 $query->execute(); 994 995// Load share key 996 $id = $shareKey->getId(); 997 $sql = 'SELECT resource_link_pk, auto_approve, expires ' . 998 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' . 999 'WHERE share_key_id = :id'; 1000 $query = $this->db->prepare($sql); 1001 $query->bindValue('id', $id, PDO::PARAM_STR); 1002 if ($query->execute()) { 1003 $row = $query->fetch(PDO::FETCH_ASSOC); 1004 if ($row !== FALSE) { 1005 $row = array_change_key_case($row); 1006 if (intval($row['resource_link_pk']) === $shareKey->resourceLinkId) { 1007 $shareKey->autoApprove = ($row['auto_approve'] === 1); 1008 $shareKey->expires = strtotime($row['expires']); 1009 $ok = true; 1010 } 1011 } 1012 } 1013 1014 return $ok; 1015 1016 } 1017 1018/** 1019 * Save resource link share key object. 1020 * 1021 * @param ResourceLinkShareKey $shareKey Resource link share key object 1022 * 1023 * @return boolean True if the resource link share key object was successfully saved 1024 */ 1025 public function saveResourceLinkShareKey($shareKey) 1026 { 1027 1028 $id = $shareKey->getId(); 1029 $expires = date("{$this->dateFormat} {$this->timeFormat}", $shareKey->expires); 1030 $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' . 1031 '(share_key_id, resource_link_pk, auto_approve, expires) ' . 1032 'VALUES (:id, :prlid, :approve, :expires)'; 1033 $query = $this->db->prepare($sql); 1034 $query->bindValue('id', $id, PDO::PARAM_STR); 1035 $query->bindValue('prlid', $shareKey->resourceLinkId, PDO::PARAM_INT); 1036 $query->bindValue('approve', $shareKey->autoApprove, PDO::PARAM_INT); 1037 $query->bindValue('expires', $expires, PDO::PARAM_STR); 1038 $ok = $query->execute(); 1039 1040 return $ok; 1041 1042 } 1043 1044/** 1045 * Delete resource link share key object. 1046 * 1047 * @param ResourceLinkShareKey $shareKey Resource link share key object 1048 * 1049 * @return boolean True if the resource link share key object was successfully deleted 1050 */ 1051 public function deleteResourceLinkShareKey($shareKey) 1052 { 1053 1054 $id = $shareKey->getId(); 1055 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' WHERE share_key_id = :id'; 1056 $query = $this->db->prepare($sql); 1057 $query->bindValue('id', $id, PDO::PARAM_STR); 1058 $ok = $query->execute(); 1059 1060 if ($ok) { 1061 $shareKey->initialize(); 1062 } 1063 1064 return $ok; 1065 1066 } 1067 1068 1069### 1070### User methods 1071### 1072 1073/** 1074 * Load user object. 1075 * 1076 * @param User $user User object 1077 * 1078 * @return boolean True if the user object was successfully loaded 1079 */ 1080 public function loadUser($user) 1081 { 1082 1083 $ok = false; 1084 if (!empty($user->getRecordId())) { 1085 $id = $user->getRecordId(); 1086 $sql = 'SELECT user_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' . 1087 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' . 1088 'WHERE (user_pk = :id)'; 1089 $query = $this->db->prepare($sql); 1090 $query->bindValue('id', $id, PDO::PARAM_INT); 1091 } else { 1092 $id = $user->getResourceLink()->getRecordId(); 1093 $uid = $user->getId(ToolProvider\ToolProvider::ID_SCOPE_ID_ONLY); 1094 $sql = 'SELECT user_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' . 1095 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' . 1096 'WHERE (resource_link_pk = :id) AND (lti_user_id = :uid)'; 1097 $query = $this->db->prepare($sql); 1098 $query->bindValue('id', $id, PDO::PARAM_INT); 1099 $query->bindValue('uid', $uid, PDO::PARAM_STR); 1100 } 1101 if ($query->execute()) { 1102 $row = $query->fetch(PDO::FETCH_ASSOC); 1103 if ($row !== false) { 1104 $row = array_change_key_case($row); 1105 $user->setRecordId(intval($row['user_pk'])); 1106 $user->setResourceLinkId(intval($row['resource_link_pk'])); 1107 $user->ltiUserId = $row['lti_user_id']; 1108 $user->ltiResultSourcedId = $row['lti_result_sourcedid']; 1109 $user->created = strtotime($row['created']); 1110 $user->updated = strtotime($row['updated']); 1111 $ok = true; 1112 } 1113 } 1114 1115 return $ok; 1116 1117 } 1118 1119/** 1120 * Save user object. 1121 * 1122 * @param User $user User object 1123 * 1124 * @return boolean True if the user object was successfully saved 1125 */ 1126 public function saveUser($user) 1127 { 1128 1129 $time = time(); 1130 $now = date("{$this->dateFormat} {$this->timeFormat}", $time); 1131 if (is_null($user->created)) { 1132 $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' (resource_link_pk, ' . 1133 'lti_user_id, lti_result_sourcedid, created, updated) ' . 1134 'VALUES (:rlid, :uid, :sourcedid, :created, :updated)'; 1135 $query = $this->db->prepare($sql); 1136 $query->bindValue('rlid', $user->getResourceLink()->getRecordId(), PDO::PARAM_INT); 1137 $query->bindValue('uid', $user->getId(ToolProvider\ToolProvider::ID_SCOPE_ID_ONLY), PDO::PARAM_STR); 1138 $query->bindValue('sourcedid', $user->ltiResultSourcedId, PDO::PARAM_STR); 1139 $query->bindValue('created', $now, PDO::PARAM_STR); 1140 $query->bindValue('updated', $now, PDO::PARAM_STR); 1141 } else { 1142 $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' . 1143 'SET lti_result_sourcedid = :sourcedid, updated = :updated ' . 1144 'WHERE (user_pk = :id)'; 1145 $query = $this->db->prepare($sql); 1146 $query->bindValue('sourcedid', $user->ltiResultSourcedId, PDO::PARAM_STR); 1147 $query->bindValue('updated', $now, PDO::PARAM_STR); 1148 $query->bindValue('id', $user->getRecordId(), PDO::PARAM_INT); 1149 } 1150 $ok = $query->execute(); 1151 if ($ok) { 1152 if (is_null($user->created)) { 1153 $user->setRecordId(intval($this->db->lastInsertId())); 1154 $user->created = $time; 1155 } 1156 $user->updated = $time; 1157 } 1158 1159 return $ok; 1160 1161 } 1162 1163/** 1164 * Delete user object. 1165 * 1166 * @param User $user User object 1167 * 1168 * @return boolean True if the user object was successfully deleted 1169 */ 1170 public function deleteUser($user) 1171 { 1172 1173 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' . 1174 'WHERE (user_pk = :id)'; 1175 $query = $this->db->prepare($sql); 1176 $query->bindValue('id', $user->getRecordId(), PDO::PARAM_INT); 1177 $ok = $query->execute(); 1178 1179 if ($ok) { 1180 $user->initialize(); 1181 } 1182 1183 return $ok; 1184 1185 } 1186 1187} 1188