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