Changeset 8 for trunk/Packages/Common/Database.php
- Timestamp:
- Jun 1, 2023, 12:18:18 AM (18 months ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/Packages/Common/Database.php
r7 r8 2 2 3 3 // Extended database class 4 // Date: 2016-01-11 4 // Date: 2020-11-10 5 6 function microtime_float() 7 { 8 list($usec, $sec) = explode(" ", microtime()); 9 return (float)$usec + (float)$sec; 10 } 5 11 6 12 class DatabaseResult 7 13 { 8 var$PDOStatement;9 var$num_rows = 0;14 public PDOStatement $PDOStatement; 15 public int $num_rows = 0; 10 16 11 17 function fetch_assoc() … … 27 33 class Database 28 34 { 29 var $Prefix; 30 var $Functions; 31 var $Type; 32 var $PDO; 33 var $Error; 34 var $insert_id; 35 var $LastQuery; 36 var $ShowSQLError; 37 var $ShowSQLQuery; 38 var $LogSQLQuery; 39 var $LogFile; 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; 40 47 41 48 function __construct() 42 49 { 43 50 $this->Prefix = ''; 44 $this->Functions = array('NOW( )', 'CURDATE()', 'CURTIME()', 'UUID()');51 $this->Functions = array('NOW(', 'CURDATE(', 'CURTIME(', 'UUID(', 'SHA1('); 45 52 $this->Type = 'mysql'; // mysql, pgsql 46 53 $this->Error = ''; … … 50 57 $this->LogSQLQuery = false; 51 58 $this->LogFile = dirname(__FILE__).'/../../Query.log'; 52 } 53 54 function Connect($Host, $User, $Password, $Database) 59 $this->Database = ''; 60 } 61 62 function Connect(string $Host, string $User, string $Password, string $Database): void 55 63 { 56 64 if ($this->Type == 'mysql') $ConnectionString = 'mysql:host='.$Host.';dbname='.$Database; 57 65 else if ($this->Type == 'pgsql') $ConnectionString = 'pgsql:dbname='.$Database.';host='.$Host; 58 66 else $ConnectionString = ''; 67 $this->Database = $Database; 59 68 try { 60 69 $this->PDO = new PDO($ConnectionString, $User, $Password); 61 62 70 } catch (Exception $E) 63 71 { … … 67 75 } 68 76 69 function Disconnect() 77 function Disconnect(): void 70 78 { 71 79 unset($this->PDO); 72 80 } 73 81 74 function Connected() 82 function Connected(): bool 75 83 { 76 84 return isset($this->PDO); 77 85 } 78 86 79 function select_db( $Database)87 function select_db(string $Database) 80 88 { 81 89 $this->query('USE `'.$Database.'`'); 82 90 } 83 91 84 function query($Query) 92 function query($Query): DatabaseResult 85 93 { 86 94 if (!$this->Connected()) throw new Exception(T('Not connected to database')); 87 if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime ();95 if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime_float(); 88 96 $this->LastQuery = $Query; 89 97 if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) 90 $Duration = ' ; '.round(microtime() - $QueryStartTime, 4). ' s'; 91 if ($this->LogSQLQuery == true) 98 { 99 $Time = round(microtime_float() - $QueryStartTime, 4); 100 $Duration = ' ; '.$Time. ' s'; 101 } 102 if (($this->LogSQLQuery == true) and ($Time != 0)) 92 103 file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND); 93 104 if ($this->ShowSQLQuery == true) … … 95 106 'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n"); 96 107 $Result = new DatabaseResult(); 97 $Result->PDOStatement = $this->PDO->query($Query); 98 if ($Result->PDOStatement) 99 { 100 $Result->num_rows = $Result->PDOStatement->rowCount(); 108 $Statement = $this->PDO->query($Query); 109 if ($Statement) 110 { 111 $Result->PDOStatement = $Statement; 112 $Result->num_rows = $Statement->rowCount(); 101 113 $this->insert_id = $this->PDO->lastInsertId(); 102 114 } else 103 115 { 104 $ this->Error = $this->PDO->errorInfo();105 $this->Error = $ this->Error[2];116 $Error = $this->PDO->errorInfo(); 117 $this->Error = $Error[2]; 106 118 if (($this->Error != '') and ($this->ShowSQLError == true)) 107 119 echo('<div><strong>SQL Error: </strong>'.$this->Error.'<br />'.$Query.'</div>'); … … 111 123 } 112 124 113 function select( $Table, $What = '*', $Condition = 1)125 function select(string $Table, string $What = '*', string $Condition = '1'): DatabaseResult 114 126 { 115 127 return $this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition); 116 128 } 117 129 118 function delete( $Table, $Condition)130 function delete(string $Table, string $Condition): void 119 131 { 120 132 $this->query('DELETE FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition); 121 133 } 122 134 123 function insert($Table, $Data) 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 124 149 { 125 150 $Name = ''; … … 128 153 { 129 154 $Name .= ',`'.$Key.'`'; 130 if (!in_array($Value, $this->Functions)) 155 if (is_null($Value)) $Value = 'NULL'; 156 else if (!$this->IsFunction($Value)) 131 157 { 132 if (is_null($Value)) $Value = 'NULL'; 133 else $Value = $this->PDO->quote($Value); 158 $Value = $this->PDO->quote($Value); 134 159 } 135 160 $Values .= ','.$Value; … … 137 162 $Name = substr($Name, 1); 138 163 $Values = substr($Values, 1); 139 $this->query('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')'); 140 $this->insert_id = $this->PDO->lastInsertId(); 141 } 142 143 function update($Table, $Condition, $Data) 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 144 173 { 145 174 $Values = ''; 146 175 foreach ($Data as $Key => $Value) 147 176 { 148 if (!in_array($Value, $this->Functions)) 177 if (is_null($Value)) $Value = 'NULL'; 178 else if (!$this->IsFunction($Value)) 149 179 { 150 if (is_null($Value)) $Value = 'NULL'; 151 else $Value = $this->PDO->quote($Value); 180 $Value = $this->PDO->quote($Value); 152 181 } 153 182 $Values .= ', `'.$Key.'`='.$Value; 154 183 } 155 184 $Values = substr($Values, 2); 156 $this->query('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');157 } 158 159 function replace( $Table, $Data)185 return 'UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')'; 186 } 187 188 function replace(string $Table, array $Data): void 160 189 { 161 190 $Name = ''; … … 163 192 foreach ($Data as $Key => $Value) 164 193 { 165 if (!in_array($Value, $this->Functions)) 194 if (is_null($Value)) $Value = 'NULL'; 195 else if (!$this->IsFunction($Value)) 166 196 { 167 if (is_null($Value)) $Value = 'NULL'; 168 else $Value = $this->PDO->quote($Value); 197 $Value = $this->PDO->quote($Value); 169 198 } 170 199 $Name .= ',`'.$Key.'`'; … … 173 202 $Name = substr($Name, 1); 174 203 $Values = substr($Values, 1); 175 //echo('REPLACE INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES ('.$Values.')<br />');176 204 $this->query('REPLACE INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')'); 177 //echo($this->error().'<br>'); 178 } 179 180 function charset($Charset) 205 } 206 207 function charset(string $Charset): void 181 208 { 182 209 $this->query('SET NAMES "'.$Charset.'"'); 183 210 } 184 211 185 function real_escape_string( $Text)212 function real_escape_string(string $Text): string 186 213 { 187 214 return addslashes($Text); 188 215 } 189 216 190 function quote( $Text)217 function quote(string $Text): string 191 218 { 192 219 return $this->PDO->quote($Text); 193 220 } 194 221 195 public function __sleep() 222 public function __sleep(): array 196 223 { 197 224 return array('LastQuery'); 198 225 } 199 226 200 public function __wakeup() 201 { 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; 202 247 } 203 248 }
Note:
See TracChangeset
for help on using the changeset viewer.