Улучшения PHP 8.4 при работе с современными версиями Firebird

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    SELECT
        CURRENT_TIMESTAMP AS TS_TZ,
        LOCALTIMESTAMP AS TS,
        CURRENT_TIME AS T_TZ,
        LOCALTIME AS T
    FROM RDB$DATABASE
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    echo "Result: \n";
    var_dump($row);
    echo "\n";
    $sth->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.3.11
Result:
array(4) {
  ["TS_TZ"]=>
  NULL
  ["TS"]=>
  string(19) "2024-09-11 15:50:21"
  ["T_TZ"]=>
  NULL
  ["T"]=>
  string(8) "15:50:21"
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    SELECT
        SUM(SALARY) AS SUM_SALARY,
        CAST(SUM(SALARY) AS NUMERIC(18, 2)) AS SUM_SALARY_2
    FROM EMPLOYEE
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    echo "Result: \n";
    var_dump($row);
    echo "\n";
    $sth->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.3.11
Result:
array(2) {
  ["SUM_SALARY"]=>
  string(4) "0.01"
  ["SUM_SALARY_2"]=>
  string(11) "16203468.02"
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    SELECT
        QUANTIZE(12354.678, 123.54) AS DF,
        CAST(QUANTIZE(12354.678, 123.54) AS DOUBLE PRECISION) AS D
    FROM RDB$DATABASE
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    echo "Result: \n";
    var_dump($row);
    echo "\n";
    $sth->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.3.11
Result:
array(2) {
  ["DF"]=>
  NULL
  ["D"]=>
  string(8) "12354.68"
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    SELECT
        CURRENT_TIMESTAMP AS TS_TZ,
        LOCALTIMESTAMP AS TS,
        CURRENT_TIME AS T_TZ,
        LOCALTIME AS T
    FROM RDB$DATABASE
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    echo "Result: \n";
    var_dump($row);
    echo "\n";
    $sth->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.4.0beta5
Result:
array(4) {
  ["TS_TZ"]=>
  string(33) "2024-09-11 17:44:52 Europe/Moscow"
  ["TS"]=>
  string(19) "2024-09-11 17:44:52"
  ["T_TZ"]=>
  string(22) "17:44:52 Europe/Moscow"
  ["T"]=>
  string(8) "17:44:52"
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    SELECT
        SUM(SALARY) AS SUM_SALARY,
        CAST(SUM(SALARY) AS NUMERIC(18, 2)) AS SUM_SALARY_2
    FROM EMPLOYEE
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    echo "Result: \n";
    var_dump($row);
    echo "\n";
    $sth->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.4.0beta5
Result:
array(2) {
  ["SUM_SALARY"]=>
  string(11) "16203468.02"
  ["SUM_SALARY_2"]=>
  string(11) "16203468.02"
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    SELECT
        QUANTIZE(12354.678, 123.54) AS DF,
        CAST(QUANTIZE(12354.678, 123.54) AS DOUBLE PRECISION) AS D
    FROM RDB$DATABASE
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    echo "Result: \n";
    var_dump($row);
    echo "\n";
    $sth->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.4.0beta5
Result:
array(2) {
  ["DF"]=>
  string(8) "12354.68"
  ["D"]=>
  string(8) "12354.68"
}

create sequence gen_employee;

create table employee (
  employee_id bigint not null,
  name varchar(50) not null,
  lastname varchar(50)
);

set term ^;

create trigger tr_employee_bi
for employee before insert
as
begin
  if (new.employee_id is null) then
     new.employee_id = next value for gen_employee;
end^

set term ;^

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TS_SQL = <<<'SQL'
    INSERT INTO employee (employee_id, name, lastname)
	VALUES (?, ?, ?)
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/test;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->prepare(TS_SQL);
    $sth->execute([null, 'John', 'Smith']);
    echo "OK\n";

	$cur_stmt = $dbh->prepare('select * from employee');
	$cur_stmt->execute();
	$rows = $cur_stmt->fetchAll(PDO::FETCH_ASSOC);
	var_dump($rows);
    $cur_stmt->closeCursor();
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.3.11
Error: SQLSTATE[HY105]: Invalid parameter type: -999 Parameter requires non-null value

INSERT INTO employee (employee_id, name, lastname)
VALUES (null, 'John', 'Smith')

PHP version: 8.4.0beta5
OK
array(1) {
  [0]=>
  array(3) {
    ["EMPLOYEE_ID"]=>
    int(2)
    ["NAME"]=>
    string(4) "John"
    ["LASTNAME"]=>
    string(5) "Smith"
  }
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TNX_PROP_SQL = <<<'SQL'
    SELECT
      TRIM(
        CASE
          WHEN T.MON$ISOLATION_MODE = 0 THEN 'CONSISTENCY'
          WHEN T.MON$ISOLATION_MODE = 1 THEN 'CONCURRENCY'
          WHEN T.MON$ISOLATION_MODE = 2 THEN 'READ COMMITTED RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 3 THEN 'READ COMMITTED NO RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 4 THEN 'READ COMMITTED READ CONSISTENCY'
        END
      ) AS ISOLATION_MODE,
      TRIM(
        CASE
          WHEN T.MON$LOCK_TIMEOUT = 0 THEN 'NO WAIT'
          ELSE 'WAIT'
        END
      ) AS WAIT_MODE,
      CASE
        WHEN T.MON$LOCK_TIMEOUT > 0 THEN MON$LOCK_TIMEOUT
      END AS LOCK_TIMEOUT,
      TRIM(
        CASE
          WHEN T.MON$READ_ONLY = 1 THEN 'READ ONLY'
          WHEN T.MON$READ_ONLY = 0 THEN 'READ WRITE'
        END
      ) AS RW_MODE,
      (T.MON$AUTO_COMMIT = 1) AS AUTO_COMMIT,
      (T.MON$AUTO_UNDO = 1) AS AUTO_UNDO
    FROM
      MON$TRANSACTIONS T
    WHERE T.MON$TRANSACTION_ID = CURRENT_TRANSACTION
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $sth = $dbh->query(TNX_PROP_SQL);
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    $sth->closeCursor();
    echo "Transaction property: \n";
    var_dump($row);
    echo "\n";
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.4.0beta5
Transaction property:
array(6) {
  ["ISOLATION_MODE"]=>
  string(31) "READ COMMITTED READ CONSISTENCY"
  ["WAIT_MODE"]=>
  string(4) "WAIT"
  ["LOCK_TIMEOUT"]=>
  NULL
  ["RW_MODE"]=>
  string(9) "READ WRITE"
  ["AUTO_COMMIT"]=>
  bool(false)
  ["AUTO_UNDO"]=>
  bool(true)
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TNX_PROP_SQL = <<<'SQL'
    SELECT
      TRIM(
        CASE
          WHEN T.MON$ISOLATION_MODE = 0 THEN 'CONSISTENCY'
          WHEN T.MON$ISOLATION_MODE = 1 THEN 'CONCURRENCY'
          WHEN T.MON$ISOLATION_MODE = 2 THEN 'READ COMMITTED RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 3 THEN 'READ COMMITTED NO RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 4 THEN 'READ COMMITTED READ CONSISTENCY'
        END
      ) AS ISOLATION_MODE,
      TRIM(
        CASE
          WHEN T.MON$LOCK_TIMEOUT = 0 THEN 'NO WAIT'
          ELSE 'WAIT'
        END
      ) AS WAIT_MODE,
      CASE
        WHEN T.MON$LOCK_TIMEOUT > 0 THEN MON$LOCK_TIMEOUT
      END AS LOCK_TIMEOUT,
      TRIM(
        CASE
          WHEN T.MON$READ_ONLY = 1 THEN 'READ ONLY'
          WHEN T.MON$READ_ONLY = 0 THEN 'READ WRITE'
        END
      ) AS RW_MODE,
      (T.MON$AUTO_COMMIT = 1) AS AUTO_COMMIT,
      (T.MON$AUTO_UNDO = 1) AS AUTO_UNDO
    FROM
      MON$TRANSACTIONS T
    WHERE T.MON$TRANSACTION_ID = CURRENT_TRANSACTION
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    // start transaction
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
    $dbh->exec('SET TRANSACTION READ WRITE NO WAIT ISOLATION LEVEL SNAPSHOT');
    // execute query
    $sth = $dbh->query(TNX_PROP_SQL);
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    $sth->closeCursor();
    echo "Transaction property: \n";
    var_dump($row);
    echo "\n";
    // commit transaction
    //$dbh->exec('COMMIT');
    $dbh->commit();
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.3.11
Transaction property:
array(6) {
  ["ISOLATION_MODE"]=>
  string(11) "CONCURRENCY"
  ["WAIT_MODE"]=>
  string(7) "NO WAIT"
  ["LOCK_TIMEOUT"]=>
  NULL
  ["RW_MODE"]=>
  string(10) "READ WRITE"
  ["AUTO_COMMIT"]=>
  bool(false)
  ["AUTO_UNDO"]=>
  bool(true)
}

Error: There is no active transaction

namespace Pdo;

class Firebird extends \PDO
{
    // Attributes for date and time formats
    public const int ATTR_DATE_FORMAT;
    public const int ATTR_TIME_FORMAT;
    public const int ATTR_TIMESTAMP_FORMAT;

    public const int TRANSACTION_ISOLATION_LEVEL;

    // Transaction isolation level
    public const int READ_COMMITTED;
    public const int REPEATABLE_READ;
    public const int SERIALIZABLE;

    public const int WRITABLE_TRANSACTION;

    public static function getApiVersion(): int;
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TNX_PROP_SQL = <<<'SQL'
    SELECT
      TRIM(
        CASE
          WHEN T.MON$ISOLATION_MODE = 0 THEN 'CONSISTENCY'
          WHEN T.MON$ISOLATION_MODE = 1 THEN 'CONCURRENCY'
          WHEN T.MON$ISOLATION_MODE = 2 THEN 'READ COMMITTED RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 3 THEN 'READ COMMITTED NO RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 4 THEN 'READ COMMITTED READ CONSISTENCY'
        END
      ) AS ISOLATION_MODE,
      TRIM(
        CASE
          WHEN T.MON$LOCK_TIMEOUT = 0 THEN 'NO WAIT'
          ELSE 'WAIT'
        END
      ) AS WAIT_MODE,
      CASE
        WHEN T.MON$LOCK_TIMEOUT > 0 THEN MON$LOCK_TIMEOUT
      END AS LOCK_TIMEOUT,
      TRIM(
        CASE
          WHEN T.MON$READ_ONLY = 1 THEN 'READ ONLY'
          WHEN T.MON$READ_ONLY = 0 THEN 'READ WRITE'
        END
      ) AS RW_MODE,
      (T.MON$AUTO_COMMIT = 1) AS AUTO_COMMIT,
      (T.MON$AUTO_UNDO = 1) AS AUTO_UNDO
    FROM
      MON$TRANSACTIONS T
    WHERE T.MON$TRANSACTION_ID = CURRENT_TRANSACTION
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
    $dbh->setAttribute(PDO\Firebird::TRANSACTION_ISOLATION_LEVEL, PDO\Firebird::REPEATABLE_READ);
    $dbh->setAttribute(PDO\Firebird::WRITABLE_TRANSACTION, false);
    // start transaction
    $dbh->beginTransaction();
    // execute query
    $sth = $dbh->query(TNX_PROP_SQL);
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    $sth->closeCursor();
    echo "Transaction property: \n";
    var_dump($row);
    echo "\n";
    // commit transaction
    $dbh->commit();
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.4.0beta5
Transaction property:
array(6) {
  ["ISOLATION_MODE"]=>
  string(11) "CONCURRENCY"
  ["WAIT_MODE"]=>
  string(4) "WAIT"
  ["LOCK_TIMEOUT"]=>
  NULL
  ["RW_MODE"]=>
  string(9) "READ ONLY"
  ["AUTO_COMMIT"]=>
  bool(false)
  ["AUTO_UNDO"]=>
  bool(true)
}

<?php

namespace MyApp;

use PDO;
use PDOException;
use PDOStatement;

const TNX_PROP_SQL = <<<'SQL'
    SELECT
      TRIM(
        CASE
          WHEN T.MON$ISOLATION_MODE = 0 THEN 'CONSISTENCY'
          WHEN T.MON$ISOLATION_MODE = 1 THEN 'CONCURRENCY'
          WHEN T.MON$ISOLATION_MODE = 2 THEN 'READ COMMITTED RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 3 THEN 'READ COMMITTED NO RECORD VERSION'
          WHEN T.MON$ISOLATION_MODE = 4 THEN 'READ COMMITTED READ CONSISTENCY'
        END
      ) AS ISOLATION_MODE,
      TRIM(
        CASE
          WHEN T.MON$LOCK_TIMEOUT = 0 THEN 'NO WAIT'
          ELSE 'WAIT'
        END
      ) AS WAIT_MODE,
      CASE
        WHEN T.MON$LOCK_TIMEOUT > 0 THEN MON$LOCK_TIMEOUT
      END AS LOCK_TIMEOUT,
      TRIM(
        CASE
          WHEN T.MON$READ_ONLY = 1 THEN 'READ ONLY'
          WHEN T.MON$READ_ONLY = 0 THEN 'READ WRITE'
        END
      ) AS RW_MODE,
      (T.MON$AUTO_COMMIT = 1) AS AUTO_COMMIT,
      (T.MON$AUTO_UNDO = 1) AS AUTO_UNDO
    FROM
      MON$TRANSACTIONS T
    WHERE T.MON$TRANSACTION_ID = CURRENT_TRANSACTION
SQL;

try {
    echo 'PHP version: ' . phpversion() . "\n";
    $dbh = new PDO(
        'firebird:dbname=inet://localhost/employee;charset=utf8',
        'SYSDBA',
        'masterkey',
        [
          PDO\Firebird::WRITABLE_TRANSACTION => false
        ]
    );
    $sth = $dbh->query(TNX_PROP_SQL);
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    $sth->closeCursor();
    echo "Transaction property: \n";
    var_dump($row);
    echo "\n";
}
catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

PHP version: 8.4.0beta5
Transaction property:
array(6) {
  ["ISOLATION_MODE"]=>
  string(31) "READ COMMITTED READ CONSISTENCY"
  ["WAIT_MODE"]=>
  string(4) "WAIT"
  ["LOCK_TIMEOUT"]=>
  NULL
  ["RW_MODE"]=>
  string(9) "READ ONLY"
  ["AUTO_COMMIT"]=>
  bool(false)
  ["AUTO_UNDO"]=>
  bool(true)
}