import _ from "lodash" ; import { Dialect , QueryInterface , QueryTypes , Sequelize } from "sequelize" ; import { AutoOptions } from "." ; import { ColumnElementType , ColumnPrecision , DialectOptions , FKRow , FKSpec , TriggerCount } from "./dialects/dialect-options" ; import { dialects } from "./dialects/dialects" ; import { Field , IndexSpec , Table , TableData } from "./types" ; /** Queries the database and builds the tables, foreignKeys, indexes, and hasTriggerTables structures in TableData  */ export class AutoBuilder { sequelize : Sequelize ; queryInterface : QueryInterface ; dialect : DialectOptions ; includeTables ? : string [ ] ; skipTables ? : string [ ] ; schema ? : string ; views : boolean ; tableData : TableData ; constructor ( sequelize , options ) { this . sequelize = sequelize ; this . queryInterface = this . sequelize . getQueryInterface ( ) ; this . dialect = dialects [ this . sequelize . getDialect ( ) as Dialect ] ; this . includeTables = options . tables ; this . skipTables = options . skipTables ; this . schema = options . schema ; this . views = ! ! options . views ; this . tableData = new TableData ( ) ; } build ( ) { let prom ; if ( this . dialect . showTablesQuery ) { const showTablesSql = this . dialect . showTablesQuery ( this . schema ) ; prom = this . executeQuery < string > ( showTablesSql ) ; } else { prom = this . queryInterface . showAllTables ( ) ; } if ( this . views ) { prom = prom . then ( tr => { const vschema = this . dialect . name === 'mysql' ? this . sequelize . getDatabaseName ( ) : this . schema ; const showViewsSql = this . dialect . showViewsQuery ( vschema ) ; return this . executeQuery < string > ( showViewsSql ) . then ( tr2 => tr . concat ( tr2 ) ) ; } ) ; } return prom . then ( tr => this . processTables ( tr ) ) . catch ( err => { console . error ( err ) ; return this . tableData ; } ) ; } private processTables ( tableResult ) { let tables = _ . map ( tableResult , t => { return { table_name : t . table_name || t . tableName || t . name || String ( t ) , table_schema : t . table_schema || t . tableSchema || t . schema || this . schema || null } as Table ; } ) ; if ( this . includeTables ) { const optables = mapOptionTables ( this . includeTables , this . schema ) ; tables = _ . intersectionWith ( tables , optables , isTableEqual ) ; } else if ( this . skipTables ) { const skipTables = mapOptionTables ( this . skipTables , this . schema ) ; tables = _ . differenceWith ( tables , skipTables , isTableEqual ) ; } const promises = tables . map ( t => { return this . mapForeignKeys ( t ) . then ( ( ) => this . mapTable ( t ) ) ; } ) ; return Promise . all ( promises ) . then ( ( ) => this . tableData ) ; } private mapForeignKeys ( table ) { const tableQname = makeTableQName ( table ) ; const sql = this . dialect . getForeignKeysQuery ( table . table_name , table . table_schema || this . sequelize . getDatabaseName ( ) ) ; const dialect = this . dialect ; const foreignKeys = this . tableData . foreignKeys ; return this . executeQuery < FKRow > ( sql ) . then ( res => { res . forEach ( assignColumnDetails ) ; } ) . catch ( err => console . error ( err ) ) ; function assignColumnDetails ( row , ix , rows ) { let ref ; if ( dialect . remapForeignKeysRow ) { ref = dialect . remapForeignKeysRow ( table . table_name , row ) as FKSpec ; } else { ref = row as any as FKSpec ; } if ( ! _ . isEmpty ( _ . trim ( ref . source_column ) ) && ! _ . isEmpty ( _ . trim ( ref . target_column ) ) ) { ref . isForeignKey = true ; ref . foreignSources = _ . pick ( ref , [ 'source_table' , 'source_schema' , 'target_schema' , 'target_table' , 'source_column' , 'target_column' ] ) ; } if ( dialect . isUnique && dialect . isUnique ( ref as any as FKRow , rows ) ) { ref . isUnique = ref . constraint_name || true ; } if ( _ . isFunction ( dialect . isPrimaryKey ) && dialect . isPrimaryKey ( ref ) ) { ref . isPrimaryKey = true ; } if ( dialect . isSerialKey && dialect . isSerialKey ( ref ) ) { ref . isSerialKey = true ; } foreignKeys [ tableQname ] = foreignKeys [ tableQname ] || { } ; foreignKeys [ tableQname ] [ ref . source_column ] = _ . assign ( { } , foreignKeys [ tableQname ] [ ref . source_column ] , ref ) ; } } private async mapTable ( table ) { try { const fields = await this . queryInterface . describeTable ( table . table_name , table . table_schema ) ; this . tableData . tables [ makeTableQName ( table ) ] = fields ; if ( this . dialect . showElementTypeQuery && ( _ . some ( fields , { type : "ARRAY" } ) || _ . some ( fields , { type : "USER-DEFINED" } ) ) ) { const stquery = this . dialect . showElementTypeQuery ( table . table_name , table . table_schema ) ; const elementTypes = await this . executeQuery < ColumnElementType > ( stquery ) ; elementTypes . forEach ( et => { const fld = fields [ et . column_name ] as Field ; if ( fld . type === "ARRAY" ) { fld . elementType = et . element_type ; if ( et . element_type === "USER-DEFINED" && et . enum_values && ! fld . special . length ) { fld . elementType = "ENUM" ; fld . special = ( this . queryInterface as any ) . queryGenerator . fromArray ( et . enum_values ) ; } } else if ( fld . type === "USER-DEFINED" ) { fld . type = ! fld . special . length ? et . udt_name : "ENUM" ; } } ) ; if ( elementTypes . some ( et => et . udt_name === 'geography' ) && this . dialect . showGeographyTypeQuery ) { const gquery = this . dialect . showGeographyTypeQuery ( table . table_name , table . table_schema ) ; const gtypes = await this . executeQuery < ColumnElementType > ( gquery ) ; gtypes . forEach ( gt => { const fld = fields [ gt . column_name ] as Field ; if ( fld . type === 'geography' ) { fld . elementType = `'${ gt . udt_name }', ${ gt . data_type }` ; } } ) ; } if ( elementTypes . some ( et => et . udt_name === 'geometry' ) && this . dialect . showGeometryTypeQuery ) { const gquery = this . dialect . showGeometryTypeQuery ( table . table_name , table . table_schema ) ; const gtypes = await this . executeQuery < ColumnElementType > ( gquery ) ; gtypes . forEach ( gt => { const fld = fields [ gt . column_name ] as Field ; if ( fld . type === 'geometry' ) { fld . elementType = `'${ gt . udt_name }', ${ gt . data_type }` ; } } ) ; } } if ( this . dialect . showPrecisionQuery && ( _ . some ( fields , { type : "DECIMAL" } ) || _ . some ( fields , { type : "NUMERIC" } ) ) ) { const prequery = this . dialect . showPrecisionQuery ( table . table_name , table . table_schema ) ; const columnPrec = await this . executeQuery < ColumnPrecision > ( prequery ) ; columnPrec . forEach ( cp => { const fld = fields [ cp . column_name ] as Field ; if ( cp . numeric_precision && ( fld . type === 'DECIMAL' || fld . type === 'NUMERIC' ) ) { fld . type = `${ fld . type }(${ cp . numeric_precision },${ cp . numeric_scale })` ; } } ) ; } this . tableData . indexes [ makeTableQName ( table ) ] = await this . queryInterface . showIndex ( { tableName : table . table_name , schema : table . table_schema } ) as IndexSpec [ ] ; if ( ! _ . some ( fields , { primaryKey : true } ) ) { const idname = _ . keys ( fields ) . find ( f => f . toLowerCase ( ) === 'id' ) ; const idfield = idname && fields [ idname ] ; if ( idfield ) { idfield . primaryKey = true ; } } const countTriggerSql = this . dialect . countTriggerQuery ( table . table_name , table . table_schema || "" ) ; const triggerResult = await this . executeQuery < TriggerCount > ( countTriggerSql ) ; const triggerCount = triggerResult && triggerResult [ 0 ] && triggerResult [ 0 ] . trigger_count ; if ( triggerCount > 0 ) { this . tableData . hasTriggerTables [ makeTableQName ( table ) ] = true ; } } catch ( err ) { console . error ( err ) ; } } private executeQuery < T > ( query ) { return this . sequelize . query ( query , { type : QueryTypes . SELECT , raw : true } ) as any as Promise < T [ ] > ; } } function mapOptionTables ( arr , defaultSchema ) { return _ . map ( arr , ( t ) => { const sp = t . split ( '.' ) ; return { table_name : sp [ sp . length - 1 ] , table_schema : sp . length > 1 ? sp [ sp . length - 2 ] : defaultSchema } ; } ) ; } function isTableEqual ( a , b ) { return a . table_name === b . table_name && ( ! b . table_schema || a . table_schema === b . table_schema ) ; } function makeTableQName ( table ) { return [ table . table_schema , table . table_name ] . filter ( Boolean ) . join ( "." ) ; }