Миграция на PostgreSQL¶

План анализа объектов T-SQL перед переходом на PG SQL¶

1. Проанализировать таблицу SmartExpressions

Пример:

SELECT ID as ИД_смарта
 ,se.Name as Название
    ,se.SubcatID as ИД_Категории
 ,sub.Description as Категория
    ,iif(IsFilter=1,'фильтр','выражение') as Тип_смарта
    ,TSqlContent
FROM SmartExpressions as se
 LEFT JOIN Subcategories as sub ON sub.SubcatID = se.SubcatID
WHERE TSqlContent is not NULL
 AND IsDeleted = 0

2. Проанализировать таблицу SmartScripts

Пример:

SELECT ss.SubcatId as ИД_Категории
 ,sub.Description as Категория
 ,ss.Id
    ,ss.Description as Название
    ,ScriptCode
FROM SmartScripts as ss
 LEFT JOIN Subcategories as sub ON ss.subcatid = sub.SubcatID

3. Проанализировать наличие отчетов FR: Администрирование — Бизнес-логика — Отчеты. Необходимо оценить количество строк без отметки «Системный» и количество строк, у которых в колонке "Блок" есть значение.

4. Проанализировать наличие нестандартных хранимых процедур и функций для портальных блоков: Администрирование — Интерфейс — Конструктор порталов. Необходимо проверить наличие нестандартных виджетов "График", "Таблица" и других типов, использующих хранимые процедуры или функции.

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

Для этого необходимо подсчитать количество имеющихся объектов нужных типов, выгрузить список и сравнить его с эталонным перечнем

Пример:

-- Посчитать количество объектов нужных типов

/*использовать USE databasename, чтобы видеть объекты не из master*/
USE databasename;
SELECT type, count(name) as 'Кол-во'
FROM dbo.sysobjects
WHERE type in ( 'P'        /*процедуры*/ ,'FN'        /*скалярные функции*/ ,'TF'        /*табличная функция*/ ,'V'/*представление*/ )
GROUP BY type

-- Получить список объектов нужных типов

/*использовать USE databasename, чтобы видеть объекты не из master*/
USE databasename;
SELECT
name, type
FROM dbo.sysobjects
WHERE type in
(
'P'        /*процедуры*/
,'FN'        /*скалярные функции*/
,'TF'        /*табличная функция*/
,'V'        /*представление*/
)
ORDER BY type, name

-- При необходимости — выгрузка кода ХП и функций

/*использовать USE databasename, чтобы видеть объекты не из master*/
USE databasename;
SELECT routine_name, routine_type
 /* реальная длина кода, не усеченная до 4000, как в столбце routine_definition */
,len(object_definition(object_id(routine_name))) as Реальная_длина_кода
 /* раскомментировать, если нужно взять код процедуры */
--,object_definition(object_id(routine_name)) as real_routine_definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_type in ('PROCEDURE','FUNCTION')
ORDER BY routine_type, routine_name

6. Проанализировать наличие SQL Server Agent -- Jobs, относящихся к конкретной БД, т.к. они могут использовать нестандартную хранимую процедуру.

Унификация TSQL — PGSQL в смартах¶

MS SQL (TSQL)

PostgreSQL (PGSQL)

Что делать. Есть ли универсальный вариант для работы в любом SQL

Сложность замены

Частота использования

Идентификаторы, транзакции и пр.

При обращениях к объектам можно использовать квадратные скобки : [table].[field]

Название объекта, заключенное в [] даст ошибку синтаксиса, нужно либо удалить скобки, либо использовать двойные кавычки (если в названии объекта присутствуют недопустимые символы): "table"."field"

Удалить []. Не использовать экранирование идентификаторов с помощью [] без необходимости. Использовать общий способ именования объектов, чтобы [] или «» не требовались.

Легко, но трудоемко

Постоянно

WITH (NOLOCK) или (NOLOCK), а также его аналог SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Аналога нет

Удалить

Легко

Постоянно

Типы данных

Использование строк ограниченной переменной длины, типа: varchar(max), nvarchar(n)

varchar(n)

Указать значение вместо "max" для varchar(max);

Исправить nvarchar на varchar (скорее всего, есть другой способ).

Использовать общий тип varchar(n)

Зависит от контекста

Средне

Работа с переменными

DECLARE @myvar INT SET @myvar = 5

SELECT * FROM somewhere WHERE something = @myvar

Самый простой из рекомендуемых способов — использовать CTE:

