-- (generated from GLOBALFORCEMGMT/EQPT_TYPE)

CREATE TABLE EQPT_TYPE
(
  CAT_CODE                 CHAR(7) NOT NULL,
  LOADED_WT_QTY            NUMERIC(12,3),
  UNLOADED_WT_QTY          NUMERIC(12,3),
  OWNER_ID                 NUMERIC(11) NOT NULL,
  UPDATE_SEQNR             NUMERIC(15) NOT NULL,
  EQPT_TYPE_RC             VARCHAR(6)
);
SET DEFINE OFF;
SET DEFINE ON;

-- (generated from GLOBALFORCEMGMT/ACFT_TYPE)

CREATE TABLE ACFT_TYPE
(
  ACFT_TYPE_ID      NUMERIC(20) NOT NULL,
  CAT_CODE          CHAR(6) NOT NULL,
  SUBCAT_CODE       CHAR(7),
  OWNER_ID          NUMERIC(11) NOT NULL,
  UPDATE_SEQNR      NUMERIC(15) NOT NULL,
  ENGINE_IND_CODE   CHAR(3)
);

-- SET DEFINE OFF;
-- SET DEFINE ON;


ALTER TABLE ACFT_TYPE
  ADD CONSTRAINT PRIMARY_1
    PRIMARY KEY (ACFT_TYPE_ID);

ALTER TABLE ADDR
  ADD CONSTRAINT PK_ADDR
    PRIMARY KEY (ADDR_ID);


CREATE TABLE AUTHPRINCIPALS
(
  PRINCIPALTYPE  NUMERIC(10) NOT NULL,
  PRINCIPALNAME  VARCHAR(255) NOT NULL,
  POLICYUID      NUMERIC(10) NOT NULL REFERENCES AUTHPOLICIES (POLICYUID) ,
  POLICYUID_2    NUMERIC(10) NOT NULL FOREIGN KEY FK_MYNAME REFERENCES AUTHPOLICIES (POLICYUID) ,
  GRANTOR        VARCHAR(255) NOT NULL,
  CONSTRAINT PK_AUTHPOLICYPRINCIPALS UNIQUE (PRINCIPALNAME, POLICYUID)
);

CREATE TABLE AUTHREALMS
(
  REALMUID     NUMERIC(10) NOT NULL PRIMARY KEY,
  REALMNAME    VARCHAR(250) NOT NULL UNIQUE,
  DESCRIPTION  VARCHAR(550)
);

CREATE TABLE PRODUCTSSESSIONS
(
  PRODUCT_UID  NUMERIC(10) NOT NULL,
  SESSION_UID  NUMERIC(10) NOT NULL,
  PRIMARY KEY (PRODUCT_UID, SESSION_UID)
);

CREATE TABLE SERVICESESSIONS
(
   SESSIONUID           NUMERIC(20)            NOT NULL,
   PRINCIPAL            VARCHAR(255)            NOT NULL,
   APPLICATION          VARCHAR(128)           NOT NULL,
   CREATIONTIME         VARCHAR(50),
   CLIENTCOUNT          NUMERIC(10)            NOT NULL,
   STATE                NUMERIC(10)            NOT NULL,
   STATETIME            VARCHAR(50),
   USESSUBSCRIBER       CHAR(1)               NOT NULL,
   PRODUCTINFO1         VARCHAR(255),
   PRODUCTINFO2         VARCHAR(255),
   PRODUCTINFO3         VARCHAR(255),
   PRODUCTINFO4         VARCHAR(255),
   CONSTRAINT P_KEY_2 PRIMARY KEY (SESSIONUID)
);

CREATE TABLE RT_MDLS
(
   MDL_UID              NUMERIC(20)            NOT NULL,
   MDL_UUID             VARCHAR(64)            NOT NULL,
   MDL_NM               VARCHAR(255)           NOT NULL,
   MDL_VERSION          VARCHAR(50),
   DESCRIPTION          VARCHAR(255),
   MDL_URI              VARCHAR(255),
   MDL_TYPE             NUMERIC(3),
   IS_PHYSICAL          CHAR(1)                NOT NULL,
   MULTI_SOURCED        CHAR(1)                DEFAULT '0',  
   VISIBILITY           NUMERIC(3) 
   
);
/* REFERENCES */

