Yii : работаем с PostgreSql и несколькими схемами
Начиная свой новый проект с использованием фреймворка Yii, решил в качестве базы данных взять неведомый мне ранее продукт СУБД PostgreSQL. Он позволяет выполнять намного более сложные операции с данными, чем MySQL. В частности, делить базы данных на схемы. При этом у каждой схемы могут быть свои таблицы, триггеры, представления (view). Так же организована довольно гибкая система предоставления прав доступа. И есть поддержка данных в формате JSON.
Про настройки и использование баз данных в приложениях на базе фреймворка Yii читайте на странице Yii : работа с базами данных в моём блоге.
Далее передо мной встал вопрос разделения данных на несколько частей. Эта задача возникает в том случае, когда необходимо уменьшить нагрузку на сервер базы данных, например, если предполагается хранить слишком большой объём данных, будет слишком частое обращение к базе данных или внутри базы будут выполняться сложные вычислительные операции. Физически при этом данные делятся на части и размещаются на разных серверах. На тестовом сервере обычно всего лишь одна база данных и разделение приходится эмулировать. При этом применяют следующие техники разделения данных:
- Префиксы таблиц: таблицам, которые должны физически размещаться на разных серверах, добавляют символы, обозначающие эти сервера, например: host1_user, host1_post как бы с сервера host1, а host2_comments, host2_media с сервера host2
CREATE DATABASE test; USE test; CREATE TABLE host1_user ( ... ); CREATE TABLE host1_post ( ... ); CREATE TABLE host2_comments( ... ); CREATE TABLE host2_media ( ... );
- Разделение по базам данных: внутри одной СУБД выделяются несколько баз, которые в промышленной эксплуатации располагаются на разных серверах:
CREATE DATABASE host1; USE host1; CREATE TABLE user ( /* ... */ ); CREATE TABLE post ( /* ... */ ); CREATE DATABASE host2; USE host2; CREATE TABLE comments( /* ... */ ); CREATE TABLE media ( /* ... */ );
- Выделение схем внутри одной базы данных, если СУБД поддерживает такую возможность
CREATE DATABASE postgres; CREATE SCHEMA host1; SET search_path TO host1; CREATE TABLE user ( /* ... */ ); CREATE TABLE post ( /* ... */ ); CREATE SCHEMA host2; SET search_path TO host2; CREATE TABLE comments( /* ... */ ); CREATE TABLE media ( /* ... */ );
СУБД PostgreSQL поддерживает работу со схемами, но стандартные классы Yii для работы с этой СУБД всегда используют только одну схему public.
Далее приводится пример, как настроить Yii для работы с PostgreSQL c несколькими схемами.
- Настройка (необходимо изменить файл protected/config/main.php)
- Класс DBConnection (необходимо создать файл protected/components/DBConnection.php)
- Класс PgsqlSchema (необходимо создать файл protected/components/PgsqlSchema.php)
- Примеры использования
<?php /** * @file protected/config/main.php */ return array( // ... 'components'=>array( // ... // Настройки для базы данных 'db1' => array( 'class' => 'DBConnection', 'connectionString' => 'pgsql:host=127.0.0.1;port=5432;dbname=postgres', 'username' => 'username', 'password' => 'password', 'charset' => 'utf8', 'defaultSchema' => 'host1', // это имя первой схемы, соответствующей одному хосту ), 'db2' => array( 'class' => 'DBConnection', 'connectionString' => 'pgsql:host=127.0.0.1;port=5432;dbname=postgres', 'username' => 'username', 'password' => 'password', 'charset' => 'utf8', 'defaultSchema' => 'host2', // это имя второй схемы, соответствующей другому хосту ), // ... ), // ... ); // (примеры использования смотри в конце статьи)
<?php
/**
* @file protected/components/DBConnection.php
*/
/**
* Database connection
*/
class DBConnection extends CDbConnection {
public $defaultSchema = null;
protected function initConnection($pdo) {
parent::initConnection($pdo);
if ($pdo->getAttribute(PDO::ATTR_DRIVER_NAME) == 'pgsql') {
$this->driverMap['pgsql']='PgsqlSchema';
if (!is_null($this->defaultSchema)) {
Yii::trace("PostgreSQL: changing schema to '{$this->defaultSchema}'", 'protected.components.DbConnection');
$cmd = $pdo->prepare("SET search_path TO '{$this->defaultSchema}'");
$cmd->execute();
}
}
}
}
<?php
/**
* @file protected/components/PgsqlSchema.php
*/
/**
* Class PgsqlSchema allows read tables definitions from PostgreSQL
*/
class PgsqlSchema extends CPgsqlSchema {
private $_defaultSchema = 'public';
private $_sequences = array();
function __construct($conn) {
parent::__construct($conn);
$this->_defaultSchema = $conn->defaultSchema;
}
protected function resolveTableNames($table, $name) {
$parts = explode('.', str_replace('"', '', $name));
if(isset($parts[1])) {
$schemaName = $parts[0];
$tableName = $parts[1];
} else {
$schemaName = $this->_defaultSchema;
$tableName = $parts[0];
}
$table->name = $tableName;
$table->schemaName = $schemaName;
if($schemaName === $this->_defaultSchema) {
$table->rawName = $this->quoteTableName($tableName);
} else {
$table->rawName = $this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName);
}
}
protected function findColumns($table) {
$sql = <<<EOD
SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef, pg_catalog.col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attnum > 0 AND NOT a.attisdropped
AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = :table
AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema))
ORDER BY a.attnum
EOD;
$command = $this->getDbConnection()->createCommand($sql);
$command->bindValue(':table', $table->name);
$command->bindValue(':schema', $table->schemaName);
if(($columns = $command->queryAll()) === array()) {
return false;
}
foreach($columns as $column) {
$c = $this->createColumn($column);
$table->columns[$c->name] = $c;
if(stripos($column['adsrc'], 'nextval') === 0 && preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i', $column['adsrc'], $matches)) {
if(strpos($matches[1],'.') !== false || $table->schemaName === $this->_defaultSchema) {
$this->_sequences[$table->rawName.'.'.$c->name] = $matches[1];
} else {
$this->_sequences[$table->rawName.'.'.$c->name] = $table->schemaName.'.'.$matches[1];
}
$c->autoIncrement = true;
}
}
return true;
}
protected function findTableNames($schema = '') {
if($schema === '') {
$schema = $this->_defaultSchema;
}
$sql = <<<EOD
SELECT table_name, table_schema FROM information_schema.tables
WHERE table_schema = :schema AND table_type = 'BASE TABLE'
EOD;
$command = $this->getDbConnection()->createCommand($sql);
$command->bindParam(':schema', $schema);
$rows = $command->queryAll();
$names = array();
foreach($rows as $row) {
if($schema === $this->_defaultSchema) {
$names[] = $row['table_name'];
} else {
$names[] = $row['table_schema'].'.'.$row['table_name'];
}
}
return $names;
}
}
<?php
// Примеры использования на основе DAO
// для настроек, указанных выше в примере файла конфигурации protected/config/main.php
$db1Rows = Yii::app()->db1->createCommand('SELECT * FROM {{users}}')->gueryAll(); // обращение к данным хоста host1
$db2Rows = Yii::app()->db2->createCommand('SELECT * FROM {{comments}}')->gueryAll(); // обращение к данным хоста host2
// обратите внимание на синтаксис имён таблиц: для варианта имитации разделения
// данных по префиксам таблицы {{users}} превращается в host1_users
// Примеры использования на базе Active Records
/**
* Пример модели для по таблицы, которая находится в схеме host1
*/
class Users extends CActiveRecord {
/**
* Возвращает имя таблицы
*/
public function tableName() {
return 'db1.users'; // в качестве префикса таблицы используется имя коннекта БД, соответствущего серверу host1
}
}
/**
* Пример модели для по таблицы, которая находится в схеме host2
*/
class Comments extends CActiveRecord {
/**
* Возвращает имя таблицы
*/
public function tableName() {
return 'db2.comments'; // в качестве префикса таблицы используется имя коннекта БД, соответствущего серверу host2
}
}
Два важных замечания.
Первое. Часто возникает потребность сделать JOIN по таблицам. Из-за того, что на тестовом сервере разделение по хостам имитируется, а физически данные расположены в одном месте, запросы легко отрабатывают. В промышленной эксплуатации запросы завершатся с ошибкой. Запросы с JOIN по таблицам, которые хранятся на разных хостах, запрещены. Я пока не знаю, как это выявить на уровне Yii. Алгоритмы работы с «разделёнными» данными: сначала надо выбрать данные из таблиц одного хоста, затем другого и соединить их в памяти приложения PHP.
Второе. Транзакции, которые затрагивают данные с разных хостов, в общем случае не смогут закомититься одновременно. Если комит по данных одного сервера прошёл, а во время комита данных второго сервера получился сбой, то возможно нарушение целостности данных. Возможно, шаман-администратор СУБД сможет настроить связи между хостами так, чтобы транзакции выполнялись одновременно (так называемые, распределённые транзакции), но обычно используют некоторые техники, позволяющих выполнять одновременное обновление данных на нескольких хостах. Эти вопросы остаются за рамками статьи.
Ссылки по теме:
Pingback: Yii + Postgres | Devdocs.ru()