cmdb.sql

DROP TABLE IF EXISTS HOSTS ;
DROP TABLE IF EXISTS OS ;
DROP TABLE IF EXISTS OSFAM ;
DROP TABLE IF EXISTS ACCESS_METOD ;

CREATE TABLE IF NOT EXISTS HOSTS (
	ID INTEGER PRIMARY KEY AUTOINCREMENT ,
	HOSTNAME VARCHAR(255) ,
	DNS_NAME VARCHAR(255) ,
	OS INTEGER REFERENCES OS(ID) ,
	ACCESS_METHOD INTEGER REFERENCES ACCESS_METOD(ID) 
);

CREATE TABLE IF NOT EXISTS OS (
	ID INTEGER PRIMARY KEY AUTOINCREMENT ,
	OS_NAME VARCHAR(100) ,
	OS_FAMILY INTEGER REFERENCES OSFAM(ID)
) ;

CREATE TABLE IF NOT EXISTS OSFAM (
	ID INTEGER PRIMARY KEY AUTOINCREMENT ,
	OS_FAMILY VARCHAR(255) UNIQUE
) ;

CREATE TABLE IF NOT EXISTS ACCESS_METOD (
	ID INTEGER PRIMARY KEY AUTOINCREMENT ,
	ACCESS_METHOD_NAME VARCHAR(255) 
) ;

INSERT OR REPLACE INTO OSFAM (OS_FAMILY) VALUES ('LINUX') ;
INSERT OR REPLACE INTO OSFAM (OS_FAMILY) VALUES ('UNIX') ;
INSERT OR REPLACE INTO OSFAM (OS_FAMILY) VALUES ('WINDOWS') ;
INSERT OR REPLACE INTO OSFAM (OS_FAMILY) VALUES ('MAINFRAME') ;

INSERT OR REPLACE INTO OS (OS_NAME,OS_FAMILY) VALUES ('REDHAT ENTERPRISE LINUX',1) ;
INSERT OR REPLACE INTO OS (OS_NAME,OS_FAMILY) VALUES ('AIX',2) ;
INSERT OR REPLACE INTO OS (OS_NAME,OS_FAMILY) VALUES ('MVS z/OS',4) ;
INSERT OR REPLACE INTO OS (OS_NAME,OS_FAMILY) VALUES ('FREEBSD',2) ;

INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('PBRUN') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('SSH-SUDO') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('SSH-ROOT') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('SSH-SU') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('TELNET-SUDO') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('TELNET-SU') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('TELNET-ROOT') ;
INSERT OR REPLACE INTO ACCESS_METOD (ACCESS_METHOD_NAME) VALUES ('TN3270') ;