Обзор работы с базами данных

public function __construct(string $driver = 'mysql')

public function tryconnect(): int

$db = new SqlConnection('mysql');
$db->server = 'localhost';
$db->user = 'root';
$db->pass = 'password';
$db->db = 'mydb';

if ($db->tryconnect()) {
    echo "Подключено!";
}

public function connect(string $server, string $user, string $pass, ?string $db = null): int

$db = new SqlConnection('pgsql');
$db->connect('192.168.1.10', 'admin', 'secret', 'production');

public static function create_conn(
    string $server,
    string $user,
    string $pass,
    string $db = '',
    string $driver = 'mysql'
): self

$db = SqlConnection::create_conn('localhost', 'root', 'password', 'mydb');
// Повторный вызов вернёт тот же экземпляр
$db2 = SqlConnection::create_conn('localhost', 'root', 'password', 'mydb');
// $db === $db2

public function ping(): bool

if (!$db->ping()) {
    $db->tryconnect();
}

public function is_valid(): bool

public function connected(): bool

public function usedb(string $dbname): void

public static function table(string $table): \Illuminate\Database\Query\Builder

$users = SqlConnection::table('users')
    ->where('active', 1)
    ->orderBy('name')
    ->get();

public static function connection(string $name = null): \Illuminate\Database\Connection

$conn = SqlConnection::connection('mysql');
$schema = $conn->getSchemaBuilder();

public static function __callStatic(string $method, array $args)

SqlConnection::purge();
SqlConnection::beginTransaction();

public function query(string $query = '', ?array $arr = null, bool $save_log = true)

$r = $db->query("SELECT * FROM users WHERE id = ?", [1]);
while ($row = $db->fetch($r)) {
    echo $row['name'];
}
$db->free_result($r);

public function query_params(string $query = '', ?array $arr = null, bool $save_log = true)

public function fetch(?\PDOStatement $result): array|false

public function fetch_assoc(?\PDOStatement $result): array|false

public function fetchAll(?\PDOStatement $result): array|false

public function free_result(?\PDOStatement $result): bool

public function num_rows(?\PDOStatement $res): int

public function last_insert_id(): string|int

public function get_var(string $field, string $q, array $arr = [])

$name = $db->get_var('name', "SELECT name FROM users WHERE id = 1");

public function get_count(string $q, string $field = 'count')

public static function escape(string $msg): string

$safe = SqlConnection::escape("O'Brien");
// O\'Brien

public static function escape2(string $field): string

$escaped = SqlConnection::escape2('users.name');
// `users`.`name`

// Префикс # отключает экранирование
$raw = SqlConnection::escape2('#COUNT(*)');
// COUNT(*)

// Префикс * возвращает как есть
$star = SqlConnection::escape2('*');
// *

public function insert(string $table, array $data, ?array $data2 = null): int

$db->insert('users', [
    'name' => 'John',
    'email' => 'john@example.com',
    'status' => 1,
]);

public function update(string $table, array $filter, array $data, ?array $data2 = null): int

$db->update('users', ['pkid' => 1], ['name' => 'Jane']);
$db->update('users', ['status' => 0, '>created_at' => '2024-01-01'], ['active' => 0]);

public function delete(string $table, array $filter, ?int $count = null): int

$db->delete('users', ['pkid' => 1]);
$db->delete('logs', ['<created_at' => '2024-01-01'], 100);

public function insert_or_update(string $table, array $data, ?array $data2 = null): int

public function insert_or_ignore(string $table, array $data, ?array $data2 = null): int

public function search(string $table, array $params): array

[
    'error_str' => '',
    'error_code' => EIO_OK,
    'res' => [...],       // массив записей
    'count' => 150,       // общее количество
    'time' => 5,          // время выполнения (мс)
]

$result = $db->search('users', [
    'filter' => ['status' => 1, '%name' => 'John'],
    'order' => ['name' => 'asc', 'id' => 'desc'],
    'limit' => 25,
    'start' => 0,
]);

foreach ($result['res'] as $row) {
    echo $row['name'];
}
echo "Всего: {$result['count']}";

public function sync_items(string $table, array $params): void

$db->sync_items('categories', [
    'new_items' => $importedCategories,
    'filter' => ['parent_id' => 0],
    'find' => function(array $old, array $new): bool {
        return $old['code'] === $new['code'];
    },
    'create' => function(array $new) use ($db): void {
        $db->insert('categories', $new);
    },
    'update' => function(array $old, array $new) use ($db): void {
        $db->update('categories', ['pkid' => $old['pkid']], $new);
    },
    'delete' => function(array $old) use ($db): void {
        $db->delete('categories', ['pkid' => $old['pkid']]);
    },
]);

public function beginTransaction(): bool
public function commit(): bool
public function rollBack(): bool

