DataConnector_sqlsrv.php

Go to the documentation of this file.

<?php

namespace ceLTIc\LTI\DataConnector;

use ceLTIc\LTI;
use ceLTIc\LTI\ConsumerNonce;
use ceLTIc\LTI\Context;
use ceLTIc\LTI\ResourceLink;
use ceLTIc\LTI\ResourceLinkShare;
use ceLTIc\LTI\ResourceLinkShareKey;
use ceLTIc\LTI\ToolConsumer;
use ceLTIc\LTI\UserResult;

###
#    NB This class assumes that a MS SQL Server connection has already been opened to the appropriate schema
###


class DataConnector_sqlsrv extends DataConnector
{
###
###  ToolConsumer methods
###

    public function loadToolConsumer($consumer)
    {
        $ok = false;
        if (!is_null($consumer->getRecordId())) {
            $sql = sprintf('SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
                'signature_method, consumer_name, consumer_version, consumer_guid, ' .
                'profile, tool_proxy, settings, protected, enabled, ' .
                'enable_from, enable_until, last_access, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' ' .
                "WHERE consumer_pk = %d", $consumer->getRecordId());
        } else {
            $key256 = static::getConsumerKey($consumer->getKey());
            $sql = sprintf('SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
                'signature_method, consumer_name, consumer_version, consumer_guid, ' .
                'profile, tool_proxy, settings, protected, enabled, ' .
                'enable_from, enable_until, last_access, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' ' .
                "WHERE consumer_key256 = %s", $this->escape($key256));
        }
        $rsConsumer = sqlsrv_query($this->db, $sql);
        if ($rsConsumer) {
            while ($row = sqlsrv_fetch_object($rsConsumer)) {
                if (empty($key256) || empty($row->consumer_key) || ($consumer->getKey() === $row->consumer_key)) {
                    $consumer->setRecordId(intval($row->consumer_pk));
                    $consumer->name = $row->name;
                    $consumer->setkey(empty($row->consumer_key) ? $row->consumer_key256 : $row->consumer_key);
                    $consumer->secret = $row->secret;
                    $consumer->ltiVersion = $row->lti_version;
                    $consumer->signatureMethod = $row->signature_method;
                    $consumer->consumerName = $row->consumer_name;
                    $consumer->consumerVersion = $row->consumer_version;
                    $consumer->consumerGuid = $row->consumer_guid;
                    $consumer->profile = json_decode($row->profile);
                    $consumer->toolProxy = $row->tool_proxy;
                    $settings = json_decode($row->settings, TRUE);
                    if (!is_array($settings)) {
                        $settings = @unserialize($row->settings);  // check for old serialized setting
                    }
                    if (!is_array($settings)) {
                        $settings = array();
                    }
                    $consumer->setSettings($settings);
                    $consumer->protected = (intval($row->protected) === 1);
                    $consumer->enabled = (intval($row->enabled) === 1);
                    $consumer->enableFrom = null;
                    if (!is_null($row->enable_from)) {
                        $consumer->enableFrom = date_timestamp_get($row->enable_from);
                    }
                    $consumer->enableUntil = null;
                    if (!is_null($row->enable_until)) {
                        $consumer->enableUntil = date_timestamp_get($row->enable_until);
                    }
                    $consumer->lastAccess = null;
                    if (!is_null($row->last_access)) {
                        $consumer->lastAccess = date_timestamp_get($row->last_access);
                    }
                    $consumer->created = date_timestamp_get($row->created);
                    $consumer->updated = date_timestamp_get($row->updated);
                    $ok = true;
                    break;
                }
            }
            sqlsrv_free_stmt($rsConsumer);
        }

        return $ok;
    }

