Сохранение данных с использованием JSON

Эта статья описывает, как сохранять данные с динамическими колонками, используя хранимые процедуры с JSON-параметрами.

Поддерживаются пять встроенных параметров:

@json_values_f1 (или @JsonValuesF1)
@json_values_f2 (или @JsonValuesF2)
@json_changes_f1 (или @JsonChangesF1)
@json_changes_f2 (или @JsonChangesF2)
@json_changes_f3 (или @JsonChangesF3)

@json_values_f1 и @json_values_f2 получают все значения строки: @json_values_f1 как массив и @json_values_f2 как объект.

@json_changes_f1 и @json_changes_f2 фиксируют все изменения: @json_changes_f1 как массивы и @json_changes_f2 как объекты.

@json_changes_f3 аналогичен @json_changes_f2, но всегда возвращает полный набор, включая колонки с пустыми значениями.

"f1", "f2" и "f3" — это индексы формата.

Также полезны следующие встроенные параметры:

@json_columns (или @JsonColumns) — получает актуальный набор имен колонок.
@table_name (или @TableName) — получает имя объекта запроса.
@edit_action (или @EditAction) — получает значения "INSERT", "UPDATE", "DELETE" или "MERGE" в зависимости от операции.

Настройка

Существует три подхода к привязке процедур сохранения изменений:

Использование таблицы xls.objects
Использование представления списка запросов
Использование суффиксов

Ниже приведен пример настройки в таблице xls.objects:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
	s02	view_cashbook	VIEW		s02.view_cashbook_insert	s02.view_cashbook_update	s02.view_cashbook_delete
	s02	usp_cashbook	PROCEDURE			s02.usp_cashbook_update

Пример настройки для представления списка запросов:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_PROCEDURE	UPDATE_PROCEDURE	DELETE_PROCEDURE	PROCEDURE_TYPE
	s02	view_cashbook	VIEW		s02.view_cashbook_insert	s02.view_cashbook_update	s02.view_cashbook_delete
	s02	usp_cashbook	PROCEDURE			s02.usp_cashbook_update

Эти настройки не обязательны, так как продукты SaveToDB автоматически привязывают процедуры по суффиксам _insert, _update и _delete.

Таким образом, вы можете использовать эту технику даже без установленного SaveToDB Framework.

Вторые строки конфигурации используют единственную процедуру UPDATE. Используйте этот подход для процедур с параметрами json_changes, или когда для всех операций используется одна и та же процедура (см. ниже).

Детали реализации

SaveToDB и DBEdit выполняют заданные процедуры, передавая JSON в параметры.

DBGate и ODataDB выполняют процедуры на стороне сервера, формируя JSON для параметров на основе команд POST, PUT, DELETE.

Примеры

Ниже представлены детальные примеры для каждого случая.

Пример для @json_values_f1
Пример для @json_values_f2
Использование универсальной процедуры обновления строк
Пример для @json_changes_f1
Пример для @json_changes_f2
Использование универсальной процедуры обновления таблиц

Пример для @json_values_f1

Пример команд для процедур insert, update и delete:

EXEC [s24].[view_cashbook_json_values_f1_delete] @id = 21;

EXEC [s24].[view_cashbook_json_values_f1_update]
    @json_columns = N'["id","date","account_id","item_id","company_id","debit","credit"]'
    , @json_values_f1 = N'[1,"2022-01-10",1,1,1,200000,null]';

EXEC [s24].[view_cashbook_json_values_f1_insert]
    @json_columns = N'["id","date","account_id","item_id","company_id","debit","credit"]'
    , @json_values_f1 = N'[21,"2022-03-31",1,2,8,null,100000]';

Пример хранимых процедур Microsoft SQL Server, которые обновляют таблицу, используя значения JSON.

Обратите внимание, что процедура delete использует обычный параметр @id.

CREATE PROCEDURE [s24].[view_cashbook_json_values_f1_insert]
    @json_columns nvarchar(max)
    , @json_values_f1 nvarchar(max)
AS
BEGIN

SET @json_values_f1 = '[' + @json_values_f1 + ']'    -- Fix for OPENJSON top-level array

INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@json_values_f1) WITH (
        [id] int '$[0]'
        , [date] date '$[1]'
        , [account_id] int '$[2]'
        , [item_id] int '$[3]'
        , [company_id] int '$[4]'
        , [debit] float '$[5]'
        , [credit] float '$[6]'
    ) t2;

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f1_update]
    @json_columns nvarchar(max)
    , @json_values_f1 nvarchar(max)
AS
BEGIN

SET @json_values_f1 = '[' + @json_values_f1 + ']'  -- Fix for OPENJSON top-level array

UPDATE s24.cashbook
SET
    [date] = t2.date
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN OPENJSON(@json_values_f1) WITH (
        [id] int '$[0]'
        , [date] date '$[1]'
        , [account_id] int '$[2]'
        , [item_id] int '$[3]'
        , [company_id] int '$[4]'
        , [debit] float '$[5]'
        , [credit] float '$[6]'
    ) t2 ON t2.id = t.id

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f1_delete]
    @id int = NULL
AS
BEGIN

DELETE FROM s24.json_test WHERE id = @id

END
GO

Пример для @json_values_f2

Пример команд для процедур insert, update и delete:

EXEC [s24].[view_cashbook_json_values_f2_delete] @id = 21;

EXEC [s24].[view_cashbook_json_values_f2_update]
    @json_values_f2 = N'{"id":1,"date":"2022-01-10","account_id":1,"item_id":1,"company_id":1,"debit":200000,"credit":null}';

EXEC [s24].[view_cashbook_json_values_f2_insert]
    @json_values_f2 = N'{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}';

В отличие от @json_values_f1, @json_values_f2 получает значения в виде объектов.

Пример хранимых процедур Microsoft SQL Server, которые обновляют таблицу, используя значения JSON.

CREATE PROCEDURE [s24].[view_cashbook_json_values_f2_insert]
    @json_values_f2 nvarchar(max)
AS
BEGIN

INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@json_values_f2) WITH (
        [id] int '$."id"'
        , [date] date '$."date"'
        , [account_id] int '$."account_id"'
        , [item_id] int '$."item_id"'
        , [company_id] int '$."company_id"'
        , [debit] float '$."debit"'
        , [credit] float '$."credit"'
    ) t2

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f2_update]
    @json_values_f2 nvarchar(max)
AS
BEGIN

UPDATE s24.cashbook
SET
    [date] = t2.date
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN OPENJSON(@json_values_f2) WITH (
        [id] int '$."id"'
        , [date] date '$."date"'
        , [account_id] int '$."account_id"'
        , [item_id] int '$."item_id"'
        , [company_id] int '$."company_id"'
        , [debit] float '$."debit"'
        , [credit] float '$."credit"'
    ) t2 ON t2.id = t.id

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f2_delete]
    @id int = NULL
AS
BEGIN

DELETE FROM s24.cashbook WHERE id = @id

END
GO

Использование универсальной процедуры обновления строк

Примеры выше демонстрируют простой случай, когда структура обновляемой таблицы известна.

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

По ссылке приведен пример такой процедуры для Microsoft SQL Server:

https://dbgate.savetodb.com/api/mssql-241/en-us/s24.xl_update_generic_row/$definition

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

Для вызова процедуры можно создать процедуру-обертку или SQL-код и использовать их в поле UPDATE_OBJECT таблицы конфигурации.

CREATE PROCEDURE [s24].[view_cashbook_json_generic_row_update]
    @id int = NULL
    , @table_name nvarchar(255) = NULL
    , @edit_action nvarchar(6) = NULL
    , @json_values_f2 nvarchar(max) = NULL
AS
BEGIN

EXEC s24.xl_update_generic_row @id, '[s24].[cashbook]', @edit_action, @json_values_f2

END

Так как используется единая процедура для операций вставки, обновления и удаления, следует указать процедуру только в поле UPDATE_OBJECT.

Примеры генерируемых команд:

