Fix #906 Oracle auto increment manual insert id (#1526)

atiertant · elhigu · commit 57a67cdadcb1 · 2016-09-12T10:52:14.000+03:00
* Oracle sequence trigger handle manual id inserts

* Update schema tests
diff --git a/src/dialects/oracle/schema/columncompiler.js b/src/dialects/oracle/schema/columncompiler.js
@@ -31,9 +31,17 @@ assign(ColumnCompiler_Oracle.prototype, {
       const createTriggerSQL =
         `create or replace trigger ${triggerName} before insert on ${tableName}` +
         ` for each row` +
-        ` when (new.${columnName} is null) ` +
+        ` declare` +
+        ` checking number := 1;` +
         ` begin` +
+        ` if (:new.${columnName} is null) then` +
+        ` while checking >= 1` +
+        ` loop` +
         ` select ${sequenceName}.nextval into :new.${columnName} from dual;` +
+        ` select count(${columnName}) into checking from ${tableName}` +
+        ` where ${columnName} = :new.${columnName};` +
+        ` end loop;` +
+        ` end if;` +
         ` end;`;
       this.pushQuery(utils.wrapSqlWithCatch(`create sequence ${sequenceName}`, -955));
       this.pushQuery(createTriggerSQL);
diff --git a/test/unit/schema/oracle.js b/test/unit/schema/oracle.js
@@ -19,7 +19,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.toSQL().length);
     expect(tableSql.toSQL()[0].sql).to.equal('create table "users" ("id" integer not null primary key, "email" varchar2(255))');
     expect(tableSql.toSQL()[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test basic create table if not exists', function() {
@@ -31,7 +31,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.toSQL().length);
     expect(tableSql.toSQL()[0].sql).to.equal("begin execute immediate 'create table \"users\" (\"id\" integer not null primary key, \"email\" varchar2(255))'; exception when others then if sqlcode != -955 then raise; end if; end;");
     expect(tableSql.toSQL()[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test drop table', function() {
@@ -209,7 +209,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.length);
     expect(tableSql[0].sql).to.equal('alter table "users" add "id" integer not null primary key');
     expect(tableSql[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test adding big incrementing id', function() {
@@ -220,7 +220,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.length);
     expect(tableSql[0].sql).to.equal('alter table "users" add "id" number(20, 0) not null primary key');
     expect(tableSql[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test rename column', function() {
diff --git a/test/unit/schema/oracledb.js b/test/unit/schema/oracledb.js
@@ -19,7 +19,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.toSQL().length);
     expect(tableSql.toSQL()[0].sql).to.equal('create table "users" ("id" integer not null primary key, "email" varchar2(255))');
     expect(tableSql.toSQL()[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test basic create table if not exists', function() {
@@ -31,7 +31,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.toSQL().length);
     expect(tableSql.toSQL()[0].sql).to.equal("begin execute immediate 'create table \"users\" (\"id\" integer not null primary key, \"email\" varchar2(255))'; exception when others then if sqlcode != -955 then raise; end if; end;");
     expect(tableSql.toSQL()[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql.toSQL()[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test drop table', function() {
@@ -209,7 +209,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.length);
     expect(tableSql[0].sql).to.equal('alter table "users" add "id" integer not null primary key');
     expect(tableSql[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test adding big incrementing id', function() {
@@ -220,7 +220,7 @@ describe("Oracle SchemaBuilder", function() {
     equal(3, tableSql.length);
     expect(tableSql[0].sql).to.equal('alter table "users" add "id" number(20, 0) not null primary key');
     expect(tableSql[1].sql).to.equal("begin execute immediate 'create sequence \"users_seq\"'; exception when others then if sqlcode != -955 then raise; end if; end;");
-    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row when (new.\"id\" is null)  begin select \"users_seq\".nextval into :new.\"id\" from dual; end;");
+    expect(tableSql[2].sql).to.equal("create or replace trigger \"users_id_trg\" before insert on \"users\" for each row declare checking number := 1; begin if (:new.\"id\" is null) then while checking >= 1 loop select \"users_seq\".nextval into :new.\"id\" from dual; select count(\"id\") into checking from \"users\" where \"id\" = :new.\"id\"; end loop; end if; end;");
   });
 
   it('test rename column', function() {
