source: trunk/Packages/Common/Database.php

Last change on this file was 8, checked in by chronos, 18 months ago
  • Modified: Updated Common package.
  • Modified: Form types made as separate FormManager package.
  • Fixed: PHP 8.1 support.
File size: 7.4 KB
Line 
1<?php
2
3// Extended database class
4// Date: 2020-11-10
5
6function microtime_float()
7{
8 list($usec, $sec) = explode(" ", microtime());
9 return (float)$usec + (float)$sec;
10}
11
12class 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
33class 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
250function TimeToMysqlDateTime($Time)
251{
252 if ($Time == NULL) return NULL;
253 else return date('Y-m-d H:i:s', $Time);
254}
255
256function TimeToMysqlDate($Time)
257{
258 if ($Time == NULL) return NULL;
259 else return date('Y-m-d', $Time);
260}
261
262function TimeToMysqlTime($Time)
263{
264 if ($Time == NULL) return NULL;
265 else return date('H:i:s', $Time);
266}
267
268function 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
278function MysqlDateToTime($Date)
279{
280 if ($Date == '') return NULL;
281 return MysqlDateTimeToTime($Date.' 0:0:0');
282}
283
284function MysqlTimeToTime($Time)
285{
286 if ($Time == '') return NULL;
287 return MysqlDateTimeToTime('0000-00-00 '.$Time);
288}
Note: See TracBrowser for help on using the repository browser.