PHP code example of aeatech / transaction-manager-postgresql

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

aeatech / transaction-manager-postgresql example snippets

use AEATech\TransactionManager\DoctrineAdapter\DbalPostgresConnectionAdapter;
use AEATech\TransactionManager\ExecutionPlanBuilder;
use AEATech\TransactionManager\ExponentialBackoff;
use AEATech\TransactionManager\GenericErrorClassifier;
use AEATech\TransactionManager\IsolationLevel;
use AEATech\TransactionManager\PostgreSQL\PostgreSQLErrorHeuristics;
use AEATech\TransactionManager\PostgreSQL\PostgreSQLIdentifierQuoter;
use AEATech\TransactionManager\PostgreSQL\PostgreSQLTransactionsFactoryBuilder;
use AEATech\TransactionManager\PostgreSQL\PostgreSQLTransactionsFactoryInterface as PgTxFactory;

// Create the PostgreSQL transactions factory
$txFactory = PostgreSQLTransactionsFactoryBuilder::build();

// Example: UPSERT by unique email
$tx = $txFactory->createInsertOnConflictUpdate(
    tableName: 'users',
    rows: [
        ['id' => 1, 'email' => '[email protected]', 'name' => 'Foo'],
    ],
    updateColumns: ['name'],
    conflictTarget: $txFactory->conflictTargetByColumns(['email']),
    columnTypes: [
        'id' => \PDO::PARAM_INT,
    ],
    isIdempotent: true,
);

$options = new TxOptions(
    isolationLevel: IsolationLevel::ReadCommitted,
    retryPolicy: new RetryPolicy(3, new ExponentialBackoff())
);

$runResult = $tm->run($tx, $options);

$tx = $txFactory->createInsert(
    tableName: 'audit_log',
    rows: [
        ['id' => 1, 'event' => 'login', 'meta' => json_encode(['ip' => '1.1.1.1'])],
        ['id' => 2, 'event' => 'logout', 'meta' => null],
    ],
    columnTypes: [
        'id' => \PDO::PARAM_INT,
        'event' => \PDO::PARAM_STR,
        // 'meta' type can be omitted; DBAL will infer
    ],
    isIdempotent: false,
);
$tm->run($tx, $options);

use AEATech\TransactionManager\StatementReusePolicy;

$tx = $txFactory->createInsert(
    tableName: 'users',
    rows: [
        ['id' => 1, 'email' => '[email protected]', 'name' => 'Foo'],
    ],
    columnTypes: ['id' => \PDO::PARAM_INT],
    isIdempotent: false,
    statementReusePolicy: StatementReusePolicy::PerTransaction,
);

$tm->run($tx, $options);

$tx = $txFactory->createInsertIgnore(
    tableName: 'users',
    rows: [
        ['id' => 1, 'email' => '[email protected]'],
        ['id' => 1, 'email' => '[email protected]'], // duplicate id — ignored
    ],
    // columnTypes optional
    isIdempotent: true,
);
$tm->run($tx, $options);

$target = $txFactory->conflictTargetByColumns(['email']);

$tx = $txFactory->createInsertOnConflictUpdate(
    tableName: 'users',
    rows: [
        ['id' => 10, 'email' => '[email protected]', 'name' => 'Alice'],
        ['id' => 11, 'email' => '[email protected]', 'name' => 'Bob'],
    ],
    updateColumns: ['name'],
    conflictTarget: $target,
    isIdempotent: true,
);
$tm->run($tx, $options);

$target = $txFactory->conflictTargetByConstraint('uniq_users_email');

$tx = $txFactory->createInsertOnConflictUpdate(
    tableName: 'users',
    rows: [
        ['id' => 10, 'email' => '[email protected]', 'name' => 'Alice'],
    ],
    updateColumns: ['name'],
    conflictTarget: $target,
    isIdempotent: true,
);
$tm->run($tx, $options);

$tx = $txFactory->createDelete(
    tableName: 'users',
    identifierColumn: 'id',
    identifierColumnType: \PDO::PARAM_INT,
    identifiers: [1, 2, 3],
    isIdempotent: true,
);
$tm->run($tx, $options);

$tx = $txFactory->createDeleteWithLimit(
    tableName: 'events',
    identifierColumn: 'account_id',
    identifierColumnType: \PDO::PARAM_INT,
    identifiers: [42],        // delete rows for account_id=42
    limit: 1000,              // at most 1000 physical rows
    isIdempotent: true,
);
$tm->run($tx, $options);

$tx = $txFactory->createUpdate(
    tableName: 'users',
    rows: [
        ['id' => 1, 'name' => 'Renamed'],
        ['id' => 2, 'name' => 'Also Renamed'],
    ],
    identifierColumn: 'id',
    identifierColumnType: \PDO::PARAM_INT,
    updateColumns: ['name'],
    updateColumnTypes: [
        'name' => \PDO::PARAM_STR,
    ],
    isIdempotent: true,
);
$tm->run($tx, $options);

$tx = $txFactory->createUpdateWhenThen(
    tableName: 'users',
    rows: [
        ['id' => 1, 'quota' => 100, 'plan' => 'basic'],
        ['id' => 2, 'quota' => 250, 'plan' => 'pro'],
    ],
    identifierColumn: 'id',
    identifierColumnType: \PDO::PARAM_INT,
    updateColumns: ['quota', 'plan'],
    updateColumnTypes: [
        'quota' => \PDO::PARAM_INT,
        'plan'  => \PDO::PARAM_STR,
    ],
    isIdempotent: true,
);
$tm->run($tx, $options);

$sql = 'UPDATE "users" SET "name" = ? WHERE "id" = ?';
$params = ['John', 123];
$types = [\PDO::PARAM_STR, \PDO::PARAM_INT];
$tx = $txFactory->createSql($sql, $params, $types, isIdempotent: true);
$tm->run($tx, $options);

bash
for v in php-cli-8.2-pg16 php-cli-8.2-pg17 php-cli-8.2-pg18 php-cli-8.3-pg16 php-cli-8.3-pg17 php-cli-8.3-pg18 php-cli-8.4-pg16 php-cli-8.4-pg17 php-cli-8.4-pg18 ; do \
  echo "Testing PHP $v..."; \
  docker-compose -p aeatech-transaction-manager-postgresql -f docker/docker-compose.yml exec -T $v vendor/bin/phpunit || break; \
done