import * as mysql from 'mysql' ; import { mapValues , keys , isEqual } from 'lodash' ; import { parse as urlParse } from 'url' ; import { TableDefinition , Database } from './schemaInterfaces' ; import Options from './options' ; export class MysqlDatabase implements Database { private db : mysql . IConnection ; private defaultSchema : string ; constructor ( public connectionString ) { this . db = mysql . createConnection ( connectionString ) ; let url = urlParse ( connectionString , true ) ; if ( url && url . pathname ) { let database = url . pathname . substr ( 1 ) ; this . defaultSchema = database ; } else { this . defaultSchema = 'public' ; } } private static mapTableDefinitionToType ( tableDefinition , customTypes , options ) { if ( ! options ) throw new Error ( ) ; return mapValues ( tableDefinition , column => { switch ( column . udtName ) { case 'char' : case 'varchar' : case 'text' : case 'tinytext' : case 'mediumtext' : case 'longtext' : case 'time' : case 'geometry' : case 'set' : case 'enum' : column . tsType = 'string' ; return column ; case 'integer' : case 'int' : case 'smallint' : case 'mediumint' : case 'bigint' : case 'double' : case 'decimal' : case 'numeric' : case 'float' : case 'year' : column . tsType = 'number' ; return column ; case 'tinyint' : column . tsType = 'boolean' ; return column ; case 'json' : column . tsType = 'Object' ; return column ; case 'date' : case 'datetime' : case 'timestamp' : column . tsType = 'Date' ; return column ; case 'tinyblob' : case 'mediumblob' : case 'longblob' : case 'blob' : case 'binary' : case 'varbinary' : case 'bit' : column . tsType = 'Buffer' ; return column ; default : if ( customTypes . indexOf ( column . udtName ) !== - 1 ) { column . tsType = options . transformTypeName ( column . udtName ) ; return column ; } else { console . log ( `Type [${ column . udtName }] has been mapped to [any] because no specific type has been found.` ) ; column . tsType = 'any' ; return column ; } } } ) ; } private static parseMysqlEnumeration ( mysqlEnum ) { return mysqlEnum . replace ( /(^(enum|set)\('|'\)$)/gi , '' ) . split ( `','` ) ; } private static getEnumNameFromColumn ( dataType , columnName ) { return `${ dataType }_${ columnName }` ; } public query ( queryString ) { return this . queryAsync ( queryString ) ; } public async getEnumTypes ( schema ? ) { let enums = { } ; let enumSchemaWhereClause ; let params ; if ( schema ) { enumSchemaWhereClause = `and table_schema = ?` ; params = [ schema ] ; } else { enumSchemaWhereClause = '' ; params = [ ] ; } const rawEnumRecords = await this . queryAsync ( 'SELECT column_name, column_type, data_type ' + 'FROM information_schema.columns ' + `WHERE data_type IN ('enum', 'set') ${ enumSchemaWhereClause }` , params ) ; rawEnumRecords . forEach ( ( enumItem ) => { const enumName = MysqlDatabase . getEnumNameFromColumn ( enumItem . data_type , enumItem . column_name ) ; const enumValues = MysqlDatabase . parseMysqlEnumeration ( enumItem . column_type ) ; if ( enums [ enumName ] && ! isEqual ( enums [ enumName ] , enumValues ) ) { const errorMsg = `Multiple enums with the same name and contradicting types were found: ` + `${ enumItem . column_name }: ${ JSON . stringify ( enums [ enumName ] ) } and ${ JSON . stringify ( enumValues ) }` ; throw new Error ( errorMsg ) ; } enums [ enumName ] = enumValues ; } ) ; return enums ; } public async getTableDefinition ( tableName , tableSchema ) { let tableDefinition = { } ; const tableColumns = await this . queryAsync ( 'SELECT column_name, data_type, is_nullable ' + 'FROM information_schema.columns ' + 'WHERE table_name = ? and table_schema = ?' , [ tableName , tableSchema ] ) ; tableColumns . map ( ( schemaItem ) => { const columnName = schemaItem . column_name ; const dataType = schemaItem . data_type ; tableDefinition [ columnName ] = { udtName : /^(enum|set)$/i . test ( dataType ) ? MysqlDatabase . getEnumNameFromColumn ( dataType , columnName ) : dataType , nullable : schemaItem . is_nullable === 'YES' } ; } ) ; return tableDefinition ; } public async getTableTypes ( tableName , tableSchema , options ) { const enumTypes = await this . getEnumTypes ( tableSchema ) ; let customTypes = keys ( enumTypes ) ; return MysqlDatabase . mapTableDefinitionToType ( await this . getTableDefinition ( tableName , tableSchema ) , customTypes , options ) ; } public async getSchemaTables ( schemaName ) { const schemaTables = await this . queryAsync ( 'SELECT table_name ' + 'FROM information_schema.columns ' + 'WHERE table_schema = ? ' + 'GROUP BY table_name' , [ schemaName ] ) ; return schemaTables . map ( ( schemaItem ) => schemaItem . table_name ) ; } public queryAsync ( queryString , escapedValues ? ) { return new Promise ( ( resolve , reject ) => { this . db . query ( queryString , escapedValues , ( error , results ) => { if ( error ) { return reject ( error ) ; } return resolve ( results ) ; } ) ; } ) ; } public getDefaultSchema ( ) { return this . defaultSchema ; } }