Archive for Разработка

Sequence in MySQL

Sequence, как объект базы данных, отсутствует в MySQL, но тем не менее, его можно организовать на триггерах. Существуют разные способы организации уникального ключа для нескольких таблиц (на уровне приложения, процедура). В этой статье рассказывается, как организовать последовательность +1 с использованием “before insert” триггеров.

Что есть Sequence?

Напомним, что такое Sequence (в Oracle). Последовательность CREATE SEQUENCE – это объект базы данных, который генерирует целые числа в соответствии с правилами, установленными во время его создания.

Задача

Требуется обеспечить уникальным значениями первичного ключа несколько таблиц (или все таблицы) в базе данных MySQL).

Решение

Будем использовать вспомогательную таблицу Sequence, в которой будет генерироваться последовательность, поле Name опционально — для имени таблицы (без префикса):

Gdn::Structure()
    ->Table('Sequence')
    ->Engine('InnoDB')
    ->PrimaryKey('SequenceID', 'int')
    ->Column('Name', 'char(20)')
    ->Set($Explicit, $Drop);

Для создания триггера в MySQL у пользователя выполняющего запрос должны быть права TRIGGER, до версии 5.1.6 нужны привелегии SUPER. Триггеры будут создаваться динамически при событии типа schema-update, чтобы поддерживать код в актуальном состоянии.

Нам понадобятся названия таблиц и их первичные ключи (ключ: имя таблицы, значение: имя ключа, как правило это “имя таблицы” + “ID”):

$SequenceTables = Model::GetSequenceTables();

Для случая, когда мы обновляем таблицы (а не создаем их) понадобиться максимальное значение первичного ключа из покрываемых sequence-ом таблиц:

