BIMRL-std-once.sql

DROP SEQUENCE seq_BIMRL_FEDERATEDMODEL_ID;

DROP TABLE BIMRL_FEDERATEDMODEL;
CREATE TABLE BIMRL_FEDERATEDMODEL (
		  ModelName	varchar2(256) NOT NULL,
		  ProjectName varchar2(256) NOT NULL,
		  ProjectNumber varchar2(256) NOT NULL, 
		  ProjectID varchar2(22),
		  FederatedID number(10) NOT NULL UNIQUE,
		  WORLDBBOX SDO_GEOMETRY,
		  MAXOCTREELEVEL number(2),
		  LastUpdateDate date,
		  Owner	varchar2(32) NOT NULL,
		  DBConnection varchar2(128),
		  PRIMARY KEY (ModelName, ProjectNumber, ProjectName)
);
GRANT SELECT,INSERT,UPDATE,DELETE ON BIMRL_FEDERATEDMODEL to PUBLIC;
CREATE PUBLIC SYNONYM BIMRL_FEDERATEDMODEL for BIMRL_FEDERATEDMODEL;

INSERT INTO BIMRL_FEDERATEDMODEL (MODELNAME, PROJECTNAME, PROJECTNUMBER, FEDERATEDID) VALUES ('Dummy Project', 'Project Name', 'Project Number', 0);

CREATE OR REPLACE TRIGGER federatedID_increment
BEFORE INSERT
ON BIMRL_FEDERATEDMODEL
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT MAX(FederatedID)+1 INTO :NEW.FederatedID FROM BIMRL_FEDERATEDMODEL;
END;
/

CREATE OR REPLACE TRIGGER ownerinfo
BEFORE INSERT ON BIMRL_FEDERATEDMODEL
FOR EACH ROW
BEGIN
  SELECT USER INTO :NEW.owner FROM DUAL;
END;
/


/*
CREATE SEQUENCE seq_BIMRL_FEDERATEDMODEL_ID;
SELECT seq_BIMRL_FEDERATEDMODEL_ID.nextval INTO :NEW.FederatedID FROM dual;
*/

DROP TABLE BIMRL_OBJECTHIERARCHY;
CREATE TABLE BIMRL_OBJECTHIERARCHY (
		  IfcSchemaVer varchar2(32) NOT NULL,
		  ElementType varchar2(64) NOT NULL, 
		  ElementSubType varchar2(64) NOT NULL, 
		  Abstract number(1) DEFAULT 0, 
		  LevelsRemoved number(3)
);
GRANT SELECT ON BIMRL_OBJECTHIERARCHY to PUBLIC;
CREATE PUBLIC SYNONYM BIMRL_OBJECTHIERARCHY FOR BIMRL_OBJECTHIERARCHY;

CREATE INDEX IDX1_BIMRL_OBJHIER on BIMRL_OBJECTHIERARCHY (IfcSchemaVer, ElementType);
CREATE INDEX IDX2_BIMRL_OBJHIER on BIMRL_OBJECTHIERARCHY (IfcSchemaVer, ElementSubType);

CREATE OR REPLACE TYPE POINT3D AS OBJECT (
		  X	DOUBLE PRECISION,
		  Y	DOUBLE PRECISION,
		  Z	DOUBLE PRECISION
);
/

GRANT EXECUTE ON POINT3D TO PUBLIC;
CREATE PUBLIC SYNONYM POINT3D FOR POINT3D;

CREATE OR REPLACE TYPE MATRIX3D AS VARRAY(3) of POINT3D;
/

GRANT EXECUTE ON MATRIX3D TO PUBLIC;
CREATE PUBLIC SYNONYM MATRIX3D FOR MATRIX3D;

CREATE TABLE "COLORDICT" (
		  "ELEMENTTYPE" VARCHAR2(64) NOT NULL ENABLE, 
		  "AMBIENTINTENSITY" FLOAT(126), 
		  "DIFFUSECOLORRED" NUMBER(3,0), 
		  "DIFFUSECOLORGREEN" NUMBER(3, 0), 
		  "DIFFUSECOLORBLUE" NUMBER(3,0), 
		  "EMISSIVECOLORRED" NUMBER(3, 0), 
		  "EMISSIVECOLORGREEN" NUMBER(3,0), 
		  "EMISSIVECOLORBLUE" NUMBER(3, 0), 
		  "SHININESS" FLOAT(126), 
		  "SPECULARCOLORRED" NUMBER(3, 0), 
		  "SPECULARCOLORGREEN" NUMBER(3, 0), 
		  "SPECULARCOLORBLUE" NUMBER(3, 0), 
		  "TRANSPARENCY" FLOAT(126)) 
;
GRANT SELECT ON COLORDICT TO PUBLIC;
CREATE PUBLIC SYNONYM COLORDICT FOR COLORDICT;