ALBIREO CMS
version: 2025.11.01
© Albireo CMS, 2020-2025

Работа с SQLite в Albireo CMS

Общее описание

SQLite — это основная база данных, которая используется в Albireo CMS для хранения динамических данных. Например это могут быть логи операций, комментарии посетителей, регистрации пользователей и т.п.

База SQLite представляет собой одиночный файл специального формата. Поддержка SQLite стандартно входит во все версии PHP.

Albireo CMS работает с SQLite через стандартный механизм PDO, но предоставляет несколько удобных «оболочек» для упрощения кода.

Общий порядок работы с SQLite

Изначально — это подключение к базе данных. Для этого можно использовать класс \Pdo\PdoConnect, который предоставляет удобный интерфейс для работы с PDO.

После этого можно выполнять различные операции с базой данных, используя SQL. Для этого можно использовать класс \Pdo\PdoQuery.

Подключение через PdoConnect

В общем случае это может выглядеть так:

// получить экземпляр класса
$pdo = \Pdo\PdoConnect::getInstance();

// выполнить подключение
$db = $pdo->connect(['dsn' => 'sqlite:' . STORAGE_DIR . 'my.sqlite']);

// если нет подключения, выйти
if (empty($db)) return;

... дальше работа с базой ...

Метод connect() принимает единственным аргументом PDO-конфигурацию базы. Например:

# Sqlite
[
    'dsn' => 'sqlite:' . STORAGE_DIR . 'my.sqlite',
]

# MySQL
[
	'dsn' => 'mysql:host=127.0.0.1;dbname=MYDB;charset:UTF8',
	'username' => 'user',
	'password' => '123456',
	'options' => [
		\PDO::ATTR_PERSISTENT => true,
         \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
	],
     'queries' => [
		'SET NAMES UTF8',
	],
]

Если подключение было успешным, то будет возвращён стандартный объект PDO.

Класс PdoConnect выполнен как Singleton и сохраняет подключения к базе, что позволяет исключить их повторные подключения.

Подключение через функцию getDB()

Если предполагается хранить параметры базы в отдельном конфигурационном файле, то проще использовать функцию getDB(), которая сама выполнит все подключения.

По умолчанию функция использует конфигурацию в файле dbase.php, где указываются псевдонимы для каждой базы. Примерно так:

return [
    'filesinfo' => [
        'dsn' => 'sqlite:' . STORAGE_DIR . 'filesinfo.sqlite',
    ],
    
    'log' => [
        'dsn' => 'sqlite:' . STORAGE_DIR . 'log.sqlite',
    ],
    
    'stat' => [
        'dsn' => 'sqlite:' . STORAGE_DIR . 'stat.sqlite',
    ],
];

Соответственно подключение в базе можно выполнить по алиасу:

$db = getDB('log');

if ($db) {
     // работа с базой ...
}

Либо использовать свой конфигурационный файл в CONFIG_DIR:

// # my-base.php
// return [
//     'my' => [
//         'dsn' => 'sqlite:' . STORAGE_DIR . 'my.sqlite',
//     ],
// ];

$db = getDB('my', configFN: 'my-base.php');

if ($db) {
     // работа с базой ...
}

Подключение PDO будет работать с любой базой, а не только SQLite.

Класс PdoQuery

PdoQuery — это надстройка над PDO для удобной и безопасной работы с базой данных в Albireo CMS.

Класс реализует основные CRUD-операции, работу с транзакциями, пагинацией, а также вспомогательные методы для вывода данных и управления таблицами.

Методы PdoQuery первым аргументом принимают PDO-объект базы, который получается после подключения. Хотя PdoQuery генерирует SQL, который может часто работать для разных баз, следует учитывать, то его основное назначение — это именно SQLite.

