source: trunk/Packages/Common/Database.php

<?php

// Extended database class
// Date: 2020-11-10

function microtime_float()
{
  list($usec, $sec) = explode(" ", microtime());
  return (float)$usec + (float)$sec;
}

class DatabaseResult
{
  public PDOStatement $PDOStatement;
  public int $num_rows = 0;

  function fetch_assoc()
  {
    return $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
  }

  function fetch_array()
  {
    return $this->PDOStatement->fetch(PDO::FETCH_BOTH);
  }

  function fetch_row()
  {
    return $this->PDOStatement->fetch(PDO::FETCH_NUM);
  }
}

class Database
{
  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(', 'SHA1(');
    $this->Type = 'mysql';  // mysql, pgsql
    $this->Error = '';
    $this->LastQuery = '';
    $this->ShowSQLError = false;
    $this->ShowSQLQuery = false;
    $this->LogSQLQuery = false;
    $this->LogFile = dirname(__FILE__).'/../../Query.log';
    $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)
    {
      unset($this->PDO);
      throw new Exception($E->getMessage());
    }
  }

  function Disconnect(): void
  {
    unset($this->PDO);
  }

  function Connected(): bool
  {
    return isset($this->PDO);
  }

  function select_db(string $Database)
  {
    $this->query('USE `'.$Database.'`');
  }

  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_float();
    $this->LastQuery = $Query;
    if (($this->ShowSQLQuery == true) or ($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)
      echo('<div style="border-bottom-width: 1px; border-bottom-style: solid; '.
      'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n");
    $Result = new DatabaseResult();
    $Statement = $this->PDO->query($Query);
    if ($Statement)
    {
      $Result->PDOStatement = $Statement;
      $Result->num_rows = $Statement->rowCount();
      $this->insert_id = $this->PDO->lastInsertId();
    } else
    {
      $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>');
        throw new Exception('SQL Error: '.$this->Error.', Query: '.$Query);
    }
    return $Result;
  }

  function select(string $Table, string $What = '*', string $Condition = '1'): DatabaseResult
  {
    return $this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
  }

  function delete(string $Table, string $Condition): void
  {
    $this->query('DELETE FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition);
  }

  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 = '';
    $Values = '';
    foreach ($Data as $Key => $Value)
    {
      $Name .= ',`'.$Key.'`';
      if (is_null($Value)) $Value = 'NULL';
      else if (!$this->IsFunction($Value))
      {
        $Value = $this->PDO->quote($Value);
      }
      $Values .= ','.$Value;
    }
    $Name = substr($Name, 1);
    $Values = substr($Values, 1);
    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 (is_null($Value)) $Value = 'NULL';
      else if (!$this->IsFunction($Value))
      {
        $Value = $this->PDO->quote($Value);
      }
      $Values .= ', `'.$Key.'`='.$Value;
    }
    $Values = substr($Values, 2);
    return 'UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')';
  }

  function replace(string $Table, array $Data): void
  {
    $Name = '';
    $Values = '';
    foreach ($Data as $Key => $Value)
    {
      if (is_null($Value)) $Value = 'NULL';
      else if (!$this->IsFunction($Value))
      {
        $Value = $this->PDO->quote($Value);
      }
      $Name .= ',`'.$Key.'`';
      $Values .= ','.$Value;
    }
    $Name = substr($Name, 1);
    $Values = substr($Values, 1);
    $this->query('REPLACE INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
  }

  function charset(string $Charset): void
  {
    $this->query('SET NAMES "'.$Charset.'"');
  }

  function real_escape_string(string $Text): string
  {
    return addslashes($Text);
  }

  function quote(string $Text): string
  {
    return $this->PDO->quote($Text);
  }

  public function __sleep(): array
  {
    return array('LastQuery');
  }

  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;
  }
}

function TimeToMysqlDateTime($Time)
{
  if ($Time == NULL) return NULL;
    else return date('Y-m-d H:i:s', $Time);
}

function TimeToMysqlDate($Time)
{
  if ($Time == NULL) return NULL;
    else return date('Y-m-d', $Time);
}

function TimeToMysqlTime($Time)
{
  if ($Time == NULL) return NULL;
    else return date('H:i:s', $Time);
}

function MysqlDateTimeToTime($DateTime)
{
  if ($DateTime == '') return NULL;
  $Parts = explode(' ', $DateTime);
  $DateParts = explode('-', $Parts[0]);
  $TimeParts = explode(':', $Parts[1]);
  $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]);
  return $Result;
}

function MysqlDateToTime($Date)
{
  if ($Date == '') return NULL;
  return MysqlDateTimeToTime($Date.' 0:0:0');
}

function MysqlTimeToTime($Time)
{
  if ($Time == '') return NULL;
  return MysqlDateTimeToTime('0000-00-00 '.$Time);
}