CREATE PROCEDURE [dbo].[AL_SAVE_METADATA]
@xml_string_id int,
@return_value varchar(max) OUTPUT,
@SQL_ERROR_CODE int OUTPUT,
@SQL_ERROR_DESCRIPTION varchar(max) OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE
@xml_string_metadata nvarchar(max),
@xml_metadata xml,
@uapmuser nvarchar(255),
@tableOp varchar(1),
@tableName varchar(30),
@tableDesc varchar(255),
@virtual int,
@inactive int,
@extended int,
@fieldName varchar(30),
@fieldDesc varchar(255),
@actualTableName varchar(30),
@actualFieldName varchar(30),
@fieldUnique int,
@fieldRequired int,
@primaryKey int,
@foreignKey int,
@dataType int,
@dataSize int,
@cnt int,
@field_already_exists$exception nvarchar(1000),
@table_already_exists$exception nvarchar(1000)
BEGIN TRY
SET @return_value = NULL
SET @SQL_ERROR_CODE = NULL
SET @SQL_ERROR_DESCRIPTION = NULL
SET @field_already_exists$exception =-20011
SET @table_already_exists$exception = -20015
SET @return_value = '<Root />'
SET @SQL_ERROR_CODE = 0
SET @SQL_ERROR_DESCRIPTION = NULL
SET @xml_string_metadata = NULL
SELECT @xml_string_metadata = ARG_XML_STRING.XML_STRING
FROM dbo.ARG_XML_STRING
WHERE ARG_XML_STRING.ID = @xml_string_id AND ARG_XML_STRING.SUB_ID = 0
DECLARE
@datarecord$xml_string nvarchar(max)
DECLARE
DB_IMPLICIT_CURSOR_FOR_datarecord CURSOR LOCAL FORWARD_ONLY FOR
SELECT ARG_XML_STRING.XML_STRING
FROM dbo.ARG_XML_STRING
WHERE ARG_XML_STRING.ID = @xml_string_id AND ARG_XML_STRING.SUB_ID > 0
ORDER BY ARG_XML_STRING.SUB_ID ASC
OPEN DB_IMPLICIT_CURSOR_FOR_datarecord
WHILE 1 = 1
BEGIN
FETCH DB_IMPLICIT_CURSOR_FOR_datarecord
INTO @datarecord$xml_string
IF @@FETCH_STATUS = -1
BREAK
SET @xml_string_metadata = @xml_string_metadata + @datarecord$xml_string;
END
CLOSE DB_IMPLICIT_CURSOR_FOR_datarecord
DEALLOCATE DB_IMPLICIT_CURSOR_FOR_datarecord
SET @xml_metadata = CAST (@xml_metadata AS XML)
SELECT @uapmuser = @xml_metadata.value('(/MetaOp/@user)[1]','nvarchar(255)');
DECLARE
@i$TableDef xml
DECLARE
DB_IMPLICIT_CURSOR_FOR_i CURSOR LOCAL FORWARD_ONLY FOR
SELECT T.TableDefs.query('.')
FROM @xml_metadata.nodes('/MetaOp/TableDef') T(TableDefs)
OPEN DB_IMPLICIT_CURSOR_FOR_i
WHILE 1 = 1
BEGIN
FETCH DB_IMPLICIT_CURSOR_FOR_i
INTO @i$TableDef
IF @@FETCH_STATUS = -1
BREAK
SELECT @tableOp = @i$TableDef.value('(/TableDef/@op)[1]','varchar(1)')
SELECT @tableName = @i$TableDef.value('(/TableDef/@name)[1]','varchar(30)')
SELECT @tableDesc = @i$TableDef.value('(/TableDef/@desc)[1]','varchar(255)')
SELECT @virtual = @i$TableDef.value('(/TableDef/@virtual)[1]','int')
SELECT @inactive = @i$TableDef.value('(/TableDef/@inactive)[1]','int')
SELECT @extended = @i$TableDef.value('(/TableDef/@extended)[1]','int')
IF @extended IS NULL
SET @extended = 0
IF @virtual IS NULL
SET @virtual = 0
IF @tableOp = 'A'
BEGIN
SELECT @cnt = count_big(1)
FROM dbo.ARG_TABLE_DEF
WHERE ARG_TABLE_DEF.TABLE_NAME = @tableName
IF @cnt > 0
BEGIN
DECLARE
@db_raise_application_error_message nvarchar(4000)
SET @db_raise_application_error_message = 'The table specified already exists in the arg_table_def : ' + ISNULL(@tableName, '')
RAISERROR(@db_raise_application_error_message, 16, 12)
END
INSERT dbo.ARG_TABLE_DEF(
TABLE_NAME,
TABLE_DESCRIPTION,
DATABASE_NAME,
VIRTUAL_TABLE,
INACTIVE,
CORE,
EXTENDED,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
@tableDesc,
'mdb',
@virtual,
@inactive,
0,
@extended,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
BEGIN
DECLARE
@j$FieldDef xml
DECLARE DB_IMPLICIT_CURSOR_FOR_i CURSOR LOCAL FORWARD_ONLY FOR
SELECT T.FieldDefs.query('.')
FROM @i$TableDef.nodes('/TableDef/FieldDef') T(FieldDefs)
OPEN DB_IMPLICIT_CURSOR_FOR_j
WHILE 1 = 1
BEGIN
FETCH DB_IMPLICIT_CURSOR_FOR_j
INTO @j$FieldDef
IF @@FETCH_STATUS = -1
BREAK
SELECT @fieldName = @j$FieldDef.value('(/FieldDef/@name)[1]','varchar(30)')
SELECT @fieldDesc = @j$FieldDef.value('(/FieldDef/@desc)[1]','varchar(255)')
SELECT @actualTableName = @j$FieldDef.value('(/FieldDef/@act_t_name)[1]','varchar(30)')
SELECT @actualFieldName = @j$FieldDef.value('(/FieldDef/@act_f_name)[1]','varchar(30)')
SELECT @dataSize = @j$FieldDef.value('(/FieldDef/@size)[1]','int')
SELECT @dataType = @j$FieldDef.value('(/FieldDef/@type)[1]','int')
SELECT @fieldUnique = @j$FieldDef.value('(/FieldDef/@unique)[1]','int')
SELECT @fieldRequired = @j$FieldDef.value('(/FieldDef/@required)[1]','int')
SELECT @primaryKey = @j$FieldDef.value('(/FieldDef/@primaryKey)[1]','int')
SELECT @foreignKey = @j$FieldDef.value('(/FieldDef/@foreignKey)[1]','int')
SELECT @inactive = @j$FieldDef.value('(/FieldDef/@inactive)[1]','int')
SELECT @cnt = count_big(1)
FROM dbo.ARG_FIELD_DEF
WHERE ARG_FIELD_DEF.TABLE_NAME = @tableName AND ARG_FIELD_DEF.FIELD_NAME = @fieldName
IF @cnt > 0
BEGIN
DECLARE
@db_raise_application_error_message$2 nvarchar(4000)
SET @db_raise_application_error_message$2 = 'The field specified already exists in the arg_field_def : ' + ISNULL(@tableName, '') + '|' + ISNULL(@fieldName, '')
RAISERROR(@db_raise_application_error_message$2, 16, 15 )
END
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
ACTUAL_TABLE_NAME,
ACTUAL_FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
INACTIVE,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
@fieldName,
@actualTableName,
@actualFieldName,
@dataType,
@dataSize,
@fieldUnique,
@fieldRequired,
@primaryKey,
@foreignKey,
@inactive,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
END
CLOSE DB_IMPLICIT_CURSOR_FOR_j
DEALLOCATE DB_IMPLICIT_CURSOR_FOR_j
END
IF @virtual = 0
BEGIN
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
'creation_date',
99,
0,
0,
0,
0,
0,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
'creation_user',
10,
64,
0,
0,
0,
0,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
'last_update_date',
99,
0,
0,
0,
0,
0,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
'last_update_user',
10,
64,
0,
0,
0,
0,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
'version_number',
4,
0,
0,
1,
0,
0,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
INSERT dbo.ARG_FIELD_DEF(
TABLE_NAME,
FIELD_NAME,
DATA_TYPE,
DATA_SIZE,
FIELD_UNIQUE,
FIELD_REQUIRED,
PRIMARY_KEY,
FOREIGN_KEY,
CORE,
CREATION_DATE,
CREATION_USER,
LAST_UPDATE_DATE,
LAST_UPDATE_USER,
VERSION_NUMBER)
VALUES (
@tableName,
'tenant',
9,
16,
0,
0,
0,
1,
0,
dbo.CURRENTUTCSECS(),
@uapmuser,
dbo.CURRENTUTCSECS(),
@uapmuser,
0)
END
END
ELSE
BEGIN
IF @tableOp = 'D'
BEGIN
DELETE dbo.ARG_FIELD_DEF
WHERE ARG_FIELD_DEF.TABLE_NAME = @tableName
DELETE dbo.ARG_TABLE_DEF
WHERE ARG_TABLE_DEF.TABLE_NAME = @tableName
END
END
END
CLOSE DB_IMPLICIT_CURSOR_FOR_i
DEALLOCATE DB_IMPLICIT_CURSOR_FOR_i
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorMessage nvarchar(max)
DECLARE @AppErrorCode int
EXECUTE al_evaluate_error @ErrorNumber OUTPUT, @ErrorSeverity OUTPUT, @ErrorState OUTPUT, @ErrorMessage OUTPUT, @AppErrorCode OUTPUT
BEGIN
SET @SQL_ERROR_CODE = @AppErrorCode
SET @SQL_ERROR_DESCRIPTION = @ErrorMessage
END
END CATCH
END
GO
[dbo].[AL_SAVE_METADATA]