changed query workflow

packages/api/src/controllers/databaseConnections.js

@@ -29,7 +29,17 @@ const generateDeploySql = require('../shell/generateDeploySql');
 const { createTwoFilesPatch } = require('diff');
 const diff2htmlPage = require('../utility/diff2htmlPage');
 const processArgs = require('../utility/processArgs');
-const { testConnectionPermission, hasPermission, loadPermissionsFromRequest, loadTablePermissionsFromRequest, getTablePermissionRole, loadDatabasePermissionsFromRequest, getDatabasePermissionRole, getTablePermissionRoleLevelIndex, testDatabaseRolePermission } = require('../utility/hasPermission');
+const {
+  testConnectionPermission,
+  hasPermission,
+  loadPermissionsFromRequest,
+  loadTablePermissionsFromRequest,
+  getTablePermissionRole,
+  loadDatabasePermissionsFromRequest,
+  getDatabasePermissionRole,
+  getTablePermissionRoleLevelIndex,
+  testDatabaseRolePermission,
+} = require('../utility/hasPermission');
 const { MissingCredentialsError } = require('../utility/exceptions');
 const pipeForkLogs = require('../utility/pipeForkLogs');
 const crypto = require('crypto');
@@ -100,7 +110,7 @@ module.exports = {
     socket.emitChanged(`database-status-changed`, { conid, database });
   },
 
-  handle_ping() { },
+  handle_ping() {},
 
   // session event handlers
 
