cmdb

来源：互联网发布：哔哩哔哩电脑直播软件编辑：程序博客网时间：2024/05/22 00:20

create sequence SERVER_SEQ increment by 1 start with 10000 nomaxvalue nocycle cache 10;

create table SERVERMASTER
(
NAME VARCHAR2(180) not null,
IDA2A2 NUMBER
)

insert into SERVERMASTER values('szxmr04-or',SERVER_SEQ.NEXTVAL);
insert into SERVERMASTER values('szxmr03-or',SERVER_SEQ.NEXTVAL);
insert into SERVERMASTER values('szxmr03-or',SERVER_SEQ.NEXTVAL);
insert into SERVERMASTER values('szxpdm08-in',SERVER_SEQ.NEXTVAL);
insert into SERVERMASTER values('szxpdm03-or',SERVER_SEQ.NEXTVAL);
insert into SERVERMASTER values('szxmda01-in',SERVER_SEQ.NEXTVAL);

select * from SERVERMASTER

create table SERVER
(
CLASSNAMEKEYMASTERREFERENCE VARCHAR2(600),
IDA3MASTERREFERENCE          NUMBER,
CLASSNAMEA2A2                VARCHAR2(600),
IDA2A2                       NUMBER not null
)

insert into PLATFORM values('A',10006,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10007,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10008,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10009,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10010,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10011,'1',PLATFORM_SEQ.NEXTVAL);

create sequence PLATFORM_SEQ increment by 1 start with 10000 nomaxvalue nocycle cache 10;

create table PLATFORMMASTER
(
NAME VARCHAR2(180) not null,
IDA2A2 NUMBER
)

insert into PLATFORMMASTER values('PDM',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORMMASTER values('ERP',PLATFORM_SEQ.NEXTVAL);

select * from cmdb.platformmaster for update

create table PLATFORM
(
CLASSNAMEKEYMASTERREFERENCE VARCHAR2(600),
IDA3MASTERREFERENCE          NUMBER,
CLASSNAMEA2A2                VARCHAR2(600),
IDA2A2                       NUMBER not null
)

insert into PLATFORM values('A',10000,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10000,'2',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10000,'3',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('B',10000,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10001,'1',PLATFORM_SEQ.NEXTVAL);
insert into PLATFORM values('A',10001,'2',PLATFORM_SEQ.NEXTVAL);

select * from PLATFORM
create sequence SOFT_SEQ increment by 1 start with 10000 nomaxvalue nocycle cache 10;

create table SOFTMASTER
(
NAME VARCHAR2(180) not null,
IDA2A2 NUMBER
)

insert into SOFTMASTER values('Windchill PDM FOUNDATION',SOFT_SEQ.NEXTVAL);
insert into SOFTMASTER values('Oracle DB',SOFT_SEQ.NEXTVAL);
insert into SOFTMASTER values('ORACLE 11i',SOFT_SEQ.NEXTVAL);
insert into SOFTMASTER values('Websphere Application Server',SOFT_SEQ.NEXTVAL);

select * from SOFTMASTER

create table SOFT
(
CLASSNAMEKEYMASTERREFERENCE VARCHAR2(600),
IDA3MASTERREFERENCE          NUMBER,
CLASSNAMEA2A2                VARCHAR2(600),
IDA2A2                       NUMBER not null
)

insert into SOFT values('A',10000,'1',SOFT_SEQ.NEXTVAL);
insert into SOFT values('A',10001,'1',SOFT_SEQ.NEXTVAL);
insert into SOFT values('A',10002,'1',SOFT_SEQ.NEXTVAL);
insert into SOFT values('A',10003,'1',SOFT_SEQ.NEXTVAL);
insert into SOFT values('A',10001,'2',SOFT_SEQ.NEXTVAL);
insert into SOFT values('A',10003,'2',SOFT_SEQ.NEXTVAL);

select * from SOFT

create sequence RESOURCECATEGORY_SEQ increment by 1 start with 10000 nomaxvalue nocycle cache 10;

create table RESOURCECATEGORY
(
CATEGORYTYPE         VARCHAR2(900) not null,
CATEGORYVALUE        VARCHAR2(1500) not null,
CLASSNAMEA2A2        VARCHAR2(600),
IDA2A2               NUMBER not null
)

insert into RESOURCECATEGORY values('A1','机房','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('B1','机柜','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('B2','消防','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('B3','空调','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('B4','UPD','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('B5','STS','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('C1','物理主机','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('C2','存储','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('C3','接入设备','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('D1','服务器','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('E1','数据库','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('E2','软件','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('E3','应用平台','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('E4','监控','',PLATFORMMASTER_SEQ.NEXTVAL);
insert into RESOURCECATEGORY values('E5','备份','',PLATFORMMASTER_SEQ.NEXTVAL);

select * from cmdb.RESOURCECATEGORY

create table RESOURCECATEGORYLINK
(
CLASSNAMEKEYROLEAOBJECTREF VARCHAR2(600),
IDA3A5                     NUMBER,
CLASSNAMEKEYROLEBOBJECTREF VARCHAR2(600),
IDA3B5                     NUMBER,
CLASSNAMEA2A2              VARCHAR2(600),
IDA2A2                     NUMBER not null
)

insert into RESOURCECATEGORYLINK values('',10015,'',10016,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10015,'',10017,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10015,'',10018,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10015,'',10019,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10015,'',10020,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10020,'',10021,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10020,'',10022,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10020,'',10023,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10021,'',10024,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10024,'',10025,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10024,'',10026,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10024,'',10027,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10024,'',10028,'',RESOURCECATEGORY_SEQ.NEXTVAL);
insert into RESOURCECATEGORYLINK values('',10024,'',10029,'',RESOURCECATEGORY_SEQ.NEXTVAL);

select A.le,B.CATEGORYTYPE,B.CATEGORYVALUE from (
select x1.le, x1.ida from (
select LEVEL as le, x0.IDA3B5 as ida from RESOURCECATEGORYLINK x0
CONNECT BY PRIOR x0.IDA3B5 = x0.IDA3A5
START WITH x0.IDA3A5 =(10015)
) x1 --where x1.le=2 or x1.le=3
) A , RESOURCECATEGORY B
where B.ida2a2 = A.ida

服务器-平台-软件
此服务器上包含的平台并在此平台中运行的软件；
服务器-软件-平台
此服务器中包含的软件并此软件所属的平台
平台-服务器-软件
平台所运行的服务器及其上运行的此平台的软件
平台-软件-服务器
平台包含的软件及软件在服务器上的分布
软件-服务器-平台
此软件在那个服务器上存在，是属于哪个平台的
软件-平台-服务器
此软件是在那个平台上运行，此平台对应的服务器是什么