1 | <?php
|
---|
2 |
|
---|
3 | // Extended database class
|
---|
4 | // Date: 2020-11-10
|
---|
5 |
|
---|
6 | function microtime_float()
|
---|
7 | {
|
---|
8 | list($usec, $sec) = explode(" ", microtime());
|
---|
9 | return (float)$usec + (float)$sec;
|
---|
10 | }
|
---|
11 |
|
---|
12 | class DatabaseResult
|
---|
13 | {
|
---|
14 | public PDOStatement $PDOStatement;
|
---|
15 | public int $num_rows = 0;
|
---|
16 |
|
---|
17 | function fetch_assoc()
|
---|
18 | {
|
---|
19 | return $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
|
---|
20 | }
|
---|
21 |
|
---|
22 | function fetch_array()
|
---|
23 | {
|
---|
24 | return $this->PDOStatement->fetch(PDO::FETCH_BOTH);
|
---|
25 | }
|
---|
26 |
|
---|
27 | function fetch_row()
|
---|
28 | {
|
---|
29 | return $this->PDOStatement->fetch(PDO::FETCH_NUM);
|
---|
30 | }
|
---|
31 | }
|
---|
32 |
|
---|
33 | class Database
|
---|
34 | {
|
---|
35 | public string $Prefix;
|
---|
36 | public array $Functions;
|
---|
37 | public string $Type;
|
---|
38 | public PDO $PDO;
|
---|
39 | public string $Error;
|
---|
40 | public string $insert_id;
|
---|
41 | public string $LastQuery;
|
---|
42 | public bool $ShowSQLError;
|
---|
43 | public bool $ShowSQLQuery;
|
---|
44 | public bool $LogSQLQuery;
|
---|
45 | public string $LogFile;
|
---|
46 | public string $Database;
|
---|
47 |
|
---|
48 | function __construct()
|
---|
49 | {
|
---|
50 | $this->Prefix = '';
|
---|
51 | $this->Functions = array('NOW(', 'CURDATE(', 'CURTIME(', 'UUID(', 'SHA1(');
|
---|
52 | $this->Type = 'mysql'; // mysql, pgsql
|
---|
53 | $this->Error = '';
|
---|
54 | $this->LastQuery = '';
|
---|
55 | $this->ShowSQLError = false;
|
---|
56 | $this->ShowSQLQuery = false;
|
---|
57 | $this->LogSQLQuery = false;
|
---|
58 | $this->LogFile = dirname(__FILE__).'/../../Query.log';
|
---|
59 | $this->Database = '';
|
---|
60 | }
|
---|
61 |
|
---|
62 | function Connect(string $Host, string $User, string $Password, string $Database): void
|
---|
63 | {
|
---|
64 | if ($this->Type == 'mysql') $ConnectionString = 'mysql:host='.$Host.';dbname='.$Database;
|
---|
65 | else if ($this->Type == 'pgsql') $ConnectionString = 'pgsql:dbname='.$Database.';host='.$Host;
|
---|
66 | else $ConnectionString = '';
|
---|
67 | $this->Database = $Database;
|
---|
68 | try {
|
---|
69 | $this->PDO = new PDO($ConnectionString, $User, $Password);
|
---|
70 | } catch (Exception $E)
|
---|
71 | {
|
---|
72 | unset($this->PDO);
|
---|
73 | throw new Exception($E->getMessage());
|
---|
74 | }
|
---|
75 | }
|
---|
76 |
|
---|
77 | function Disconnect(): void
|
---|
78 | {
|
---|
79 | unset($this->PDO);
|
---|
80 | }
|
---|
81 |
|
---|
82 | function Connected(): bool
|
---|
83 | {
|
---|
84 | return isset($this->PDO);
|
---|
85 | }
|
---|
86 |
|
---|
87 | function select_db(string $Database)
|
---|
88 | {
|
---|
89 | $this->query('USE `'.$Database.'`');
|
---|
90 | }
|
---|
91 |
|
---|
92 | function query($Query): DatabaseResult
|
---|
93 | {
|
---|
94 | if (!$this->Connected()) throw new Exception(T('Not connected to database'));
|
---|
95 | if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime_float();
|
---|
96 | $this->LastQuery = $Query;
|
---|
97 | if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true))
|
---|
98 | {
|
---|
99 | $Time = round(microtime_float() - $QueryStartTime, 4);
|
---|
100 | $Duration = ' ; '.$Time. ' s';
|
---|
101 | }
|
---|
102 | if (($this->LogSQLQuery == true) and ($Time != 0))
|
---|
103 | file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND);
|
---|
104 | if ($this->ShowSQLQuery == true)
|
---|
105 | echo('<div style="border-bottom-width: 1px; border-bottom-style: solid; '.
|
---|
106 | 'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n");
|
---|
107 | $Result = new DatabaseResult();
|
---|
108 | $Statement = $this->PDO->query($Query);
|
---|
109 | if ($Statement)
|
---|
110 | {
|
---|
111 | $Result->PDOStatement = $Statement;
|
---|
112 | $Result->num_rows = $Statement->rowCount();
|
---|
113 | $this->insert_id = $this->PDO->lastInsertId();
|
---|
114 | } else
|
---|
115 | {
|
---|
116 | $Error = $this->PDO->errorInfo();
|
---|
117 | $this->Error = $Error[2];
|
---|
118 | if (($this->Error != '') and ($this->ShowSQLError == true))
|
---|
119 | echo('<div><strong>SQL Error: </strong>'.$this->Error.'<br />'.$Query.'</div>');
|
---|
120 | throw new Exception('SQL Error: '.$this->Error.', Query: '.$Query);
|
---|
121 | }
|
---|
122 | return $Result;
|
---|
123 | }
|
---|
124 |
|
---|
125 | function select(string $Table, string $What = '*', string $Condition = '1'): DatabaseResult
|
---|
126 | {
|
---|
127 | return $this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
|
---|
128 | }
|
---|
129 |
|
---|
130 | function delete(string $Table, string $Condition): void
|
---|
131 | {
|
---|
132 | $this->query('DELETE FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
|
---|
133 | }
|
---|
134 |
|
---|
135 | function insert(string $Table, array $Data): int
|
---|
136 | {
|
---|
137 | $this->query($this->GetInsert($Table, $Data));
|
---|
138 | $this->insert_id = $this->PDO->lastInsertId();
|
---|
139 | return $this->insert_id;
|
---|
140 | }
|
---|
141 |
|
---|
142 | function IsFunction(string $Text): bool
|
---|
143 | {
|
---|
144 | $Pos = strpos($Text, '(');
|
---|
145 | return ($Pos !== false) && in_array(substr($Text, 0, $Pos + 1), $this->Functions);
|
---|
146 | }
|
---|
147 |
|
---|
148 | function GetInsert(string $Table, array $Data): string
|
---|
149 | {
|
---|
150 | $Name = '';
|
---|
151 | $Values = '';
|
---|
152 | foreach ($Data as $Key => $Value)
|
---|
153 | {
|
---|
154 | $Name .= ',`'.$Key.'`';
|
---|
155 | if (is_null($Value)) $Value = 'NULL';
|
---|
156 | else if (!$this->IsFunction($Value))
|
---|
157 | {
|
---|
158 | $Value = $this->PDO->quote($Value);
|
---|
159 | }
|
---|
160 | $Values .= ','.$Value;
|
---|
161 | }
|
---|
162 | $Name = substr($Name, 1);
|
---|
163 | $Values = substr($Values, 1);
|
---|
164 | return 'INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')';
|
---|
165 | }
|
---|
166 |
|
---|
167 | function update(string $Table, string $Condition, array $Data): void
|
---|
168 | {
|
---|
169 | $this->query($this->GetUpdate($Table, $Condition, $Data));
|
---|
170 | }
|
---|
171 |
|
---|
172 | function GetUpdate(string $Table, string $Condition, array $Data): string
|
---|
173 | {
|
---|
174 | $Values = '';
|
---|
175 | foreach ($Data as $Key => $Value)
|
---|
176 | {
|
---|
177 | if (is_null($Value)) $Value = 'NULL';
|
---|
178 | else if (!$this->IsFunction($Value))
|
---|
179 | {
|
---|
180 | $Value = $this->PDO->quote($Value);
|
---|
181 | }
|
---|
182 | $Values .= ', `'.$Key.'`='.$Value;
|
---|
183 | }
|
---|
184 | $Values = substr($Values, 2);
|
---|
185 | return 'UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')';
|
---|
186 | }
|
---|
187 |
|
---|
188 | function replace(string $Table, array $Data): void
|
---|
189 | {
|
---|
190 | $Name = '';
|
---|
191 | $Values = '';
|
---|
192 | foreach ($Data as $Key => $Value)
|
---|
193 | {
|
---|
194 | if (is_null($Value)) $Value = 'NULL';
|
---|
195 | else if (!$this->IsFunction($Value))
|
---|
196 | {
|
---|
197 | $Value = $this->PDO->quote($Value);
|
---|
198 | }
|
---|
199 | $Name .= ',`'.$Key.'`';
|
---|
200 | $Values .= ','.$Value;
|
---|
201 | }
|
---|
202 | $Name = substr($Name, 1);
|
---|
203 | $Values = substr($Values, 1);
|
---|
204 | $this->query('REPLACE INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
|
---|
205 | }
|
---|
206 |
|
---|
207 | function charset(string $Charset): void
|
---|
208 | {
|
---|
209 | $this->query('SET NAMES "'.$Charset.'"');
|
---|
210 | }
|
---|
211 |
|
---|
212 | function real_escape_string(string $Text): string
|
---|
213 | {
|
---|
214 | return addslashes($Text);
|
---|
215 | }
|
---|
216 |
|
---|
217 | function quote(string $Text): string
|
---|
218 | {
|
---|
219 | return $this->PDO->quote($Text);
|
---|
220 | }
|
---|
221 |
|
---|
222 | public function __sleep(): array
|
---|
223 | {
|
---|
224 | return array('LastQuery');
|
---|
225 | }
|
---|
226 |
|
---|
227 | public function __wakeup(): void
|
---|
228 | {
|
---|
229 | }
|
---|
230 |
|
---|
231 | public function Transaction(array $Queries): void
|
---|
232 | {
|
---|
233 | $this->PDO->beginTransaction();
|
---|
234 | foreach ($Queries as $Query)
|
---|
235 | {
|
---|
236 | $Statement = $this->PDO->prepare($Query);
|
---|
237 | $Statement->execute();
|
---|
238 | }
|
---|
239 | $this->PDO->commit();
|
---|
240 | }
|
---|
241 |
|
---|
242 | public function TableExists(string $Name): bool
|
---|
243 | {
|
---|
244 | $DbResult = $this->query('SELECT * FROM information_schema.tables WHERE table_schema = "'.$this->Database.
|
---|
245 | '" AND table_name = "'.$Name.'" LIMIT 1');
|
---|
246 | return $DbResult->num_rows != 0;
|
---|
247 | }
|
---|
248 | }
|
---|
249 |
|
---|
250 | function TimeToMysqlDateTime($Time)
|
---|
251 | {
|
---|
252 | if ($Time == NULL) return NULL;
|
---|
253 | else return date('Y-m-d H:i:s', $Time);
|
---|
254 | }
|
---|
255 |
|
---|
256 | function TimeToMysqlDate($Time)
|
---|
257 | {
|
---|
258 | if ($Time == NULL) return NULL;
|
---|
259 | else return date('Y-m-d', $Time);
|
---|
260 | }
|
---|
261 |
|
---|
262 | function TimeToMysqlTime($Time)
|
---|
263 | {
|
---|
264 | if ($Time == NULL) return NULL;
|
---|
265 | else return date('H:i:s', $Time);
|
---|
266 | }
|
---|
267 |
|
---|
268 | function MysqlDateTimeToTime($DateTime)
|
---|
269 | {
|
---|
270 | if ($DateTime == '') return NULL;
|
---|
271 | $Parts = explode(' ', $DateTime);
|
---|
272 | $DateParts = explode('-', $Parts[0]);
|
---|
273 | $TimeParts = explode(':', $Parts[1]);
|
---|
274 | $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]);
|
---|
275 | return $Result;
|
---|
276 | }
|
---|
277 |
|
---|
278 | function MysqlDateToTime($Date)
|
---|
279 | {
|
---|
280 | if ($Date == '') return NULL;
|
---|
281 | return MysqlDateTimeToTime($Date.' 0:0:0');
|
---|
282 | }
|
---|
283 |
|
---|
284 | function MysqlTimeToTime($Time)
|
---|
285 | {
|
---|
286 | if ($Time == '') return NULL;
|
---|
287 | return MysqlDateTimeToTime('0000-00-00 '.$Time);
|
---|
288 | }
|
---|