Переносим задачи в файловые хранилища

Вступление

Обращаю внимание, что данный функционал еще не использовался на реальных проектах и не испытан в боевых условиях. Но поделиться с сообществом данными наработками считаю нужным. Каждый может допилить функционал под себя и это будет правильно, т.к. маршруты задач и процессы у всех разные.

Предыстория

Недавно, на одном из проектов заказчик озвучил обеспокоенность размером БД. Размер БД приближается к 2 ТБ. Хотя железо мощное, однако эту мощь все равно нельзя бесконечно наращивать. В связи с таким разрастанием БД, операции администрирования требуют все больше и больше ресурсов: время резервного копирования, размер бэкапа, время восстановления из бэкапа. Для этих операций есть определенные приемлемые пределы, выход за которые как раз может быть достигнут разрастанием БД. 

Проведенный анализ показал следующую картину:

Видно, что данные в таблице SBTask занимают 46% от общего размера БД, а это не много ни мало ~850 ГБ. Но что в этой таблице занимает столько места? Ответ: маршрут задачи - данные в поле WorkflowDescription, что подтверждает следующий запрос:

Таким образом, 778 ГБ (~91%) из этих 850 ГБ занимают данные маршрутов задач.

Что с этим делать

Рассуждения следующие:

1. Заказчик работает в ДИРЕКТУМе уже несколько лет. Поэтому очевидно, что большинство задач уже "отработали" свое и хранятся по сути для истории.

2. Неизвестно, данные из каких выполненных задач могут пригодиться в будущем. Поэтому удалять задачи нельзя.

Удалять задачи нельзя, но хранить их маршруты в БД накладно. Давайте перенесем их маршруты в файлы, а файлы положим в специальное ФХ!

Плюсы:

• Требования к железу в этом ФХ могут быть намного ниже, нежели в боевой БД, в первую очередь, в плане производительности.
• Создавать бэкап такого ФХ надо не каждый день/неделю, как в боевой БД, а по факту очередного "архивирования" маршрутов задач. Т.е., например, раз в 6-12 мес.
• Размер боевой БД существенно уменьшается: операции администрирования выполняются быстрее, запас свободного места на дорогостоящих дисках возрастает.
• Вся переписка по задаче, вложения - сохраняются.

Минусы чистки маршрута задачи:

• Очищаются параметры задачи. Может быть ситуация, когда параметры уже выполненной задачи могут использоваться другими задачами, которые еще не выполнены. 
• Задачи нельзя рестартовать/возобновить.
• Задачи, в состоянии "В работе", упадут с ошибкой.

Однако, минусы можно невелировать, если предусмотреть механизм экспорта маршрута задачи из файла обратно в БД автоматически или вручную. В данной статье описан механизм ручного восстановления маршрута задачи.

Также хочу обратить внимание, что размер маршрутов некоторых задач может быть меньше, чем создаваемый ЭД. Такие задачи нецелесообразно переносить в ФХ, т.к. выигрыша в занимаемом месте не будет.

Реализация

1. Создаем ТКЭД для ЭД, в которых будем хранить маршрут задачи:

Кнопка Восстановить - переносит маршрут задачи из файла обратно в БД, и удаляет файл.  Кстати, код можно запускать и при открытии файла, а не на кнопке, чтобы не выполнять лишних действий, таких как открытие карточки и нажатие кнопки.

Код:

  // Проверка на наличие схемы в задаче, если есть - то выдать предупреждение
  TaskID = Object.IntegerNum 
  Query = "
    SELECT WorkflowDescription
    FROM SBTask
    WHERE XRecID = " & TaskID
  Res = SQL(Query)
  if Assigned(Res)
    MsgBox = MessageBox("Перенос схемы ТМ из ЭД в задачу"; "Маршрут задачи содержит данные, начать перенос из файла?"; "Да|Нет"; "Нет"; "Нет")
  else
    MsgBox = MessageBox("Перенос схемы ТМ из ЭД в задачу"; "Перенести схему ТМ обратно в задачу ?"; "Да|Нет"; "Нет"; "Нет")  
  endif

  if MsgBox == "Да"
    // Выгрузить тело файла на диск 
    UnloadPath = GetTempFolder() & Object.ID & "_" & Object.IntegerNum & ".txt"
    if ФайлСуществует(UnloadPath)
      ФайлУдалить(UnloadPath)
    endif
    Object.Export(1; UnloadPath)
    Text = ФайлСчитать(UnloadPath)
    
    // Если есть текст - записать в схему ТМ
    if Assigned(Text)
      UpdateQuery = "
        UPDATE TOP(1) SBTask
        SET WorkflowDescription = cast(cast('" & Text & "' AS VARCHAR(MAX)) as VARBINARY(MAX))
        WHERE XRecID = " & TaskID
      SQL(UpdateQuery)  
      ShowMessage("Перенос выполнен") // убрать, если будет использоваться в невизуальном режиме
      
      // Удалить документ
      Object.LeaveEditMode
      EDocuments.DeleteByID(Object.ID)
    endif
  endif

