Базы данных

#1. Вступление

Как и почти каждое современное веб-приложение, Cotonti взаимодействует с базой данных, и это взаимодействие реализовано в движке чрезвычайно просто и доступно.

В настоящее время поддерживается версия MySQL 5.1+

#2. Конфигурация

Конфигурация подключения к базе данных находится в файле конфигурации вашего приложения datas/config.php. Она формируется автоматически при установке Cotonti на хостинг.

#3. Выполнение SQL запросов

Вы можете получить доступ к объекту БД через класс-фасад Cot. Объект базы данных Cotonti построен поверх PDO и предоставляет метод для выполнения запросов к БД а также методы упрощающие построение запросов INSERT, UPDATE и DELETE.

Вы можете выполнить SQL-запрос, выполнив следующие шаги:

  1. Создать SQL-запрос
  2. Привязать параметры (необязательно)
  3. Вызвать один из методов выполнения SQL из PDOStatement.

В следующем примере показаны различные способы извлечения данных из базы данных:

// Возвращает набор строк. Каждая строка представляет собой ассоциативный массив имен столбцов
// и значений.
// Если запрос не вернул результатов, возвращается пустой массив
$posts = Cot::$db->query('SELECT * FROM post')->fetchAll();

// Возвращает одну (первую или следующую) строку из набора результатов
// Если запрос не вернул результата, возвращается значение FALSE
$post = Cot::$db->query('SELECT * FROM post WHERE id=?', 1)->fetch();

// Возвращает один столбец из следующей строки набора результатов
// Или FALSE, если строк больше нет.
$titles = Cot::$db->query('SELECT title FROM post')->fetchColumn();
$count = Cot::$db->query('SELECT COUNT(*) FROM post')->fetchColumn();

Для запросов, не возвращающих данные, следует использовать метод PDOStatement::execute(). Например:

Cot::$db->query('UPDATE post SET status=1 WHERE id=1')->execute();

Для запросов INSERT, UPDATE и DELETE вместо написания чистого SQL можно вызвать методы insert(), update(), delete() соответственно, для создания указанных SQL конструкций. Например:

// INSERT (table_name, column_values)
Cot::$db->insert(
    'posts', 
    [
        'titlle' => 'Lorem ipsum', 
        'text' => 'Morbi imperdiet tortor ut nisl ultricies finibus',
    ]
);
$newPostId = Cot::$db->lastInsertId();

// UPDATE (table_name, column_values, condition, params)
$condition = [
	"created_at < '" . date('Y-m-d H:i:s', time() - 3600) . "'",
	'category = :category',
];
$updatedRowsCount = Cot::$db->update(
    'posts', 
    ['status' => 1], 
    $condition, 
    ['category' => $category]
);

// DELETE (table_name, condition, params)
$deletedRowsCount = Cot::$db->delete(
    'posts', 
    'status = 0 AND category = ?', 
    $category
);

Методы insert(), update() и delete() не требуют экранирования названия таблицы. Это будет сделано автоматически.

Также методы insert() и update() не требуют экранирования элементов массива с данными для вставки/обновления.

Методы update() и delete() принимают в качестве условия (condition) строку с SQL-условием или массив строк. В случае с массивом каждая строка будет обернута в скобки и они объединяются оператором AND.

В примере выше:

$condition = [
	"created_at < '2024-07-08 12:45:56'",
	'category = :category'
];

эквивалентно:

(created_at < '2024-07-08 12:45:56') AND (category = :category)

#3.1. Привязка параметров

При создании SQL запроса с параметрами, всегда нужно использовать привязку параметров для предотвращения атак через SQL инъекции. Например:

$post = Cot::$db->query(
	'SELECT * FROM post WHERE category = ? AND status = ?',
	[$category, status]
)->fetch();

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

Если в запросе всего один параметер, вместо массива можно передать скалярное значение:

$post = Cot::$db->query('SELECT * FROM post WHERE category = ?', $category)->fetch();

Можно использовать именованные параметры:

$post = Cot::$db->query(
	'SELECT * FROM post WHERE id = :id AND status = :status',
	['id' => $id, ':status' => status]
)->fetch();

