[RFE] Update Oracle database schema

Summary

The documentation could provide a database schema for Oracle 12.1 (see above). This schema allows me to load the same schema for my production environment and my integration tests (using H2) with Flyway.

Actual Behavior

The current schema for Oracle still uses triggers to compute the next sequence value. This schema cannot be loaded by others databases during integration tests. This is not a big deal, but that could be improved.

Expected Behavior

A schema using more "standard" SQL statements.

Version

4.2.1

Sample

Here is the schema i used for my Oracle instance and my integration tests (using H2) :

CREATE SEQUENCE acl_sid_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TABLE acl_sid (
  id NUMBER(38) NOT NULL DEFAULT acl_sid_sequence.NEXTVAL PRIMARY KEY,
  principal NUMBER(1) NOT NULL CHECK (principal in (0, 1)),
  sid NVARCHAR2(100) NOT NULL,
  CONSTRAINT unique_acl_sid UNIQUE (sid, principal)
);

CREATE SEQUENCE acl_class_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TABLE acl_class (
  id NUMBER(38) NOT NULL DEFAULT acl_class_sequence.NEXTVAL PRIMARY KEY,
  class NVARCHAR2(100) NOT NULL,
  CONSTRAINT uk_acl_class UNIQUE (class)
);

CREATE SEQUENCE acl_object_identity_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TABLE acl_object_identity (
  id NUMBER(38) NOT NULL DEFAULT acl_object_identity_sequence.NEXTVAL PRIMARY KEY,
  object_id_class NUMBER(38) NOT NULL,
  object_id_identity NUMBER(38) NOT NULL,
  parent_object NUMBER(38),
  owner_sid NUMBER(38),
  entries_inheriting NUMBER(1) NOT NULL CHECK (entries_inheriting in (0, 1)),
  CONSTRAINT uk_acl_object_identity UNIQUE (object_id_class, object_id_identity),
  CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id),
  CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id),
  CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id)
);

CREATE SEQUENCE acl_entry_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TABLE acl_entry (
  id NUMBER(38) NOT NULL DEFAULT acl_entry_sequence.NEXTVAL PRIMARY KEY,
  acl_object_identity NUMBER(38) NOT NULL,
  ace_order INTEGER NOT NULL,
  sid NUMBER(38) NOT NULL,
  mask INTEGER NOT NULL,
  granting NUMBER(1) NOT NULL CHECK (granting in (0, 1)),
  audit_success NUMBER(1) NOT NULL CHECK (audit_success in (0, 1)),
  audit_failure NUMBER(1) NOT NULL CHECK (audit_failure in (0, 1)),
  CONSTRAINT unique_acl_entry UNIQUE (acl_object_identity, ace_order),
  CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id),
  CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id)
);

Start by locating the current Oracle schema and its trigger-based sequence definitions, then compare them with the SQL schema provided in the issue. Done means providing an Oracle 12.1 schema that uses standard SQL statements and can be loaded for both Oracle production and H2 integration tests.