Краткое описание методов

  • fetchAll — получить все записи по SQL-запросу
  • execute — выполнить подготовленный запрос
  • query — выполнить "сырой" SQL-запрос
  • exec — выполнить SQL без возврата результата (например, CREATE, DROP)
  • insert — вставить одну или несколько записей
  • insertSql — вставка с "сырыми" значениями (использовать осторожно!)
  • update — обновить записи
  • delete — удалить записи
  • tableExists — проверить существование таблицы
  • dropTable — удалить таблицу
  • countRecord — количество записей в таблице
  • getPagination — получить параметры для пагинации
  • outTableRows — вывести массив данных в виде HTML-таблицы
  • outListRows — вывести массив данных в виде HTML-списка
  • vacuum — выполнить VACUUM (SQLite)
  • begin/commit — управление транзакциями
  • journalMode — установить режим журнала (SQLite)
  • tableInfo — информация о структуре таблицы
  • buildPlaceholders — создать плейсхолдеры для IN()
  • upsert — вставка или обновление записей "insert or update" (UPSERT).

Примеры использования

1. Получить все записи

// Получить все записи из таблицы с условиями
$sql = 'SELECT name, age FROM users WHERE age > :min_age';
$vars = [':min_age' => 18];
$rows = \Pdo\PdoQuery::fetchAll($db, $sql, $vars);

foreach ($rows as $row) {
    echo $row['name'] . ' — ' . $row['age'] . '<br>';
}

2. Вставка данных

// Вставить одну запись
\Pdo\PdoQuery::insert($db, 'users', ['name' => 'Ivan', 'age' => 30]);

// Вставить несколько записей
\Pdo\PdoQuery::insert(
    $db,
    'users',
    ['name' => 'Anna', 'age' => 25],
    ['name' => 'Petr', 'age' => 28]
);

3. Обновление данных

// Обновить возраст пользователя по id
\Pdo\PdoQuery::update(
    $db,
    'users',
    ['age' => 31],      // set
    ['id' => 5],        // data для where
    'id = :id'          // where
);

4. Удаление данных

// Удалить пользователя по id
\Pdo\PdoQuery::delete($db, 'users', 'id = :id', [':id' => 5]);

5. Создание таблиц

