Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 311 and 402] [Versions 311 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  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  class 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, type, 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, type, 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              $context->type = $row['type'];
 479              $settings = unserialize($row['settings']);
 480              if (!is_array($settings)) {
 481                  $settings = array();
 482              }
 483              $context->setSettings($settings);
 484              $context->created = strtotime($row['created']);
 485              $context->updated = strtotime($row['updated']);
 486          }
 487  
 488          return $ok;
 489  
 490      }
 491  
 492  /**
 493   * Save context object.
 494   *
 495   * @param Context $context Context object
 496   *
 497   * @return boolean True if the context object was successfully saved
 498   */
 499      public function saveContext($context)
 500      {
 501  
 502          $time = time();
 503          $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
 504          $settingsValue = serialize($context->getSettings());
 505          $id = $context->getRecordId();
 506          $consumer_pk = $context->getConsumer()->getRecordId();
 507          if (empty($id)) {
 508              $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' (consumer_pk, lti_context_id, ' .
 509                     'type, settings, created, updated) ' .
 510                     'VALUES (:cid, :ctx, :type, :settings, :created, :updated)';
 511              $query = $this->db->prepare($sql);
 512              $query->bindValue('cid', $consumer_pk, PDO::PARAM_INT);
 513              $query->bindValue('ctx', $context->ltiContextId, PDO::PARAM_STR);
 514              $query->bindValue('type', $context->type, PDO::PARAM_STR);
 515              $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
 516              $query->bindValue('created', $now, PDO::PARAM_STR);
 517              $query->bindValue('updated', $now, PDO::PARAM_STR);
 518          } else {
 519              $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' SET ' .
 520                     'lti_context_id = :ctx, type = :type, settings = :settings, '.
 521                     'updated = :updated ' .
 522                     'WHERE (consumer_pk = :cid) AND (context_pk = :ctxid)';
 523              $query = $this->db->prepare($sql);
 524              $query->bindValue('ctx', $context->ltiContextId, PDO::PARAM_STR);
 525              $query->bindValue('type', $context->type, PDO::PARAM_STR);
 526              $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
 527              $query->bindValue('updated', $now, PDO::PARAM_STR);
 528              $query->bindValue('cid', $consumer_pk, PDO::PARAM_INT);
 529              $query->bindValue('ctxid', $id, PDO::PARAM_INT);
 530          }
 531          $ok = $query->execute();
 532          if ($ok) {
 533              if (empty($id)) {
 534                  $context->setRecordId(intval($this->db->lastInsertId()));
 535                  $context->created = $time;
 536              }
 537              $context->updated = $time;
 538          }
 539  
 540          return $ok;
 541  
 542      }
 543  
 544  /**
 545   * Delete context object.
 546   *
 547   * @param Context $context Context object
 548   *
 549   * @return boolean True if the Context object was successfully deleted
 550   */
 551      public function deleteContext($context)
 552      {
 553  
 554          $id = $context->getRecordId();
 555  
 556  // Delete any outstanding share keys for resource links for this context
 557          $sql = 'DELETE sk ' .
 558                 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
 559                 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
 560                 'WHERE rl.context_pk = :id';
 561          $query = $this->db->prepare($sql);
 562          $query->bindValue('id', $id, PDO::PARAM_INT);
 563          $query->execute();
 564  
 565  // Delete any users in resource links for this context
 566          $sql = 'DELETE u ' .
 567                 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' .
 568                 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
 569                 'WHERE rl.context_pk = :id';
 570          $query = $this->db->prepare($sql);
 571          $query->bindValue('id', $id, PDO::PARAM_INT);
 572          $query->execute();
 573  
 574  // Update any resource links for which this consumer is acting as a primary resource link
 575          $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' .
 576                 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
 577                 'SET prl.primary_resource_link_pk = null, prl.share_approved = null ' .
 578                 'WHERE rl.context_pk = :id';
 579          $query = $this->db->prepare($sql);
 580          $query->bindValue('id', $id, PDO::PARAM_INT);
 581          $query->execute();
 582  
 583  // Delete any resource links for this consumer
 584          $sql = 'DELETE rl ' .
 585                 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' .
 586                 'WHERE rl.context_pk = :id';
 587          $query = $this->db->prepare($sql);
 588          $query->bindValue('id', $id, PDO::PARAM_INT);
 589          $query->execute();
 590  
 591  // Delete context
 592          $sql = 'DELETE c ' .
 593                 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ' .
 594                 'WHERE c.context_pk = :id';
 595          $query = $this->db->prepare($sql);
 596          $query->bindValue('id', $id, PDO::PARAM_INT);
 597          $ok = $query->execute();
 598  
 599          if ($ok) {
 600              $context->initialize();
 601          }
 602  
 603          return $ok;
 604  
 605      }
 606  
 607  ###
 608  ###  ResourceLink methods
 609  ###
 610  
 611  /**
 612   * Load resource link object.
 613   *
 614   * @param ResourceLink $resourceLink Resource_Link object
 615   *
 616   * @return boolean True if the resource link object was successfully loaded
 617   */
 618      public function loadResourceLink($resourceLink)
 619      {
 620  
 621          if (!empty($resourceLink->getRecordId())) {
 622              $sql = 'SELECT resource_link_pk, context_pk, consumer_pk, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' .
 623                     "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
 624                     'WHERE (resource_link_pk = :id)';
 625              $query = $this->db->prepare($sql);
 626              $query->bindValue('id', $resourceLink->getRecordId(), PDO::PARAM_INT);
 627          } else if (!empty($resourceLink->getContext())) {
 628              $sql = 'SELECT resource_link_pk, context_pk, consumer_pk, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' .
 629                     "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
 630                     'WHERE (context_pk = :id) AND (lti_resource_link_id = :rlid)';
 631              $query = $this->db->prepare($sql);
 632              $query->bindValue('id', $resourceLink->getContext()->getRecordId(), PDO::PARAM_INT);
 633              $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR);
 634          } else {
 635              $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 ' .
 636                     "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' r LEFT OUTER JOIN ' .
 637                     $this->dbTableNamePrefix . DataConnector::CONTEXT_TABLE_NAME . ' c ON r.context_pk = c.context_pk ' .
 638                     ' WHERE ((r.consumer_pk = :id1) OR (c.consumer_pk = :id2)) AND (lti_resource_link_id = :rlid)';
 639              $query = $this->db->prepare($sql);
 640              $query->bindValue('id1', $resourceLink->getConsumer()->getRecordId(), PDO::PARAM_INT);
 641              $query->bindValue('id2', $resourceLink->getConsumer()->getRecordId(), PDO::PARAM_INT);
 642              $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR);
 643          }
 644          $ok = $query->execute();
 645          if ($ok) {
 646            $row = $query->fetch(PDO::FETCH_ASSOC);
 647            $ok = ($row !== FALSE);
 648          }
 649  
 650          if ($ok) {
 651              $row = array_change_key_case($row);
 652              $resourceLink->setRecordId(intval($row['resource_link_pk']));
 653              if (!is_null($row['context_pk'])) {
 654                  $resourceLink->setContextId(intval($row['context_pk']));
 655              } else {
 656                  $resourceLink->setContextId(null);
 657              }
 658              if (!is_null($row['consumer_pk'])) {
 659                  $resourceLink->setConsumerId(intval($row['consumer_pk']));
 660              } else {
 661                  $resourceLink->setConsumerId(null);
 662              }
 663              $resourceLink->ltiResourceLinkId = $row['lti_resource_link_id'];
 664              $settings = unserialize($row['settings']);
 665              if (!is_array($settings)) {
 666                  $settings = array();
 667              }
 668              $resourceLink->setSettings($settings);
 669              if (!is_null($row['primary_resource_link_pk'])) {
 670                  $resourceLink->primaryResourceLinkId = intval($row['primary_resource_link_pk']);
 671              } else {
 672                  $resourceLink->primaryResourceLinkId = null;
 673              }
 674              $resourceLink->shareApproved = (is_null($row['share_approved'])) ? null : (intval($row['share_approved']) === 1);
 675              $resourceLink->created = strtotime($row['created']);
 676              $resourceLink->updated = strtotime($row['updated']);
 677          }
 678  
 679          return $ok;
 680  
 681      }
 682  
 683  /**
 684   * Save resource link object.
 685   *
 686   * @param ResourceLink $resourceLink Resource_Link object
 687   *
 688   * @return boolean True if the resource link object was successfully saved
 689   */
 690      public function saveResourceLink($resourceLink) {
 691  
 692          $time = time();
 693          $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
 694          $settingsValue = serialize($resourceLink->getSettings());
 695          if (!empty($resourceLink->getContext())) {
 696              $consumerId = null;
 697              $contextId = strval($resourceLink->getContext()->getRecordId());
 698          } else if (!empty($resourceLink->getContextId())) {
 699              $consumerId = null;
 700              $contextId = strval($resourceLink->getContextId());
 701          } else {
 702              $consumerId = strval($resourceLink->getConsumer()->getRecordId());
 703              $contextId = null;
 704          }
 705          if (empty($resourceLink->primaryResourceLinkId)) {
 706              $primaryResourceLinkId = null;
 707          } else {
 708              $primaryResourceLinkId = $resourceLink->primaryResourceLinkId;
 709          }
 710          $id = $resourceLink->getRecordId();
 711          if (empty($id)) {
 712              $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' (consumer_pk, context_pk, ' .
 713                     'lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated) ' .
 714                     'VALUES (:cid, :ctx, :rlid, :settings, :prlid, :share_approved, :created, :updated)';
 715              $query = $this->db->prepare($sql);
 716              $query->bindValue('cid', $consumerId, PDO::PARAM_INT);
 717              $query->bindValue('ctx', $contextId, PDO::PARAM_INT);
 718              $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR);
 719              $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
 720              $query->bindValue('prlid', $primaryResourceLinkId, PDO::PARAM_INT);
 721              $query->bindValue('share_approved', $resourceLink->shareApproved, PDO::PARAM_INT);
 722              $query->bindValue('created', $now, PDO::PARAM_STR);
 723              $query->bindValue('updated', $now, PDO::PARAM_STR);
 724          } else if (!is_null($contextId)) {
 725              $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' SET ' .
 726                     'consumer_pk = NULL, context_pk = :ctx, lti_resource_link_id = :rlid, settings = :settings, '.
 727                     'primary_resource_link_pk = :prlid, share_approved = :share_approved, updated = :updated ' .
 728                     'WHERE (resource_link_pk = :id)';
 729              $query = $this->db->prepare($sql);
 730              $query->bindValue('ctx', $contextId, PDO::PARAM_INT);
 731              $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR);
 732              $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
 733              $query->bindValue('prlid', $primaryResourceLinkId, PDO::PARAM_INT);
 734              $query->bindValue('share_approved', $resourceLink->shareApproved, PDO::PARAM_INT);
 735              $query->bindValue('updated', $now, PDO::PARAM_STR);
 736              $query->bindValue('id', $id, PDO::PARAM_INT);
 737          } else {
 738              $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' SET ' .
 739                     'context_pk = :ctx, lti_resource_link_id = :rlid, settings = :settings, '.
 740                     'primary_resource_link_pk = :prlid, share_approved = :share_approved, updated = :updated ' .
 741                     'WHERE (consumer_pk = :cid) AND (resource_link_pk = :id)';
 742              $query = $this->db->prepare($sql);
 743              $query->bindValue('ctx', $contextId, PDO::PARAM_INT);
 744              $query->bindValue('rlid', $resourceLink->getId(), PDO::PARAM_STR);
 745              $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
 746              $query->bindValue('prlid', $primaryResourceLinkId, PDO::PARAM_INT);
 747              $query->bindValue('share_approved', $resourceLink->shareApproved, PDO::PARAM_INT);
 748              $query->bindValue('updated', $now, PDO::PARAM_STR);
 749              $query->bindValue('cid', $consumerId, PDO::PARAM_INT);
 750              $query->bindValue('id', $id, PDO::PARAM_INT);
 751          }
 752          $ok = $query->execute();
 753          if ($ok) {
 754              if (empty($id)) {
 755                  $resourceLink->setRecordId(intval($this->db->lastInsertId()));
 756                  $resourceLink->created = $time;
 757              }
 758              $resourceLink->updated = $time;
 759          }
 760  
 761          return $ok;
 762  
 763      }
 764  
 765  /**
 766   * Delete resource link object.
 767   *
 768   * @param ResourceLink $resourceLink Resource_Link object
 769   *
 770   * @return boolean True if the resource link object was successfully deleted
 771   */
 772      public function deleteResourceLink($resourceLink)
 773      {
 774  
 775          $id = $resourceLink->getRecordId();
 776  
 777  // Delete any outstanding share keys for resource links for this consumer
 778          $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
 779                 'WHERE (resource_link_pk = :id)';
 780          $query = $this->db->prepare($sql);
 781          $query->bindValue('id', $id, PDO::PARAM_INT);
 782          $ok = $query->execute();
 783  
 784  // Delete users
 785          if ($ok) {
 786              $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
 787                     'WHERE (resource_link_pk = :id)';
 788              $query = $this->db->prepare($sql);
 789              $query->bindValue('id', $id, PDO::PARAM_INT);
 790              $ok = $query->execute();
 791          }
 792  
 793  // Update any resource links for which this is the primary resource link
 794          if ($ok) {
 795              $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
 796                     'SET primary_resource_link_pk = NULL ' .
 797                     'WHERE (primary_resource_link_pk = :id)';
 798              $query = $this->db->prepare($sql);
 799              $query->bindValue('id', $id, PDO::PARAM_INT);
 800              $ok = $query->execute();
 801          }
 802  
 803  // Delete resource link
 804          if ($ok) {
 805              $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
 806                     'WHERE (resource_link_pk = :id)';
 807              $query = $this->db->prepare($sql);
 808              $query->bindValue('id', $id, PDO::PARAM_INT);
 809              $ok = $query->execute();
 810          }
 811  
 812          if ($ok) {
 813              $resourceLink->initialize();
 814          }
 815  
 816          return $ok;
 817  
 818      }
 819  
 820  /**
 821   * Get array of user objects.
 822   *
 823   * Obtain an array of User objects for users with a result sourcedId.  The array may include users from other
 824   * resource links which are sharing this resource link.  It may also be optionally indexed by the user ID of a specified scope.
 825   *
 826   * @param ResourceLink $resourceLink      Resource link object
 827   * @param boolean     $localOnly True if only users within the resource link are to be returned (excluding users sharing this resource link)
 828   * @param int         $idScope     Scope value to use for user IDs
 829   *
 830   * @return array Array of User objects
 831   */
 832      public function getUserResultSourcedIDsResourceLink($resourceLink, $localOnly, $idScope)
 833      {
 834  
 835          $id = $resourceLink->getRecordId();
 836          $users = array();
 837  
 838          if ($localOnly) {
 839              $sql = 'SELECT u.user_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' .
 840                     "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' AS u '  .
 841                     "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' AS rl '  .
 842                     'ON u.resource_link_pk = rl.resource_link_pk ' .
 843                     'WHERE (rl.resource_link_pk = :id) AND (rl.primary_resource_link_pk IS NULL)';
 844              $query = $this->db->prepare($sql);
 845              $query->bindValue('id', $id, PDO::PARAM_INT);
 846          } else {
 847              $sql = 'SELECT u.user_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' .
 848                     "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' AS u '  .
 849                     "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' AS rl '  .
 850                     'ON u.resource_link_pk = rl.resource_link_pk ' .
 851                     'WHERE ((rl.resource_link_pk = :id) AND (rl.primary_resource_link_pk IS NULL)) OR ' .
 852                     '((rl.primary_resource_link_pk = :pid) AND (share_approved = 1))';
 853              $query = $this->db->prepare($sql);
 854              $query->bindValue('id', $id, PDO::PARAM_INT);
 855              $query->bindValue('pid', $id, PDO::PARAM_INT);
 856          }
 857          if ($query->execute()) {
 858              while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
 859                  $row = array_change_key_case($row);
 860                  $user = ToolProvider\User::fromRecordId($row['user_pk'], $resourceLink->getDataConnector());
 861                  if (is_null($idScope)) {
 862                      $users[] = $user;
 863                  } else {
 864                      $users[$user->getId($idScope)] = $user;
 865                  }
 866              }
 867          }
 868  
 869          return $users;
 870  
 871      }
 872  
 873  /**
 874   * Get array of shares defined for this resource link.
 875   *
 876   * @param ResourceLink $resourceLink Resource_Link object
 877   *
 878   * @return array Array of ResourceLinkShare objects
 879   */
 880      public function getSharesResourceLink($resourceLink)
 881      {
 882  
 883          $id = $resourceLink->getRecordId();
 884  
 885          $shares = array();
 886  
 887          $sql = 'SELECT consumer_pk, resource_link_pk, share_approved ' .
 888                 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
 889                 'WHERE (primary_resource_link_pk = :id) ' .
 890                 'ORDER BY consumer_pk';
 891          $query = $this->db->prepare($sql);
 892          $query->bindValue('id', $id, PDO::PARAM_INT);
 893          if ($query->execute()) {
 894              while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
 895                  $row = array_change_key_case($row);
 896                  $share = new ToolProvider\ResourceLinkShare();
 897                  $share->resourceLinkId = intval($row['resource_link_pk']);
 898                  $share->approved = (intval($row['share_approved']) === 1);
 899                  $shares[] = $share;
 900              }
 901          }
 902  
 903          return $shares;
 904  
 905      }
 906  
 907  
 908  ###
 909  ###  ConsumerNonce methods
 910  ###
 911  
 912  /**
 913   * Load nonce object.
 914   *
 915   * @param ConsumerNonce $nonce Nonce object
 916   *
 917   * @return boolean True if the nonce object was successfully loaded
 918   */
 919      public function loadConsumerNonce($nonce)
 920      {
 921  
 922          $ok = true;
 923  
 924  // Delete any expired nonce values
 925          $now = date("{$this->dateFormat} {$this->timeFormat}", time());
 926          $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE expires <= :now';
 927          $query = $this->db->prepare($sql);
 928          $query->bindValue('now', $now, PDO::PARAM_STR);
 929          $query->execute();
 930  
 931  // Load the nonce
 932          $id = $nonce->getConsumer()->getRecordId();
 933          $value = $nonce->getValue();
 934          $sql = "SELECT value T FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE (consumer_pk = :id) AND (value = :value)';
 935          $query = $this->db->prepare($sql);
 936          $query->bindValue('id', $id, PDO::PARAM_INT);
 937          $query->bindValue('value', $value, PDO::PARAM_STR);
 938          $ok = $query->execute();
 939          if ($ok) {
 940              $row = $query->fetch(PDO::FETCH_ASSOC);
 941              if ($row === false) {
 942                  $ok = false;
 943              }
 944          }
 945  
 946          return $ok;
 947  
 948      }
 949  
 950  /**
 951   * Save nonce object.
 952   *
 953   * @param ConsumerNonce $nonce Nonce object
 954   *
 955   * @return boolean True if the nonce object was successfully saved
 956   */
 957      public function saveConsumerNonce($nonce)
 958      {
 959  
 960          $id = $nonce->getConsumer()->getRecordId();
 961          $value = $nonce->getValue();
 962          $expires = date("{$this->dateFormat} {$this->timeFormat}", $nonce->expires);
 963          $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' (consumer_pk, value, expires) VALUES (:id, :value, :expires)';
 964          $query = $this->db->prepare($sql);
 965          $query->bindValue('id', $id, PDO::PARAM_INT);
 966          $query->bindValue('value', $value, PDO::PARAM_STR);
 967          $query->bindValue('expires', $expires, PDO::PARAM_STR);
 968          $ok = $query->execute();
 969  
 970          return $ok;
 971  
 972      }
 973  
 974  
 975  ###
 976  ###  ResourceLinkShareKey methods
 977  ###
 978  
 979  /**
 980   * Load resource link share key object.
 981   *
 982   * @param ResourceLinkShareKey $shareKey Resource_Link share key object
 983   *
 984   * @return boolean True if the resource link share key object was successfully loaded
 985   */
 986      public function loadResourceLinkShareKey($shareKey)
 987      {
 988  
 989          $ok = false;
 990  
 991  // Clear expired share keys
 992          $now = date("{$this->dateFormat} {$this->timeFormat}", time());
 993          $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' WHERE expires <= :now';
 994          $query = $this->db->prepare($sql);
 995          $query->bindValue('now', $now, PDO::PARAM_STR);
 996          $query->execute();
 997  
 998  // Load share key
 999          $id = $shareKey->getId();
1000          $sql = 'SELECT resource_link_pk, auto_approve, expires ' .
1001                 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
1002                 'WHERE share_key_id = :id';
1003          $query = $this->db->prepare($sql);
1004          $query->bindValue('id', $id, PDO::PARAM_STR);
1005          if ($query->execute()) {
1006              $row = $query->fetch(PDO::FETCH_ASSOC);
1007              if ($row !== FALSE) {
1008                  $row = array_change_key_case($row);
1009                  if (intval($row['resource_link_pk']) === $shareKey->resourceLinkId) {
1010                      $shareKey->autoApprove = ($row['auto_approve'] === 1);
1011                      $shareKey->expires = strtotime($row['expires']);
1012                      $ok = true;
1013                  }
1014              }
1015          }
1016  
1017          return $ok;
1018  
1019      }
1020  
1021  /**
1022   * Save resource link share key object.
1023   *
1024   * @param ResourceLinkShareKey $shareKey Resource link share key object
1025   *
1026   * @return boolean True if the resource link share key object was successfully saved
1027   */
1028      public function saveResourceLinkShareKey($shareKey)
1029      {
1030  
1031          $id = $shareKey->getId();
1032          $expires = date("{$this->dateFormat} {$this->timeFormat}", $shareKey->expires);
1033          $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
1034                 '(share_key_id, resource_link_pk, auto_approve, expires) ' .
1035                 'VALUES (:id, :prlid, :approve, :expires)';
1036          $query = $this->db->prepare($sql);
1037          $query->bindValue('id', $id, PDO::PARAM_STR);
1038          $query->bindValue('prlid', $shareKey->resourceLinkId, PDO::PARAM_INT);
1039          $query->bindValue('approve', $shareKey->autoApprove, PDO::PARAM_INT);
1040          $query->bindValue('expires', $expires, PDO::PARAM_STR);
1041          $ok = $query->execute();
1042  
1043          return $ok;
1044  
1045      }
1046  
1047  /**
1048   * Delete resource link share key object.
1049   *
1050   * @param ResourceLinkShareKey $shareKey Resource link share key object
1051   *
1052   * @return boolean True if the resource link share key object was successfully deleted
1053   */
1054      public function deleteResourceLinkShareKey($shareKey)
1055      {
1056  
1057          $id = $shareKey->getId();
1058          $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' WHERE share_key_id = :id';
1059          $query = $this->db->prepare($sql);
1060          $query->bindValue('id', $id, PDO::PARAM_STR);
1061          $ok = $query->execute();
1062  
1063          if ($ok) {
1064              $shareKey->initialize();
1065          }
1066  
1067          return $ok;
1068  
1069      }
1070  
1071  
1072  ###
1073  ###  User methods
1074  ###
1075  
1076  /**
1077   * Load user object.
1078   *
1079   * @param User $user User object
1080   *
1081   * @return boolean True if the user object was successfully loaded
1082   */
1083      public function loadUser($user)
1084      {
1085  
1086          $ok = false;
1087          if (!empty($user->getRecordId())) {
1088              $id = $user->getRecordId();
1089              $sql = 'SELECT user_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' .
1090                     "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
1091                     'WHERE (user_pk = :id)';
1092              $query = $this->db->prepare($sql);
1093              $query->bindValue('id', $id, PDO::PARAM_INT);
1094          } else {
1095              $id = $user->getResourceLink()->getRecordId();
1096              $uid = $user->getId(ToolProvider\ToolProvider::ID_SCOPE_ID_ONLY);
1097              $sql = 'SELECT user_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' .
1098                     "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
1099                     'WHERE (resource_link_pk = :id) AND (lti_user_id = :uid)';
1100              $query = $this->db->prepare($sql);
1101              $query->bindValue('id', $id, PDO::PARAM_INT);
1102              $query->bindValue('uid', $uid, PDO::PARAM_STR);
1103          }
1104          if ($query->execute()) {
1105              $row = $query->fetch(PDO::FETCH_ASSOC);
1106              if ($row !== false) {
1107                  $row = array_change_key_case($row);
1108                  $user->setRecordId(intval($row['user_pk']));
1109                  $user->setResourceLinkId(intval($row['resource_link_pk']));
1110                  $user->ltiUserId = $row['lti_user_id'];
1111                  $user->ltiResultSourcedId = $row['lti_result_sourcedid'];
1112                  $user->created = strtotime($row['created']);
1113                  $user->updated = strtotime($row['updated']);
1114                  $ok = true;
1115              }
1116          }
1117  
1118          return $ok;
1119  
1120      }
1121  
1122  /**
1123   * Save user object.
1124   *
1125   * @param User $user User object
1126   *
1127   * @return boolean True if the user object was successfully saved
1128   */
1129      public function saveUser($user)
1130      {
1131  
1132          $time = time();
1133          $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
1134          if (is_null($user->created)) {
1135              $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' (resource_link_pk, ' .
1136                     'lti_user_id, lti_result_sourcedid, created, updated) ' .
1137                     'VALUES (:rlid, :uid, :sourcedid, :created, :updated)';
1138              $query = $this->db->prepare($sql);
1139              $query->bindValue('rlid', $user->getResourceLink()->getRecordId(), PDO::PARAM_INT);
1140              $query->bindValue('uid', $user->getId(ToolProvider\ToolProvider::ID_SCOPE_ID_ONLY), PDO::PARAM_STR);
1141              $query->bindValue('sourcedid', $user->ltiResultSourcedId, PDO::PARAM_STR);
1142              $query->bindValue('created', $now, PDO::PARAM_STR);
1143              $query->bindValue('updated', $now, PDO::PARAM_STR);
1144          } else {
1145              $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
1146                     'SET lti_result_sourcedid = :sourcedid, updated = :updated ' .
1147                     'WHERE (user_pk = :id)';
1148              $query = $this->db->prepare($sql);
1149              $query->bindValue('sourcedid', $user->ltiResultSourcedId, PDO::PARAM_STR);
1150              $query->bindValue('updated', $now, PDO::PARAM_STR);
1151              $query->bindValue('id', $user->getRecordId(), PDO::PARAM_INT);
1152          }
1153          $ok = $query->execute();
1154          if ($ok) {
1155              if (is_null($user->created)) {
1156                  $user->setRecordId(intval($this->db->lastInsertId()));
1157                  $user->created = $time;
1158              }
1159              $user->updated = $time;
1160          }
1161  
1162          return $ok;
1163  
1164      }
1165  
1166  /**
1167   * Delete user object.
1168   *
1169   * @param User $user User object
1170   *
1171   * @return boolean True if the user object was successfully deleted
1172   */
1173      public function deleteUser($user)
1174      {
1175  
1176          $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
1177                 'WHERE (user_pk = :id)';
1178          $query = $this->db->prepare($sql);
1179          $query->bindValue('id', $user->getRecordId(), PDO::PARAM_INT);
1180          $ok = $query->execute();
1181  
1182          if ($ok) {
1183              $user->initialize();
1184          }
1185  
1186          return $ok;
1187  
1188      }
1189  
1190  }