raw data test

feat: filter extentions on fe

.nvmrc

@@ -0,0 +1 @@
+v21.7.3

e2e-tests/cypress.config.js

@@ -25,6 +25,9 @@ module.exports = defineConfig({
             case 'oauth':
               serverProcess = exec('yarn start:oauth');
               break;
+            case 'browse-data':
+              serverProcess = exec('yarn start:browse-data');
+              break;
           }
 
           await waitOn({ resources: ['http://localhost:3000'] });

e2e-tests/cypress/e2e/browse-data.cy.js

@@ -0,0 +1,43 @@
+describe('Data browser data', () => {
+  it('Load table data', () => {
+    cy.visit('http://localhost:3000');
+
+    cy.contains('MySql-connection').click();
+    cy.contains('Chinook').click();
+    cy.contains('Album').click();
+    cy.contains('Let There Be Rock').click();
+    cy.contains('Rows: 347');
+    cy.realPress(['Control', 'ArrowRight']);
+    cy.contains('Aerosmith');
+  });
+
+  it.only('Filter model', () => {
+    cy.visit('http://localhost:3000');
+
+    cy.contains('MySql-connection').click();
+    cy.contains('Chinook').click();
+    cy.testid('SqlObjectList_search').clear().type('album');
+    cy.contains('Tables (1/11)');
+    cy.contains('347 rows, InnoDB');
+    cy.testid('SqlObjectList_searchMenuDropDown').click();
+    cy.contains('Column name').click();
+    cy.contains('Tables (2/11)');
+    cy.contains('AlbumId');
+    cy.contains('Column name').click();
+    cy.contains('AlbumId').should('not.exist');
+    cy.contains('Tables (1/11)');
+  });
+
+  it('Show raw data', () => {
+    cy.visit('http://localhost:3000');
+
+    cy.contains('MySql-connection').click();
+    cy.contains('Chinook').click();
+    cy.contains('Album').rightclick();
+    cy.contains('Open raw data').click();
+    cy.contains('Let There Be Rock').click();
+    cy.contains('Rows: 347').should('not.exist');
+    cy.realPress(['Control', 'ArrowRight']);
+    cy.contains('Aerosmith').should('not.exist');
+  });
+});

e2e-tests/env/browse-data/.env

@@ -0,0 +1,8 @@
+CONNECTIONS=mysql
+
+LABEL_mysql=MySql-connection
+SERVER_mysql=localhost
+USER_mysql=root
+PASSWORD_mysql=Pwd2020Db
+PORT_mysql=16004
+ENGINE_mysql=mysql@dbgate-plugin-mysql

e2e-tests/init/browse-data.js

@@ -0,0 +1,43 @@
+const dbgateApi = require('dbgate-api');
+dbgateApi.initializeApiEnvironment();
+const dbgatePluginMysql = require('dbgate-plugin-mysql');
+dbgateApi.registerPlugins(dbgatePluginMysql);
+const dbgatePluginPostgres = require('dbgate-plugin-postgres');
+dbgateApi.registerPlugins(dbgatePluginPostgres);
+
+async function run() {
+  await dbgateApi.executeQuery({
+    connection: {
+      server: process.env.SERVER_mysql,
+      user: process.env.USER_mysql,
+      password: process.env.PASSWORD_mysql,
+      port: process.env.PORT_mysql,
+      engine: 'mysql@dbgate-plugin-mysql',
+    },
+    sql: 'drop database if exists Chinook',
+  });
+
+  await dbgateApi.executeQuery({
+    connection: {
+      server: process.env.SERVER_mysql,
+      user: process.env.USER_mysql,
+      password: process.env.PASSWORD_mysql,
+      port: process.env.PORT_mysql,
+      engine: 'mysql@dbgate-plugin-mysql',
+    },
+    sql: 'create database Chinook',
+  });
+
+  await dbgateApi.importDatabase({
+    connection: {
+      server: process.env.SERVER_mysql,
+      user: process.env.USER_mysql,
+      password: process.env.PASSWORD_mysql,
+      port: process.env.PORT_mysql,
+      engine: 'mysql@dbgate-plugin-mysql',
+    },
+    inputFile: '../data/Chinook-mysql.sql',
+  });
+}
+
+dbgateApi.runScript(run);

e2e-tests/package.json

@@ -18,12 +18,14 @@
     "cy:run:add-connection": "cypress run --spec cypress/e2e/add-connection.cy.js",
     "cy:run:portal": "cypress run --spec cypress/e2e/portal.cy.js",
     "cy:run:oauth": "cypress run --spec cypress/e2e/oauth.cy.js",
+    "cy:run:browse-data": "cypress run --spec cypress/e2e/browse-data.cy.js",
 
     "start:add-connection": "cd .. && node packer/build/bundle.js --listen-api --run-e2e-tests",
     "start:portal": "cd .. && env-cmd -f e2e-tests/env/portal/.env node e2e-tests/init/portal.js && env-cmd -f e2e-tests/env/portal/.env node packer/build/bundle.js --listen-api --run-e2e-tests",
     "start:oauth": "cd .. && env-cmd -f e2e-tests/env/oauth/.env node packer/build/bundle.js --listen-api --run-e2e-tests",
+    "start:browse-data": "cd .. && env-cmd -f e2e-tests/env/browse-data/.env node packer/build/bundle.js --listen-api --run-e2e-tests",
 
-    "test": "start-server-and-test start:add-connection http://localhost:3000 cy:run:add-connection && start-server-and-test start:portal http://localhost:3000 cy:run:portal && start-server-and-test start:oauth http://localhost:3000 cy:run:oauth",
+    "test": "start-server-and-test start:add-connection http://localhost:3000 cy:run:add-connection && start-server-and-test start:portal http://localhost:3000 cy:run:portal && start-server-and-test start:oauth http://localhost:3000 cy:run:oauth && start-server-and-test start:browse-data http://localhost:3000 cy:run:browse-data",
     "test:ci": "yarn test"
   }
 }