    public function saveToolConsumer($consumer)
    {
        $id = $consumer->getRecordId();
        $key = $consumer->getKey();
        $key256 = static::getConsumerKey($key);
        if ($key === $key256) {
            $key = null;
        }
        $protected = ($consumer->protected) ? 1 : 0;
        $enabled = ($consumer->enabled) ? 1 : 0;
        $profile = (!empty($consumer->profile)) ? json_encode($consumer->profile) : null;
        $settingsValue = json_encode($consumer->getSettings());
        $time = time();
        $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
        $from = null;
        if (!is_null($consumer->enableFrom)) {
            $from = date_format($consumer->enableFrom, "{$this->dateFormat} {$this->timeFormat}");
        }
        $until = null;
        if (!is_null($consumer->enableUntil)) {
            $until = date_format($consumer->enableUntil, "{$this->dateFormat} {$this->timeFormat}");
        }
        $last = null;
        if (!is_null($consumer->lastAccess)) {
            $last = date_format($consumer->lastAccess, $this->dateFormat);
        }
        if (empty($id)) {
            $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' (consumer_key256, consumer_key, name, ' .
                'secret, lti_version, signature_method, consumer_name, consumer_version, consumer_guid, profile, ' .
                'tool_proxy, settings, protected, enabled, ' .
                'enable_from, enable_until, last_access, created, updated) ' .
                'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %d, %s, %s, %s, %s, %s)', $this->escape($key256),
                $this->escape($key), $this->escape($consumer->name), $this->escape($consumer->secret),
                $this->escape($consumer->ltiVersion), $this->escape($consumer->signatureMethod),
                $this->escape($consumer->consumerName), $this->escape($consumer->consumerVersion),
                $this->escape($consumer->consumerGuid), $this->escape($profile), $this->escape($consumer->toolProxy),
                $this->escape($settingsValue), $protected, $enabled, $this->escape($from), $this->escape($until),
                $this->escape($last), $this->escape($now), $this->escape($now));
        } else {
            $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' SET ' .
                'consumer_key256 = %s, consumer_key = %s, ' .
                'name = %s, secret= %s, lti_version = %s, signature_method = %s, consumer_name = %s, consumer_version = %s, consumer_guid = %s, ' .
                'profile = %s, tool_proxy = %s, settings = %s, ' .
                'protected = %d, enabled = %d, enable_from = %s, enable_until = %s, last_access = %s, updated = %s ' .
                'WHERE consumer_pk = %d', $this->escape($key256), $this->escape($key), $this->escape($consumer->name),
                $this->escape($consumer->secret), $this->escape($consumer->ltiVersion), $this->escape($consumer->signatureMethod),
                $this->escape($consumer->consumerName), $this->escape($consumer->consumerVersion),
                $this->escape($consumer->consumerGuid), $this->escape($profile), $this->escape($consumer->toolProxy),
                $this->escape($settingsValue), $protected, $enabled, $this->escape($from), $this->escape($until),
                $this->escape($last), $this->escape($now), $consumer->getRecordId());
        }
        $ok = sqlsrv_query($this->db, $sql);
        if ($ok) {
            if (empty($id)) {
                $consumer->setRecordId($this->insert_id());
                $consumer->created = $time;
            }
            $consumer->updated = $time;
        }

        return $ok;
    }

    public function deleteToolConsumer($consumer)
    {
// Delete any nonce values for this consumer
        $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . static::NONCE_TABLE_NAME . ' WHERE consumer_pk = %d',
            $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any outstanding share keys for resource links for this consumer
        $sql = sprintf('DELETE sk ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
            'WHERE rl.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any outstanding share keys for resource links for contexts in this consumer
        $sql = sprintf('DELETE sk ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
            'WHERE c.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any users in resource links for this consumer
        $sql = sprintf('DELETE u ' .
            "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' u ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
            'WHERE rl.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any users in resource links for contexts in this consumer
        $sql = sprintf('DELETE u ' .
            "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' u ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
            'WHERE c.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Update any resource links for which this consumer is acting as a primary resource link
        $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' prl ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
            'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' .
            'WHERE rl.consumer_pk = %d', $consumer->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);

// Update any resource links for contexts in which this consumer is acting as a primary resource link
        $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' prl ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
            'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' .
            'WHERE c.consumer_pk = %d', $consumer->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);

// Delete any resource links for this consumer
        $sql = sprintf('DELETE rl ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ' .
            'WHERE rl.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any resource links for contexts in this consumer
        $sql = sprintf('DELETE rl ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
            'WHERE c.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any contexts for this consumer
        $sql = sprintf('DELETE c ' .
            "FROM {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' c ' .
            'WHERE c.consumer_pk = %d', $consumer->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete consumer
        $sql = sprintf('DELETE c ' .
            "FROM {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' c ' .
            'WHERE c.consumer_pk = %d', $consumer->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);

        if ($ok) {
            $consumer->initialize();
        }

        return $ok;
    }

    public function getToolConsumers()
    {
        $consumers = array();

        $sql = 'SELECT consumer_pk, consumer_key256, consumer_key, name, secret, lti_version, ' .
            'signature_method, consumer_name, consumer_version, consumer_guid, ' .
            'profile, tool_proxy, settings, ' .
            'protected, enabled, enable_from, enable_until, last_access, created, updated ' .
            "FROM {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' ' .
            'ORDER BY name';
        $rsConsumers = sqlsrv_query($this->db, $sql);
        if ($rsConsumers) {
            while ($row = sqlsrv_fetch_object($rsConsumers)) {
                $key = empty($row->consumer_key) ? $row->consumer_key256 : $row->consumer_key;
                $consumer = new ToolConsumer($key, $this);
                $consumer->setRecordId(intval($row->consumer_pk));
                $consumer->name = $row->name;
                $consumer->secret = $row->secret;
                $consumer->ltiVersion = $row->lti_version;
                $consumer->signatureMethod = $row->signature_method;
                $consumer->consumerName = $row->consumer_name;
                $consumer->consumerVersion = $row->consumer_version;
                $consumer->consumerGuid = $row->consumer_guid;
                $consumer->profile = json_decode($row->profile);
                $consumer->toolProxy = $row->tool_proxy;
                $settings = json_decode($row->settings, TRUE);
                if (!is_array($settings)) {
                    $settings = @unserialize($row->settings);  // check for old serialized setting
                }
                if (!is_array($settings)) {
                    $settings = array();
                }
                $consumer->setSettings($settings);
                $consumer->protected = (intval($row->protected) === 1);
                $consumer->enabled = (intval($row->enabled) === 1);
                $consumer->enableFrom = null;
                if (!is_null($row->enable_from)) {
                    $consumer->enableFrom = date_timestamp_get($row->enable_from);
                }
                $consumer->enableUntil = null;
                if (!is_null($row->enable_until)) {
                    $consumer->enableUntil = date_timestamp_get($row->enable_until);
                }
                $consumer->lastAccess = null;
                if (!is_null($row->last_access)) {
                    $consumer->lastAccess = date_timestamp_get($row->last_access);
                }
                $consumer->created = date_timestamp_get($row->created);
                $consumer->updated = date_timestamp_get($row->updated);
                $consumers[] = $consumer;
            }
            sqlsrv_free_stmt($rsConsumers);
        }

        return $consumers;
    }

###
###  Context methods
###

    public function loadContext($context)
    {
        $ok = false;
        if (!is_null($context->getRecordId())) {
            $sql = sprintf('SELECT context_pk, consumer_pk, title, lti_context_id, type, settings, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' ' .
                'WHERE (context_pk = %d)', $context->getRecordId());
        } else {
            $sql = sprintf('SELECT context_pk, consumer_pk, title, lti_context_id, type, settings, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' ' .
                'WHERE (consumer_pk = %d) AND (lti_context_id = %s)', $context->getConsumer()->getRecordId(),
                $this->escape($context->ltiContextId));
        }
        $rs_context = sqlsrv_query($this->db, $sql);
        if ($rs_context) {
            $row = sqlsrv_fetch_object($rs_context);
            if ($row) {
                $context->setRecordId(intval($row->context_pk));
                $context->setConsumerId(intval($row->consumer_pk));
                $context->title = $row->title;
                $context->ltiContextId = $row->lti_context_id;
                $context->type = $row->type;
                $settings = json_decode($row->settings, TRUE);
                if (!is_array($settings)) {
                    $settings = @unserialize($row->settings);  // check for old serialized setting
                }
                if (!is_array($settings)) {
                    $settings = array();
                }
                $context->setSettings($settings);
                $context->created = date_timestamp_get($row->created);
                $context->updated = date_timestamp_get($row->updated);
                $ok = true;
            }
        }

        return $ok;
    }

    public function saveContext($context)
    {
        $time = time();
        $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
        $settingsValue = json_encode($context->getSettings());
        $id = $context->getRecordId();
        $consumer_pk = $context->getConsumer()->getRecordId();
        if (empty($id)) {
            $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' (consumer_pk, title, ' .
                'lti_context_id, type, settings, created, updated) ' .
                'VALUES (%d, %s, %s, %s, %s, %s, %s)', $consumer_pk, $this->escape($context->title),
                $this->escape($context->ltiContextId), $this->escape($context->type), $this->escape($settingsValue),
                $this->escape($now), $this->escape($now));
        } else {
            $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' SET ' .
                'title = %s, lti_context_id = %s, type = %s, settings = %s, ' .
                'updated = %s' .
                'WHERE (consumer_pk = %d) AND (context_pk = %d)', $this->escape($context->title),
                $this->escape($context->ltiContextId), $this->escape($context->type), $this->escape($settingsValue),
                $this->escape($now), $consumer_pk, $id);
        }
        $ok = sqlsrv_query($this->db, $sql);
        if ($ok) {
            if (empty($id)) {
                $context->setRecordId($this->insert_id());
                $context->created = $time;
            }
            $context->updated = $time;
        }

        return $ok;
    }

    public function deleteContext($context)
    {
// Delete any outstanding share keys for resource links for this context
        $sql = sprintf('DELETE sk ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
            'WHERE rl.context_pk = %d', $context->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete any users in resource links for this context
        $sql = sprintf('DELETE u ' .
            "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' u ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
            'WHERE rl.context_pk = %d', $context->getRecordId());
        sqlsrv_query($this->db, $sql);

// Update any resource links for which this consumer is acting as a primary resource link
        $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' prl ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
            'SET prl.primary_resource_link_pk = null, prl.share_approved = null ' .
            'WHERE rl.context_pk = %d', $context->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);

// Delete any resource links for this consumer
        $sql = sprintf('DELETE rl ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' rl ' .
            'WHERE rl.context_pk = %d', $context->getRecordId());
        sqlsrv_query($this->db, $sql);

// Delete context
        $sql = sprintf('DELETE c ' .
            "FROM {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' c ', 'WHERE c.context_pk = %d',
            $context->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);
        if ($ok) {
            $context->initialize();
        }

        return $ok;
    }

###
###  ResourceLink methods
###

    public function loadResourceLink($resourceLink)
    {
        $ok = false;
        if (!is_null($resourceLink->getRecordId())) {
            $sql = sprintf('SELECT resource_link_pk, context_pk, consumer_pk, title, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' ' .
                'WHERE (resource_link_pk = %d)', $resourceLink->getRecordId());
        } elseif (!is_null($resourceLink->getContext())) {
            $sql = sprintf('SELECT resource_link_pk, context_pk, consumer_pk, title, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' ' .
                'WHERE (context_pk = %d) AND (lti_resource_link_id = %s)', $resourceLink->getContext()->getRecordId(),
                $this->escape($resourceLink->getId()));
        } else {
            $sql = sprintf('SELECT r.resource_link_pk, r.context_pk, r.consumer_pk, r.title, r.lti_resource_link_id, r.settings, r.primary_resource_link_pk, r.share_approved, r.created, r.updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' r LEFT OUTER JOIN ' .
                $this->dbTableNamePrefix . static::CONTEXT_TABLE_NAME . ' c ON r.context_pk = c.context_pk ' .
                ' WHERE ((r.consumer_pk = %d) OR (c.consumer_pk = %d)) AND (lti_resource_link_id = %s)',
                $resourceLink->getConsumer()->getRecordId(), $resourceLink->getConsumer()->getRecordId(),
                $this->escape($resourceLink->getId()));
        }
        $rsContext = sqlsrv_query($this->db, $sql);
        if ($rsContext) {
            $row = sqlsrv_fetch_object($rsContext);
            if ($row) {
                $resourceLink->setRecordId(intval($row->resource_link_pk));
                if (!is_null($row->context_pk)) {
                    $resourceLink->setContextId(intval($row->context_pk));
                } else {
                    $resourceLink->setContextId(null);
                }
                if (!is_null($row->consumer_pk)) {
                    $resourceLink->setConsumerId(intval($row->consumer_pk));
                } else {
                    $resourceLink->setConsumerId(null);
                }
                $resourceLink->title = $row->title;
                $resourceLink->ltiResourceLinkId = $row->lti_resource_link_id;
                $settings = json_decode($row->settings, TRUE);
                if (!is_array($settings)) {
                    $settings = @unserialize($row->settings);  // check for old serialized setting
                }
                if (!is_array($settings)) {
                    $settings = array();
                }
                $resourceLink->setSettings($settings);
                if (!is_null($row->primary_resource_link_pk)) {
                    $resourceLink->primaryResourceLinkId = intval($row->primary_resource_link_pk);
                } else {
                    $resourceLink->primaryResourceLinkId = null;
                }
                $resourceLink->shareApproved = (is_null($row->share_approved)) ? null : (intval($row->share_approved) === 1);
                $resourceLink->created = date_timestamp_get($row->created);
                $resourceLink->updated = date_timestamp_get($row->updated);
                $ok = true;
            }
        }

        return $ok;
    }

    public function saveResourceLink($resourceLink)
    {
        if (is_null($resourceLink->shareApproved)) {
            $approved = 'NULL';
        } elseif ($resourceLink->shareApproved) {
            $approved = '1';
        } else {
            $approved = '0';
        }
        if (empty($resourceLink->primaryResourceLinkId)) {
            $primaryResourceLinkId = 'NULL';
        } else {
            $primaryResourceLinkId = strval($resourceLink->primaryResourceLinkId);
        }
        $time = time();
        $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
        $settingsValue = json_encode($resourceLink->getSettings());
        if (!is_null($resourceLink->getContext())) {
            $consumerId = 'NULL';
            $contextId = strval($resourceLink->getContext()->getRecordId());
        } elseif (!is_null($resourceLink->getContextId())) {
            $consumerId = 'NULL';
            $contextId = strval($resourceLink->getContextId());
        } else {
            $consumerId = strval($resourceLink->getConsumer()->getRecordId());
            $contextId = 'NULL';
        }
        $id = $resourceLink->getRecordId();
        if (empty($id)) {
            $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' (consumer_pk, context_pk, ' .
                'title, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated) ' .
                'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)', $consumerId, $contextId, $this->escape($resourceLink->title),
                $this->escape($resourceLink->getId()), $this->escape($settingsValue), $primaryResourceLinkId, $approved,
                $this->escape($now), $this->escape($now));
        } elseif ($contextId !== 'NULL') {
            $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' SET ' .
                'consumer_pk = %s, title = %s, lti_resource_link_id = %s, settings = %s, ' .
                'primary_resource_link_pk = %s, share_approved = %s, updated = %s ' .
                'WHERE (context_pk = %s) AND (resource_link_pk = %d)', $consumerId, $this->escape($resourceLink->title),
                $this->escape($resourceLink->getId()), $this->escape($settingsValue), $primaryResourceLinkId, $approved,
                $this->escape($now), $contextId, $id);
        } else {
            $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' SET ' .
                'context_pk = %s, title = %s, lti_resource_link_id = %s, settings = %s, ' .
                'primary_resource_link_pk = %s, share_approved = %s, updated = %s ' .
                'WHERE (consumer_pk = %s) AND (resource_link_pk = %d)', $contextId, $this->escape($resourceLink->title),
                $this->escape($resourceLink->getId()), $this->escape($settingsValue), $primaryResourceLinkId, $approved,
                $this->escape($now), $consumerId, $id);
        }
        $ok = sqlsrv_query($this->db, $sql);
        if ($ok) {
            if (empty($id)) {
                $resourceLink->setRecordId($this->insert_id());
                $resourceLink->created = $time;
            }
            $resourceLink->updated = $time;
        }

        return $ok;
    }

    public function deleteResourceLink($resourceLink)
    {
// Delete any outstanding share keys for resource links for this consumer
        $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
            'WHERE (resource_link_pk = %d)', $resourceLink->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);