WITH vars AS (SELECT 5 AS myvar) SELECT * FROM somewhere,vars WHERE something = vars.myvar;

Пример для одной переменной:

WITH decl AS (SELECT '5' AS my_var)

SELECT my_var from decl

Пример для нескольких переменных:

WITH my_var (var1, var2) as (

  values (5, 'foo')

)

SELECT *

FROM table1, my_var

/* т.е. используется CROSS JOIN */

WHERE something_from_table1 = var1

  OR something_else = var2;

Сложно

Часто

Операторы, условия, циклы

SELECT TOP N ...

SELECT .... LIMIT N¶

TOP удалить, LIMIT добавить.

Использовать общий LIMIT.

Легко

Редко

ISNULL()

COALESCE()

Заменить ISNULL на COALESCE.

Использовать общий COALESCE.

Легко

Средне

IIF()

CASE WHEN...THEN

Заменить.

Использовать общий CASE.

Cредне

Редко

WHILE ... BEGIN ... END

WHILE ... LOOP... END LOOP

Добавить операторы LOOP, ограничивающие "тело цикла"

Легко

Редко

IF логическое-выражение BEGIN операторы

END

IF логическое-выражение

THEN операторы

END IF

При использовании более сложной конструкции вида:

IF-BEGIN-END-ELSE-BEGIN-END

в PG SQL может использоваться конструкция вида:

if-then-elseif-then-else-end if;

Cредне

Функции работы со строками

Конкатенация строк:

или CONCAT()

|| или CONCAT

Использовать общий CONCAT

Легко, но трудоемко

Постоянно

Определение длины строки:

LEN()

LENGTH()

Заменить

Легко

Редко

Определение расположения последовательности в строке:

CHARINDEX()

STRPOS()

Заменить

Легко

Редко

Вставка одной строки в другую с заданной позиции:

STUFF или STRING_AGG

STRING_AGG

Использовать общий STRING_AGG

Cредне

Редко

Дата, время, форматирование

Получение текущей даты и времени (локальное время):

GetDate()

CURRENT_TIMESTAMP

Заменить.

Использовать общий CURRENT_TIMESTAMP

Легко

Средне

Приращение даты-времени:

DATEADD

Пример:

select DATEADD(day,1,getdate())

функции:

interval или make_interval()

Пример:

select CURRENT_TIMESTAMP + interval '1 day';

select CURRENT_TIMESTAMP + make_interval(days => 1);

Переписать конструкцию.

Средне

Особенности написания SQL на PostgreSQL¶

При написании SQL на PostgreSQL нельзя использовать контекстные параметры smart-запросов SQL внутри конструкции DO $$ ... END $$;

Пример того, как нужно использовать контекстные параметры

;WITH myconstants (var0) as (
      SELECT
       LanguageID
      FROM
       Users
      WHERE
       UserID = @eventParam0
)

  SELECT
      CASE WHEN COALESCE(ld.Description, '') = '' THEN
          REPLACE(REPLACE(t.Description, '<p>', ''), '</p>', '')
      ELSE
          REPLACE(REPLACE(ld.Description, '<p>', ''), '</p>', '')
      END
  FROM
       Tasks t
  LEFT JOIN (
        SELECT Description
        FROM Tasks_AllLocalized (@ContextID,  (select var0 from myconstants) )
        LIMIT 1
   ) ld on true
  WHERE
       TaskID = @ContextID;

Пример того, как не нужно использовать контекстные параметры

/*
DO $$
DECLARE
   Lang INTEGER;
BEGIN

   SELECT
       LanguageID
   INTO Lang
   FROM
       Users
   WHERE
       UserID = @eventParam0;

   CREATE TEMPORARY TABLE temp_ld AS
   SELECT
       Description
   FROM
       Tasks_AllLocalized (@ContextID,  Lang )
   LIMIT 1;

       END $$;

           SELECT
       CASE WHEN COALESCE(ld.Description, '') = '' THEN
           REPLACE(REPLACE(t.Description, '<p>', ''), '</p>', '')
       ELSE
           REPLACE(REPLACE(ld.Description, '<p>', ''), '</p>', '')
       END
   FROM
       Tasks t
   LEFT JOIN temp_ld ld ON TRUE
   WHERE
       TaskID = @ContextID;
*/

Миграция на PostgreSQL¶

План анализа объектов T-SQL перед переходом на PG SQL¶

Унификация TSQL — PGSQL в смартах¶

SELECT .... LIMIT N¶

Особенности написания SQL на PostgreSQL¶

Рекомендации¶