uT9RzeXK

1<?php
2
3namespace Pafnooty\Storage;
4
5
6class SQLStorage implements \ArrayAccess, \Countable, \IteratorAggregate
7{
8    private $pdo;
9    private $property;
10
11    public function __construct($path, $property = '')
12    {
13        if (is_string($path)) {
14            $this->pdo = new \PDO("sqlite:$path.db");
15        } elseif ($path instanceof \PDO) {
16            $this->pdo = $path;
17        } 
18        if (preg_match('/^[^a-z]/', $property)) {
19            $property = '_$' . $property;
20        }
21        $this->property = $property;
22        
23        $sql = <<<SQL
24        CREATE TABLE IF NOT EXISTS {$this->property}array 
25        (offset TEXT, value TEXT)
26SQL;
27        $this->pdo->exec($sql);
28    }
29    
30    public function __get($property) 
31    {
32        return $this[$property];
33    }
34    
35    public function __set($property, $value)
36    {
37        $this[$property] = $value;
38    }
39    
40    
41    public function __unset($property)
42    {
43        unset($this[$property]);
44    }
45    
46    public function __isset($property) 
47    {
48        return isset($this[$property]);
49    }
50    
51    public function __debugInfo()
52    {
53        return $this->toArray();
54    }
55    
56    public function offsetSet($offset, $value) 
57    {
58        if (is_null($offset)) {
59            $offset = $this->findMaxOffset() + 1;
60        }
61        
62        unset($this[$offset]); // clean old values
63            
64        if (is_array($value) || $value instanceof \Traversable) {
65            foreach ($value as $k => $v) {
66                $this[$offset][$k] = $v;
67            }
68            return;
69        }
70        
71        $sql = "UPDATE {$this->property}array SET value=:value WHERE offset=:offset";
72        $params = ['value' => serialize($value), 'offset' => $offset];
73        
74        $stmt = $this->pdo->prepare($sql);
75        if ($stmt->execute($params) && $stmt->rowCount() > 0) {
76            return;
77        }
78        $sql = "INSERT INTO {$this->property}array (offset, value) VALUES (:offset, :value)";
79        
80        $stmt = $this->pdo->prepare($sql);
81        $stmt->execute($params);
82    }
83
84    public function contains($value) 
85    {
86        $sql = "SELECT COUNT(offset) FROM {$this->property}array WHERE value=:value";
87        $stmt = $this->pdo->prepare($sql);
88        $stmt->execute(['value' => serialize($value)]);
89        return $stmt->fetchColumn(0) > 0;
90    }
91
92    public function offsetExists($offset) 
93    {
94        $sql = "SELECT COUNT(offset) FROM {$this->property}array WHERE offset=:offset";
95        $stmt = $this->pdo->prepare($sql);
96        $stmt->execute(['offset' => $offset]);
97        if ($stmt->fetchColumn(0) > 0) {
98            return true;
99        }
100        $sql = "SELECT COUNT(name) FROM sqlite_master WHERE type='table' AND name=:name";
101        $stmt = $this->pdo->prepare($sql);
102        
103        $stmt->execute(['name' => "{$this->property}{$offset}_array"]);
104        return $stmt->fetchColumn(0) > 0;
105    }
106
107    public function offsetUnset($offset) 
108    {
109        $sql = "DELETE FROM {$this->property}array WHERE offset=:offset";
110        $params = ['offset' => $offset];
111        
112        $stmt = $this->pdo->prepare($sql);
113        $stmt->execute($params);
114        
115        if ($this[$offset] instanceof SQLStorage) {
116            foreach ($this[$offset] as $key => $value) {
117                unset($this[$offset][$key]);
118            }
119        }
120        
121        $sql = "DROP TABLE IF EXISTS {$this->property}{$offset}_array";
122        $this->pdo->exec($sql);
123    }
124
125    public function offsetGet($offset)
126    {
127        $sql = "SELECT value FROM {$this->property}array WHERE offset=:offset";
128        $params = ['offset' => $offset];
129        
130        $stmt = $this->pdo->prepare($sql);
131        if ($stmt->execute($params)) {
132            $result = $stmt->fetch();
133            if ($result) {
134                return unserialize($result['value']);
135            }
136        }
137        return new SQLStorage($this->pdo, $this->property . str_replace('_', '$', $offset) . '_');
138    }
139    
140    private function findMaxOffset() 
141    {
142        $sql = "SELECT offset FROM {$this->property}array";
143        $offsets = [];
144        $stmt = $this->pdo->query($sql);
145        if ($stmt) {
146            foreach ($stmt as $row) {
147                if (is_numeric($row['offset'])) {
148                    $offsets[] = (int)$row['offset'];
149                }
150            }
151        }
152        if (empty($offsets)) {
153            return -1;
154        }
155        return max($offsets);
156    }
157    
158    public function count() 
159    {
160        $sql = "SELECT COUNT(offset) FROM {$this->property}array";
161        $stmt = $this->pdo->query($sql);
162        return $stmt->fetchColumn(0);
163    }
164    
165    public function toArray()
166    {
167        $array = [];
168        
169        $sql = "SELECT * FROM {$this->property}array";
170        $result = $this->pdo->query($sql);
171        if ($result) {
172            foreach ($result as $row) {
173                $array[$row['offset']] = unserialize($row['value']);
174            }
175        }
176        
177        $sql = "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '{$this->property}%_array'";
178        $result = $this->pdo->query($sql);
179        if ($result) {
180            foreach ($this->pdo->query($sql) as $row) {
181                $tableName = $row['name'];
182                preg_match("/^{$this->property}([^_]+)_array$/i", $tableName, $matches);
183                if (!empty($matches[1]) && !isset($array[$matches[1]])) {
184                    $name = $matches[1];
185                    $array[$name] = $this->$name->toArray();
186                }
187            }
188        }
189        return $array;
190    }
191    
192    public function getIterator() 
193    {
194        return new \ArrayIterator($this->toArray());
195    }
196}