DanilovAnatoly Apr 27 2009 at 08:24

Версионность данных в MySQL

3 min

2.1K

В 1С есть очень полезная фича — периодические реквизиты, с помощью них можно хранить не только значение, но и всю историю его изменения. Например, если мы храним значение курса бакса по отношению к рублю, то хранится не только актуальное значение, но и значение за вчера, позавчера и т.д.(период хранения может быть любой). В MySQL, к сожалению, такой фичи нет(наверное и не надо). Значит сделаем сами ;).

Рассмотрим на примере. Допустим у нас есть простейшая система заявок основанная на таблице TTroubleticket.

Таблица TTroubleticket
fkey	номер заявки
fcontact	контактная информация подавшего заявку
ftext	текст заявки

Кроме контактной информации и текста заявки необходимо хранить статус заявки со всей историей его изменений. Сразу напрашивается такое решение: хранить статус в другой таблице со всей историей изменений.

Таблица TStatusHistory
fkey
f_troubleticket	ссылка на запись в TTroubleticket
fvalue	значение
fsdate	дата присвоения значения

Тогда чтобы получить текущее значение нам надо выполнить следующий запрос:

    SET @f_troubleticket = 10;
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        (SELECT 
            fvalue 
         FROM 
            TStatusHistory 
         WHERE 
            f_troubleticket = tt.fkey 
         ORDER BY 
            fsdate DESC 
         LIMIT 0,1
        ) AS fstatus
    FROM 
        TTroubleticket AS tt
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1

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

Таблица TStatusHistory
fkey
f_troubleticket	ссылка на запись в TTroubleticket
fvalue	значение
fsdate	дата присвоения значения
fedate дата	завершения действия значения

Теперь чтобы получить значение на дату нам надо немного изменить запрос:

    
    SET @f_troubleticket = 10;
    SET @dt = 20090401000000; # 01 апреля 2009г 00:00
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        (SELECT 
            fvalue 
         FROM 
            TStatusHistory 
         WHERE 
            f_troubleticket = tt.fkey 
            AND @dt BETWEEN fsdate AND fedate 
         ORDER BY 
            fsdate DESC 
         LIMIT 0,1
        ) as fstatus
    FROM 
        TTroubleticket AS tt
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1

С этой сложностью справились. Но время идет, а система совершенствуется: нам понадобился такой параметр как тип заявки и тоже периодический. Фигня!-добавляем еще таблицу.

Таблица TTypeHistory
fkey
f_troubleticket	ссылка на запись в TTroubleticket
fvalue	значение
fsdate	дата присвоения значения
fedate	дата завершения действия значения

Зарос преобразуется:

    
    SET @f_troubleticket = 10;
    SET @dt = 20090401000000; # 01 апреля 2009г 00:00
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        (SELECT 
            fvalue 
          FROM 
            TStatusHistory 
          WHERE 
            f_troubleticket = tt.fkey 
            AND @dt BETWEEN fsdate AND fedate 
          ORDER BY 
            fsdate DESC 
          LIMIT 0,1
        ) AS fstatus,
        (SELECT 
            fvalue 
          FROM 
            TTypeHistory 
          WHERE 
            f_troubleticket = tt.fkey 
            AND @dt BETWEEN fsdate AND fedate 
          ORDER BY 
            fsdate DESC 
          LIMIT 0,1
        ) AS ftype
    FROM 
        TTroubleticket AS tt
    WHERE
        fkey = @f_troubleticket 
    LIMIT 0, 1

И со временем у нас может появиться куча дополнительных таблиц, т.е. система усложняется, а в этом нет ничего хорошего.
Усовершенствуем наш подход — не будем создавать отдельные таблицы для каждой периодической переменной, все периодические переменные будем хранить в одной таблице.

Таблица THistory
fkey
f_troubleticket	ссылка на запись в TTroubleticket
fstatus	статус
ftype	тип
fsdate дата	присвоения значения
fedate дата	завершения действия значения

Также, все актуальные значения будем хранить в основной таблице.

Таблица TTroubleticket
fkey	номер заявки
fcontact	контактная информация подавшего заявку
ftext	текст заявки
fstatus	текущей статус
ftype	текущий тип

И теперь когда нам понадобится получить актуальные значения, нам достаточно выполнить простой запрос:

    
    SET @f_troubleticket = 10;
    SELECT
        fkey, fcontact, ftext,
        fstatus,
        ftype
    FROM 
        TTroubleticket
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1

Ну, а если нам понадобится получить значения на дату, воспользуемся конструкцией LEFT JOIN:

    
    SET @f_troubleticket = 10;
    SET @dt = 20090401000000; # 01 апреля 2009г 00:00
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        his.fstatus,
        his.ftype
    FROM 
        TTroubleticket AS tt
            LEFT JOIN TTroubleticketHistory as his 
                ON his.f_troubleticket = tt.fkey 
                     AND @dt BETWEEN his.fsdate AND his.fedate
    WHERE
        tt.fkey = @f_troubleticket
    LIMIT 0, 1

Вот собственно и всё. Вроде бы получилось очень даже не сложное решение. А как вы храните периодические значения?

p.s. За идею спасибо А. Матвееву.

Tags:

mysql

Hubs:

Lumber room