@@ -256,23 +266,24 @@ module.exports = {
         auditLogger:
           auditLogSessionGroup && select?.from?.name?.pureName
             ? response => {
-              sendToAuditLog(req, {
-                category: 'dbop',
-                component: 'DatabaseConnectionsController',
-                event: 'sql.select',
-                action: 'select',
-                severity: 'info',
-                conid,
-                database,
-                schemaName: select?.from?.name?.schemaName,
-                pureName: select?.from?.name?.pureName,
-                sumint1: response?.rows?.length,
-                sessionParam: `${conid}::${database}::${select?.from?.name?.schemaName || '0'}::${select?.from?.name?.pureName
+                sendToAuditLog(req, {
+                  category: 'dbop',
+                  component: 'DatabaseConnectionsController',
+                  event: 'sql.select',
+                  action: 'select',
+                  severity: 'info',
+                  conid,
+                  database,
+                  schemaName: select?.from?.name?.schemaName,
+                  pureName: select?.from?.name?.pureName,
+                  sumint1: response?.rows?.length,
+                  sessionParam: `${conid}::${database}::${select?.from?.name?.schemaName || '0'}::${
+                    select?.from?.name?.pureName
                   }`,
-                sessionGroup: auditLogSessionGroup,
-                message: `Loaded table data from ${select?.from?.name?.pureName}`,
-              });
-            }
+                  sessionGroup: auditLogSessionGroup,
+                  message: `Loaded table data from ${select?.from?.name?.pureName}`,
+                });
+              }
             : null,
       }
     );
@@ -335,21 +346,21 @@ module.exports = {
         auditLogger:
           auditLogSessionGroup && options?.pureName
             ? response => {
-              sendToAuditLog(req, {
-                category: 'dbop',
-                component: 'DatabaseConnectionsController',
-                event: 'nosql.collectionData',
-                action: 'select',
-                severity: 'info',
-                conid,
-                database,
-                pureName: options?.pureName,
-                sumint1: response?.result?.rows?.length,
-                sessionParam: `${conid}::${database}::${options?.pureName}`,
-                sessionGroup: auditLogSessionGroup,
-                message: `Loaded collection data ${options?.pureName}`,
-              });
-            }
+                sendToAuditLog(req, {
+                  category: 'dbop',
+                  component: 'DatabaseConnectionsController',
+                  event: 'nosql.collectionData',
+                  action: 'select',
+                  severity: 'info',
+                  conid,
+                  database,
+                  pureName: options?.pureName,
+                  sumint1: response?.result?.rows?.length,
+                  sessionParam: `${conid}::${database}::${options?.pureName}`,
+                  sessionGroup: auditLogSessionGroup,
+                  message: `Loaded collection data ${options?.pureName}`,
+                });
+              }
             : null,
       }
     );
@@ -455,10 +466,18 @@ module.exports = {
       [changeSet.inserts, 'create_update_delete'],
       [changeSet.deletes, 'create_update_delete'],
       [changeSet.updates, 'update_only'],
-    ]
+    ];
     for (const [operations, requiredRole] of fieldsAndRoles) {
       for (const operation of operations) {
-        const role = getTablePermissionRole(conid, database, 'tables', operation.schemaName, operation.pureName, tablePermissions, databasePermissions);
+        const role = getTablePermissionRole(
+          conid,
+          database,
+          'tables',
+          operation.schemaName,
+          operation.pureName,
+          tablePermissions,
+          databasePermissions
+        );
         if (getTablePermissionRoleLevelIndex(role) < getTablePermissionRoleLevelIndex(requiredRole)) {
           throw new Error('DBGM-00262 Permission not granted');
         }
@@ -628,7 +647,15 @@ module.exports = {
       function applyTablePermissionRole(list, objectTypeField) {
         const res = [];
         for (const item of list ?? []) {
-          const tablePermissionRole = getTablePermissionRole(conid, database, objectTypeField, item.schemaName, item.pureName, tablePermissions, databasePermissionRole);
+          const tablePermissionRole = getTablePermissionRole(
+            conid,
+            database,
+            objectTypeField,
+            item.schemaName,
+            item.pureName,
+            tablePermissions,
+            databasePermissionRole
+          );
           if (tablePermissionRole != 'deny') {
             res.push({
               ...item,
@@ -647,7 +674,7 @@ module.exports = {
         functions: applyTablePermissionRole(opened.structure.functions, 'functions'),
         triggers: applyTablePermissionRole(opened.structure.triggers, 'triggers'),
         collections: applyTablePermissionRole(opened.structure.collections, 'collections'),
-      }
+      };
       return res;
     }
 
@@ -881,17 +908,17 @@ module.exports = {
     return {
       ...(command == 'backup'
         ? driver.backupDatabaseCommand(
-          connection,
-          { outputFile, database, options, selectedTables, skippedTables, argsFormat },
-          // @ts-ignore
-          externalTools
-        )
+            connection,
+            { outputFile, database, options, selectedTables, skippedTables, argsFormat },
+            // @ts-ignore
+            externalTools
+          )
         : driver.restoreDatabaseCommand(
-          connection,
-          { inputFile, database, options, argsFormat },
-          // @ts-ignore
-          externalTools
-        )),
+            connection,
+            { inputFile, database, options, argsFormat },
+            // @ts-ignore
+            externalTools
+          )),
       transformMessage: driver.transformNativeCommandMessage
         ? message => driver.transformNativeCommandMessage(message, command)
         : null,
@@ -990,7 +1017,10 @@ module.exports = {
   async executeSessionQuery({ sesid, conid, database, sql }, req) {
     await testConnectionPermission(conid, req);
     logger.info({ sesid, sql }, 'DBGM-00010 Processing query');
-    sessions.dispatchMessage(sesid, 'Query execution started');
+    sessions.dispatchMessage(sesid, {
+      message: 'Query execution started',
+      sql,
+    });
 
     const opened = await this.ensureOpened(conid, database);
     opened.subprocess.send({ msgtype: 'executeSessionQuery', sql, sesid });

packages/api/src/controllers/sessions.js

@@ -188,7 +188,10 @@ module.exports = {
     });
 
     logger.info({ sesid, sql }, 'DBGM-00019 Processing query');
-    this.dispatchMessage(sesid, 'Query execution started');
+    this.dispatchMessage(sesid, {
+      message: 'Query execution started',
+      sql,
+    });
     session.subprocess.send({
       msgtype: 'executeQuery',
       sql,

packages/web/src/elements/SqlHighlighter.svelte

@@ -5,6 +5,8 @@
   import { onMount, afterUpdate } from 'svelte';
 
   export let code = '';
+  export let inline = false;
+  export let onClick = null;
 
   let domCode;
 
@@ -29,7 +31,11 @@
     The `sql` class hints the language; highlight.js will
     read it even though we register the grammar explicitly.
   -->
-  <pre bind:this={domCode} class="sql">{code}</pre>
+  {#if inline}
+    <span bind:this={domCode} class="sql" class:clickable={!!onClick} on:click={onClick}>{code}</span>
+  {:else}
+    <pre bind:this={domCode} class="sql" class:clickable={!!onClick} on:click={onClick}>{code}</pre>
+  {/if}
 {/key}
 
 <style>
@@ -38,4 +44,8 @@
     padding: 0;
     padding: 0.5em;
   }
+
+  .clickable {
+    cursor: pointer;
+  }
 </style>

packages/web/src/modals/ShowSqlModal.svelte

@@ -0,0 +1,59 @@
+<script lang='ts'>
+  import _ from 'lodash';
+  import FormStyledButton from '../buttons/FormStyledButton.svelte';
+  import newQuery from '../query/newQuery';
+  import SqlEditor from '../query/SqlEditor.svelte';
+
+  import ModalBase from './ModalBase.svelte';
+  import { closeCurrentModal } from './modalTools';
+  import { _t } from '../translations';
+
+  export let sql;
+  export let onConfirm;
+  export let engine = null;
+</script>
+
+<ModalBase {...$$restProps}>
+  <div slot="header">SQL Script</div>
+
+  <div class="editor">
+    <SqlEditor {engine} value={sql} readOnly />
+  </div>
+
+  <div slot="footer">
+    <FormStyledButton
+      type="button"
+      value={_t('common.close', { defaultMessage: 'Close' })}
+      on:click={closeCurrentModal}
+      data-testid="ShowSqlModal_closeButton"
+    />
+    <FormStyledButton
+      type="button"
+      value="Open script"
+      on:click={() => {
+        newQuery({
+          initialData: sql,
+        });
+
+        closeCurrentModal();
+      }}
+      data-testid="ShowSqlModal_openScriptButton"
+    />
+  </div>
+</ModalBase>
+
+<style>
+  .editor {
+    position: relative;
+    height: 30vh;
+    width: 40vw;
+  }
+
+  .form-margin {
+    margin: var(--dim-large-form-margin);
+  }
+
+  .flex-wrap {
+    flex-wrap: wrap;
+  }
+</style>

packages/web/src/plugins/ThemeDark.svelte

@@ -75,7 +75,7 @@
 
     --token-base: #303030;
     --token-text: #ddd;
-    --token-keyword: white;
+    --token-keyword: #096dd9;
     --token-selector-tag: white;
     --token-literal: white;
     --token-section: white;

packages/web/src/query/MessageView.svelte

@@ -4,6 +4,8 @@
   import RowsFilterSwitcher from '../forms/RowsFilterSwitcher.svelte';
   import SearchInput from '../elements/SearchInput.svelte';
   import { filterName } from 'dbgate-tools';
+  import InlineButton from '../buttons/InlineButton.svelte';
+  import FontIcon from '../icons/FontIcon.svelte';
 
   export let items: any[];
   export let showProcedure = false;
@@ -12,8 +14,10 @@
   export let startLine = 0;
   export let onMessageClick = null;
   export let onExplainError = null;
+  export let engine = null;
 
   export let filter = '';
+  export let onClear = null;
 
   $: time0 = items[0] && new Date(items[0].time).getTime();
 
@@ -37,6 +41,17 @@
 
 <div class="main">
   <div class="topbar">
+    {#if onClear}
+      <InlineButton
+        icon="img clear"
+        on:click={() => {
+          onClear();
+        }}
+      >
+        <FontIcon icon="icon delete" padRight />
+        Clear
+      </InlineButton>
+    {/if}
     <RowsFilterSwitcher
       icon="img debug"
       label="Debug"
@@ -92,6 +107,7 @@
           previousRow={index > 0 ? items[index - 1] : null}
           {onMessageClick}
           {onExplainError}
+          {engine}
         />
       {/each}
     </table>

packages/web/src/query/MessageViewRow.svelte

@@ -17,6 +17,9 @@
   import FontIcon from '../icons/FontIcon.svelte';
   import { plusExpandIcon } from '../icons/expandIcons';
   import InlineButton from '../buttons/InlineButton.svelte';
+  import SqlHighlighter from '../elements/SqlHighlighter.svelte';
+  import { showModal } from '../modals/modalTools';
+  import ShowSqlModal from '../modals/ShowSqlModal.svelte';
 
   export let row;
   export let index;
@@ -29,6 +32,7 @@
   export let previousRow = null;
   export let onMessageClick = null;
   export let onExplainError = null;
+  export let engine = null;
 
   let isExpanded = false;
 </script>
@@ -55,10 +59,22 @@
         }}><FontIcon icon="img ai" /> Explain</InlineButton
       >
     {/if}
+    {#if row.sql}
+      <SqlHighlighter
+        code={row.sql.substring(0, 100) + (row.sql.length > 100 ? '...' : '')}
+        inline
+        onClick={() => {
+          showModal(ShowSqlModal, {
+            sql: row.sql,
+            engine,
+          });
+        }}
+      />
+    {/if}
   </td>
   <td>{moment(row.time).format('HH:mm:ss')}</td>
   <td>{formatDuration(new Date(row.time).getTime() - time0)}</td>
-  <td> {previousRow ? formatDuration(new Date(row.time).getTime() - new Date(previousRow.time).getTime()) : 'n/a'}</td>
+  <td>{previousRow ? formatDuration(new Date(row.time).getTime() - new Date(previousRow.time).getTime()) : 'n/a'}</td>
   {#if showProcedure}
     <td>{row.procedure || ''}</td>
   {/if}

packages/web/src/query/SocketMessageView.svelte

@@ -12,12 +12,13 @@
   export let showLine = false;
   export let showCaller = false;
   export let eventName;
-  export let executeNumber;
+  export let executeNumber = null;
   export let showNoMessagesAlert = false;
   export let startLine = 0;
   export let onChangeErrors = null;
   export let onMessageClick = null;
   export let onExplainError = null;
+  export let engine = null;
 
   const cachedMessagesRef = createRef([]);
   const lastErrorMessageCountRef = createRef(0);
@@ -43,6 +44,11 @@
     return () => {};
   });
 
+  function handleClearMessages() {
+    cachedMessagesRef.set([]);
+    displayedMessages = [];
+  }
+
   $: {
     if (executeNumber >= 0) {
       displayedMessages = [];
@@ -79,5 +85,7 @@
     {showCaller}
     {startLine}
     {onExplainError}
+    {engine}
+    onClear={executeNumber == null ? handleClearMessages : null}
   />
 {/if}

packages/web/src/tabs/QueryTab.svelte

@@ -771,12 +771,12 @@
               <SocketMessageView
                 eventName={sessionId ? `session-info-${sessionId}` : null}
                 onMessageClick={handleMesageClick}
-                {executeNumber}
                 startLine={executeStartLine}
                 showProcedure
                 showLine
                 onChangeErrors={handleChangeErrors}
                 onExplainError={isProApp() ? handleExplainError : null}
+                engine={$connection && $connection.engine}
               />
             </svelte:fragment>
           </ResultTabs>