1<?php 2 3/** 4 * SQLPermanentStorage 5 * 6 * Generic SQL Store to store key value pairs. To be used in several other modules that needs 7 * to store data permanently. 8 * 9 * @author Andreas Åkre Solberg <andreas@uninett.no>, UNINETT AS. 10 * @package SimpleSAMLphp 11 */ 12class sspmod_core_Storage_SQLPermanentStorage 13{ 14 private $db; 15 16 public function __construct($name, $config = null) 17 { 18 if (is_null($config)) { 19 $config = SimpleSAML_Configuration::getInstance(); 20 } 21 22 $datadir = $config->getPathValue('datadir', 'data/'); 23 24 if (!is_dir($datadir)) { 25 throw new Exception('Data directory ['.$datadir.'] does not exist'); 26 } else if (!is_writable($datadir)) { 27 throw new Exception('Data directory ['.$datadir.'] is not writable'); 28 } 29 30 $sqllitedir = $datadir.'sqllite/'; 31 if (!is_dir($sqllitedir)) { 32 mkdir($sqllitedir); 33 } 34 35 $dbfile = 'sqlite:'.$sqllitedir.$name.'.sqlite'; 36 if ($this->db = new \PDO($dbfile)) { 37 $q = @$this->db->query('SELECT key1 FROM data LIMIT 1'); 38 if ($q === false) { 39 $this->db->exec(' 40 CREATE TABLE data ( 41 key1 text, 42 key2 text, 43 type text, 44 value text, 45 created timestamp, 46 updated timestamp, 47 expire timestamp, 48 PRIMARY KEY (key1,key2,type) 49 ); 50 '); 51 } 52 } else { 53 throw new Exception('Error creating SQL lite database ['.$dbfile.'].'); 54 } 55 } 56 57 public function set($type, $key1, $key2, $value, $duration = null) 58 { 59 if ($this->exists($type, $key1, $key2)) { 60 $this->update($type, $key1, $key2, $value, $duration); 61 } else { 62 $this->insert($type, $key1, $key2, $value, $duration); 63 } 64 } 65 66 private function insert($type, $key1, $key2, $value, $duration = null) 67 { 68 $expire = is_null($duration) ? null : (time() + $duration); 69 70 $query = "INSERT INTO data (key1, key2, type, created, updated, expire, value)". 71 " VALUES(:key1, :key2, :type, :created, :updated, :expire, :value)"; 72 $prepared = $this->db->prepare($query); 73 $data = array(':key1' => $key1, ':key2' => $key2, 74 ':type' => $type, ':created' => time(), 75 ':updated' => time(), ':expire' => $expire, 76 ':value' => serialize($value)); 77 $prepared->execute($data); 78 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 79 return $results; 80 } 81 82 private function update($type, $key1, $key2, $value, $duration = null) 83 { 84 $expire = is_null($duration) ? null : (time() + $duration); 85 86 $query = "UPDATE data SET updated = :updated, value = :value, expire = :expire WHERE key1 = :key1 AND key2 = :key2 AND type = :type"; 87 $prepared = $this->db->prepare($query); 88 $data = array(':key1' => $key1, ':key2' => $key2, 89 ':type' => $type, ':updated' => time(), 90 ':expire' => $expire, ':value' => serialize($value)); 91 $prepared->execute($data); 92 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 93 return $results; 94 } 95 96 public function get($type = null, $key1 = null, $key2 = null) 97 { 98 $conditions = self::getCondition($type, $key1, $key2); 99 $query = 'SELECT * FROM data WHERE '.$conditions; 100 101 $prepared = $this->db->prepare($query); 102 $prepared->execute(); 103 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 104 if (count($results) !== 1) { 105 return null; 106 } 107 108 $res = $results[0]; 109 $res['value'] = unserialize($res['value']); 110 return $res; 111 } 112 113 /* 114 * Return the value directly (not in a container) 115 */ 116 public function getValue($type = null, $key1 = null, $key2 = null) 117 { 118 $res = $this->get($type, $key1, $key2); 119 if ($res === null) { 120 return null; 121 } 122 return $res['value']; 123 } 124 125 public function exists($type, $key1, $key2) 126 { 127 $query = 'SELECT * FROM data WHERE type = :type AND key1 = :key1 AND key2 = :key2 LIMIT 1'; 128 $prepared = $this->db->prepare($query); 129 $data = array(':type' => $type, ':key1' => $key1, ':key2' => $key2); 130 $prepared->execute($data); 131 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 132 return (count($results) == 1); 133 } 134 135 public function getList($type = null, $key1 = null, $key2 = null) 136 { 137 $conditions = self::getCondition($type, $key1, $key2); 138 $query = 'SELECT * FROM data WHERE '.$conditions; 139 $prepared = $this->db->prepare($query); 140 $prepared->execute(); 141 142 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 143 if (count($results) == 0) { 144 return null; 145 } 146 147 foreach ($results as $key => $value) { 148 $results[$key]['value'] = unserialize($results[$key]['value']); 149 } 150 return $results; 151 } 152 153 public function getKeys($type = null, $key1 = null, $key2 = null, $whichKey = 'type') 154 { 155 if (!in_array($whichKey, array('key1', 'key2', 'type'), true)) { 156 throw new Exception('Invalid key type'); 157 } 158 159 $conditions = self::getCondition($type, $key1, $key2); 160 $query = 'SELECT DISTINCT :whichKey FROM data WHERE '.$conditions; 161 $prepared = $this->db->prepare($query); 162 $data = array('whichKey' => $whichKey); 163 $prepared->execute($data); 164 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 165 166 if (count($results) == 0) { 167 return null; 168 } 169 170 $resarray = array(); 171 foreach ($results as $key => $value) { 172 $resarray[] = $value[$whichKey]; 173 } 174 return $resarray; 175 } 176 177 public function remove($type, $key1, $key2) 178 { 179 $query = 'DELETE FROM data WHERE type = :type AND key1 = :key1 AND key2 = :key2'; 180 $prepared = $this->db->prepare($query); 181 $data = array(':type' => $type, ':key1' => $key1, ':key2' => $key2); 182 $prepared->execute($data); 183 $results = $prepared->fetchAll(PDO::FETCH_ASSOC); 184 return (count($results) == 1); 185 } 186 187 public function removeExpired() 188 { 189 $query = "DELETE FROM data WHERE expire IS NOT NULL AND expire < :expire"; 190 $prepared = $this->db->prepare($query); 191 $data = array(':expire' => time()); 192 $prepared->execute($data); 193 return $prepared->rowCount(); 194 } 195 196 /** 197 * Create a SQL condition statement based on parameters 198 */ 199 private function getCondition($type = null, $key1 = null, $key2 = null) 200 { 201 $conditions = array(); 202 if (!is_null($type)) { 203 $conditions[] = "type = ".$this->db->quote($type); 204 } 205 if (!is_null($key1)) { 206 $conditions[] = "key1 = ".$this->db->quote($key1); 207 } 208 if (!is_null($key2)) { 209 $conditions[] = "key2 = ".$this->db->quote($key2); 210 } 211 212 $conditions[] = "(expire IS NULL OR expire >= ".time().")"; 213 return join(' AND ', $conditions); 214 } 215} 216 217