EXEC [s24].[view_cashbook_json_generic_row_update]
    @id = 21
    , @table_name = N'[s24].[view_cashbook_json_generic_row]'
    , @edit_action = N'DELETE'
    , @json_values_f2 = N'{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}';

EXEC [s24].[view_cashbook_json_generic_row_update]
    @id = 1
    , @table_name = N'[s24].[view_cashbook_json_generic_row]'
    , @edit_action = N'UPDATE'
    , @json_values_f2 = N'{"id":1,"date":"2022-01-10","account_id":1,"item_id":1,"company_id":1,"debit":200000,"credit":null}';

EXEC [s24].[view_cashbook_json_generic_row_update]
    @id = 21
    , @table_name = N'[s24].[view_cashbook_json_generic_row]'
    , @edit_action = N'INSERT'
    , @json_values_f2 = N'{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}';

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

Именно поэтому требуется использование процедуры-обертки или SQL-кода, чтобы вызвать универсальную процедуру с правильным именем целевой таблицы.

Пример для @json_changes_f1

Пример команд сохранения изменений для процедуры с параметром @json_changes_f1:

EXEC [s24].[view_cashbook_json_changes_f1_update]
    @id = NULL
    , @json_changes_f1 = N'{
        "table_name":"[s24].[view_cashbook_json_changes_f1]",
        "actions":{
            "insert":{
                "action":"insert"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"]
                , "rows":[[21,"2022-03-31",1,2,8,null,100000]]
                }
            ,"update":{
                "action":"update"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"]
                , "rows":[[1,"2022-01-10",1,1,1,200000,null]]
                }
            ,"delete":{
                "action":"delete"
                , "columns":["id"]
                , "rows":[[21]]
                }
            }
        }';

SaveToDB и DBEdit генерируют единственную команду для всех изменений и выполняют единственный вызов процедуры.

Это значительно увеличивает общую производительность.

DBGate и ODataDB для таких объектов создают обычный API, получают стандартные запросы POST, PUT и DELETE в едином пакетном запросе и выполняют процедуру с параметрами JSON на стороне сервера.

Пример процедуры SQL Server, которая использует JSON-массивы значений для обновления таблицы:

CREATE PROCEDURE [s24].[view_cashbook_json_changes_f1_update]
    @id int = NULL
    , @json_changes_f1 nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON

DECLARE @insert nvarchar(max),  @update nvarchar(max), @delete nvarchar(max)

SELECT
    @insert = t2.[insert]
    , @update = t2.[update]
    , @delete = t2.[delete]
FROM
    OPENJSON(@json_changes_f1) WITH (
        actions nvarchar(max) AS json
    ) t1
    CROSS APPLY OPENJSON(t1.actions) WITH (
        [insert] nvarchar(max) '$.insert' AS json
        , [update] nvarchar(max) '$.update' AS json
        , [delete] nvarchar(max) '$.delete' AS json
    ) t2

IF @insert IS NOT NULL
INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@insert) WITH (
        [rows] nvarchar(max) '$.rows' AS json
    ) t1
    CROSS APPLY OPENJSON(t1.[rows]) WITH (
        [id] int '$[0]'
        , [date] date '$[1]'
        , [account_id] int '$[2]'
        , [item_id] int '$[3]'
        , [company_id] int '$[4]'
        , [debit] float '$[5]'
        , [credit] float '$[6]'
    ) t2;

IF @update IS NOT NULL
UPDATE s24.cashbook
SET
    [date] = t2.[date]
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.id AS id
            , t2.[date] AS [date]
            , t2.account_id AS account_id
            , t2.item_id AS item_id
            , t2.company_id AS company_id
            , t2.debit AS debit
            , t2.credit AS credit
        FROM
            OPENJSON(@update) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$[0]'
                , [date] date '$[1]'
                , [account_id] int '$[2]'
                , [item_id] int '$[3]'
                , [company_id] int '$[4]'
                , [debit] float '$[5]'
                , [credit] float '$[6]'
            ) t2
    ) t2 ON t2.id = t.id

IF @delete IS NOT NULL
DELETE FROM s24.cashbook
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.[id] AS [id]
        FROM
            OPENJSON(@delete) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$[0]'
            ) t2
    ) t2 ON t2.[id] = t.[id]

