Changeset 8 for trunk/Packages/Common/Database.php

Timestamp:

Jun 1, 2023, 12:18:18 AM (12 months ago)

Author:

chronos

Message:

• Modified: Updated Common package.
• Modified: Form types made as separate FormManager package.
• Fixed: PHP 8.1 support.

File:

• trunk/Packages/Common/Database.php (modified) (10 diffs)

trunk/Packages/Common/Database.php

@@ -2 +2 @@
 
 // Extended database class
-// Date: 2016-01-11
+// Date: 2020-11-10
+
+function microtime_float()
+{
+  list($usec, $sec) = explode(" ", microtime());
+  return (float)$usec + (float)$sec;
+}
 
 class DatabaseResult
 {
-  var $PDOStatement;
-  var $num_rows = 0;
+  public PDOStatement $PDOStatement;
+  public int $num_rows = 0;
 
   function fetch_assoc()
@@ -27 +33 @@
 class Database
 {
-  var $Prefix;
-  var $Functions;
-  var $Type;
-  var $PDO;
-  var $Error;
-  var $insert_id;
-  var $LastQuery;
-  var $ShowSQLError;
-  var $ShowSQLQuery;
-  var $LogSQLQuery;
-  var $LogFile;
+  public string $Prefix;
+  public array $Functions;
+  public string $Type;
+  public PDO $PDO;
+  public string $Error;
+  public string $insert_id;
+  public string $LastQuery;
+  public bool $ShowSQLError;
+  public bool $ShowSQLQuery;
+  public bool $LogSQLQuery;
+  public string $LogFile;
+  public string $Database;
 
   function __construct()
   {
     $this->Prefix = '';
-    $this->Functions = array('NOW()', 'CURDATE()', 'CURTIME()', 'UUID()');
+    $this->Functions = array('NOW(', 'CURDATE(', 'CURTIME(', 'UUID(', 'SHA1(');
     $this->Type = 'mysql';  // mysql, pgsql
     $this->Error = '';
@@ -50 +57 @@
     $this->LogSQLQuery = false;
     $this->LogFile = dirname(__FILE__).'/../../Query.log';
-  }
-
-  function Connect($Host, $User, $Password, $Database)
+    $this->Database = '';
+  }
+
+  function Connect(string $Host, string $User, string $Password, string $Database): void
   {
     if ($this->Type == 'mysql') $ConnectionString = 'mysql:host='.$Host.';dbname='.$Database;
       else if ($this->Type == 'pgsql') $ConnectionString = 'pgsql:dbname='.$Database.';host='.$Host;
       else $ConnectionString = '';
+    $this->Database = $Database;
     try {
       $this->PDO = new PDO($ConnectionString, $User, $Password);
-
     } catch (Exception $E)
     {
@@ -67 +75 @@
   }
 
-  function Disconnect()
+  function Disconnect(): void
   {
     unset($this->PDO);
   }
 
-  function Connected()
+  function Connected(): bool
   {
     return isset($this->PDO);
   }
 
-  function select_db($Database)
+  function select_db(string $Database)
   {
     $this->query('USE `'.$Database.'`');
   }
 
-  function query($Query)
+  function query($Query): DatabaseResult
   {
     if (!$this->Connected()) throw new Exception(T('Not connected to database'));
-    if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime();
+    if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime_float();
     $this->LastQuery = $Query;
     if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true))
-      $Duration = ' ; '.round(microtime() - $QueryStartTime, 4). ' s';
-    if ($this->LogSQLQuery == true)
+    {
+      $Time = round(microtime_float() - $QueryStartTime, 4);
+      $Duration = ' ; '.$Time. ' s';
+    }
+    if (($this->LogSQLQuery == true) and ($Time != 0))
       file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND);
     if ($this->ShowSQLQuery == true)
@@ -95 +106 @@
       'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n");
     $Result = new DatabaseResult();
