You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
470 lines
15 KiB
470 lines
15 KiB
use bst_mdmdm_db;
|
|
|
|
-- iot 3.8.2 to iot 4.0.0 migration
|
|
ALTER TABLE DM_OPERATION
|
|
ADD OPERATION_DETAILS VARBINARY(MAX) DEFAULT NULL;
|
|
GO
|
|
|
|
ALTER TABLE DM_APPLICATION
|
|
ADD DEVICE_ID INTEGER NOT NULL DEFAULT 0,
|
|
ENROLMENT_ID INTEGER NOT NULL DEFAULT 0;
|
|
GO
|
|
|
|
UPDATE a
|
|
SET a.DEVICE_ID = dest.DEVICE_ID,
|
|
a.ENROLMENT_ID = dest.ENROLMENT_ID
|
|
FROM DM_APPLICATION a
|
|
INNER JOIN DM_DEVICE_APPLICATION_MAPPING dest ON a.ID = dest.APPLICATION_ID;
|
|
GO
|
|
|
|
ALTER TABLE DM_APPLICATION
|
|
ADD CONSTRAINT fk_dm_device_application
|
|
FOREIGN KEY (DEVICE_ID) REFERENCES DM_DEVICE (ID)
|
|
ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
ALTER TABLE DM_APPLICATION
|
|
ADD CONSTRAINT fk_dm_enrolment_application
|
|
FOREIGN KEY (ENROLMENT_ID) REFERENCES dbo.DM_ENROLMENT (ID)
|
|
ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
GO
|
|
|
|
ALTER TABLE DM_OPERATION
|
|
ADD ENABLED BIT NOT NULL DEFAULT 0;
|
|
GO
|
|
|
|
/*
|
|
DM_OPERATION
|
|
Since the data type of CREATED_TIMESTAMP and RECEIVED_TIMESTAMP columns changed from
|
|
TIMESTAMP to BIGINT and required to store the UNIX seconds value, the existing TIMESTAMP
|
|
values in the mentioned columns should be converted to UNIX integers and stored.
|
|
*/
|
|
|
|
-- Create temporary columns with the new data type
|
|
ALTER TABLE DM_OPERATION
|
|
ADD TEMP_CREATED_TIMESTAMP BIGINT;
|
|
|
|
ALTER TABLE DM_OPERATION
|
|
ADD TEMP_RECEIVED_TIMESTAMP BIGINT;
|
|
GO
|
|
|
|
-- Update the temporary columns with the converted values
|
|
UPDATE DM_OPERATION
|
|
SET TEMP_CREATED_TIMESTAMP = DATEDIFF(SECOND, '19700101', CREATED_TIMESTAMP);
|
|
GO
|
|
|
|
UPDATE DM_OPERATION
|
|
SET TEMP_RECEIVED_TIMESTAMP = DATEDIFF(SECOND, '19700101', RECEIVED_TIMESTAMP);
|
|
GO
|
|
|
|
-- Drop the original columns
|
|
ALTER TABLE DM_OPERATION
|
|
DROP COLUMN CREATED_TIMESTAMP;
|
|
|
|
ALTER TABLE DM_OPERATION
|
|
DROP COLUMN RECEIVED_TIMESTAMP;
|
|
GO
|
|
|
|
-- Rename the temporary columns to the original names
|
|
EXEC sp_rename 'DM_OPERATION.TEMP_CREATED_TIMESTAMP', 'CREATED_TIMESTAMP', 'COLUMN';
|
|
|
|
EXEC sp_rename 'DM_OPERATION.TEMP_RECEIVED_TIMESTAMP', 'RECEIVED_TIMESTAMP', 'COLUMN';
|
|
GO
|
|
|
|
-- Add constraints back if needed
|
|
ALTER TABLE DM_OPERATION
|
|
ALTER COLUMN CREATED_TIMESTAMP BIGINT NOT NULL;
|
|
|
|
ALTER TABLE DM_OPERATION
|
|
ALTER COLUMN RECEIVED_TIMESTAMP BIGINT NULL;
|
|
GO
|
|
|
|
-- Drop tables not in 5.3.0
|
|
--DROP TABLE DM_CONFIG_OPERATION;
|
|
--DROP TABLE DM_COMMAND_OPERATION;
|
|
--DROP TABLE DM_POLICY_OPERATION;
|
|
--DROP TABLE DM_PROFILE_OPERATION;
|
|
--DROP TABLE DM_DEVICE_APPLICATION_MAPPING;
|
|
|
|
-- iot 4.0.0 to iot 4.1.0 migration
|
|
ALTER TABLE DM_POLICY
|
|
ADD PAYLOAD_VERSION VARCHAR(45) DEFAULT NULL;
|
|
GO
|
|
|
|
ALTER TABLE DM_POLICY_CORRECTIVE_ACTION
|
|
ADD FEATURE_ID INTEGER DEFAULT NULL,
|
|
IS_REACTIVE BIT NOT NULL DEFAULT 0;
|
|
GO
|
|
|
|
CREATE TABLE DM_GEOFENCE (
|
|
ID INT IDENTITY NOT NULL,
|
|
FENCE_NAME VARCHAR(255) NOT NULL,
|
|
DESCRIPTION VARCHAR(MAX) DEFAULT NULL,
|
|
LATITUDE FLOAT DEFAULT NULL,
|
|
LONGITUDE FLOAT DEFAULT NULL,
|
|
RADIUS DECIMAL(30,4) DEFAULT NULL,
|
|
GEO_JSON VARCHAR(MAX) DEFAULT NULL,
|
|
FENCE_SHAPE VARCHAR(100) DEFAULT NULL,
|
|
CREATED_TIMESTAMP DATETIME2 NOT NULL,
|
|
OWNER VARCHAR(255) NOT NULL,
|
|
TENANT_ID INTEGER DEFAULT 0,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DM_GEOFENCE_GROUP_MAPPING (
|
|
ID INT IDENTITY NOT NULL,
|
|
FENCE_ID INT NOT NULL,
|
|
GROUP_ID INT NOT NULL,
|
|
PRIMARY KEY (ID),
|
|
CONSTRAINT fk_dm_geofence_group_mapping_geofence FOREIGN KEY (FENCE_ID) REFERENCES
|
|
DM_GEOFENCE (ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT fk_dm_geofence_group_mapping_group FOREIGN KEY (GROUP_ID) REFERENCES
|
|
DM_GROUP (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DM_DEVICE_EVENT (
|
|
ID INT NOT NULL IDENTITY,
|
|
EVENT_SOURCE VARCHAR(100) NOT NULL,
|
|
EVENT_LOGIC VARCHAR(100) NOT NULL,
|
|
ACTIONS TEXT DEFAULT NULL,
|
|
CREATED_TIMESTAMP DATETIME2(0) NOT NULL,
|
|
TENANT_ID INTEGER DEFAULT 0,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DM_DEVICE_EVENT_GROUP_MAPPING (
|
|
ID INT NOT NULL IDENTITY,
|
|
EVENT_ID INT NOT NULL,
|
|
GROUP_ID INT NOT NULL,
|
|
PRIMARY KEY (ID),
|
|
CONSTRAINT fk_dm_event_group_mapping_event FOREIGN KEY (EVENT_ID) REFERENCES
|
|
DM_DEVICE_EVENT (ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT fk_dm_event_group_mapping_group FOREIGN KEY (GROUP_ID) REFERENCES
|
|
DM_GROUP (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DM_GEOFENCE_EVENT_MAPPING (
|
|
ID INT NOT NULL IDENTITY,
|
|
FENCE_ID INT NOT NULL,
|
|
EVENT_ID INT NOT NULL,
|
|
PRIMARY KEY (ID),
|
|
CONSTRAINT fk_dm_geofence_event_mapping_geofence FOREIGN KEY (FENCE_ID) REFERENCES
|
|
DM_GEOFENCE (ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT fk_dm_geofence_event_mapping_event FOREIGN KEY (EVENT_ID) REFERENCES
|
|
DM_DEVICE_EVENT (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
GO
|
|
|
|
/*
|
|
CREATE TABLE DM_EVENT_OPERATION_ASSIGNMENT_TASK (
|
|
ID INT NOT NULL IDENTITY,
|
|
DEVICE_ID INT DEFAULT NULL,
|
|
GROUP_ID INT DEFAULT NULL,
|
|
OPERATION_CODE VARCHAR(100) NOT NULL,
|
|
EVENT_SOURCE VARCHAR(250) NOT NULL,
|
|
EVENT_META_ID INT DEFAULT NULL,
|
|
STATUS VARCHAR(100) NOT NULL,
|
|
TENANT_ID INT NOT NULL,
|
|
CREATED_TIMESTAMP DATETIME2(0) NOT NULL,
|
|
COMPLETED_TIMESTAMP DATETIME2(0) DEFAULT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
*/
|
|
|
|
-- iot 4.1.0 to uem 5.0.0
|
|
CREATE TABLE DM_BILLING (
|
|
INVOICE_ID INTEGER IDENTITY NOT NULL,
|
|
TENANT_ID INTEGER DEFAULT 0,
|
|
DEVICE_ID INTEGER DEFAULT NULL,
|
|
BILLING_START DATETIME2(0) NOT NULL,
|
|
BILLING_END DATETIME2(0) NOT NULL DEFAULT GETDATE(),
|
|
PRIMARY KEY (INVOICE_ID),
|
|
CONSTRAINT fk_DM_BILLING_DM_DEVICE2 FOREIGN KEY (DEVICE_ID)
|
|
REFERENCES DM_DEVICE (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
GO
|
|
|
|
ALTER TABLE DM_ENROLMENT
|
|
ADD IS_TRANSFERRED TINYINT NOT NULL DEFAULT 0;
|
|
GO
|
|
|
|
CREATE TABLE DM_DEVICE_STATUS (
|
|
ID INTEGER IDENTITY(1,1) NOT NULL,
|
|
ENROLMENT_ID INTEGER NOT NULL,
|
|
DEVICE_ID INTEGER NOT NULL,
|
|
STATUS VARCHAR(50) DEFAULT NULL,
|
|
UPDATE_TIME DATETIME2 DEFAULT NULL,
|
|
CHANGED_BY VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY (ID),
|
|
CONSTRAINT FK_DM_DEVICE_STATUS_DEVICE FOREIGN KEY (DEVICE_ID) REFERENCES
|
|
DM_DEVICE (ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT FK_DM_DEVICE_STATUS_ENROLMENT FOREIGN KEY (ENROLMENT_ID) REFERENCES
|
|
DM_ENROLMENT (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
GO
|
|
|
|
ALTER TABLE DM_DEVICE_INFO
|
|
ALTER COLUMN VALUE_FIELD VARCHAR(1500) NULL;
|
|
GO
|
|
|
|
-- uem 5.1.0 to uem 5.2.0
|
|
CREATE TABLE DM_TRACCAR_UNSYNCED_DEVICES (
|
|
ID INT NOT NULL IDENTITY(1,1),
|
|
DEVICE_NAME VARCHAR(100) NOT NULL,
|
|
IOTS_DEVICE_IDENTIFIER VARCHAR(300) NULL UNIQUE,
|
|
TRACCAR_DEVICE_UNIQUE_ID INT NOT NULL,
|
|
TRACCAR_USENAME VARCHAR(100) NULL,
|
|
STATUS VARCHAR(100) NULL,
|
|
TENANT_ID INTEGER DEFAULT 0,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
GO
|
|
|
|
-- uem 5.2.0 to uem 5.3.0
|
|
ALTER TABLE DM_GROUP
|
|
ADD PARENT_GROUP_ID INTEGER DEFAULT 0,
|
|
PARENT_PATH VARCHAR(255) DEFAULT NULL;
|
|
GO
|
|
|
|
CREATE TABLE DM_APP_ICONS (
|
|
ID INTEGER IDENTITY(1,1) NOT NULL,
|
|
ICON_PATH VARCHAR(150) DEFAULT NULL,
|
|
PACKAGE_NAME VARCHAR(150) NOT NULL,
|
|
VERSION VARCHAR(50) DEFAULT '1.1.0',
|
|
CREATED_TIMESTAMP DATETIME2 NOT NULL,
|
|
TENANT_ID INTEGER NOT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DM_DEVICE_SUB_TYPE (
|
|
TENANT_ID INT DEFAULT 0,
|
|
SUB_TYPE_ID VARCHAR(45) NOT NULL,
|
|
DEVICE_TYPE VARCHAR(45) NOT NULL,
|
|
SUB_TYPE_NAME VARCHAR(45) NOT NULL,
|
|
TYPE_DEFINITION TEXT NOT NULL,
|
|
PRIMARY KEY (SUB_TYPE_ID,DEVICE_TYPE)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE SUB_OPERATION_TEMPLATE (
|
|
SUB_OPERATION_TEMPLATE_ID INTEGER IDENTITY(1,1),
|
|
OPERATION_DEFINITION VARCHAR(MAX) NOT NULL,
|
|
OPERATION_CODE varchar(100) NOT NULL,
|
|
SUB_TYPE_ID VARCHAR(45) NOT NULL,
|
|
DEVICE_TYPE VARCHAR(45) NOT NULL,
|
|
CREATE_TIMESTAMP BIGINT NULL DEFAULT NULL,
|
|
UPDATE_TIMESTAMP BIGINT NULL DEFAULT NULL,
|
|
PRIMARY KEY (SUB_OPERATION_TEMPLATE_ID),
|
|
UNIQUE (SUB_TYPE_ID, OPERATION_CODE, DEVICE_TYPE),
|
|
CONSTRAINT fk_SUB_OPERATION_TEMPLATE_DM_DEVICE_SUB_TYPE FOREIGN KEY (SUB_TYPE_ID, DEVICE_TYPE) REFERENCES DM_DEVICE_SUB_TYPE (SUB_TYPE_ID, DEVICE_TYPE)
|
|
);
|
|
GO
|
|
|
|
ALTER TABLE DM_DEVICE_CERTIFICATE
|
|
ADD DEVICE_IDENTIFIER VARCHAR(300);
|
|
GO
|
|
|
|
--
|
|
IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DEVICE_TYPE' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_TYPE'))
|
|
CREATE INDEX IDX_DEVICE_TYPE ON DM_DEVICE_TYPE (NAME, PROVIDER_TENANT_ID);
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DEVICE_NAME' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_TYPE'))
|
|
CREATE INDEX IDX_DEVICE_NAME ON DM_DEVICE_TYPE (NAME);
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DEVICE_TYPE_DEVICE_NAME' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_TYPE'))
|
|
CREATE INDEX IDX_DEVICE_TYPE_DEVICE_NAME ON DM_DEVICE_TYPE (ID, NAME);
|
|
GO
|
|
|
|
ALTER TABLE DM_ENROLMENT_OP_MAPPING
|
|
ADD INITIATED_BY VARCHAR(100) NULL,
|
|
DEVICE_ID INTEGER DEFAULT NULL,
|
|
DEVICE_IDENTIFICATION VARCHAR(300) DEFAULT NULL,
|
|
TENANT_ID INTEGER DEFAULT 0;
|
|
GO
|
|
|
|
ALTER TABLE DM_ENROLMENT_OP_MAPPING
|
|
ADD OPERATION_CODE VARCHAR(50) NOT NULL DEFAULT 'OP_CODE',
|
|
TYPE VARCHAR(20) NOT NULL DEFAULT 'TYPE',
|
|
DEVICE_TYPE VARCHAR(300) NOT NULL DEFAULT 'DEVICE_TYPE';
|
|
GO
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_EN_OP_MAPPING_EN_ID_STATUS' AND OBJECT_ID = OBJECT_ID('DM_ENROLMENT_OP_MAPPING'))
|
|
CREATE INDEX IDX_EN_OP_MAPPING_EN_ID_STATUS ON DM_ENROLMENT_OP_MAPPING(ENROLMENT_ID, STATUS);
|
|
GO
|
|
|
|
-- Drop the default constraint
|
|
ALTER TABLE DM_DEVICE_OPERATION_RESPONSE
|
|
DROP CONSTRAINT DF__DM_DEVICE__OPERA__02FC7413;
|
|
GO
|
|
|
|
-- Add the new column
|
|
ALTER TABLE DM_DEVICE_OPERATION_RESPONSE
|
|
ADD IS_LARGE_RESPONSE BIT NOT NULL DEFAULT 0;
|
|
GO
|
|
|
|
-- Alter the existing column
|
|
ALTER TABLE DM_DEVICE_OPERATION_RESPONSE
|
|
ALTER COLUMN OPERATION_RESPONSE VARCHAR(MAX) NULL;
|
|
GO
|
|
|
|
-- Make sure to replace 'default_value' with the actual default value you want to use
|
|
ALTER TABLE DM_DEVICE_OPERATION_RESPONSE
|
|
ADD CONSTRAINT DF__DM_DEVICE__OPERA__02FC7413 DEFAULT 'default_value' FOR OPERATION_RESPONSE;
|
|
GO
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DM_RES_RT' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_OPERATION_RESPONSE'))
|
|
GO
|
|
|
|
CREATE TABLE DM_DEVICE_OPERATION_RESPONSE_LARGE (
|
|
ID INTEGER NOT NULL,
|
|
OPERATION_ID INTEGER NOT NULL,
|
|
EN_OP_MAP_ID INTEGER NOT NULL,
|
|
OPERATION_RESPONSE VARBINARY(MAX) DEFAULT NULL,
|
|
RECEIVED_TIMESTAMP DATETIME2 DEFAULT NULL,
|
|
DEVICE_IDENTIFICATION VARCHAR(300) DEFAULT NULL
|
|
PRIMARY KEY (ID),
|
|
CONSTRAINT FK_DM_DEVICE_OPERATION_RESP_LARGE_OPERATION FOREIGN KEY (OPERATION_ID) REFERENCES
|
|
DM_OPERATION (ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT FK_DM_EN_OP_MAP_RESPONSE_LARGE FOREIGN KEY (EN_OP_MAP_ID) REFERENCES
|
|
DM_ENROLMENT_OP_MAPPING (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
GO
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_ENID_OPID_LARGE' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_OPERATION_RESPONSE_LARGE'))
|
|
CREATE INDEX IDX_ENID_OPID_LARGE ON DM_DEVICE_OPERATION_RESPONSE_LARGE(OPERATION_ID);
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DM_EN_OP_MAP_RES_LARGE' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_OPERATION_RESPONSE_LARGE'))
|
|
CREATE INDEX IDX_DM_EN_OP_MAP_RES_LARGE ON DM_DEVICE_OPERATION_RESPONSE_LARGE(EN_OP_MAP_ID);
|
|
GO
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DM_APPLICATION' AND OBJECT_ID = OBJECT_ID('DM_APPLICATION'))
|
|
CREATE INDEX IDX_DM_APPLICATION ON DM_APPLICATION(DEVICE_ID, ENROLMENT_ID, TENANT_ID);
|
|
GO
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DM_DEVICE_INFO_DID_EID_KFIELD' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_INFO'))
|
|
CREATE INDEX IDX_DM_DEVICE_INFO_DID_EID_KFIELD ON DM_DEVICE_INFO(DEVICE_ID, ENROLMENT_ID, KEY_FIELD);
|
|
|
|
--IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_DM_DEVICE_DETAIL_DID_EID' AND OBJECT_ID = OBJECT_ID('DM_DEVICE_DETAIL'))
|
|
CREATE INDEX IDX_DM_DEVICE_DETAIL_DID_EID ON DM_DEVICE_DETAIL(DEVICE_ID, ENROLMENT_ID);
|
|
GO
|
|
|
|
CREATE TABLE DM_METADATA (
|
|
METADATA_ID INTEGER IDENTITY(1,1) NOT NULL,
|
|
DATA_TYPE VARCHAR(16) NOT NULL,
|
|
METADATA_KEY VARCHAR(128) NOT NULL,
|
|
METADATA_VALUE VARCHAR(8000) NOT NULL,
|
|
TENANT_ID INTEGER NOT NULL,
|
|
PRIMARY KEY (METADATA_ID),
|
|
CONSTRAINT METADATA_KEY_TENANT_ID UNIQUE(METADATA_KEY, TENANT_ID)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DM_OTP_DATA (
|
|
ID INT IDENTITY NOT NULL,
|
|
OTP_TOKEN VARCHAR(100) NOT NULL,
|
|
TENANT_ID INTEGER NOT NULL,
|
|
USERNAME VARCHAR(500) NOT NULL,
|
|
EMAIL VARCHAR(100) NOT NULL,
|
|
EMAIL_TYPE VARCHAR(20) NOT NULL,
|
|
META_INFO VARCHAR(8000) NOT NULL,
|
|
CREATED_AT DATETIME2(0) NOT NULL,
|
|
EXPIRY_TIME INT NOT NULL DEFAULT 3600,
|
|
IS_EXPIRED BIT DEFAULT 0,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DYNAMIC_TASK (
|
|
DYNAMIC_TASK_ID INTEGER IDENTITY(1,1) NOT NULL,
|
|
NAME VARCHAR(255) DEFAULT NULL ,
|
|
CRON VARCHAR(8000) DEFAULT NULL,
|
|
IS_ENABLED BIT NOT NULL DEFAULT 0,
|
|
TASK_CLASS_NAME VARCHAR(8000) DEFAULT NULL,
|
|
TENANT_ID INTEGER DEFAULT 0,
|
|
PRIMARY KEY (DYNAMIC_TASK_ID)
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE DYNAMIC_TASK_PROPERTIES (
|
|
DYNAMIC_TASK_ID INTEGER NOT NULL,
|
|
PROPERTY_NAME VARCHAR(100) DEFAULT 0,
|
|
PROPERTY_VALUE VARCHAR(100) DEFAULT NULL,
|
|
TENANT_ID VARCHAR(100),
|
|
PRIMARY KEY (DYNAMIC_TASK_ID, PROPERTY_NAME, TENANT_ID),
|
|
CONSTRAINT FK_DYNAMIC_TASK_TASK_PROPERTIES FOREIGN KEY (DYNAMIC_TASK_ID) REFERENCES
|
|
DYNAMIC_TASK (DYNAMIC_TASK_ID) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
GO
|
|
|
|
-- uem 5.3.2.0 to uem 5.3.3.0
|
|
|
|
ALTER TABLE DM_OPERATION
|
|
ADD TENANT_ID INTEGER NOT NULL DEFAULT 0;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_OP_CREATED' AND OBJECT_ID = OBJECT_ID('DM_OPERATION'))
|
|
CREATE INDEX IDX_OP_CREATED ON DM_OPERATION(CREATED_TIMESTAMP);
|
|
IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_OP_CODE' AND OBJECT_ID = OBJECT_ID('DM_OPERATION'))
|
|
CREATE INDEX IDX_OP_CODE ON DM_OPERATION(OPERATION_CODE);
|
|
IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_OP_INITIATED_BY' AND OBJECT_ID = OBJECT_ID('DM_OPERATION'))
|
|
CREATE INDEX IDX_OP_INITIATED_BY ON DM_OPERATION(INITIATED_BY);
|
|
|
|
IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IDX_ENROLMENT_OP_MAPPING_CREATED_TS' AND OBJECT_ID = OBJECT_ID('DM_ENROLMENT_OP_MAPPING'))
|
|
CREATE INDEX IDX_ENROLMENT_OP_MAPPING_CREATED_TS ON DM_ENROLMENT_OP_MAPPING(CREATED_TIMESTAMP);
|
|
GO
|
|
|
|
ALTER TABLE DM_METADATA
|
|
ALTER COLUMN METADATA_VALUE TEXT NOT NULL;
|
|
GO
|
|
|
|
ALTER TABLE DM_OTP_DATA
|
|
ALTER COLUMN META_INFO VARCHAR(8000) NULL;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM SYS.VIEWS WHERE NAME = 'FEATURE_NON_COMPLIANCE_INFO')
|
|
exec('CREATE VIEW FEATURE_NON_COMPLIANCE_INFO AS
|
|
SELECT TOP 100 PERCENT
|
|
DM_DEVICE.ID AS DEVICE_ID,
|
|
DM_DEVICE.DEVICE_IDENTIFICATION,
|
|
DM_DEVICE_DETAIL.DEVICE_MODEL,
|
|
DM_DEVICE_DETAIL.VENDOR,
|
|
DM_DEVICE_DETAIL.OS_VERSION,
|
|
DM_ENROLMENT.OWNERSHIP,
|
|
DM_ENROLMENT.OWNER,
|
|
DM_ENROLMENT.STATUS AS CONNECTIVITY_STATUS,
|
|
DM_POLICY_COMPLIANCE_STATUS.POLICY_ID,
|
|
DM_DEVICE_TYPE.NAME
|
|
AS PLATFORM,
|
|
DM_POLICY_COMPLIANCE_FEATURES.FEATURE_CODE,
|
|
DM_POLICY_COMPLIANCE_FEATURES.STATUS AS IS_COMPLAINT,
|
|
DM_DEVICE.TENANT_ID
|
|
FROM
|
|
DM_POLICY_COMPLIANCE_FEATURES, DM_POLICY_COMPLIANCE_STATUS, DM_ENROLMENT, DM_DEVICE, DM_DEVICE_TYPE, DM_DEVICE_DETAIL
|
|
WHERE
|
|
DM_POLICY_COMPLIANCE_FEATURES.COMPLIANCE_STATUS_ID = DM_POLICY_COMPLIANCE_STATUS.ID AND
|
|
DM_POLICY_COMPLIANCE_STATUS.ENROLMENT_ID =
|
|
DM_ENROLMENT.ID AND
|
|
DM_POLICY_COMPLIANCE_STATUS.DEVICE_ID = DM_DEVICE.ID AND
|
|
DM_DEVICE.DEVICE_TYPE_ID = DM_DEVICE_TYPE.ID AND
|
|
DM_DEVICE.ID = DM_DEVICE_DETAIL.DEVICE_ID
|
|
ORDER BY TENANT_ID, DEVICE_ID');
|
|
GO
|
|
|
|
/*
|
|
ALTER TABLE DM_DEVICE_SUB_TYPE
|
|
ALTER COLUMN DEVICE_TYPE VARCHAR(45) NOT NULL;
|
|
GO
|
|
*/
|
|
|
|
ALTER TABLE DM_DEVICE_SUB_TYPE
|
|
ALTER COLUMN SUB_TYPE_NAME VARCHAR(100) NOT NULL;
|
|
GO
|
|
|
|
/*
|
|
ALTER TABLE SUB_OPERATION_TEMPLATE
|
|
ALTER COLUMN DEVICE_TYPE VARCHAR(45) NOT NULL;
|
|
GO
|
|
*/
|