Ignore:
Timestamp:
Dec 6, 2021, 11:33:48 AM (3 years ago)
Author:
chronos
Message:
  • Modified: Updated Common package.
  • Added: Explicit types for better type checking.
  • Fixed: Support for php 8.0.
File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/Packages/Common/Database.php

    r92 r95  
    22
    33// Extended database class
    4 // Date: 2016-01-11
     4// Date: 2020-11-10
    55
    66function microtime_float()
     
    1212class DatabaseResult
    1313{
    14   var $PDOStatement;
    15   var $num_rows = 0;
     14  public PDOStatement $PDOStatement;
     15  public int $num_rows = 0;
    1616
    1717  function fetch_assoc()
     
    3333class Database
    3434{
    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;
    4647
    4748  function __construct()
    4849  {
    4950    $this->Prefix = '';
    50     $this->Functions = array('NOW()', 'CURDATE()', 'CURTIME()', 'UUID()');
     51    $this->Functions = array('NOW(', 'CURDATE(', 'CURTIME(', 'UUID(', 'SHA1(');
    5152    $this->Type = 'mysql';  // mysql, pgsql
    5253    $this->Error = '';
     
    5657    $this->LogSQLQuery = false;
    5758    $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
    6263  {
    6364    if ($this->Type == 'mysql') $ConnectionString = 'mysql:host='.$Host.';dbname='.$Database;
    6465      else if ($this->Type == 'pgsql') $ConnectionString = 'pgsql:dbname='.$Database.';host='.$Host;
    6566      else $ConnectionString = '';
     67    $this->Database = $Database;
    6668    try {
    6769      $this->PDO = new PDO($ConnectionString, $User, $Password);
    68 
    6970    } catch (Exception $E)
    7071    {
     
    7475  }
    7576
    76   function Disconnect()
     77  function Disconnect(): void
    7778  {
    7879    unset($this->PDO);
    7980  }
    8081
    81   function Connected()
     82  function Connected(): bool
    8283  {
    8384    return isset($this->PDO);
    8485  }
    8586
    86   function select_db($Database)
     87  function select_db(string $Database)
    8788  {
    8889    $this->query('USE `'.$Database.'`');
    8990  }
    9091
    91   function query($Query)
     92  function query($Query): DatabaseResult
    9293  {
    9394    if (!$this->Connected()) throw new Exception(T('Not connected to database'));
     
    9697    //echo('a'.$this->ShowSQLQuery.'<'.$QueryStartTime.', '.microtime_float());
    9798    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))
    100104      file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND);
    101105    if ($this->ShowSQLQuery == true)
     
    103107      'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n");
    104108    $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();
    109114      $this->insert_id = $this->PDO->lastInsertId();
    110115    } 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];
    114119      if (($this->Error != '') and ($this->ShowSQLError == true))
    115120        echo('<div><strong>SQL Error: </strong>'.$this->Error.'<br />'.$Query.'</div>');
     
    119124  }
    120125
    121   function select($Table, $What = '*', $Condition = 1)
     126  function select(string $Table, string $What = '*', string $Condition = '1'): DatabaseResult
    122127  {
    123128    return $this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
    124129  }
    125130
    126   function delete($Table, $Condition)
     131  function delete(string $Table, string $Condition): void
    127132  {
    128133    $this->query('DELETE FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
    129134  }
    130135
    131   function insert($Table, $Data)
     136  function insert(string $Table, array $Data): int
    132137  {
    133138    $this->query($this->GetInsert($Table, $Data));
    134139    $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
    138150  {
    139151    $Name = '';
     
    142154    {
    143155      $Name .= ',`'.$Key.'`';
    144       if (!in_array($Value, $this->Functions))
     156      if (is_null($Value)) $Value = 'NULL';
     157      else if (!$this->IsFunction($Value))
    145158      {
    146         if (is_null($Value)) $Value = 'NULL';
    147         else $Value = $this->PDO->quote($Value);
     159        $Value = $this->PDO->quote($Value);
    148160      }
    149161      $Values .= ','.$Value;
     
    154166  }
    155167
    156   function update($Table, $Condition, $Data)
     168  function update(string $Table, string $Condition, array $Data): void
    157169  {
    158170    $this->query($this->GetUpdate($Table, $Condition, $Data));
    159171  }
    160  
    161   function GetUpdate($Table, $Condition, $Data)
     172
     173  function GetUpdate(string $Table, string $Condition, array $Data): string
    162174  {
    163175    $Values = '';
    164176    foreach ($Data as $Key => $Value)
    165177    {
    166       if (!in_array($Value, $this->Functions))
     178      if (is_null($Value)) $Value = 'NULL';
     179      else if (!$this->IsFunction($Value))
    167180      {
    168         if (is_null($Value)) $Value = 'NULL';
    169         else $Value = $this->PDO->quote($Value);
     181        $Value = $this->PDO->quote($Value);
    170182      }
    171183      $Values .= ', `'.$Key.'`='.$Value;
     
    175187  }
    176188
    177   function replace($Table, $Data)
     189  function replace(string $Table, array $Data): void
    178190  {
    179191    $Name = '';
     
    181193    foreach ($Data as $Key => $Value)
    182194    {
    183       if (!in_array($Value, $this->Functions))
     195      if (is_null($Value)) $Value = 'NULL';
     196      else if (!$this->IsFunction($Value))
    184197      {
    185         if (is_null($Value)) $Value = 'NULL';
    186         else $Value = $this->PDO->quote($Value);
     198        $Value = $this->PDO->quote($Value);
    187199      }
    188200      $Name .= ',`'.$Key.'`';
     
    196208  }
    197209
    198   function charset($Charset)
     210  function charset(string $Charset): void
    199211  {
    200212    $this->query('SET NAMES "'.$Charset.'"');
    201213  }
    202214
    203   function real_escape_string($Text)
     215  function real_escape_string(string $Text): string
    204216  {
    205217    return addslashes($Text);
    206218  }
    207219
    208   function quote($Text)
     220  function quote(string $Text): string
    209221  {
    210222    return $this->PDO->quote($Text);
    211223  }
    212224
    213   public function __sleep()
     225  public function __sleep(): array
    214226  {
    215227    return array('LastQuery');
    216228  }
    217229
    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;
    231252  }
    232253}
Note: See TracChangeset for help on using the changeset viewer.