rickbergfalk · January 27, 2018 15:23 · Jan 27, 2018
diff --git a/get-schema-for-connection-update.js b/get-schema-for-connection-update.js
@@ -0,0 +1,162 @@
+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