Tips to get hana schema working

get-schema-for-connection-update.js

const runQuery = require('./run-query.js')
const _ = require('lodash')
const fs = require('fs')
const path = require('path')
const decipher = require('./decipher.js')

const sqldir = path.join(__dirname, '/../resources/')

const sqlSchemaPostgres = fs.readFileSync(sqldir + '/schema-postgres.sql', {
  encoding: 'utf8'
})
const sqlSchemaVertica = fs.readFileSync(sqldir + '/schema-vertica.sql', {
  encoding: 'utf8'
})
const sqlSchemaCrate = fs.readFileSync(sqldir + '/schema-crate.sql', {
  encoding: 'utf8'
})
const sqlSchemaCrateV0 = fs.readFileSync(sqldir + '/schema-crate.v0.sql', {
  encoding: 'utf8'
})

// HANA - I'm guessing a separate schema query is needed for hana...
// After googling it looks like you might need to use SYS schema or something?
// https://stackoverflow.com/questions/41727670/hana-list-show-tables-sql-command
// Try something like this
const sqlSchemaHana = `
  SELECT 
    -- TODO if (view) is wanted in UI need to return ''Views' here
    -- I am hardcoding to 'Tables' for now
    'Tables' AS table_type,
    -- TODO not sure if HANA is picky about capital references
    SCHEMA_NAME AS table_schema,
    TABLE_NAME AS table_name,
    COLUMN_NAME AS column_name,
    DATA_TYPE_NAME AS data_type,
    IS_NULLABLE AS is_nullable
  FROM 
    SYS.TABLE_COLUMNS
  ORDER BY 
    SCHEMA_NAME, 
    TABLE_NAME, 
    POSITION
`


function getStandardSchemaSql(whereSql = '') {
  return `
    SELECT 
      (CASE t.table_type WHEN 'BASE TABLE' THEN 'Tables' WHEN 'VIEW' THEN 'Views' ELSE t.table_type END) AS table_type, 
      t.table_schema, 
      t.table_name, 
      c.column_name, 
      c.data_type, 
      c.is_nullable 
    FROM 
      INFORMATION_SCHEMA.TABLES t 
      JOIN INFORMATION_SCHEMA.COLUMNS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name 
    ${whereSql}
    ORDER BY 
      t.table_type, 
      t.table_schema, 
      t.table_name, 
      c.ordinal_position
  `
}

function getPrimarySql(connection) {
  if (connection.driver === 'vertica') {
    return sqlSchemaVertica
  } else if (connection.driver === 'crate') {
    return sqlSchemaCrate
  } else if (connection.driver === 'postgres') {
    return sqlSchemaPostgres
  } else if (connection.driver === 'mysql') {
    if (connection.database) {
      return getStandardSchemaSql(
        `WHERE t.table_schema = '${connection.database}'`
      )
    }
    return getStandardSchemaSql(
      `WHERE t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')`
    )
  // HANA assuming driver is named hana
  } else if (connection.driver === 'hana') {
    return sqlSchemaHana
  } else {
    return getStandardSchemaSql(
      `WHERE t.table_schema NOT IN ('information_schema') `
    )
  }
}

function getSecondarySql(connection) {
  if (connection.driver === 'crate') {
    return sqlSchemaCrateV0
  }
}

function getSchemaForConnection(connection, doneCallback) {
  connection.username = decipher(connection.username)
  connection.password = decipher(connection.password)
  connection.maxRows =
    typeof Number.MAX_SAFE_INTEGER === 'undefined'
      ? 9007199254740991
      : Number.MAX_SAFE_INTEGER

  const primarySchemaSql = getPrimarySql(connection)

  runQuery(primarySchemaSql, connection, function(err, queryResult) {
    const secondarySchemaSql = getSecondarySql(connection)
    if (err && !secondarySchemaSql) {
      console.error(err)
      return doneCallback(err)
    }
    if (err && secondarySchemaSql) {
      return runQuery(secondarySchemaSql, connection, function(
        err,
        queryResult
      ) {
        if (err) {
          return doneCallback(err)
        }
        return formatResults(queryResult, doneCallback)
      })
    }
    return formatResults(queryResult, doneCallback)
  })
}

function formatResults(queryResult, doneCallback) {
  const tree = {}
  const bySchema = _.groupBy(queryResult.rows, 'table_schema')
  for (const schema in bySchema) {
    if (bySchema.hasOwnProperty(schema)) {
      tree[schema] = {}
      const byTableName = _.groupBy(bySchema[schema], 'table_name')
      for (const tableName in byTableName) {
        if (byTableName.hasOwnProperty(tableName)) {
          tree[schema][tableName] = byTableName[tableName]
        }
      }
    }
  }
  // TODO get rid of is_nullable since no plans on using it in UI
  /*
  At this point, tree should look like this:
    {
      "schama-name": {
        "table-name": [
          {
            column_name: "the column name",
            data_type: "string",
            is_nullable: "no"
          }
        ]
      }
    }
  */
  return doneCallback(null, tree)
}

module.exports = getSchemaForConnection