// Delete users
        if ($ok) {
            $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' ' .
                'WHERE (resource_link_pk = %d)', $resourceLink->getRecordId());
            $ok = sqlsrv_query($this->db, $sql);
        }

// Update any resource links for which this is the primary resource link
        if ($ok) {
            $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' ' .
                'SET primary_resource_link_pk = NULL ' .
                'WHERE (primary_resource_link_pk = %d)', $resourceLink->getRecordId());
            $ok = sqlsrv_query($this->db, $sql);
        }

// Delete resource link
        if ($ok) {
            $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' ' .
                'WHERE (resource_link_pk = %s)', $resourceLink->getRecordId());
            $ok = sqlsrv_query($this->db, $sql);
        }

        if ($ok) {
            $resourceLink->initialize();
        }

        return $ok;
    }

    public function getUserResultSourcedIDsResourceLink($resourceLink, $localOnly, $idScope)
    {
        $userResults = array();

        if ($localOnly) {
            $sql = sprintf('SELECT u.user_result_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' AS u ' .
                "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' AS rl ' .
                'ON u.resource_link_pk = rl.resource_link_pk ' .
                "WHERE (rl.resource_link_pk = %d) AND (rl.primary_resource_link_pk IS NULL)", $resourceLink->getRecordId());
        } else {
            $sql = sprintf('SELECT u.user_result_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' AS u ' .
                "INNER JOIN {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' AS rl ' .
                'ON u.resource_link_pk = rl.resource_link_pk ' .
                'WHERE ((rl.resource_link_pk = %d) AND (rl.primary_resource_link_pk IS NULL)) OR ' .
                '((rl.primary_resource_link_pk = %d) AND (share_approved = 1))', $resourceLink->getRecordId(),
                $resourceLink->getRecordId());
        }
        $rsUser = sqlsrv_query($this->db, $sql);
        if ($rsUser) {
            while ($row = sqlsrv_fetch_object($rsUser)) {
                $userresult = LTI\UserResult::fromResourceLink($resourceLink, $row->lti_user_id);
                if (is_null($idScope)) {
                    $userResults[] = $userresult;
                } else {
                    $userResults[$userresult->getId($idScope)] = $userresult;
                }
            }
        }

        return $userResults;
    }

    public function getSharesResourceLink($resourceLink)
    {
        $shares = array();

        $sql = sprintf('SELECT c.consumer_name, r.resource_link_pk, r.title, r.share_approved ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' AS r ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' AS c ON r.consumer_pk = c.consumer_pk ' .
            'WHERE (r.primary_resource_link_pk = %d) ' .
            'UNION ' .
            'SELECT c2.consumer_name, r2.resource_link_pk, r2.title, r2.share_approved ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_TABLE_NAME . ' AS r2 ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONTEXT_TABLE_NAME . ' AS x ON r2.context_pk = x.context_pk ' .
            "INNER JOIN {$this->dbTableNamePrefix}" . static::CONSUMER_TABLE_NAME . ' AS c2 ON x.consumer_pk = c2.consumer_pk ' .
            'WHERE (r2.primary_resource_link_pk = %d) ' .
            'ORDER BY consumer_name, title', $resourceLink->getRecordId(), $resourceLink->getRecordId());
        $rsShare = sqlsrv_query($this->db, $sql);
        if ($rsShare) {
            while ($row = sqlsrv_fetch_object($rsShare)) {
                $share = new LTI\ResourceLinkShare();
                $share->resourceLinkId = intval($row->resource_link_pk);
                $share->approved = (intval($row->share_approved) === 1);
                $shares[] = $share;
            }
        }

        return $shares;
    }

