Code:
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)
/