ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
DataConnector_mysql.php
Go to the documentation of this file.
1<?php
2
4
12
23###
24# NB This class assumes that a MySQL connection has already been opened to the appropriate schema
25###
26
27
29{
30
31###
32### ToolConsumer methods
33###
34
42 public function loadToolConsumer($consumer)
43 {
44
45 $ok = false;
46 if (!empty($consumer->getRecordId())) {
47 $sql = sprintf('SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
48 'consumer_name, consumer_version, consumer_guid, ' .
49 'profile, tool_proxy, settings, protected, enabled, ' .
50 'enable_from, enable_until, last_access, created, updated ' .
51 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' .
52 "WHERE consumer_pk = %d",
53 $consumer->getRecordId());
54 } else {
55 $key256 = DataConnector::getConsumerKey($consumer->getKey());
56 $sql = sprintf('SELECT consumer_pk, name, consumer_key256, consumer_key, secret, lti_version, ' .
57 'consumer_name, consumer_version, consumer_guid, ' .
58 'profile, tool_proxy, settings, protected, enabled, ' .
59 'enable_from, enable_until, last_access, created, updated ' .
60 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' .
61 "WHERE consumer_key256 = %s",
62 DataConnector::quoted($key256));
63 }
64 $rsConsumer = mysql_query($sql);
65 if ($rsConsumer) {
66 while ($row = mysql_fetch_object($rsConsumer)) {
67 if (empty($key256) || empty($row->consumer_key) || ($consumer->getKey() === $row->consumer_key)) {
68 $consumer->setRecordId(intval($row->consumer_pk));
69 $consumer->name = $row->name;
70 $consumer->setkey(empty($row->consumer_key) ? $row->consumer_key256 : $row->consumer_key);
71 $consumer->secret = $row->secret;
72 $consumer->ltiVersion = $row->lti_version;
73 $consumer->consumerName = $row->consumer_name;
74 $consumer->consumerVersion = $row->consumer_version;
75 $consumer->consumerGuid = $row->consumer_guid;
76 $consumer->profile = json_decode($row->profile);
77 $consumer->toolProxy = $row->tool_proxy;
78 $settings = unserialize($row->settings);
79 if (!is_array($settings)) {
80 $settings = array();
81 }
82 $consumer->setSettings($settings);
83 $consumer->protected = (intval($row->protected) === 1);
84 $consumer->enabled = (intval($row->enabled) === 1);
85 $consumer->enableFrom = null;
86 if (!is_null($row->enable_from)) {
87 $consumer->enableFrom = strtotime($row->enable_from);
88 }
89 $consumer->enableUntil = null;
90 if (!is_null($row->enable_until)) {
91 $consumer->enableUntil = strtotime($row->enable_until);
92 }
93 $consumer->lastAccess = null;
94 if (!is_null($row->last_access)) {
95 $consumer->lastAccess = strtotime($row->last_access);
96 }
97 $consumer->created = strtotime($row->created);
98 $consumer->updated = strtotime($row->updated);
99 $ok = true;
100 break;
101 }
102 }
103 mysql_free_result($rsConsumer);
104 }
105
106 return $ok;
107
108 }
109
118 {
119
120 $id = $consumer->getRecordId();
121 $key = $consumer->getKey();
123 if ($key === $key256) {
124 $key = null;
125 }
126 $protected = ($consumer->protected) ? 1 : 0;
127 $enabled = ($consumer->enabled)? 1 : 0;
128 $profile = (!empty($consumer->profile)) ? json_encode($consumer->profile) : null;
129 $settingsValue = serialize($consumer->getSettings());
130 $time = time();
131 $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
132 $from = null;
133 if (!is_null($consumer->enableFrom)) {
134 $from = date("{$this->dateFormat} {$this->timeFormat}", $consumer->enableFrom);
135 }
136 $until = null;
137 if (!is_null($consumer->enableUntil)) {
138 $until = date("{$this->dateFormat} {$this->timeFormat}", $consumer->enableUntil);
139 }
140 $last = null;
141 if (!is_null($consumer->lastAccess)) {
142 $last = date($this->dateFormat, $consumer->lastAccess);
143 }
144 if (empty($id)) {
145 $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' (consumer_key256, consumer_key, name, ' .
146 'secret, lti_version, consumer_name, consumer_version, consumer_guid, profile, tool_proxy, settings, protected, enabled, ' .
147 'enable_from, enable_until, last_access, created, updated) ' .
148 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %d, %s, %s, %s, %s, %s)',
151 DataConnector::quoted($consumer->consumerName), DataConnector::quoted($consumer->consumerVersion), DataConnector::quoted($consumer->consumerGuid),
152 DataConnector::quoted($profile), DataConnector::quoted($consumer->toolProxy), DataConnector::quoted($settingsValue),
153 $protected, $enabled, DataConnector::quoted($from), DataConnector::quoted($until), DataConnector::quoted($last),
155 } else {
156 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' SET ' .
157 'consumer_key256 = %s, consumer_key = %s, ' .
158 'name = %s, secret= %s, lti_version = %s, consumer_name = %s, consumer_version = %s, consumer_guid = %s, ' .
159 'profile = %s, tool_proxy = %s, settings = %s, ' .
160 'protected = %d, enabled = %d, enable_from = %s, enable_until = %s, last_access = %s, updated = %s ' .
161 'WHERE consumer_pk = %d',
165 DataConnector::quoted($consumer->consumerName), DataConnector::quoted($consumer->consumerVersion), DataConnector::quoted($consumer->consumerGuid),
166 DataConnector::quoted($profile), DataConnector::quoted($consumer->toolProxy), DataConnector::quoted($settingsValue),
167 $protected, $enabled,
169 DataConnector::quoted($now), $consumer->getRecordId());
170 }
171 $ok = mysql_query($sql);
172 if ($ok) {
173 if (empty($id)) {
174 $consumer->setRecordId(mysql_insert_id());
175 $consumer->created = $time;
176 }
177 $consumer->updated = $time;
178 }
179
180 return $ok;
181
182 }
183
192 {
193
194// Delete any nonce values for this consumer
195 $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE consumer_pk = %d',
196 $consumer->getRecordId());
197 mysql_query($sql);
198
199// Delete any outstanding share keys for resource links for this consumer
200 $sql = sprintf('DELETE sk ' .
201 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
202 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
203 'WHERE rl.consumer_pk = %d',
204 $consumer->getRecordId());
205 mysql_query($sql);
206
207// Delete any outstanding share keys for resource links for contexts in this consumer
208 $sql = sprintf('DELETE sk ' .
209 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
210 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
211 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
212 'WHERE c.consumer_pk = %d',
213 $consumer->getRecordId());
214 mysql_query($sql);
215
216// Delete any users in resource links for this consumer
217 $sql = sprintf('DELETE u ' .
218 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' .
219 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
220 'WHERE rl.consumer_pk = %d',
221 $consumer->getRecordId());
222 mysql_query($sql);
223
224// Delete any users in resource links for contexts in this consumer
225 $sql = sprintf('DELETE u ' .
226 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' .
227 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
228 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
229 'WHERE c.consumer_pk = %d',
230 $consumer->getRecordId());
231 mysql_query($sql);
232
233// Update any resource links for which this consumer is acting as a primary resource link
234 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' .
235 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
236 'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' .
237 'WHERE rl.consumer_pk = %d',
238 $consumer->getRecordId());
239 $ok = mysql_query($sql);
240
241// Update any resource links for contexts in which this consumer is acting as a primary resource link
242 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' .
243 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
244 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
245 'SET prl.primary_resource_link_pk = NULL, prl.share_approved = NULL ' .
246 'WHERE c.consumer_pk = %d',
247 $consumer->getRecordId());
248 $ok = mysql_query($sql);
249
250// Delete any resource links for this consumer
251 $sql = sprintf('DELETE rl ' .
252 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' .
253 'WHERE rl.consumer_pk = %d',
254 $consumer->getRecordId());
255 mysql_query($sql);
256
257// Delete any resource links for contexts in this consumer
258 $sql = sprintf('DELETE rl ' .
259 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' .
260 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ON rl.context_pk = c.context_pk ' .
261 'WHERE c.consumer_pk = %d',
262 $consumer->getRecordId());
263 mysql_query($sql);
264
265// Delete any contexts for this consumer
266 $sql = sprintf('DELETE c ' .
267 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ' .
268 'WHERE c.consumer_pk = %d',
269 $consumer->getRecordId());
270 mysql_query($sql);
271
272// Delete consumer
273 $sql = sprintf('DELETE c ' .
274 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' c ' .
275 'WHERE c.consumer_pk = %d',
276 $consumer->getRecordId());
277 $ok = mysql_query($sql);
278
279 if ($ok) {
280 $consumer->initialize();
281 }
282
283 return $ok;
284
285 }
286
287###
288# Load all tool consumers from the database
289###
290 public function getToolConsumers()
291 {
292
293 $consumers = array();
294
295 $sql = 'SELECT consumer_pk, consumer_key, consumer_key, name, secret, lti_version, consumer_name, consumer_version, consumer_guid, ' .
296 'profile, tool_proxy, settings, ' .
297 'protected, enabled, enable_from, enable_until, last_access, created, updated ' .
298 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONSUMER_TABLE_NAME . ' ' .
299 'ORDER BY name';
300 $rsConsumers = mysql_query($sql);
301 if ($rsConsumers) {
302 while ($row = mysql_fetch_object($rsConsumers)) {
303 $consumer = new ToolProvider\ToolConsumer($row->consumer_key, $this);
304 $consumer->setRecordId(intval($row->consumer_pk));
305 $consumer->name = $row->name;
306 $consumer->secret = $row->secret;
307 $consumer->ltiVersion = $row->lti_version;
308 $consumer->consumerName = $row->consumer_name;
309 $consumer->consumerVersion = $row->consumer_version;
310 $consumer->consumerGuid = $row->consumer_guid;
311 $consumer->profile = json_decode($row->profile);
312 $consumer->toolProxy = $row->tool_proxy;
313 $settings = unserialize($row->settings);
314 if (!is_array($settings)) {
315 $settings = array();
316 }
317 $consumer->setSettings($settings);
318 $consumer->protected = (intval($row->protected) === 1);
319 $consumer->enabled = (intval($row->enabled) === 1);
320 $consumer->enableFrom = null;
321 if (!is_null($row->enable_from)) {
322 $consumer->enableFrom = strtotime($row->enable_from);
323 }
324 $consumer->enableUntil = null;
325 if (!is_null($row->enable_until)) {
326 $consumer->enableUntil = strtotime($row->enable_until);
327 }
328 $consumer->lastAccess = null;
329 if (!is_null($row->last_access)) {
330 $consumer->lastAccess = strtotime($row->last_access);
331 }
332 $consumer->created = strtotime($row->created);
333 $consumer->updated = strtotime($row->updated);
334 $consumers[] = $consumer;
335 }
336 mysql_free_result($rsConsumers);
337 }
338
339 return $consumers;
340
341 }
342
343###
344### ToolProxy methods
345###
346
347###
348# Load the tool proxy from the database
349###
350 public function loadToolProxy($toolProxy)
351 {
352
353 return false;
354
355 }
356
357###
358# Save the tool proxy to the database
359###
360 public function saveToolProxy($toolProxy)
361 {
362
363 return false;
364
365 }
366
367###
368# Delete the tool proxy from the database
369###
370 public function deleteToolProxy($toolProxy)
371 {
372
373 return false;
374
375 }
376
377###
378### Context methods
379###
380
388 public function loadContext($context)
389 {
390
391 $ok = false;
392 if (!empty($context->getRecordId())) {
393 $sql = sprintf('SELECT context_pk, consumer_pk, lti_context_id, settings, created, updated ' .
394 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' ' .
395 'WHERE (context_pk = %d)',
396 $context->getRecordId());
397 } else {
398 $sql = sprintf('SELECT context_pk, consumer_pk, lti_context_id, settings, created, updated ' .
399 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' ' .
400 'WHERE (consumer_pk = %d) AND (lti_context_id = %s)',
401 $context->getConsumer()->getRecordId(), DataConnector::quoted($context->ltiContextId));
402 }
403 $rs_context = mysql_query($sql);
404 if ($rs_context) {
405 $row = mysql_fetch_object($rs_context);
406 if ($row) {
407 $context->setRecordId(intval($row->context_pk));
408 $context->setConsumerId(intval($row->consumer_pk));
409 $context->ltiContextId = $row->lti_context_id;
410 $settings = unserialize($row->settings);
411 if (!is_array($settings)) {
412 $settings = array();
413 }
414 $context->setSettings($settings);
415 $context->created = strtotime($row->created);
416 $context->updated = strtotime($row->updated);
417 $ok = true;
418 }
419 }
420
421 return $ok;
422
423 }
424
432 public function saveContext($context)
433 {
434
435 $time = time();
436 $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
437 $settingsValue = serialize($context->getSettings());
438 $id = $context->getRecordId();
439 $consumer_pk = $context->getConsumer()->getRecordId();
440 if (empty($id)) {
441 $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' (consumer_pk, lti_context_id, ' .
442 'settings, created, updated) ' .
443 'VALUES (%d, %s, %s, %s, %s)',
444 $consumer_pk, DataConnector::quoted($context->ltiContextId),
445 DataConnector::quoted($settingsValue),
447 } else {
448 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' SET ' .
449 'lti_context_id = %s, settings = %s, '.
450 'updated = %s' .
451 'WHERE (consumer_pk = %d) AND (context_pk = %d)',
452 DataConnector::quoted($context->ltiContextId), DataConnector::quoted($settingsValue),
453 DataConnector::quoted($now), $consumer_pk, $id);
454 }
455 $ok = mysql_query($sql);
456 if ($ok) {
457 if (empty($id)) {
458 $context->setRecordId(mysql_insert_id());
459 $context->created = $time;
460 }
461 $context->updated = $time;
462 }
463
464 return $ok;
465
466 }
467
475 public function deleteContext($context)
476 {
477
478// Delete any outstanding share keys for resource links for this context
479 $sql = sprintf('DELETE sk ' .
480 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' sk ' .
481 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON sk.resource_link_pk = rl.resource_link_pk ' .
482 'WHERE rl.context_pk = %d',
483 $context->getRecordId());
484 mysql_query($sql);
485
486// Delete any users in resource links for this context
487 $sql = sprintf('DELETE u ' .
488 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' u ' .
489 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON u.resource_link_pk = rl.resource_link_pk ' .
490 'WHERE rl.context_pk = %d',
491 $context->getRecordId());
492 mysql_query($sql);
493
494// Update any resource links for which this consumer is acting as a primary resource link
495 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' prl ' .
496 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ON prl.primary_resource_link_pk = rl.resource_link_pk ' .
497 'SET prl.primary_resource_link_pk = null, prl.share_approved = null ' .
498 'WHERE rl.context_pk = %d',
499 $context->getRecordId());
500 $ok = mysql_query($sql);
501
502// Delete any resource links for this consumer
503 $sql = sprintf('DELETE rl ' .
504 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' rl ' .
505 'WHERE rl.context_pk = %d',
506 $context->getRecordId());
507 mysql_query($sql);
508
509// Delete context
510 $sql = sprintf('DELETE c ' .
511 "FROM {$this->dbTableNamePrefix}" . DataConnector::CONTEXT_TABLE_NAME . ' c ',
512 'WHERE c.context_pk = %d',
513 $context->getRecordId());
514 $ok = mysql_query($sql);
515 if ($ok) {
516 $context->initialize();
517 }
518
519 return $ok;
520
521 }
522
523###
524### ResourceLink methods
525###
526
534 public function loadResourceLink($resourceLink)
535 {
536
537 $ok = false;
538 if (!empty($resourceLink->getRecordId())) {
539 $sql = sprintf('SELECT resource_link_pk, context_pk, consumer_pk, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' .
540 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
541 'WHERE (resource_link_pk = %d)',
542 $resourceLink->getRecordId());
543 } else if (!empty($resourceLink->getContext())) {
544 $sql = sprintf('SELECT resource_link_pk, context_pk, consumer_pk, lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated ' .
545 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
546 'WHERE (context_pk = %d) AND (lti_resource_link_id = %s)',
547 $resourceLink->getContext()->getRecordId(), DataConnector::quoted($resourceLink->getId()));
548 } else {
549 $sql = sprintf('SELECT r.resource_link_pk, r.context_pk, r.consumer_pk, r.lti_resource_link_id, r.settings, r.primary_resource_link_pk, r.share_approved, r.created, r.updated ' .
550 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' r LEFT OUTER JOIN ' .
551 $this->dbTableNamePrefix . DataConnector::CONTEXT_TABLE_NAME . ' c ON r.context_pk = c.context_pk ' .
552 ' WHERE ((r.consumer_pk = %d) OR (c.consumer_pk = %d)) AND (lti_resource_link_id = %s)',
553 $resourceLink->getConsumer()->getRecordId(), $resourceLink->getConsumer()->getRecordId(), DataConnector::quoted($resourceLink->getId()));
554 }
555 $rsContext = mysql_query($sql);
556 if ($rsContext) {
557 $row = mysql_fetch_object($rsContext);
558 if ($row) {
559 $resourceLink->setRecordId(intval($row->resource_link_pk));
560 if (!is_null($row->context_pk)) {
561 $resourceLink->setContextId(intval($row->context_pk));
562 } else {
563 $resourceLink->setContextId(null);
564 }
565 if (!is_null($row->consumer_pk)) {
566 $resourceLink->setConsumerId(intval($row->consumer_pk));
567 } else {
568 $resourceLink->setConsumerId(null);
569 }
570 $resourceLink->ltiResourceLinkId = $row->lti_resource_link_id;
571 $settings = unserialize($row->settings);
572 if (!is_array($settings)) {
573 $settings = array();
574 }
575 $resourceLink->setSettings($settings);
576 if (!is_null($row->primary_resource_link_pk)) {
577 $resourceLink->primaryResourceLinkId = intval($row->primary_resource_link_pk);
578 } else {
579 $resourceLink->primaryResourceLinkId = null;
580 }
581 $resourceLink->shareApproved = (is_null($row->share_approved)) ? null : (intval($row->share_approved) === 1);
582 $resourceLink->created = strtotime($row->created);
583 $resourceLink->updated = strtotime($row->updated);
584 $ok = true;
585 }
586 }
587
588 return $ok;
589
590 }
591
599 public function saveResourceLink($resourceLink) {
600
601 if (is_null($resourceLink->shareApproved)) {
602 $approved = 'NULL';
603 } else if ($resourceLink->shareApproved) {
604 $approved = '1';
605 } else {
606 $approved = '0';
607 }
608 if (empty($resourceLink->primaryResourceLinkId)) {
609 $primaryResourceLinkId = 'NULL';
610 } else {
611 $primaryResourceLinkId = strval($resourceLink->primaryResourceLinkId);
612 }
613 $time = time();
614 $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
615 $settingsValue = serialize($resourceLink->getSettings());
616 if (!empty($resourceLink->getContext())) {
617 $consumerId = 'NULL';
618 $contextId = strval($resourceLink->getContext()->getRecordId());
619 } else if (!empty($resourceLink->getContextId())) {
620 $consumerId = 'NULL';
621 $contextId = strval($resourceLink->getContextId());
622 } else {
623 $consumerId = strval($resourceLink->getConsumer()->getRecordId());
624 $contextId = 'NULL';
625 }
626 $id = $resourceLink->getRecordId();
627 if (empty($id)) {
628 $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' (consumer_pk, context_pk, ' .
629 'lti_resource_link_id, settings, primary_resource_link_pk, share_approved, created, updated) ' .
630 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
631 $consumerId, $contextId, DataConnector::quoted($resourceLink->getId()),
632 DataConnector::quoted($settingsValue),
633 $primaryResourceLinkId, $approved, DataConnector::quoted($now), DataConnector::quoted($now));
634 } else if ($contextId !== 'NULL') {
635 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' SET ' .
636 'consumer_pk = %s, lti_resource_link_id = %s, settings = %s, '.
637 'primary_resource_link_pk = %s, share_approved = %s, updated = %s ' .
638 'WHERE (context_pk = %s) AND (resource_link_pk = %d)',
639 $consumerId, DataConnector::quoted($resourceLink->getId()),
640 DataConnector::quoted($settingsValue), $primaryResourceLinkId, $approved, DataConnector::quoted($now),
641 $contextId, $id);
642 } else {
643 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' SET ' .
644 'context_pk = %s, lti_resource_link_id = %s, settings = %s, '.
645 'primary_resource_link_pk = %s, share_approved = %s, updated = %s ' .
646 'WHERE (consumer_pk = %s) AND (resource_link_pk = %d)',
647 $contextId, DataConnector::quoted($resourceLink->getId()),
648 DataConnector::quoted($settingsValue), $primaryResourceLinkId, $approved, DataConnector::quoted($now),
649 $consumerId, $id);
650 }
651 $ok = mysql_query($sql);
652 if ($ok) {
653 if (empty($id)) {
654 $resourceLink->setRecordId(mysql_insert_id());
655 $resourceLink->created = $time;
656 }
657 $resourceLink->updated = $time;
658 }
659
660 return $ok;
661
662 }
663
671 public function deleteResourceLink($resourceLink)
672 {
673
674// Delete any outstanding share keys for resource links for this consumer
675 $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
676 'WHERE (resource_link_pk = %d)',
677 $resourceLink->getRecordId());
678 $ok = mysql_query($sql);
679
680// Delete users
681 if ($ok) {
682 $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
683 'WHERE (resource_link_pk = %d)',
684 $resourceLink->getRecordId());
685 $ok = mysql_query($sql);
686 }
687
688// Update any resource links for which this is the primary resource link
689 if ($ok) {
690 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
691 'SET primary_resource_link_pk = NULL ' .
692 'WHERE (primary_resource_link_pk = %d)',
693 $resourceLink->getRecordId());
694 $ok = mysql_query($sql);
695 }
696
697// Delete resource link
698 if ($ok) {
699 $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
700 'WHERE (resource_link_pk = %s)',
701 $resourceLink->getRecordId());
702 $ok = mysql_query($sql);
703 }
704
705 if ($ok) {
706 $resourceLink->initialize();
707 }
708
709 return $ok;
710
711 }
712
725 public function getUserResultSourcedIDsResourceLink($resourceLink, $localOnly, $idScope)
726 {
727
728 $users = array();
729
730 if ($localOnly) {
731 $sql = sprintf('SELECT u.user_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' .
732 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' AS u ' .
733 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' AS rl ' .
734 'ON u.resource_link_pk = rl.resource_link_pk ' .
735 "WHERE (rl.resource_link_pk = %d) AND (rl.primary_resource_link_pk IS NULL)",
736 $resourceLink->getRecordId());
737 } else {
738 $sql = sprintf('SELECT u.user_pk, u.lti_result_sourcedid, u.lti_user_id, u.created, u.updated ' .
739 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' AS u ' .
740 "INNER JOIN {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' AS rl ' .
741 'ON u.resource_link_pk = rl.resource_link_pk ' .
742 'WHERE ((rl.resource_link_pk = %d) AND (rl.primary_resource_link_pk IS NULL)) OR ' .
743 '((rl.primary_resource_link_pk = %d) AND (share_approved = 1))',
744 $resourceLink->getRecordId(), $resourceLink->getRecordId());
745 }
746 $rsUser = mysql_query($sql);
747 if ($rsUser) {
748 while ($row = mysql_fetch_object($rsUser)) {
749 $user = ToolProvider\User::fromResourceLink($resourceLink, $row->lti_user_id);
750 $user->setRecordId(intval($row->user_pk));
751 $user->ltiResultSourcedId = $row->lti_result_sourcedid;
752 $user->created = strtotime($row->created);
753 $user->updated = strtotime($row->updated);
754 if (is_null($idScope)) {
755 $users[] = $user;
756 } else {
757 $users[$user->getId($idScope)] = $user;
758 }
759 }
760 }
761
762 return $users;
763
764 }
765
773 public function getSharesResourceLink($resourceLink)
774 {
775
776 $shares = array();
777
778 $sql = sprintf('SELECT consumer_pk, resource_link_pk, share_approved ' .
779 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_TABLE_NAME . ' ' .
780 'WHERE (primary_resource_link_pk = %d) ' .
781 'ORDER BY consumer_pk',
782 $resourceLink->getRecordId());
783 $rsShare = mysql_query($sql);
784 if ($rsShare) {
785 while ($row = mysql_fetch_object($rsShare)) {
786 $share = new ToolProvider\ResourceLinkShare();
787 $share->resourceLinkId = intval($row->resource_link_pk);
788 $share->approved = (intval($row->share_approved) === 1);
789 $shares[] = $share;
790 }
791 }
792
793 return $shares;
794
795 }
796
797
798###
799### ConsumerNonce methods
800###
801
809 public function loadConsumerNonce($nonce)
810 {
811
812 $ok = true;
813
814// Delete any expired nonce values
815 $now = date("{$this->dateFormat} {$this->timeFormat}", time());
816 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . " WHERE expires <= '{$now}'";
817 mysql_query($sql);
818
819// Load the nonce
820 $sql = sprintf("SELECT value AS T FROM {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . ' WHERE (consumer_pk = %d) AND (value = %s)',
821 $nonce->getConsumer()->getRecordId(), DataConnector::quoted($nonce->getValue()));
822 $rs_nonce = mysql_query($sql);
823 if ($rs_nonce) {
824 $row = mysql_fetch_object($rs_nonce);
825 if ($row === false) {
826 $ok = false;
827 }
828 }
829
830 return $ok;
831
832 }
833
841 public function saveConsumerNonce($nonce)
842 {
843
844 $expires = date("{$this->dateFormat} {$this->timeFormat}", $nonce->expires);
845 $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::NONCE_TABLE_NAME . " (consumer_pk, value, expires) VALUES (%d, %s, %s)",
846 $nonce->getConsumer()->getRecordId(), DataConnector::quoted($nonce->getValue()),
847 DataConnector::quoted($expires));
848 $ok = mysql_query($sql);
849
850 return $ok;
851
852 }
853
854
855###
856### ResourceLinkShareKey methods
857###
858
866 public function loadResourceLinkShareKey($shareKey)
867 {
868
869 $ok = false;
870
871// Clear expired share keys
872 $now = date("{$this->dateFormat} {$this->timeFormat}", time());
873 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . " WHERE expires <= '{$now}'";
874 mysql_query($sql);
875
876// Load share key
877 $id = mysql_real_escape_string($shareKey->getId());
878 $sql = 'SELECT resource_link_pk, auto_approve, expires ' .
879 "FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
880 "WHERE share_key_id = '{$id}'";
881 $rsShareKey = mysql_query($sql);
882 if ($rsShareKey) {
883 $row = mysql_fetch_object($rsShareKey);
884 if ($row && (intval($row->resource_link_pk) === $shareKey->resourceLinkId)) {
885 $shareKey->autoApprove = (intval($row->auto_approve) === 1);
886 $shareKey->expires = strtotime($row->expires);
887 $ok = true;
888 }
889 }
890
891 return $ok;
892
893 }
894
902 public function saveResourceLinkShareKey($shareKey)
903 {
904
905 if ($shareKey->autoApprove) {
906 $approve = 1;
907 } else {
908 $approve = 0;
909 }
910 $expires = date("{$this->dateFormat} {$this->timeFormat}", $shareKey->expires);
911 $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . ' ' .
912 '(share_key_id, resource_link_pk, auto_approve, expires) ' .
913 "VALUES (%s, %d, {$approve}, '{$expires}')",
914 DataConnector::quoted($shareKey->getId()), $shareKey->resourceLinkId);
915 $ok = mysql_query($sql);
916
917 return $ok;
918
919 }
920
928 public function deleteResourceLinkShareKey($shareKey)
929 {
930
931 $sql = "DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::RESOURCE_LINK_SHARE_KEY_TABLE_NAME . " WHERE share_key_id = '{$shareKey->getId()}'";
932
933 $ok = mysql_query($sql);
934
935 if ($ok) {
936 $shareKey->initialize();
937 }
938
939 return $ok;
940
941 }
942
943
944###
945### User methods
946###
947
955 public function loadUser($user)
956 {
957
958 $ok = false;
959 if (!empty($user->getRecordId())) {
960 $sql = sprintf('SELECT user_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' .
961 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
962 'WHERE (user_pk = %d)',
963 $user->getRecordId());
964 } else {
965 $sql = sprintf('SELECT user_pk, resource_link_pk, lti_user_id, lti_result_sourcedid, created, updated ' .
966 "FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
967 'WHERE (resource_link_pk = %d) AND (lti_user_id = %s)',
968 $user->getResourceLink()->getRecordId(),
970 }
971 $rsUser = mysql_query($sql);
972 if ($rsUser) {
973 $row = mysql_fetch_object($rsUser);
974 if ($row) {
975 $user->setRecordId(intval($row->user_pk));
976 $user->setResourceLinkId(intval($row->resource_link_pk));
977 $user->ltiUserId = $row->lti_user_id;
978 $user->ltiResultSourcedId = $row->lti_result_sourcedid;
979 $user->created = strtotime($row->created);
980 $user->updated = strtotime($row->updated);
981 $ok = true;
982 }
983 }
984
985 return $ok;
986
987 }
988
996 public function saveUser($user)
997 {
998
999 $time = time();
1000 $now = date("{$this->dateFormat} {$this->timeFormat}", $time);
1001 if (is_null($user->created)) {
1002 $sql = sprintf("INSERT INTO {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' (resource_link_pk, ' .
1003 'lti_user_id, lti_result_sourcedid, created, updated) ' .
1004 'VALUES (%d, %s, %s, %s, %s)',
1005 $user->getResourceLink()->getRecordId(),
1008 } else {
1009 $sql = sprintf("UPDATE {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
1010 'SET lti_result_sourcedid = %s, updated = %s ' .
1011 'WHERE (user_pk = %d)',
1012 DataConnector::quoted($user->ltiResultSourcedId),
1014 $user->getRecordId());
1015 }
1016 $ok = mysql_query($sql);
1017 if ($ok) {
1018 if (is_null($user->created)) {
1019 $user->setRecordId(mysql_insert_id());
1020 $user->created = $time;
1021 }
1022 $user->updated = $time;
1023 }
1024
1025 return $ok;
1026
1027 }
1028
1036 public function deleteUser($user)
1037 {
1038
1039 $sql = sprintf("DELETE FROM {$this->dbTableNamePrefix}" . DataConnector::USER_RESULT_TABLE_NAME . ' ' .
1040 'WHERE (user_pk = %d)',
1041 $user->getRecordId());
1042 $ok = mysql_query($sql);
1043
1044 if ($ok) {
1045 $user->initialize();
1046 }
1047
1048 return $ok;
1049
1050 }
1051
1052}
sprintf('%.4f', $callTime)
date( 'd-M-Y', $objPHPExcel->getProperties() ->getCreated())
$users
Definition: authpage.php:44
An exception for terminatinating execution or to throw for unit testing.
Class to represent a tool consumer nonce.
Class to represent a tool consumer context.
Definition: Context.php:18
Class to represent an LTI Data Connector for MySQL.
saveResourceLinkShareKey($shareKey)
Save resource link share key object.
getUserResultSourcedIDsResourceLink($resourceLink, $localOnly, $idScope)
Get array of user objects.
getSharesResourceLink($resourceLink)
Get array of shares defined for this resource link.
deleteResourceLink($resourceLink)
Delete resource link object.
loadResourceLinkShareKey($shareKey)
Load resource link share key object.
deleteResourceLinkShareKey($shareKey)
Delete resource link share key object.
Class to provide a connection to a persistent store for LTI objects.
const CONTEXT_TABLE_NAME
Default name for database table used to store contexts.
const NONCE_TABLE_NAME
Default name for database table used to store nonce values.
const USER_RESULT_TABLE_NAME
Default name for database table used to store users.
static quoted($value, $addQuotes=true)
Quote a string for use in a database query.
const RESOURCE_LINK_TABLE_NAME
Default name for database table used to store resource links.
const CONSUMER_TABLE_NAME
Default name for database table used to store tool consumers.
static getConsumerKey($key)
Return a hash of a consumer key for values longer than 255 characters.
const RESOURCE_LINK_SHARE_KEY_TABLE_NAME
Default name for database table used to store resource link share keys.
Class to represent a tool consumer resource link share key.
Class to represent a tool consumer resource link share.
Class to represent a tool consumer.
Class to represent an LTI Tool Provider.
const ID_SCOPE_ID_ONLY
Use ID value only.
Class to represent a tool consumer user.
Definition: User.php:16
static fromResourceLink($resourceLink, $ltiUserId)
Class constructor from resource link.
Definition: User.php:413
$key
Definition: croninfo.php:18
$consumer
Definition: demo.php:30
if(!array_key_exists('StateId', $_REQUEST)) $id
$time
Definition: cron.php:21
$from