if (!\Pdo\PdoQuery::tableExists($db, 'comments')) {
    // создать таблицу
    \Pdo\PdoQuery::query($db, "CREATE TABLE IF NOT EXISTS comments (
               id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
               page TEXT NOT NULL DEFAULT '',
               author TEXT NOT NULL DEFAULT '',
               date TEXT NOT NULL DEFAULT '',
               allow INTEGER NOT NULL DEFAULT 0,
               user TEXT NOT NULL DEFAULT '',
               content TEXT NOT NULL DEFAULT '');");
    
    // создать индексы
    \Pdo\PdoQuery::exec($this->db, "
        CREATE INDEX IF NOT EXISTS idx_comments_page ON comments(page);
        CREATE INDEX IF NOT EXISTS idx_comments_allow ON comments(allow);
    ");
}

6. Пагинация

Для получения записей лучше воспользоваться функцией getPages(), которая сама формирует пагинацицю.

$limit = 10;
$pagination = \Pdo\PdoQuery::getPagination($db, 'users', $limit);

$rows = \Pdo\PdoQuery::fetchAll(
    $db,
    'SELECT * FROM users LIMIT ' . $limit . ' OFFSET ' . $pagination['offset']
);

// Вывод таблицы
echo \Pdo\PdoQuery::outTableRows($rows);

// Вывод пагинации (пример)
if ($pagination['max'] > 1) {
    // echo ... пагинация ...
}

7. Транзакции

\Pdo\PdoQuery::begin($db);
// ... операции ...
\Pdo\PdoQuery::commit($db);

8. Получить id последней вставленной строки (SQLite)

$lastId = \Pdo\PdoQuery::lastInsertRowId($db, 'users');

9. Быстрый вывод данных

// получение
$rows = \Pdo\PdoQuery::fetchAll($db, 'SELECT * FROM users');

// ОТЛАДКА
// вывод как таблица
echo \Pdo\PdoQuery::outTableRows($rows);

// вывод как список
echo \Pdo\PdoQuery::outListRows($rows);

10. Использование плейсхолдеров для IN()

$ids = [1, 2, 3];
$placeholders = \Pdo\PdoQuery::buildPlaceholders($ids);
$sql = 'SELECT * FROM users WHERE id IN (' . $placeholders . ')';
$rows = \Pdo\PdoQuery::fetchAll($db, $sql, $ids);

11. Транзакции и журналирования

// устанавливается до начала блока транзакций
\Pdo\PdoQuery::journalMode($db, 'MEMORY');
    // MEMORY файл журнала ведется в памяти - очень быстрый
    // PERSIST начало файла журнала забивается
    // TRUNCATE файл журнала обрезается до нуля
    // DELETE файл журнала удаляется после завершения транзакции - по умолчанию
    // WAL особый новый вариант

// начало блока транзакций
\Pdo\PdoQuery::begin($db);

... работа с базой

// фиксация транзакций
\Pdo\PdoQuery::commit($db);

12. Очистка таблицы

// очистка
\Pdo\PdoQuery::dropTable($db, 'my_table');

// сжатие файла
\Pdo\PdoQuery::vacuum($db);

13. Создание индексов

// используется exec(), поскольку не нужен результат
\Pdo\PdoQuery::exec($db, "CREATE INDEX idx_info ON file_info (info);");
\Pdo\PdoQuery::exec($db, "CREATE INDEX idx_type ON file_info (type);");
\Pdo\PdoQuery::exec($db, "CREATE INDEX idx_type_draft ON file_info (type, draft);");
\Pdo\PdoQuery::exec($db, "CREATE INDEX idx_date_unix ON file_info (date_unix);");

14. Добавление нового поля в таблицу

// проверим есть ли в таблице поле user
$p = \Pdo\PdoQuery::fetchAll($db, "SELECT name FROM PRAGMA_TABLE_INFO('comments') WHERE name='user'");
if (!$p) 
    \Pdo\PdoQuery::exec($db, "ALTER TABLE comments ADD COLUMN user TEXT NOT NULL DEFAULT ''");

upsert

upsert — вставка или обновление записей в таблице SQLite по принципу "insert or update" (UPSERT).

Если запись с указанным уникальным ключом уже существует, будут обновлены выбранные поля.

Сигнатура:

\Pdo\PdoQuery::upsert(
    \PDO $db,              // PDO-соединение с базой данных
    string $table,         // Имя таблицы
    array $rows,           // Одна или несколько записей для вставки (ассоциативный массив или массив массивов)
    array|string $uniqueBy,// Поле или массив полей, по которым определяется уникальность (конфликт)
    $update = null         // (необязательно) Массив полей для обновления при конфликте. Если не указано — все, кроме уникальных
)

Примеры использования

1. Вставка или обновление одной записи

\Pdo\PdoQuery::upsert(
    $db,
    'users',
    ['email' => 'foo@example.com', 'name' => 'Foo', 'age' => 25],
    'email',           // уникальное поле
    ['name', 'age']    // обновлять только name и age при конфликте
);

2. Вставка или обновление нескольких записей

\Pdo\PdoQuery::upsert(
    $db,
    'users',
    [
        ['email' => 'foo@example.com', 'name' => 'Foo', 'age' => 25],
        ['email' => 'bar@example.com', 'name' => 'Bar', 'age' => 30],
    ],
    'email',           // уникальное поле
    ['name', 'age']    // обновлять только name и age при конфликте
);

3. Обновлять все поля (кроме уникальных) при конфликте

\Pdo\PdoQuery::upsert(
    $db,
    'users',
    ['email' => 'foo@example.com', 'name' => 'Foo', 'age' => 25],
    'email'
    // $update не указан — обновятся все поля, кроме email
);

4. Несколько уникальных полей

\Pdo\PdoQuery::upsert(
    $db,
    'products',
    ['sku' => 'A123', 'warehouse' => 1, 'stock' => 50],
    ['sku', 'warehouse'], // конфликт по двум полям
    ['stock']
);

Особенности

  • Работает только с SQLite 3.24.0+ (поддержка ON CONFLICT ... DO UPDATE).
  • Если передан массив записей, метод выполнит UPSERT для каждой.
  • Если $update не указан, обновляются все поля, кроме уникальных.
  • Возвращает результат выполнения для каждой записи (true/false).

Пример SQL, который генерирует метод:

INSERT INTO users (email, name, age)
VALUES (:email, :name, :age)
ON CONFLICT(email) DO UPDATE SET name=excluded.name, age=excluded.age;