Ignore:
Timestamp:
Aug 3, 2021, 11:20:41 AM (3 years ago)
Author:
chronos
Message:
  • Modified: Used explicit types where possible for better error reporting.
  • Modified: Updated Common packaged to newer version.
  • Modified: Simplified pages title.
  • Added: Simple keyword based spam filter for meet items.
Location:
trunk
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk

    • Property svn:ignore

      •  

        old new  
        1 nbproject
        2 Config.php
        31.settings
        42.project
        53.buildpath
         4.htaccess

  • trunk/Packages/Common/Database.php

    r56 r63  
    22
    33// Extended database class
    4 // Date: 2020-04-07
     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'));
    9495    if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime_float();
    95     $this->LastQuery = $Query;   
     96    $this->LastQuery = $Query;
     97    //echo('a'.$this->ShowSQLQuery.'<'.$QueryStartTime.', '.microtime_float());
    9698    if (($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true))
    97       $Duration = ' ; '.round(microtime_float() - $QueryStartTime, 4). ' s';
    98     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))
    99104      file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND);
    100105    if ($this->ShowSQLQuery == true)
    … …  
    102107      'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n");
    103108    $Result = new DatabaseResult();
    104     $Result->PDOStatement = $this->PDO->query($Query);
    105     if ($Result->PDOStatement)
    106     {
    107       $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();
    108114      $this->insert_id = $this->PDO->lastInsertId();
    109115    } else
    110     {     
    111       $this->Error = $this->PDO->errorInfo();
    112       $this->Error = $this->Error[2];
     116    {
     117      $Error = $this->PDO->errorInfo();
     118      $this->Error = $Error[2];
    113119      if (($this->Error != '') and ($this->ShowSQLError == true))
    114120        echo('<div><strong>SQL Error: </strong>'.$this->Error.'<br />'.$Query.'</div>');
    … …  
    118124  }
    119125
    120   function select($Table, $What = '*', $Condition = 1)
     126  function select(string $Table, string $What = '*', string $Condition = '1'): DatabaseResult
    121127  {
    122128    return $this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
    123129  }
    124130
    125   function delete($Table, $Condition)
     131  function delete(string $Table, string $Condition): void
    126132  {
    127133    $this->query('DELETE FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
    128134  }
    129135
    130   function insert($Table, $Data)
     136  function insert(string $Table, array $Data): int
    131137  {
    132138    $this->query($this->GetInsert($Table, $Data));
    133139    $this->insert_id = $this->PDO->lastInsertId();
    134   }
    135  
    136   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
    137150  {
    138151    $Name = '';
    … …  
    141154    {
    142155      $Name .= ',`'.$Key.'`';
    143       if (!in_array($Value, $this->Functions))
     156      if (is_null($Value)) $Value = 'NULL';
     157      else if (!$this->IsFunction($Value))
    144158      {
    145         if (is_null($Value)) $Value = 'NULL';
    146         else $Value = $this->PDO->quote($Value);
     159        $Value = $this->PDO->quote($Value);
    147160      }
    148161      $Values .= ','.$Value;
    … …  
    153166  }
    154167
    155   function update($Table, $Condition, $Data)
     168  function update(string $Table, string $Condition, array $Data): void
    156169  {
    157170    $this->query($this->GetUpdate($Table, $Condition, $Data));
    158171  }
    159  
    160   function GetUpdate($Table, $Condition, $Data)
     172
     173  function GetUpdate(string $Table, string $Condition, array $Data): string
    161174  {
    162175    $Values = '';
    163176    foreach ($Data as $Key => $Value)
    164177    {
    165       if (!in_array($Value, $this->Functions))
     178      if (is_null($Value)) $Value = 'NULL';
     179      else if (!$this->IsFunction($Value))
    166180      {
    167         if (is_null($Value)) $Value = 'NULL';
    168         else $Value = $this->PDO->quote($Value);
     181        $Value = $this->PDO->quote($Value);
    169182      }
    170183      $Values .= ', `'.$Key.'`='.$Value;
    … …  
    174187  }
    175188
    176   function replace($Table, $Data)
     189  function replace(string $Table, array $Data): void
    177190  {
    178191    $Name = '';
    … …  
    180193    foreach ($Data as $Key => $Value)
    181194    {
    182       if (!in_array($Value, $this->Functions))
     195      if (is_null($Value)) $Value = 'NULL';
     196      else if (!$this->IsFunction($Value))
    183197      {
    184         if (is_null($Value)) $Value = 'NULL';
    185         else $Value = $this->PDO->quote($Value);
     198        $Value = $this->PDO->quote($Value);
    186199      }
    187200      $Name .= ',`'.$Key.'`';
    … …  
    195208  }
    196209
    197   function charset($Charset)
     210  function charset(string $Charset): void
    198211  {
    199212    $this->query('SET NAMES "'.$Charset.'"');
    200213  }
    201214
    202   function real_escape_string($Text)
     215  function real_escape_string(string $Text): string
    203216  {
    204217    return addslashes($Text);
    205218  }
    206219
    207   function quote($Text)
     220  function quote(string $Text): string
    208221  {
    209222    return $this->PDO->quote($Text);
    210223  }
    211224
    212   public function __sleep()
     225  public function __sleep(): array
    213226  {
    214227    return array('LastQuery');
    215228  }
    216229
    217   public function __wakeup()
    218   {
    219   }
    220  
    221   public function Transaction($Queries)
    222   {
    223       $this->PDO->beginTransaction();
    224       foreach ($Queries as $Query)
    225       {
    226         $Statement = $this->PDO->prepare($Query);
    227         $Statement->execute();
    228       }         
    229       $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;
    230252  }
    231253}
Note: See TracChangeset for help on using the changeset viewer.