END

Пример для @json_changes_f2

Пример команд для процедуры с параметром @json_changes_f2:

EXEC [s24].[view_cashbook_json_changes_f2_update]
     @id = NULL
     , @json_changes_f2 = N'{
        "table_name":"[s24].[view_cashbook_json_changes_f2]"
        , "actions":{
            "insert":{
                "action":"insert"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"]
                , "rows":[{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}]
                }
            ,"update":{
                "action":"update"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"],
                , "rows":[{"id":1,"date":"2022-01-10","account_id":1,"item_id":1,"company_id":1,"debit":200000,"credit":null}]
                }
            , "delete":{
                "action":"delete"
                , "columns":["id"]
                , "rows":[{"id":21}]
                }
            }
        }';

В отличие от @json_changes_f1, @json_changes_f2 получает значения в виде объектов.

Пример хранимой процедуры SQL Server, которая использует значения объектов JSON для обновления таблицы:

CREATE PROCEDURE [s24].[view_cashbook_json_changes_f2_update]
    @id int = NULL
    , @json_changes_f2 nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON

DECLARE @insert nvarchar(max),  @update nvarchar(max), @delete nvarchar(max)

SELECT
    @insert = t2.[insert]
    , @update = t2.[update]
    , @delete = t2.[delete]
FROM
    OPENJSON(@json_changes_f2) WITH (
        actions nvarchar(max) AS json
    ) t1
    CROSS APPLY OPENJSON(t1.actions) WITH (
        [insert] nvarchar(max) '$.insert' AS json
        , [update] nvarchar(max) '$.update' AS json
        , [delete] nvarchar(max) '$.delete' AS json
    ) t2

IF @insert IS NOT NULL
INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@insert) WITH (
        [rows] nvarchar(max) '$.rows' AS json
    ) t1
    CROSS APPLY OPENJSON(t1.[rows]) WITH (
        [id] int '$."id"'
        , [date] date '$."date"'
        , [account_id] int '$."account_id"'
        , [item_id] int '$."item_id"'
        , [company_id] int '$."company_id"'
        , [debit] float '$."debit"'
        , [credit] float '$."credit"'
    ) t2;

IF @update IS NOT NULL
UPDATE s24.cashbook
SET
    [date] = t2.[date]
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.id AS id
            , t2.[date] AS [date]
            , t2.account_id AS account_id
            , t2.item_id AS item_id
            , t2.company_id AS company_id
            , t2.debit AS debit
            , t2.credit AS credit
        FROM
            OPENJSON(@update) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$."id"'
                , [date] date '$."date"'
                , [account_id] int '$."account_id"'
                , [item_id] int '$."item_id"'
                , [company_id] int '$."company_id"'
                , [debit] float '$."debit"'
                , [credit] float '$."credit"'
            ) t2
    ) t2 ON t2.id = t.id;

IF @delete IS NOT NULL
DELETE FROM s24.cashbook
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.[id] AS [id]
        FROM
            OPENJSON(@delete) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$."id"'
            ) t2
    ) t2 ON t2.id = t.id

END

Использование универсальной процедуры обновления таблиц

Примеры выше можно использовать, если структура таблицы данных известна.

Можно создать полностью универсальную процедуру для сохранения изменений в таблицу.

По ссылке приведен пример такой процедуры для Microsoft SQL Server:

https://dbgate.savetodb.com/api/mssql-241/en-us/s24.xl_update_generic_table/$definition

В дополнение к данным изменений, процедура получает имя целевой таблицы.

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

Для вызова таких процедур можно создать процедуру-обертку, представленную ниже, или использовать SQL-код в поле UPDATE_OBJECT таблицы конфигурации.

CREATE PROCEDURE [s24].[view_cashbook_json_generic_table_update]
    @id int = NULL
    , @table_name nvarchar(255) = NULL
    , @json_changes_f2 nvarchar(max) = NULL
AS
BEGIN

EXEC s24.xl_update_generic_table '[s24].[cashbook]', @json_changes_f2

END