[sqlite]`SqliteQueryRunner#dropColumns` fails when a table has check constraints

[uki00a]

Issue type:

[ ] question [x] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [x] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

How to reproduce

See: /test/functional/query-runner/drop-column.ts

Summary

This problem is caused by behavioral differences between deno-sqlite and node-sqlite3.

• The following code does not work:

import {
  open
} from "./vendor/https/deno.land/x/sqlite/mod.ts"

const db = await open(':memory:')
const sql = 'CREATE TABLE "temporary_post" ("text" varchar NOT NULL, "tag" varchar NOT NULL, CONSTRAINT "UQ_12e79261329cf680e4e4db3cc0d" UNIQUE ("text", "tag"), CONSTRAINT "CHK_68ac5e061fa2568e3140a952e5" CHECK (("version" < 999)))';

db.query(sql); // An error occurs here.

• The following code works properly:

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database(':memory:');
const sql = 'CREATE TABLE "temporary_post" ("text" varchar NOT NULL, "tag" varchar NOT NULL, CONSTRAINT "UQ_12e79261329cf680e4e4db3cc0d" UNIQUE ("text", "tag"), CONSTRAINT "CHK_68ac5e061fa2568e3140a952e5" CHECK (("version" < 999)))';
db.all(sql, (err, data) => {
  if (err) {
    console.error(err);
  } else {
    console.log(data);
  }
});

Log output of /test/functional/query-runner/drop-column.ts

  query runner > drop column
query: PRAGMA foreign_keys = OFF;
query: BEGIN TRANSACTION
query: SELECT 'DROP VIEW "' || name || '";' as query FROM "sqlite_master" WHERE "type" = 'view'
query: SELECT 'DROP TABLE "' || name || '";' as query FROM "sqlite_master" WHERE "type" = 'table' AND "name" != 'sqlite_sequence'
query: COMMIT
query: PRAGMA foreign_keys = ON;
query: BEGIN TRANSACTION
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('book', 'book2', 'faculty', 'photo', 'post', 'student', 'teacher')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('book', 'book2', 'faculty', 'photo', 'post', 'student', 'teacher')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata'
query: CREATE TABLE "book" ("ean" varchar PRIMARY KEY NOT NULL)
query: CREATE TABLE "book2" ("ean" varchar PRIMARY KEY NOT NULL) WITHOUT ROWID
query: CREATE TABLE "faculty" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar NOT NULL)
query: CREATE TABLE "photo" ("id" integer PRIMARY KEY NOT NULL, "name" varchar NOT NULL, "tag" varchar NOT NULL, "description" varchar NOT NULL, "text" varchar NOT NULL, CONSTRAINT "UQ_5e12f79f234f4458a1647250247" UNIQUE ("description"), CONSTRAINT "UQ_1fd23a1fd90eeaef249327f6620" UNIQUE ("name"))
query: CREATE UNIQUE INDEX "IDX_a8748c57ceb67d0dc55f93e4b6" ON "photo" ("tag") 
query: CREATE UNIQUE INDEX "IDX_8c8d1507b95a3121921432600c" ON "photo" ("text") 
query: CREATE TABLE "post" ("id" integer PRIMARY KEY NOT NULL, "version" integer NOT NULL, "name" varchar NOT NULL DEFAULT ('My post'), "text" varchar NOT NULL, "tag" varchar NOT NULL, CONSTRAINT "UQ_d7c82163ac258e5d18d52d0fe16" UNIQUE ("version"), CONSTRAINT "UQ_12e79261329cf680e4e4db3cc0d" UNIQUE ("text", "tag"), CONSTRAINT "CHK_68ac5e061fa2568e3140a952e5" CHECK ("version" < 999))
query: CREATE TABLE "student" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar NOT NULL, "facultyId" integer, "teacherId" integer)
query: CREATE INDEX "student_name_index" ON "student" ("name") 
query: CREATE TABLE "teacher" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar NOT NULL)
query: DROP INDEX "student_name_index"
query: CREATE TABLE "temporary_student" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar NOT NULL, "facultyId" integer, "teacherId" integer, CONSTRAINT "FK_c2d85c74bcbf0c516b69674b94d" FOREIGN KEY ("facultyId") REFERENCES "faculty" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT "FK_f4481746c56ffa6cf77829a4bcc" FOREIGN KEY ("teacherId") REFERENCES "teacher" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)
query: INSERT INTO "temporary_student"("id", "name", "facultyId", "teacherId") SELECT "id", "name", "facultyId", "teacherId" FROM "student"
query: SELECT last_insert_rowid()
query: DROP TABLE "student"
query: ALTER TABLE "temporary_student" RENAME TO "student"
query: CREATE INDEX "student_name_index" ON "student" ("name") 
query: COMMIT
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('post')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('post')
query: PRAGMA table_info("post")
query: PRAGMA index_list("post")
query: PRAGMA foreign_key_list("post")
query: PRAGMA index_info("sqlite_autoindex_post_2")
query: PRAGMA index_info("sqlite_autoindex_post_1")
query: CREATE TABLE "temporary_post" ("text" varchar NOT NULL, "tag" varchar NOT NULL, CONSTRAINT "UQ_12e79261329cf680e4e4db3cc0d" UNIQUE ("text", "tag"), CONSTRAINT "CHK_68ac5e061fa2568e3140a952e5" CHECK (("version" < 999)))
query failed: CREATE TABLE "temporary_post" ("text" varchar NOT NULL, "tag" varchar NOT NULL, CONSTRAINT "UQ_12e79261329cf680e4e4db3cc0d" UNIQUE ("text", "tag"), CONSTRAINT "CHK_68ac5e061fa2568e3140a952e5" CHECK (("version" < 999)))
error: SqliteError: no such column: version
    at DB._error (https://deno.land/x/sqlite@d451a28e55180730a296a9383cd5dd26155a2c11/src/db.js:252:16)
    at DB.query (https://deno.land/x/sqlite@d451a28e55180730a296a9383cd5dd26155a2c11/src/db.js:112:18)
    at run (SqliteQueryRunner.ts:58:49)
    at SqliteQueryRunner.query (SqliteQueryRunner.ts:69:28)
    1) should correctly drop column and revert drop