2. Создаем запись в справочнике "Виды электронных документов", указываем в ней ФХ (естественно НЕ SQL-Server Storage), ТКЭД.

3. Механизм переноса маршрута реализован в виде сценария:

  // Диалог поиска задач
  InputData = InputDialogEx("*Типовой маршрут|*Дата старта|*Дата завершения";; "ReferenceM:ТМТ|Date|Date"; "Параметры поиска")
  
  TMCodes   = SubString(InputData; "|"; 1) // Коды выбранных ТМ (множественный выбор)
  StartDate = SubString(InputData; "|"; 2) // фильтр по дате старта задач 
  EndDate   = SubString(InputData; "|"; 3) // фильтр по дате завершения задач

  if CharPos("Отменить"; InputData) > 0 
    // Диалог отменен
    Exit()
  endif
  
  // Подстраховка. MinDateDiffConst это кол-во дней: из текущей даты вычитается это количество дней. В результате получаем дату, позже которой не чистим маршрут выполненных задач, 
  // т.к. есть вероятность, что от каких-то из этих задач могут зависеть другие, еще не выполненные задачи/процессы.
  MinDateDiffConst = 90 // 3 месяца 
  
  StartDateSQL = Format("CONVERT(DATETIME, CAST('%0:s' as DATETIME), 104)"; ArrayOf(StartDate)) 
  EndDateSQL   = Format("CONVERT(DATETIME, CAST('%0:s' as DATETIME), 104)"; ArrayOf(EndDate))
  
  // Проверка ограничения на дату завершения задач
  MinDateDiffText = Format("SELECT DATEDIFF(dd, %0:s, GETDATE())"; ArrayOf(EndDateSQL))
  if SQL(MinDateDiffText) <= MinDateDiffConst   
    Raise(CreateException(""; "Крайняя дата завершения задач должна быть не позднее: " & CR & "текущая дата - " & MinDateDiffConst & " дней."; ecWarning))
  endif     
  
  // Проверка на то, чтобы дата начала была меньше даты завершения  
  StartEndDateDiff = Format("SELECT DATEDIFF(dd, %0:s, %1:s)"; ArrayOf(StartDateSQL; EndDateSQL))
  if SQL(StartEndDateDiff) <= 0
    Raise(CreateException(""; "Дата начала должна быть меньше даты завершения"; ecWarning))
  endif

  // Условие по ТМ
  TMIDStr = ""
  foreach RefCode in CSubString(TMCodes; ";")
    TMIDStr = AddSubString(СпрРекв("ТМТ"; RefCode; SYSREQ_ID); TMIDStr; ", ")
  endforeach

  // Текст запроса поиска задач для "чистки"
  QueryText = Format("
    SELECT XRecID
    FROM SBTask
    WHERE StandardRoute IN (%0:s) 
      AND DATEADD(dd, 0, DATEDIFF(dd, 0, StartDate)) >= %1:s 
      AND DATEADD(dd, 0, DATEDIFF(dd, 0, EndDate)) <= %2:s
      AND State IN ('D') — только задачи в состоянии 'Выполнена'";
    ArrayOf(TMIDStr;
            StartDateSQL;
            EndDateSQL)) 

  Query = CreateQuery()
  Query.CommandText = QueryText
  Query.Open
  RecordCount = Query.RecordCount
  
  // Проверка на наличие задач, которые вернул запрос
  if RecordCount = 0
    Raise(CreateException(""; "Не найдено задач для обработки."; ecWarning))
  endif
  
  MsgBox = MessageBox("Подтверждение"; "Будет обработано " & RecordCount & " задач. Продолжить?"; "Да|Нет"; "Нет"; "Нет")
  if MsgBox <<>> "Да"
    Exit()
  endif  
  
  Time = Time() 
  Query.First
  
  // Прогресс-бар для наглядности
  Progress = CreateProgress(; RecordCount)
  Progress.Show
  i = 1 // Счетчик для прогресс бара
  EDocumentKindCode   = "Д000096" // Код вида электронного документа
  EDocumentEditorCode = "EDOTXT"  // Код приложения редактора
   
  while not Query.EOF
    Progress.Text = i & "/" & RecordCount
    
    TaskID = Query.XRecID 
    // Получить маршрут задачи в виде строки   
    WFDescQuery = "
      select cast(cast(WorkflowDescription AS VARBINARY(MAX)) as VARCHAR(MAX))       
      from SBTask
      WHERE XRecID = " & TaskID
    WFDescText = SQL(WFDescQuery)
    
    if Assigned(WFDescText)
      // Сохраняем полученный маршрут на диск
      TempPath = GetTempFolder()
      FileName = TempPath & "WFDescription_" & TaskID & ".txt"
      ФайлЗаписать(FileName; "Y"; WFDescText)
  
      // Создать ЭД, в тело которого перенести описание схемы маршрута задачи
      EDoc = EDocuments.CreateNewFromFile("WFDescription"; EDocumentKindCode; EDocumentEditorCode; FileName; FALSE)
      EDoc.IntegerNum = TaskID // ИД задачи
      // TODO: Можно еще назначить права участникам конкретной задачи, чтобы они могли перенести из этого ЭД маршрут обратно в задачу
      EDoc.Save
      
      // Вложить файл в задачу
      Task = Tasks.GetObjectByID(TaskID)
      AttachList = Task.GetAttachments(FALSE)
      AttachList.Add(EDoc.Info)
      Task.Save
      Task= nil
      EDoc = nil
  
      // Очистить маршрут задачи
      WFDescClearQuery = "
        UPDATE TOP(1) SBTask       
        SET WorkflowDescription = NULL
        FROM SBTask
        WHERE XRecID = " & TaskID
   
      SQL(WFDescClearQuery)      
    
  
      // Удалить временный файл с диска
      if ФайлСуществует(FileName)
        ФайлУдалить(FileName)  
      endif
    endif 

    Query.Next
    Progress.Next
    i = i + 1
  endwhile
  Progress.Hide
  ShowMessage(Time & " -> " & Time())

