oracle 12.1 autoincrement feature

[brettmc]

Oracle 12.1 introduced the IDENTITY feature, which is some syntactic sugar around sequence/default, and could be used instead of the current 'create sequence' and 'create trigger' implementation:

CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );

(see https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1)

I'd like to implement this feature, but just wanted to run the idea past the maintainers before I start coding anything. Using the postgresql platform as a guide, I'm imagining that I would:

• create Oracle121Platform which extends OraclePlatform
• override OraclePlatform's various numeric type declaration SQL methods to check for autoincrement
• stub out getCreateAutoincrementSql and other methods which create sequences and triggers

In some quick playing with just hacking some changes into OraclePlatform, I was able to generate: CREATE TABLE test_table(id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL, type VARCHAR2(32) NOT NULL); which can be created and works as expected in an Oracle 12.1 db.

Is there anything else I should consider, aside from unit tests? :)

[Ocramius]

create Oracle121Platform which extends OraclePlatform

Sounds like yet another platform to add - a bit messy for just one tiny feature that already works via existing sequences.

[brettmc]

couple of minor points:

• identity columns are a performance win against sequence+trigger (I've seen numbers suggesting 10x faster)
• generated schema is a lot neater (I use doctrine as a tool to version-control and generate schemas against multiple database implementations)

also, there are other features in recent oracle versions that might be useful (although I wouldn't call any of them "killer" features):

• "is json" check constraints (which could be used on json types)