Database setup - Rule-based groups

Create the database table manually before using rule based user groups.

The rule-based user groups feature stores the definitions of the rule-based user groups in a database table. This includes the name, rule, and description of the group. Use one of the following SQL statements to create the table, using a database and schema of the choice. Replace schema_name in the scripts with the schema name of the choice.

Syntax for DB2 and Derby databases:

CREATE TABLE schema_name.SOFTGROUPS
(
   ID INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   GROUPNAME VARCHAR(128) NOT NULL,
   RULE VARCHAR(300) NOT NULL,
   DESCRIPTION VARCHAR(512),
   LASTMODIFIED TIMESTAMP,
   PRIMARY KEY (ID),
   UNIQUE (GROUPNAME)
);
CREATE INDEX schema_name.SOFTGROUPSIX1 ON schema_name.SOFTGROUPS (LASTMODIFIED DESC);

Syntax for SQL databases:

CREATE TABLE schema_name.SOFTGROUPS
(
  ID INT NOT NULL IDENTITY PRIMARY KEY,
  GROUPNAME VARCHAR(128) NOT NULL UNIQUE,
  "RULE" VARCHAR(300) NOT NULL,
  DESCRIPTION VARCHAR(512),
  LASTMODIFIED DATETIME
);
 CREATE INDEX SOFTGROUPSIX1 
ON  schema_name.SOFTGROUPS(LASTMODIFIED DESC);
sp_indexoption 'schema_name.SOFTGROUPS', 'disallowpagelocks', TRUE;

Syntax for Oracle databases:

CREATE TABLE schema_name.SOFTGROUPS
(
  ID           INT,
  GROUPNAME    VARCHAR(128) NOT NULL,
  RULE         VARCHAR(300) NOT NULL,
  DESCRIPTION  VARCHAR(512),
  LASTMODIFIED TIMESTAMP,
  PRIMARY KEY  (ID),
  UNIQUE (GROUPNAME)     
);
 CREATE INDEX schema_name.SOFTGROUPSIX1 ON schema_name.SOFTGROUPS (LASTMODIFIED DESC);
 CREATE SEQUENCE softgroups_seq;
 CREATE TRIGGER softgroups_seq_trigger
  before INSERT ON schema_name.SOFTGROUPS
  FOR each ROW
BEGIN
    IF ( :new.id IS NULL ) THEN
      SELECT softgroups_seq.nextval
      INTO   :new.id
      FROM   dual;
    END IF;
END;
/

Oracle does not support auto-increment or identity feature directly as part of the ID column definition. Create a sequence and a trigger. For easy submission of the statement, make sure to add the final slash character (/). We can submit the statement by pressing the Enter key.

Parent Configure the rule-based user groups adapter