Enrich DDL with Annotations/Metadata to improve database automation capabilities

Loïc Lefèvre-Oracle

If I make the parallel with the evolution of the Java development experience, I can see tremendous productivity improvement with the Annotation concept (see https://en.wikipedia.org/wiki/Java_annotation).

Indeed, using Annotations such as:

@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)

@DiscriminatorColumn(name="TRANSACTION_TYPE_CODE",discriminatorType=DiscriminatorType.STRING)

@Table(schema = "COST_SHARE", name = "CS_TRANSACTION")

public class Vehicle extends MyDefaultVehicle implements Serializable{

    @Persistent

    protected String vehicleName = null;

    @Getter

    public String getVehicleName() {

        return this.vehicleName;

    }

    public void setVehicleName(@Optional vehicleName) {

        this.vehicleName = vehicleName;

    }

  @Override

    public List addVehicleNameToList(List names) {

        @Optional

        List localNames = names;

        if(localNames == null) {

            localNames = new ArrayList();

        }

        localNames.add(getVehicleName());

        return localNames;

    }

}

can enrich greatly metadata and be also used to generate code which is extremely powerful regarding productivity and simplification (less errors).

Hence such concept of annotation could/should bring same advantages in the Database world.

I can imagine following annotations defined at design/modeling time by developers or Application DBA but the possibilities are infinite.

@ExpectedRows('Production', 1000000)

@ExpectedRows('Development', 200)

@ExpectedNewRows(1000,PER_DAY)

create table customer (

@PK

ID NUMBER,

@Distinct_Values(10)

Country Varchar2(40),

...

...

@ILM_Policy(Compress QUERY HIGH, < sysdate - 365 )

Last_Modification Date,

@Encrypt(AES192)

@Redact(MASKING_RULE_NAME, module = 'support')

Credit_Card_Number Varchar2(16) not null,

...

);

issueing such statement could:

1/ send me warnings telling me that I haven't the rights to configure column encryption because of missing privilege or that it is the first time I'm using on this database DB option Advanced Security and prompt for feedback ("Are you sure you want to activate ASO?")

2/ following the usual message, gives me advises:

TABLE CREATED

Advises:

- you could partition this table in production by interval (Last_Modification) and then subpartition by list (Country)

- you could use Advanced Compression for ILM policy instead of QUERY HIGH since you don't have access to HCC compression (no Oracle storage available)

3/ and generate/execute statements (PL/SQL for instance) for configuring ILM policy, data redaction rule, put the ID column as not nullable (and warn me)

...

Possibilities are infinite and could boost productivity.

ctrieb

Yes, this will be helpfull, but not prio 1.

Mike Kutz

Should one be using a data modeling tool to do this?

(eg SQL*Developer Data Modeler)

William Robertson

Should one be using a data modeling tool to do this?

(eg SQL*Developer Data Modeler)

Mike Kutz wrote:

Should one be using a data modeling tool to do this? (eg SQL*Developer Data Modeler)

There are many data modelling tools (although as far as I can tell, no good ones - I haven't tried the new SQL Dev one yet though). I think the idea here is that these annotations would be embedded with the object for use by any connected application. I agree a modelling tool is a good place for expected volumes though.

William Robertson

"@PK" would be redundant because the primary key would already be declared and so the information is already available from the dictionary. Likewise with things like compression, which are surely already defined through standard syntax.

I'm not sure about the expected rowcount examples. I can't help thinking it will end up wildly inaccurate in many cases, and there is some overlap with the existing statistics model in that you can already set num_rows to 1000000, num_distinct to 10 etc.

Mike Kutz

If you go by that route of logic, then the "ILM_Policy" would be redundant for 12c and above.

And the example is missing a "data audit trail" tag that would be redundant due to Flashback Data Archive which is included on 11.2.0.4 and above.

You should be able to access that information via data dictionaries.