Add basic autocomplete for SQL keywords and tables

4 files changed, 61 insertions, 13 deletions

diff --git a/index.html b/index.html
index 7f51820..9206134 100644
--- a/index.html
+++ b/index.html
@@ -6,6 +6,7 @@
 
     <link rel="stylesheet" href="node_modules/codemirror/lib/codemirror.css"></link>
     <link rel="stylesheet" href="node_modules/codemirror/theme/dracula.css"></link>
+    <link rel="stylesheet" href="node_modules/codemirror/addon/hint/show-hint.css"></link>
     <link rel="stylesheet" href="node_modules/datatables/media/css/jquery.dataTables.css"></link>
     <link rel="stylesheet" type="text/css" href="./styles/style.css"></link>
     <link rel="stylesheet" type="text/css" href="./styles/table-style.css"></link>
diff --git a/main.js b/main.js
index 5f4342b..cc89b11 100644
--- a/main.js
+++ b/main.js
@@ -58,4 +58,7 @@ app.on("activate", () => {
 });
 
 ipcMain.on("queryExecutor.runQueryComplete", (event, payload) => uiWindow.webContents.send("queryExecutor.runQueryComplete", payload));
-ipcMain.on("queryExecutor.runQuery", (event, payload) => queryExecutorProcess.webContents.send("queryExecutor.runQuery", payload));
\ No newline at end of file
+ipcMain.on("queryExecutor.runQuery", (event, payload) => queryExecutorProcess.webContents.send("queryExecutor.runQuery", payload));
+
+ipcMain.on("queryExecutor.queryTableMetadataComplete", (event, payload) => uiWindow.webContents.send("queryExecutor.queryTableMetadataComplete", payload));
+ipcMain.on("queryExecutor.queryTableMetadata", (event, payload) => queryExecutorProcess.webContents.send("queryExecutor.queryTableMetadata", payload));
\ No newline at end of file
diff --git a/query-executor.js b/query-executor.js
index 58189b5..7bd836d 100644
--- a/query-executor.js
+++ b/query-executor.js
@@ -25,3 +25,34 @@ ipcRenderer.on("queryExecutor.runQuery", (event, payload) => {
   });
 
 });
+
+ipcRenderer.on("queryExecutor.queryTableMetadata", (event, payload) => {
+
+  let tableMetadata = {};
+
+  let tableDataQuery = 
+    "SELECT c.table_schema || '.' || c.table_name identifier, c.column_name " +
+    "FROM information_schema.columns c " +
+    "WHERE c.table_schema != 'pg_catalog'";
+
+  connectionPool.query(tableDataQuery, (err, res) => {
+
+    console.log(err, res)
+
+    res.rows.forEach((row) => {
+      if(tableMetadata.hasOwnProperty(row.identifier)) {
+        tableMetadata[row.identifier].push(row.column_name);
+      }
+      else {
+        tableMetadata[row.identifier] = [row.column_name];
+      }
+    })
+
+    ipcRenderer.send("queryExecutor.queryTableMetadataComplete", {
+      "error": err,
+      "result": tableMetadata
+    });
+
+  });
+
+});
\ No newline at end of file
diff --git a/renderer.js b/renderer.js
index 750447f..8cd7701 100644
--- a/renderer.js
+++ b/renderer.js
@@ -5,6 +5,8 @@ const $ = require("jquery");
 const cm = require("codemirror");
 require("datatables")(window, $);
 require("codemirror/mode/sql/sql");
+require("codemirror/addon/hint/show-hint.js")
+require("codemirror/addon/hint/sql-hint.js")
 const Split = require("split.js");
 
 const editorContext = cm(document.getElementById("editor"), {
@@ -12,12 +14,23 @@ const editorContext = cm(document.getElementById("editor"), {
   mode: "text/x-sql",
   theme: "dracula",
   lineNumbers: true,
-  gutters: ["CodeMirror-linenumbers", "statement-pointer"]
+  gutters: ["CodeMirror-linenumbers", "statement-pointer"],
+  extraKeys: { "Ctrl-Space": "autocomplete" }
 });
 
 editorContext.on("cursorActivity", (instance) => {
   let coords = instance.getCursor();
-  $("#cursor-coords").text("Ln " + (parseInt(coords.line)+1) + ", Col " + (parseInt(coords.ch)+1));
+  $("#cursor-coords").text("Ln " + (parseInt(coords.line) + 1) + ", Col " + (parseInt(coords.ch) + 1));
+});
+
+ipcRenderer.send("queryExecutor.queryTableMetadata");
+ipcRenderer.on("queryExecutor.queryTableMetadataComplete", (event, response) => {
+  console.log(response);
+  cm.commands.autocomplete = function (cmInstance) {
+    cm.showHint(cmInstance, cm.hint.sql, {
+      tables: response.result
+    });
+  }
 });
 
 const statementDelimiter = "/";
@@ -47,7 +60,7 @@ function runQuery() {
 function findQuery() {
   let selectedText = editorContext.getSelection();
 
-  if(selectedText !== "") {
+  if (selectedText !== "") {
     _clearQueryMarks();
     return selectedText;
   }
@@ -55,11 +68,11 @@ function findQuery() {
     let cursorLine = editorContext.getCursor().line;
 
     let statementStartLine = editorContext.firstLine();
-    
+
     // lineCount rather than lastLine here, since lineCount is index 1 based.
     // getRange(from, to) below is 0 based, but the range is exclusive, so if we need to include the last line we need the +1 
     let statementEndLine = editorContext.lineCount();
-  
+
     // if the current line is a delimiter, thats the end of the statement
     if (editorContext.getLine(cursorLine) === statementDelimiter) {
       statementEndLine = cursorLine;
@@ -75,7 +88,7 @@ function findQuery() {
     }
 
     // mode up the document until a previous statements delimiter is found or the start of the document is reached
-    for(let i = cursorLine - 1; i >= editorContext.firstLine(); i--) {
+    for (let i = cursorLine - 1; i >= editorContext.firstLine(); i--) {
       if (editorContext.getLine(i) === statementDelimiter) {
         statementStartLine = i + 1;
         break;
@@ -86,7 +99,7 @@ function findQuery() {
       { line: statementStartLine, ch: 0 },
       { line: statementEndLine, ch: 0 }
     );
-    
+
     console.log(query);
 
     _clearQueryMarks();
@@ -116,7 +129,7 @@ function _clearQueryMarks() {
 
 ipcRenderer.on("queryExecutor.runQueryComplete", (event, response) => {
   _stopExecTimer();
-  if(response.error === undefined) {
+  if (response.error === undefined) {
     handleResult(response.result);
   }
   else {
@@ -178,7 +191,7 @@ function _resultTable() {
 }
 
 function _setExecutionStatusIndicator(status) {
-  switch(status) {
+  switch (status) {
     case "RUNNING":
       $("#execution-status").removeClass().addClass("exec-running").text("Running");
       break;
@@ -187,7 +200,7 @@ function _setExecutionStatusIndicator(status) {
       break;
     case "ERROR":
       $("#execution-status").removeClass().addClass("exec-error").text("Error");
-      break;        
+      break;
   }
 }
 
@@ -206,7 +219,7 @@ function _onKeyUp(event) {
 }
 
 $(document).ready(() => {
-  
+
   // Event handlers
   $("#run-query").click(runQuery);
   $(document).keyup(_onKeyUp);
@@ -224,7 +237,7 @@ $(document).ready(() => {
       return {
         "flex-basis": gutterSize + "px"
       }
-    } 
+    }
   });
 
 })