knex-schema-inspector - npm Package Compare versions

Comparing version 1.6.4 to 1.6.5

dist/dialects/mssql.js

@@ -249,5 +249,4 @@ "use strict";
                             .joinRaw("LEFT JOIN [sys].[foreign_key_columns] AS [fk] ON [fk].[parent_object_id] = [c].[object_id] AND [fk].[parent_column_id] = [c].[column_id]")
-                            .joinRaw("LEFT JOIN (\n          SELECT\n            [ic].[object_id],\n            [ic].[column_id],\n            [ix].[is_unique],\n            [ix].[is_primary_key],\n            ROW_NUMBER() OVER (\n              PARTITION BY [ic].[object_id],\n                [ic].[column_id] ORDER BY [ix].[is_primary_key] DESC,\n                [ix].[is_unique] DESC\n              ) AS index_priority\n          FROM\n            [sys].[index_columns] [ic]\n          JOIN [sys].[indexes] AS [ix] ON [ix].[object_id] = [ic].[object_id]\n            AND [ix].[index_id] = [ic].[index_id]\n        ) AS [i] ON [i].[object_id] = [c].[object_id] AND [i].[column_id] = [c].[column_id]")
-                            .where({ 's.name': this.schema })
-                            .andWhereRaw("ISNULL([i].index_priority, 1) = 1");
+                            .joinRaw("LEFT JOIN (\n          SELECT\n            [ic].[object_id],\n            [ic].[column_id],\n            [ix].[is_unique],\n            [ix].[is_primary_key],\n            MAX([ic].[index_column_id]) OVER(partition by [ic].[index_id], [ic].[object_id]) AS index_column_count,\n            ROW_NUMBER() OVER (\n              PARTITION BY [ic].[object_id], [ic].[column_id]\n              ORDER BY [ix].[is_primary_key] DESC, [ix].[is_unique] DESC\n            ) AS index_priority\n          FROM\n            [sys].[index_columns] [ic]\n          JOIN [sys].[indexes] AS [ix] ON [ix].[object_id] = [ic].[object_id]\n            AND [ix].[index_id] = [ic].[index_id]\n        ) AS [i]\n        ON [i].[object_id] = [c].[object_id]\n        AND [i].[column_id] = [c].[column_id]\n        AND ISNULL([i].[index_column_count], 1) = 1\n        AND ISNULL([i].[index_priority], 1) = 1")
+                            .where({ 's.name': this.schema });
                         if (table) {
@@ -254,0 +253,0 @@ query.andWhere({ 'o.name': table });

dist/dialects/oracledb.js

@@ -160,4 +160,4 @@ "use strict";
                         query = this.knex
-                            .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')
+                            .with('uc', this.knex.raw("\n          SELECT /*+ materialize */\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 (\n            SELECT\n              \"COLUMN_NAME\",\n              \"CONSTRAINT_NAME\",\n              COUNT(*) OVER(PARTITION BY \"CONSTRAINT_NAME\") \"INDEX_COLUMN_COUNT\"\n            FROM \"USER_CONS_COLUMNS\"\n          ) \"ucc\"\n            ON \"uc\".\"CONSTRAINT_NAME\" = \"ucc\".\"CONSTRAINT_NAME\"\n            AND \"uc\".\"CONSTRAINT_TYPE\" IN ('P', 'U', 'R')\n            AND \"ucc\".\"INDEX_COLUMN_COUNT\" = 1\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', this.knex.raw('COALESCE("ct"."CONSTRAINT_TYPE", "uct"."CONSTRAINT_TYPE") AS "CONSTRAINT_TYPE"'), 'fk.TABLE_NAME as REFERENCED_TABLE_NAME', 'fk.COLUMN_NAME as REFERENCED_COLUMN_NAME')
                             .from('USER_TAB_COLS as c')
@@ -168,6 +168,4 @@ .leftJoin('USER_COL_COMMENTS as cm', {
                         })
-                            .leftJoin('uc as ct', {
-                            'c.TABLE_NAME': 'ct.TABLE_NAME',
-                            'c.COLUMN_NAME': 'ct.COLUMN_NAME',
-                        })
+                            .joinRaw("LEFT JOIN \"uc\" \"ct\"\n          ON \"c\".\"TABLE_NAME\" = \"ct\".\"TABLE_NAME\"\n          AND \"c\".\"COLUMN_NAME\" = \"ct\".\"COLUMN_NAME\"\n          AND \"ct\".\"CONSTRAINT_TYPE\" != 'U'")
+                            .joinRaw("LEFT JOIN \"uc\" \"uct\"\n          ON \"c\".\"TABLE_NAME\" = \"uct\".\"TABLE_NAME\"\n          AND \"c\".\"COLUMN_NAME\" = \"uct\".\"COLUMN_NAME\"\n          AND \"uct\".\"CONSTRAINT_TYPE\" = 'U'")
                             .leftJoin('uc as fk', 'ct.R_CONSTRAINT_NAME', 'fk.CONSTRAINT_NAME')
@@ -174,0 +172,0 @@ .where({ 'c.HIDDEN_COLUMN': 'NO' });

dist/dialects/postgres.js

@@ -248,3 +248,3 @@ "use strict";
                             .joinRaw("\n        LEFT JOIN pg_catalog.pg_class\n          ON pg_catalog.pg_class.oid = quote_ident(c.table_name):: regclass:: oid\n          AND pg_catalog.pg_class.relname = c.table_name\n      ")
-                            .joinRaw("\n        LEFT JOIN LATERAL (\n          SELECT\n            pg_index.indisprimary,\n            pg_index.indisunique\n          FROM pg_index\n          JOIN pg_attribute\n            ON pg_attribute.attrelid = pg_index.indrelid\n            AND pg_attribute.attnum = any(pg_index.indkey)\n          WHERE pg_index.indrelid = quote_ident(c.table_name)::regclass\n          AND pg_attribute.attname = c.column_name\n          LIMIT 1\n        ) pg ON true\n      ")
+                            .joinRaw("\n        LEFT JOIN LATERAL (\n          SELECT\n            pg_index.indisprimary,\n            pg_index.indisunique\n          FROM pg_index\n          JOIN pg_attribute\n            ON pg_attribute.attrelid = pg_index.indrelid\n            AND pg_attribute.attnum = any(pg_index.indkey)\n          WHERE pg_index.indrelid = quote_ident(c.table_name)::regclass\n          AND pg_attribute.attname = c.column_name\n          AND pg_index.indnkeyatts = 1 \n          LIMIT 1\n        ) pg ON true\n      ")
                             .joinRaw("\n        LEFT JOIN LATERAL (\n          SELECT\n            k2.table_schema AS foreign_key_schema,\n            k2.table_name AS foreign_key_table,\n            k2.column_name AS foreign_key_column\n          FROM\n            information_schema.key_column_usage k1\n            JOIN information_schema.referential_constraints fk using (\n              constraint_schema, constraint_name\n            )\n            JOIN information_schema.key_column_usage k2\n              ON k2.constraint_schema = fk.unique_constraint_schema\n              AND k2.constraint_name = fk.unique_constraint_name\n              AND k2.ordinal_position = k1.position_in_unique_constraint\n            WHERE k1.table_name = c.table_name\n            AND k1.column_name = c.column_name\n        ) ffk ON TRUE\n      ")
@@ -251,0 +251,0 @@ .whereIn('c.table_schema', this.explodedSchema)

dist/dialects/sqlite.js

@@ -166,3 +166,4 @@ "use strict";
                         getColumnsForTable = function (table) { return __awaiter(_this, void 0, void 0, function () {
-                            var tablesWithAutoIncrementPrimaryKeys, columns, foreignKeys, indexList;
+                            var tablesWithAutoIncrementPrimaryKeys, columns, foreignKeys, indexList, indexInfoList;
+                            var _this = this;
                             return __generator(this, function (_a) {
@@ -188,5 +189,14 @@ switch (_a.label) {
                                         indexList = _a.sent();
+                                        return [4 /*yield*/, Promise.all(indexList.map(function (index) {
+                                                return _this.knex.raw("PRAGMA index_info(??)", index.name);
+                                            }))];
+                                    case 5:
+                                        indexInfoList = _a.sent();
                                         return [2 /*return*/, columns.map(function (raw) {
                                                 var foreignKey = foreignKeys.find(function (fk) { return fk.from === raw.name; });
-                                                var index = indexList.find(function (fk) { return fk.name === raw.name; });
+                                                var indexIndex = indexInfoList.findIndex(function (list) {
+                                                    return list.find(function (fk) { return fk.name === raw.name; });
+                                                });
+                                                var index = indexList[indexIndex];
+                                                var indexInfo = indexInfoList[indexIndex];
                                                 return {
@@ -204,3 +214,3 @@ name: raw.name,
                                                     is_nullable: raw.notnull === 0,
-                                                    is_unique: !!(index === null || index === void 0 ? void 0 : index.unique),
+                                                    is_unique: !!(index === null || index === void 0 ? void 0 : index.unique) && (indexInfo === null || indexInfo === void 0 ? void 0 : indexInfo.length) === 1,
                                                     is_primary_key: raw.pk === 1,
@@ -207,0 +217,0 @@ has_auto_increment: raw.pk === 1 && tablesWithAutoIncrementPrimaryKeys.includes(table),

lib/dialects/mssql.ts

@@ -268,7 +268,7 @@ import { Knex } from 'knex';
             [ix].[is_primary_key],
+            MAX([ic].[index_column_id]) OVER(partition by [ic].[index_id], [ic].[object_id]) AS index_column_count,
             ROW_NUMBER() OVER (
-              PARTITION BY [ic].[object_id],
-                [ic].[column_id] ORDER BY [ix].[is_primary_key] DESC,
-                [ix].[is_unique] DESC
-              ) AS index_priority
+              PARTITION BY [ic].[object_id], [ic].[column_id]
+              ORDER BY [ix].[is_primary_key] DESC, [ix].[is_unique] DESC
+            ) AS index_priority
           FROM
@@ -278,6 +278,9 @@ [sys].[index_columns] [ic]
             AND [ix].[index_id] = [ic].[index_id]
-        ) AS [i] ON [i].[object_id] = [c].[object_id] AND [i].[column_id] = [c].[column_id]`
+        ) AS [i]
+        ON [i].[object_id] = [c].[object_id]
+        AND [i].[column_id] = [c].[column_id]
+        AND ISNULL([i].[index_column_count], 1) = 1
+        AND ISNULL([i].[index_priority], 1) = 1`
       )
-      .where({ 's.name': this.schema })
-      .andWhereRaw(`ISNULL([i].index_priority, 1) = 1`);
+      .where({ 's.name': this.schema });
 
@@ -284,0 +287,0 @@ if (table) {

lib/dialects/oracledb.ts

@@ -127,2 +127,6 @@ import { Knex } from 'knex';
   async columnInfo<T>(table?: string, column?: string) {
+    /**
+     * NOTICE: This query is optimized for speed and sacrifices some elegance and
+     * beauty to achive this. If you plan on refactoring, please keep this in mind.
+     */
     const query = this.knex
@@ -132,3 +136,3 @@ .with(
         this.knex.raw(`
-          SELECT /*+ materialize */ DISTINCT
+          SELECT /*+ materialize */
             "uc"."TABLE_NAME",
@@ -140,4 +144,12 @@ "ucc"."COLUMN_NAME",
           FROM "USER_CONSTRAINTS" "uc"
-            INNER JOIN "USER_CONS_COLUMNS" "ucc" ON "uc"."CONSTRAINT_NAME" = "ucc"."CONSTRAINT_NAME"
+          INNER JOIN (
+            SELECT
+              "COLUMN_NAME",
+              "CONSTRAINT_NAME",
+              COUNT(*) OVER(PARTITION BY "CONSTRAINT_NAME") "INDEX_COLUMN_COUNT"
+            FROM "USER_CONS_COLUMNS"
+          ) "ucc"
+            ON "uc"."CONSTRAINT_NAME" = "ucc"."CONSTRAINT_NAME"
             AND "uc"."CONSTRAINT_TYPE" IN ('P', 'U', 'R')
+            AND "ucc"."INDEX_COLUMN_COUNT" = 1
         `)
@@ -157,3 +169,5 @@ )
         'cm.COMMENTS as COLUMN_COMMENT',
-        'ct.CONSTRAINT_TYPE',
+        this.knex.raw(
+          'COALESCE("ct"."CONSTRAINT_TYPE", "uct"."CONSTRAINT_TYPE") AS "CONSTRAINT_TYPE"'
+        ),
         'fk.TABLE_NAME as REFERENCED_TABLE_NAME',
@@ -167,6 +181,14 @@ 'fk.COLUMN_NAME as REFERENCED_COLUMN_NAME'
       })
-      .leftJoin('uc as ct', {
-        'c.TABLE_NAME': 'ct.TABLE_NAME',
-        'c.COLUMN_NAME': 'ct.COLUMN_NAME',
-      })
+      .joinRaw(
+        `LEFT JOIN "uc" "ct"
+          ON "c"."TABLE_NAME" = "ct"."TABLE_NAME"
+          AND "c"."COLUMN_NAME" = "ct"."COLUMN_NAME"
+          AND "ct"."CONSTRAINT_TYPE" != 'U'`
+      )
+      .joinRaw(
+        `LEFT JOIN "uc" "uct"
+          ON "c"."TABLE_NAME" = "uct"."TABLE_NAME"
+          AND "c"."COLUMN_NAME" = "uct"."COLUMN_NAME"
+          AND "uct"."CONSTRAINT_TYPE" = 'U'`
+      )
       .leftJoin('uc as fk', 'ct.R_CONSTRAINT_NAME', 'fk.CONSTRAINT_NAME')
@@ -236,2 +258,5 @@ .where({ 'c.HIDDEN_COLUMN': 'NO' });
   async foreignKeys(table?: string): Promise<ForeignKey[]> {
+    /**
+     * NOTICE: This query is optimized for speed. Please keep this in mind.
+     */
     const query = this.knex
@@ -238,0 +263,0 @@ .with(

lib/dialects/postgres.ts

@@ -274,2 +274,3 @@ import { Knex } from 'knex';
           AND pg_attribute.attname = c.column_name
+          AND pg_index.indnkeyatts = 1 
           LIMIT 1
@@ -276,0 +277,0 @@ ) pg ON true

lib/dialects/sqlite.ts

@@ -149,6 +149,20 @@ import { Knex } from 'knex';
 
+      const indexInfoList = await Promise.all(
+        indexList.map((index) =>
+          this.knex.raw<{ seqno: number; cid: number; name: string }[]>(
+            `PRAGMA index_info(??)`,
+            index.name
+          )
+        )
+      );
+
       return columns.map((raw): Column => {
         const foreignKey = foreignKeys.find((fk) => fk.from === raw.name);
-        const index = indexList.find((fk) => fk.name === raw.name);
 
+        const indexIndex = indexInfoList.findIndex((list) =>
+          list.find((fk) => fk.name === raw.name)
+        );
+        const index = indexList[indexIndex];
+        const indexInfo = indexInfoList[indexIndex];
+
         return {
@@ -166,3 +180,3 @@ name: raw.name,
           is_nullable: raw.notnull === 0,
-          is_unique: !!index?.unique,
+          is_unique: !!index?.unique && indexInfo?.length === 1,
           is_primary_key: raw.pk === 1,
@@ -169,0 +183,0 @@ has_auto_increment:

package.json

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

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

Improved metrics

Total package byte prevSize

311402

increased by1.12%

Lines of code

6803

increased by0.74%

No dependency changes