#3.2. Экранирование имён таблиц и столбцов

Cot::$db->query(
    'SELECT * FROM ' . Cot::$db->quoteTableName(Cot::$db->pages) 
    . ' WHERE page_cat = ' . Cot::$db->quote($category)
)->fetchColumn();

Методы для экранирования:

  • Cot::$db->quoteTableName() или сокращенная версия (Cot::$db->quoteT())
    Экранирует имя таблицы для использования в запросе. Если имя таблицы содержит префикс схемы, префикс также будет корректно экранирован.
  • Cot::$db->quoteColumnName() или сокращенная версия (Cot::$db->quoteC())
    Экранирует имя столбца. Если имя сожержит префикс, он также будет корректно экранирован. Если имя столбца уже экранировано, то метод ничего не делает.
  • Cot::$db->quote()
    Экранирует строковое значение для использования в запросе. Вместо этого метода рекомендуется использовать привязку параметров, если это возможно.

#4. Использование транзакций

Вы можете использовать метод Cot::$db->transaction() для выполнения нескольких запросов в рамках транзакции. Если при выполнении переданной функции будет выброшено исключение, база данных откатится на состояние, которое было до выполнения запросов, и исключение будет брошено дальше. Если функция-замыкание будет выполнена успешно, транзакция будет зафискирована автоматически. При использовании этого метода нет необходимости заботиться об откате или фискировании транзакции:

Cot::$db->transaction(
	function(): void 
	{
		Cot::$db->update('posts', ['status' = 1], 'category = ?', $category);
		Cot::$db->delete('posts', 'status = 0']);
		// ... executing other SQL statements ...
	}
);

#4.1. Ручное использование транзакций

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

Cot::$db->beginTransaction();
try {
    Cot::$db->update('posts', ['status' = 1], 'category = ?', $category);
    Cot::$db->delete('posts', 'status = 0']);
	// ... executing other SQL statements ...
    
    Cot::$db->commit();
} catch(\Throwable $e) {
    Cot::$db->rollBack();
	throw $e;
}

#4.2. Указание уровня изоляции

Cotonti поддерживает настройку уровня изоляции для ваших транзакций. По умолчанию, при старте транзакции, будет использован уровень изоляции, настроенный в вашей базе данных. Вы можете переопределить уровень изоляции по умолчанию, как указано ниже:

$isolationLevel = \cot\database\DataBaseDictionary::REPEATABLE_READ;

Cot::$db->transaction(
	function(): void 
	{
		....
	},
	$isolationLevel
);
 
// или

Cot::$db->beginTransaction($isolationLevel);

#4.3. Обработка Deadlock'ов (взаимоблокировок)

Метод Cot::$db->transaction() принимает необязательный третий аргумент, который определяет, сколько раз следует повторять транзакцию при возникновении Deadlock'а. Как только эти попытки будут исчерпаны, будет выдано исключение:

Cot::$db->transaction(
	function(): void 
	{
		....
	},
	$isolationLevel,
	5
);

#5. Работа со схемой базы данных

(функционал пополняется)

Объект базы данных Cotonti предоставляет несколько методов для работы со схемой базы данных. Эти методы описаны ниже::

tableExists($tableName) - проверяет существование таблицы

fieldExists($tableName, $fieldName) - проверяет существование поля

indexExists($tableName, $indexName, $indexColumns = []) - проверяет существование индекса

addIndex($tableName, $indexName, $indexColumns = []) - создает индекс, где аргумент $indexColumns это строка, содержащая имя одного поля или массив с одним или множеством имен полей. Если пусто, $indexName будет использован в качестве имени поля.

#6. Выполнение SQL скриптов

Метод CotDB::runScript($script, $translaction = false) позволяет выполнять SQL-скрипты (содержимое SQL-файлов). Имена таблиц должны быть экранированы символом экранирования MySQL ` и иметь префикс cot_, который будет заменен на префикс из настроек в datas/config.php.


Комментарии отсутствуют
Добавление комментариев доступно только зарегистрированным пользователям