-    $Result->PDOStatement = $this->PDO->query($Query);
-    if ($Result->PDOStatement)
-    {
-      $Result->num_rows = $Result->PDOStatement->rowCount();
+    $Statement = $this->PDO->query($Query);
+    if ($Statement)
+    {
+      $Result->PDOStatement = $Statement;
+      $Result->num_rows = $Statement->rowCount();
       $this->insert_id = $this->PDO->lastInsertId();
     } else
     {
-      $this->Error = $this->PDO->errorInfo();
-      $this->Error = $this->Error[2];
+      $Error = $this->PDO->errorInfo();
+      $this->Error = $Error[2];
       if (($this->Error != '') and ($this->ShowSQLError == true))
         echo('<div><strong>SQL Error: </strong>'.$this->Error.'<br />'.$Query.'</div>');
@@ -111 +123 @@
   }
 
-  function select($Table, $What = '*', $Condition = 1)
+  function select(string $Table, string $What = '*', string $Condition = '1'): DatabaseResult
   {
     return $this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
   }
 
-  function delete($Table, $Condition)
+  function delete(string $Table, string $Condition): void
   {
     $this->query('DELETE FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
   }
 
-  function insert($Table, $Data)
+  function insert(string $Table, array $Data): int
+  {
+    $this->query($this->GetInsert($Table, $Data));
+    $this->insert_id = $this->PDO->lastInsertId();
+    return $this->insert_id;
+  }
+
+  function IsFunction(string $Text): bool
+  {
+    $Pos = strpos($Text, '(');
+    return ($Pos !== false) && in_array(substr($Text, 0, $Pos + 1), $this->Functions);
+  }
+
+  function GetInsert(string $Table, array $Data): string
   {
     $Name = '';
@@ -128 +153 @@
     {
       $Name .= ',`'.$Key.'`';
-      if (!in_array($Value, $this->Functions))
+      if (is_null($Value)) $Value = 'NULL';
+      else if (!$this->IsFunction($Value))
       {
-        if (is_null($Value)) $Value = 'NULL';
-        else $Value = $this->PDO->quote($Value);
+        $Value = $this->PDO->quote($Value);
       }
       $Values .= ','.$Value;
@@ -137 +162 @@
     $Name = substr($Name, 1);
     $Values = substr($Values, 1);
-    $this->query('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
-    $this->insert_id = $this->PDO->lastInsertId();
-  }
-
-  function update($Table, $Condition, $Data)
+    return 'INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')';
+  }
+
+  function update(string $Table, string $Condition, array $Data): void
+  {
+    $this->query($this->GetUpdate($Table, $Condition, $Data));
+  }
+
+  function GetUpdate(string $Table, string $Condition, array $Data): string
   {
     $Values = '';
     foreach ($Data as $Key => $Value)
     {
-      if (!in_array($Value, $this->Functions))
+      if (is_null($Value)) $Value = 'NULL';
+      else if (!$this->IsFunction($Value))
       {
-        if (is_null($Value)) $Value = 'NULL';
-        else $Value = $this->PDO->quote($Value);
+        $Value = $this->PDO->quote($Value);
       }
       $Values .= ', `'.$Key.'`='.$Value;
     }
     $Values = substr($Values, 2);
-    $this->query('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');
-  }
-
-  function replace($Table, $Data)
+    return 'UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')';
+  }
+
+  function replace(string $Table, array $Data): void
   {
     $Name = '';
@@ -163 +192 @@
     foreach ($Data as $Key => $Value)
     {
-      if (!in_array($Value, $this->Functions))
+      if (is_null($Value)) $Value = 'NULL';
+      else if (!$this->IsFunction($Value))
       {
-        if (is_null($Value)) $Value = 'NULL';
-        else $Value = $this->PDO->quote($Value);
+        $Value = $this->PDO->quote($Value);
       }
       $Name .= ',`'.$Key.'`';
@@ -173 +202 @@
     $Name = substr($Name, 1);
     $Values = substr($Values, 1);
-    //echo('REPLACE INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES ('.$Values.')<br />');
     $this->query('REPLACE INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
-    //echo($this->error().'<br>');
-  }
-
-  function charset($Charset)
+  }
+
+  function charset(string $Charset): void
   {
     $this->query('SET NAMES "'.$Charset.'"');
   }
 
-  function real_escape_string($Text)
+  function real_escape_string(string $Text): string
   {
     return addslashes($Text);
   }
 
-  function quote($Text)
+  function quote(string $Text): string
   {
     return $this->PDO->quote($Text);
   }
 
-  public function __sleep()
+  public function __sleep(): array
   {
     return array('LastQuery');
   }
 
-  public function __wakeup()
-  {
+  public function __wakeup(): void
+  {
+  }
+
+  public function Transaction(array $Queries): void
+  {
+    $this->PDO->beginTransaction();
+    foreach ($Queries as $Query)
+    {
+      $Statement = $this->PDO->prepare($Query);
+      $Statement->execute();
+    }
+    $this->PDO->commit();
+  }
+
+  public function TableExists(string $Name): bool
+  {
+    $DbResult = $this->query('SELECT * FROM information_schema.tables  WHERE table_schema = "'.$this->Database.
+    '" AND table_name = "'.$Name.'" LIMIT 1');
+    return $DbResult->num_rows != 0;
   }
 }