$NextAutoIncrement = MaxAutoIncrement(array_keys($SequenceTables));
$Construct->Query("alter table {$DatabasePrefix}Sequence 
    auto_increment = $NextAutoIncrement");

Код функции MaxAutoIncrement() не приводится, остается в качестве упражнения, можно сделать несколькими способами.

Шаблон кода тела триггера, для каждой из таблиц, где:
%1$s — префикс таблиц
%2$s — имя таблицы
%3$s — имя первичного ключа таблицы

$TriggerCodeFormat = 'begin
if new.%3$s then begin
    replace into %1$sSequence values(new.%3$s, "%2$s");
end; else begin
    insert into %1$sSequence values(null, "%2$s");
    set new.%3$s = last_insert_id();
end; end if;
end';

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

Далее запускается цикл с именами таблиц и создается триггер с имененем «Sequence» + «имя_таблицы» применив шаблон, предварительно получив все триггеры запросом show triggers и проверкой на, возможно, ранее уже созданный точно такой же триггер для этой таблицы:

$CreateTriggerFormat = 'create trigger Sequence%2$s before insert on %2$s for each row ' . $TriggerCodeFormat;
foreach ($SequenceTables as $Table => $PrimaryKey) {
    $TriggerName = "Sequence{$Table}";
    $Statement = GetValueR("$TriggerName.Statement", $DatabaseTriggers);
    $TriggerCode = sprintf($TriggerCodeFormat, $Px, $Table, $PrimaryKey);
    if ($TriggerCode != $Statement) {
        $CreateTriggerSql = sprintf($CreateTriggerFormat, $Px, $Table, $PrimaryKey);
        try {
            $Construct->Query("drop trigger if exists $TriggerName");
            $Construct->Query($CreateTriggerSql);
        } catch (Exception $Exception) {
            if (Debug()) throw $Exception;
            elseif ($Exception->GetCode() != 256) throw $Exception;
        }
    }
}

Ограничения

  1. На данный момент (MySQL 5.5) нельзя создать более одного триггера для одной таблицы, с  теми же самыми действиями и событиями.
  2. Не реализовано удаление записей из таблицы Sequence.
  3. Update первичного ключа, при выполнении запроса вида:
    update t1 set id = 1 where id = 2;

    не сработает триггер и соответственно запись в таблице Sequence останется неверной.

  4. Однозначная идентификация по ID из Sequence может поломаться, если вставляется запись с заданным значением первичного ключа.

Ссылки

  1. http://sql-language.ru/create-sequence.html
  2. http://club.shelek.ru/viewart.php?id=317
  3. http://www.rldp.ru/mysql/mysqlpro/triggers.htm
  4. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_trigger

Добавить комментарий

Структура для локализации контента

На основе vanillaforums/Garden #551: Multi translate for any content. Задача сделать структуру для локализации контента, причем количество языков — неизвестно, как и количество полей.

1. Структура

Задачу будем решать с помощью модели EAV (Entity-attribute-value Model).

Gdn::Structure()
    ->Table('Localization')
    ->Column('Language', 'char(2)')
    ->Column('Name', 'varchar(50)', False, 'index') // Table name
    ->Column('Field', 'varchar(50)')
    ->Column('RowID', 'int', False)
    ->Column('Value', 'text', True)
    ->Set();

Структура довольно понятна и упрощена (денормализована). О недостатках поговорим позже, сейчас хотелось бы отметить преимущество — изменения в структуру больше не потребуется.
Language — двух-буквенный код языка
Name — имя таблицы с полем Field, содержимое которого нужно перевести
Field — имя поля из таблицы Name
RowID — значение первичного ключа таблицы Name
Value — перевод содержимого

По-хорошему, индексы должны быть на полях Language и Name. Скорее всего, в большинстве случаев, будет использоваться 2-3 языка, поэтому смысла добавлять в индекс Language нет — селективность высокая. Поэтому ограничимся индексом поля Name.

2. Преобразование строк в колонки

По иностранному это называется pivot. Существует несколько способов развернуть таблицу. Рецепт развертывания, шаблон запроса в сыром виде:

select 
    group_concat(if(Field = 'FIELD1', Value, NULL)) as `FIELD1`,
    group_concat(if(Field = 'FIELD2', Value, NULL)) as `FIELD2`,
    ...
    group_concat(if(Field = 'FIELDN', Value, NULL)) as `FIELDN`,
    RowID
from Localization
where Language = 'LANGUAGE_CODE' and Name = 'TABLE_NAME'
group by RowID

Если используемая вами СУБД не поддерживает group_concat() можно использовать функцию max(). В результате получаем значение первичного ключа RowID, набор переводов полей FIELD1, FIELD2, …, FIELDN языка LANGUAGE_CODE для таблицы TABLE_NAME. Думайте об этом результате как о таблице, которую нужно соеденить с основной.

3. Пример

К примеру, у нас есть приложение типа «Каталог» с двумя локализированным полями «Название» (Name) и «Описание» (Description).

Gdn::Structure()
    ->Engine('MyISAM')
    ->Table('CatalogItem')
    ->PrimaryKey('CatalogItemID', 'usmallint')
    ->Column('Name', 'varchar(200)')
    ->Column('Description', 'text', True)
    ->Column('Price', 'float')
    ->Column('Image', 'varchar(250)', True)
    ->Set();

a) Преобразование таблицы Localization (это пример кода внутри какой-то модели: $this->Name — имя таблицы):

$LocaleName = Gdn::Config('Garden.Locale');
$LanguageCode = ArrayValue(0, explode('-', LocaleName));
Gdn::SQL()
    ->Select("if(Field = 'Name', Value, Null)", 'group_concat', 'Name')
    ->Select("if(Field = 'Description', Value, Null)", 'group_concat', 'Description');
$InnerSQL = Gdn::SQL()
    ->Select('RowID')
    ->From('Localization')
    ->Where('Language', $LanguageCode)
    ->Where('Name', $this->Name)
    ->GroupBy('RowID')
    ->GetSelect();
$InnerSQL = $this->SQL->ApplyParameters($InnerSQL);
Gdn::SQL()->Reset();

1. Первые строчки — определение какой язык используется (здесь язык берется из конфига, но ничто не мешает передать его как параметр).
2. Далее, выбор полей, тоже заданы жестко, и также ничто не мешает передать их параметром (*).
3. Цепочка построения подзапроса, InnerSQL — параметризированный текст sql запроса (с токенами).
4. ApplyParameters() подставляет в параметры значения.
5. Reset() сбрасывает все параметры.

