JSON Server на MS SQL 2016
В MS SQL 2016 появилась возможность работать с json.
И появилась идея сделать аналог популярного json-server (JSON-Server as a Fake REST API in Frontend Development), только с иными возможностями, не как Fake JSON-Server, а как полнофункциональный, описывающий динамически все модели/сущности, логику и данные для отображения.
Идея проста, процедура DB получает json с описанием что делать, и возвращает результат в виде json.
Простой REST сервер имеет всего один POST метод вызывающий процедуру DB. Это не очень правильно все делать одним post методом, но нет пока время все красиво сделать :)
Все действия (создание/правка/удаление моделей/данных/логики) это post запросы.
Все модели, логика - являются данными, никакого хардкода :)
Сделал все это дело за три выходных дня, и код далек от совершенства :)
Данный вариант простой и можно дорабатывать до бесконечности.
Создадим таблицы Product, Order, History.
Product - список продуктов
Order - список заказанных продуктов
History - при добавлении заказа данные о нем помещаются в таблицу History
Создание таблицы Product:
{
"Operation": "ChangeModel",
"Action": "ins",
"Data":
{
"TableName": "Product",
"Columns":
[
{"Column": "Name", "Value": "nvarchar(50)"},
{"Column": "Price", "Value": "int"}
]
}
}
Создание таблицы Order:
{
"Operation": "ChangeModel",
"Action": "ins",
"Data":
{
"TableName": "Order",
"Columns":
[
{"Column": "Product", "Value": "int"},
{"Column": "Qty", "Value": "int"},
{"Column": "Price", "Value": "int"}
]
}
}
Создание таблицы History:
{
"Operation": "ChangeModel",
"Action": "ins",
"Data":
{
"TableName": "History",
"Columns":
[
{"Column": "Product", "Value": "int"},
{"Column": "Qty", "Value": "int"},
{"Column": "Summa", "Value": "int"}
]
}
}
Добавление логики (при добавлении заказа данные о нем помещаются в таблицу History):
{
"Operation": "ChangeLogic",
"Action": "ins",
"Name": "Add to History",
"ActionTable": "Order",
"TriggerTable": "History",
"ActionType": "ins",
"TriggerActionType": "ins",
"TriggerLinkExp": "",
"Data":
{
"Columns":
[
{"Column": "Product", "Exp": "[Order].[OrderId]"},
{"Column": "Qty", "Exp": "[Order].[Qty]"},
{"Column": "Summa", "Exp": "SUM([Order].[Qty] * [Order].[Price])"}
]
}
}
Все, структура/таблицы/модели и логика созданы.
Добавляет данные в таблицу Product:
{
"Operation": "ChangeData",
"Action": "ins",
"Data":
{
"TableName": "Product",
"Columns":
[
{"Column": "Name", "Value": "Product 1"},
{"Column": "Price", "Value": "5"}
]
}
}
Добавляет данные в таблицу Order:
{
"Operation": "ChangeData",
"Action": "ins",
"Data":
{
"TableName": "Order",
"Columns":
[
{"Column": "Product", "Value": "1"},
{"Column": "Qty", "Value": "10"},
{"Column": "Price", "Value": "5"}
]
}
}
Все, данные добавили.
Теперь создаем модель получения результата (все Order c Qty > 0):
{
"Operation": "GetDataModel",
"Name": "Get Orders",
"Tables":
[
{
"TableName": "Order",
"Columns": [ "Product", "Qty", "Price" ],
"Criteria": "Qty > 0"
}
]
}
Теперь создаем модель получения результата (все History):
{
"Operation": "GetDataModel",
"Name": "Get History",
"Tables":
[
{
"TableName": "History",
"Columns": [ "Product", "Qty", "Summa" ]
}
]
}
Получить результат (Order):
{
"Operation": "GetData",
"Name": "Get Orders",
"Tables":
[
{
"TableName": "Order"
}
]
}
Результат:
[{"Order":[{"Product":1,"Qty":10,"Price":5}]}]
Получить результат (History):
"Operation": "GetData",
"Name": "Get History",
"Tables":
[
{
"TableName": "History"
}
]
}
Результат:
[{"History":[{"Product":1,"Qty":10,"Summa":50}]}]
Старт сервера:
1) Выполнить скрипт (только для MSSQL 2016): script.sql
2) Прописать в server.js CONNECTION (DB по умолчанию 'Test')
3) npm install
4) node server.js
http://localhost:8081/api/data
package.json:
{
"dependencies":
{
"body-parser": "^1.17.2",
"edge": "^7.10.1",
"edge-sql": "^0.1.2",
"edge-sql-plus": "0.0.10",
"express": "^4.15.3"
}
}
var CONNECTION = "Server=localhost\\SQL2016;Database=Test;Integrated Security=True";
var PORT = 8081;
var express = require('express');
var bodyParser = require("body-parser");
var app = express();
var edge = require('edge');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
var server = app.listen(PORT,
function() {
var port = server.address().port
console.log("RESTful API server started on port", port);
});
app.post('/api/data', function (req, res) {
executeQuery(req, res);
});
var executeQuery = function (req, res) {
var str = JSON.stringify(req.body).toString();
str = str.replace(/\\r|\\n|\\t/g, '').replace(/\\"/g, '"').substring(2);
var execProc = edge.func('sql-plus', {
source: 'exec RunAction',
connectionString: CONNECTION
});
execProc({json: str},
function(err, result) {
if (err) {
console.log(err);
res.send(err);
}
console.log(result);
res.send(result);
});
}
script.sql:
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Test')
CREATE DATABASE [Test]
GO
USE Test
GO
-------------------------------------------------------------------------------------------------------------------------------------------------
-- DROP ALL
-------------------------------------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS [dbo].[RunAction]
DROP PROCEDURE IF EXISTS [dbo].[ExecTrigger]
DROP PROCEDURE IF EXISTS [dbo].[UpdateModel]
DROP PROCEDURE IF EXISTS [dbo].[UpdateData]
DROP PROCEDURE IF EXISTS [dbo].[GetData]
DROP PROCEDURE IF EXISTS [dbo].[ExecAction]
DROP FUNCTION IF EXISTS [dbo].[fGetExp]
DROP TYPE IF EXISTS [dbo].[TriggerColumn]
DROP TYPE IF EXISTS [dbo].[TableColumn]
DROP TYPE IF EXISTS [dbo].[TableData]
DROP TABLE IF EXISTS [dbo].[t_trigger_item]
DROP TABLE IF EXISTS [dbo].[t_trigger]
DROP TABLE IF EXISTS [dbo].[t_getdata]
GO
-------------------------------------------------------------------------------------------------------------------------------------------------
-- CREATE TYPES
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TYPE [dbo].[TriggerColumn] AS TABLE(
trigger_table varchar(50) not null,
trigger_column varchar(50) not null,
trigger_exp nvarchar(max) not null
)
GO
CREATE TYPE [dbo].[TableColumn] AS TABLE(
column_name varchar(50) not null,
column_val nvarchar(max) null,
column_type int not null
)
GO
CREATE TYPE [dbo].[TableData] AS TABLE(
table_name varchar(50) not null,
table_columns nvarchar(max) not null,
criteria nvarchar(4000) null
)
GO
-------------------------------------------------------------------------------------------------------------------------------------------------
-- CREATE TABLES
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[t_trigger]
(
trigger_id int NOT NULL IDENTITY (1, 1),
name varchar(50) NOT NULL,
action_table varchar(50) NOT NULL,
trigger_table varchar(50) NOT NULL,
action_type nchar(3) NOT NULL,
trigger_action_type nchar(3) NOT NULL,
trigger_link_exp nvarchar(4000) NULL,
) ON [PRIMARY]
GO
ALTER TABLE dbo.t_trigger ADD CONSTRAINT PK_trigger_id PRIMARY KEY CLUSTERED (trigger_id)
GO
CREATE TABLE [dbo].[t_trigger_item]
(
trigger_item_id int NOT NULL IDENTITY (1, 1),
trigger_id int NOT NULL,
trigger_column varchar(50) NOT NULL,
trigger_exp nvarchar(4000) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.t_trigger_item ADD CONSTRAINT PK_trigger_item_id PRIMARY KEY CLUSTERED (trigger_item_id)
GO
ALTER TABLE dbo.t_trigger_item ADD CONSTRAINT FK_trigger_item_trigger FOREIGN KEY (trigger_id) REFERENCES dbo.t_trigger (trigger_id)
GO
CREATE TABLE [dbo].[t_getdata]
(
getdata_id int NOT NULL IDENTITY (1, 1),
getdata_name nvarchar(50) NOT NULL,
getdata_json nvarchar(max) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.t_getdata ADD CONSTRAINT PK_t_getdata_id PRIMARY KEY CLUSTERED (getdata_id)
GO
-------------------------------------------------------------------------------------------------------------------------------------------------
-- CREATE FUNCTIONS
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fGetExp](@str nvarchar(4000))
RETURNS @table_exp TABLE (exp_key nvarchar(50), exp_val nvarchar(500), exp_str nvarchar(4000), exp_tables nvarchar(500), exp_sql nvarchar(4000), exp_sql_total nvarchar(max))
AS
BEGIN
declare @exp int = 1
declare @begin int
declare @mid int
declare @end int
declare @exp_val_temp VARCHAR(500)
while(CHARINDEX('SUM(', @str) > 0 OR CHARINDEX('COUNT(', @str) > 0)
begin
set @begin = (select min(i) from( select CHARINDEX('SUM(', @str) i union select CHARINDEX('COUNT(', @str) as i) a where i > 0)
set @end = CHARINDEX(')', substring(@str, @begin, LEN(@str) - @begin + 1))
insert into @table_exp (exp_key, exp_val)
values (cast(@exp as varchar) + 'EXP', substring(@str, @begin, @end))
set @str = stuff(@str, @begin, @end, cast(@exp as varchar) + 'EXP')
set @exp = @exp + 1
end
while(CHARINDEX('[', @str) > 0)
begin
set @begin = CHARINDEX('[', @str)
set @end = CHARINDEX(']', REPLACE(@str, '].[', '...')) - @begin + 1
insert into @table_exp (exp_key, exp_val)
values (cast(@exp as varchar) + 'EXP', substring(@str, @begin, @end))
set @str = stuff(@str, @begin, @end, cast(@exp as varchar) + 'EXP')
set @exp = @exp + 1
end
DECLARE @exp_key VARCHAR(50), @exp_val VARCHAR(500), @exp_tables VARCHAR(500)
DECLARE cursor1 CURSOR LOCAL FOR
SELECT exp_key, exp_val
FROM @table_exp
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @exp_key, @exp_val
WHILE @@FETCH_STATUS=0
BEGIN
while(CHARINDEX('[', @exp_val) > 0)
begin
set @begin = CHARINDEX('[', @exp_val)
set @end = CHARINDEX('].[', @exp_val) - @begin + 1
if not exists(select 1 from @table_exp where exp_key = @exp_key and exp_tables != substring(@exp_val, @begin, @end))
update @table_exp set exp_tables = substring(@exp_val, @begin, @end)
where exp_key = @exp_key
set @exp_val = stuff(@exp_val, @begin, @end + 2, '')
end
FETCH NEXT FROM cursor1 INTO @exp_key, @exp_val
END
CLOSE cursor1
DEALLOCATE cursor1
declare @exp_sql_total nvarchar(max) = @str;
DECLARE cursor2 CURSOR LOCAL FOR
SELECT exp_key, exp_val, exp_tables
FROM @table_exp
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @exp_key, @exp_val, @exp_tables
WHILE @@FETCH_STATUS=0
BEGIN
declare @sqt_ nvarchar(4000) = '(SELECT ' + @exp_val + ' FROM ' + @exp_tables + ' WHERE ' + REPLACE(@exp_tables,']','Id]') + ' = @ID)'
update @table_exp set exp_sql = @sqt_
where exp_key = @exp_key
set @exp_sql_total = replace(@exp_sql_total, @exp_key, @sqt_)
FETCH NEXT FROM cursor2 INTO @exp_key, @exp_val, @exp_tables
END
CLOSE cursor2
DEALLOCATE cursor2
update @table_exp set exp_str = @str, exp_sql_total = @exp_sql_total
return;
END
GO
-------------------------------------------------------------------------------------------------------------------------------------------------
-- CREATE PROCEDURES
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[ExecTrigger]
@id_action int,
@trigger_link_exp nvarchar(4000),
@trigger_type char(3),
@trigger_table varchar(50),
@TriggerColumns TriggerColumn READONLY
as
begin
SET NOCOUNT ON
declare @exp_sql_total nvarchar(max)
declare @sql nvarchar(max)
declare @cols nvarchar(max) = ''
declare @vals nvarchar(max) = ''
if @trigger_type = 'ins'
begin
set @sql = 'INSERT INTO ' + @trigger_table +'(#cols) VALUES (#vals)'
end
if @trigger_type = 'upd'
begin
set @sql = 'UPDATE ' + @trigger_table +' SET #cols'
end
if @trigger_type = 'del'
begin
set @sql = 'DELETE FROM ' + @trigger_table
end
DECLARE @trigger_column varchar(50), @trigger_exp nvarchar(4000)
DECLARE cursor1 CURSOR LOCAL FOR
SELECT trigger_column, trigger_exp
FROM @TriggerColumns
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @trigger_column, @trigger_exp
WHILE @@FETCH_STATUS=0
BEGIN
select top 1 @exp_sql_total = exp_sql_total
from dbo.fGetExp(@trigger_exp)
if @trigger_type = 'ins'
begin
if len(@cols) > 0 set @cols = @cols + ','
set @cols = @cols + @trigger_column
if len(@vals) > 0 set @vals = @vals + ','
set @vals = @vals + @exp_sql_total
end
if @trigger_type = 'upd'
begin
if len(@cols) > 0 set @cols = @cols + ','
set @cols = @cols + @trigger_column + ' = (' + @exp_sql_total + ')'
end
FETCH NEXT FROM cursor1 INTO @trigger_column, @trigger_exp
END
CLOSE cursor1
DEALLOCATE cursor1
if @trigger_type = 'ins'
begin
set @sql = REPLACE(@sql, '#cols', @cols)
set @sql = REPLACE(@sql, '#vals', @vals)
EXEC sp_executeSql @sql, N'@ID INT', @id_action
end
if @trigger_type = 'upd'
begin
set @sql = REPLACE(@sql, '#cols', @cols)
IF LEN(ISNULL(@trigger_link_exp,'')) > 0
begin
set @sql = @sql + ' WHERE ' + @trigger_link_exp
end
EXEC sp_executeSql @sql, N'@ID INT', @id_action
end
if @trigger_type = 'del'
begin
IF LEN(ISNULL(@trigger_link_exp,'')) > 0
begin
set @sql = @sql + ' WHERE ' + @trigger_link_exp
end
EXEC sp_executeSql @sql, N'@ID INT', @id_action
end
end
GO
CREATE PROCEDURE [dbo].[UpdateModel]
@Action char(3),
@TableName varchar(50),
@TableColumn TableColumn readonly
as
begin
SET NOCOUNT ON
declare @sql nvarchar(max) = ''
if (@Action = 'ins')
begin
set @sql = @sql + '[' + @TableName + 'Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,'
select @sql = @sql + column_name + ' ' + column_val + ','
from @TableColumn
set @sql = 'DROP TABLE IF EXISTS [dbo].[' + @TableName + ']; CREATE TABLE [dbo].[' + @TableName + '](' + @sql + ');'
EXEC sp_executeSql @sql
end
if (@Action = 'upd')
begin
select @sql = @sql + 'ALTER TABLE ' + @TableName + case when COL_LENGTH(@TableName, column_name) IS NULL then ' ADD ' else ' ALTER COLUMN ' end + column_name + ' ' + column_val + '; '
from @TableColumn
EXEC sp_executeSql @sql
end
if (@Action = 'del')
begin
set @sql = 'DROP TABLE IF EXISTS [dbo].[' + @TableName + '];'
EXEC sp_executeSql @sql
end
end
GO
CREATE PROCEDURE [dbo].[UpdateData]
@Action char(3),
@TableName varchar(50),
@TableColumn TableColumn readonly,
@CurrentId int output
as
begin
SET NOCOUNT ON
declare @sql nvarchar(max) = ''
declare @sqlCol nvarchar(max) = ''
declare @sqlVal nvarchar(max) = ''
if (@Action = 'ins')
begin
select @sqlCol = @sqlCol + column_name + ',', @sqlVal = @sqlVal + '''' + column_val + ''','
from @TableColumn
set @sqlCol = SUBSTRING(@sqlCol, 1, LEN(@sqlCol)-1)
set @sqlVal = SUBSTRING(@sqlVal, 1, LEN(@sqlVal)-1)
set @sql = 'INSERT INTO [dbo].[' + @TableName + '](' + @sqlCol + ') VALUES(' + @sqlVal + '); SET @CurrentId = scope_identity();'
EXEC sp_executeSql @sql, N'@CurrentId INT OUTPUT', @CurrentId OUTPUT
end
if (@Action = 'upd')
begin
select @sqlCol = @sqlCol + column_name + ' = ''' + column_val + ''','
from @TableColumn
set @sqlCol = SUBSTRING(@sqlCol, 1, LEN(@sqlCol)-1)
set @sql = 'UPDATE [dbo].[' + @TableName + '] SET ' + @sqlCol + ' WHERE _Id = ' + CAST(ISNULL(@CurrentId, 0) as varchar) + ';'
EXEC sp_executeSql @sql
end
if (@Action = 'del')
begin
set @sql = 'DELETE FROM [dbo].[' + @TableName + '] WHERE _Id = ' + CAST(ISNULL(@CurrentId, 0) as varchar) + ';'
EXEC sp_executeSql @sql
end
end
GO
CREATE PROCEDURE [dbo].[GetData]
@TableData TableData readonly,
@TableDataCriteria TableData readonly
as
begin
SET NOCOUNT ON
declare @sql nvarchar(max) = ''
declare @sqlTable varchar(50) = ''
declare @sqlCol nvarchar(max) = ''
declare @criteria nvarchar(4000)
declare @criteriaAdd nvarchar(4000)
declare @json nvarchar(max) = ''
declare @res nvarchar(max)
select @sqlTable = table_name, @sqlCol = table_columns, @criteria = criteria
from @TableData
DECLARE cursor1 CURSOR LOCAL FOR
select table_name, table_columns, criteria
from @TableData
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @sqlTable, @sqlCol, @criteria
WHILE @@FETCH_STATUS=0
BEGIN
select @criteriaAdd = criteria
from @TableDataCriteria where table_name = @sqlTable
set @sqlCol = REPLACE(REPLACE(REPLACE(@sqlCol, '"', ''), '[', ''), ']', '')
set @sql = 'SELECT @res = (SELECT ' + @sqlCol + ' FROM [dbo].[' + @sqlTable + ']'
if LEN(ISNULL(@CriteriaAdd,'')) > 0
begin
if LEN(ISNULL(@Criteria,'')) = 0
set @Criteria = @CriteriaAdd
else
set @Criteria = @Criteria + ' AND ' + @CriteriaAdd
end
if LEN(ISNULL(@Criteria,'')) > 0
set @sql = @sql + ' WHERE ' + @Criteria
set @sql = @sql + ' FOR JSON PATH, ROOT(''' + @sqlTable + '''))'
EXEC sp_executeSql @sql, N'@res nvarchar(max) output', @res output
if len(@json) > 0 set @json = @json + ', '
set @json = @json + @res
FETCH NEXT FROM cursor1 INTO @sqlTable, @sqlCol, @criteria
END
CLOSE cursor1
DEALLOCATE cursor1
SELECT '[' + @json + ']' AS ReturnData
end
GO
CREATE PROCEDURE [dbo].[ExecAction]
@Action char(3),
@TableName varchar(50),
@TableColumn TableColumn readonly,
@CurrentId int
as
begin
SET NOCOUNT ON
DECLARE @TriggerColumns TriggerColumn
DECLARE @trigger_id INT, @trigger_table VARCHAR(50), @trigger_action_type CHAR(3), @trigger_link_exp NVARCHAR(4000)
DECLARE cursor1 CURSOR LOCAL FOR
select t.trigger_id, t.trigger_table, t.trigger_action_type, t.trigger_link_exp
from t_trigger t
where t.action_table = @TableName and t.action_type = @Action
order by t.trigger_id
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @trigger_id, @trigger_table, @trigger_action_type, @trigger_link_exp
WHILE @@FETCH_STATUS=0
BEGIN
delete from @TriggerColumns
insert into @TriggerColumns (trigger_table, trigger_column, trigger_exp)
select t.trigger_table, ti.trigger_column, ti.trigger_exp
from t_trigger_item ti
join t_trigger t on t.trigger_id = ti.trigger_id
where t.trigger_id = @trigger_id
exec ExecTrigger @CurrentId, @trigger_link_exp, @trigger_action_type, @trigger_table, @TriggerColumns
FETCH NEXT FROM cursor1 INTO @trigger_id, @trigger_table, @trigger_action_type, @trigger_link_exp
END
CLOSE cursor1
DEALLOCATE cursor1
end
GO
CREATE PROCEDURE [dbo].[RunAction]
@json nvarchar(max)
as
begin
SET NOCOUNT ON
declare @Operation varchar(20)
declare @TableName varchar(50)
declare @Name nvarchar(50)
declare @Action char(3)
declare @CurrentId int
declare @TableColumn TableColumn
declare @TableData TableData
declare @TableDataCriteria TableData
declare @TriggerColumn TriggerColumn
declare @ActionTable varchar(50)
declare @TriggerTable varchar(50)
declare @ActionType char(3)
declare @TriggerActionType char(3)
declare @TriggerLinkExp nvarchar(4000)
SELECT @Operation = Operation, @Action = Action, @Name = Name,
@ActionTable = ActionTable, @TriggerTable = TriggerTable, @ActionType = ActionType, @TriggerActionType = TriggerActionType, @TriggerLinkExp = TriggerLinkExp
FROM OPENJSON(@json, N'$')
WITH (
Operation VARCHAR(20) N'$.Operation',
Action VARCHAR(3) N'$.Action',
Name NVARCHAR(50) N'$.Name',
ActionTable VARCHAR(50) N'$.ActionTable',
TriggerTable VARCHAR(50) N'$.TriggerTable',
ActionType VARCHAR(3) N'$.ActionType',
TriggerActionType VARCHAR(3) N'$.TriggerActionType',
TriggerLinkExp NVARCHAR(4000) N'$.TriggerLinkExp'
)
if (@Operation in ('ChangeModel', 'ChangeData'))
begin
SELECT @TableName = TableName, @CurrentId = CurrentId
FROM OPENJSON(@json, N'$.Data')
WITH (
TableName VARCHAR(50) N'$.TableName',
CurrentId INT N'$.CurrentId'
)
insert into @TableColumn(column_name, column_val, column_type)
SELECT ColumnName, ColumnValue, 0
FROM OPENJSON(@json, N'$.Data.Columns')
WITH (
ColumnName VARCHAR(50) N'$.Column',
ColumnValue NVARCHAR(max) N'$.Value'
)
end
if (@Operation = 'ChangeLogic')
begin
insert into @TriggerColumn(trigger_table, trigger_column, trigger_exp)
SELECT @TriggerTable, ColumnName, ColumnExp
FROM OPENJSON(@json, N'$.Data.Columns')
WITH (
ColumnName VARCHAR(50) N'$.Column',
ColumnExp NVARCHAR(max) N'$.Exp'
)
insert into t_trigger (name, action_table, action_type, trigger_table, trigger_action_type, trigger_link_exp)
values (@Name, @ActionTable, @ActionType, @TriggerTable, @TriggerActionType, @TriggerLinkExp)
declare @trigger_id int = scope_identity()
insert into t_trigger_item (trigger_id, trigger_column, trigger_exp)
select @trigger_id, trigger_column, trigger_exp
from @TriggerColumn
end
if (@Operation in ('GetData'))
begin
insert into @TableDataCriteria(table_name, table_columns, criteria)
SELECT json_value(value, '$.TableName'), '', json_value(value, '$.Criteria')
FROM OPENJSON(@json, N'$.Tables') t
select @json = getdata_json
from t_getdata where getdata_name = @Name
insert into @TableData(table_name, table_columns, criteria)
SELECT json_value(value, '$.TableName'), json_query(value, '$.Columns'), json_value(value, '$.Criteria')
FROM OPENJSON(@json, N'$.Tables') t
end
if (@Operation in ('GetDataModel'))
begin
insert into @TableData(table_name, table_columns, criteria)
SELECT json_value(value, '$.TableName'), json_query(value, '$.Columns'), json_value(value, '$.Criteria')
FROM OPENJSON(@json, N'$.Tables') t
end
if (@Operation = 'ChangeModel')
begin
exec UpdateModel @Action, @TableName, @TableColumn
end
if (@Operation = 'ChangeData')
begin
exec UpdateData @Action, @TableName, @TableColumn, @CurrentId output
exec ExecAction @Action, @TableName, @TableColumn, @CurrentId
-- select @CurrentId -- RETURN ID
end
if (@Operation = 'GetDataModel')
begin
exec GetData @TableData
if not exists(select 1 from t_getdata where getdata_name = @Name)
insert into t_getdata(getdata_name, getdata_json) values (@Name, @json)
else
update t_getdata set getdata_json = @json where getdata_name = @Name
end
if (@Operation = 'GetData')
begin
exec GetData @TableData, @TableDataCriteria
end
end
GO
