knex-schema-inspector - npm Package Compare versions

Comparing version 1.5.7 to 1.5.8

dist/dialects/oracledb.d.ts

@@ -18,3 +18,3 @@ import { Knex } from 'knex';
     REFERENCED_COLUMN_NAME: string | null;
-    CONSTRAINT_TYPE: 'P' | 'U' | null;
+    CONSTRAINT_TYPE: 'P' | 'U' | 'R' | null;
     VIRTUAL_COLUMN: 'YES' | 'NO';
@@ -21,0 +21,0 @@ IDENTITY_COLUMN: 'YES' | 'NO';

dist/dialects/oracledb.js

@@ -75,3 +75,3 @@ "use strict";
                     case 0: return [4 /*yield*/, this.knex
-                            .select('TABLE_NAME')
+                            .select('TABLE_NAME as name')
                             .from('USER_TABLES')];
@@ -81,4 +81,4 @@ case 1:
                         return [2 /*return*/, records.map(function (_a) {
-                                var TABLE_NAME = _a.TABLE_NAME;
-                                return TABLE_NAME;
+                                var name = _a.name;
+                                return name;
                             })];
@@ -91,24 +91,14 @@ }
         return __awaiter(this, void 0, void 0, function () {
-            var query, rawTable, records;
+            var query;
             return __generator(this, function (_a) {
                 switch (_a.label) {
                     case 0:
-                        query = this.knex.select('TABLE_NAME').from('USER_TABLES');
+                        query = this.knex
+                            .select('TABLE_NAME as name')
+                            .from('USER_TABLES');
                         if (!table) return [3 /*break*/, 2];
-                        return [4 /*yield*/, query
-                                .andWhere({ TABLE_NAME: table })
-                                .first()];
-                    case 1:
-                        rawTable = _a.sent();
-                        return [2 /*return*/, {
-                                name: rawTable.TABLE_NAME,
-                            }];
+                        return [4 /*yield*/, query.andWhere({ TABLE_NAME: table }).first()];
+                    case 1: return [2 /*return*/, _a.sent()];
                     case 2: return [4 /*yield*/, query];
-                    case 3:
-                        records = _a.sent();
-                        return [2 /*return*/, records.map(function (rawTable) {
-                                return {
-                                    name: rawTable.TABLE_NAME,
-                                };
-                            })];
+                    case 3: return [2 /*return*/, _a.sent()];
                 }
@@ -133,3 +123,3 @@ });
                         result = _a.sent();
-                        return [2 /*return*/, (result && result.count === 1) || false];
+                        return [2 /*return*/, !!(result === null || result === void 0 ? void 0 : result.count)];
                 }
@@ -146,3 +136,3 @@ });
         return __awaiter(this, void 0, void 0, function () {
-            var query, records;
+            var query;
             return __generator(this, function (_a) {
@@ -152,3 +142,3 @@ switch (_a.label) {
                         query = this.knex
-                            .select('TABLE_NAME', 'COLUMN_NAME')
+                            .select('TABLE_NAME as table', 'COLUMN_NAME as column')
                             .from('USER_TAB_COLS');
@@ -159,11 +149,3 @@ if (table) {
                         return [4 /*yield*/, query];
-                    case 1:
-                        records = _a.sent();
-                        return [2 /*return*/, records.map(function (_a) {
-                                var TABLE_NAME = _a.TABLE_NAME, COLUMN_NAME = _a.COLUMN_NAME;
-                                return ({
-                                    table: TABLE_NAME,
-                                    column: COLUMN_NAME,
-                                });
-                            })];
+                    case 1: return [2 /*return*/, _a.sent()];
                 }
@@ -180,5 +162,4 @@ });
                         query = this.knex
-                            .with('uc', this.knex.raw('SELECT "TABLE_NAME", "CONSTRAINT_NAME", "R_CONSTRAINT_NAME", "CONSTRAINT_TYPE" FROM "USER_CONSTRAINTS"'))
-                            .with('ucc', this.knex.raw('SELECT "TABLE_NAME", "COLUMN_NAME", "CONSTRAINT_NAME" FROM "USER_CONS_COLUMNS"'))
-                            .select('c.TABLE_NAME', 'c.COLUMN_NAME', 'c.DATA_DEFAULT', 'c.DATA_TYPE', 'c.DATA_LENGTH', 'c.DATA_PRECISION', 'c.DATA_SCALE', 'c.NULLABLE', 'c.IDENTITY_COLUMN', 'c.VIRTUAL_COLUMN', 'cm.COMMENTS as COLUMN_COMMENT', 'pk.CONSTRAINT_TYPE', 'fk.REFERENCED_TABLE_NAME', 'fk.REFERENCED_COLUMN_NAME')
+                            .with('uc', this.knex.raw("\n          SELECT /*+ materialize */ DISTINCT\n            \"uc\".\"TABLE_NAME\",\n            \"ucc\".\"COLUMN_NAME\",\n            \"uc\".\"CONSTRAINT_NAME\",\n            \"uc\".\"CONSTRAINT_TYPE\",\n            \"uc\".\"R_CONSTRAINT_NAME\"\n          FROM \"USER_CONSTRAINTS\" \"uc\"\n            INNER JOIN \"USER_CONS_COLUMNS\" \"ucc\" ON \"uc\".\"CONSTRAINT_NAME\" = \"ucc\".\"CONSTRAINT_NAME\"\n            AND \"uc\".\"CONSTRAINT_TYPE\" IN ('P', 'U', 'R')\n        "))
+                            .select('c.TABLE_NAME', 'c.COLUMN_NAME', 'c.DATA_DEFAULT', 'c.DATA_TYPE', 'c.DATA_LENGTH', 'c.DATA_PRECISION', 'c.DATA_SCALE', 'c.NULLABLE', 'c.IDENTITY_COLUMN', 'c.VIRTUAL_COLUMN', 'cm.COMMENTS as COLUMN_COMMENT', 'ct.CONSTRAINT_TYPE', 'fk.TABLE_NAME as REFERENCED_TABLE_NAME', 'fk.COLUMN_NAME as REFERENCED_COLUMN_NAME')
                             .from('USER_TAB_COLS as c')
@@ -189,4 +170,7 @@ .leftJoin('USER_COL_COMMENTS as cm', {
                         })
-                            .leftJoin(this.knex.raw("\n        (\n          SELECT\n            \"uc\".\"CONSTRAINT_TYPE\",\n            \"uc\".\"TABLE_NAME\",\n            \"cc\".\"COLUMN_NAME\"\n          FROM\n            \"uc\"\n            INNER JOIN \"ucc\" \"cc\" ON \"uc\".\"CONSTRAINT_NAME\" = \"cc\".\"CONSTRAINT_NAME\"\n          WHERE\n            \"uc\".\"CONSTRAINT_TYPE\" IN ('P', 'U')\n        ) \"pk\" ON \"c\".\"TABLE_NAME\" = \"pk\".\"TABLE_NAME\" AND \"c\".\"COLUMN_NAME\" = \"pk\".\"COLUMN_NAME\"\n      "))
-                            .leftJoin(this.knex.raw("\n        (\n          SELECT\n            \"uc\".\"TABLE_NAME\",\n            \"cc\".\"COLUMN_NAME\",\n            \"rc\".\"TABLE_NAME\" AS \"REFERENCED_TABLE_NAME\",\n            \"rc\".\"COLUMN_NAME\" AS \"REFERENCED_COLUMN_NAME\"\n          FROM\n            \"uc\"\n            INNER JOIN \"ucc\" \"cc\" ON \"uc\".\"CONSTRAINT_NAME\" = \"cc\".\"CONSTRAINT_NAME\"\n            INNER JOIN \"ucc\" \"rc\" ON \"uc\".\"R_CONSTRAINT_NAME\" = \"rc\".\"CONSTRAINT_NAME\"\n          WHERE\n            \"uc\".\"CONSTRAINT_TYPE\" = 'R'\n        ) \"fk\" ON \"c\".\"TABLE_NAME\" = \"fk\".\"TABLE_NAME\" AND \"c\".\"COLUMN_NAME\" = \"fk\".\"COLUMN_NAME\"\n      "));
+                            .leftJoin('uc as ct', {
+                            'c.TABLE_NAME': 'ct.TABLE_NAME',
+                            'c.COLUMN_NAME': 'ct.COLUMN_NAME',
+                        })
+                            .leftJoin('uc as fk', 'ct.R_CONSTRAINT_NAME', 'fk.CONSTRAINT_NAME');
                         if (table) {
@@ -197,6 +181,9 @@ query.where({ 'c.TABLE_NAME': table });
                         return [4 /*yield*/, query
-                                .andWhere({ 'c.COLUMN_NAME': column })
-                                .first()];
+                                .andWhere({
+                                'c.COLUMN_NAME': column,
+                            })
+                                // NOTE: .first() is signifigantly slower on this query
+                                .andWhereRaw('rownum = 1')];
                     case 1:
-                        rawColumn = _a.sent();
+                        rawColumn = (_a.sent())[0];
                         return [2 /*return*/, rawColumnToColumn(rawColumn)];
@@ -216,13 +203,17 @@ case 2: return [4 /*yield*/, query];
         return __awaiter(this, void 0, void 0, function () {
-            var count;
+            var result;
             return __generator(this, function (_a) {
-                count = this.knex
-                    .count({ count: '*' })
-                    .from('USER_TAB_COLS')
-                    .where({
-                    TABLE_NAME: table,
-                    COLUMN_NAME: column,
-                })
-                    .first().count;
-                return [2 /*return*/, !!count];
+                switch (_a.label) {
+                    case 0: return [4 /*yield*/, this.knex
+                            .count({ count: '*' })
+                            .from('USER_TAB_COLS')
+                            .where({
+                            TABLE_NAME: table,
+                            COLUMN_NAME: column,
+                        })
+                            .first()];
+                    case 1:
+                        result = _a.sent();
+                        return [2 /*return*/, !!(result === null || result === void 0 ? void 0 : result.count)];
+                }
             });
@@ -235,18 +226,19 @@ });
     oracleDB.prototype.primary = function (table) {
+        var _a;
         return __awaiter(this, void 0, void 0, function () {
             var result;
-            return __generator(this, function (_a) {
-                switch (_a.label) {
+            return __generator(this, function (_b) {
+                switch (_b.label) {
                     case 0: return [4 /*yield*/, this.knex
                             .select('cc.COLUMN_NAME')
                             .from('USER_CONSTRAINTS as uc')
-                            .leftJoin('USER_CONS_COLUMNS as cc', 'uc.CONSTRAINT_NAME', 'cc.CONSTRAINT_NAME')
+                            .join('USER_CONS_COLUMNS as cc', 'uc.CONSTRAINT_NAME', 'cc.CONSTRAINT_NAME')
                             .where({
+                            'uc.TABLE_NAME': table,
                             'uc.CONSTRAINT_TYPE': 'P',
-                            'uc.TABLE_NAME': table,
                         })
                             .first()];
                     case 1:
-                        result = _a.sent();
-                        return [2 /*return*/, result ? result.COLUMN_NAME : null];
+                        result = _b.sent();
+                        return [2 /*return*/, (_a = result === null || result === void 0 ? void 0 : result.COLUMN_NAME) !== null && _a !== void 0 ? _a : null];
                 }
@@ -265,3 +257,3 @@ });
                         query = this.knex
-                            .with('ucc', this.knex.raw('SELECT "TABLE_NAME", "COLUMN_NAME", "CONSTRAINT_NAME" FROM "USER_CONS_COLUMNS"'))
+                            .with('ucc', this.knex.raw('SELECT /*+ materialize */ "TABLE_NAME", "COLUMN_NAME", "CONSTRAINT_NAME" FROM "USER_CONS_COLUMNS"'))
                             .select('uc.TABLE_NAME as table', 'cc.COLUMN_NAME as column', 'rcc.TABLE_NAME as foreign_key_table', 'rcc.COLUMN_NAME as foreign_key_column', 'uc.CONSTRAINT_NAME as constraint_name', this.knex.raw('NULL as "on_update"'), 'uc.DELETE_RULE as on_delete')
@@ -268,0 +260,0 @@ .from('USER_CONSTRAINTS as uc')

lib/dialects/oracledb.ts

@@ -7,6 +7,2 @@ import { Knex } from 'knex';
 
-type RawTable = {
-  TABLE_NAME: string;
-};
-
 type RawColumn = {
@@ -24,3 +20,3 @@ TABLE_NAME: string;
   REFERENCED_COLUMN_NAME: string | null;
-  CONSTRAINT_TYPE: 'P' | 'U' | null;
+  CONSTRAINT_TYPE: 'P' | 'U' | 'R' | null;
   VIRTUAL_COLUMN: 'YES' | 'NO';
@@ -63,7 +59,7 @@ IDENTITY_COLUMN: 'YES' | 'NO';
    */
-  async tables() {
+  async tables(): Promise<string[]> {
     const records = await this.knex
-      .select<{ TABLE_NAME: string }[]>('TABLE_NAME')
+      .select<Table[]>('TABLE_NAME as name')
       .from('USER_TABLES');
-    return records.map(({ TABLE_NAME }) => TABLE_NAME);
+    return records.map(({ name }) => name);
   }
@@ -78,21 +74,11 @@
   async tableInfo<T>(table?: string) {
-    const query = this.knex.select('TABLE_NAME').from('USER_TABLES');
+    const query = this.knex
+      .select<Table[]>('TABLE_NAME as name')
+      .from('USER_TABLES');
 
     if (table) {
-      const rawTable: RawTable = await query
-        .andWhere({ TABLE_NAME: table })
-        .first();
-
-      return {
-        name: rawTable.TABLE_NAME,
-      } as T extends string ? Table : Table[];
+      return await query.andWhere({ TABLE_NAME: table }).first();
     }
 
-    const records: RawTable[] = await query;
-
-    return records.map((rawTable): Table => {
-      return {
-        name: rawTable.TABLE_NAME,
-      };
-    }) as T extends string ? Table : Table[];
+    return await query;
   }
@@ -109,3 +95,3 @@
       .first();
-    return (result && result.count === 1) || false;
+    return !!result?.count;
   }
@@ -121,5 +107,5 @@
     const query = this.knex
-      .select<{ TABLE_NAME: string; COLUMN_NAME: string }[]>(
-        'TABLE_NAME',
-        'COLUMN_NAME'
+      .select<{ table: string; column: string }[]>(
+        'TABLE_NAME as table',
+        'COLUMN_NAME as column'
       )
@@ -132,8 +118,3 @@ .from('USER_TAB_COLS');
 
-    const records = await query;
-
-    return records.map(({ TABLE_NAME, COLUMN_NAME }) => ({
-      table: TABLE_NAME,
-      column: COLUMN_NAME,
-    }));
+    return await query;
   }
@@ -151,13 +132,15 @@
         'uc',
-        this.knex.raw(
-          'SELECT "TABLE_NAME", "CONSTRAINT_NAME", "R_CONSTRAINT_NAME", "CONSTRAINT_TYPE" FROM "USER_CONSTRAINTS"'
-        )
+        this.knex.raw(`
+          SELECT /*+ materialize */ DISTINCT
+            "uc"."TABLE_NAME",
+            "ucc"."COLUMN_NAME",
+            "uc"."CONSTRAINT_NAME",
+            "uc"."CONSTRAINT_TYPE",
+            "uc"."R_CONSTRAINT_NAME"
+          FROM "USER_CONSTRAINTS" "uc"
+            INNER JOIN "USER_CONS_COLUMNS" "ucc" ON "uc"."CONSTRAINT_NAME" = "ucc"."CONSTRAINT_NAME"
+            AND "uc"."CONSTRAINT_TYPE" IN ('P', 'U', 'R')
+        `)
       )
-      .with(
-        'ucc',
-        this.knex.raw(
-          'SELECT "TABLE_NAME", "COLUMN_NAME", "CONSTRAINT_NAME" FROM "USER_CONS_COLUMNS"'
-        )
-      )
-      .select(
+      .select<RawColumn[]>(
         'c.TABLE_NAME',
@@ -174,5 +157,5 @@ 'c.COLUMN_NAME',
         'cm.COMMENTS as COLUMN_COMMENT',
-        'pk.CONSTRAINT_TYPE',
-        'fk.REFERENCED_TABLE_NAME',
-        'fk.REFERENCED_COLUMN_NAME'
+        'ct.CONSTRAINT_TYPE',
+        'fk.TABLE_NAME as REFERENCED_TABLE_NAME',
+        'fk.COLUMN_NAME as REFERENCED_COLUMN_NAME'
       )
@@ -184,34 +167,7 @@ .from('USER_TAB_COLS as c')
       })
-      .leftJoin(
-        this.knex.raw(`
-        (
-          SELECT
-            "uc"."CONSTRAINT_TYPE",
-            "uc"."TABLE_NAME",
-            "cc"."COLUMN_NAME"
-          FROM
-            "uc"
-            INNER JOIN "ucc" "cc" ON "uc"."CONSTRAINT_NAME" = "cc"."CONSTRAINT_NAME"
-          WHERE
-            "uc"."CONSTRAINT_TYPE" IN ('P', 'U')
-        ) "pk" ON "c"."TABLE_NAME" = "pk"."TABLE_NAME" AND "c"."COLUMN_NAME" = "pk"."COLUMN_NAME"
-      `)
-      )
-      .leftJoin(
-        this.knex.raw(`
-        (
-          SELECT
-            "uc"."TABLE_NAME",
-            "cc"."COLUMN_NAME",
-            "rc"."TABLE_NAME" AS "REFERENCED_TABLE_NAME",
-            "rc"."COLUMN_NAME" AS "REFERENCED_COLUMN_NAME"
-          FROM
-            "uc"
-            INNER JOIN "ucc" "cc" ON "uc"."CONSTRAINT_NAME" = "cc"."CONSTRAINT_NAME"
-            INNER JOIN "ucc" "rc" ON "uc"."R_CONSTRAINT_NAME" = "rc"."CONSTRAINT_NAME"
-          WHERE
-            "uc"."CONSTRAINT_TYPE" = 'R'
-        ) "fk" ON "c"."TABLE_NAME" = "fk"."TABLE_NAME" AND "c"."COLUMN_NAME" = "fk"."COLUMN_NAME"
-      `)
-      );
+      .leftJoin('uc as ct', {
+        'c.TABLE_NAME': 'ct.TABLE_NAME',
+        'c.COLUMN_NAME': 'ct.COLUMN_NAME',
+      })
+      .leftJoin('uc as fk', 'ct.R_CONSTRAINT_NAME', 'fk.CONSTRAINT_NAME');
 
@@ -223,5 +179,8 @@ if (table) {
     if (column) {
-      const rawColumn: RawColumn = await query
-        .andWhere({ 'c.COLUMN_NAME': column })
-        .first();
+      const [rawColumn] = await query
+        .andWhere({
+          'c.COLUMN_NAME': column,
+        })
+        // NOTE: .first() is signifigantly slower on this query
+        .andWhereRaw('rownum = 1');
 
@@ -240,3 +199,3 @@ return rawColumnToColumn(rawColumn);
   async hasColumn(table: string, column: string): Promise<boolean> {
-    const { count } = this.knex
+    const result = await this.knex
       .count<{ count: 0 | 1 }>({ count: '*' })
@@ -249,3 +208,3 @@ .from('USER_TAB_COLS')
       .first();
-    return !!count;
+    return !!result?.count;
   }
@@ -256,3 +215,2 @@
    */
-
   async primary(table: string): Promise<string> {
@@ -262,3 +220,3 @@ const result = await this.knex
       .from('USER_CONSTRAINTS as uc')
-      .leftJoin(
+      .join(
         'USER_CONS_COLUMNS as cc',
@@ -269,7 +227,8 @@ 'uc.CONSTRAINT_NAME',
       .where({
+        'uc.TABLE_NAME': table,
         'uc.CONSTRAINT_TYPE': 'P',
-        'uc.TABLE_NAME': table,
       })
       .first();
-    return result ? result.COLUMN_NAME : null;
+
+    return result?.COLUMN_NAME ?? null;
   }
@@ -280,3 +239,3 @@
 
-  async foreignKeys(table?: string) {
+  async foreignKeys(table?: string): Promise<ForeignKey[]> {
     const query = this.knex
@@ -286,3 +245,3 @@ .with(
         this.knex.raw(
-          'SELECT "TABLE_NAME", "COLUMN_NAME", "CONSTRAINT_NAME" FROM "USER_CONS_COLUMNS"'
+          'SELECT /*+ materialize */ "TABLE_NAME", "COLUMN_NAME", "CONSTRAINT_NAME" FROM "USER_CONS_COLUMNS"'
         )
@@ -289,0 +248,0 @@ )

package.json

 {
   "name": "knex-schema-inspector",
-  "version": "1.5.7",
+  "version": "1.5.8",
   "description": "Utility for extracting information about existing DB schema",
@@ -5,0 +5,0 @@ "main": "dist/index.js",

test/oracle.spec.ts

@@ -484,2 +484,11 @@ import knex, { Knex } from 'knex';
 
+  describe('.hasColumn', () => {
+    it('returns has column', async () => {
+      expect(await inspector.hasColumn('TEAMS', 'ID')).to.equal(true);
+      expect(await inspector.hasColumn('PAGE_VISITS', 'INVALID')).to.equal(
+        false
+      );
+    });
+  });
+
   describe('.primary', () => {
@@ -486,0 +495,0 @@ it('returns primary key for a table', async () => {

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Worsened metrics

Total package byte prevSize

289574

decreased by-0.59%

Lines of code

6279

decreased by-0.6%

No dependency changes