Пример использования

Использование функционала демонстрируется на тестовой БД.

1. Смотрим размер таблиц SQL-запросом:

DBCC UPDATEUSAGE (0)

DECLARE @DBSize int 
set @DBSize = (
select ltrim(str(pages.pages * 8192 / 1024.,15,0)) as data
    FROM (
        select 
            sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize,
            sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize
        from dbo.sysfiles
    ) sf,
    (
        select 
            sum(a.total_pages) as reservedpages,
            sum(a.used_pages) as usedpages,
            sum(
                CASE
                    — XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                    When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                    When a.type <> 1 Then a.used_pages
                    When p.index_id < 2 Then a.data_pages
                    Else 0
                END
            ) as pages
        from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
        left join sys.internal_tables it on p.object_id = it.object_id
    ) pages
)
create table #t(name varchar(255), row varchar(255), reserved varchar(255), data varchar(255), index_size varchar(255), unused varchar(255))

insert into #t
exec sp_msforeachtable N'exec sp_spaceused ''?''' 

select *
,CASE 
  WHEN CAST(REPLACE(data,' KB','') AS bigint) > 0
  THEN CAST(REPLACE(data,' KB','') AS bigint)*100/@DBSize + CAST(REPLACE(data,' KB','') AS bigint)*100/cast(@DBSize AS DECIMAL)%1
ELSE 0
END 
 AS [% от размера БД] 
from #t order by CONVERT(bigint,REPLACE(data,' KB','')) DESC
drop table #t

-- Размер данных в поле WorkflowDescription
select  SUM(CAST(datalength(WorkflowDescription) AS bigint))/1024 AS [Size In KB]
from SBTask 

Запрос, результат которого записывается в @DBSize, это часть данных, возвращаемое exec sp_spaceused. Но использование sp_spaceused не подходит, т.к. возвращает сразу 2 набора данных.

Результат запроса: 

2. Запускаем сценарий переноса маршрута в ФХ:

3. По окончании работы сценария размер таблиц следующий:

4. В задачи вложен документ с маршрутом:

Если на данный документ выдать права обычным пользователям и проинструктировать их, то они смогу самостоятельно "заливать" схему задачи обратной в БД и возобновлять/рестартовать эту задачу.