Pull to refresh

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

Reading time3 min
Views2.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:
Hubs:
Total votes 15: ↑11 and ↓4+7
Comments20

Articles