CG_STORE_MODELS_SEGMENTATION.sql

-- PROMPT Creating Table 'MODELS_SEGMENTATION'
CREATE TABLE MODELS_SEGMENTATION
(
	ID NUMBER(8,0)
	,PARENT_ID NUMBER(8,0)
	,NAME VARCHAR2(50)
)
/

ALTER TABLE MODELS_SEGMENTATION
	MODIFY(ID CONSTRAINT MODELS_SEGMENTATION_ID_NNULL NOT NULL)
	MODIFY(NAME CONSTRAINT MODELS_SEGMENTATION_NAME_NNULL NOT NULL)
	ADD CONSTRAINT MODELS_SEGMENTATION_PK PRIMARY KEY (ID)
	ADD CONSTRAINT MODELS_SEGMENTATION_NAME_UK UNIQUE (NAME)
/

CREATE OR REPLACE FUNCTION parent_exists
	(parent_id IN NUMBER)
RETURN BOOLEAN
AS
v_id_cnt NUMBER;
BEGIN
	SELECT COUNT(*) INTO v_id_cnt FROM MODELS_SEGMENTATION WHERE PARENT_ID = parent_id;
	RETURN 1 = v_id_cnt;
	EXCEPTION
		WHEN OTHERS
			THEN RETURN FALSE;
END parent_exists;
/

CREATE OR REPLACE TRIGGER MODELS_SEGMENTATION_BI
	BEFORE INSERT ON MODELS_SEGMENTATION
	FOR EACH ROW
BEGIN
	IF :new.PARENT_ID != null THEN
		IF NOT parent_exists(:new.PARENT_ID) THEN
			RAISE_APPLICATION_ERROR(-20000, 'PARENT GROUP NOT FOUND');
		END IF;
	END IF;
END;
/

-- FURNITURE
INSERT INTO MODELS_SEGMENTATION VALUES (1, NULL, 'FURNITURE' );
INSERT INTO MODELS_SEGMENTATION VALUES (2, 1, 'SOFAS' );
INSERT INTO MODELS_SEGMENTATION VALUES (3, 1, 'CHAIRS' );

-- FOLIAGE
INSERT INTO MODELS_SEGMENTATION VALUES (4, NULL, 'FOLIAGE' );
INSERT INTO MODELS_SEGMENTATION VALUES (5, 4, 'BUSHES' );
INSERT INTO MODELS_SEGMENTATION VALUES (6, 4, 'FLOWERS' );

-- CHARACTERS
INSERT INTO MODELS_SEGMENTATION VALUES (7, NULL, 'CHARACTERS' );
INSERT INTO MODELS_SEGMENTATION VALUES (8, 7, 'HUMANOID' );
INSERT INTO MODELS_SEGMENTATION VALUES (9, 7, 'ANIMAL' );

-- VEHICLE
INSERT INTO MODELS_SEGMENTATION VALUES (10, NULL, 'VEHICLE' );
INSERT INTO MODELS_SEGMENTATION VALUES (11, 10, 'LAND VEHICLE' );
INSERT INTO MODELS_SEGMENTATION VALUES (12, 10, 'AIR SHIP' );

-- WEAPONS
INSERT INTO MODELS_SEGMENTATION VALUES (13, NULL, 'WEAPONS' );
INSERT INTO MODELS_SEGMENTATION VALUES (14, 13, 'PROJECTILE' );
INSERT INTO MODELS_SEGMENTATION VALUES (15, 13, 'HAND HELD' );

-- ARCHITECTURE
INSERT INTO MODELS_SEGMENTATION VALUES (16, NULL, 'ARCHITECTURE' );
INSERT INTO MODELS_SEGMENTATION VALUES (17, 16, 'PUBLIC' );
INSERT INTO MODELS_SEGMENTATION VALUES (18, 16, 'INTERIOR' );

COMMIT;