import { IConnection } from 'mysql2Types' ; import { getConfig } from '../../../config-helper' ; import { hash , getFirstDefined } from '../../utility' ; const SALT = 'hoads8fh49hgfls' ; const mysql2 = require ( 'mysql2/promise' ) ; let QUERY_RULES = { selectRead : /\s*SELECT/i , selectWrite : /\s*SELECT.*FOR UPDATE/i , } ; export type MysqlOptions = { user : string ; database : string ; password : string ; host : string ; port : number ; connectTimeout : number ; multipleStatements : boolean ; namedPlaceholders : boolean ; } ; const DEFAULTS = { user : 'voiceweb' , database : 'voiceweb' , password : '' , host : 'localhost' , port : 3306 , connectTimeout : 30000 , multipleStatements : false , namedPlaceholders : true , } ; export default class Mysql { rootConn : IConnection ; pool : any ; readPool : any ; constructor ( ) { this . rootConn = null ; } /**
     * Get options from params first, then config, and falling back to defaults.
     *   For configuring, use the following order of priority:
     *     1. options in config.json
     *     2. hard coded DEFAULTS
     */ getMysqlOptions ( ) { const config = getConfig ( ) ; return { user : getFirstDefined ( config . MYSQLUSER , DEFAULTS . user ) , database : getFirstDefined ( config . MYSQLDBNAME , DEFAULTS . database ) , password : getFirstDefined ( config . MYSQLPASS , DEFAULTS . password ) , host : getFirstDefined ( config . MYSQLHOST , DEFAULTS . host ) , port : getFirstDefined ( config . MYSQLPORT , DEFAULTS . port ) , connectTimeout : DEFAULTS . connectTimeout , multipleStatements : false , namedPlaceholders : true , } ; } getMysqlReplicaOptions ( ) { const config = getConfig ( ) ; let options = this . getMysqlOptions ( ) ; if ( config . MYSQLREPLICAHOST ) { options . host = config . MYSQLREPLICAHOST ; options . port = config . MYSQLREPLICAPORT ; } return options ; } async getConnection ( options ) { return mysql2 . createConnection ( options ) ; } async createPool ( ) { return mysql2 . createPool ( this . getMysqlOptions ( ) ) ; } async createReadPool ( ) { return mysql2 . createPool ( this . getMysqlReplicaOptions ( ) ) ; } poolPromise : Promise < any > ; async getPool ( ) { if ( this . pool ) return this . pool ; return ( this . poolPromise || ( this . poolPromise = new Promise ( async ( resolve ) => { this . pool = await this . createPool ( ) ; resolve ( this . pool ) ; } ) ) ) ; } async getReadPool ( ) { if ( this . readPool ) return this . readPool ; return ( this . poolPromise || ( this . poolPromise = new Promise ( async ( resolve ) => { this . readPool = await this . createReadPool ( ) ; resolve ( this . readPool ) ; } ) ) ) ; } async query ( ... args ) { let sqlQuery = args [ 0 ] ; if ( QUERY_RULES . selectRead . test ( sqlQuery ) && ! QUERY_RULES . selectWrite . test ( sqlQuery ) ) { return ( await this . getReadPool ( ) ) . query ( ... args ) ; } return ( await this . getPool ( ) ) . query ( ... args ) ; } async escape ( ... args ) { return ( await this . getPool ( ) ) . escape ( ... args ) ; } async ensureRootConnection ( ) { if ( this . rootConn ) { return ; } const opts = Object . assign ( { } , this . getMysqlOptions ( ) ) ; delete opts . database ; const config = getConfig ( ) ; opts . user = config . DB_ROOT_USER ; opts . password = config . DB_ROOT_PASS ; opts . multipleStatements = true ; const conn = await this . getConnection ( opts ) ; conn . on ( 'error' , this . handleError . bind ( this ) ) ; this . rootConn = conn ; } private handleError ( err ) { console . error ( 'unhandled mysql error' , err . message ) ; } /**
     * Insert or update query generator.
     */ async upsert ( tableName , columns , values ) { const params = values . map ( ( val ) => { return '?' ; } ) ; const dupeSql = columns . map ( ( column ) => { return `${ column } = ?` ; } ) ; const allValues = values . concat ( values ) ; await this . query ( `INSERT INTO ${ tableName } (${ columns . join ( ',' ) })
       VALUES (${ params . join ( ',' ) })
       ON DUPLICATE KEY UPDATE ${ dupeSql . join ( ',' ) };` , allValues ) ; } private getProcedureName ( body ) { return 'F_' + hash ( body , SALT ) ; } /**
     * Call a stored procedure by procedure name generated in getProcedureName.
     */ async callProc ( name ) { return this . rootConn . query ( `CALL \`${ name }\`` ) ; } /**
     * Create and execute a stored procedure as root.
     */ async rootTransaction ( body ) { const name = this . getProcedureName ( body ) ; const transactionQuery = `
      CREATE PROCEDURE \`${ name }\`()
      BEGIN
          DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
          BEGIN
            ROLLBACK;
            RESIGNAL;
          END;
          START TRANSACTION;
          ${ body }
          COMMIT;
      END;` ; await this . ensureRootConnection ( ) ; try { await this . rootConn . query ( transactionQuery ) ; } catch ( err ) { if ( err . code !== 'ER_SP_ALREADY_EXISTS' ) { throw err ; } } await this . callProc ( name ) ; } /**
     * Execute a prepared statement on the root connection.
     */ async rootExec ( sql , values ? ) { values = values || [ ] ; await this . ensureRootConnection ( ) ; return this . rootConn . execute ( sql , values ) ; } /**
     * Execute a regular query on the root connection.
     */ async rootQuery ( sql ) { await this . ensureRootConnection ( ) ; return this . rootConn . query ( sql ) ; } /**
     * Close all connections to the database.
     */ endConnection ( ) { if ( this . pool ) { this . pool . end ( ) . catch ( ( e ) => console . error ( e ) ) ; this . pool = null ; } if ( this . rootConn ) { this . rootConn . destroy ( ) ; this . rootConn = null ; } } } let instance ; export function getMySQLInstance ( ) { return instance || ( instance = new Mysql ( ) ) ; }