Рекомендации
При разработке и использовании хранимых процедур учитывайте следующие моменты:
•В запросах select к таблицам рекомендуется указывать параметр with(nolock). Это позволяет избежать блокировок таблиц во время чтения данных и сохранить производительность системы.
•Псевдонимы (alias) колонок, возвращаемых функциями SQL, не должны совпадать с именами существующих колонок таблиц БД.
•Для проверки прав доступа пользователя к задачам в запросе может использоваться JOIN с таблицей UserTaskPermissions, в которой хранятся права на задачи, полученные пользователями всеми возможными в "Первой Форме" способами.
Основные таблицы БД "Первая Форма", которые используются при составлении запросов, и особенности обращения к ДП различных типов описаны здесь.
Примеры хранимых процедур для иерархий приведены в разделе с описанием иерархий.
Вызов веб-сервисов с помощью SQL
В данном разделе представлены основные процедуры заданий, их параметры и краткое описание, а также некоторые примеры кода. Все значения параметров в примерах могут изменяться в зависимости от бизнес-задач.
Для работы веб-сервисов "Первой Формы" необходимо выдать права роли, в которую входит служебный пользователь D10TaskUser.
Также надо выдать права на создание экземпляров объектов OLE-автоматизации в пакетах Transact-SQL.
Процедура |
Параметры и краткое описание |
||
|---|---|---|---|
tc_ws_addAcceptant |
Добавление подписчика в текущую запрошенную динамическую подпись. Параметры: •@TaskSignatureId (int) — номер запрошенной подписи, обязателен, •@InitUserID (int) — ID пользователя который добавил акцептанта, обязателен, •@UserID (int) — ID пользователя, которого добавляют в акцептанты, обязателен , •@WriteComment(bit) — нужно ли писать комментарий, необязателен, •@SendEmail(bit) — отправлять ли письмо, необязателен. |
||
tc_ws_addComment |
Добавление комментария в задачу. Параметры: •@UserID (int) — ID пользователя, от имени которого генерируется комментарий, •@TaskID (int) — ID задачи, в которой пишут комментарий, обязателен, •@Comment nvarchar(8000) — текст комментария, обязателен. |
||
tc_ws_addNewPerformer |
Назначение нового исполнителя задачи. Параметры: •@UserID (int) — ID пользователя, является исполнителем, •@TaskID (int) — ID задачи, в которой назначается новый исполнитель, •@SessionUserID (int) — ID пользователя, от имени которого выполняется действие, обязателен. |
||
tc_ws_AddNewSignatureToTask |
Запрос подписи в задачу. Параметры: •@TaskID (int) — ID задачи, в которой запрашивают подпись, обязателен, •@SignatureID (int) — ID подписи, которую запрашивают, обязателен, •@UserID (int) — ID пользователя, который запрашивает подпись, обязателен, •@IsDynamic (bit) — динамическая ли подпись, необязателен, •@Reason (varchar(max)) — причина запроса подписи, необязателен. |
||
tc_ws_addSubscriber |
Добавление пользователя в подписчики задачи. Параметры: •@UserID (int) — ID пользователя, которого надо подписать к задачи, обязателен, •@TaskID (int) — ID задачи, к которой надо подписать, обязателен, •@IsSubscribeChildTask (bit) — подписывание к дочерним задачам, необязателен, •@SubscribeType (int) — тип подписки. Значения: o1 — Добавление вручную o2 — Акцептант o3 — Скопирован из родительской o4 — Консультант\Исполнитель\Заказчик\Автоподписка o5 — Не определено o6 — Прочее\Автоматизация o7 — Участник подписанной группы •@SessionUserId (int) — ID пользователя, от имени которого выполняется действие, обязателен. |
||
tc_ws_addSubscriberMailed |
Добавление пользователя в подписчики с уведомлением по почте (на данный момент на почту уведомления не отправляются, фактически работает как dbo.tc_ws_addSubscriber). Параметры: •@UserID (int) — ID пользователя, которого надо подписать к задачи, обязателен, •@TaskID (int) — ID задачи, к которой надо подписать, обязателен, •@IsSubscribeChildTask (bit) — подписывание к дочерним задачам, необязателен, •@ActingUserID — ID пользователя, который подписывает, обязателен. |
||
tc_ws_AddUserComment |
Отправление комментарий конкретному пользователю. Параметры: •@TaskID (int) — ID задачи, в которой надо написать комментарий, обязателен, •@CommentRecipients (nvarchar(4000)) — список через точку с запятой логин (на английском) получателей, обязателен, •@UserID (int) — ID пользователя отправителя, обязателен, •@Text (nvarchar(8000)) — текст комментария, обязателен, •@NeedSubscribe (bit) — нужно ли подписываться на задачу, необязателен |
||
tc_ws_AddUserCommentWithSms |
Отправление пользователю комментарий через смс (требуется подключение к системе Clickatell). Параметры: •@TaskID (int) — ID задачи, в которой надо написать комментарий, обязателен, •@CommentRecipients (nvarchar(8000)) — список через точку с запятой логин (на английском) получателей, обязателен, •@UserID (int) — ID пользователя отправителя, обязателен, •@Text (nvarchar(8000)) — текст комментария , обязателен, •@NeedSubscribe (bit) — нужно ли подписываться на задачу, необязателен |
||
tc_ws_changeTaskStep |
Совершение перехода в задаче. Параметры: •@StepID (int) — номер перехода который надо сделать, обязателен, •@Userid (int) — ID пользователя, который делает переход, обязателен, •@Taskid (int) — ID задачи, в которой надо сделать переход, обязателен •@InitiatorUserId (int) — ID пользователя-инициатора перехода, |
||
tc_ws_DeletePerformerFromTask |
Удаление исполнителя из задачи. Параметры: •@Taskid (int) — ID задачи, в которой надо удалить исполнителя, обязателен, •@Userid (int) — ID пользователя, которого удаляют из исполнителей, обязателен, •@SessionUserId (int) — ID пользователя, от имени которого выполняется действие, обязателен. |
||
tc_ws_DeleteRequestedSignature |
Удаление запрошенной подписи. Параметры: •@UserID (int) — ID пользователя, который удаляет запрошенную подпись, обязателен, •@TaskSignatureID (int) — номер запрошенной подписи, обязателен |
||
tc_ws_DeleteTask |
Удаление задачи. Параметры: •@UserID (int) — ID пользователя, который удаляет задачу, обязателен, •@TaskID (int) — ID задачи, которая удаляется, обязателен |
||
tc_ws_LinkOrgUnitWithGroup |
Установление связи между орг. единицей и группой пользователей. Параметры: •@OrgUnitID (int) — ID орг. единицы, обязателен, •@GroupID (int) — ID группы, обязателен |
||
tc_ws_moveTask |
Перемещение задачи в категорию. Параметры: •@UserID (int) — ID пользователя, от имени которого будет сделано перемещение, обязателен, •@TaskID (int) — ID перемещаемой задачи, обязателен, •@SubcatID (int) — ID категории, в которую будет перемещена задача, обязателен |
||
tc_ws_newTask |
Создание новой задачи. Параметры: •@UserID (int) — IDd пользователя, который будет являться заказчиком, обязателен, •@Task (nvarchar(8000)) — текст задачи, обязателен, •@OrderedTime (datetime) — срок задачи, обязателен (в зависимости от настройки категории), •@Category (int) — ID категории, в которую будет поставлена задача, обязателен, •@Comment (nvarchar(8000)) — первый комментарий к задаче, не обязателен, •@ExtParamStr (nvarchar(8000)) — служебная строка для заполнения доп. параметров, описание см. ниже, обязателен (в зависимости от настройки обязательных ДП при постановке в категории), •@PerfID (int) — ID исполнителя при постановке, обязателен (в зависимости от настройки категории), •@PriorityID (int) — ID приоритета задачи, не обязателен, •@D3TaskID — не используется, •@ParentID (int) — ID родительской задачи, не обязателен, •@UserNick (nvarchar(20))- ник пользователя, ставящего задачу, не обязателен (может использоваться вместо @UserID), •@Remind (bit) — напоминание о приближении срока задачи, не обязателен, •@PropagateSubscribers (bit) — подписать подписчиков родительской задачи, не обязателен, •@PropagateFiles (boolean) — перенести файлы в дочернюю задачу, •@TaskStartTime (datetime) — постановка даты начала работ, не обязателен
|
||
tc_ws_PeriodicExecution |
Явное выполнение периодических заданий из очереди |
||
tc_ws_updateExtParamsInTasks |
Обновление дополнительных параметров в задаче. Параметры: •@TaskID (int) — id задачи, обязателен, •@Str (nvarchar(8000)) — служебная строка для заполнения доп. параметров, описание см. ниже, обязателен, •@SessionUserID (int) — id пользователя, от имени которого выполняется действие, обязателен, •@Comment (bit) — если равен 1, то пишется системный комментарий об изменении ДП, если 0 — не пишется; по умолчанию равен 1; не обязателен |
||
tc_ws_updateOrderedTime |
Обновление срока задачи. Параметры: •@TaskID (int) — ID задачи, обязателен, •@OrderedTime (datetime) — срок задачи, обязателен |
||
tc_ws_updateTaskDescription |
Обновление текста задачи. Параметры: •@TaskID (int) — ID задачи, обязателен, •@Description (nvarchar(max)) — новый текст, обязателен |
||
Служебная строка |
|||
Заполняет/изменяет значение ДП. Например, для одного ДП строка имеет вид: '#nномер#vзначение::' где номер — ID ДП, а значение — значение, которое должно быть записано в ДП. Для двух ДП (и более — аналогично): '#nномер1#vзначение1::#nномер2#vзначение2::'
Пример кода: Пусть в задаче 15678 от имени пользователя "Диспетчер задач" нужно обновить ДП с ID=167 (тип "Текст"), записать в него значение 'Иван Иванович', а в ДП с ID=168 (тип "ДатаВремя") записать текущую дату. Строку можно задекларировать следующим образом: set @str = '#n167#vИван Иванович::#n168#v'+ cast (getdate() as varchar) + '::' ID пользователя "Диспетчер задач" получим следующим образом: select @UserID = SystemRobotID from Settings where CustomerID = 1 Наконец, сделаем вызов процедуры: exec tc_ws_UpdateExtParamsInTasks
|
|||
Вызов опубликованного смарт-пакета
Если вы работаете с системой на уровне БД, меняяя данные в таблицах напрямую через SQL-запросы, периодически вы можете сталкиваться с тем, что внесенные в БД изменения применяются не сразу. Чтобы обойти это, можно использовать механизм публикаций.
Чтобы сделать http-запрос к опубликованному смарт-пакету, можно использовать следующий скрипт.
Вызов задания
Некоторые хранимые процедуры нужно вызывать регулярно по расписанию. Часто для этого используются смарт-расписания. Но если процедура обрабатывает большие объемы данных (например, при интеграции с внешними системами), то смарт-расписание будет прерываться по тайм-ауту. Чтобы избежать этого, такие хранимые процедуры можно вызывать как задания (джобы).
Получение входящих параметров
Часто набор входящих параметров передается в формате XML (например, при использовании фильтров в иерархиях, виджетах, планировщиках и т.п.). Для обработки таких входящих параметров на MS SQL удобно использовать процедуру crm_repGetParams, поставляемую вместе с "Первой Формой".
Имя параметра передается как атрибут Name. Для периода времени передаются две даты — From и To, для остальных параметров передается значение Value.
Получение значений входящих параметров из формата XML с помощью процедуры crm_repGetParams может выглядеть следующим образом.
Для PG процедура crm_repGetParams не используется. Пример передачи параметра для PG.
Для проверки прав доступа пользователей на задачи рекомендуется использовать стандартные системные табличные функции fn_UserTaskPermissions и fn_CheckUserTaskPermissions.
Использование функции dbo.fn_CheckUserTaskPermissions
Пример вывода списка задач, которые доступны пользователю из определенной или любой категории.
Использование функции dbo.fn_UserTaskPermissions()
Пример быстрого вывода списка задач, которые доступны пользователю из определенной категории.
Вместо объединения UserTaskPermissions и таблицы Tasks эффективнее использовать функцию проверки прав пользователя dbo.fn_UserTaskPermissions(). Она имеет два аргумента: @UserID (ID пользователя) и @SubcatID (ID категории). Функция возвращает все записи указанной категории, доступные указанному пользователю, и содержит все поля, которые есть в таблице Tasks.
Объединение результата dbo.fn_UserTaskPermissions() с Tasks
Поскольку все поля можно брать из результата функции, в большинстве случаев нет необходимости дополнительно связывать в запросе результат dbo.fn_UserTaskPermissions() с таблицей Tasks — это создает лишнюю нагрузку на процессор.
Если же в вашем запросе невозможно обойтись без связи с таблицей Tasks, а простой join с dbo.fn_UserTaskPermissions() начинает заметно тормозить даже на небольшой выборке, попробуйте следующие варианты:
1. Вместо прямого join с dbo.ваша_таблица по TaskID используйте конструкцию.
Это заставит сервер применить проверку прав после отбора по dbo.ваша_таблица.
2. Добавьте в самый конец оператора option (force order).
Использование dbo.fn_UserTaskPermissions() для нескольких пользователей и/или категорий
Если вам нужно получить задачи, доступные N пользователям (например, с учетом заместителей), и/или задачи из M категорий, функцию dbo.fn_UserTaskPermissions() необходимо вызывать N*M раз.
Пример 4 (применим если не используются поля из таблицы Tasks)
Использование dbo.fn_UserTaskPermissions() для неопределенного количества категорий
Если количество категорий заранее не известно, тогда применение функции dbo.fn_UserTaskPermissions() становится не настолько эффективным, но все же возможным. В этом случае рекомендуется провести эксперимент: в качестве второго параметра передать null и проанализировать снижение производительности. Если снижение существенно, стоит отказаться от использования функции dbo.fn_UserTaskPermissions() и использовать join с представлением UserTaskPermissions.
Доступные настройки
События, на которые можно назначить выполнение хранимых процедур в интерфейсе системы:
•При постановке задачи: Категории и процессы — Категории — Настройки — опция Процедура при постановке (SQL) (Устаревшее, не используется)
•При сохранении задачи: Категории и процессы — Категории — Настройки — опция Процедура при сохранении (Устаревшее, не используется)
•При переходе по маршруту: Категории и процессы — Категории — Маршрут — Настройки перехода — Настройки — опция Процедура при переходе (SQL)
•При подписании на переходе: Категории и процессы — Категории — Маршрут — Настройки перехода — Подписи на переходе — опция Процедура
•При завершении обработки писем: Категории и процессы — Категории — Почта — Настройки перехода — Настройки почтового ящика — опция Процедура при завершении обработки (SQL)
•При изменении статуса: Категории и процессы — Статусы — Настройки статуса — опция Выполняется встроенная процедура
Процедура выполняется после наступления события. В случае возникновения ошибки в ходе работы процедуры пользователю будет выведено соответствующее сообщение.
Диагностика работы СУБД
С помощью процедуры sp_Blitz можно проанализировать производительность работы СУБД и выявить "узкие места".
Каждый вызов настраивает результат проверки через параметр @OutputType или фильтрует выводимые данные по уровню важности с помощью параметров @IgnorePrioritiesBelow и @IgnorePrioritiesAbove.
Примеры вызовов:
•EXEC sp_Blitz;
Запускает полную диагностику и выводит список всех найденных проблем, предупреждений и рекомендаций в виде таблицы.
•EXEC sp_Blitz @IgnorePrioritiesAbove = 50;
Показывает только критические проблемы с высоким приоритетом (до 50 включительно), скрывая менее важные сообщения.
•EXEC sp_Blitz @IgnorePrioritiesBelow = 200;
Фильтрует вывод, оставляя только информационные сообщения и рекомендации с приоритетом от 200 и выше.
•EXEC sp_Blitz @IgnorePrioritiesBelow = 50, @IgnorePrioritiesAbove = 150;
Выводит проблемы в определённом диапазоне приоритетов (от 50 до 150), исключая как очень критические, так и чисто информационные записи.
•EXEC sp_Blitz @OutputType = 'COUNT';
Не выводит детальный список, а возвращает только общее количество найденных проблем.
•EXEC sp_Blitz @OutputType = 'XML';
Формирует результат проверки в структурированном XML-формате.
•EXEC sp_Blitz @OutputType = 'MARKDOWN';
Генерирует отчет в формате Markdown.
•EXEC sp_Blitz @OutputType = 'CSV';
Возвращает данные в формате CSV (значения, разделённые запятыми).
•EXEC sp_Blitz @OutputType = 'SCHEMA';
Возвращает не сами данные диагностики, а метаинформацию: схему (структуру) выходной таблицы с именами и типами столбцов.
Полезные ссылки
Администрирование хранимых процедур