###
###  ConsumerNonce methods
###

    public function loadConsumerNonce($nonce)
    {
        $ok = false;

// Delete any expired nonce values
        $now = date("{$this->dateFormat} {$this->timeFormat}", time());
        $sql = "DELETE FROM {$this->dbTableNamePrefix}" . static::NONCE_TABLE_NAME . " WHERE expires <= '{$now}'";
        sqlsrv_query($this->db, $sql);

// Load the nonce
        $sql = sprintf("SELECT value AS T FROM {$this->dbTableNamePrefix}" . static::NONCE_TABLE_NAME . ' WHERE (consumer_pk = %d) AND (value = %s)',
            $nonce->getConsumer()->getRecordId(), $this->escape($nonce->getValue()));
        $rs_nonce = sqlsrv_query($this->db, $sql);
        if ($rs_nonce) {
            if (sqlsrv_fetch_object($rs_nonce)) {
                $ok = true;
            }
        }

        return $ok;
    }

    public function saveConsumerNonce($nonce)
    {
        $expires = date("{$this->dateFormat} {$this->timeFormat}", $nonce->expires);
        $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . static::NONCE_TABLE_NAME . " (consumer_pk, value, expires) VALUES (%d, %s, %s)",
            $nonce->getConsumer()->getRecordId(), $this->escape($nonce->getValue()), $this->escape($expires));
        $ok = sqlsrv_query($this->db, $sql);

        return $ok;
    }

