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 ( ... );
    
    Как это реализовать в Yii
  • Разделение по базам данных: внутри одной СУБД выделяются несколько баз, которые в промышленной эксплуатации располагаются на разных серверах:
    CREATE DATABASE host1;
    USE host1;
    CREATE TABLE user ( /* ... */ );
    CREATE TABLE post  ( /* ... */ );
    
    CREATE DATABASE host2;
    USE host2;
    CREATE TABLE comments( /* ... */ );
    CREATE TABLE media ( /* ... */ );
    
    Как это реализовать в Yii
  • Выделение схем внутри одной базы данных, если СУБД поддерживает такую возможность
    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 &amp;&amp; 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.

Второе. Транзакции, которые затрагивают данные с разных хостов, в общем случае не смогут закомититься одновременно. Если комит по данных одного сервера прошёл, а во время комита данных второго сервера получился сбой, то возможно нарушение целостности данных. Возможно, шаман-администратор СУБД сможет настроить связи между хостами так, чтобы транзакции выполнялись одновременно (так называемые, распределённые транзакции), но обычно используют некоторые техники, позволяющих выполнять одновременное обновление данных на нескольких хостах. Эти вопросы остаются за рамками статьи.

Ссылки по теме:

Павел Волынцев

Уже более 15 лет занимаюсь разработкой веб-проектов. Fullstack Senior Developer. IT евангелист — доношу свет знаний об информационных технологиях. Профессиональные цели: Дать людям возможность дать людям больше.

Читайте также:

  • BarrylA

    I see you are sharing valuable content on your website, do you know that you can monetize your site and earn a lot of money, just search in google for — Make money online by Nelpersempre — additional $100-$200 a day is worth a try

    • pvolyntsev

      BarrylA, good idea, I agree. But now I am under attack of the generosity and altruism and share my works without any fee. Probably because there is nothing valuable. May be later a bit.

  • pavel_volyntsev

    Блин, сбилась разметка в примерах кода

    • Всё, исправил. И года не прошло. Также уменьшил размеры шрифта на сайте, иначе читать примеры вообще невозможно. Сегодня сделаю копию примеров на gist.github.com, чтобы можно было легко делиться 🙂

  • Pingback: Yii + Postgres | Devdocs.ru()

  • очень полезно. но вот незадача — в config
    // database settings are configured in database.php
    ‘db’ => require(dirname(__FILE__) . ‘/database.php’),

    то есть идет обращение к файлу. можно в этом файле поправить чтоб заработало? или нужно явно в конфиге править?

    • Если настройки вынесены в отдельные файлы, то можно подключить так http://take.ms/EiIKI

      • а в одном файле их не описать?

        • Это был один из вариантов. Можно подключать и из одного файла.
          Например, вот так мог бы выглядеть database.php http://take.ms/QeXsW
          А вот так подключаться http://take.ms/UimAl

          • я про это и спрашивал. спасибо огромное

  • появился такой вопрос — как сделать чтобы $model-id возвращал returning id?

    https://github.com/yiisoft/yii/issues/3925

    • Особенность реализации PostgreSQL в том, что там нет понятия AUTO_INCREMENT у первичного ключа таблицы.
      Уникальное значение идентификатора нужно определить заранее, а затем устанавливать его как обычное свойство модели.

      Вот это не работает:

      $user = new User();
      $user->save();
      $lastId = Yii::app()->db->getLastInsertID(); // <-- будет ошибка

      Нужно так:

      «`

      $lastId = Yii::app()->db->getLastInsertID(‘tbl_user_seq’); // взять последний номер в последовательности, он тут же увеличится и повторов не будет

      $user = new User();

      $user->id = $lastId; // явно назначить идентификатор

      $user->save();

      «`

      где tbl_user_seq — это последовательность (sequence), созданная специально для нумерации сущностей таблицы tbl_user. Последовательность должны быть инициализирована так же как и таблица

      «`

      CREATE TABLE tbl_user ( — таблица

      id integer PRIMARY KEY

      — … другие поля

      );

      CREATE SEQUENCE tbl_user START 1; — последовательность специально для таблицы tbl_user

      «`

      Вот документация
      * http://www.postgresql.org/docs/9.1/static/sql-createsequence.html
      * http://www.postgresql.org/docs/9.1/static/functions-sequence.html

      • Булат Камалов

        просто при создании таблицы надо указать у столбца id тип serial. Это аналог auto increment на mysql

        • Булат, ты прав. Так и есть. Сиквенс будет создан неявно. В коде Yii для PostgreSQL такой случай обрабатывается.

          Надо мне почаще самому с PostgreSQL работать. Сейчас вообще нет ни одного проекта на нём.

    • К сожалению, я не могу проверить свой собственный пример кода, потому что у меня не осталось рабочих проектов на Yii + PostgreSQL. Если тебе не трудно, то отпишись — решилась твоя проблема или не решилась.

  • Для Yii2 нашёл такой ответ на StaskOverflow : http://stackoverflow.com/questions/26436024/how-to-set-default-schema-in-yii2 — но это только для случая, когда в проекте используется одна схема