AipGateway/Database/AIP 테이블 스크립트.SQL

-- DROP TABLE aipdb.dbo.TB_LINKED_SYSTEM;
CREATE TABLE aipdb.dbo.TB_LINKED_SYSTEM (
	SystemId int IDENTITY(1,1) NOT NULL,
	SystemName varchar(100) COLLATE Korean_Wansung_CI_AS NOT NULL,
	SystemDesc varchar(255) COLLATE Korean_Wansung_CI_AS NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_LINKED_SYSTEM PRIMARY KEY (SystemId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 시스템 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 시스템 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM', @level2type=N'Column', @level2name=N'SystemId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 시스템 이름', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM', @level2type=N'Column', @level2name=N'SystemName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 시스템 설명', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM', @level2type=N'Column', @level2name=N'SystemDesc';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SYSTEM', @level2type=N'Column', @level2name=N'UseYn';


-- DROP TABLE aipdb.dbo.TB_LINKED_SERVER;
CREATE TABLE aipdb.dbo.TB_LINKED_SERVER (
	ServerId int IDENTITY(1,1) NOT NULL,
	SystemId int NOT NULL,
	ServerIpAddr varchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ServerDesc varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_LINKED_SERVER PRIMARY KEY (ServerId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'ServerId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 시스템 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'SystemId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 IP Address', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'ServerIpAddr';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 설명', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'ServerDesc';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_SERVER', @level2type=N'Column', @level2name=N'UseYn';

ALTER TABLE aipdb.dbo.TB_LINKED_SERVER ADD CONSTRAINT FK_LINKED_SERVER_1 FOREIGN KEY (SystemId) REFERENCES aipdb.dbo.TB_LINKED_SYSTEM(SystemId);


-- DROP TABLE aipdb.dbo.TB_LINKED_DESCRYPT_KEY;
CREATE TABLE aipdb.dbo.TB_LINKED_DECRYPT_KEY (
	Id int IDENTITY(1,1) NOT NULL,
	ServerId int NOT NULL,
	DecryptKey varchar(64) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ExpiredAt datetime NOT NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_LINKED_DECRYPTION_KEY PRIMARY KEY (Id)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 복호화 키', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 복호화 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'Id';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'ServerId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 복호화 키', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'DecryptKey';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'만료일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'ExpiredAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_DECRYPT_KEY', @level2type=N'Column', @level2name=N'UseYn';

ALTER TABLE aipdb.dbo.TB_LINKED_DECRYPT_KEY ADD CONSTRAINT FK_LINKED_DECRYPTION_1 FOREIGN KEY (ServerId) REFERENCES aipdb.dbo.TB_LINKED_SERVER(ServerId);


-- DROP TABLE aipdb.dbo.TB_LINKED_API_KEY;
CREATE TABLE aipdb.dbo.TB_LINKED_API_KEY (
	Id int IDENTITY(1,1) NOT NULL,
	ServerId int NOT NULL,
	ApiKey varchar(64) COLLATE Korean_Wansung_CI_AS NOT NULL,
	policyLookupYn bit DEFAULT 0 NOT NULL,
	fileInfoLookupYn bit DEFAULT 0 NOT NULL,
	applyLabelYn bit DEFAULT 0 NOT NULL,
	releaseLabelYn bit DEFAULT 0 NOT NULL,
	encryptionFileYn bit DEFAULT 0 NOT NULL,
	decryptionFileYn bit DEFAULT 0 NOT NULL,
	ExpiredAt datetime NOT NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_LINKED_API_KEY PRIMARY KEY (Id)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 API KEY', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 API KEY ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'Id';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'ServerId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 API KEY', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'ApiKey';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'레이블 조회 및 템플릿 조회 API 허용 여부', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'policyLookupYn';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 정보 조회 API 허용 여부', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'fileInfoLookupYn';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'레이블 적용 API 허용 여부', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'applyLabelYn';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'레이블 해제 API 허용 여부', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'releaseLabelYn';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 암호화 API 허용 여부', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'encryptionFileYn';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 복호화 API 허용 여부', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'decryptionFileYn';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'만료일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'ExpiredAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_LINKED_API_KEY', @level2type=N'Column', @level2name=N'UseYn';

ALTER TABLE aipdb.dbo.TB_LINKED_API_KEY ADD CONSTRAINT FK_LINKED_API_KEY_1 FOREIGN KEY (ServerId) REFERENCES aipdb.dbo.TB_LINKED_SERVER(ServerId);



-- DROP TABLE aipdb.dbo.TB_AIP_LABEL;
CREATE TABLE aipdb.dbo.TB_AIP_LABEL (
	LabelId int IDENTITY(1,1) NOT NULL,
	LabelGuid varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	LabelName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	LabelDesc varchar(512) COLLATE Korean_Wansung_CI_AS NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_API_LABEL PRIMARY KEY (LabelId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 레이블 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 레이블 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'LabelId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 레이블 GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'LabelGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 레이블 이름', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'LabelName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 레이블 설명', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'LabelDesc';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_LABEL', @level2type=N'Column', @level2name=N'UseYn';


-- DROP TABLE aipdb.dbo.TB_AIP_POLICY;
CREATE TABLE aipdb.dbo.TB_AIP_POLICY (
	PolicyId int IDENTITY(1,1) NOT NULL,
	PolicyGuid varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	PolicyName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	PolicyDesc varchar(512) COLLATE Korean_Wansung_CI_AS NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_API_POLICY PRIMARY KEY (PolicyId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 정책 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 정책 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'PolicyId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 정책 GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'PolicyGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 정책 이름', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'PolicyName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 정책 설명', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'PolicyDesc';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_POLICY', @level2type=N'Column', @level2name=N'UseYn';

-- DROP TABLE aipdb.dbo.TB_AIP_PROTECTION;
CREATE TABLE aipdb.dbo.TB_AIP_PROTECTION (
	ProtectionId int IDENTITY(1,1) NOT NULL,
	ProtectionGuid varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ProtectionName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ProtectionDesc varchar(512) COLLATE Korean_Wansung_CI_AS NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	DeletedAt datetime NULL,
	UseYn bit DEFAULT 1 NOT NULL,
	CONSTRAINT PK_API_PROTECTION PRIMARY KEY (ProtectionId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 보호정책 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 보호정책 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'ProtectionId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 보호정책 GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'ProtectionGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 보호정책 이름', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'ProtectionName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 보호정책 설명', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'ProtectionDesc';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'CreatedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'삭제일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'DeletedAt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'사용여부(1:사용,0:삭제됨)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_PROTECTION', @level2type=N'Column', @level2name=N'UseYn';



-- DROP TABLE aipdb.dbo.TB_AIP_SERVER;
CREATE TABLE aipdb.dbo.TB_AIP_SERVER (
	AipServerId int IDENTITY(1,1) NOT NULL,
	AipServerName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	IpAddr varchar(30) COLLATE Korean_Wansung_CI_AS NULL,
	Port int NULL default 0,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	CONSTRAINT PK_AIP_SERVER PRIMARY KEY (AipServerId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 서버 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_SERVER';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 서버 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_SERVER', @level2type=N'Column', @level2name=N'AipServerId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 서버 이름', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_SERVER', @level2type=N'Column', @level2name=N'AipServerName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 서버 IP Address', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_SERVER', @level2type=N'Column', @level2name=N'IpAddr';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 서버 Port', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_SERVER', @level2type=N'Column', @level2name=N'Port';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_SERVER', @level2type=N'Column', @level2name=N'CreatedAt';


-- DROP TABLE aipdb.dbo.TB_AIP_CONFIG;
CREATE TABLE aipdb.dbo.TB_AIP_CONFIG (
	Id int IDENTITY(1,1) NOT NULL,
	AipServerId int NOT NULL,
	ConfigKey varchar(1023) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ConfigValue varchar(MAX) COLLATE Korean_Wansung_CI_AS NULL,
	CreatedAt datetime DEFAULT getdate() NOT NULL,
	CONSTRAINT PK_AIP_CONFIG PRIMARY KEY (Id)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 환경설정 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_CONFIG';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 환경설정 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_CONFIG', @level2type=N'Column', @level2name=N'Id';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP Server ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_CONFIG', @level2type=N'Column', @level2name=N'AipServerId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 환경설정 Key', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_CONFIG', @level2type=N'Column', @level2name=N'ConfigKey';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP 환경설정 Value', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_CONFIG', @level2type=N'Column', @level2name=N'ConfigValue';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'등록일시(년월일시분초)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_CONFIG', @level2type=N'Column', @level2name=N'CreatedAt';

ALTER TABLE aipdb.dbo.TB_AIP_CONFIG ADD CONSTRAINT FK_AIP_CONFIG_1 FOREIGN KEY (AipServerId) REFERENCES aipdb.dbo.TB_AIP_SERVER(AipServerId);

-- DROP TABLE aipdb.dbo.TB_AIP_API_INFO;
CREATE TABLE aipdb.dbo.TB_AIP_API_INFO (
	ApiId int NOT NULL,
	AipEndPoint varchar(256) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ApiName varchar(256) COLLATE Korean_Wansung_CI_AS NOT NULL,
	ApiDesc varchar(512) COLLATE Korean_Wansung_CI_AS NULL,
	CONSTRAINT PK_AIP_API_INFO PRIMARY KEY (ApiId)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP API 정보', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_API_INFO';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP API ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_API_INFO', @level2type=N'Column', @level2name=N'ApiId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP API End Point', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_API_INFO', @level2type=N'Column', @level2name=N'AipEndPoint';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP API 이름', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_API_INFO', @level2type=N'Column', @level2name=N'ApiName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AIP API 설명', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_API_INFO', @level2type=N'Column', @level2name=N'ApiDesc';

-- DROP TABLE aipdb.dbo.TB_AIP_FILE_JOB_LOG;
CREATE TABLE aipdb.dbo.TB_AIP_FILE_JOB_LOG (
	Id bigint IDENTITY(1,1) NOT NULL,
	LogDate datetime DEFAULT getdate() NOT NULL,
	ApiId int NOT NULL,
	FileId varchar(64) COLLATE Korean_Wansung_CI_AS NOT NULL,
	FileName varchar(1023) COLLATE Korean_Wansung_CI_AS NOT NULL,
	FileExt varchar(10) COLLATE Korean_Wansung_CI_AS NOT NULL,
	FileOwner varchar(100) COLLATE Korean_Wansung_CI_AS NULL,
	FileLabelGuid varchar(255) COLLATE Korean_Wansung_CI_AS NULL,
	FileProtectionGuid varchar(255) COLLATE Korean_Wansung_CI_AS NULL,
	FileSize bigint NULL,
	NewFileName varchar(1023) COLLATE Korean_Wansung_CI_AS NULL,
	NewFileExt varchar(10) COLLATE Korean_Wansung_CI_AS NOT NULL,
	NewFileOwner varchar(100) COLLATE Korean_Wansung_CI_AS NULL,
	NewFileLabelGuid varchar(255) COLLATE Korean_Wansung_CI_AS NULL,
	NewFileProtectionGuid varchar(255) COLLATE Korean_Wansung_CI_AS NULL,
	NewFileSize bigint NULL,
	ApiGuid varchar(64) COLLATE Korean_Wansung_CI_AS NOT NULL,
	JobOwner varchar(100) COLLATE Korean_Wansung_CI_AS NULL,
	ServerIpAddr varchar(30) COLLATE Korean_Wansung_CI_AS NULL,
	ApiKey varchar(64) COLLATE Korean_Wansung_CI_AS NULL,
	DecryptKey varchar(64) COLLATE Korean_Wansung_CI_AS NULL,
	JobResult int NULL,
	JobTime int NULL,
	JobMessage varchar(MAX) COLLATE Korean_Wansung_CI_AS NULL,
	CONSTRAINT PK_AIP_FILE_JOB_LOG PRIMARY KEY (Id)
);

EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'로그 일련번호', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'Id';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'로그 일시', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'LogDate';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 명칭', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 확장자', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileExt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 소유자', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileOwner';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 Label GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileLabelGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 보호정책 GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileProtectionGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'파일 사이즈', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'FileSize';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'신규 파일 명칭', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'NewFileName';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'신규 파일 확장자', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'NewFileExt';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'신규 파일 소유자', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'NewFileOwner';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'신규 파일 Label GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'NewFileLabelGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'신규 파일 보호정책 GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'NewFileProtectionGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'신규 파일 사이즈', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'NewFileSize';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API GUID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'ApiGuid';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'ApiId';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'작업 요청장', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'JobOwner';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'연계 서버 IP Address', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'ServerIpAddr';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API KEY', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'ApiKey';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'복호화 키', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'DecryptKey';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'작업 결과', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'JobResult';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'작업 시간', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'JobTime';
EXEC aipdb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'작업 결과 메시지', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'TB_AIP_FILE_JOB_LOG', @level2type=N'Column', @level2name=N'JobMessage';



-- DROP TABLE aipdb.dbo.TB_AIP_API_CALL_LOG;
CREATE TABLE aipdb.dbo.TB_AIP_API_CALL_LOG (
	Id bigint IDENTITY(1,1) NOT NULL,
	LogDate datetime DEFAULT getdate() NOT NULL,
	ApiEndPoint varchar(64) NOT NULL,
	ApiGuid varchar(64) COLLATE Korean_Wansung_CI_AS NOT NULL,
	IPAddress varchar(30) COLLATE Korean_Wansung_CI_AS NULL,
	RequestAt datetime2(3) NULL,
	ResponseAt datetime2(3) NULL,
	ResponseStatusCode int NULL,
	ResponseTime int NULL,
	ErrorCode int NULL,
	ErrorMessage varchar(MAX) COLLATE Korean_Wansung_CI_AS NULL,
	CONSTRAINT PK_AIP_API_CALL_LOG PRIMARY KEY (Id)
);
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'AIP API 호출 Log', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'로그 일련 번호', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'Id';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'로그 일시', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'LogDate';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API End Point', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ApiEndPoint';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API GUID', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ApiGuid';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 시작 시각', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'RequestAt';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 완료 시각', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ResponseAt';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 결과', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ResponseStatusCode';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 작업 처리시각(ms)', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ResponseTime';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 서버 IP Address', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'IPAddress';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 결과 오류 코드', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ErrorCode';
EXEC aipdb.sys.sp_addextendedproperty 'MS_Description', N'API 호출 결과 오류 메시지', 'schema', N'dbo', 'table', N'TB_AIP_API_CALL_LOG', 'column', N'ErrorMessage';


게이트웨이 로그
요청타입, IsLabeledOrProtectedFile, GetFileStatus, DeleteLabelOrUnProtected
요청자
소유자
파일명x
레이블 ID
Legacy IP
파일 사이즈
요청 결과
날짜

기본정보
날짜:
요청 타입:
소유자:
요청자:
결과: 성공
내용: success
작업 코드: 
게이트웨이 IP:
기간계 IP:
API Key:

MIP 문서 속성
레이블 ID:
템플릿 ID:
원 문서 ID:
문서 ID:

파일 속성
파일명:
원본 사이즈:
사이브:
Hash:





팀 동기화 관리
	부서명: 부서명
	팀 생성:	적용시 팀즈 팀이 생성됩니다. 팀 생성 여부
	하위 부서 포함: 하위 부서의 사용자를 포함하여 팀을 생성합니다. 하위부서 포함 여부
	하위 팀 생성: 하위 모든 부서의 팀즈 팀을 생성합니다. 하위팀 생성 여부
	상위부터 포함 제외: 상위 부서중에 하위 부서 포함이 체크된 경우 현재 부서 및 하위 부서를 포함하지 않도록 합니다. 상위부서 포함 제외 여부
	겸직 사용자 포함 제외: 팀에 겸직 사용자를 추가하지 않도록 설정합니다. 겸직 사용자 포함 제외

==> 사용자 추가/사용자 삭제