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