package.json

@@ -1,6 +1,6 @@
 {
   "private": true,
-  "version": "6.1.3-beta.1",
+  "version": "6.1.3-beta.2",
   "name": "dbgate-all",
   "workspaces": [
     "packages/*",

packages/datalib/src/TableGridDisplay.ts

@@ -37,7 +37,8 @@ export class TableGridDisplay extends GridDisplay {
     public displayOptions: any,
     serverVersion,
     public getDictionaryDescription: DictionaryDescriptionFunc = null,
-    isReadOnly = false
+    isReadOnly = false,
+    public isRawMode = false
   ) {
     super(config, setConfig, cache, setCache, driver, dbinfo, serverVersion);
 
@@ -172,6 +173,9 @@ export class TableGridDisplay extends GridDisplay {
   }
 
   addHintsToSelect(select: Select): boolean {
+    if (this.isRawMode) {
+      return false;
+    }
     let res = false;
     const groupColumns = this.groupColumns;
     for (const column of this.hintBaseColumns || this.getGridColumns()) {

packages/tools/src/SqlDumper.ts

@@ -18,6 +18,7 @@ import type {
   AlterProcessor,
   SqlObjectInfo,
   CallableObjectInfo,
+  SchedulerEventInfo,
 } from 'dbgate-types';
 import _isString from 'lodash/isString';
 import _isNumber from 'lodash/isNumber';
@@ -431,6 +432,14 @@ export class SqlDumper implements AlterProcessor {
   changeTriggerSchema(obj: TriggerInfo, newSchema: string) {}
   renameTrigger(obj: TriggerInfo, newSchema: string) {}
 
+  createSchedulerEvent(obj: SchedulerEventInfo) {
+    this.putRaw(obj.createSql);
+    this.endCommand();
+  }
+  dropSchedulerEvent(obj: SchedulerEventInfo, { testIfExists = false }) {
+    this.putCmd('^drop ^event  %f', obj);
+  }
+
   dropConstraintCore(cnt: ConstraintInfo) {
     this.putCmd('^alter ^table %f ^drop ^constraint %i', cnt, cnt.constraintName);
   }

packages/tools/src/SqlGenerator.ts

@@ -3,6 +3,7 @@ import type {
   EngineDriver,
   FunctionInfo,
   ProcedureInfo,
+  SchedulerEventInfo,
   TableInfo,
   TriggerInfo,
   ViewInfo,
@@ -49,12 +50,16 @@ interface SqlGeneratorOptions {
   dropTriggers: boolean;
   checkIfTriggerExists: boolean;
   createTriggers: boolean;
+
+  dropSchedulerEvents: boolean;
+  checkIfSchedulerEventExists: boolean;
+  createSchedulerEvents: boolean;
 }
 
 interface SqlGeneratorObject {
   schemaName: string;
   pureName: string;
-  objectTypeField: 'tables' | 'views' | 'procedures' | 'functions';
+  objectTypeField: 'tables' | 'views' | 'procedures' | 'functions' | 'triggers' | 'schedulerEvents';
 }
 
 export class SqlGenerator {
@@ -64,6 +69,7 @@ export class SqlGenerator {
   private procedures: ProcedureInfo[];
   private functions: FunctionInfo[];
   private triggers: TriggerInfo[];
+  private schedulerEvents: SchedulerEventInfo[];
   public dbinfo: DatabaseInfo;
   public isTruncated = false;
   public isUnhandledException = false;
@@ -83,6 +89,7 @@ export class SqlGenerator {
     this.procedures = this.extract('procedures');
     this.functions = this.extract('functions');
     this.triggers = this.extract('triggers');
+    this.schedulerEvents = this.extract('schedulerEvents');
   }
 
   private handleException = error => {
@@ -104,6 +111,8 @@ export class SqlGenerator {
       if (this.checkDumper()) return;
       this.dropObjects(this.triggers, 'Trigger');
       if (this.checkDumper()) return;
+      this.dropObjects(this.schedulerEvents, 'SchedulerEvent');
+      if (this.checkDumper()) return;
 
       this.dropTables();
       if (this.checkDumper()) return;
@@ -130,6 +139,8 @@ export class SqlGenerator {
       if (this.checkDumper()) return;
       this.createObjects(this.triggers, 'Trigger');
       if (this.checkDumper()) return;
+      this.createObjects(this.schedulerEvents, 'SchedulerEvent');
+      if (this.checkDumper()) return;
     } finally {
       process.off('uncaughtException', this.handleException);
     }

packages/web/src/appobj/DatabaseObjectAppObject.svelte

@@ -349,7 +349,34 @@
       case 'functions':
         return [...defaultDatabaseObjectAppObjectActions['functions']];
       case 'triggers':
-        return [...defaultDatabaseObjectAppObjectActions['triggers']];
+        return [
+          ...defaultDatabaseObjectAppObjectActions['triggers'],
+          hasPermission('dbops/model/edit') && {
+            label: 'Drop trigger',
+            isDrop: true,
+            requiresWriteAccess: true,
+          },
+          {
+            divider: true,
+          },
+          {
+            label: 'SQL generator',
+            submenu: [
+              {
+                label: 'CREATE TRIGGER',
+                sqlGeneratorProps: {
+                  createTriggers: true,
+                },
+              },
+              {
+                label: 'DROP TRIGGER',
+                sqlGeneratorProps: {
+                  dropTriggers: true,
+                },
+              },
+            ],
+          },
+        ];
       case 'collections':
         return [
           ...defaultDatabaseObjectAppObjectActions['collections'],
@@ -390,10 +417,11 @@
       case 'schedulerEvents':
         const menu: DbObjMenuItem[] = [
           ...defaultDatabaseObjectAppObjectActions['schedulerEvents'],
-          {
-            divider: true,
+          hasPermission('dbops/model/edit') && {
+            label: 'Drop event',
+            isDrop: true,
+            requiresWriteAccess: true,
           },
-          ,
         ];
 
         if (data?.status === 'ENABLED') {
@@ -408,6 +436,29 @@
           });
         }
 
+        menu.push(
+          {
+            divider: true,
+          },
+          {
+            label: 'SQL generator',
+            submenu: [
+              {
+                label: 'CREATE SCHEDULER EVENT',
+                sqlGeneratorProps: {
+                  createSchedulerEvents: true,
+                },
+              },
+              {
+                label: 'DROP SCHEDULER EVENT',
+                sqlGeneratorProps: {
+                  dropSchedulerEvents: true,
+                },
+              },
+            ],
+          }
+        );
+
         return menu;
     }
   }
@@ -671,7 +722,7 @@
       openDatabaseObjectDetail(
         menu.tab,
         menu.scriptTemplate,
-        { ...data, defaultActionId: menu.defaultActionId },
+        { ...data, defaultActionId: menu.defaultActionId, isRawMode: menu.isRawMode },
         menu.forceNewTab,
         menu.initialData,
         menu.icon,
@@ -706,7 +757,7 @@
   export async function openDatabaseObjectDetail(
     tabComponent,
     scriptTemplate,
-    { schemaName, pureName, conid, database, objectTypeField, defaultActionId },
+    { schemaName, pureName, conid, database, objectTypeField, defaultActionId, isRawMode },
     forceNewTab?,
     initialData?,
     icon?,
@@ -740,6 +791,7 @@
           objectTypeField,
           initialArgs: scriptTemplate ? { scriptTemplate } : null,
           defaultActionId,
+          isRawMode,
         },
       },
       initialData,
@@ -806,10 +858,11 @@
         database,
         objectTypeField,
         defaultActionId: prefferedAction.defaultActionId,
+        isRawMode: prefferedAction?.isRawMode ?? false,
       },
       forceNewTab,
       null,
-      null,
+      prefferedAction.icon,
       data,
       tabPreviewMode
     );

packages/web/src/appobj/SubProcedureLineList.svelte

@@ -9,6 +9,7 @@
   export let data;
   export let filter;
   export let isExpandedBySearch;
+  export let passProps;
 </script>
 
 <AppObjectList
@@ -18,4 +19,5 @@
   module={procedureLineAppObject}
   {filter}
   {isExpandedBySearch}
+  {passProps}
 />

packages/web/src/appobj/SubTableColumnList.svelte

@@ -7,6 +7,7 @@
   export let data;
   export let filter;
   export let isExpandedBySearch;
+  export let passProps;
 </script>
 
 <AppObjectList
@@ -18,4 +19,5 @@
   module={columnAppObject}
   {filter}
   {isExpandedBySearch}
+  {passProps}
 />

packages/web/src/appobj/appObjectTools.ts

@@ -15,6 +15,13 @@ function getTableLikeActions(dataTab) {
       tab: dataTab,
       defaultActionId: 'openTable',
     },
+    {
+      label: 'Open raw data',
+      tab: dataTab,
+      defaultActionId: 'openRawTable',
+      isRawMode: true,
+      icon: dataTab == 'ViewDataTab' ? 'img raw-view' : 'img raw-table',
+    },
     // {
     //   label: 'Open form',
     //   tab: dataTab,

packages/web/src/buttons/DropDownButton.svelte

@@ -33,6 +33,13 @@
   }
 </script>
 
-<InlineButton {square} {narrow} on:click={handleClick} bind:this={domButton} {disabled}>
+<InlineButton
+  {square}
+  {narrow}
+  on:click={handleClick}
+  bind:this={domButton}
+  {disabled}
+  data-testid={$$props['data-testid']}
+>
   <FontIcon icon={isLoading ? 'icon loading' : icon} />
 </InlineButton>

packages/web/src/datagrid/LoadingDataGridCore.svelte

@@ -16,6 +16,7 @@
 
   export let preprocessLoadedRow = null;
   export let setLoadedRows = null;
+  export let isRawMode = false;
 
   // export let griderFactory;
 
@@ -65,7 +66,7 @@
     if (nextRows.errorMessage) {
       errorMessage = nextRows.errorMessage;
     } else {
-      if (allRowCount == null) handleLoadRowCount();
+      if (allRowCount == null && !isRawMode) handleLoadRowCount();
 
       loadedRows = [...loadedRows, ...(preprocessLoadedRow ? nextRows.map(preprocessLoadedRow) : nextRows)];
       isLoadedAll = nextRows.length === 0;

packages/web/src/datagrid/TableDataGrid.svelte

@@ -45,6 +45,8 @@
   export let setCache;
   export let multipleGridsOnTab = false;
 
+  export let isRawMode = false;
+
   $: connection = useConnectionInfo({ conid });
   $: dbinfo = useDatabaseInfo({ conid, database });
   $: serverVersion = useDatabaseServerVersion({ conid, database });
@@ -71,7 +73,8 @@
           { showHintColumns: getBoolSettingsValue('dataGrid.showHintColumns', true) },
           $serverVersion,
           table => getDictionaryDescription(table, conid, database, $apps, $connections),
-          $connection?.isReadOnly
+          $connection?.isReadOnly,
+          isRawMode
         )
       : null;
 

packages/web/src/elements/SearchInput.svelte

@@ -43,6 +43,7 @@
   on:keydown={handleKeyDown}
   bind:this={domInput}
   on:focus={e => domInput.select()}
+  data-testid={$$props['data-testid']}
 />
 
 <style>

packages/web/src/icons/FontIcon.svelte

@@ -255,9 +255,11 @@
     'img database': 'mdi mdi-database color-icon-gold',
     'img sqlite-database': 'mdi mdi-database color-icon-blue',
     'img table': 'mdi mdi-table color-icon-blue',
+    'img raw-table': 'mdi mdi-table-large color-icon-blue',
     'img collection': 'mdi mdi-table color-icon-red',
     'img query-data': 'mdi mdi-table color-icon-yellow',
     'img view': 'mdi mdi-table color-icon-magenta',
+    'img raw-view': 'mdi mdi-table-large color-icon-magenta',
     'img procedure': 'mdi mdi-cog color-icon-blue',
     'img function': 'mdi mdi-function-variant',
     'img table-structure': 'mdi mdi-tools color-icon-blue',

packages/web/src/modals/SqlGeneratorModal.svelte

@@ -74,7 +74,7 @@
   $: generatePreview($valuesStore, $checkedObjectsStore);
 
   $: objectList = _.flatten(
-    ['tables', 'views', 'matviews', 'procedures', 'functions'].map(objectTypeField =>
+    ['tables', 'views', 'matviews', 'procedures', 'functions', 'triggers', 'schedulerEvents'].map(objectTypeField =>
       _.sortBy(
         (($dbinfo || {})[objectTypeField] || []).map(obj => ({ ...obj, objectTypeField })),
         ['schemaName', 'pureName']
@@ -214,8 +214,8 @@
 
                   <FormCheckboxField label="Truncate tables (delete all rows)" name="truncate" />
 
-                  {#each ['View', 'Matview', 'Procedure', 'Function', 'Trigger'] as objtype}
-                    <div class="obj-heading">{getObjectTypeFieldLabel(objtype.toLowerCase() + 's')}</div>
+                  {#each ['View', 'Matview', 'Procedure', 'Function', 'Trigger', 'SchedulerEvent'] as objtype}
+                    <div class="obj-heading">{getObjectTypeFieldLabel(objtype + 's')}</div>
                     <FormCheckboxField label="Create" name={`create${objtype}s`} />
                     <FormCheckboxField label="Drop" name={`drop${objtype}s`} />
                     {#if values[`drop${objtype}s`]}

packages/web/src/plugins/AvailablePluginsList.svelte

@@ -7,15 +7,16 @@
   import { useApiCall } from '../utility/api';
   import WidgetsInnerContainer from '../widgets/WidgetsInnerContainer.svelte';
   import PluginsList from './PluginsList.svelte';
+  import { filterName } from 'dbgate-tools';
 
   let filter = '';
-  let search = '';
+  // let search = '';
 
-  $: plugins = useApiCall('plugins/search', { filter: search }, []);
+  $: plugins = useApiCall('plugins/search', { filter: '' }, []);
 
-  const setDebouncedFilter = _.debounce(value => (search = value), 500);
-
-  $: setDebouncedFilter(filter);
+  // const setDebouncedFilter = _.debounce(value => (search = value), 500);
+  //
+  // $: setDebouncedFilter(filter);
 </script>
 
 <SearchBoxWrapper>
@@ -25,6 +26,6 @@
   {#if $plugins?.errorMessage}
     <ErrorInfo message={$plugins?.errorMessage} />
   {:else}
-    <PluginsList plugins={$plugins} />
+    <PluginsList plugins={$plugins.filter(i => filterName(filter, i.name))} />
   {/if}
 </WidgetsInnerContainer>

packages/web/src/tabs/TableDataTab.svelte

@@ -55,7 +55,7 @@
     onClick: () => getCurrentEditor().startAutoRefresh(),
   });
 
-  export const matchingProps = ['conid', 'database', 'schemaName', 'pureName'];
+  export const matchingProps = ['conid', 'database', 'schemaName', 'pureName', 'isRawMode'];
   export const allowAddToFavorites = props => true;
   export const allowSwitchDatabase = props => true;
 </script>
@@ -108,6 +108,7 @@
   export let database;
   export let schemaName;
   export let pureName;
+  export let isRawMode = false;
 
   export const activator = createActivator('TableDataTab', true);
 
@@ -245,6 +246,8 @@
       ...INTERVALS.map(seconds => ({ command: `tableData.setAutoRefresh.${seconds}`, text: `...${seconds} seconds` })),
     ];
   }
+
+  $: console.log('isRawMode', isRawMode);
 </script>
 
 <ToolStripContainer>

packages/web/src/widgets/ConnectionList.svelte

@@ -215,6 +215,7 @@
     onFocusFilteredList={() => {
       domListHandler?.focusFirst();
     }}
+    data-testid="ConnectionList_search"
   />
   <CloseSearchButton bind:filter />
   <DropDownButton

packages/web/src/widgets/SqlObjectList.svelte

@@ -214,6 +214,7 @@
       onFocusFilteredList={() => {
         domListHandler?.focusFirst();
       }}
+      data-testid="SqlObjectList_search"
     />
     <CloseSearchButton bind:filter />
     <DropDownButton
@@ -221,6 +222,7 @@
       menu={createSearchMenu}
       square={!!filter}
       narrow={false}
+      data-testid="SqlObjectList_searchMenuDropDown"
     />
     {#if !filter}
       <DropDownButton icon="icon plus-thick" menu={createAddMenu} />

plugins/dbgate-plugin-oracle/src/frontend/driver.js

@@ -10,7 +10,7 @@ const dialect = {
   limitSelect: false,
   offsetFetchRangeSyntax: true,
   rowNumberOverPaging: true,
-  ilike: true,
+  ilike: false,
   // stringEscapeChar: '\\',
   stringEscapeChar: "'",
   fallbackDataType: 'varchar(250)',