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