###
###  ResourceLinkShareKey methods
###

    public function loadResourceLinkShareKey($shareKey)
    {
        $ok = false;

// Clear expired share keys
        $now = date("{$this->dateFormat} {$this->timeFormat}", time());
        $sql = "DELETE FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . " WHERE expires <= '{$now}'";
        sqlsrv_query($this->db, $sql);

// Load share key
//        $id = $this->escape_string($this->db, $shareKey->getId());
        $id = $shareKey->getId();
        $sql = 'SELECT resource_link_pk, auto_approve, expires ' .
            "FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
            "WHERE share_key_id = '{$id}'";
        $rsShareKey = sqlsrv_query($this->db, $sql);
        if ($rsShareKey) {
            $row = sqlsrv_fetch_object($rsShareKey);
            if ($row && (intval($row->resource_link_pk) === $shareKey->resourceLinkId)) {
                $shareKey->autoApprove = (intval($row->auto_approve) === 1);
                $shareKey->expires = date_timestamp_get($row->expires);
                $ok = true;
            }
        }

        return $ok;
    }

    public function saveResourceLinkShareKey($shareKey)
    {
        if ($shareKey->autoApprove) {
            $approve = 1;
        } else {
            $approve = 0;
        }
        $expires = date("{$this->dateFormat} {$this->timeFormat}", $shareKey->expires);
        $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
            '(share_key_id, resource_link_pk, auto_approve, expires) ' .
            "VALUES (%s, %d, {$approve}, '{$expires}')", $this->escape($shareKey->getId()), $shareKey->resourceLinkId);
        $ok = sqlsrv_query($this->db, $sql);

        return $ok;
    }

    public function deleteResourceLinkShareKey($shareKey)
    {
        $sql = "DELETE FROM {$this->dbTableNamePrefix}" . static::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . " WHERE share_key_id = '{$shareKey->getId()}'";

        $ok = sqlsrv_query($this->db, $sql);

        if ($ok) {
            $shareKey->initialize();
        }

        return $ok;
    }