CREATE TABLE AUTHPERMISSIONS
(
   PERMISSIONUID        NUMERIC(20)            NOT NULL DEFAULT 0 CONSTRAINT FK_MYFK REFERENCES MY_TABLE (c1, c2) MATCH FULL ON UPDATE CASCADE ON DELETE NO ACTION,
   RESOURCENAME         VARCHAR(250)           NOT NULL DEFAULT '',
   CONSTRAINT P_KEY_2a PRIMARY KEY (PERMISSIONUID),
   CONSTRAINT U_KEY_3a UNIQUE (PERMISSIONUID),
   CONSTRAINT F_KEY_1a FOREIGN KEY (PERMISSIONUID)
);


/* DEFAULT & COLLATE */

CREATE TABLE AUTHPERMISSIONS
(
   PERMISSIONUID        NUMERIC(20)            NOT NULL DEFAULT 0,
   RESOURCENAME         VARCHAR(250)           NOT NULL DEFAULT '',
   ACTIONS              NUMERIC(10)            NOT NULL DEFAULT (10),
   CONTENTMODIFIER      VARCHAR(250),
   PERMTYPEUID          NUMERIC(10)            NOT NULL DEFAULT (20),
   REALMUID             NUMERIC(20)            NOT NULL DEFAULT SESSION_USER,
   POLICYUID            NUMERIC(20)            NOT NULL DEFAULT SYSDATE
);

CREATE TABLE AUDITENTRIES
(
   TIMESTAMP            VARCHAR(50)            NOT NULL,
   CONTEXT              VARCHAR(64)            NOT NULL,
   ACTIVITY             VARCHAR(64)            NOT NULL,
   RESOURCES            VARCHAR(4000)          NOT NULL,
   PRINCIPAL            VARCHAR(255)            NOT NULL,
   HOSTNAME             VARCHAR(64)            NOT NULL,
   VMID                 VARCHAR(64)            NOT NULL
);

CREATE TABLE AUTHPERMISSIONS
(
   PERMISSIONUID        NUMERIC(20)            NOT NULL,
   RESOURCENAME         VARCHAR(250)           NOT NULL,
   ACTIONS              NUMERIC(10)            NOT NULL,
   CONTENTMODIFIER      VARCHAR(250),
   PERMTYPEUID          NUMERIC(10)            NOT NULL,
   REALMUID             NUMERIC(20)            NOT NULL,
   POLICYUID            NUMERIC(20)            NOT NULL,
	CONSTRAINT P_KEY_2a PRIMARY KEY (PERMISSIONUID)
);

CREATE TABLE AUTHPOLICIES
(
  POLICYUID    NUMERIC(10) NOT NULL PRIMARY KEY,
  DESCRIPTION  VARCHAR(250),
  POLICYNAME   VARCHAR(250) NOT NULL
);

CREATE TABLE AUTHPRINCIPALS
(
  PRINCIPALTYPE  NUMERIC(10) NOT NULL,
  PRINCIPALNAME  VARCHAR(255) NOT NULL,
  POLICYUID      NUMERIC(10) NOT NULL CONSTRAINT FK_ATHPLCY_PLCYUID REFERENCES AUTHPOLICIES (POLICYUID) ,
  GRANTOR        VARCHAR(255) NOT NULL,
  CONSTRAINT PK_AUTHPOLICYPRINCIPALS UNIQUE (PRINCIPALNAME, POLICYUID)
);

CREATE TABLE AUTHREALMS
(
  REALMUID     NUMERIC(10) NOT NULL CONSTRAINT PK_AUTHREALMS UNIQUE,
  REALMNAME    VARCHAR(250) NOT NULL UNIQUE,
  DESCRIPTION  VARCHAR(550)
);

CREATE TABLE IDTABLE
(
  IDCONTEXT  VARCHAR(20) NOT NULL PRIMARY KEY,
  NEXTID     NUMERIC
);

CREATE TABLE PRINCIPALTYPES
(
  PRINCIPALTYPEUID  NUMERIC(10) NOT NULL CONSTRAINT PK_PRNCPLTYPUID UNIQUE,
  PRINCIPALTYPE     VARCHAR(60) NOT NULL,
  DISPLAYNAME       VARCHAR(80) NOT NULL,
  LASTCHANGEDBY     VARCHAR(255) NOT NULL,
  LASTCHANGED       VARCHAR(50) NULL
);

