ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
SQL.php
Go to the documentation of this file.
1<?php
2
3namespace SimpleSAML\Store;
4
5use \SimpleSAML_Configuration as Configuration;
6use \SimpleSAML\Logger;
7use \SimpleSAML\Store;
8
14class SQL extends Store
15{
21 public $pdo;
22
23
29 public $driver;
30
31
37 public $prefix;
38
39
46
47
51 public function __construct()
52 {
53 $config = Configuration::getInstance();
54
55 $dsn = $config->getString('store.sql.dsn');
56 $username = $config->getString('store.sql.username', null);
57 $password = $config->getString('store.sql.password', null);
58 $options = $config->getArray('store.sql.options', null);
59 $this->prefix = $config->getString('store.sql.prefix', 'simpleSAMLphp');
60 try {
61 $this->pdo = new \PDO($dsn, $username, $password, $options);
62 } catch (\PDOException $e) {
63 throw new \Exception("Database error: " . $e->getMessage());
64 }
65 $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
66
67 $this->driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
68
69 if ($this->driver === 'mysql') {
70 $this->pdo->exec('SET time_zone = "+00:00"');
71 }
72
73 $this->initTableVersionTable();
74 $this->initKVTable();
75 }
76
77
81 private function initTableVersionTable()
82 {
83 $this->tableVersions = array();
84
85 try {
86 $fetchTableVersion = $this->pdo->query('SELECT _name, _version FROM '.$this->prefix.'_tableVersion');
87 } catch (\PDOException $e) {
88 $this->pdo->exec(
89 'CREATE TABLE '.$this->prefix.
90 '_tableVersion (_name VARCHAR(30) NOT NULL UNIQUE, _version INTEGER NOT NULL)'
91 );
92 return;
93 }
94
95 while (($row = $fetchTableVersion->fetch(\PDO::FETCH_ASSOC)) !== false) {
96 $this->tableVersions[$row['_name']] = (int) $row['_version'];
97 }
98 }
99
100
104 private function initKVTable()
105 {
106 $current_version = $this->getTableVersion('kvstore');
107
108 $text_t = 'TEXT';
109 if ($this->driver === 'mysql') {
110 // TEXT data type has size constraints that can be hit at some point, so we use LONGTEXT instead
111 $text_t = 'LONGTEXT';
112 }
113
118 $table_updates = array(
119 array(
120 'CREATE TABLE '.$this->prefix.
121 '_kvstore (_type VARCHAR(30) NOT NULL, _key VARCHAR(50) NOT NULL, _value '.$text_t.
122 ' NOT NULL, _expire TIMESTAMP, PRIMARY KEY (_key, _type))',
123 'CREATE INDEX '.$this->prefix.'_kvstore_expire ON '.$this->prefix.'_kvstore (_expire)'
124 ),
134 array(
135 'CREATE TABLE '.$this->prefix.
136 '_kvstore_new (_type VARCHAR(30) NOT NULL, _key VARCHAR(50) NOT NULL, _value '.$text_t.
137 ' NOT NULL, _expire TIMESTAMP NULL, PRIMARY KEY (_key, _type))',
138 'INSERT INTO '.$this->prefix.'_kvstore_new SELECT * FROM ' . $this->prefix.'_kvstore',
139 'DROP TABLE '.$this->prefix.'_kvstore',
140 'ALTER TABLE '.$this->prefix.'_kvstore_new RENAME TO ' . $this->prefix . '_kvstore',
141 'CREATE INDEX '.$this->prefix.'_kvstore_expire ON '.$this->prefix.'_kvstore (_expire)'
142 )
143 );
144
145 $latest_version = count($table_updates);
146
147 if ($current_version == $latest_version) {
148 return;
149 }
150
151 // Only run queries for after the current version
152 $updates_to_run = array_slice($table_updates, $current_version);
153
154 foreach ($updates_to_run as $version_updates) {
155 foreach ($version_updates as $query) {
156 $this->pdo->exec($query);
157 }
158 }
159
160 $this->setTableVersion('kvstore', $latest_version);
161 }
162
163
171 public function getTableVersion($name)
172 {
173 assert(is_string($name));
174
175 if (!isset($this->tableVersions[$name])) {
176 return 0;
177 }
178
179 return $this->tableVersions[$name];
180 }
181
182
190 {
191 assert(is_string($name));
192 assert(is_int($version));
193
194 $this->insertOrUpdate(
195 $this->prefix.'_tableVersion',
196 array('_name'),
197 array('_name' => $name, '_version' => $version)
198 );
199 $this->tableVersions[$name] = $version;
200 }
201
202
212 public function insertOrUpdate($table, array $keys, array $data)
213 {
214 assert(is_string($table));
215
216 $colNames = '('.implode(', ', array_keys($data)).')';
217 $values = 'VALUES(:'.implode(', :', array_keys($data)).')';
218
219 switch ($this->driver) {
220 case 'mysql':
221 $query = 'REPLACE INTO '.$table.' '.$colNames.' '.$values;
222 $query = $this->pdo->prepare($query);
223 $query->execute($data);
224 return;
225 case 'sqlite':
226 $query = 'INSERT OR REPLACE INTO '.$table.' '.$colNames.' '.$values;
227 $query = $this->pdo->prepare($query);
228 $query->execute($data);
229 return;
230 }
231
232 // default implementation, try INSERT, and UPDATE if that fails.
233 $insertQuery = 'INSERT INTO '.$table.' '.$colNames.' '.$values;
234 $insertQuery = $this->pdo->prepare($insertQuery);
235 try {
236 $insertQuery->execute($data);
237 return;
238 } catch (\PDOException $e) {
239 $ecode = (string) $e->getCode();
240 switch ($ecode) {
241 case '23505': // PostgreSQL
242 break;
243 default:
244 Logger::error('Error while saving data: '.$e->getMessage());
245 throw $e;
246 }
247 }
248
249 $updateCols = array();
250 $condCols = array();
251 foreach ($data as $col => $value) {
252 $tmp = $col.' = :'.$col;
253
254 if (in_array($col, $keys, true)) {
255 $condCols[] = $tmp;
256 } else {
257 $updateCols[] = $tmp;
258 }
259 }
260
261 $updateQuery = 'UPDATE '.$table.' SET '.implode(',', $updateCols).' WHERE '.implode(' AND ', $condCols);
262 $updateQuery = $this->pdo->prepare($updateQuery);
263 $updateQuery->execute($data);
264 }
265
266
270 private function cleanKVStore()
271 {
272 Logger::debug('store.sql: Cleaning key-value store.');
273
274 $query = 'DELETE FROM '.$this->prefix.'_kvstore WHERE _expire < :now';
275 $params = array('now' => gmdate('Y-m-d H:i:s'));
276
277 $query = $this->pdo->prepare($query);
278 $query->execute($params);
279 }
280
281
290 public function get($type, $key)
291 {
292 assert(is_string($type));
293 assert(is_string($key));
294
295 if (strlen($key) > 50) {
296 $key = sha1($key);
297 }
298
299 $query = 'SELECT _value FROM '.$this->prefix.
300 '_kvstore WHERE _type = :type AND _key = :key AND (_expire IS NULL OR _expire > :now)';
301 $params = array('type' => $type, 'key' => $key, 'now' => gmdate('Y-m-d H:i:s'));
302
303 $query = $this->pdo->prepare($query);
304 $query->execute($params);
305
306 $row = $query->fetch(\PDO::FETCH_ASSOC);
307 if ($row === false) {
308 return null;
309 }
310
311 $value = $row['_value'];
312 if (is_resource($value)) {
313 $value = stream_get_contents($value);
314 }
315 $value = urldecode($value);
316 $value = unserialize($value);
317
318 if ($value === false) {
319 return null;
320 }
321 return $value;
322 }
323
324
333 public function set($type, $key, $value, $expire = null)
334 {
335 assert(is_string($type));
336 assert(is_string($key));
337 assert($expire === null || (is_int($expire) && $expire > 2592000));
338
339 if (rand(0, 1000) < 10) {
340 $this->cleanKVStore();
341 }
342
343 if (strlen($key) > 50) {
344 $key = sha1($key);
345 }
346
347 if ($expire !== null) {
348 $expire = gmdate('Y-m-d H:i:s', $expire);
349 }
350
351 $value = serialize($value);
352 $value = rawurlencode($value);
353
354 $data = array(
355 '_type' => $type,
356 '_key' => $key,
357 '_value' => $value,
358 '_expire' => $expire,
359 );
360
361 $this->insertOrUpdate($this->prefix.'_kvstore', array('_type', '_key'), $data);
362 }
363
364
371 public function delete($type, $key)
372 {
373 assert(is_string($type));
374 assert(is_string($key));
375
376 if (strlen($key) > 50) {
377 $key = sha1($key);
378 }
379
380 $data = array(
381 '_type' => $type,
382 '_key' => $key,
383 );
384
385 $query = 'DELETE FROM '.$this->prefix.'_kvstore WHERE _type=:_type AND _key=:_key';
386 $query = $this->pdo->prepare($query);
387 $query->execute($data);
388 }
389}
$version
Definition: build.php:27
An exception for terminatinating execution or to throw for unit testing.
static error($string)
Definition: Logger.php:166
static debug($string)
Definition: Logger.php:211
initTableVersionTable()
Initialize the table-version table.
Definition: SQL.php:81
setTableVersion($name, $version)
Set table version.
Definition: SQL.php:189
getTableVersion($name)
Get table version.
Definition: SQL.php:171
initKVTable()
Initialize key-value table.
Definition: SQL.php:104
insertOrUpdate($table, array $keys, array $data)
Insert or update a key-value in the store.
Definition: SQL.php:212
__construct()
Initialize the SQL data store.
Definition: SQL.php:51
cleanKVStore()
Clean the key-value table of expired entries.
Definition: SQL.php:270
$password
Definition: cron.php:14
$key
Definition: croninfo.php:18
$config
Definition: bootstrap.php:15
$expire
Definition: saml2-acs.php:140
$keys
foreach($paths as $path) $dsn
Definition: migrateto20.php:56
$row
$query
$type
if(empty($password)) $table
Definition: pwgen.php:24
$values
$data
Definition: bench.php:6