###
###  UserResult methods
###

    public function loadUserResult($userresult)
    {
        $ok = false;
        if (!is_null($userresult->getRecordId())) {
            $sql = sprintf('SELECT user_result_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' ' .
                'WHERE (user_result_pk = %d)', $userresult->getRecordId());
        } else {
            $sql = sprintf('SELECT user_result_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' .
                "FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' ' .
                'WHERE (resource_link_pk = %d) AND (lti_user_id = %s)', $userresult->getResourceLink()->getRecordId(),
                $this->escape($userresult->getId(LTI\ToolProvider::ID_SCOPE_ID_ONLY)));
        }
        $rsUser = sqlsrv_query($this->db, $sql);
        if ($rsUser) {
            $row = sqlsrv_fetch_object($rsUser);
            if ($row) {
                $userresult->setRecordId(intval($row->user_result_pk));
                $userresult->setResourceLinkId(intval($row->resource_link_pk));
                $userresult->ltiUserId = $row->lti_user_id;
                $userresult->ltiResultSourcedId = $row->lti_result_sourcedid;
                $userresult->created = date_timestamp_get($row->created);
                $userresult->updated = date_timestamp_get($row->updated);
                $ok = true;
            }
        }

        return $ok;
    }

    public function saveUserResult($userresult)
    {
        $time = time();
        $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
        if (is_null($userresult->created)) {
            $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' (resource_link_pk, ' .
                'lti_user_id, lti_result_sourcedid, created, updated) ' .
                'VALUES (%d, %s, %s, %s, %s)', $userresult->getResourceLink()->getRecordId(),
                $this->escape($userresult->getId(LTI\ToolProvider::ID_SCOPE_ID_ONLY)),
                $this->escape($userresult->ltiResultSourcedId), $this->escape($now), $this->escape($now));
        } else {
            $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' ' .
                'SET lti_result_sourcedid = %s, updated = %s ' .
                'WHERE (user_result_pk = %d)', $this->escape($userresult->ltiResultSourcedId), $this->escape($now),
                $userresult->getRecordId());
        }
        $ok = sqlsrv_query($this->db, $sql);
        if ($ok) {
            if (is_null($userresult->created)) {
                $userresult->setRecordId($this->insert_id());
                $userresult->created = $time;
            }
            $userresult->updated = $time;
        }

        return $ok;
    }

    public function deleteUserResult($userresult)
    {
        $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . static::USER_RESULT_TABLE_NAME . ' ' .
            'WHERE (user_result_pk = %d)', $userresult->getRecordId());
        $ok = sqlsrv_query($this->db, $sql);

        if ($ok) {
            $userresult->initialize();
        }

        return $ok;
    }

    public function escape($value, $addQuotes = true)
    {
        if (is_null($value)) {
            $value = 'null';
        } else {
            if ($addQuotes) {
                $value = str_replace("'", "''", $value);
                $value = "'{$value}'";
            }
        }

        return $value;
    }

    private function insert_id()
    {
        $id = 0;
        $sql = 'SELECT SCOPE_IDENTITY() AS insid;';
        $rsId = sqlsrv_query($this->db, $sql);
        if ($rsId) {
            sqlsrv_fetch($rsId);
            $id = sqlsrv_get_field($rsId, 0, SQLSRV_PHPTYPE_INT);
        }

        return $id;
    }

}