1<?php
2
3namespace IMSGlobal\LTI\ToolProvider\DataConnector;
4
5use IMSGlobal\LTI\ToolProvider;
6use IMSGlobal\LTI\ToolProvider\ConsumerNonce;
7use IMSGlobal\LTI\ToolProvider\Context;
8use IMSGlobal\LTI\ToolProvider\ResourceLink;
9use IMSGlobal\LTI\ToolProvider\ResourceLinkShareKey;
10use IMSGlobal\LTI\ToolProvider\ToolConsumer;
11use IMSGlobal\LTI\ToolProvider\User;
12use PDO;
13
14/**
15 * Class to represent an LTI Data Connector for PDO connections
16 *
17 * @author  Stephen P Vickers <svickers@imsglobal.org>
18 * @copyright  IMS Global Learning Consortium Inc
19 * @date  2016
20 * @version 3.0.0
21 * @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
22 */
23
24
25class DataConnector_pdo extends DataConnector
26{
27
28/**
29 * Class constructor
30 *
31 * @param object $db                 Database connection object
32 * @param string $dbTableNamePrefix  Prefix for database table names (optional, default is none)
33 */
34    public function __construct($db, $dbTableNamePrefix = '')
35    {
36
37        parent::__construct($db, $dbTableNamePrefix);
38        if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'oci') {
39            $this->date_format = 'd-M-Y';
40        }
41
42    }
43
44###
45###  ToolConsumer methods
46###
47
48/**
49 * Load tool consumer object.
50 *
51 * @param ToolConsumer $consumer ToolConsumer object
52 *
53 * @return boolean True if the tool consumer object was successfully loaded
54 */
55    public function loadToolConsumer($consumer)
56    {
57
58        $ok = false;
59        if (!empty($consumer->getRecordId())) {
60            $sql = 'SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
61                   'consumer_name, consumer_version, consumer_guid, ' .
62                   'profile, tool_proxy, settings, protected, enabled, ' .
63                   'enable_from, enable_until, last_access, created, updated ' .
64                   "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' .
65                   'WHERE consumer_pk = :id';
66            $query = $this->db->prepare($sql);
67            $id = $consumer->getRecordId();
68            $query->bindValue('id', $id, PDO::PARAM_INT);
69        } else {
70            $sql = 'SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
71                   'consumer_name, consumer_version, consumer_guid, ' .
72                   'profile, tool_proxy, settings, protected, enabled, ' .
73                   'enable_from, enable_until, last_access, created, updated ' .
74                   "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' .
75                   'WHERE consumer_key256 = :key256';
76            $query = $this->db->prepare($sql);
77            $key256 = DataConnector::getConsumerKey($consumer->getKey());
78            $query->bindValue('key256', $key256, PDO::PARAM_STR);
79        }
80
81        if ($query->execute()) {
82            while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
83                $row = array_change_key_case($row);
84                if (empty($key256) || empty($row['consumer_key']) || ($consumer->getKey() === $row['consumer_key'])) {
85                    $consumer->setRecordId(intval($row['consumer_pk']));
86                    $consumer->name = $row['name'];
87                    $consumer->setkey(empty($row['consumer_key']) ? $row['consumer_key256'] : $row['consumer_key']);
88                    $consumer->secret = $row['secret'];
89                    $consumer->ltiVersion = $row['lti_version'];
90                    $consumer->consumerName = $row['consumer_name'];
91                    $consumer->consumerVersion = $row['consumer_version'];
92                    $consumer->consumerGuid = $row['consumer_guid'];
93                    $consumer->profile = json_decode($row['profile']);
94                    $consumer->toolProxy = $row['tool_proxy'];
95                    $settings = unserialize($row['settings']);
96                    if (!is_array($settings)) {
97                        $settings = array();
98                    }
99                    $consumer->setSettings($settings);
100                    $consumer->protected = (intval($row['protected']) === 1);
101                    $consumer->enabled = (intval($row['enabled']) === 1);
102                    $consumer->enableFrom = null;
103                    if (!is_null($row['enable_from'])) {
104                        $consumer->enableFrom = strtotime($row['enable_from']);
105                    }
106                    $consumer->enableUntil = null;
107                    if (!is_null($row['enable_until'])) {
108                        $consumer->enableUntil = strtotime($row['enable_until']);
109                    }
110                    $consumer->lastAccess = null;
111                    if (!is_null($row['last_access'])) {
112                        $consumer->lastAccess = strtotime($row['last_access']);
113                    }
114                    $consumer->created = strtotime($row['created']);
115                    $consumer->updated = strtotime($row['updated']);
116                    $ok = true;
117                    break;
118                }
119            }
120        }
121
122        return $ok;
123
124    }
125
126/**
127 * Save tool consumer object.
128 *
129 * @param ToolConsumer $consumer Consumer object
130 *
131 * @return boolean True if the tool consumer object was successfully saved
132 */
133    public function saveToolConsumer($consumer)
134    {
135
136        $id = $consumer->getRecordId();
137        $key = $consumer->getKey();
138        $key256 = $this->getConsumerKey($key);
139        if ($key === $key256) {
140            $key = null;
141        }
142        $protected = ($consumer->protected) ? 1 : 0;
143        $enabled = ($consumer->enabled)? 1 : 0;
144        $profile = (!empty($consumer->profile)) ? json_encode($consumer->profile) : null;
145        $settingsValue = serialize($consumer->getSettings());
146        $time = time();
147        $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
148        $from = null;
149        if (!is_null($consumer->enableFrom)) {
150            $from = date("{$this->dateFormat} {$this->timeFormat}", $consumer->enableFrom);
151        }
152        $until = null;
153        if (!is_null($consumer->enableUntil)) {
154            $until = date("{$this->dateFormat} {$this->timeFormat}", $consumer->enableUntil);
155        }
156        $last = null;
157        if (!is_null($consumer->lastAccess)) {
158            $last = date($this->dateFormat, $consumer->lastAccess);
159        }
160        if (empty($id)) {
161            $sql = "INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' (consumer_key256, consumer_key, name, ' .
162                   'secret, lti_version, consumer_name, consumer_version, consumer_guid, profile, tool_proxy, settings, protected, enabled, ' .
163                   'enable_from, enable_until, last_access, created, updated) ' .
164                   'VALUES (:key256, :key, :name, :secret, :lti_version, :consumer_name, :consumer_version, :consumer_guid, :profile, :tool_proxy, :settings, ' .
165                   ':protected, :enabled, :enable_from, :enable_until, :last_access, :created, :updated)';
166            $query = $this->db->prepare($sql);
167            $query->bindValue('key256', $key256, PDO::PARAM_STR);
168            $query->bindValue('key', $key, PDO::PARAM_STR);
169            $query->bindValue('name', $consumer->name, PDO::PARAM_STR);
170            $query->bindValue('secret', $consumer->secret, PDO::PARAM_STR);
171            $query->bindValue('lti_version', $consumer->ltiVersion, PDO::PARAM_STR);
172            $query->bindValue('consumer_name', $consumer->consumerName, PDO::PARAM_STR);
173            $query->bindValue('consumer_version', $consumer->consumerVersion, PDO::PARAM_STR);
174            $query->bindValue('consumer_guid', $consumer->consumerGuid, PDO::PARAM_STR);
175            $query->bindValue('profile', $profile, PDO::PARAM_STR);
176            $query->bindValue('tool_proxy', $consumer->toolProxy, PDO::PARAM_STR);
177            $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
178            $query->bindValue('protected', $protected, PDO::PARAM_INT);
179            $query->bindValue('enabled', $enabled, PDO::PARAM_INT);
180            $query->bindValue('enable_from', $from, PDO::PARAM_STR);
181            $query->bindValue('enable_until', $until, PDO::PARAM_STR);
182            $query->bindValue('last_access', $last, PDO::PARAM_STR);
183            $query->bindValue('created', $now, PDO::PARAM_STR);
184            $query->bindValue('updated', $now, PDO::PARAM_STR);
185        } else {
186            $sql = 'UPDATE ' . $this->dbTableNamePrefix . DataConnector::CONSUMER_TABLE_NAME . ' ' .
187                   'SET consumer_key256 = :key256, consumer_key = :key, name = :name, secret = :secret, lti_version = :lti_version, ' .
188                   'consumer_name = :consumer_name, consumer_version = :consumer_version, consumer_guid = :consumer_guid, ' .
189                   'profile = :profile, tool_proxy = :tool_proxy, settings = :settings, ' .
190                   'protected = :protected, enabled = :enabled, enable_from = :enable_from, enable_until = :enable_until, last_access = :last_access, updated = :updated ' .
191                   'WHERE consumer_pk = :id';
192            $query = $this->db->prepare($sql);
193            $query->bindValue('key256', $key256, PDO::PARAM_STR);
194            $query->bindValue('key', $key, PDO::PARAM_STR);
195            $query->bindValue('name', $consumer->name, PDO::PARAM_STR);
196            $query->bindValue('secret', $consumer->secret, PDO::PARAM_STR);
197            $query->bindValue('lti_version', $consumer->ltiVersion, PDO::PARAM_STR);
198            $query->bindValue('consumer_name', $consumer->consumerName, PDO::PARAM_STR);
199            $query->bindValue('consumer_version', $consumer->consumerVersion, PDO::PARAM_STR);
200            $query->bindValue('consumer_guid', $consumer->consumerGuid, PDO::PARAM_STR);
201            $query->bindValue('profile', $profile, PDO::PARAM_STR);
202            $query->bindValue('tool_proxy', $consumer->toolProxy, PDO::PARAM_STR);
203            $query->bindValue('settings', $settingsValue, PDO::PARAM_STR);
204            $query->bindValue('protected', $protected, PDO::PARAM_INT);
205            $query->bindValue('enabled', $enabled, PDO::PARAM_INT);
206            $query->bindValue('enable_from', $from, PDO::PARAM_STR);
207            $query->bindValue('enable_until', $until, PDO::PARAM_STR);
208            $query->bindValue('last_access', $last, PDO::PARAM_STR);
209            $query->bindValue('updated', $now, PDO::PARAM_STR);
210            $query->bindValue('id', $id, PDO::PARAM_INT);
211        }
212        $ok = $query->execute();
213        if ($ok) {
214            if (empty($id)) {
215                $consumer->setRecordId(intval($this->db->lastInsertId()));
216                $consumer->created = $time;
217            }
218            $consumer->updated = $time;
219        }
220
221        return $ok;
222
223    }
224
225/**
226 * Delete tool consumer object.
227 *
228 * @param ToolConsumer $consumer Consumer object
229 *
230 * @return boolean True if the tool consumer object was successfully deleted
231 */
232    public function deleteToolConsumer($consumer)
233    {
234
235        $id = $consumer->getRecordId();
236
237// Delete any nonce values for this consumer
238        $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE consumer_pk = :id';
239        $query = $this->db->prepare($sql);
240        $query->bindValue('id', $id, PDO::PARAM_INT);
241        $query->execute();
242
243// Delete any outstanding share keys for resource links for this consumer
244        $sql = 'DELETE sk ' .
245               "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
246               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
247               'WHERE rl.consumer_pk = :id';
248        $query = $this->db->prepare($sql);
249        $query->bindValue('id', $id, PDO::PARAM_INT);
250        $query->execute();
251
252// Delete any outstanding share keys for resource links for contexts in this consumer
253        $sql = 'DELETE sk ' .
254               "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
255               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
256               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
257               'WHERE c.consumer_pk = :id';
258        $query = $this->db->prepare($sql);
259        $query->bindValue('id', $id, PDO::PARAM_INT);
260        $query->execute();
261
262// Delete any users in resource links for this consumer
263        $sql = 'DELETE u ' .
264               "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' .
265               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
266               'WHERE rl.consumer_pk = :id';
267        $query = $this->db->prepare($sql);
268        $query->bindValue('id', $id, PDO::PARAM_INT);
269        $query->execute();
270
271// Delete any users in resource links for contexts in this consumer
272        $sql = 'DELETE u ' .
273               "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' .
274               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
275               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
276               'WHERE c.consumer_pk = :id';
277        $query = $this->db->prepare($sql);
278        $query->bindValue('id', $id, PDO::PARAM_INT);
279        $query->execute();
280
281// Update any resource links for which this consumer is acting as a primary resource link
282        $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' .
283               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
284               'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' .
285               'WHERE rl.consumer_pk = :id';
286        $query = $this->db->prepare($sql);
287        $query->bindValue('id', $id, PDO::PARAM_INT);
288        $query->execute();
289
290// Update any resource links for contexts in which this consumer is acting as a primary resource link
291        $sql = "UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' .
292               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
293               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
294               'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' .
295               'WHERE c.consumer_pk = :id';
296        $query = $this->db->prepare($sql);
297        $query->bindValue('id', $id, PDO::PARAM_INT);
298        $query->execute();
299
300// Delete any resource links for this consumer
301        $sql = 'DELETE rl ' .
302               "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' .
303               'WHERE rl.consumer_pk = :id';
304        $query = $this->db->prepare($sql);
305        $query->bindValue('id', $id, PDO::PARAM_INT);
306        $query->execute();
307
308// Delete any resource links for contexts in this consumer
309        $sql = 'DELETE rl ' .
310               "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' .
311               "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
312               'WHERE c.consumer_pk = :id';
313        $query = $this->db->prepare($sql);
314        $query->bindValue('id', $id, PDO::PARAM_INT);
315        $query->execute();
316
317// Delete any contexts for this consumer
318        $sql = 'DELETE c ' .
319               "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ' .
320               'WHERE c.consumer_pk = :id';
321        $query = $this->db->prepare($sql);
322        $query->bindValue('id', $id, PDO::PARAM_INT);
323        $query->execute();
324
325// Delete consumer
326        $sql = 'DELETE c ' .
327               "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' c ' .
328               'WHERE c.consumer_pk = :id';
329        $query = $this->db->prepare($sql);
330        $query->bindValue('id', $id, PDO::PARAM_INT);
331        $ok = $query->execute();
332
333        if ($ok) {
334            $consumer->initialize();
335        }
336
337        return $ok;
338
339    }
340
341###
342#    Load all tool consumers from the database
343###
344    public function getToolConsumers()
345    {
346
347        $consumers = array();
348
349        $sql = 'SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
350               'consumer_name, consumer_version, consumer_guid, ' .
351               'profile, tool_proxy, settings, protected, enabled, ' .
352               'enable_from, enable_until, last_access, created, updated ' .
353               "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' .
354               'ORDER BY name';
355        $query = $this->db->prepare($sql);
356        $ok = ($query !== FALSE);
357
358        if ($ok) {
359            $ok = $query->execute();
360        }
361
362        if ($ok) {
363            while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
364                $row = array_change_key_case($row);
365                $key = empty($row['consumer_key']) ? $row['consumer_key256'] : $row['consumer_key'];
366                $consumer = new ToolProvider\ToolConsumer($key, $this);
367                $consumer->setRecordId(intval($row['consumer_pk']));
368                $consumer->name = $row['name'];
369                $consumer->secret = $row['secret'];
370                $consumer->ltiVersion = $row['lti_version'];
371                $consumer->consumerName = $row['consumer_name'];
372                $consumer->consumerVersion = $row['consumer_version'];
373                $consumer->consumerGuid = $row['consumer_guid'];
374                $consumer->profile = json_decode($row['profile']);
375                $consumer->toolProxy = $row['tool_proxy'];
376                $settings = unserialize($row['settings']);
377                if (!is_array($settings)) {
378                    $settings = array();
379                }
380                $consumer->setSettings($settings);
381                $consumer->protected = (intval($row['protected']) === 1);
382                $consumer->enabled = (intval($row['enabled']) === 1);
383                $consumer->enableFrom = null;
384                if (!is_null($row['enable_from'])) {
385                    $consumer->enableFrom = strtotime($row['enable_from']);
386                }
387                $consumer->enableUntil = null;
388                if (!is_null($row['enable_until'])) {
389                    $consumer->enableUntil = strtotime($row['enable_until']);
390                }
391                $consumer->lastAccess = null;
392                if (!is_null($row['last_access'])) {
393                    $consumer->lastAccess = strtotime($row['last_access']);
394                }
395                $consumer->created = strtotime($row['created']);
396                $consumer->updated = strtotime($row['updated']);
397                $consumers[] = $consumer;
398            }
399        }
400
401        return $consumers;
402
403    }
404
405###
406###  ToolProxy methods
407###
408
409###
410#    Load the tool proxy from the database
411###
412    public function loadToolProxy($toolProxy)
413    {
414
415        return false;
416
417    }
418
419###
420#    Save the tool proxy to the database
421###
422    public function saveToolProxy($toolProxy)
423    {
424
425        return false;
426
427    }
428
429###
430#    Delete the tool proxy from the database
431###
432    public function deleteToolProxy($toolProxy)
433    {
434
435        return false;
436
437    }
438
439###
440###  Context methods
441###
442
443/**
444 * Load context object.
445 *
446 * @param Context $context Context object
447 *
448 * @return boolean True if the context object was successfully loaded
449 */
450    public function loadContext($context)
451    {
452
453        $ok = false;
454        if (!empty($context->getRecordId())) {
455            $sql = 'SELECT context_pk, consumer_pk, lti_context_id, 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}
1191