import KnexOracle, { parseDefaultValue } from 'knex-schema-inspector/dist/dialects/oracledb'; import { Column } from 'knex-schema-inspector/dist/types/column'; import { SchemaOverview } from '../types/overview'; import { SchemaInspector } from '../types/schema'; export default class Oracle extends KnexOracle implements SchemaInspector { private static _mapColumnAutoIncrement(column: Column): Column { // Knex doesn't support AUTO_INCREMENT. Assume all numeric primary // keys without a default are AUTO_INCREMENT const hasAutoIncrement = !column.default_value && column.data_type === 'NUMBER' && column.is_primary_key; return { ...column, default_value: hasAutoIncrement ? 'AUTO_INCREMENT' : column.default_value, has_auto_increment: hasAutoIncrement, }; } columnInfo(): Promise; columnInfo(table: string): Promise; columnInfo(table: string, column: string): Promise; async columnInfo(table?: string, column?: string): Promise { if (column) { const columnInfo: Column = await super.columnInfo(table!, column!); return Oracle._mapColumnAutoIncrement(columnInfo); } const columnInfo: Column[] = await super.columnInfo(table!); return columnInfo.map(Oracle._mapColumnAutoIncrement); } async overview(): Promise { type RawColumn = { table_name: string; column_name: string; default_value: string; is_nullable: string; is_generated: string; data_type: string; numeric_precision: number | null; numeric_scale: number | null; column_key: string; max_length: number | null; }; /** * NOTICE: This query is optimized for speed. Please keep this in mind. */ const columns = await this.knex.raw(` WITH "uc" AS ( SELECT /*+ MATERIALIZE */ "uc"."TABLE_NAME", "ucc"."COLUMN_NAME", "uc"."CONSTRAINT_TYPE", COUNT(*) OVER( PARTITION BY "uc"."CONSTRAINT_NAME" ) "CONSTRAINT_COUNT" FROM "USER_CONSTRAINTS" "uc" INNER JOIN "USER_CONS_COLUMNS" "ucc" ON "uc"."CONSTRAINT_NAME" = "ucc"."CONSTRAINT_NAME" AND "uc"."CONSTRAINT_TYPE" = 'P' ) SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */ "c"."TABLE_NAME" "table_name", "c"."COLUMN_NAME" "column_name", "c"."DATA_DEFAULT" "default_value", "c"."NULLABLE" "is_nullable", "c"."DATA_TYPE" "data_type", "c"."DATA_PRECISION" "numeric_precision", "c"."DATA_SCALE" "numeric_scale", "ct"."CONSTRAINT_TYPE" "column_key", "c"."CHAR_LENGTH" "max_length", "c"."VIRTUAL_COLUMN" "is_generated" FROM "USER_TAB_COLS" "c" LEFT JOIN "uc" "ct" ON "c"."TABLE_NAME" = "ct"."TABLE_NAME" AND "c"."COLUMN_NAME" = "ct"."COLUMN_NAME" AND "ct"."CONSTRAINT_COUNT" = 1 WHERE "c"."HIDDEN_COLUMN" = 'NO' `); const overview: SchemaOverview = {}; for (const column of columns) { if (column.table_name in overview === false) { overview[column.table_name] = { primary: columns.find((nested: { column_key: string; table_name: string }) => { return nested.table_name === column.table_name && nested.column_key === 'P'; })?.column_name || 'id', columns: {}, }; } // Knex doesn't support AUTO_INCREMENT. Assume all numeric primary // keys without a default are AUTO_INCREMENT const hasAutoIncrement = !column.default_value && column.data_type === 'NUMBER' && column.column_key === 'P'; overview[column.table_name].columns[column.column_name] = { ...column, is_nullable: column.is_nullable === 'Y', is_generated: column.is_generated === 'YES', default_value: hasAutoIncrement ? 'AUTO_INCREMENT' : parseDefaultValue(column.default_value), }; } return overview; } }