Solaris

REM ------------------------------------------------------------------------
REM                           ACCESS CONTROL TABLES
REM ------------------------------------------------------------------------

CREATE SEQUENCE gam_product_ruleset_id_seq
	START WITH 1000
	NOCYCLE
/

CREATE SEQUENCE gam_product_rule_id_seq
	START WITH 1000
	NOCYCLE
/

CREATE TABLE gam_product_rulesets (
        ruleset_id NUMBER(10)
		CONSTRAINT gprs_pk
		PRIMARY KEY
		USING INDEX
		TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited),
        ruleset_name VARCHAR2(40) NOT NULL
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

REM Primary key constraint causes a unique index to be built.

CREATE TABLE gam_product_ruleset_types (
        target_type	NUMBER(10)
		CONSTRAINT gprtyp_pk
		PRIMARY KEY
		USING INDEX
		TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited),
        description	VARCHAR2(100) NOT NULL
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

REM Primary key constraint causes a unique index to be built.


CREATE TABLE gam_product_ruleset_targets (
        target_type	CONSTRAINT gprt_tt
		NOT NULL REFERENCES gam_product_ruleset_types
		ON DELETE CASCADE, target_id VARCHAR2(100)
		NOT NULL, ruleset_id CONSTRAINT gprt_rs_fk NOT NULL REFERENCES gam_product_rulesets ON DELETE CASCADE
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

ALTER TABLE gam_product_ruleset_targets ADD (
	CONSTRAINT gprt_unique
	UNIQUE (target_type, target_id, ruleset_id)
	USING INDEX
	TABLESPACE gam_ind
	STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
)
/

CREATE INDEX gam_product_rsta_targ ON gam_product_ruleset_targets (
	target_type, target_id
)
TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

CREATE TABLE gam_product_rule_tests (
        rule_test NUMBER(10)
		CONSTRAINT gprtst_pk
		PRIMARY KEY
		USING INDEX
		TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited),
        description	VARCHAR2(100) NOT NULL
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

INSERT INTO gam_product_rule_tests (rule_test, description) VALUES (0, 'Always true, no arguments');
INSERT INTO gam_product_rule_tests (rule_test, description) VALUES (1, 'Presence of product <test arg 0>');
INSERT INTO gam_product_rule_tests (rule_test, description) VALUES (2, 'Balance of element <test arg 0> less than [test arg 1, 0 if none]');

CREATE TABLE gam_product_rule_actions (
        rule_action	NUMBER(10)
		CONSTRAINT gpra_pk
		PRIMARY KEY
		USING INDEX TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited),
        description	VARCHAR2(100) NOT NULL
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

INSERT INTO gam_product_rule_actions (rule_action, description) VALUES (0, 'Deny access to desired resource');
INSERT INTO gam_product_rule_actions (rule_action, description) VALUES (1, 'Allow access, but warn with message <action arg 0>');
INSERT INTO gam_product_rule_actions (rule_action, description) VALUES (2, 'Allow access, with optional message [action arg 0]');
INSERT INTO gam_product_rule_actions (rule_action, description) VALUES (3, 'Purchase product <action arg 0>');


CREATE TABLE gam_product_rules (
        rule_id	NUMBER(10)
		CONSTRAINT gpr_pk
		PRIMARY KEY
		USING INDEX TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited), 
        ruleset_id	CONSTRAINT gpr_rs_fk NOT NULL REFERENCES gam_product_rulesets ON DELETE CASCADE, 
        rule_priority NUMBER(10) NOT NULL, 
        rule_truth CHAR(1) CONSTRAINT gpr_truth_chk CHECK (rule_truth IN ('Y', 'N')), 
        rule_test CONSTRAINT gpr_t_fk NOT NULL REFERENCES gam_product_rule_tests, 
        rule_action	CONSTRAINT gpr_a_fk NOT NULL REFERENCES gam_product_rule_actions
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

CREATE INDEX gam_product_rules_rs ON gam_product_rules (
	ruleset_id
)
TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0
			MAXEXTENTS unlimited)
/

CREATE TABLE gam_product_rule_args (
        rule_id	CONSTRAINT gpra_r_fk NOT NULL REFERENCES gam_product_rules ON DELETE CASCADE,
        rule_arg_type CHAR(1) CONSTRAINT gpra_type_chk CHECK (rule_arg_type IN ('A', 'T')), 
        rule_arg_index	NUMBER(10) NOT NULL, 
        rule_arg_value	VARCHAR2(255) NOT NULL
) TABLESPACE gam_tab STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/

ALTER TABLE gam_product_rule_args ADD (
	CONSTRAINT gpra_unique UNIQUE (rule_id, rule_arg_type, rule_arg_index)
	USING INDEX TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
)
/

CREATE INDEX gam_product_ra_id ON gam_product_rule_args (
	rule_id
)
TABLESPACE gam_ind STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0 MAXEXTENTS unlimited)
/