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