$db->beginTransaction();
try {
    $db->insert('orders', ['user_id' => 1, 'total' => 100]);
    $db->update('users', ['pkid' => 1], ['balance' => '-100']);
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

public function affected_rows(): ?int

public function found_rows(): ?int

public function error(): string

public function errorstr(): string

public function information_schema_load_current_db(): void

public static function getEngine(): string

$engine = ConnectionManager::getEngine(); // 'mysql' или 'pgsql'

public static function mysqlConfig(
    $host,
    $database,
    $username,
    $password,
    $port = 3306
): array

$config = ConnectionManager::mysqlConfig('localhost', 'mydb', 'root', 'secret');
// ['driver' => 'mysql', 'host' => 'localhost', ...]

public static function pgsqlConfig(
    $host,
    $database,
    $username,
    $password,
    $port = 5432
): array

public static function readWriteConfig(
    $readHosts,
    $writeHosts,
    $database,
    $username,
    $password
): array

$config = ConnectionManager::readWriteConfig(
    ['replica1.local', 'replica2.local'],
    'master.local',
    'mydb',
    'root',
    'secret'
);

public static function fromConstants(): array

$connections = ConnectionManager::fromConstants();
// ['mysql' => [...]] или ['pgsql' => [...]]

public static function init($config, $name = 'default'): \Illuminate\Database\Capsule\Manager

DB::init([
    'driver' => 'mysql',
    'host' => 'localhost',
    'database' => 'mydb',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
]);

public static function initPdo(\PDO $pdo, string $driver = 'mysql'): \Illuminate\Database\Capsule\Manager

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'root', 'secret');
DB::initPdo($pdo);

public static function getCapsule(): \Illuminate\Database\Capsule\Manager

public static function connection($name = null): \Illuminate\Database\Connection

$conn = DB::connection('mysql');

DB::table('users')->where('active', 1)->get();
DB::beginTransaction();
DB::purge();

use IO\Database\SqlConnection;

$db = new SqlConnection('mysql');
$db->server = 'localhost';
$db->user = 'root';
$db->pass = 'password';
$db->db = 'mydb';

if ($db->tryconnect()) {
    $result = $db->search('users', [
        'filter' => ['status' => 1],
        'limit' => 10,
    ]);

    foreach ($result['res'] as $user) {
        echo $user['name'] . "\n";
    }
}

use IO\Database\DB;
use IO\Database\ConnectionManager;

// Из констант окружения
$connections = ConnectionManager::fromConstants();
foreach ($connections as $name => $config) {
    DB::init($config, $name);
}

// Использование
$users = DB::table('users')->where('active', 1)->get();
DB::table('users')->insert(['name' => 'New User']);

$db = new SqlConnection('mysql');
$db->connect('localhost', 'root', 'pass', 'mydb');

// SELECT
$r = $db->query("SELECT * FROM users WHERE status = ?", [1]);
while ($row = $db->fetch($r)) {
    echo $row['name'];
}
$db->free_result($r);

// INSERT
$db->query("INSERT INTO logs (msg, created_at) VALUES (?, NOW())", ['test']);

// UPDATE
$db->query("UPDATE users SET active = 0 WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)");

$db->beginTransaction();
try {
    $db->insert('transfers', [
        'from_user' => 1,
        'to_user' => 2,
        'amount' => 500,
    ]);

    $db->update('users', ['pkid' => 1], ['balance' => '-500']);
    $db->update('users', ['pkid' => 2], ['balance' => '+500']);

    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

// SELECT с фильтрами
$users = SqlConnection::table('users')
    ->where('active', 1)
    ->where('role', 'admin')
    ->orderBy('name', 'asc')
    ->limit(25)
    ->get();

// JOIN
$orders = SqlConnection::table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->select('orders.*', 'users.name')
    ->where('orders.status', 'pending')
    ->get();

// COUNT
$count = SqlConnection::table('users')->where('active', 1)->count();

// UPDATE
SqlConnection::table('users')
    ->where('pkid', 1)
    ->update(['last_login' => now()]);

$filter = [
    'status' => 1,                    // WHERE status = 1
    '%name' => 'John',                // WHERE name LIKE '%John%'
    '>age' => 18,                     // WHERE age > 18
    '<=price' => 100,                 // WHERE price <= 100
    '~deleted_at' => null,            // WHERE deleted_at IS NULL
    '!~deleted_at' => null,           // WHERE deleted_at IS NOT NULL
    'role' => ['admin', 'moderator'], // WHERE role IN ('admin', 'moderator')
    '$or' => [
        ['type' => 'premium'],
        ['status' => 'active'],
    ],
];

$users = SqlConnection::table('users');
$users = SqlConnection::applyFilterToQuery($users, $filter);
$results = $users->get();

$db1 = SqlConnection::create_conn('host1', 'user1', 'pass1', 'db1');
$db2 = SqlConnection::create_conn('host2', 'user2', 'pass2', 'db2');

if ($db->ping()) {
    // Соединение активно
} else {
    $db->tryconnect();
}

use IO\Classes\MigrationLog;

MigrationLog::getApplied();      // list<string> имен миграций
MigrationLog::getMaxBatch();     // int максимальный batch
MigrationLog::log('name', 1);    // записать миграцию
MigrationLog::removeLog('name', 1); // удалить запись

use IO\Classes\CronJob;

CronJob::getEnabled();            // list<array> активные задачи
CronJob::findByClassname('DeleteCron'); // ?array
CronJob::getRunningCount(1);     // int выполняющихся потоков

use IO\Classes\CronWork;

CronWork::markRunning(1);        // int ID записи
CronWork::markDone($id, $log, $timestamps, $avg, $count);
CronWork::markFailed($id, $error);
CronWork::getStats();            // array{running, completed, failed}
CronWork::cleanupOlderThan('2026-01-01');

use IO\Classes\CronData;

CronData::load('my_key');        // ?array
CronData::save('my_key', $data);

use IO\Classes\CacheRow;

CacheRow::get('key');             // mixed
CacheRow::set('key', $value, 3600);
CacheRow::forget('key');
CacheRow::flush();
CacheRow::gc();                  // удалить истёкшие

// app/Classes/User.php
namespace App\Classes;
use IO\SqlClass;

class User extends SqlClass
{
    protected string $table = 'users';
    protected string $primaryKey = 'pkid';
    public bool $timestamps = false;
}