b) Соединение с основной таблицей ‘CatalogItem’:

$SQL = Gdn::SQL();
$SQL
    ->Select('ci.Name, ci.Description, ci.Price, ci.Image')
    ->From('CatalogItem ci');
// Трюк с пустым префиксом.
$Prefix = $SQL->Database->DatabasePrefix;
$SQL->Database->DatabasePrefix = '';
$SQL->Join("($LozalizedTable) lt", 'lt.RowID = ci.CatalogItemID', 'left');
$SQL->Database->DatabasePrefix = $Prefix;
$SQL
    ->Select('lt.Name as LocalizedName')
    ->Select('lt.Description as LocalizedDescription');

В дополнительных полях LocalizedName и LocalizedDescription мы получаем наш перевод, NULL если перевода нет. Обратите внимание! Что перед JOIN-ом нужно применить трюк с пустым префиксом таблиц, иначе метод Gdn_SQLDriver::MapAliases() добавит префикс и подзапрос получится с синтаксической ошибкой вида:

... Prefix_(select ... )

4. Финал

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

protected function JoinWithSql($Sql, $On, $Type = 'left') {
    // Трюк с пустым префиксом, инача SqlBuilder выдаст ошибку.
    $Prefix = $this->SQL->Database->DatabasePrefix;
    $this->SQL->Database->DatabasePrefix = '';
    $this->SQL->Join($Sql, $On, $Type);
    $this->SQL->Database->DatabasePrefix = $Prefix;
}

protected function GetLocalizedSqlTable($Fields) {
    $LanguageCode = LocaleLanguageCode();
    if (is_string($Fields)) $Fields = explode(',', $Fields); // (*)
    foreach ($Fields as $Field) 
        $this->SQL->Select("if(Field = '$Field', Value, Null)", 
            'group_concat', $Field);
    $InnerSQL = $this->SQL
        ->Select('RowID')
        ->From('Localization')
        ->Where('Language', $LanguageCode)
        ->Where('Name', $this->Name)
        ->GroupBy('RowID')
        ->GetSelect();
    $InnerSQL = $this->SQL->ApplyParameters($InnerSQL);
    $this->SQL->Reset();
    return $InnerSQL;
}

И потом где-нибудь в public методах можно использовать:

$LozalizedTable = $this->GetLocalizedSqlTable('Name, Description'); // (*)
...
$this->JoinWithSql("($LozalizedTable) lt", 'lt.RowID = ci.CatalogItemID', 'left');
$SQL
    ->Select('lt.Name as LocalizedName')
    ->Select('lt.Description as LocalizedDescription');

Методы выше получает дополнительные поля наряду с основными, если это не нужно, то можно сделать так:

$SQL->Select('lt.Name, ci.Name', 'ifnull', 'Name');
$SQL->Select('lt.Description, ci.Description', 'ifnull', 'Description');

Если нет перевода, то значение поля берется из основной таблицы.
Заметьте, что $this->GetLocalizedSqlTable() начинает новую цепочку построения запроса, так что убедитесь что до этого вызова $SQL «чистый», иначе все параметры попадут в $InnerSQL.

5. Прозводительность

Прозводительность конечно зависит от основного запроса. Explain говорит что DERIVED Localization таблица будет просматриваться вся (type = ALL), но я ему не верю. Если сделать explain отдельно по дополнительному запросу, то:

select_type: SIMPLE
        table: Localization
         type: ref
possible_keys: Name
          key: Name
      key_len: 152
          ref: const
        Extra: Using where; Using filesort

На тестовой табличке с ~ 2 миллионами строк запрос выполняется менее чем 0.01 секунды, так что с производительностью тут все нормально.

Update: Индекс используется. При игнорировании индекса, запрос занял 0.5 секунды.

Ссылки

EAV Model:
http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm
http://en.wikipedia.org/wiki/Entity-attribute-value_model
Pivot tables (crosstabs):
http://www.artfulsoftware.com/infotree/queries.php#78
http://en.wikibooks.org/wiki/Programming:MySQL/Pivot_table
MySQL ifnull function:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

Добавить комментарий