electron paste WIP

feat: transform geography binary data to wkt

.github/workflows/build-app-pro.yaml

@@ -123,6 +123,7 @@ jobs:
           cp ../dbgate-merged/app/dist/*win_arm64.zip artifacts/dbgate-premium-windows-latest-arm64.zip || true
           cp ../dbgate-merged/app/dist/*-mac_universal.dmg artifacts/dbgate-premium-latest.dmg || true
           cp ../dbgate-merged/app/dist/*-mac_x64.dmg artifacts/dbgate-premium-latest-x64.dmg || true
+          cp ../dbgate-merged/app/dist/*-mac_arm64.dmg artifacts/dbgate-premium-latest-arm64.dmg || true
 
           mv ../dbgate-merged/app/dist/*.exe artifacts/ || true
           mv ../dbgate-merged/app/dist/*.zip artifacts/ || true

.github/workflows/build-app.yaml

@@ -98,6 +98,7 @@ jobs:
           cp app/dist/*win_arm64.zip artifacts/dbgate-windows-latest-arm64.zip || true
           cp app/dist/*-mac_universal.dmg artifacts/dbgate-latest.dmg || true
           cp app/dist/*-mac_x64.dmg artifacts/dbgate-latest-x64.dmg || true
+          cp app/dist/*-mac_arm64.dmg artifacts/dbgate-latest-arm64.dmg || true
 
           mv app/dist/*.exe artifacts/ || true
           mv app/dist/*.zip artifacts/ || true

.github/workflows/e2e-tests.yaml

@@ -1,41 +0,0 @@
-name: E2E tests
-on:
-  push:
-    branches:
-      - master
-      - develop
-      - 'feature/**'
-
-jobs:
-  e2e-tests:
-    runs-on: ubuntu-latest
-    container: node:18
-
-    steps:
-      - name: Install dependencies
-        run: |
-          apt-get update
-          apt-get install -y xvfb libgtk2.0-0 libgtk-3-0 libgbm-dev libnotify-dev libnss3 libxss1 libasound2 libxtst6
-      - uses: actions/checkout@v2
-        with:
-          fetch-depth: 1
-      - name: yarn install
-        run: |
-          yarn install
-      - name: Build
-        run: |
-          yarn prepare:packer
-      - name: yarn install cypress
-        run: |
-          cd e2e-tests
-          yarn install
-      - name: Run Cypress tests
-        run: |
-          cd e2e-tests
-          yarn test:ci
-
-    services:
-      mysql:
-        image: mysql:8.0.18
-        env:
-          MYSQL_ROOT_PASSWORD: Pwd2020Db

.github/workflows/run-tests.yaml

@@ -12,16 +12,27 @@ jobs:
     container: node:18
 
     steps:
-      - name: Context
-        env:
-          GITHUB_CONTEXT: ${{ toJson(github) }}
-        run: echo "$GITHUB_CONTEXT"
+      - name: Install dependencies for cypress
+        run: |
+          apt-get update
+          apt-get install -y xvfb libgtk2.0-0 libgtk-3-0 libgbm-dev libnotify-dev libnss3 libxss1 libasound2 libxtst6
       - uses: actions/checkout@v2
         with:
           fetch-depth: 1
       - name: yarn install
         run: |
           yarn install
+      - name: Build packer dist for cypress
+        run: |
+          yarn prepare:packer
+      - name: yarn install cypress
+        run: |
+          cd e2e-tests
+          yarn install
+      - name: Run Cypress tests
+        run: |
+          cd e2e-tests
+          yarn test:ci
       - name: Integration tests
         run: |
           cd integration-tests
@@ -84,5 +95,10 @@ jobs:
         env:
           CLICKHOUSE_ADMIN_PASSWORD: Pwd2020Db
 
+      oracle:
+        image: gvenzl/oracle-xe:21-slim
+        env:
+          ORACLE_PASSWORD: Pwd2020Db
+
       # cockroachdb:
       #   image: cockroachdb/cockroach

CHANGELOG.md

@@ -8,6 +8,22 @@ Builds:
  - linux - application for linux
  - win - application for Windows
 
+### 6.1.0
+- ADDED: Fulltext search in DB model and connections, highlight searched names
+- ADDED: Tab preview mode configuration #963
+- CHANGED: Single-click to open server connection/database + ability to configure this #959
+- ADDED: Option to align numbers to right in data grid #957
+- FIXED: Cursor Becomes Stuck When Escaping "Case" #954
+- ADDED: Postgres GEOGRAPHY types are shown on map, event when executing query #948
+- FIXED: Error displaying CLOB and NCLOB in Oracle
+- FIXED: Analysing of foreign keys in Postgres and MS SQL, when the same FKS are used across different schemas
+- ADDED: Support of views, procedures, functions to Oracle. Added integration tests for Oracle
+- ADDED: Display "No rows" message, quick add new row
+- ADDED: Choose default database from list
+- ADDED: Default database is automatically selected on connect
+- ADDED: Apple-Silicon-only build for Mac #949
+- ADDED: Display comment into tables and column list #755
+
 ### 6.0.0
 - ADDED: Order or filter the indexes for huge tables #922
 - ADDED: Empty string filters

app/package.json

@@ -38,7 +38,8 @@
         "target": "default",
         "arch": [
           "universal",
-          "x64"
+          "x64",
+          "arm64"
         ]
       }
     },

common/volatilePackages.js

@@ -19,6 +19,7 @@ const volatilePackages = [
   'activedirectory2',
   'axios',
   'ssh2',
+  'wkx',
 ];
 
 module.exports = volatilePackages;

e2e-tests/cypress.config.js

@@ -1,6 +1,6 @@
 const { defineConfig } = require('cypress');
 const killPort = require('kill-port');
-const { clearDataWithBackup } = require('./e2eTestTools');
+const { clearTestingData } = require('./e2eTestTools');
 const waitOn = require('wait-on');
 const { exec } = require('child_process');
 
@@ -10,7 +10,7 @@ module.exports = defineConfig({
       // implement node event listeners here
 
       on('before:spec', async details => {
-        await clearDataWithBackup();
+        await clearTestingData();
         
         if (config.isInteractive) {
           await killPort(3000);

e2e-tests/e2eTestTools.js

@@ -4,29 +4,26 @@ const fs = require('fs');
 
 const baseDir = path.join(os.homedir(), '.dbgate');
 
-function createTimeStamp() {
-  const now = new Date();
-  const year = now.getFullYear();
-  const month = String(now.getMonth() + 1).padStart(2, '0'); // měsíc je 0-indexovaný
-  const day = String(now.getDate()).padStart(2, '0');
-  const hours = String(now.getHours()).padStart(2, '0');
-  const minutes = String(now.getMinutes()).padStart(2, '0');
-  const seconds = String(now.getSeconds()).padStart(2, '0');
+// function createTimeStamp() {
+//   const now = new Date();
+//   const year = now.getFullYear();
+//   const month = String(now.getMonth() + 1).padStart(2, '0'); // měsíc je 0-indexovaný
+//   const day = String(now.getDate()).padStart(2, '0');
+//   const hours = String(now.getHours()).padStart(2, '0');
+//   const minutes = String(now.getMinutes()).padStart(2, '0');
+//   const seconds = String(now.getSeconds()).padStart(2, '0');
 
-  // Poskládáme datum a čas do názvu souboru
-  const ts = `${year}-${month}-${day}_${hours}-${minutes}-${seconds}`;
-  return ts;
-}
+//   // Poskládáme datum a čas do názvu souboru
+//   const ts = `${year}-${month}-${day}_${hours}-${minutes}-${seconds}`;
+//   return ts;
+// }
 
-function clearDataWithBackup() {
-  if (fs.existsSync(path.join(baseDir, 'connections.jsonl'))) {
-    fs.renameSync(
-      path.join(baseDir, 'connections.jsonl'),
-      path.join(baseDir, `connections-${createTimeStamp()}.jsonl.bak`)
-    );
+function clearTestingData() {
+  if (fs.existsSync(path.join(baseDir, 'connections-e2etests.jsonl'))) {
+    fs.unlinkSync(path.join(baseDir, 'connections-e2etests.jsonl'));
   }
 }
 
 module.exports = {
-  clearDataWithBackup,
+  clearTestingData,
 };

integration-tests/__tests__/alter-database.spec.js

@@ -3,9 +3,15 @@ const _ = require('lodash');
 const fp = require('lodash/fp');
 const { testWrapper } = require('../tools');
 const engines = require('../engines');
-const { getAlterDatabaseScript, extendDatabaseInfo, generateDbPairingId } = require('dbgate-tools');
+const {
+  getAlterDatabaseScript,
+  extendDatabaseInfo,
+  generateDbPairingId,
+  formatQueryWithoutParams,
+  runCommandOnDriver,
+} = require('dbgate-tools');
 
-const initSql = ['CREATE TABLE t1 (id int primary key)', 'CREATE TABLE t2 (id int primary key)'];
+const initSql = ['CREATE TABLE ~t1 (~id int primary key)', 'CREATE TABLE ~t2 (~id int primary key)'];
 
 function flatSource(engineCond = x => !x.skipReferences) {
   return _.flatten(
@@ -16,13 +22,14 @@ function flatSource(engineCond = x => !x.skipReferences) {
 }
 
 async function testDatabaseDiff(conn, driver, mangle, createObject = null) {
-  await driver.query(conn, `create table t1 (id int not null primary key)`);
+  await runCommandOnDriver(conn, driver, `create table ~t1 (~id int not null primary key)`);
 
-  await driver.query(
+  await runCommandOnDriver(
     conn,
-    `create table t2 (
-    id int not null primary key, 
-    t1_id int null references t1(id)
+    driver,
+    `create table ~t2 (
+    ~id int not null primary key, 
+    ~t1_id int null references ~t1(~id)
   )`
   );
 
@@ -63,7 +70,7 @@ describe('Alter database', () => {
         db => {
           _.remove(db[type], x => x.pureName == 'obj1');
         },
-        object.create1
+        formatQueryWithoutParams(driver, object.create1)
       );
       expect(db[type].length).toEqual(0);
     })
@@ -72,9 +79,9 @@ describe('Alter database', () => {
   test.each(flatSource(x => x.supportRenameSqlObject))(
     'Rename object - %s - %s',
     testWrapper(async (conn, driver, type, object, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, object.create1, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.create1);
 
       const structure = extendDatabaseInfo(await driver.analyseFull(conn));
 

integration-tests/__tests__/alter-table.spec.js

@@ -4,7 +4,12 @@ const fp = require('lodash/fp');
 const { testWrapper } = require('../tools');
 const engines = require('../engines');
 const crypto = require('crypto');
-const { getAlterTableScript, extendDatabaseInfo, generateDbPairingId } = require('dbgate-tools');
+const {
+  getAlterTableScript,
+  extendDatabaseInfo,
+  generateDbPairingId,
+  formatQueryWithoutParams,
+} = require('dbgate-tools');
 
 function pickImportantTableInfo(engine, table) {
   const props = ['columnName', 'defaultValue'];
@@ -15,7 +20,10 @@ function pickImportantTableInfo(engine, table) {
     columns: table.columns
       .filter(x => x.columnName != 'rowid')
       .map(fp.pick(props))
-      .map(props => _.omitBy(props, x => x == null)),
+      .map(props => _.omitBy(props, x => x == null))
+      .map(props =>
+        _.omitBy(props, (v, k) => k == 'defaultValue' && v == 'NULL' && engine.setNullDefaultInsteadOfDrop)
+      ),
   };
 }
 
@@ -25,27 +33,36 @@ function checkTableStructure(engine, t1, t2) {
 }
 
 async function testTableDiff(engine, conn, driver, mangle) {
-  await driver.query(conn, `create table t0 (id int not null primary key)`);
+  await driver.query(conn, formatQueryWithoutParams(driver, `create table ~t0 (~id int not null primary key)`));
 
   await driver.query(
     conn,
-    `create table t1 (
-    col_pk int not null primary key, 
-    col_std int, 
-    col_def int default 12,
-    ${engine.skipReferences ? '' : 'col_fk int references t0(id),'}
-    col_idx int,
-    col_uq int ${engine.skipUnique ? '' : 'unique'} ,
-    col_ref int ${engine.skipUnique ? '' : 'unique'}
+    formatQueryWithoutParams(
+      driver,
+      `create table ~t1 (
+    ~col_pk int not null primary key, 
+    ~col_std int, 
+    ~col_def int default 12,
+    ${engine.skipReferences ? '' : '~col_fk int references ~t0(~id),'}
+    ~col_idx int,
+    ~col_uq int ${engine.skipUnique ? '' : 'unique'} ,
+    ~col_ref int ${engine.skipUnique ? '' : 'unique'}
   )`
+    )
   );
 
   if (!engine.skipIndexes) {
-    await driver.query(conn, `create index idx1 on t1(col_idx)`);
+    await driver.query(conn, formatQueryWithoutParams(driver, `create index ~idx1 on ~t1(~col_idx)`));
   }
 
   if (!engine.skipReferences) {
-    await driver.query(conn, `create table t2 (id int not null primary key, fkval int null references t1(col_ref))`);
+    await driver.query(
+      conn,
+      formatQueryWithoutParams(
+        driver,
+        `create table ~t2 (~id int not null primary key, ~fkval int null references ~t1(~col_ref))`
+      )
+    );
   }
 
   const tget = x => x.tables.find(y => y.pureName == 't1');
@@ -175,5 +192,4 @@ describe('Alter table', () => {
   //     });
   //   })
   // );
-
 });

integration-tests/__tests__/data-duplicator.spec.js

@@ -2,7 +2,7 @@ const engines = require('../engines');
 const stream = require('stream');
 const { testWrapper } = require('../tools');
 const dataDuplicator = require('dbgate-api/src/shell/dataDuplicator');
-const { runCommandOnDriver } = require('dbgate-tools');
+const { runCommandOnDriver, runQueryOnDriver } = require('dbgate-tools');
 
 describe('Data duplicator', () => {
   test.each(engines.filter(x => !x.skipDataDuplicator).map(engine => [engine.label, engine]))(
@@ -84,10 +84,10 @@ describe('Data duplicator', () => {
         ],
       });
 
-      const res1 = await driver.query(conn, `select count(*) as cnt from t1`);
+      const res1 = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t1`));
       expect(res1.rows[0].cnt.toString()).toEqual('6');
 
-      const res2 = await driver.query(conn, `select count(*) as cnt from t2`);
+      const res2 = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t2`));
       expect(res2.rows[0].cnt.toString()).toEqual('6');
     })
   );
@@ -145,13 +145,15 @@ describe('Data duplicator', () => {
         },
       });
 
-      const res1 = await driver.query(conn, `select count(*) as cnt from t1`);
+      const res1 = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t1`));
       expect(res1.rows[0].cnt.toString()).toEqual('1');
 
-      const res2 = await driver.query(conn, `select count(*) as cnt from t2`);
+      const res2 = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t2`));
       expect(res2.rows[0].cnt.toString()).toEqual('2');
 
-      const res3 = await driver.query(conn, `select count(*) as cnt from t2 where valfk is not null`);
+      const res3 = await runQueryOnDriver(conn, driver, dmp =>
+        dmp.put(`select count(*) as ~cnt from ~t2 where ~valfk is not null`)
+      );
       expect(res3.rows[0].cnt.toString()).toEqual('1');
     })
   );

integration-tests/__tests__/db-import.spec.js

@@ -5,6 +5,7 @@ const tableWriter = require('dbgate-api/src/shell/tableWriter');
 const copyStream = require('dbgate-api/src/shell/copyStream');
 const importDatabase = require('dbgate-api/src/shell/importDatabase');
 const fakeObjectReader = require('dbgate-api/src/shell/fakeObjectReader');
+const { runQueryOnDriver } = require('dbgate-tools');
 
 function createImportStream() {
   const pass = new stream.PassThrough({
@@ -37,7 +38,7 @@ describe('DB Import', () => {
       });
       await copyStream(reader, writer);
 
-      const res = await driver.query(conn, `select count(*) as cnt from t1`);
+      const res = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t1`));
       expect(res.rows[0].cnt.toString()).toEqual('6');
     })
   );
@@ -65,10 +66,10 @@ describe('DB Import', () => {
       });
       await copyStream(reader2, writer2);
 
-      const res1 = await driver.query(conn, `select count(*) as cnt from t1`);
+      const res1 = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t1`));
       expect(res1.rows[0].cnt.toString()).toEqual('6');
 
-      const res2 = await driver.query(conn, `select count(*) as cnt from t2`);
+      const res2 = await runQueryOnDriver(conn, driver, dmp => dmp.put(`select count(*) as ~cnt from ~t2`));
       expect(res2.rows[0].cnt.toString()).toEqual('6');
     })
   );

integration-tests/__tests__/deploy-database.spec.js

@@ -4,7 +4,7 @@ const { testWrapper, testWrapperPrepareOnly } = require('../tools');
 const _ = require('lodash');
 const engines = require('../engines');
 const deployDb = require('dbgate-api/src/shell/deployDb');
-const { databaseInfoFromYamlModel } = require('dbgate-tools');
+const { databaseInfoFromYamlModel, runQueryOnDriver, formatQueryWithoutParams } = require('dbgate-tools');
 const generateDeploySql = require('dbgate-api/src/shell/generateDeploySql');
 const connectUtility = require('dbgate-api/src/utility/connectUtility');
 
@@ -69,6 +69,29 @@ function checkStructure(
   }
 }
 
+// function convertObjectText(text, driver) {
+//   if (!text) return undefined;
+//   text = formatQueryWithoutParams(driver, text);
+//   if (driver.dialect.requireFromDual && text.startsWith('create view ') && !text.includes('from')) {
+//     text = text + ' from dual';
+//   }
+//   return text;
+// }
+
+// function convertModelToEngine(model, driver) {
+//   return model.map(x => ({
+//     ...x,
+//     text: convertObjectText(x.text, driver),
+//   }));
+// }
+
+function convertModelToEngine(model, driver) {
+  return model.map(x => ({
+    ...x,
+    text: x.text ? formatQueryWithoutParams(driver, x.text) : undefined,
+  }));
+}
+
 async function testDatabaseDeploy(engine, conn, driver, dbModelsYaml, options) {
   const { testEmptyLastScript, finalCheckAgainstModel, markDeleted, allowDropStatements } = options || {};
   let index = 0;
@@ -83,13 +106,13 @@ async function testDatabaseDeploy(engine, conn, driver, dbModelsYaml, options) {
 
   for (const loadedDbModel of dbModelsYaml) {
     if (_.isString(loadedDbModel)) {
-      await driver.script(conn, loadedDbModel);
+      await driver.script(conn, formatQueryWithoutParams(driver, loadedDbModel));
     } else {
       const { sql, isEmpty } = await generateDeploySql({
         systemConnection: conn.isPreparedOnly ? undefined : conn,
         connection: conn.isPreparedOnly ? conn : undefined,
         driver,
-        loadedDbModel,
+        loadedDbModel: convertModelToEngine(loadedDbModel, driver),
         dbdiffOptionsExtra,
       });
       console.debug('Generated deploy script:', sql);
@@ -106,7 +129,7 @@ async function testDatabaseDeploy(engine, conn, driver, dbModelsYaml, options) {
         systemConnection: conn.isPreparedOnly ? undefined : conn,
         connection: conn.isPreparedOnly ? conn : undefined,
         driver,
-        loadedDbModel,
+        loadedDbModel: convertModelToEngine(loadedDbModel, driver),
         dbdiffOptionsExtra,
       });
     }
@@ -117,7 +140,12 @@ async function testDatabaseDeploy(engine, conn, driver, dbModelsYaml, options) {
   const dbhan = conn.isPreparedOnly ? await connectUtility(driver, conn, 'read') : conn;
   const structure = await driver.analyseFull(dbhan);
   if (conn.isPreparedOnly) await driver.close(dbhan);
-  checkStructure(engine, structure, finalCheckAgainstModel ?? _.findLast(dbModelsYaml, x => _.isArray(x)), options);
+  checkStructure(
+    engine,
+    structure,
+    convertModelToEngine(finalCheckAgainstModel ?? _.findLast(dbModelsYaml, x => _.isArray(x)), driver),
+    options
+  );
 }
 
 describe('Deploy database', () => {
@@ -339,7 +367,7 @@ describe('Deploy database', () => {
         ],
       ]);
 
-      const res = await driver.query(conn, `select count(*) as cnt from t1`);
+      const res = await runQueryOnDriver(conn, driver, `select count(*) as ~cnt from ~t1`);
       expect(res.rows[0].cnt.toString()).toEqual('3');
     })
   );
@@ -386,7 +414,7 @@ describe('Deploy database', () => {
         ],
       ]);
 
-      const res = await driver.query(conn, `select val from t1 where id = 2`);
+      const res = await runQueryOnDriver(conn, driver, `select ~val from ~t1 where ~id = 2`);
       expect(res.rows[0].val.toString()).toEqual('5');
     })
   );
@@ -414,8 +442,8 @@ describe('Deploy database', () => {
         ],
       ]);
 
-      await driver.query(conn, `insert into t1 (id) values (1)`);
-      const res = await driver.query(conn, ` select val from t1 where id = 1`);
+      await runQueryOnDriver(conn, driver, `insert into ~t1 (~id) values (1)`);
+      const res = await runQueryOnDriver(conn, driver, ` select ~val from ~t1 where ~id = 1`);
       expect(res.rows[0].val.toString().substring(0, 2)).toEqual('20');
     })
   );
@@ -438,7 +466,7 @@ describe('Deploy database', () => {
             },
           },
         ],
-        'insert into t1 (id, val) values (1, 1); insert into t1 (id) values (2)',
+        'insert into ~t1 (~id, ~val) values (1, 1); insert into ~t1 (~id) values (2)',
         [
           {
             name: 't1.table.yaml',
@@ -452,16 +480,16 @@ describe('Deploy database', () => {
             },
           },
         ],
-        'insert into t1 (id) values (3);',
+        'insert into ~t1 (~id) values (3);',
       ]);
 
-      const res1 = await driver.query(conn, `select val from t1 where id = 1`);
+      const res1 = await runQueryOnDriver(conn, driver, `select ~val from ~t1 where ~id = 1`);
       expect(res1.rows[0].val).toEqual(1);
 
-      const res2 = await driver.query(conn, `select val from t1 where id = 2`);
+      const res2 = await runQueryOnDriver(conn, driver, `select ~val from ~t1 where ~id = 2`);
       expect(res2.rows[0].val).toEqual(20);
 
-      const res3 = await driver.query(conn, `select val from t1 where id = 3`);
+      const res3 = await runQueryOnDriver(conn, driver, `select ~val from ~t1 where ~id = 3`);
       expect(res2.rows[0].val).toEqual(20);
     })
   );
@@ -525,17 +553,17 @@ describe('Deploy database', () => {
 
   const V1 = {
     name: 'v1.view.sql',
-    text: 'create view v1 as select * from t1',
+    text: 'create view ~v1 as select * from ~t1',
   };
 
   const V1_VARIANT2 = {
     name: 'v1.view.sql',
-    text: 'create view v1 as select 1 as c1',
+    text: 'create view ~v1 as select ~id + ~id ~idsum from ~t1',
   };
 
   const V1_DELETED = {
     name: '_deleted_v1.view.sql',
-    text: 'create view _deleted_v1 as select * from t1',
+    text: 'create view ~_deleted_v1 as select * from ~t1',
   };
 
   test.each(engines.map(engine => [engine.label, engine]))(
@@ -682,15 +710,15 @@ describe('Deploy database', () => {
         [
           {
             name: '1.predeploy.sql',
-            text: 'create table t1 (id int primary key); insert into t1 (id) values (1);',
+            text: 'create table ~t1 (~id int primary key); insert into ~t1 (~id) values (1);',
           },
         ],
       ]);
 
-      const res1 = await driver.query(conn, 'SELECT COUNT(*) AS cnt FROM t1');
+      const res1 = await runQueryOnDriver(conn, driver, 'SELECT COUNT(*) AS ~cnt FROM ~t1');
       expect(res1.rows[0].cnt == 1).toBeTruthy();
 
-      const res2 = await driver.query(conn, 'SELECT COUNT(*) AS cnt FROM dbgate_deploy_journal');
+      const res2 = await runQueryOnDriver(conn, driver, 'SELECT COUNT(*) AS ~cnt FROM ~dbgate_deploy_journal');
       expect(res2.rows[0].cnt == 1).toBeTruthy();
     })
   );
@@ -702,48 +730,53 @@ describe('Deploy database', () => {
         [
           {
             name: 't1.uninstall.sql',
-            text: 'drop table t1',
+            text: 'drop table ~t1',
           },
           {
             name: 't1.install.sql',
-            text: 'create table t1 (id int primary key); insert into t1 (id) values (1)',
+            text: 'create table ~t1 (~id int primary key); insert into ~t1 (~id) values (1)',
           },
           {
             name: 't2.once.sql',
-            text: 'create table t2 (id int primary key); insert into t2 (id) values (1)',
+            text: 'create table ~t2 (~id int primary key); insert into ~t2 (~id) values (1)',
           },
         ],
         [
           {
             name: 't1.uninstall.sql',
-            text: 'drop table t1',
+            text: 'drop table ~t1',
           },
           {
             name: 't1.install.sql',
-            text: 'create table t1 (id int primary key, val int); insert into t1 (id, val) values (1, 11)',
+            text: 'create table ~t1 (~id int primary key, ~val int); insert into ~t1 (~id, ~val) values (1, 11)',
           },
           {
             name: 't2.once.sql',
-            text: 'insert into t2 (id) values (2)',
+            text: 'insert into ~t2 (~id) values (2)',
           },
         ],
       ]);
 
-      const res1 = await driver.query(conn, 'SELECT val from t1 where id = 1');
+      const res1 = await runQueryOnDriver(conn, driver, 'SELECT ~val from ~t1 where ~id = 1');
       expect(res1.rows[0].val == 11).toBeTruthy();
 
-      const res2 = await driver.query(conn, 'SELECT COUNT(*) AS cnt FROM t2');
+      const res2 = await runQueryOnDriver(conn, driver, 'SELECT COUNT(*) AS ~cnt FROM ~t2');
       expect(res2.rows[0].cnt == 1).toBeTruthy();
 
-      const res3 = await driver.query(conn, 'SELECT COUNT(*) AS cnt FROM dbgate_deploy_journal');
+      const res3 = await runQueryOnDriver(conn, driver, 'SELECT COUNT(*) AS ~cnt FROM ~dbgate_deploy_journal');
       expect(res3.rows[0].cnt == 3).toBeTruthy();
 
-      const res4 = await driver.query(conn, "SELECT run_count from dbgate_deploy_journal where name = 't2.once.sql'");
+      const res4 = await runQueryOnDriver(
+        conn,
+        driver,
+        "SELECT ~run_count from ~dbgate_deploy_journal where ~name = 't2.once.sql'"
+      );
       expect(res4.rows[0].run_count == 1).toBeTruthy();
 
-      const res5 = await driver.query(
+      const res5 = await runQueryOnDriver(
         conn,
-        "SELECT run_count from dbgate_deploy_journal where name = 't1.install.sql'"
+        driver,
+        "SELECT ~run_count from ~dbgate_deploy_journal where ~name = 't1.install.sql'"
       );
       expect(res5.rows[0].run_count == 2).toBeTruthy();
     })

integration-tests/__tests__/object-analyse.spec.js

@@ -1,8 +1,9 @@
 const { testWrapper } = require('../tools');
 const engines = require('../engines');
 const _ = require('lodash');
+const { formatQueryWithoutParams, runCommandOnDriver } = require('dbgate-tools');
 
-const initSql = ['CREATE TABLE t1 (id int primary key)', 'CREATE TABLE t2 (id int primary key)'];
+const initSql = ['CREATE TABLE ~t1 (~id int primary key)', 'CREATE TABLE ~t2 (~id int primary key)'];
 
 function flatSource() {
   return _.flatten(
@@ -34,9 +35,9 @@ describe('Object analyse', () => {
   test.each(flatSource())(
     'Full analysis - %s - %s',
     testWrapper(async (conn, driver, type, object, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, object.create1, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.create1);
       const structure = await driver.analyseFull(conn);
 
       expect(structure[type].length).toEqual(1);
@@ -47,11 +48,11 @@ describe('Object analyse', () => {
   test.each(flatSource())(
     'Incremental analysis - add - %s - %s',
     testWrapper(async (conn, driver, type, object, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, object.create2, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.create2);
       const structure1 = await driver.analyseFull(conn);
-      await driver.query(conn, object.create1, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.create1);
       const structure2 = await driver.analyseIncremental(conn, structure1);
 
       expect(structure2[type].length).toEqual(2);
@@ -62,12 +63,12 @@ describe('Object analyse', () => {
   test.each(flatSource())(
     'Incremental analysis - drop - %s - %s',
     testWrapper(async (conn, driver, type, object, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, object.create1, { discardResult: true });
-      await driver.query(conn, object.create2, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.create1);
+      await runCommandOnDriver(conn, driver, object.create2);
       const structure1 = await driver.analyseFull(conn);
-      await driver.query(conn, object.drop2, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.drop2);
       const structure2 = await driver.analyseIncremental(conn, structure1);
 
       expect(structure2[type].length).toEqual(1);
@@ -78,11 +79,11 @@ describe('Object analyse', () => {
   test.each(flatSource())(
     'Create SQL - add - %s - %s',
     testWrapper(async (conn, driver, type, object, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, object.create1, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.create1);
       const structure1 = await driver.analyseFull(conn);
-      await driver.query(conn, object.drop1, { discardResult: true });
+      await runCommandOnDriver(conn, driver, object.drop1);
       const structure2 = await driver.analyseIncremental(conn, structure1);
       expect(structure2[type].length).toEqual(0);
 
@@ -98,10 +99,10 @@ describe('Object analyse', () => {
   test.each(flatSourceParameters())(
     'Test parameters simple analyse - %s - %s',
     testWrapper(async (conn, driver, testName, parameter, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
-      for (const sql of engine.parametersOtherSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
+      for (const sql of engine.parametersOtherSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, parameter.create, { discardResult: true });
+      await runCommandOnDriver(conn, driver, parameter.create);
       const structure = await driver.analyseFull(conn);
 
       const parameters = structure[parameter.objectTypeField].find(x => x.pureName == 'obj1').parameters;
@@ -116,15 +117,15 @@ describe('Object analyse', () => {
   test.each(flatSourceParameters())(
     'Test parameters create SQL - %s - %s',
     testWrapper(async (conn, driver, testName, parameter, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
-      for (const sql of engine.parametersOtherSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) await runCommandOnDriver(conn, driver, sql);
+      for (const sql of engine.parametersOtherSql) await runCommandOnDriver(conn, driver, sql);
 
-      await driver.query(conn, parameter.create, { discardResult: true });
+      await runCommandOnDriver(conn, driver, parameter.create);
       const structure1 = await driver.analyseFull(conn);
-      await driver.query(conn, parameter.drop, { discardResult: true });
+      await runCommandOnDriver(conn, driver, parameter.drop);
 
       const obj = structure1[parameter.objectTypeField].find(x => x.pureName == 'obj1');
-      await driver.script(conn, obj.createSql);
+      await driver.script(conn, obj.createSql, { discardResult: true });
 
       const structure2 = await driver.analyseFull(conn);
       const parameters = structure2[parameter.objectTypeField].find(x => x.pureName == 'obj1').parameters;

integration-tests/__tests__/query.spec.js

@@ -1,11 +1,12 @@
 const engines = require('../engines');
 const { splitQuery } = require('dbgate-query-splitter');
 const { testWrapper } = require('../tools');
+const { runQueryOnDriver, runCommandOnDriver, formatQueryWithoutParams } = require('dbgate-tools');
 
 const initSql = [
-  'CREATE TABLE t1 (id int primary key)',
-  'INSERT INTO t1 (id) VALUES (1)',
-  'INSERT INTO t1 (id) VALUES (2)',
+  'CREATE TABLE ~t1 (~id int primary key)',
+  'INSERT INTO ~t1 (~id) VALUES (1)',
+  'INSERT INTO ~t1 (~id) VALUES (2)',
 ];
 
 expect.extend({
@@ -51,7 +52,7 @@ class StreamHandler {
 function executeStreamItem(driver, conn, sql) {
   return new Promise(resolve => {
     const handler = new StreamHandler(resolve);
-    driver.stream(conn, sql, handler);
+    driver.stream(conn, formatQueryWithoutParams(driver, sql), handler);
   });
 }
 
@@ -68,9 +69,11 @@ describe('Query', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Simple query - %s',
     testWrapper(async (conn, driver, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) {
+        await runCommandOnDriver(conn, driver, dmp => dmp.put(sql));
+      }
 
-      const res = await driver.query(conn, 'SELECT id FROM t1 ORDER BY id');
+      const res = await runQueryOnDriver(conn, driver, dmp => dmp.put('SELECT ~id FROM ~t1 ORDER BY ~id'));
       expect(res.columns).toEqual([
         expect.objectContaining({
           columnName: 'id',
@@ -91,8 +94,11 @@ describe('Query', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Simple stream query - %s',
     testWrapper(async (conn, driver, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
-      const results = await executeStream(driver, conn, 'SELECT id FROM t1 ORDER BY id');
+      for (const sql of initSql) {
+        await runCommandOnDriver(conn, driver, dmp => dmp.put(sql));
+      }
+
+      const results = await executeStream(driver, conn, 'SELECT ~id FROM ~t1 ORDER BY ~id');
       expect(results.length).toEqual(1);
       const res = results[0];
 
@@ -104,11 +110,14 @@ describe('Query', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'More queries - %s',
     testWrapper(async (conn, driver, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) {
+        await runCommandOnDriver(conn, driver, dmp => dmp.put(sql));
+      }
+
       const results = await executeStream(
         driver,
         conn,
-        'SELECT id FROM t1 ORDER BY id; SELECT id FROM t1 ORDER BY id DESC'
+        'SELECT ~id FROM ~t1 ORDER BY ~id; SELECT ~id FROM ~t1 ORDER BY ~id DESC'
       );
       expect(results.length).toEqual(2);
 
@@ -128,7 +137,7 @@ describe('Query', () => {
       const results = await executeStream(
         driver,
         conn,
-        'CREATE TABLE t1 (id int primary key); INSERT INTO t1 (id) VALUES (1); INSERT INTO t1 (id) VALUES (2); SELECT id FROM t1 ORDER BY id; '
+        'CREATE TABLE ~t1 (~id int primary key); INSERT INTO ~t1 (~id) VALUES (1); INSERT INTO ~t1 (~id) VALUES (2); SELECT ~id FROM ~t1 ORDER BY ~id; '
       );
       expect(results.length).toEqual(1);
 
@@ -144,7 +153,7 @@ describe('Query', () => {
       const results = await executeStream(
         driver,
         conn,
-        'CREATE TABLE t1 (id int); INSERT INTO t1 (id) VALUES (1); INSERT INTO t1 (id) VALUES (2) '
+        'CREATE TABLE ~t1 (~id int); INSERT INTO ~t1 (~id) VALUES (1); INSERT INTO ~t1 (~id) VALUES (2) '
       );
       expect(results.length).toEqual(0);
     })
@@ -153,16 +162,57 @@ describe('Query', () => {
   test.each(engines.filter(x => !x.skipDataModifications).map(engine => [engine.label, engine]))(
     'Save data query - %s',
     testWrapper(async (conn, driver, engine) => {
-      for (const sql of initSql) await driver.query(conn, sql, { discardResult: true });
+      for (const sql of initSql) {
+        await runCommandOnDriver(conn, driver, dmp => dmp.put(sql));
+      }
 
       await driver.script(
         conn,
-        'INSERT INTO t1 (id) VALUES (3);INSERT INTO t1 (id) VALUES (4);UPDATE t1 SET id=10 WHERE id=1;DELETE FROM t1 WHERE id=2;',
+        formatQueryWithoutParams(
+          driver,
+          'INSERT INTO ~t1 (~id) VALUES (3);INSERT INTO ~t1 (~id) VALUES (4);UPDATE ~t1 SET ~id=10 WHERE ~id=1;DELETE FROM ~t1 WHERE ~id=2;'
+        ),
         { discardResult: true }
       );
-      const res = await driver.query(conn, 'SELECT COUNT(*) AS cnt FROM t1');
+      const res = await runQueryOnDriver(conn, driver, dmp => dmp.put('SELECT COUNT(*) AS ~cnt FROM ~t1'));
       // console.log(res);
       expect(res.rows[0].cnt == 3).toBeTruthy();
     })
   );
+
+  test.each(engines.filter(x => !x.skipDataDuplicator).map(engine => [engine.label, engine]))(
+    'Select scope identity - %s',
+    testWrapper(async (conn, driver, engine) => {
+      await runCommandOnDriver(conn, driver, dmp =>
+        dmp.createTable({
+          pureName: 't1',
+          columns: [
+            { columnName: 'id', dataType: 'int', notNull: true, autoIncrement: true },
+            { columnName: 'val', dataType: 'varchar(50)' },
+          ],
+          primaryKey: {
+            columns: [{ columnName: 'id' }],
+          },
+        })
+      );
+
+      const structure = await driver.analyseFull(conn);
+      const table = structure.tables.find(x => x.pureName == 't1');
+
+      let res;
+      if (driver.dialect.requireStandaloneSelectForScopeIdentity) {
+        await runCommandOnDriver(conn, driver, dmp => dmp.put("INSERT INTO ~t1 (~val) VALUES ('aaa')"));
+        res = await runQueryOnDriver(conn, driver, dmp => dmp.selectScopeIdentity(table));
+      } else {
+        res = await runQueryOnDriver(conn, driver, dmp => {
+          dmp.putCmd("INSERT INTO ~t1 (~val) VALUES ('aaa')");
+          dmp.selectScopeIdentity(table);
+        });
+      }
+      const row = res.rows[0];
+      const keys = Object.keys(row);
+      expect(keys.length).toEqual(1);
+      expect(row[keys[0]] == 1).toBeTruthy();
+    })
+  );
 });

integration-tests/__tests__/schema-tests.spec.js

@@ -76,7 +76,7 @@ describe('Schema tests', () => {
 });
 
 describe('Base analyser test', () => {
-  test.each(engines.map(engine => [engine.label, engine]))(
+  test.each(engines.filter(x => !x.skipIncrementalAnalysis).map(engine => [engine.label, engine]))(
     'Structure without change - %s',
     testWrapper(async (conn, driver, engine) => {
       await baseStructure(conn, driver);

integration-tests/__tests__/table-analyse.spec.js

@@ -1,12 +1,13 @@
+const { runCommandOnDriver } = require('dbgate-tools');
 const engines = require('../engines');
 const { testWrapper } = require('../tools');
 
-const t1Sql = 'CREATE TABLE t1 (id int not null primary key, val1 varchar(50))';
-const ix1Sql = 'CREATE index ix1 ON t1(val1, id)';
+const t1Sql = 'CREATE TABLE ~t1 (~id int not null primary key, ~val1 varchar(50))';
+const ix1Sql = 'CREATE index ~ix1 ON ~t1(~val1, ~id)';
 const t2Sql = engine =>
-  `CREATE TABLE t2 (id int not null primary key, val2 varchar(50) ${engine.skipUnique ? '' : 'unique'})`;
-const t3Sql = 'CREATE TABLE t3 (id int not null primary key, valfk int, foreign key (valfk) references t2(id))';
-const t4Sql = 'CREATE TABLE t4 (id int not null primary key, valdef int not null default 12)';
+  `CREATE TABLE ~t2 (~id int not null primary key, ~val2 varchar(50) ${engine.skipUnique ? '' : 'unique'})`;
+const t3Sql = 'CREATE TABLE ~t3 (~id int not null primary key, ~valfk int, foreign key (~valfk) references ~t2(~id))';
+const t4Sql = 'CREATE TABLE ~t4 (~id int not null primary key, ~valdef int default 12 not null)';
 // const fkSql = 'ALTER TABLE t3 ADD FOREIGN KEY (valfk) REFERENCES t2(id)'
 
 const txMatch = (engine, tname, vcolname, nextcol, defaultValue) =>
@@ -15,7 +16,7 @@ const txMatch = (engine, tname, vcolname, nextcol, defaultValue) =>
     columns: [
       expect.objectContaining({
         columnName: 'id',
-        dataType: expect.stringMatching(/int.*/i),
+        dataType: expect.stringMatching(/int.*|number/i),
         ...(engine.skipNullability ? {} : { notNull: true }),
       }),
       expect.objectContaining({
@@ -59,9 +60,10 @@ describe('Table analyse', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Table structure - full analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t1Sql);
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t1Sql));
 
       const structure = await driver.analyseFull(conn);
+      console.log(JSON.stringify(structure, null, 2));
 
       expect(structure.tables.length).toEqual(1);
       expect(structure.tables[0]).toEqual(t1Match(engine));
@@ -71,13 +73,13 @@ describe('Table analyse', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Table add - incremental analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t2Sql(engine));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t2Sql(engine)));
 
       const structure1 = await driver.analyseFull(conn);
       expect(structure1.tables.length).toEqual(1);
       expect(structure1.tables[0]).toEqual(t2Match(engine));
 
-      await driver.query(conn, t1Sql);
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t1Sql));
       const structure2 = await driver.analyseIncremental(conn, structure1);
 
       expect(structure2.tables.length).toEqual(2);
@@ -89,14 +91,14 @@ describe('Table analyse', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Table remove - incremental analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t1Sql);
-      await driver.query(conn, t2Sql(engine));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t1Sql));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t2Sql(engine)));
       const structure1 = await driver.analyseFull(conn);
       expect(structure1.tables.length).toEqual(2);
       expect(structure1.tables.find(x => x.pureName == 't1')).toEqual(t1Match(engine));
       expect(structure1.tables.find(x => x.pureName == 't2')).toEqual(t2Match(engine));
 
-      await driver.query(conn, 'DROP TABLE t2');
+      await runCommandOnDriver(conn, driver, dmp => dmp.put('DROP TABLE ~t2'));
       const structure2 = await driver.analyseIncremental(conn, structure1);
 
       expect(structure2.tables.length).toEqual(1);
@@ -107,15 +109,14 @@ describe('Table analyse', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Table change - incremental analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t1Sql);
-      await driver.query(conn, t2Sql(engine));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t1Sql));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t2Sql(engine)));
       const structure1 = await driver.analyseFull(conn);
 
       if (engine.dbSnapshotBySeconds) await new Promise(resolve => setTimeout(resolve, 1100));
 
-      await driver.query(
-        conn,
-        `ALTER TABLE t2 ADD ${engine.alterTableAddColumnSyntax ? 'COLUMN' : ''} nextcol varchar(50)`
+      await runCommandOnDriver(conn, driver, dmp =>
+        dmp.put(`ALTER TABLE ~t2 ADD ${engine.alterTableAddColumnSyntax ? 'COLUMN' : ''} ~nextcol varchar(50)`)
       );
       const structure2 = await driver.analyseIncremental(conn, structure1);
 
@@ -130,8 +131,8 @@ describe('Table analyse', () => {
   test.each(engines.filter(x => !x.skipIndexes).map(engine => [engine.label, engine]))(
     'Index - full analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t1Sql);
-      await driver.query(conn, ix1Sql);
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t1Sql));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(ix1Sql));
       const structure = await driver.analyseFull(conn);
 
       const t1 = structure.tables.find(x => x.pureName == 't1');
@@ -145,7 +146,7 @@ describe('Table analyse', () => {
   test.each(engines.filter(x => !x.skipUnique).map(engine => [engine.label, engine]))(
     'Unique - full analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t2Sql(engine));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t2Sql(engine)));
       const structure = await driver.analyseFull(conn);
 
       const t2 = structure.tables.find(x => x.pureName == 't2');
@@ -159,8 +160,8 @@ describe('Table analyse', () => {
   test.each(engines.filter(x => !x.skipReferences).map(engine => [engine.label, engine]))(
     'Foreign key - full analysis - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t2Sql(engine));
-      await driver.query(conn, t3Sql);
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t2Sql(engine)));
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t3Sql));
       // await driver.query(conn, fkSql);
 
       const structure = await driver.analyseFull(conn);
@@ -179,7 +180,7 @@ describe('Table analyse', () => {
   test.each(engines.map(engine => [engine.label, engine]))(
     'Table structure - default value - %s',
     testWrapper(async (conn, driver, engine) => {
-      await driver.query(conn, t4Sql);
+      await runCommandOnDriver(conn, driver, dmp => dmp.put(t4Sql));
 
       const structure = await driver.analyseFull(conn);
 

integration-tests/__tests__/table-create.spec.js

@@ -2,7 +2,7 @@ const _ = require('lodash');
 const fp = require('lodash/fp');
 const engines = require('../engines');
 const { testWrapper } = require('../tools');
-const { extendDatabaseInfo } = require('dbgate-tools');
+const { extendDatabaseInfo, runCommandOnDriver } = require('dbgate-tools');
 
 function createExpector(value) {
   return _.cloneDeepWith(value, x => {
@@ -25,7 +25,7 @@ function checkTableStructure2(t1, t2) {
 }
 
 async function testTableCreate(conn, driver, table) {
-  await driver.query(conn, `create table t0 (id int not null primary key)`);
+  await runCommandOnDriver(conn, driver, dmp => dmp.put('create table ~t0 (~id int not null primary key)'));
 
   const dmp = driver.createDumper();
   const table1 = {

integration-tests/docker-compose.yaml

@@ -70,3 +70,9 @@ services:
 #       - cockroachdb
 #     restart: on-failure
 
+  oracle:
+    image: gvenzl/oracle-xe:21-slim
+    environment:
+      ORACLE_PASSWORD: Pwd2020Db
+    ports:
+      - 15006:1521

integration-tests/engines.js

@@ -1,9 +1,9 @@
 const views = {
   type: 'views',
-  create1: 'CREATE VIEW obj1 AS SELECT id FROM t1',
-  create2: 'CREATE VIEW obj2 AS SELECT id FROM t2',
-  drop1: 'DROP VIEW obj1',
-  drop2: 'DROP VIEW obj2',
+  create1: 'CREATE VIEW ~obj1 AS SELECT ~id FROM ~t1',
+  create2: 'CREATE VIEW ~obj2 AS SELECT ~id FROM ~t2',
+  drop1: 'DROP VIEW ~obj1',
+  drop2: 'DROP VIEW ~obj2',
 };
 const matviews = {
   type: 'matviews',
@@ -400,17 +400,54 @@ end;$$`,
     dbSnapshotBySeconds: true,
     skipChangeColumn: true,
   },
+  {
+    label: 'Oracle',
+    connection: {
+      engine: 'oracle@dbgate-plugin-oracle',
+      password: 'Pwd2020Db',
+      user: 'system',
+      server: 'oracle',
+      port: 1521,
+      serviceName: 'xe',
+    },
+    local: {
+      server: 'localhost',
+      port: 15006,
+    },
+    skipOnCI: false,
+    dbSnapshotBySeconds: true,
+    setNullDefaultInsteadOfDrop: true,
+    skipIncrementalAnalysis: true,
+    objects: [
+      views,
+      {
+        type: 'procedures',
+        create1: 'CREATE PROCEDURE ~obj1 AS BEGIN SELECT ~id FROM ~t1 END',
+        create2: 'CREATE PROCEDURE ~obj2 AS BEGIN SELECT ~id FROM ~t2 END',
+        drop1: 'DROP PROCEDURE ~obj1',
+        drop2: 'DROP PROCEDURE ~obj2',
+      },
+      {
+        type: 'functions',
+        create1: 'CREATE FUNCTION ~obj1 RETURN NUMBER IS v_count NUMBER; \n BEGIN SELECT COUNT(*) INTO v_count FROM ~t1;\n RETURN v_count;\n END ~obj1',
+        create2: 'CREATE FUNCTION ~obj2 RETURN NUMBER IS v_count NUMBER; \n BEGIN SELECT COUNT(*) INTO v_count FROM ~t2;\n RETURN v_count;\n END ~obj2',
+        drop1: 'DROP FUNCTION ~obj1',
+        drop2: 'DROP FUNCTION ~obj2',
+      },
+    ],
+  },
 ];
 
 const filterLocal = [
   // filter local testing
-  'MySQL',
+  '-MySQL',
   '-MariaDB',
   '-PostgreSQL',
   '-SQL Server',
   '-SQLite',
   '-CockroachDB',
   '-ClickHouse',
+  'Oracle',
 ];
 
 const enginesPostgre = engines.filter(x => x.label == 'PostgreSQL');

integration-tests/tools.js

@@ -1,10 +1,12 @@
 const requireEngineDriver = require('dbgate-api/src/utility/requireEngineDriver');
 const crypto = require('crypto');
 
-function randomDbName() {
+function randomDbName(dialect) {
   const generatedKey = crypto.randomBytes(6);
   const newKey = generatedKey.toString('hex');
-  return `db${newKey}`;
+  const res = `db${newKey}`;
+  if (dialect.upperCaseAllDbObjectNames) return res.toUpperCase();
+  return res;
 }
 
 function extractConnection(engine) {
@@ -32,12 +34,14 @@ async function connect(engine, database) {
     return conn;
   } else {
     const conn = await driver.connect(connection);
-    await driver.query(conn, `CREATE DATABASE ${database}`);
+    const dmp = driver.createDumper();
+    dmp.createDatabase(database);
+    await driver.query(conn, dmp.s);
     await driver.close(conn);
 
     const res = await driver.connect({
       ...connection,
-      database,
+      database: (driver.dialect.userDatabaseNamePrefix ?? '') + database,
     });
     return res;
   }
@@ -55,12 +59,14 @@ async function prepareConnection(engine, database) {
     };
   } else {
     const conn = await driver.connect(connection);
-    await driver.query(conn, `CREATE DATABASE ${database}`);
+    const dmp = driver.createDumper();
+    dmp.createDatabase(database);
+    await driver.query(conn, dmp.s);
     await driver.close(conn);
 
     return {
       ...connection,
-      database,
+      database: (driver.dialect.userDatabaseNamePrefix ?? '') + database,
       isPreparedOnly: true,
     };
   }
@@ -71,7 +77,7 @@ const testWrapper =
   async (label, ...other) => {
     const engine = other[other.length - 1];
     const driver = requireEngineDriver(engine.connection);
-    const conn = await connect(engine, randomDbName());
+    const conn = await connect(engine, randomDbName(driver.dialect));
     try {
       await body(conn, driver, ...other);
     } finally {
@@ -84,7 +90,7 @@ const testWrapperPrepareOnly =
   async (label, ...other) => {
     const engine = other[other.length - 1];
     const driver = requireEngineDriver(engine.connection);
-    const conn = await prepareConnection(engine, randomDbName());
+    const conn = await prepareConnection(engine, randomDbName(driver.dialect));
     await body(conn, driver, ...other);
   };
 

package.json

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

packages/api/src/auth/authProvider.js

@@ -230,7 +230,7 @@ class LoginsProvider extends AuthProviderBase {
         ),
       };
     }
-    
+
     return { error: 'Invalid credentials' };
   }
 
@@ -271,11 +271,10 @@ function hasEnvLogins() {
   return false;
 }
 
-function detectEnvAuthProvider() {
+function detectEnvAuthProviderCore() {
   if (process.env.AUTH_PROVIDER) {
     return process.env.AUTH_PROVIDER;
   }
-
   if (process.env.STORAGE_DATABASE) {
     return 'denyall';
   }
@@ -291,6 +290,14 @@ function detectEnvAuthProvider() {
   return 'none';
 }
 
+function detectEnvAuthProvider() {
+  const authProvider = detectEnvAuthProviderCore();
+  if (process.env.BASIC_AUTH && authProvider != 'logins' && authProvider != 'ad') {
+    throw new Error(`BASIC_AUTH is not supported with ${authProvider} auth provider`);
+  }
+  return authProvider;
+}
+
 function createEnvAuthProvider() {
   const authProvider = detectEnvAuthProvider();
   switch (authProvider) {

packages/api/src/controllers/config.js

@@ -9,6 +9,7 @@ const _ = require('lodash');
 const AsyncLock = require('async-lock');
 const jwt = require('jsonwebtoken');
 
+const processArgs = require('../utility/processArgs');
 const currentVersion = require('../currentVersion');
 const platformInfo = require('../utility/platformInfo');
 const connections = require('../controllers/connections');
@@ -83,7 +84,8 @@ module.exports = {
       isElectron: platformInfo.isElectron,
       isLicenseValid,
       isLicenseExpired: checkedLicense?.isExpired,
-      trialDaysLeft: checkedLicense?.licenseTypeObj?.isTrial && !checkedLicense?.isExpired ? checkedLicense?.daysLeft : null,
+      trialDaysLeft:
+        checkedLicense?.licenseTypeObj?.isTrial && !checkedLicense?.isExpired ? checkedLicense?.daysLeft : null,
       checkedLicense,
       configurationError,
       logoutUrl,
@@ -101,7 +103,10 @@ module.exports = {
       adminPasswordState: adminConfig?.adminPasswordState,
       storageDatabase: process.env.STORAGE_DATABASE,
       logsFilePath: getLogsFilePath(),
-      connectionsFilePath: path.join(datadir(), 'connections.jsonl'),
+      connectionsFilePath: path.join(
+        datadir(),
+        processArgs.runE2eTests ? 'connections-e2etests.jsonl' : 'connections.jsonl'
+      ),
       ...currentVersion,
     };
 

packages/api/src/controllers/connections.js

@@ -199,7 +199,9 @@ module.exports = {
     const dir = datadir();
     if (!portalConnections) {
       // @ts-ignore
-      this.datastore = new JsonLinesDatabase(path.join(dir, 'connections.jsonl'));
+      this.datastore = new JsonLinesDatabase(
+        path.join(dir, processArgs.runE2eTests ? 'connections-e2etests.jsonl' : 'connections.jsonl')
+      );
     }
     await this.checkUnsavedConnectionsLimit();
   },
@@ -220,7 +222,7 @@ module.exports = {
   },
 
   test_meta: true,
-  test(connection) {
+  test({ connection, requestDbList }) {
     const subprocess = fork(
       global['API_PACKAGE'] || process.argv[1],
       [
@@ -235,7 +237,7 @@ module.exports = {
       }
     );
     pipeForkLogs(subprocess);
-    subprocess.send(connection);
+    subprocess.send({ connection, requestDbList });
     return new Promise(resolve => {
       subprocess.on('message', resp => {
         if (handleProcessCommunication(resp, subprocess)) return;

packages/api/src/proc/connectProcess.js

@@ -16,13 +16,19 @@ Platform: ${process.platform}
 
 function start() {
   childProcessChecker();
-  process.on('message', async connection => {
+  process.on('message', async args => {
+    // @ts-ignore
+    const { connection, requestDbList } = args;
     if (handleProcessCommunication(connection)) return;
     try {
       const driver = requireEngineDriver(connection);
       const dbhan = await connectUtility(driver, connection, 'app');
       const res = await driver.getVersion(dbhan);
-      process.send({ msgtype: 'connected', ...res });
+      let databases = undefined;
+      if (requestDbList) {
+        databases = await driver.listDatabases(dbhan);
+      }
+      process.send({ msgtype: 'connected', ...res, databases });
       await driver.close(dbhan);
     } catch (e) {
       console.error(e);

packages/datalib/src/DataDuplicator.ts

@@ -66,7 +66,7 @@ class DuplicatorItemHolder {
     this.autoColumn = this.table.columns.find(x => x.autoIncrement)?.columnName;
     if (
       this.table.primaryKey?.columns?.length != 1 ||
-      this.table.primaryKey?.columns?.[0].columnName != this.autoColumn
+      this.table.primaryKey?.columns?.[0]?.columnName != this.autoColumn
     ) {
       this.autoColumn = null;
     }
@@ -140,6 +140,9 @@ class DuplicatorItemHolder {
           weakref.foreignKey.columns[0].columnName
         );
       });
+      if (this.duplicator.driver.dialect.requireFromDual) {
+        dmp.put(' ^from ^dual');
+      }
     });
     const qrow = qres.rows[0];
     return this.weakReferences.filter(x => qrow[x.columnName] == 0).map(x => x.columnName);
@@ -194,6 +197,7 @@ class DuplicatorItemHolder {
               res = await runQueryOnDriver(pool, driver, dmp => dmp.selectScopeIdentity(this.table));
             }
             // console.log('IDRES', JSON.stringify(res));
+            // console.log('*********** ENTRIES OF', res?.rows?.[0]);
             const resId = Object.entries(res?.rows?.[0])?.[0]?.[1];
             if (resId != null) {
               this.idMap[chunk[this.autoColumn]] = resId;

packages/tools/src/DatabaseAnalyser.ts

@@ -359,7 +359,7 @@ export class DatabaseAnalyser {
   }
 
   static byTableFilter(table) {
-    return x => x.pureName == table.pureName && x.schemaName == x.schemaName;
+    return x => x.pureName == table.pureName && x.schemaName == table.schemaName;
   }
 
   static extractPrimaryKeys(table, pkColumns) {

packages/tools/src/driverBase.ts

@@ -1,4 +1,5 @@
 import _compact from 'lodash/compact';
+import _isString from 'lodash/isString';
 import { SqlDumper } from './SqlDumper';
 import { splitQuery } from 'dbgate-query-splitter';
 import { dumpSqlSelect } from 'dbgate-sqltree';
@@ -26,9 +27,17 @@ const dialect = {
   defaultSchemaName: null,
 };
 
-export async function runCommandOnDriver(pool, driver: EngineDriver, cmd: (dmp: SqlDumper) => void): Promise<void> {
+export async function runCommandOnDriver(
+  pool,
+  driver: EngineDriver,
+  cmd: (dmp: SqlDumper) => void | string
+): Promise<void> {
   const dmp = driver.createDumper();
-  cmd(dmp as any);
+  if (_isString(cmd)) {
+    dmp.put(cmd);
+  } else {
+    cmd(dmp as any);
+  }
   // console.log('CMD:', dmp.s);
   await driver.query(pool, dmp.s, { discardResult: true });
 }
@@ -39,11 +48,21 @@ export async function runQueryOnDriver(
   cmd: (dmp: SqlDumper) => void
 ): Promise<QueryResult> {
   const dmp = driver.createDumper();
-  cmd(dmp as any);
+  if (_isString(cmd)) {
+    dmp.put(cmd);
+  } else {
+    cmd(dmp as any);
+  }
   // console.log('QUERY:', dmp.s);
   return await driver.query(pool, dmp.s);
 }
 
+export function formatQueryWithoutParams(driver: EngineDriver, sql: string) {
+  const dmp = driver.createDumper();
+  dmp.put(sql);
+  return dmp.s;
+}
+
 export const driverBase = {
   analyserClass: null,
   dumperClass: SqlDumper,

packages/tools/src/filterName.ts

@@ -2,9 +2,9 @@ import _compact from 'lodash/compact';
 import _isString from 'lodash/isString';
 import _startCase from 'lodash/startCase';
 
-export interface FilterNameDefinition {
-  childName: string;
-}
+// export interface FilterNameDefinition {
+//   childName: string;
+// }
 
 function camelMatch(filter: string, text: string): boolean {
   if (!text) return false;
@@ -20,7 +20,7 @@ function camelMatch(filter: string, text: string): boolean {
   }
 }
 
-export function filterName(filter: string, ...names: (string | FilterNameDefinition)[]) {
+export function filterName(filter: string, ...names: string[]) {
   if (!filter) return true;
 
   // const camelVariants = [name.replace(/[^A-Z]/g, '')]
@@ -28,22 +28,135 @@ export function filterName(filter: string, ...names: (string | FilterNameDefinit
 
   const namesCompacted = _compact(names);
 
-  // @ts-ignore
-  const namesOwn: string[] = namesCompacted.filter(x => _isString(x));
-  // @ts-ignore
-  const namesChild: string[] = namesCompacted.filter(x => x.childName).map(x => x.childName);
-
   for (const token of tokens) {
-    // const tokenUpper = token.toUpperCase();
-    if (token.startsWith('#')) {
-      // const tokenUpperSub = tokenUpper.substring(1);
-      const found = namesChild.find(name => camelMatch(token.substring(1), name));
-      if (!found) return false;
-    } else {
-      const found = namesOwn.find(name => camelMatch(token, name));
-      if (!found) return false;
-    }
+    const found = namesCompacted.find(name => camelMatch(token, name));
+    if (!found) return false;
   }
 
   return true;
 }
+
+export function filterNameCompoud(
+  filter: string,
+  namesMain: string[],
+  namesChild: string[]
+): 'main' | 'child' | 'both' | 'none' {
+  if (!filter) return 'both';
+
+  // const camelVariants = [name.replace(/[^A-Z]/g, '')]
+  const tokens = filter.split(' ').map(x => x.trim());
+
+  const namesCompactedMain = _compact(namesMain);
+  const namesCompactedChild = _compact(namesChild);
+
+  let isMainOnly = true;
+  let isChildOnly = true;
+
+  for (const token of tokens) {
+    const foundMain = namesCompactedMain.find(name => camelMatch(token, name));
+    const foundChild = namesCompactedChild.find(name => camelMatch(token, name));
+    if (!foundMain && !foundChild) return 'none';
+
+    if (!foundMain) isMainOnly = false;
+    if (!foundChild) isChildOnly = false;
+  }
+
+  if (isMainOnly && isChildOnly) return 'both';
+  if (isMainOnly) return 'main';
+  if (isChildOnly) return 'child';
+  return 'none';
+}
+
+export function tokenizeBySearchFilter(text: string, filter: string): { text: string; isMatch: boolean }[] {
+  const camelTokens = [];
+  const stdTokens = [];
+  for (const token of filter.split(' ').map(x => x.trim())) {
+    if (token.replace(/[A-Z]/g, '').length == 0) {
+      camelTokens.push(token);
+    } else {
+      stdTokens.push(token.toUpperCase());
+    }
+  }
+
+  let res = [
+    {
+      text,
+      isMatch: false,
+    },
+  ];
+
+  for (const token of camelTokens) {
+    const nextres = [];
+    for (const item of res) {
+      const indexes = [];
+      for (const char of token) {
+        if (indexes.length == 0 && char == item.text[0]?.toUpperCase()) {
+          // handle first letter of camelcase
+          indexes.push(0);
+        } else {
+          const index = item.text.indexOf(char, indexes.length > 0 ? indexes[indexes.length - 1] + 1 : 0);
+          if (index < 0) {
+            indexes.push(-1);
+          } else {
+            indexes.push(index);
+          }
+        }
+      }
+      if (indexes.some(x => x < 0)) {
+        nextres.push(item);
+      } else {
+        let lastIndex = 0;
+        for (let i = 0; i < indexes.length; i++) {
+          if (indexes[i] > lastIndex) {
+            nextres.push({ text: item.text.substring(lastIndex, indexes[i]), isMatch: false });
+          }
+          nextres.push({ text: item.text.substring(indexes[i], indexes[i] + 1), isMatch: true });
+          lastIndex = indexes[i] + 1;
+        }
+        nextres.push({ text: item.text.substring(lastIndex), isMatch: false });
+      }
+    }
+    res = nextres;
+  }
+
+  for (const token of stdTokens) {
+    const nextres = [];
+    for (const item of res) {
+      const index = item.text?.toUpperCase().indexOf(token);
+      if (index < 0) {
+        nextres.push(item);
+      } else {
+        nextres.push({ text: item.text.substring(0, index), isMatch: false });
+        nextres.push({ text: item.text.substring(index, index + token.length), isMatch: true });
+        nextres.push({ text: item.text.substring(index + token.length), isMatch: false });
+      }
+    }
+    res = nextres;
+  }
+
+  res = res.filter(x => x.text.length > 0);
+
+  if (res.length == 1 && !res[0].isMatch) {
+    return null;
+  }
+
+  return res;
+
+  // const result = [];
+  // let lastMatch = 0;
+  // for (const token of tokens) {
+  //   const index = text.indexOf(token, lastMatch);
+  //   if (index < 0) {
+  //     result.push({ token, isMatch: false });
+  //     continue;
+  //   }
+
+  //   result.push({ token: text.substring(lastMatch, index), isMatch: false });
+  //   result.push({ token: text.substring(index, index + token.length), isMatch: true });
+  //   lastMatch = index + token.length;
+  // }
+
+  // result.push({ token: text.substring(lastMatch), isMatch: false });
+
+  // return result;
+}

packages/types/dbinfo.d.ts

@@ -75,7 +75,7 @@ export interface DatabaseObjectInfo extends NamedObjectInfo {
   modifyDate?: string;
   hashCode?: string;
   objectTypeField?: string;
-  obejctComment?: string;
+  objectComment?: string;
 }
 
 export interface SqlObjectInfo extends DatabaseObjectInfo {

packages/types/dialect.d.ts

@@ -51,6 +51,8 @@ export interface SqlDialect {
 
   dropReferencesWhenDropTable?: boolean;
   requireFromDual?: boolean;
+  userDatabaseNamePrefix?: string; // c## in Oracle
+  upperCaseAllDbObjectNames?: boolean;
 
   predefinedDataTypes: string[];
 

packages/web/package.json

@@ -20,7 +20,7 @@
     "@rollup/plugin-replace": "^3.0.0",
     "@rollup/plugin-typescript": "^8.2.5",
     "@tsconfig/svelte": "^1.0.0",
-    "ace-builds": "^1.4.8",
+    "ace-builds": "^1.36.5",
     "chart.js": "^4.4.2",
     "chartjs-adapter-moment": "^1.0.0",
     "cross-env": "^7.0.3",

packages/web/src/appobj/AppFileAppObject.svelte

@@ -29,8 +29,8 @@
 
   export const extractKey = data => data.fileName;
   export const createMatcher =
-    ({ fileName }) =>
     filter =>
+    ({ fileName }) =>
       filterName(filter, fileName);
   const APP_ICONS = {
     'config.json': 'img json',

packages/web/src/appobj/AppFolderAppObject.svelte

@@ -1,6 +1,6 @@
 <script lang="ts" context="module">
   export const extractKey = data => data.name;
-  export const createMatcher = data => filter => filterName(filter, data.name);
+  export const createMatcher = filter => data => filterName(filter, data.name);
 </script>
 
 <script lang="ts">

packages/web/src/appobj/AppObjectCore.svelte

@@ -5,6 +5,7 @@
   import CheckboxField from '../forms/CheckboxField.svelte';
   import { copyTextToClipboard } from '../utility/clipboard';
   import { showSnackbarSuccess } from '../utility/snackbar';
+  import TokenizedFilteredText from '../widgets/TokenizedFilteredText.svelte';
 
   const dispatch = createEventDispatcher();
 
@@ -30,6 +31,9 @@
   export let onUnpin = null;
   export let showPinnedInsteadOfUnpin = false;
   export let indentLevel = 0;
+  export let disableBoldScroll = false;
+  export let filter = null;
+  export let disableHover = false;
 
   $: isChecked =
     checkedObjectsStore && $checkedObjectsStore.find(x => module?.extractKey(data) == module?.extractKey(x));
@@ -57,6 +61,13 @@
     }
   }
 
+  function handleMouseDown(e) {
+    if (e.button == 1) {
+      e.preventDefault();
+      e.stopPropagation();
+    }
+  }
+
   function setChecked(value) {
     if (!value && isChecked) {
       checkedObjectsStore.update(x => x.filter(y => module?.extractKey(data) != module?.extractKey(y)));
@@ -69,7 +80,7 @@
   // $: console.log(title, indentLevel);
   let domDiv;
 
-  $: if (isBold && domDiv) {
+  $: if (isBold && domDiv && !disableBoldScroll) {
     domDiv.scrollIntoView({ block: 'nearest', inline: 'nearest' });
   }
 
@@ -82,9 +93,12 @@
   class="main"
   class:isBold
   class:isChoosed
+  class:disableHover
   draggable={true}
   on:click={handleClick}
   on:mouseup={handleMouseUp}
+  on:mousedown={handleMouseDown}
+  on:mousedown
   on:dblclick
   use:contextMenu={disableContextMenu ? null : menu}
   on:dragstart={e => {
@@ -121,7 +135,7 @@
   {#if colorMark}
     <FontIcon style={`color:${colorMark}`} icon="icon square" />
   {/if}
-  {title}
+  <TokenizedFilteredText text={title} {filter} />
   {#if statusIconBefore}
     <span class="status">
       <FontIcon icon={statusIconBefore} />
@@ -143,7 +157,7 @@
   {/if}
   {#if extInfo}
     <span class="ext-info">
-      {extInfo}
+      <TokenizedFilteredText text={extInfo} {filter} />
     </span>
   {/if}
   {#if onPin}
@@ -186,7 +200,7 @@
     white-space: nowrap;
     font-weight: normal;
   }
-  .main:hover {
+  .main:hover:not(.disableHover) {
     background-color: var(--theme-bg-hover);
   }
   .isBold {

packages/web/src/appobj/AppObjectGroup.svelte

@@ -19,6 +19,7 @@
   export let onDropOnGroup = undefined;
   export let groupContextMenu = null;
   export let collapsedGroupNames;
+  export let filter = undefined;
 
   $: isExpanded = !$collapsedGroupNames.includes(group);
 
@@ -86,6 +87,9 @@
         on:objectClick
         {disableContextMenu}
         {passProps}
+        isExpandedBySearch={filter && item.isChildMatched}
+        {filter}
+        isMainMatched={item.isMainMatched}
       />
     {/each}
   </div>

packages/web/src/appobj/AppObjectList.svelte

@@ -1,11 +1,13 @@
 <script>
-  import _, { sortBy } from 'lodash';
-  import { asyncFilter } from '../utility/common';
+  import _ from 'lodash';
   import AppObjectGroup from './AppObjectGroup.svelte';
   import { plusExpandIcon } from '../icons/expandIcons';
 
   import AppObjectListItem from './AppObjectListItem.svelte';
   import { writable } from 'svelte/store';
+  import Link from '../elements/Link.svelte';
+  import { focusedConnectionOrDatabase } from '../stores';
+  import { tick } from 'svelte';
 
   export let list;
   export let module;
@@ -16,7 +18,7 @@
   export let expandIconFunc = undefined;
   export let checkedObjectsStore = null;
   export let disableContextMenu = false;
-  export let passProps;
+  export let passProps = {};
   export let getIsExpanded = null;
   export let setIsExpanded = null;
   export let sortGroups = false;
@@ -26,25 +28,47 @@
   export let groupFunc = undefined;
   export let onDropOnGroup = undefined;
   export let emptyGroupNames = [];
+  export let isExpandedBySearch = false;
 
   export let collapsedGroupNames = writable([]);
-  export let onChangeFilteredList;
+  export let onChangeFilteredList = undefined;
 
-  $: filtered = !groupFunc
-    ? list.filter(data => {
-        const matcher = module.createMatcher && module.createMatcher(data);
-        if (matcher && !matcher(filter)) return false;
-        return true;
-      })
-    : null;
+  let expandLimited = false;
 
-  $: childrenMatched = !groupFunc
-    ? list.filter(data => {
-        const matcher = module.createChildMatcher && module.createChildMatcher(data);
-        if (matcher && !matcher(filter)) return false;
-        return true;
-      })
-    : null;
+  $: matcher = module.createMatcher && module.createMatcher(filter, passProps?.searchSettings);
+
+  $: dataLabeled = _.compact(
+    (list || []).map(data => {
+      const matchResult = matcher ? matcher(data) : true;
+
+      let isMatched = true;
+      let isMainMatched = true;
+      let isChildMatched = true;
+
+      if (matchResult == false) {
+        isMatched = false;
+        isChildMatched = false;
+        isMainMatched = false;
+      } else if (matchResult == 'child') {
+        isMainMatched = false;
+      } else if (matchResult == 'main') {
+        isChildMatched = false;
+      } else if (matchResult == 'none') {
+        isMatched = false;
+        isChildMatched = false;
+        isMainMatched = false;
+      } else if (matchResult == 'both') {
+        isChildMatched = !module.disableShowChildrenWithParentMatch;
+      }
+
+      const group = groupFunc ? groupFunc(data) : undefined;
+      return { group, data, isMatched, isChildMatched, isMainMatched };
+    })
+  );
+
+  $: filtered = dataLabeled.filter(x => x.isMatched).map(x => x.data);
+  $: childrenMatched = dataLabeled.filter(x => x.isChildMatched).map(x => x.data);
+  $: mainMatched = dataLabeled.filter(x => x.isMainMatched).map(x => x.data);
 
   // let filtered = [];
 
@@ -60,17 +84,6 @@
   //   }
   // }
 
-  $: listGrouped = groupFunc
-    ? _.compact(
-        (list || []).map(data => {
-          const matcher = module.createMatcher && module.createMatcher(data);
-          const isMatched = matcher && !matcher(filter) ? false : true;
-          const group = groupFunc(data);
-          return { group, data, isMatched };
-        })
-      )
-    : null;
-
   function extendGroups(base, emptyList) {
     const res = {
       ...base,
@@ -82,7 +95,24 @@
     return res;
   }
 
-  $: groups = groupFunc ? extendGroups(_.groupBy(listGrouped, 'group'), emptyGroupNames) : null;
+  function setExpandLimited() {
+    expandLimited = true;
+  }
+
+  $: groups = groupFunc ? extendGroups(_.groupBy(dataLabeled, 'group'), emptyGroupNames) : null;
+
+  $: listLimited = isExpandedBySearch && !expandLimited ? filtered.slice(0, filter.trim().length < 3 ? 1 : 3) : list;
+  $: isListLimited = isExpandedBySearch && listLimited.length < filtered.length;
+  $: listMissingItems = isListLimited ? filtered.slice(listLimited.length) : [];
+
+  $: if (
+    $focusedConnectionOrDatabase &&
+    listMissingItems.some(
+      x => $focusedConnectionOrDatabase.conid == x?.connection?._id && $focusedConnectionOrDatabase.database == x?.name
+    )
+  ) {
+    tick().then(setExpandLimited);
+  }
 </script>
 
 {#if groupFunc}
@@ -108,7 +138,7 @@
     />
   {/each}
 {:else}
-  {#each list as data}
+  {#each listLimited as data}
     <AppObjectListItem
       isHidden={!filtered.includes(data)}
       {module}
@@ -121,10 +151,20 @@
       {checkedObjectsStore}
       {disableContextMenu}
       {filter}
-      isExpandedBySearch={childrenMatched.includes(data)}
+      isExpandedBySearch={filter && childrenMatched.includes(data)}
+      isMainMatched={filter && mainMatched.includes(data)}
       {passProps}
       {getIsExpanded}
       {setIsExpanded}
     />
   {/each}
+  {#if isListLimited}
+    <div class="ml-2">
+      <Link
+        onClick={() => {
+          expandLimited = true;
+        }}>Show next {filtered.length - listLimited.length}</Link
+      >
+    </div>
+  {/if}
 {/if}

packages/web/src/appobj/AppObjectListItem.svelte

@@ -24,6 +24,7 @@
   export let passProps;
   export let getIsExpanded = null;
   export let setIsExpanded = null;
+  export let isMainMatched = false;
 
   let isExpandedCore = false;
 
@@ -57,16 +58,21 @@
     {module}
     {disableContextMenu}
     {passProps}
+    {filter}
   />
 
   {#if (isExpanded || isExpandedBySearch) && subItemsComponent}
     <div class="subitems">
       <svelte:component
-        this={subItemsComponent(data)}
+        this={subItemsComponent(data, {
+          isExpandedBySearch,
+        })}
         {data}
         {filter}
         {passProps}
-        isExpandedOnlyBySearch={isExpandedBySearch && !isExpanded}
+        {isExpandedBySearch}
+        {isExpanded}
+        {isMainMatched}
       />
     </div>
   {/if}

packages/web/src/appobj/ArchiveFileAppObject.svelte

@@ -42,8 +42,8 @@
 
   export const extractKey = data => data.fileName;
   export const createMatcher =
-    ({ fileName }) =>
     filter =>
+    ({ fileName }) =>
       filterName(filter, fileName);
   const ARCHIVE_ICONS = {
     'table.yaml': 'img table',
@@ -70,7 +70,7 @@
   import { getExtensions } from '../stores';
 
   import createQuickExportMenu from '../utility/createQuickExportMenu';
-  import { exportQuickExportFile,  } from '../utility/exportFileTools';
+  import { exportQuickExportFile } from '../utility/exportFileTools';
   import openNewTab from '../utility/openNewTab';
   import AppObjectCore from './AppObjectCore.svelte';
   import InputTextModal from '../modals/InputTextModal.svelte';

packages/web/src/appobj/ArchiveFolderAppObject.svelte

@@ -1,6 +1,6 @@
 <script lang="ts" context="module">
   export const extractKey = data => data.name;
-  export const createMatcher = data => filter => filterName(filter, data.name);
+  export const createMatcher = filter => data => filterName(filter, data.name);
 </script>
 
 <script lang="ts">

packages/web/src/appobj/ColumnAppObject.svelte

@@ -1,5 +1,17 @@
 <script lang="ts" context="module">
   export const extractKey = ({ columnName }) => columnName;
+
+  export const createMatcher =
+    (filter, cfg = DEFAULT_OBJECT_SEARCH_SETTINGS) =>
+    data => {
+      const filterArgs = [];
+      if (cfg.columnName) filterArgs.push(data.columnName);
+      if (cfg.columnComment) filterArgs.push(data.columnComment);
+      if (cfg.columnDataType) filterArgs.push(data.dataType);
+
+      const res = filterName(filter, ...filterArgs);
+      return res;
+    };
 </script>
 
 <script lang="ts">
@@ -9,6 +21,8 @@
   import { renameDatabaseObjectDialog, alterDatabaseDialog } from '../utility/alterDatabaseTools';
 
   import AppObjectCore from './AppObjectCore.svelte';
+  import { DEFAULT_OBJECT_SEARCH_SETTINGS } from '../stores';
+  import { filterName } from 'dbgate-tools';
 
   export let data;
 
@@ -35,7 +49,21 @@
     ];
   }
 
-  $: extInfo = data.foreignKey ? `${data.dataType} -> ${data.foreignKey.refTableName}` : data.dataType;
+  function getExtInfo(data) {
+    const res = [];
+    if (data.foreignKey) {
+      res.push(`${data.dataType} -> ${data.foreignKey.refTableName}`);
+    } else {
+      res.push(data.dataType);
+    }
+    if (data.columnComment) {
+      res.push(data.columnComment);
+    }
+    if (res.length > 0) return res.join(', ');
+    return null;
+  }
+
+  $: extInfo = getExtInfo(data);
 </script>
 
 <AppObjectCore
@@ -45,5 +73,5 @@
   {extInfo}
   icon={getColumnIcon(data, true)}
   menu={createMenu}
-  disableHover
+  \
 />

packages/web/src/appobj/ConnectionAppObject.svelte

@@ -1,16 +1,24 @@
 <script context="module">
   export const extractKey = data => data._id;
-  export const createMatcher = props => filter => {
-    const { _id, displayName, server } = props;
-    const databases = getLocalStorage(`database_list_${_id}`) || [];
-    return filterName(filter, displayName, server, ...databases.map(x => x.name));
-  };
-  export const createChildMatcher = props => filter => {
-    if (!filter) return false;
-    const { _id } = props;
-    const databases = getLocalStorage(`database_list_${_id}`) || [];
-    return filterName(filter, ...databases.map(x => x.name));
-  };
+  export const createMatcher =
+    (filter, cfg = DEFAULT_CONNECTION_SEARCH_SETTINGS) =>
+    props => {
+      const { _id, displayName, server, user, engine } = props;
+      const databases = getLocalStorage(`database_list_${_id}`) || [];
+      const match = (engine || '').match(/^([^@]*)@/);
+      const engineDisplay = match ? match[1] : engine;
+
+      return filterNameCompoud(
+        filter,
+        [
+          cfg.displayName ? displayName : null,
+          cfg.server ? server : null,
+          cfg.user ? user : null,
+          cfg.engine ? engineDisplay : null,
+        ],
+        cfg.database ? databases.map(x => x.name) : []
+      );
+    };
   export function openConnection(connection, disableExpand = false) {
     if (connection.singleDatabase) {
       if (getOpenedSingleDatabaseConnections().includes(connection._id)) {
@@ -42,6 +50,10 @@
         expandedConnections.update(x => _.uniq([...x, connection._id]));
       }
 
+      if (connection.defaultDatabase) {
+        switchCurrentDatabase({ connection, name: connection.defaultDatabase });
+      }
+
       // if (!config.runAsPortal && getCurrentSettings()['defaultAction.connectionClick'] != 'connect') {
       //   expandedConnections.update(x => _.uniq([...x, connection._id]));
       // }
@@ -94,6 +106,7 @@
   import AppObjectCore from './AppObjectCore.svelte';
   import {
     currentDatabase,
+    DEFAULT_CONNECTION_SEARCH_SETTINGS,
     expandedConnections,
     extensions,
     focusedConnectionOrDatabase,
@@ -106,7 +119,7 @@
     openedConnections,
     openedSingleDatabaseConnections,
   } from '../stores';
-  import { filterName } from 'dbgate-tools';
+  import { filterName, filterNameCompoud } from 'dbgate-tools';
   import { showModal } from '../modals/modalTools';
   import ConfirmModal from '../modals/ConfirmModal.svelte';
   import InputTextModal from '../modals/InputTextModal.svelte';
@@ -123,6 +136,7 @@
   import { getConnectionLabel } from 'dbgate-tools';
   import hasPermission from '../utility/hasPermission';
   import { switchCurrentDatabase } from '../utility/common';
+  import { getConnectionClickActionSetting } from '../settings/settingsTools';
 
   export let data;
   export let passProps;
@@ -177,24 +191,40 @@
   };
 
   const handleClick = async e => {
+    // focusedConnectionOrDatabase.set({
+    //   conid: data?._id,
+    //   connection: data,
+    //   database: data.singleDatabase ? data.defaultDatabase : null,
+    // });
+
+    const config = getCurrentConfig();
+
+    const connectionClickAction = getConnectionClickActionSetting();
+    if (connectionClickAction == 'openDetails') {
+      if (config.runAsPortal == false && !config.storageDatabase) {
+        openNewTab({
+          title: getConnectionLabel(data),
+          icon: 'img connection',
+          tabComponent: 'ConnectionTab',
+          tabPreviewMode: true,
+          props: {
+            conid: data._id,
+          },
+        });
+      }
+    }
+    if (connectionClickAction == 'connect') {
+      await tick();
+      handleConnect();
+    }
+  };
+
+  const handleMouseDown = () => {
     focusedConnectionOrDatabase.set({
       conid: data?._id,
       connection: data,
       database: data.singleDatabase ? data.defaultDatabase : null,
     });
-
-    const config = getCurrentConfig();
-    if (config.runAsPortal == false && !config.storageDatabase) {
-      openNewTab({
-        title: getConnectionLabel(data),
-        icon: 'img connection',
-        tabComponent: 'ConnectionTab',
-        tabPreviewMode: true,
-        props: {
-          conid: data._id,
-        },
-      });
-    }
   };
 
   const handleSqlRestore = () => {
@@ -373,6 +403,7 @@
   colorMark={passProps?.connectionColorFactory && passProps?.connectionColorFactory({ conid: data._id })}
   menu={getContextMenu}
   on:click={handleClick}
+  on:mousedown={handleMouseDown}
   on:dblclick
   on:expand
   on:dblclick={handleDoubleClick}
@@ -385,4 +416,5 @@
     (data.singleDatabase
       ? $focusedConnectionOrDatabase?.database == data.defaultDatabase
       : !$focusedConnectionOrDatabase?.database)}
+  disableBoldScroll={!!$focusedConnectionOrDatabase}
 />

packages/web/src/appobj/DatabaseAppObject.svelte

@@ -3,6 +3,11 @@
 
   export const extractKey = props => props.name;
 
+  export const createMatcher = filter => props => {
+    const { name, displayName, server } = props;
+    return filterName(filter, name, displayName, server);
+  };
+
   export function disconnectDatabaseConnection(conid, database, showConfirmation = true) {
     const closeCondition = x =>
       x.props?.conid == conid &&
@@ -351,11 +356,13 @@ await dbgateApi.dropAllDbObjects(${JSON.stringify(
           text: `New ${driver?.collectionSingularLabel ?? 'collection/container'}`,
         },
       hasPermission(`dbops/query`) &&
-        driver?.databaseEngineTypes?.includes('sql') && isProApp() && { onClick: handleQueryDesigner, text: 'Design query' },
-      driver?.databaseEngineTypes?.includes('sql') && isProApp() && {
-        onClick: handleNewPerspective,
-        text: 'Design perspective query',
-      },
+        driver?.databaseEngineTypes?.includes('sql') &&
+        isProApp() && { onClick: handleQueryDesigner, text: 'Design query' },
+      driver?.databaseEngineTypes?.includes('sql') &&
+        isProApp() && {
+          onClick: handleNewPerspective,
+          text: 'Design perspective query',
+        },
       connection.useSeparateSchemas && { onClick: handleRefreshSchemas, text: 'Refresh schemas' },
 
       { divider: true },
@@ -463,7 +470,13 @@ await dbgateApi.dropAllDbObjects(${JSON.stringify(
   import openNewTab from '../utility/openNewTab';
   import AppObjectCore from './AppObjectCore.svelte';
   import { showSnackbarError, showSnackbarSuccess } from '../utility/snackbar';
-  import { extractDbNameFromComposite, extractPackageName, findEngineDriver, getConnectionLabel } from 'dbgate-tools';
+  import {
+    extractDbNameFromComposite,
+    extractPackageName,
+    filterName,
+    findEngineDriver,
+    getConnectionLabel,
+  } from 'dbgate-tools';
   import InputTextModal from '../modals/InputTextModal.svelte';
   import { getDatabaseInfo, useUsedApps } from '../utility/metadataLoaders';
   import { openJsonDocument } from '../tabs/JsonTab.svelte';
@@ -486,6 +499,7 @@ await dbgateApi.dropAllDbObjects(${JSON.stringify(
   import ChooseArchiveFolderModal from '../modals/ChooseArchiveFolderModal.svelte';
   import { extractShellConnection } from '../impexp/createImpExpScript';
   import { getNumberIcon } from '../icons/FontIcon.svelte';
+  import { getDatabaseClickActionSetting } from '../settings/settingsTools';
 
   export let data;
   export let passProps;
@@ -522,6 +536,11 @@ await dbgateApi.dropAllDbObjects(${JSON.stringify(
   }}
   on:click={() => {
     // switchCurrentDatabase(data);
+    if (getDatabaseClickActionSetting() == 'switch') {
+      switchCurrentDatabase(data);
+    }
+  }}
+  on:mousedown={() => {
     $focusedConnectionOrDatabase = { conid: data.connection?._id, database: data.name, connection: data.connection };
   }}
   on:dragstart
@@ -553,4 +572,5 @@ await dbgateApi.dropAllDbObjects(${JSON.stringify(
     : null}
   isChoosed={data.connection?._id == $focusedConnectionOrDatabase?.conid &&
     data.name == $focusedConnectionOrDatabase?.database}
+  disableBoldScroll={!!$focusedConnectionOrDatabase}
 />

packages/web/src/appobj/DatabaseObjectAppObject.svelte

@@ -3,14 +3,34 @@
 
   export const extractKey = ({ schemaName, pureName }) => (schemaName ? `${schemaName}.${pureName}` : pureName);
   export const createMatcher =
-    ({ schemaName, pureName, columns }) =>
-    filter =>
-      filterName(
-        filter,
-        pureName,
-        schemaName,
-        ...(columns?.map(({ columnName }) => ({ childName: columnName })) || [])
-      );
+    (filter, cfg = DEFAULT_OBJECT_SEARCH_SETTINGS) =>
+    ({ schemaName, pureName, objectComment, tableEngine, columns, objectTypeField, createSql }) => {
+      const mainArgs = [];
+      const childArgs = [];
+      if (cfg.schemaName) mainArgs.push(schemaName);
+      if (objectTypeField == 'tables') {
+        if (cfg.tableName) mainArgs.push(pureName);
+        if (cfg.tableComment) mainArgs.push(objectComment);
+        if (cfg.tableEngine) mainArgs.push(tableEngine);
+
+        for (const column of columns || []) {
+          if (cfg.columnName) childArgs.push(column.columnName);
+          if (cfg.columnComment) childArgs.push(column.columnComment);
+          if (cfg.columnDataType) childArgs.push(column.dataType);
+        }
+      } else if (objectTypeField == 'collections') {
+        if (cfg.collectionName) mainArgs.push(pureName);
+      } else {
+        if (cfg.sqlObjectName) mainArgs.push(pureName);
+        if (cfg.sqlObjectText) childArgs.push(createSql);
+      }
+
+      const res = filterNameCompoud(filter, mainArgs, childArgs);
+      return res;
+    };
+
+  export const disableShowChildrenWithParentMatch = true;
+
   export const createTitle = ({ schemaName, pureName }) => (schemaName ? `${schemaName}.${pureName}` : pureName);
 
   export const databaseObjectIcons = {
@@ -867,7 +887,35 @@
     return createDatabaseObjectMenu(data);
   }
 
-  export function handleObjectClick(data, { forceNewTab = false, tabPreviewMode = false, focusTab = false }) {
+  export function handleObjectClick(data, clickAction) {
+    //   on:click={() => handleObjectClick(data, { tabPreviewMode: true })}
+    // on:middleclick={() => handleObjectClick(data, { forceNewTab: true })}
+    // on:dblclick={() => handleObjectClick(data, { tabPreviewMode: false, focusTab: true })}
+    const openDetailOnArrows = getOpenDetailOnArrowsSettings();
+
+    let forceNewTab = false;
+    let tabPreviewMode = false;
+    let focusTab = false;
+
+    switch (clickAction) {
+      case 'leftClick':
+        tabPreviewMode = true;
+        break;
+      case 'middleClick':
+        forceNewTab = true;
+        break;
+      case 'dblClick':
+        focusTab = true;
+        break;
+      case 'keyEnter':
+        focusTab = true;
+        break;
+      case 'keyArrow':
+        if (!openDetailOnArrows) return;
+        tabPreviewMode = true;
+        break;
+    }
+
     return handleDatabaseObjectClick(data, { forceNewTab, tabPreviewMode, focusTab });
   }
 </script>
@@ -877,9 +925,11 @@
   import AppObjectCore from './AppObjectCore.svelte';
   import {
     currentDatabase,
+    DEFAULT_OBJECT_SEARCH_SETTINGS,
     extensions,
     getActiveTab,
     getCurrentSettings,
+    getDatabaseObjectAppObjectSearchSettings,
     getExtensions,
     getLastUsedDefaultActions,
     lastUsedDefaultActions,
@@ -892,6 +942,7 @@
   import {
     extractDbNameFromComposite,
     filterName,
+    filterNameCompoud,
     generateDbPairingId,
     getAlterDatabaseScript,
     getConnectionLabel,
@@ -916,23 +967,21 @@
   import { openImportExportTab } from '../utility/importExportTools';
   import { defaultDatabaseObjectAppObjectActions, matchDatabaseObjectAppObject } from './appObjectTools';
   import { getSupportedScriptTemplates } from '../utility/applyScriptTemplate';
-  import { getBoolSettingsValue } from '../settings/settingsTools';
+  import { getBoolSettingsValue, getOpenDetailOnArrowsSettings } from '../settings/settingsTools';
   import { isProApp } from '../utility/proTools';
 
   export let data;
   export let passProps;
 
-  function handleClick({ forceNewTab = false, tabPreviewMode = false, focusTab = false } = {}) {
-    $selectedDatabaseObjectAppObject = _.pick(data, ['conid', 'database', 'objectTypeField', 'pureName', 'schemaName']);
-    handleDatabaseObjectClick(data, { forceNewTab, tabPreviewMode, focusTab });
-  }
-
   function createMenu() {
     return createDatabaseObjectMenu(data, passProps?.connection);
   }
 
   function getExtInfo(data) {
     const res = [];
+    if (data.objectComment) {
+      res.push(data.objectComment);
+    }
     if (data.tableRowCount != null) {
       res.push(`${formatRowCount(data.tableRowCount)} rows`);
     }
@@ -958,12 +1007,15 @@
   onUnpin={isPinned ? () => pinnedTables.update(list => list.filter(x => !testEqual(x, data))) : null}
   extInfo={getExtInfo(data)}
   isChoosed={matchDatabaseObjectAppObject($selectedDatabaseObjectAppObject, data)}
-  on:click={() => handleClick({ tabPreviewMode: true })}
-  on:middleclick={() => handleClick({ forceNewTab: true })}
-  on:dblclick={() => handleClick({ tabPreviewMode: false, focusTab: true })}
+  on:click={() => handleObjectClick(data, 'leftClick')}
+  on:middleclick={() => handleObjectClick(data, 'middleClick')}
+  on:dblclick={() => handleObjectClick(data, 'dblClick')}
   on:expand
   on:dragstart
   on:dragenter
   on:dragend
   on:drop
+  on:mousedown={() => {
+    $selectedDatabaseObjectAppObject = _.pick(data, ['conid', 'database', 'objectTypeField', 'pureName', 'schemaName']);
+  }}
 />

packages/web/src/appobj/MacroAppObject.svelte

@@ -1,6 +1,9 @@
 <script lang="ts" context="module">
   export const extractKey = data => data.name;
-  export const createMatcher = ({ name, title }) => filter => filterName(filter, name, title);
+  export const createMatcher =
+    filter =>
+    ({ name, title }) =>
+      filterName(filter, name, title);
 </script>
 
 <script lang="ts">

packages/web/src/appobj/ProcedureLineAppObject.svelte

@@ -0,0 +1,23 @@
+<script lang="ts" context="module">
+  export const extractKey = ({ columnName }) => columnName;
+
+  export const createMatcher =
+    (filter, cfg = DEFAULT_OBJECT_SEARCH_SETTINGS) =>
+    data => {
+      const filterArgs = [];
+      if (cfg.sqlObjectText) filterArgs.push(data.lineData);
+
+      const res = filterName(filter, ...filterArgs);
+      return res;
+    };
+</script>
+
+<script lang="ts">
+  import AppObjectCore from './AppObjectCore.svelte';
+  import { filterName } from 'dbgate-tools';
+  import { DEFAULT_OBJECT_SEARCH_SETTINGS } from '../stores';
+
+  export let data;
+</script>
+
+<AppObjectCore {...$$restProps} {data} icon="icon text" title={data.lineData?.substring(0, 100)} disableHover />

packages/web/src/appobj/SavedFileAppObject.svelte

@@ -104,8 +104,8 @@
 
   export const extractKey = data => data.file;
   export const createMatcher =
-    ({ file }) =>
     filter =>
+    ({ file }) =>
       filterName(filter, file);
 </script>
 

packages/web/src/appobj/SubDatabaseList.svelte

@@ -11,17 +11,22 @@
   export let data;
   export let passProps;
 
-  export let isExpandedOnlyBySearch;
+  export let isExpandedBySearch;
+  export let isExpanded;
+  export let isMainMatched;
+
+  $: isExpandedOnlyBySearch = isExpandedBySearch && !isExpanded;
 
   $: databases = useDatabaseList({ conid: isExpandedOnlyBySearch ? null : data._id });
   $: dbList = isExpandedOnlyBySearch ? getLocalStorage(`database_list_${data._id}`) || [] : $databases || [];
+
+  // .filter(x => filterName(filter, x.name, data.displayName, data.server))
 </script>
 
 <AppObjectList
-  list={_.sortBy(
-    dbList.filter(x => filterName(filter, x.name, data.displayName, data.server)),
-    x => x.sortOrder ?? x.name
-  ).map(db => ({ ...db, connection: data }))}
+  list={_.sortBy(dbList, x => x.sortOrder ?? x.name).map(db => ({ ...db, connection: data }))}
   module={databaseAppObject}
   {passProps}
+  filter={isMainMatched ? '' : filter}
+  {isExpandedBySearch}
 />

packages/web/src/appobj/SubProcedureLineList.svelte

@@ -0,0 +1,21 @@
+<script lang="ts" context="module">
+  export const extractKey = ({ lineData }) => lineData;
+</script>
+
+<script lang="ts">
+  import AppObjectList from './AppObjectList.svelte';
+  import * as procedureLineAppObject from './ProcedureLineAppObject.svelte';
+
+  export let data;
+  export let filter;
+  export let isExpandedBySearch;
+</script>
+
+<AppObjectList
+  list={(data.createSql?.split('\n') || []).map(lineData => ({
+    lineData,
+  }))}
+  module={procedureLineAppObject}
+  {filter}
+  {isExpandedBySearch}
+/>

packages/web/src/appobj/SubTableColumnList.svelte

@@ -5,6 +5,8 @@
   import * as columnAppObject from './ColumnAppObject.svelte';
 
   export let data;
+  export let filter;
+  export let isExpandedBySearch;
 </script>
 
 <AppObjectList
@@ -14,4 +16,6 @@
     foreignKey: findForeignKeyForColumn(data, col),
   }))}
   module={columnAppObject}
+  {filter}
+  {isExpandedBySearch}
 />

packages/web/src/buttons/DropDownButton.svelte

@@ -7,20 +7,32 @@
 
   export let icon = 'icon chevron-down';
   export let menu;
+  export let asyncMenu = undefined;
   export let narrow = false;
+  export let square = true;
   export let disabled = false;
-  let domButton;
 
-  function handleClick() {
+  let domButton;
+  let isLoading = false;
+
+  async function handleClick() {
     if (disabled) return;
-    
+
+    let items = menu;
+
+    if (asyncMenu) {
+      isLoading = true;
+      items = await asyncMenu();
+      isLoading = false;
+    }
+
     const rect = domButton.getBoundingClientRect();
     const left = rect.left;
     const top = rect.bottom;
-    currentDropDownMenu.set({ left, top, items: menu });
+    currentDropDownMenu.set({ left, top, items });
   }
 </script>
 
-<InlineButton square {narrow} on:click={handleClick} bind:this={domButton} {disabled}>
-  <FontIcon {icon} />
+<InlineButton {square} {narrow} on:click={handleClick} bind:this={domButton} {disabled}>
+  <FontIcon icon={isLoading ? 'icon loading' : icon} />
 </InlineButton>

packages/web/src/datagrid/ColumnManager.svelte

@@ -235,6 +235,7 @@
       {columnIndex}
       {allowChangeChangeSetStructure}
       isSelected={selectedColumns.includes(column.uniqueName) || currentColumnUniqueName == column.uniqueName}
+      {filter}
       on:click={() => {
         if (domFocusField) domFocusField.focus();
         selectedColumns = [column.uniqueName];

packages/web/src/datagrid/ColumnManagerRow.svelte

@@ -15,6 +15,7 @@
   export let conid;
   export let database;
   export let isDynamicStructure;
+  export let filter = undefined;
 
   export let tableInfo;
   export let setTableInfo;
@@ -83,7 +84,7 @@
         }}
       />
     {/if}
-    <ColumnLabel {...column} showDataType {conid} {database} />
+    <ColumnLabel {...column} showDataType {conid} {database} {filter} />
   </div>
 
   {#if allowChangeChangeSetStructure && !isDynamicStructure}

packages/web/src/datagrid/DataGridCell.svelte

@@ -54,6 +54,8 @@
 
   // don't parse JSON for explicit data types
   $: jsonParsedValue = !editorTypes?.explicitDataType && isJsonLikeLongString(value) ? safeJsonParse(value) : null;
+
+  $: showHint = allowHintField && rowData && _.some(col.hintColumnNames, hintColumnName => rowData[hintColumnName]);
 </script>
 
 <td
@@ -68,11 +70,12 @@
   class:isDeleted
   class:isAutofillSelected
   class:isFocusedColumn
+  class:alignRight={_.isNumber(value) && !showHint}
   {style}
 >
   <CellValue {rowData} {value} {jsonParsedValue} {editorTypes} />
 
-  {#if allowHintField && rowData && _.some(col.hintColumnNames, hintColumnName => rowData[hintColumnName])}
+  {#if showHint}
     <span class="hint"
       >{col.hintColumnNames.map(hintColumnName => rowData[hintColumnName]).join(col.hintColumnDelimiter || ' ')}</span
     >
@@ -199,4 +202,9 @@
     overflow: visible;
     cursor: crosshair;
   }
+
+  .alignRight {
+    color: var(--theme-icon-green);
+    text-align: var(--data-grid-numbers-align);
+  }
 </style>

packages/web/src/datagrid/DataGridCore.svelte

@@ -416,7 +416,6 @@
   import GenerateSqlFromDataModal from '../modals/GenerateSqlFromDataModal.svelte';
   import { showModal } from '../modals/modalTools';
   import StatusBarTabItem from '../widgets/StatusBarTabItem.svelte';
-  import { findCommand } from '../commands/runCommand';
   import { openJsonDocument } from '../tabs/JsonTab.svelte';
   import EditJsonModal from '../modals/EditJsonModal.svelte';
   import { apiCall } from '../utility/api';
@@ -430,8 +429,7 @@
   import { openJsonLinesData } from '../utility/openJsonLinesData';
   import contextMenuActivator from '../utility/contextMenuActivator';
   import InputTextModal from '../modals/InputTextModal.svelte';
-  import hasPermission from '../utility/hasPermission';
-
+  
   export let onLoadNextData = undefined;
   export let grider = undefined;
   export let display: GridDisplay = undefined;
@@ -483,6 +481,7 @@
   let domFocusField;
   let domHorizontalScroll;
   let domVerticalScroll;
+  let domContainer;
 
   let currentCell = topLeftCell;
   let selectedCells = [topLeftCell];
@@ -492,7 +491,7 @@
   let autofillSelectedCells = emptyCellArray;
   const domFilterControlsRef = createRef({});
 
-  let isGridFocused=false;
+  let isGridFocused = false;
 
   const tabid = getContext('tabid');
 
@@ -596,6 +595,7 @@
     selectedCells = [cell];
     await tick();
     scrollIntoView(cell);
+    domFocusField?.focus();
   }
 
   export async function cloneRows() {
@@ -1748,6 +1748,11 @@
         // { text: 'Copy as JSON', onClick: () => copyToClipboardCore('json') },
       ],
     },
+    {
+      text: 'Paste',
+      onClick: () => domFocusField.paste(),
+      keyText: 'CtrlOrCommand+V',
+    },
     { placeTag: 'switch' },
     { divider: true },
     { placeTag: 'save' },
@@ -1838,11 +1843,11 @@
     {/if}
   </div>
 {:else if isDynamicStructure && isLoadedAll && grider?.rowCount == 0}
-  <div>
+  <div class="ml-2">
     <ErrorInfo
       alignTop
       message={grider.editable
-        ? 'No rows loaded, check filter or add new documents. You could copy documents from ohter collections/tables with Copy advanved/Copy as JSON command.'
+        ? 'No rows loaded, check filter or add new documents. You could copy documents from other collections/tables with Copy advanved/Copy as JSON command.'
         : 'No rows loaded'}
     />
     {#if display.filterCount > 0}
@@ -1867,9 +1872,15 @@
     class:data-grid-focused={isGridFocused}
     bind:clientWidth={containerWidth}
     bind:clientHeight={containerHeight}
+    bind:this={domContainer}
     use:contextMenu={buildMenu}
     use:contextMenuActivator={activator}
     on:wheel={handleGridWheel}
+    on:click={e => {
+      if (e.target == domContainer) {
+        domFocusField?.focus();
+      }
+    }}
   >
     <input
       type="text"
@@ -2014,6 +2025,24 @@
         {/each}
       </tbody>
     </table>
+
+    {#if !isDynamicStructure && isLoadedAll && grider?.rowCount == 0}
+      <div class="no-rows-info ml-2">
+        <div class="mb-3">
+          <ErrorInfo alignTop message="No rows loaded" icon="img info" />
+        </div>
+        {#if display.filterCount > 0}
+          <FormStyledButton value="Reset filter" on:click={() => display.clearFilters()} />
+        {/if}
+        {#if grider.editable}
+          <FormStyledButton value="Add row" on:click={insertNewRow} />
+        {/if}
+        {#if onOpenQuery}
+          <FormStyledButton value="Open Query" on:click={onOpenQuery} />
+        {/if}
+      </div>
+    {/if}
+
     <HorizontalScrollBar
       minimum={0}
       maximum={maxScrollColumn}
@@ -2092,4 +2121,8 @@
     right: 40px;
     bottom: 20px;
   }
+
+  .no-rows-info {
+    margin-top: 60px;
+  }
 </style>

packages/web/src/datagrid/ReferenceManager.svelte

@@ -9,7 +9,7 @@
   import SearchBoxWrapper from '../elements/SearchBoxWrapper.svelte';
   import SearchInput from '../elements/SearchInput.svelte';
   import FontIcon from '../icons/FontIcon.svelte';
-  import ColumnManagerRow from './ColumnManagerRow.svelte';
+  import TokenizedFilteredText from '../widgets/TokenizedFilteredText.svelte';
 
   export let managerSize;
   export let display: GridDisplay;
@@ -21,7 +21,6 @@
 
   $: foreignKeys = display?.baseTable?.foreignKeys || [];
   $: dependencies = display?.baseTable?.dependencies || [];
-
 </script>
 
 <SearchBoxWrapper>
@@ -46,7 +45,7 @@
       >
         <FontIcon icon="img link" />
         <div class="ml-1 nowrap">
-          {fk.refTableName}
+          <TokenizedFilteredText text={fk.refTableName} {filter} />
           ({fk.columns.map(x => x.columnName).join(', ')})
         </div>
       </div>
@@ -70,7 +69,7 @@
       >
         <FontIcon icon="img reference" />
         <div class="ml-1 nowrap">
-          {fk.pureName}
+          <TokenizedFilteredText text={fk.pureName} {filter} />
           ({fk.columns.map(x => x.columnName).join(', ')})
         </div>
       </div>
@@ -89,5 +88,4 @@
   .link:hover {
     text-decoration: underline;
   }
-
 </style>

packages/web/src/elements/ColumnLabel.svelte

@@ -11,6 +11,7 @@
   import { openDatabaseObjectDetail } from '../appobj/DatabaseObjectAppObject.svelte';
 
   import FontIcon from '../icons/FontIcon.svelte';
+  import TokenizedFilteredText from '../widgets/TokenizedFilteredText.svelte';
   import Link from './Link.svelte';
 
   export let notNull = false;
@@ -25,6 +26,7 @@
   export let conid = undefined;
   export let database = undefined;
   export let iconOverride = undefined;
+  export let filter = undefined;
 
   $: icon = iconOverride || getColumnIcon($$props, forceIcon);
 </script>
@@ -33,7 +35,7 @@
   {#if icon}
     <FontIcon {icon} />
   {/if}
-  {headerText || columnName}
+  <TokenizedFilteredText text={headerText || columnName} {filter} />
   {#if extInfo}
     <span class="extinfo">{extInfo}</span>
   {/if}

packages/web/src/forms/FormDropDownTextFieldRaw.svelte

@@ -9,6 +9,7 @@
   export let disabled = false;
   export let defaultValue;
   export let menu;
+  export let asyncMenu;
 
   const { values, setFieldValue } = getFormContext();
 
@@ -26,5 +27,5 @@
     value={$values[name] ?? defaultValue}
     on:input={e => setFieldValue(name, e.target['value'])}
   />
-  <DropDownButton {menu} {disabled} />
+  <DropDownButton {menu} {asyncMenu} {disabled} />
 </div>

packages/web/src/icons/FontIcon.svelte

@@ -143,6 +143,7 @@
     'icon parent-filter': 'mdi mdi-home-alert',
     'icon parent-filter-outline': 'mdi mdi-home-alert-outline',
     'icon download': 'mdi mdi-download',
+    'icon text': 'mdi mdi-text',
 
     'icon run': 'mdi mdi-play',
     'icon chevron-down': 'mdi mdi-chevron-down',
@@ -286,6 +287,7 @@
     'img import': 'mdi mdi-database-import color-icon-green',
     'img export': 'mdi mdi-database-export color-icon-green',
     'img transform': 'mdi mdi-rotate-orbit color-icon-blue',
+    'img tip': 'mdi mdi-lightbulb-on color-icon-yellow',
   };
 </script>
 

packages/web/src/modals/DropDownMenu.svelte

@@ -55,6 +55,8 @@
   let submenuItem;
   let submenuOffset;
 
+  let switchIndex = 0;
+
   const dispatch = createEventDispatcher();
   let closeHandlers = [];
 
@@ -80,6 +82,14 @@
       submenuOffset = hoverOffset;
       return;
     }
+    if (item.switchStore && item.switchValue) {
+      item.switchStore.update(x => ({
+        ...x,
+        [item.switchValue]: !x[item.switchValue],
+      }));
+      switchIndex++;
+      return;
+    }
     dispatchClose();
     if (onCloseParent) onCloseParent();
     if (item.onClick) item.onClick();
@@ -131,7 +141,18 @@
         }}
       >
         <a on:click={e => handleClick(e, item)} class:disabled={item.disabled} class:bold={item.isBold}>
-          {item.text || item.label}
+          <span>
+            {#if item.switchValue && item.switchStoreGetter}
+              {#key switchIndex}
+                {#if item.switchStoreGetter()[item.switchValue]}
+                  <FontIcon icon="icon check" padRight />
+                {:else}
+                  <FontIcon icon="icon invisible-box" padRight />
+                {/if}
+              {/key}
+            {/if}
+            {item.text || item.label}
+          </span>
           {#if item.keyText}
             <span class="keyText">{formatKeyText(item.keyText)}</span>
           {/if}
@@ -179,6 +200,7 @@
     white-space: nowrap;
     overflow-y: auto;
     max-height: calc(100% - 20px);
+    user-select: none;
   }
 
   .keyText {

packages/web/src/settings/ConnectionDriverFields.svelte

@@ -16,8 +16,12 @@
   import FormColorField from '../forms/FormColorField.svelte';
   import FontIcon from '../icons/FontIcon.svelte';
   import FormDropDownTextField from '../forms/FormDropDownTextField.svelte';
+  import { getConnectionLabel } from 'dbgate-tools';
 
-  const { values } = getFormContext();
+  export let getDatabaseList;
+  export let currentConnection;
+
+  const { values, setFieldValue } = getFormContext();
   const electron = getElectron();
 
   $: authType = $values.authType;
@@ -69,6 +73,14 @@
     'me-central-1',
     'sa-east-1',
   ];
+
+  async function createDatabasesMenu() {
+    const databases = await getDatabaseList();
+    return databases.map(db => ({
+      text: db.name,
+      onClick: () => setFieldValue('defaultDatabase', db.name),
+    }));
+  }
 </script>
 
 <FormSelectField
@@ -377,11 +389,13 @@
 {/if}
 
 {#if driver?.showConnectionField('defaultDatabase', $values, showConnectionFieldArgs)}
-  <FormTextField
+  <FormDropDownTextField
     label="Default database"
     name="defaultDatabase"
     disabled={isConnected}
     data-testid="ConnectionDriverFields_defaultDatabase"
+    asyncMenu={createDatabasesMenu}
+    placeholder="(not selected - optional)"
   />
 {/if}
 
@@ -412,7 +426,8 @@
         templateProps={{ noMargin: true }}
         disabled={isConnected}
         data-testid="ConnectionDriverFields_displayName"
-        />
+        placeholder={getConnectionLabel(currentConnection)}
+      />
     </div>
     <div class="col-6 mr-1">
       <FormColorField

packages/web/src/settings/SettingsModal.svelte

@@ -100,7 +100,7 @@ ORDER BY
           { label: 'Connection', slot: 2 },
           { label: 'Themes', slot: 3 },
           { label: 'Default Actions', slot: 4 },
-          { label: 'Confirmations', slot: 5 },
+          { label: 'Behaviour', slot: 5 },
           { label: 'Other', slot: 6 },
         ]}
       >
@@ -138,6 +138,8 @@ ORDER BY
             defaultValue="10"
           />
 
+          <FormCheckboxField name="dataGrid.alignNumbersRight" label="Align numbers to right" defaultValue={false} />
+
           <div class="heading">SQL editor</div>
 
           <div class="flex">
@@ -278,6 +280,30 @@ ORDER BY
         </svelte:fragment>
         <svelte:fragment slot="4">
           <div class="heading">Default actions</div>
+
+          <FormSelectField
+            label="Connection click"
+            name="defaultAction.connectionClick"
+            isNative
+            defaultValue="connect"
+            options={[
+              { value: 'openDetails', label: 'Edit / open details' },
+              { value: 'connect', label: 'Connect' },
+              { value: 'none', label: 'Do nothing' },
+            ]}
+          />
+
+          <FormSelectField
+            label="Database click"
+            name="defaultAction.databaseClick"
+            isNative
+            defaultValue="switch"
+            options={[
+              { value: 'switch', label: 'Switch database' },
+              { value: 'none', label: 'Do nothing' },
+            ]}
+          />
+
           <FormCheckboxField name="defaultAction.useLastUsedAction" label="Use last used action" defaultValue={true} />
 
           <FormDefaultActionField
@@ -307,6 +333,24 @@ ORDER BY
           />
         </svelte:fragment>
         <svelte:fragment slot="5">
+          <div class="heading">Behaviour</div>
+
+          <FormCheckboxField name="behaviour.useTabPreviewMode" label="Use tab preview mode" defaultValue={true} />
+
+          <div class="tip">
+            <FontIcon icon="img tip" /> When you single-click or select a file in the "Tables, Views, Functions" view, it
+            is shown in a preview mode and reuses an existing tab (preview tab). This is useful if you are quickly browsing
+            tables and don't want every visited table to have its own tab. When you start editing the table or use double-click
+            to open the table from the "Tables" view, a new tab is dedicated to that table.
+          </div>
+
+          <FormCheckboxField
+            name="behaviour.openDetailOnArrows"
+            label="Open detail on keyboard navigation"
+            defaultValue={true}
+            disabled={values['behaviour.useTabPreviewMode'] === false}
+          />
+
           <div class="heading">Confirmations</div>
 
           <FormCheckboxField name="skipConfirm.tableDataSave" label="Skip confirmation when saving table data (SQL)" />
@@ -381,6 +425,11 @@ ORDER BY
     margin-top: var(--dim-large-form-margin);
   }
 
+  .tip {
+    margin-left: var(--dim-large-form-margin);
+    margin-top: var(--dim-large-form-margin);
+  }
+
   .themes {
     overflow-x: scroll;
     display: flex;

packages/web/src/settings/settingsTools.ts

@@ -28,3 +28,18 @@ export function getStringSettingsValue(name, defaultValue) {
   if (res == null) return defaultValue;
   return res;
 }
+
+export function getConnectionClickActionSetting(): 'connect' | 'openDetails' | 'none' {
+  return getStringSettingsValue('defaultAction.connectionClick', 'connect');
+}
+
+export function getDatabaseClickActionSetting(): 'switch' | 'none' {
+  return getStringSettingsValue('defaultAction.databaseClick', 'switch');
+}
+
+export function getOpenDetailOnArrowsSettings(): boolean {
+  return (
+    getBoolSettingsValue('behaviour.useTabPreviewMode', true) &&
+    getBoolSettingsValue('behaviour.openDetailOnArrows', true)
+  );
+}

packages/web/src/stores.ts

@@ -161,6 +161,38 @@ export const lastUsedDefaultActions = writableWithStorage({}, 'lastUsedDefaultAc
 export const selectedDatabaseObjectAppObject = writable(null);
 export const focusedConnectionOrDatabase = writable<{ conid: string; database?: string; connection: any }>(null);
 
+export const DEFAULT_OBJECT_SEARCH_SETTINGS = {
+  collectionName: true,
+  schemaName: false,
+  tableName: true,
+  viewName: true,
+  columnName: true,
+  columnDataType: false,
+  tableComment: true,
+  columnComment: true,
+  sqlObjectName: true,
+  sqlObjectText: true,
+  tableEngine: false,
+};
+
+export const DEFAULT_CONNECTION_SEARCH_SETTINGS = {
+  displayName: true,
+  server: true,
+  user: false,
+  engine: false,
+  database: true,
+};
+
+export const databaseObjectAppObjectSearchSettings = writableWithStorage(
+  DEFAULT_OBJECT_SEARCH_SETTINGS,
+  'databaseObjectAppObjectSearchSettings'
+);
+
+export const connectionAppObjectSearchSettings = writableWithStorage(
+  DEFAULT_CONNECTION_SEARCH_SETTINGS,
+  'connectionAppObjectSearchSettings'
+);
+
 export const currentThemeDefinition = derived([currentTheme, extensions], ([$currentTheme, $extensions]) =>
   $extensions.themes.find(x => x.themeClassName == $currentTheme)
 );
@@ -186,6 +218,9 @@ export const visibleTitleBar = derived(useSettings(), $settings => {
   // console.log('nativeMenuOnStartup', nativeMenuOnStartup);
   return !$settings['app.fullscreen'] && !nativeMenuOnStartup;
 });
+export const alignDataGridNumbersToRight = derived(useSettings(), $settings => {
+  return !!$settings?.['dataGrid.alignNumbersRight'];
+});
 
 export const visibleHamburgerMenuWidget = derived(useSettings(), $settings => {
   const electron = getElectron();
@@ -199,6 +234,7 @@ subscribeCssVariable(visibleSelectedWidget, x => (x ? 1 : 0), '--dim-visible-lef
 subscribeCssVariable(leftPanelWidth, x => `${x}px`, '--dim-left-panel-width');
 subscribeCssVariable(visibleTitleBar, x => (x ? 1 : 0), '--dim-visible-titlebar');
 subscribeCssVariable(lockedDatabaseMode, x => (x ? 0 : 1), '--dim-visible-tabs-databases');
+subscribeCssVariable(alignDataGridNumbersToRight, x => (x ? 'right' : 'left'), '--data-grid-numbers-align');
 
 let activeTabIdValue = null;
 activeTabId.subscribe(value => {
@@ -358,4 +394,20 @@ let lastUsedDefaultActionsValue = {};
 lastUsedDefaultActions.subscribe(value => {
   lastUsedDefaultActionsValue = value;
 });
-export const getLastUsedDefaultActions = () => lastUsedDefaultActionsValue;
+export const getLastUsedDefaultActions = () => lastUsedDefaultActionsValue;
+
+let databaseObjectAppObjectSearchSettingsValue: typeof DEFAULT_OBJECT_SEARCH_SETTINGS = {
+  ...DEFAULT_OBJECT_SEARCH_SETTINGS,
+};
+databaseObjectAppObjectSearchSettings.subscribe(value => {
+  databaseObjectAppObjectSearchSettingsValue = value;
+});
+export const getDatabaseObjectAppObjectSearchSettings = () => databaseObjectAppObjectSearchSettingsValue;
+
+let connectionAppObjectSearchSettingsValue: typeof DEFAULT_CONNECTION_SEARCH_SETTINGS = {
+  ...DEFAULT_CONNECTION_SEARCH_SETTINGS,
+};
+connectionAppObjectSearchSettings.subscribe(value => {
+  connectionAppObjectSearchSettingsValue = value;
+});
+export const getConnectionAppObjectSearchSettings = () => connectionAppObjectSearchSettingsValue;

packages/web/src/tabs/ConnectionTab.svelte

@@ -63,15 +63,16 @@
 
   const testIdRef = createRef(0);
 
-  async function handleTest(e) {
+  async function handleTest(e, requestDbList = false) {
     isTesting = true;
     testIdRef.update(x => x + 1);
     const testid = testIdRef.get();
-    const resp = await apiCall('connections/test', e.detail);
+    const resp = await apiCall('connections/test', { connection: e.detail, requestDbList });
     if (testIdRef.get() != testid) return;
 
     isTesting = false;
     sqlConnectResult = resp;
+    return resp;
   }
 
   function handleCancelTest() {
@@ -80,6 +81,10 @@
   }
 
   function getCurrentConnection() {
+    return getCurrentConnectionCore($values, driver);
+  }
+
+  function getCurrentConnectionCore(values, driver) {
     const allProps = [
       'databaseFile',
       'useDatabaseUrl',
@@ -94,15 +99,15 @@
       'socketPath',
       'serviceName',
     ];
-    const visibleProps = allProps.filter(x => driver?.showConnectionField(x, $values, { config: $config }));
+    const visibleProps = allProps.filter(x => driver?.showConnectionField(x, values, { config: $config }));
     const omitProps = _.difference(allProps, visibleProps);
-    if (!$values.defaultDatabase) omitProps.push('singleDatabase');
+    if (!values.defaultDatabase) omitProps.push('singleDatabase');
 
-    let connection: Dictionary<string | boolean> = _.omit($values, omitProps);
+    let connection: Dictionary<string | boolean> = _.omit(values, omitProps);
     if (driver?.beforeConnectionSave) connection = driver?.beforeConnectionSave(connection);
 
-    if (driver?.showConnectionTab('sshTunnel', $values)) {
-      if (!$values.useSshTunnel) {
+    if (driver?.showConnectionTab('sshTunnel', values)) {
+      if (!values.useSshTunnel) {
         connection = _.omitBy(connection, (v, k) => k.startsWith('ssh'));
       }
     } else {
@@ -110,8 +115,8 @@
       connection = _.omitBy(connection, (v, k) => k.startsWith('ssh'));
     }
 
-    if (driver?.showConnectionTab('ssl', $values)) {
-      if (!$values.useSsl) {
+    if (driver?.showConnectionTab('ssl', values)) {
+      if (!values.useSsl) {
         connection = _.omitBy(connection, (v, k) => k.startsWith('ssl'));
       }
     } else {
@@ -122,6 +127,8 @@
     return connection;
   }
 
+  $: currentConnection = getCurrentConnectionCore($values, driver);
+
   async function handleSave() {
     let connection = getCurrentConnection();
     connection = {
@@ -190,6 +197,14 @@
   $: isConnected = $openedConnections.includes($values._id) || $openedSingleDatabaseConnections.includes($values._id);
 
   // $: console.log('CONN VALUES', $values);
+
+  async function getDatabaseList() {
+    const resp = await handleTest({ detail: getCurrentConnection() }, true);
+    if (resp && resp.msgtype == 'connected') {
+      return resp.databases;
+    }
+    return [];
+  }
 </script>
 
 <FormProviderCore template={FormFieldTemplateLarge} {values}>
@@ -202,6 +217,7 @@
         {
           label: 'General',
           component: ConnectionDriverFields,
+          props: { getDatabaseList, currentConnection },
         },
         driver?.showConnectionTab('sshTunnel', $values) && {
           label: 'SSH Tunnel',
@@ -223,20 +239,24 @@
         <div class="buttons">
           {#if onlyTestButton}
             {#if isTesting}
-              <FormButton value="Cancel test" on:click={handleCancelTest} data-testid="ConnectionTab_buttonCancelTest" />
+              <FormButton
+                value="Cancel test"
+                on:click={handleCancelTest}
+                data-testid="ConnectionTab_buttonCancelTest"
+              />
             {:else}
               <FormButton value="Test connection" on:click={handleTest} data-testid="ConnectionTab_buttonDisconnect" />
             {/if}
           {:else if isConnected}
-            <FormButton value="Disconnect" on:click={handleDisconnect} data-testid='ConnectionTab_buttonDisconnect' />
+            <FormButton value="Disconnect" on:click={handleDisconnect} data-testid="ConnectionTab_buttonDisconnect" />
           {:else}
-            <FormButton value="Connect" on:click={handleConnect} data-testid='ConnectionTab_buttonConnect' />
+            <FormButton value="Connect" on:click={handleConnect} data-testid="ConnectionTab_buttonConnect" />
             {#if isTesting}
               <FormButton value="Cancel test" on:click={handleCancelTest} />
             {:else}
-              <FormButton value="Test" on:click={handleTest} data-testid='ConnectionTab_buttonTest' />
+              <FormButton value="Test" on:click={handleTest} data-testid="ConnectionTab_buttonTest" />
             {/if}
-            <FormButton value="Save" on:click={handleSave} data-testid='ConnectionTab_buttonSave' />
+            <FormButton value="Save" on:click={handleSave} data-testid="ConnectionTab_buttonSave" />
           {/if}
         </div>
         <div class="test-result">

packages/web/src/utility/changeCurrentDbByTab.ts

@@ -12,6 +12,7 @@ import { shouldShowTab } from '../tabpanel/TabsPanel.svelte';
 import { callWhenAppLoaded, getAppLoaded } from './appLoadManager';
 import { getConnectionInfo } from './metadataLoaders';
 import { switchCurrentDatabase } from './common';
+import { extractDbNameFromComposite, isCompositeDbName } from 'dbgate-tools';
 
 // let lastCurrentTab = null;
 
@@ -82,7 +83,10 @@ currentDatabase.subscribe(currentDb => {
   if (currentDb) {
     focusedConnectionOrDatabase.set({
       conid: currentDb.connection?._id,
-      database: currentDb.name,
+      database:
+        currentDb.connection?.useSeparateSchemas && isCompositeDbName(currentDb.name)
+          ? extractDbNameFromComposite(currentDb.name)
+          : currentDb.name,
       connection: currentDb.connection,
     });
   } else {

packages/web/src/utility/openNewTab.ts

@@ -2,10 +2,12 @@ import _ from 'lodash';
 import uuidv1 from 'uuid/v1';
 import { getActiveTab, getOpenedTabs, openedTabs } from '../stores';
 import tabs from '../tabs';
-import { setSelectedTabFunc } from './common';
+import { setSelectedTabFunc, switchCurrentDatabase } from './common';
 import localforage from 'localforage';
 import stableStringify from 'json-stable-stringify';
 import { saveAllPendingEditorData } from '../query/useEditorData';
+import { getConnectionInfo } from './metadataLoaders';
+import { getBoolSettingsValue } from '../settings/settingsTools';
 
 function findFreeNumber(numbers: number[]) {
   if (numbers.length == 0) return 1;
@@ -21,7 +23,23 @@ export default async function openNewTab(newTab, initialData: any = undefined, o
 
   let existing = null;
 
-  const { savedFile, savedFolder, savedFilePath } = newTab.props || {};
+  if (!getBoolSettingsValue('behaviour.useTabPreviewMode', true) && newTab.tabPreviewMode) {
+    newTab = {
+      ...newTab,
+      tabPreviewMode: false,
+    };
+  }
+
+  const { savedFile, savedFolder, savedFilePath, conid, database } = newTab.props || {};
+
+  if (conid && database) {
+    const connection = await getConnectionInfo({ conid });
+    await switchCurrentDatabase({
+      connection,
+      name: database,
+    });
+  }
+
   const { tabPreviewMode } = newTab;
   if (savedFile || savedFilePath) {
     existing = oldTabs.find(

packages/web/src/widgets/AppObjectListHandler.svelte

@@ -16,6 +16,7 @@
 
   let isListFocused = false;
   let domDiv = null;
+  let lastInputMethod = null;
   export let hideContent = false;
 
   function handleKeyDown(ev) {
@@ -25,6 +26,11 @@
       const selected = getSelectedObject();
       const index = _.findIndex(listInstance, x => selectedObjectMatcher(x, selected));
 
+      if (index < 0) {
+        focusFirst();
+        return;
+      }
+
       if (index == 0 && diff < 0) {
         onFocusFilterBox?.();
         return;
@@ -41,7 +47,7 @@
 
         if (listInstance[newIndex]) {
           selectedObjectStore.set(listInstance[newIndex]);
-          handleObjectClick?.(listInstance[newIndex], { tabPreviewMode: true });
+          handleObjectClick?.(listInstance[newIndex], 'keyArrow');
         }
 
         if (newIndex == 0) {
@@ -58,7 +64,7 @@
       ev.preventDefault();
     }
     if (ev.keyCode == keycodes.enter) {
-      handleObjectClick?.(getSelectedObject(), { tabPreviewMode: false, focusTab: true });
+      handleObjectClick?.(getSelectedObject(), 'keyEnter');
       ev.preventDefault();
     }
     if (ev.keyCode == keycodes.pageDown) {
@@ -72,14 +78,14 @@
     if (ev.keyCode == keycodes.home) {
       if (listInstance[0]) {
         selectedObjectStore.set(listInstance[0]);
-        handleObjectClick?.(listInstance[0], { tabPreviewMode: true });
+        handleObjectClick?.(listInstance[0], 'keyArrow');
         onScrollTop?.();
       }
     }
     if (ev.keyCode == keycodes.end) {
       if (listInstance[listInstance.length - 1]) {
         selectedObjectStore.set(listInstance[listInstance.length - 1]);
-        handleObjectClick?.(listInstance[listInstance.length - 1], { tabPreviewMode: true });
+        handleObjectClick?.(listInstance[listInstance.length - 1], 'keyArrow');
       }
     }
     if (ev.keyCode == keycodes.numPadAdd) {
@@ -110,16 +116,18 @@
     domDiv?.focus();
     if (listInstance[0]) {
       selectedObjectStore.set(listInstance[0]);
-      handleObjectClick?.(listInstance[0], { tabPreviewMode: true });
+      handleObjectClick?.(listInstance[0], 'keyArrow');
       onScrollTop?.();
     }
   }
 
-  async function handleFocus() {
+  async function handleFocus(e) {
     isListFocused = true;
-    // await tick();
-    await sleep(100);
-    // console.log('ON FOCUS AFTER SLEEP');
+
+    if (lastInputMethod == 'mouse') {
+      return;
+    }
+
     const listInstance = _.isFunction(list) ? list() : list;
     const selected = getSelectedObject();
     const index = _.findIndex(listInstance, x => selectedObjectMatcher(x, selected));
@@ -129,7 +137,7 @@
         const index2 = _.findIndex(listInstance, x => selectedObjectMatcher(x, focused));
         if (index2 >= 0) {
           selectedObjectStore.set(focused);
-          handleObjectClick?.(focused, { tabPreviewMode: true });
+          handleObjectClick?.(focused, 'keyArrow');
           return;
         }
       }
@@ -138,6 +146,15 @@
   }
 </script>
 
+<svelte:window
+  on:keydown={() => {
+    lastInputMethod = 'keyboard';
+  }}
+  on:mousedown={() => {
+    lastInputMethod = 'mouse';
+  }}
+/>
+
 <div
   tabindex="0"
   on:keydown={handleKeyDown}

packages/web/src/widgets/ConnectionList.svelte

@@ -20,6 +20,8 @@
     getFocusedConnectionOrDatabase,
     currentDatabase,
     getCurrentConfig,
+    connectionAppObjectSearchSettings,
+    getConnectionAppObjectSearchSettings,
   } from '../stores';
   import runCommand from '../commands/runCommand';
   import { filterName, getConnectionLabel } from 'dbgate-tools';
@@ -38,6 +40,13 @@
   import { switchCurrentDatabase } from '../utility/common';
   import openNewTab from '../utility/openNewTab';
   import { openConnection } from '../appobj/ConnectionAppObject.svelte';
+  import {
+    getBoolSettingsValue,
+    getConnectionClickActionSetting,
+    getDatabaseClickActionSetting,
+    getOpenDetailOnArrowsSettings,
+  } from '../settings/settingsTools';
+  import DropDownButton from '../buttons/DropDownButton.svelte';
 
   const connections = useConnectionList();
   const serverStatus = useServerStatus();
@@ -181,6 +190,21 @@
       { text: 'Delete', onClick: handleDelete },
     ];
   }
+
+  function createSearchMenu() {
+    const res = [];
+    res.push({ label: 'Search by:', isBold: true, disabled: true });
+    res.push({ label: 'Display name', switchValue: 'displayName' });
+    res.push({ label: 'Server', switchValue: 'server' });
+    res.push({ label: 'User', switchValue: 'user' });
+    res.push({ label: 'Database engine', switchValue: 'engine' });
+    res.push({ label: 'Database name', switchValue: 'database' });
+    return res.map(item => ({
+      ...item,
+      switchStore: connectionAppObjectSearchSettings,
+      switchStoreGetter: getConnectionAppObjectSearchSettings,
+    }));
+  }
 </script>
 
 <SearchBoxWrapper>
@@ -193,8 +217,13 @@
     }}
   />
   <CloseSearchButton bind:filter />
+  <DropDownButton icon="icon filter" menu={createSearchMenu} square={!!filter} narrow={false} />
   {#if $commandsCustomized['new.connection']?.enabled}
-    <InlineButton on:click={() => runCommand('new.connection')} title="Add new connection" data-testid="ConnectionList_buttonNewConnection">
+    <InlineButton
+      on:click={() => runCommand('new.connection')}
+      title="Add new connection"
+      data-testid="ConnectionList_buttonNewConnection"
+    >
       <FontIcon icon="icon plus-thick" />
     </InlineButton>
     <InlineButton on:click={() => runCommand('new.connection.folder')} title="Add new connection folder">
@@ -234,28 +263,35 @@
     onFocusFilterBox={text => {
       domFilter?.focus(text);
     }}
-    handleObjectClick={(data, options) => {
+    handleObjectClick={(data, clickAction) => {
+      const connectionClickAction = getConnectionClickActionSetting();
+      const databaseClickAction = getDatabaseClickActionSetting();
+      const openDetailOnArrows = getOpenDetailOnArrowsSettings();
+
       if (data.database) {
-        if (options.focusTab) {
-          if ($openedSingleDatabaseConnections.includes(data.conid)) {
-            switchCurrentDatabase({ connection: data.connection, name: data.database });
-          } else {
-            switchCurrentDatabase({ connection: data.connection, name: data.database });
-          }
-          // console.log('FOCUSING DB', passProps);
-          // passProps?.onFocusSqlObjectList?.();
+        if (databaseClickAction == 'switch' && clickAction == 'leftClick') {
+          switchCurrentDatabase({ connection: data.connection, name: data.database });
+        }
+
+        if (clickAction == 'keyEnter' || clickAction == 'dblClick') {
+          switchCurrentDatabase({ connection: data.connection, name: data.database });
         }
       } else {
-        if (options.focusTab) {
+        if (clickAction == 'keyEnter' || clickAction == 'dblClick') {
           openConnection(data.connection);
         } else {
           const config = getCurrentConfig();
-          if (config.runAsPortal == false && !config.storageDatabase) {
+          if (
+            config.runAsPortal == false &&
+            !config.storageDatabase &&
+            connectionClickAction == 'openDetails' &&
+            (clickAction == 'leftClick' || (clickAction == 'keyArrow' && openDetailOnArrows))
+          ) {
             openNewTab({
               title: getConnectionLabel(data.connection),
               icon: 'img connection',
               tabComponent: 'ConnectionTab',
-              tabPreviewMode: options.tabPreviewMode,
+              tabPreviewMode: true,
               props: {
                 conid: data.conid,
               },
@@ -278,7 +314,12 @@
       expandOnClick
       isExpandable={data => $openedConnections.includes(data._id) && !data.singleDatabase}
       {filter}
-      passProps={{ ...passProps, connectionColorFactory: $connectionColorFactory, showPinnedInsteadOfUnpin: true }}
+      passProps={{
+        ...passProps,
+        connectionColorFactory: $connectionColorFactory,
+        showPinnedInsteadOfUnpin: true,
+        searchSettings: $connectionAppObjectSearchSettings,
+      }}
       getIsExpanded={data => $expandedConnections.includes(data._id) && !data.singleDatabase}
       setIsExpanded={(data, value) => {
         expandedConnections.update(old => (value ? [...old, data._id] : old.filter(x => x != data._id)));
@@ -302,7 +343,11 @@
       expandOnClick
       isExpandable={data => $openedConnections.includes(data._id) && !data.singleDatabase}
       {filter}
-      passProps={{ connectionColorFactory: $connectionColorFactory, showPinnedInsteadOfUnpin: true }}
+      passProps={{
+        connectionColorFactory: $connectionColorFactory,
+        showPinnedInsteadOfUnpin: true,
+        searchSettings: $connectionAppObjectSearchSettings,
+      }}
       getIsExpanded={data => $expandedConnections.includes(data._id) && !data.singleDatabase}
       setIsExpanded={(data, value) => {
         expandedConnections.update(old => (value ? [...old, data._id] : old.filter(x => x != data._id)));

packages/web/src/widgets/SingleConnectionDatabaseList.svelte

@@ -13,6 +13,7 @@
   import { filterName } from 'dbgate-tools';
   import { currentDatabase, focusedConnectionOrDatabase, getFocusedConnectionOrDatabase } from '../stores';
   import { switchCurrentDatabase } from '../utility/common';
+  import { getConnectionClickActionSetting, getDatabaseClickActionSetting } from '../settings/settingsTools';
 
   export let connection;
 
@@ -81,9 +82,16 @@
     onFocusFilterBox={text => {
       domFilter?.focus(text);
     }}
-    handleObjectClick={(data, options) => {
+    handleObjectClick={(data, clickAction) => {
+      const connectionClickAction = getConnectionClickActionSetting();
+      const databaseClickAction = getDatabaseClickActionSetting();
+
       if (data.database) {
-        if (options.focusTab) {
+        if (databaseClickAction == 'switch' && clickAction == 'leftClick') {
+          switchCurrentDatabase({ connection: data.connection, name: data.database });
+        }
+
+        if (clickAction == 'keyEnter' || clickAction == 'dblClick') {
           switchCurrentDatabase({ connection: data.connection, name: data.database });
         }
       }

packages/web/src/widgets/SqlObjectList.svelte

@@ -27,7 +27,7 @@
   import AppObjectList from '../appobj/AppObjectList.svelte';
   import _ from 'lodash';
   import * as databaseObjectAppObject from '../appobj/DatabaseObjectAppObject.svelte';
-  import SubColumnParamList from '../appobj/SubColumnParamList.svelte';
+  import SubTableColumnList from '../appobj/SubTableColumnList.svelte';
   import { chevronExpandIcon } from '../icons/expandIcons';
   import ErrorInfo from '../elements/ErrorInfo.svelte';
   import LoadingInfo from '../elements/LoadingInfo.svelte';
@@ -35,11 +35,13 @@
   import DropDownButton from '../buttons/DropDownButton.svelte';
   import FontIcon from '../icons/FontIcon.svelte';
   import CloseSearchButton from '../buttons/CloseSearchButton.svelte';
-  import { findEngineDriver } from 'dbgate-tools';
+  import { extractDbNameFromComposite, findEngineDriver } from 'dbgate-tools';
   import {
     currentDatabase,
+    databaseObjectAppObjectSearchSettings,
     extensions,
     focusedConnectionOrDatabase,
+    getDatabaseObjectAppObjectSearchSettings,
     getSelectedDatabaseObjectAppObject,
     selectedDatabaseObjectAppObject,
   } from '../stores';
@@ -53,6 +55,7 @@
   import { matchDatabaseObjectAppObject } from '../appobj/appObjectTools';
   import FocusedConnectionInfoWidget from './FocusedConnectionInfoWidget.svelte';
   import SubProcedureParamList from '../appobj/SubProcedureParamList.svelte';
+  import SubProcedureLineList from '../appobj/SubProcedureLineList.svelte';
 
   export let conid;
   export let database;
@@ -124,11 +127,33 @@
     return res;
   }
 
-  $: flatFilteredList = objectList.filter(data => {
-    const matcher = databaseObjectAppObject.createMatcher(data);
-    if (matcher && !matcher(filter)) return false;
-    return true;
-  });
+  function createSearchMenu() {
+    const res = [];
+    res.push({ label: 'Search by:', isBold: true, disabled: true });
+    if (driver?.databaseEngineTypes?.includes('document')) {
+      res.push({ label: 'Collection names', switchValue: 'collectionName' });
+    }
+    if (driver?.databaseEngineTypes?.includes('sql')) {
+      res.push({ label: 'Schema name', switchValue: 'schemaName' });
+      res.push({ label: 'Table name', switchValue: 'tableName' });
+      res.push({ label: 'View name', switchValue: 'viewName' });
+      res.push({ label: 'Column name', switchValue: 'columnName' });
+      res.push({ label: 'Column data type', switchValue: 'columnType' });
+      res.push({ label: 'Table comment', switchValue: 'tableComment' });
+      res.push({ label: 'Column comment', switchValue: 'columnComment' });
+      res.push({ label: 'Procedure/function/trigger name', switchValue: 'sqlObjectName' });
+      res.push({ label: 'Procedure/function/trigger text', switchValue: 'sqlObjectText' });
+      res.push({ label: 'Table engine', switchValue: 'tableEngine' });
+    }
+    return res.map(item => ({
+      ...item,
+      switchStore: databaseObjectAppObjectSearchSettings,
+      switchStoreGetter: getDatabaseObjectAppObjectSearchSettings,
+    }));
+  }
+
+  $: matcher = databaseObjectAppObject.createMatcher(filter, $databaseObjectAppObjectSearchSettings);
+  $: flatFilteredList = objectList.filter(data => !matcher || matcher(data));
 
   export function focus() {
     domListHandler?.focusFirst();
@@ -137,7 +162,8 @@
   $: differentFocusedDb =
     $focusedConnectionOrDatabase &&
     ($focusedConnectionOrDatabase.conid != conid ||
-      ($focusedConnectionOrDatabase?.database && $focusedConnectionOrDatabase?.database != database));
+      ($focusedConnectionOrDatabase?.database &&
+        $focusedConnectionOrDatabase?.database != extractDbNameFromComposite(database)));
 </script>
 
 {#if $status && $status.name == 'error'}
@@ -183,7 +209,7 @@
 {:else}
   <SearchBoxWrapper>
     <SearchInput
-      placeholder="Search in tables, objects, # prefix in columns"
+      placeholder="Search in tables, views, procedures"
       bind:value={filter}
       bind:this={domFilter}
       onFocusFilteredList={() => {
@@ -191,7 +217,10 @@
       }}
     />
     <CloseSearchButton bind:filter />
-    <DropDownButton icon="icon plus-thick" menu={createAddMenu} />
+    <DropDownButton icon="icon filter" menu={createSearchMenu} square={!!filter} narrow={false} />
+    {#if !filter}
+      <DropDownButton icon="icon plus-thick" menu={createAddMenu} />
+    {/if}
     <InlineButton on:click={handleRefreshDatabase} title="Refresh database connection and object list" square>
       <FontIcon icon="icon refresh" />
     </InlineButton>
@@ -220,7 +249,7 @@
         selectedObjectStore={selectedDatabaseObjectAppObject}
         getSelectedObject={getSelectedDatabaseObjectAppObject}
         selectedObjectMatcher={matchDatabaseObjectAppObject}
-        handleObjectClick={(data, options) => databaseObjectAppObject.handleObjectClick(data, options)}
+        handleObjectClick={(data, clickAction) => databaseObjectAppObject.handleObjectClick(data, clickAction)}
         onScrollTop={() => {
           domContainer?.scrollTop();
         }}
@@ -239,10 +268,14 @@
             .map(x => ({ ...x, conid, database }))}
           module={databaseObjectAppObject}
           groupFunc={data => getObjectTypeFieldLabel(data.objectTypeField, driver)}
-          subItemsComponent={data =>
+          subItemsComponent={(data, { isExpandedBySearch }) =>
             data.objectTypeField == 'procedures' || data.objectTypeField == 'functions'
-              ? SubProcedureParamList
-              : SubColumnParamList}
+              ? isExpandedBySearch
+                ? SubProcedureLineList
+                : SubProcedureParamList
+              : isExpandedBySearch && (data.objectTypeField == 'views' || data.objectTypeField == 'matviews')
+                ? SubProcedureLineList
+                : SubTableColumnList}
           isExpandable={data =>
             data.objectTypeField == 'tables' ||
             data.objectTypeField == 'views' ||
@@ -255,6 +288,7 @@
             showPinnedInsteadOfUnpin: true,
             connection: $connection,
             hideSchemaName: !!$appliedCurrentSchema,
+            searchSettings: $databaseObjectAppObjectSearchSettings,
           }}
           getIsExpanded={data =>
             expandedObjects.includes(`${data.objectTypeField}||${data.schemaName}||${data.pureName}`)}

packages/web/src/widgets/TokenizedFilteredText.svelte

@@ -0,0 +1,26 @@
+<script lang="ts">
+  import { tokenizeBySearchFilter } from 'dbgate-tools';
+
+  export let text = '';
+  export let filter = '';
+
+  $: tokenized = filter ? tokenizeBySearchFilter(text, filter) : null;
+</script>
+
+{#if tokenized}
+  {#each tokenized as token}
+    {#if token.isMatch}
+      <span class="highlight">{token.text}</span>
+    {:else}
+      {token.text}
+    {/if}
+  {/each}
+{:else}
+  {text}
+{/if}
+
+<style>
+  .highlight {
+    background-color: var(--theme-bg-orange);
+  }
+</style>

plugins/dbgate-plugin-mssql/src/backend/sql/foreignKeys.js

@@ -13,28 +13,48 @@ SELECT
     deleteAction = rc.DELETE_RULE,
 
     objectId = o.object_id 
-
 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
-INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
+INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
+    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
+    AND C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
 
-LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
-LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
---LEFT JOIN (
---SELECT i1.TABLE_NAME, i2.COLUMN_NAME
---FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
---INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
---WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
---) PT ON PT.TABLE_NAME = PK.TABLE_NAME
-INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON FK.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
+LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
+    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
+    AND C.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
 
-LEFT JOIN sys.indexes IX ON IX.name = C.UNIQUE_CONSTRAINT_NAME
-LEFT JOIN sys.objects IXT ON IXT.object_id = IX.object_id
-LEFT JOIN sys.index_columns IXC ON IX.index_id = IXC.index_id and IX.object_id = IXC.object_id
-LEFT JOIN sys.columns IXCC ON IXCC.column_id = IXC.column_id AND IXCC.object_id = IXC.object_id
-LEFT JOIN sys.schemas IXS ON IXT.schema_id = IXS.schema_id
+LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
+    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
+    AND C.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
 
-inner join sys.objects o on FK.TABLE_NAME = o.name
-inner join sys.schemas s on o.schema_id = s.schema_id and FK.TABLE_SCHEMA = s.name
+INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
+    ON FK.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
+    AND FK.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
+
+LEFT JOIN sys.indexes IX 
+    ON IX.name = C.UNIQUE_CONSTRAINT_NAME
+    AND IX.object_id = OBJECT_ID(PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME)
+
+LEFT JOIN sys.objects IXT 
+    ON IXT.object_id = IX.object_id
+
+LEFT JOIN sys.index_columns IXC 
+    ON IX.object_id = IXC.object_id 
+    AND IX.index_id = IXC.index_id
+
+LEFT JOIN sys.columns IXCC 
+    ON IXCC.object_id = IXC.object_id
+    AND IXCC.column_id = IXC.column_id
+
+LEFT JOIN sys.schemas IXS 
+    ON IXT.schema_id = IXS.schema_id
+
+INNER JOIN sys.objects o 
+    ON o.name = FK.TABLE_NAME
+    AND SCHEMA_NAME(o.schema_id) = FK.TABLE_SCHEMA
+
+INNER JOIN sys.schemas s 
+    ON o.schema_id = s.schema_id
+    AND s.name = FK.TABLE_SCHEMA
 
 where o.object_id =OBJECT_ID_CONDITION and s.name =SCHEMA_NAME_CONDITION
 `;

plugins/dbgate-plugin-mssql/src/frontend/MsSqlDumper.js

@@ -132,7 +132,9 @@ class MsSqlDumper extends SqlDumper {
     } else {
       this.dropDefault(oldcol);
       if (oldcol.columnName != newcol.columnName) this.renameColumn(oldcol, newcol.columnName);
-      this.fillNewNotNullDefaults(newcol);
+      if (!oldcol.notNull) {
+        this.fillNewNotNullDefaults(newcol);
+      }
       this.put('^alter ^table %f ^alter ^column %i ', oldcol, oldcol.columnName, newcol.columnName);
       this.columnDefinition(newcol, { includeDefault: false });
       this.endCommand();

plugins/dbgate-plugin-mysql/src/frontend/Dumper.js

@@ -32,10 +32,12 @@ class Dumper extends SqlDumper {
   }
 
   changeColumn(oldcol, newcol, constraints) {
-    this.fillNewNotNullDefaults({
-      ...newcol,
-      columnName: oldcol.columnName,
-    });
+    if (!oldcol.notNull) {
+      this.fillNewNotNullDefaults({
+        ...newcol,
+        columnName: oldcol.columnName,
+      });
+    }
     this.put('^alter ^table %f ^change ^column %i %i ', oldcol, oldcol.columnName, newcol.columnName);
     this.columnDefinition(newcol);
     this.inlineConstraints(constraints);

plugins/dbgate-plugin-oracle/src/backend/Analyser.js

@@ -23,7 +23,7 @@ function getColumnInfo(
     columnName: column_name,
     dataType: fullDataType,
     notNull: is_nullable == 'N',
-    defaultValue: autoIncrement ? undefined : default_value,
+    defaultValue: autoIncrement ? undefined : default_value?.trim(),
     autoIncrement,
   };
 }
@@ -40,7 +40,7 @@ class Analyser extends DatabaseAnalyser {
   }
 
   async _computeSingleObjectId() {
-    const { typeField,  pureName } = this.singleObjectFilter;
+    const { typeField, pureName } = this.singleObjectFilter;
     this.singleObjectId = `${typeField}:${pureName}`;
   }
 
@@ -114,7 +114,8 @@ class Analyser extends DatabaseAnalyser {
             indexes.rows.filter(
               idx =>
                 idx.tableName == newTable.pureName &&
-                !uniqueNames.rows.find(x => x.constraintName == idx.constraintName)
+                !uniqueNames.rows.find(x => x.constraintName == idx.constraintName) &&
+                !idx.constraintName.startsWith('SYS_C')
             ),
             'constraintName'
           ).map(idx => ({
@@ -141,6 +142,9 @@ class Analyser extends DatabaseAnalyser {
                 ..._.pick(col, ['columnName']),
               })),
           })),
+          identitySequenceName: (columnsGrouped[columnGroup(table)] || [])
+            .find(x => x?.default_value?.endsWith('.nextval'))
+            ?.default_value?.match(/\"([^"]+)\"\.nextval/)?.[1],
         };
       }),
       views: views.rows.map(view => ({
@@ -167,14 +171,14 @@ class Analyser extends DatabaseAnalyser {
           objectId: `procedures:${proc.pure_name}`,
           pureName: proc.pure_name,
           // schemaName: proc.schema_name,
-          createSql: `CREATE PROCEDURE "${proc.pure_name}"() LANGUAGE ${proc.language}\nAS\n$$\n${proc.definition}\n$$`,
+          createSql: `SET SQLTERMINATOR "/"\nCREATE ${proc.source_code}\n/\n`,
           contentHash: proc.hash_code,
         })),
       functions: routines.rows
         .filter(x => x.object_type == 'FUNCTION')
         .map(func => ({
           objectId: `functions:${func.pure_name}`,
-          createSql: `CREATE FUNCTION "${func.pure_name}"() RETURNS ${func.data_type} LANGUAGE ${func.language}\nAS\n$$\n${func.definition}\n$$`,
+          createSql: `SET SQLTERMINATOR "/"\nCREATE ${func.source_code}\n/\n`,
           pureName: func.pure_name,
           // schemaName: func.schema_name,
           contentHash: func.hash_code,

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

@@ -3,7 +3,7 @@ const stream = require('stream');
 
 const driverBase = require('../frontend/driver');
 const Analyser = require('./Analyser');
-const { createBulkInsertStreamBase, makeUniqueColumnNames } = global.DBGATE_PACKAGES['dbgate-tools'];
+const { makeUniqueColumnNames } = global.DBGATE_PACKAGES['dbgate-tools'];
 const createOracleBulkInsertStream = require('./createOracleBulkInsertStream');
 
 let platformInfo;
@@ -12,16 +12,11 @@ let oracledbValue;
 function getOracledb() {
   if (!oracledbValue) {
     oracledbValue = require('oracledb');
+    oracledbValue.fetchAsString = [oracledbValue.CLOB, oracledbValue.NCLOB];
   }
   return oracledbValue;
 }
 
-/*
-pg.types.setTypeParser(1082, 'text', val => val); // date
-pg.types.setTypeParser(1114, 'text', val => val); // timestamp without timezone
-pg.types.setTypeParser(1184, 'text', val => val); // timestamp
-*/
-
 function extractOracleColumns(result) {
   if (!result /*|| !result.fields */) return [];
   const res = result.map(fld => ({

plugins/dbgate-plugin-oracle/src/backend/sql/routines.js

@@ -1,41 +1,10 @@
 module.exports = `
-select
-  routine_name as "pure_name",
-  -- routine_schema as "schema_name",
-  routine_definition as "definition",
-  ora_hash(routine_definition) as "hash_code",
-  routine_type as "object_type",
-  'fixme_data_type' as "data_type",
-  'fixme_external_language' as "language"
-from (select
-    sys_context('userenv', 'DB_NAME')                                                          routine_catalog,
-    sys_context('userenv', 'DB_NAME')                                                          specific_catalog,
-    ap.owner                                                                                   specific_schema,
-    ap.owner                                                                                   routine_schema,
-    decode( ap.procedure_name, null, ap.object_name || ap.procedure_name, ap.procedure_name )  specific_name,
-    decode( ap.procedure_name, null, ap.object_name || ap.procedure_name, ap.procedure_name )  routine_name,
-    ao.object_type                                                                             routine_type,
-    decode(impltypeowner, null, to_char(null), SYS_CONTEXT('userenv', 'DB_NAME'))              type_udt_catalog,
-    --to_clob(get_proc_text(ap.owner, ap.object_name, ao.object_type, 32767))                    routine_body,
-    'fixme_routine_body.' || ap.owner || '.' || decode( ap.procedure_name, null, ap.object_name || ap.procedure_name, ap.procedure_name ) routine_body,
-    --to_clob(get_proc_text(ap.owner, ap.object_name, ao.object_type,  4000))                    routine_definition,
-    'fixme_routine_definition.' || ap.owner || '.' || decode( ap.procedure_name, null, ap.object_name || ap.procedure_name, ap.procedure_name ) routine_definition,
-    sys_context('userenv', 'DB_NAME')                                                          character_set_catalog,
-   'SYS'                                                                                       character_set_schema,
-    sys_context('userenv', 'DB_NAME')                                                          collation_catalog,
-   'SYS'                                                                                       collation_schema,
-    deterministic                                                                              is_deterministic,
-    pipelined                                                                                  is_pipelined ,
-    aggregate                                                                                  is_aggregate,
-    authid                                                                                     is_definer
-  from
-    all_procedures ap,
-    all_objects    ao
-  where
-    ap.owner = '$owner' and
-    ap.owner       = ao.owner       and
-    ap.object_name = ao.object_name and
-    ao.object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION')
-    and ao.object_name =OBJECT_ID_CONDITION
-    ) routines
+SELECT 
+  name as "pure_name",
+  type as "object_type",
+  LISTAGG(text, '') WITHIN GROUP (ORDER BY line) AS "source_code",
+  ora_hash(LISTAGG(text, '') WITHIN GROUP (ORDER BY line)) AS "hash_code"
+FROM all_source
+WHERE type in ('FUNCTION', 'PROCEDURE') AND OWNER = '$owner'
+GROUP BY name, type
 `;

plugins/dbgate-plugin-oracle/src/backend/sql/views.js

@@ -1,13 +1,10 @@
 module.exports = `
-select avv.*,
-  ora_hash("create_sql") as "hash_code"
+select avv.*
 from (select
   view_name as "pure_name",
-  -- owner as "schema_name",
-  -- SUBSTR(text_vc, 1, 3900) AS "create_sql"
   text as "create_sql"
   from all_views av
   where owner = '$owner' and text is not null
   ) avv
-  where 'views:' || "pure_name" =OBJECT_ID_CONDITION
+  where 'views:' || "pure_name" is not null
 `;

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

@@ -60,9 +60,9 @@ class Dumper extends SqlDumper {
   //   this.putCmd('^alter ^table %f ^rename ^to %i', obj, newname);
   // }
 
-  // renameColumn(column, newcol) {
-  //   this.putCmd('^alter ^table %f ^rename ^column %i ^to %i', column, column.columnName, newcol);
-  // }
+  renameColumn(column, newcol) {
+    this.putCmd('^alter ^table %f ^rename ^column %i ^to %i', column, column.columnName, newcol);
+  }
 
   // dropTable(obj, options = {}) {
   //   this.put('^drop ^table');
@@ -87,30 +87,48 @@ class Dumper extends SqlDumper {
   //   super.columnDefinition(col, options);
   // }
 
-  // changeColumn(oldcol, newcol, constraints) {
-  //   if (oldcol.columnName != newcol.columnName) {
-  //     this.putCmd('^alter ^table %f ^rename ^column %i ^to %i', oldcol, oldcol.columnName, newcol.columnName);
-  //   }
-  //   if (!testEqualTypes(oldcol, newcol)) {
-  //     this.putCmd('^alter ^table %f ^alter ^column %i ^type %s', oldcol, newcol.columnName, newcol.dataType);
-  //   }
-  //   if (oldcol.notNull != newcol.notNull) {
-  //     if (newcol.notNull) this.putCmd('^alter ^table %f ^alter ^column %i ^set ^not ^null', newcol, newcol.columnName);
-  //     else this.putCmd('^alter ^table %f ^alter ^column %i ^drop ^not ^null', newcol, newcol.columnName);
-  //   }
-  //   if (oldcol.defaultValue != newcol.defaultValue) {
-  //     if (newcol.defaultValue == null) {
-  //       this.putCmd('^alter ^table %f ^alter ^column %i ^drop ^default', newcol, newcol.columnName);
-  //     } else {
-  //       this.putCmd(
-  //         '^alter ^table %f ^alter ^column %i ^set ^default %s',
-  //         newcol,
-  //         newcol.columnName,
-  //         newcol.defaultValue
-  //       );
-  //     }
-  //   }
-  // }
+  changeColumn(oldcol, newcol, constraints) {
+    if (oldcol.columnName != newcol.columnName) {
+      this.putCmd('^alter ^table %f ^rename ^column %i ^to %i', oldcol, oldcol.columnName, newcol.columnName);
+    }
+
+    if (!oldcol.notNull) {
+      this.fillNewNotNullDefaults(newcol);
+    }
+
+    if (!testEqualTypes(oldcol, newcol) || oldcol.notNull != newcol.notNull) {
+      this.putCmd(
+        '^alter ^table %f ^modify (%i %s %k)',
+        newcol,
+        newcol.columnName,
+        newcol.dataType,
+        newcol.notNull ? 'not null' : 'null'
+      );
+    }
+
+    if (oldcol.defaultValue != newcol.defaultValue) {
+      if (newcol.defaultValue?.trim()) {
+        this.putCmd('^alter ^table %f ^modify (%i ^default %s)', newcol, newcol.columnName, newcol.defaultValue);
+      } else {
+        this.putCmd('^alter ^table %f ^modify (%i ^default ^null)', newcol, newcol.columnName);
+      }
+    }
+  }
+
+  selectScopeIdentity(table) {
+    const sequence = table.identitySequenceName;
+    if (sequence) {
+      this.put('^select %i.CURRVAL FROM DUAL', sequence);
+    }
+  }
+
+  renameTable(obj, newname) {
+    this.putCmd('^alter ^table %f ^rename ^to %i', obj, newname);
+  }
+
+  renameSqlObject(obj, newname) {
+    this.putCmd('^rename %f ^to %i', obj, newname);
+  }
 
   // putValue(value) {
   //   if (value === true) this.putRaw('true');

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

@@ -13,13 +13,16 @@ const dialect = {
   ilike: true,
   // stringEscapeChar: '\\',
   stringEscapeChar: "'",
-  fallbackDataType: 'varchar',
+  fallbackDataType: 'varchar(250)',
   anonymousPrimaryKey: false,
   enableConstraintsPerTable: true,
   dropColumnDependencies: ['dependencies'],
   quoteIdentifier(s) {
     return '"' + s + '"';
   },
+  userDatabaseNamePrefix: 'C##',
+  upperCaseAllDbObjectNames: true,
+  requireStandaloneSelectForScopeIdentity: true,
 
   createColumn: true,
   dropColumn: true,
@@ -34,6 +37,7 @@ const dialect = {
   dropUnique: true,
   createCheck: true,
   dropCheck: true,
+  renameSqlObject: true,
 
   dropReferencesWhenDropTable: true,
   requireFromDual: true,

plugins/dbgate-plugin-postgres/package.json

@@ -35,6 +35,7 @@
     "webpack-cli": "^5.1.4"
   },
   "dependencies": {
+    "wkx": "^0.5.0",
     "pg-copy-streams": "^6.0.6",
     "dbgate-query-splitter": "^4.11.2",
     "dbgate-tools": "^6.0.0-alpha.1",

plugins/dbgate-plugin-postgres/src/backend/Analyser.js

@@ -97,30 +97,30 @@ class Analyser extends DatabaseAnalyser {
     let fkColumns = null;
 
     this.feedback({ analysingMessage: 'Loading foreign key constraints' });
-    const fk_tableConstraints = await this.analyserQuery('fk_tableConstraints', ['tables']);
+    // const fk_tableConstraints = await this.analyserQuery('fk_tableConstraints', ['tables']);
 
     this.feedback({ analysingMessage: 'Loading foreign key refs' });
-    const fk_referentialConstraints = await this.analyserQuery('fk_referentialConstraints', ['tables']);
+    const foreignKeys = await this.analyserQuery('foreignKeys', ['tables']);
 
     this.feedback({ analysingMessage: 'Loading foreign key columns' });
     const fk_keyColumnUsage = await this.analyserQuery('fk_keyColumnUsage', ['tables']);
 
-    const cntKey = x => `${x.constraint_name}|${x.constraint_schema}`;
+    // const cntKey = x => `${x.constraint_name}|${x.constraint_schema}`;
     const fkRows = [];
-    const fkConstraintDct = _.keyBy(fk_tableConstraints.rows, cntKey);
-    for (const fkRef of fk_referentialConstraints.rows) {
-      const cntBase = fkConstraintDct[cntKey(fkRef)];
-      const cntRef = fkConstraintDct[`${fkRef.unique_constraint_name}|${fkRef.unique_constraint_schema}`];
-      if (!cntBase || !cntRef) continue;
+    // const fkConstraintDct = _.keyBy(fk_tableConstraints.rows, cntKey);
+    for (const fkRef of foreignKeys.rows) {
+      // const cntBase = fkConstraintDct[cntKey(fkRef)];
+      // const cntRef = fkConstraintDct[`${fkRef.unique_constraint_name}|${fkRef.unique_constraint_schema}`];
+      // if (!cntBase || !cntRef) continue;
       const baseCols = _.sortBy(
         fk_keyColumnUsage.rows.filter(
-          x => x.table_name == cntBase.table_name && x.constraint_name == cntBase.constraint_name
+          x => x.table_name == fkRef.table_name && x.constraint_name == fkRef.constraint_name && x.table_schema == fkRef.table_schema
         ),
         'ordinal_position'
       );
       const refCols = _.sortBy(
         fk_keyColumnUsage.rows.filter(
-          x => x.table_name == cntRef.table_name && x.constraint_name == cntRef.constraint_name
+          x => x.table_name == fkRef.ref_table_name && x.constraint_name == fkRef.unique_constraint_name && x.table_schema == fkRef.ref_table_schema
         ),
         'ordinal_position'
       );
@@ -132,10 +132,10 @@ class Analyser extends DatabaseAnalyser {
 
         fkRows.push({
           ...fkRef,
-          pure_name: cntBase.table_name,
-          schema_name: cntBase.table_schema,
-          ref_table_name: cntRef.table_name,
-          ref_schema_name: cntRef.table_schema,
+          pure_name: fkRef.table_name,
+          schema_name: fkRef.table_schema,
+          ref_table_name: fkRef.ref_table_name,
+          ref_schema_name: fkRef.ref_table_schema,
           column_name: baseCol.column_name,
           ref_column_name: refCol.column_name,
         });

plugins/dbgate-plugin-postgres/src/backend/drivers.js

@@ -3,6 +3,7 @@ const stream = require('stream');
 
 const driverBases = require('../frontend/drivers');
 const Analyser = require('./Analyser');
+const wkx = require('wkx');
 const pg = require('pg');
 const pgCopyStreams = require('pg-copy-streams');
 const {
@@ -21,10 +22,38 @@ pg.types.setTypeParser(1082, 'text', val => val); // date
 pg.types.setTypeParser(1114, 'text', val => val); // timestamp without timezone
 pg.types.setTypeParser(1184, 'text', val => val); // timestamp
 
-function extractPostgresColumns(result) {
+function extractGeographyDate(value) {
+  try {
+    const buffer = Buffer.from(value, 'hex');
+    const parsed = wkx.Geometry.parse(buffer).toWkt();
+
+    return parsed;
+  } catch (_err) {
+    return value;
+  }
+}
+
+function transformRow(row, columnsToTransform) {
+  if (!columnsToTransform?.length) return row;
+
+  for (const col of columnsToTransform) {
+    const { columnName, dataTypeName } = col;
+    if (dataTypeName == 'geography') {
+      row[columnName] = extractGeographyDate(row[columnName]);
+    }
+  }
+
+  return row;
+}
+
+function extractPostgresColumns(result, dbhan) {
   if (!result || !result.fields) return [];
+
+  const { typeIdToName = {} } = dbhan;
   const res = result.fields.map(fld => ({
     columnName: fld.name,
+    dataTypeId: fld.dataTypeID,
+    dataTypeName: typeIdToName[fld.dataTypeID],
   }));
   makeUniqueColumnNames(res);
   return res;
@@ -105,6 +134,10 @@ const drivers = driverBases.map(driverBase => ({
       database,
     };
 
+    const datatypes = await this.query(dbhan, `SELECT oid, typname FROM pg_type WHERE typname in ('geography')`);
+    const typeIdToName = _.fromPairs(datatypes.rows.map(cur => [cur.oid, cur.typname]));
+    dbhan['typeIdToName'] = typeIdToName;
+
     if (isReadOnly) {
       await this.query(dbhan, 'SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY');
     }
@@ -122,7 +155,7 @@ const drivers = driverBases.map(driverBase => ({
       };
     }
     const res = await dbhan.client.query({ text: sql, rowMode: 'array' });
-    const columns = extractPostgresColumns(res);
+    const columns = extractPostgresColumns(res, dbhan);
     return { rows: (res.rows || []).map(row => zipDataRow(row, columns)), columns };
   },
   stream(dbhan, sql, options) {
@@ -132,17 +165,26 @@ const drivers = driverBases.map(driverBase => ({
     });
 
     let wasHeader = false;
+    let columnsToTransform = null;
 
     query.on('row', row => {
       if (!wasHeader) {
-        columns = extractPostgresColumns(query._result);
+        columns = extractPostgresColumns(query._result, dbhan);
         if (columns && columns.length > 0) {
           options.recordset(columns);
         }
         wasHeader = true;
       }
 
-      options.row(zipDataRow(row, columns));
+      if (!columnsToTransform) {
+        const transormableTypeNames = Object.values(dbhan.typeIdToName ?? {});
+        columnsToTransform = columns.filter(x => transormableTypeNames.includes(x.dataTypeName));
+      }
+
+      const zippedRow = zipDataRow(row, columns);
+      const transformedRow = transformRow(zippedRow, columnsToTransform);
+
+      options.row(transformedRow);
     });
 
     query.on('end', () => {
@@ -157,7 +199,7 @@ const drivers = driverBases.map(driverBase => ({
       }
 
       if (!wasHeader) {
-        columns = extractPostgresColumns(query._result);
+        columns = extractPostgresColumns(query._result, dbhan);
         if (columns && columns.length > 0) {
           options.recordset(columns);
         }
@@ -234,6 +276,8 @@ const drivers = driverBases.map(driverBase => ({
     let wasHeader = false;
     let columns = null;
 
+    let columnsToTransform = null;
+
     const pass = new stream.PassThrough({
       objectMode: true,
       highWaterMark: 100,
@@ -241,7 +285,7 @@ const drivers = driverBases.map(driverBase => ({
 
     query.on('row', row => {
       if (!wasHeader) {
-        columns = extractPostgresColumns(query._result);
+        columns = extractPostgresColumns(query._result, dbhan);
         pass.write({
           __isStreamHeader: true,
           ...(structure || { columns }),
@@ -249,12 +293,20 @@ const drivers = driverBases.map(driverBase => ({
         wasHeader = true;
       }
 
-      pass.write(zipDataRow(row, columns));
+      if (!columnsToTransform) {
+        const transormableTypeNames = Object.values(dbhan.typeIdToName ?? {});
+        columnsToTransform = columns.filter(x => transormableTypeNames.includes(x.dataTypeName));
+      }
+
+      const zippedRow = zipDataRow(row, columns);
+      const transformedRow = transformRow(zippedRow, columnsToTransform);
+
+      options.row(transformedRow);
     });
 
     query.on('end', () => {
       if (!wasHeader) {
-        columns = extractPostgresColumns(query._result);
+        columns = extractPostgresColumns(query._result, dbhan);
         pass.write({
           __isStreamHeader: true,
           ...(structure || { columns }),

plugins/dbgate-plugin-postgres/src/backend/sql/fk_referential_constraints.js

@@ -1,11 +0,0 @@
-module.exports = `
-select 
-	fk.constraint_name as "constraint_name",
-	fk.constraint_schema as "constraint_schema",
-	fk.update_rule as "update_action",
-	fk.delete_rule as "delete_action",
-    fk.unique_constraint_name as "unique_constraint_name",
-    fk.unique_constraint_schema as "unique_constraint_schema" 
-from information_schema.referential_constraints fk
-where fk.constraint_schema =SCHEMA_NAME_CONDITION
-`;

plugins/dbgate-plugin-postgres/src/backend/sql/fk_table_constraints.js

@@ -1,9 +0,0 @@
-module.exports = `
-select 
-	base.table_name as "table_name",
-	base.table_schema as "table_schema",
-	base.constraint_name as "constraint_name",
-	base.constraint_schema as "constraint_schema"
-from information_schema.table_constraints base
-where ('tables:' || base.table_schema || '.' || base.table_name) =OBJECT_ID_CONDITION and base.table_schema =SCHEMA_NAME_CONDITION
-`;

plugins/dbgate-plugin-postgres/src/backend/sql/foreignKeys.js

@@ -0,0 +1,20 @@
+module.exports = `
+SELECT 
+    nsp.nspname AS table_schema,
+    rel.relname AS table_name,
+    con.conname AS constraint_name,
+    nsp2.nspname AS ref_table_schema,
+    rel2.relname AS ref_table_name,
+    conpk.conname AS unique_constraint_name
+FROM pg_constraint con
+JOIN pg_class rel ON rel.oid = con.conrelid
+JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
+JOIN pg_class rel2 ON rel2.oid = con.confrelid
+JOIN pg_namespace nsp2 ON nsp2.oid = rel2.relnamespace
+JOIN pg_constraint conpk 
+    ON conpk.conrelid = con.confrelid 
+   AND conpk.conkey = con.confkey
+   AND conpk.contype IN ('p','u')  -- 'p' = primary key, 'u' = unique constraint
+WHERE con.contype = 'f' AND ('tables:' || nsp.nspname || '.' || rel.relname) =OBJECT_ID_CONDITION AND nsp.nspname =SCHEMA_NAME_CONDITION
+;
+`;

plugins/dbgate-plugin-postgres/src/backend/sql/index.js

@@ -15,10 +15,8 @@ const uniqueNames = require('./uniqueNames');
 const geometryColumns = require('./geometryColumns');
 const geographyColumns = require('./geographyColumns');
 const proceduresParameters = require('./proceduresParameters');
-
+const foreignKeys = require('./foreignKeys');
 const fk_keyColumnUsage = require('./fk_key_column_usage');
-const fk_referentialConstraints = require('./fk_referential_constraints');
-const fk_tableConstraints = require('./fk_table_constraints');
 
 module.exports = {
   columns,
@@ -27,8 +25,7 @@ module.exports = {
   viewModifications,
   primaryKeys,
   fk_keyColumnUsage,
-  fk_referentialConstraints,
-  fk_tableConstraints,
+  foreignKeys,
   views,
   routines,
   routineModifications,

plugins/dbgate-plugin-postgres/src/backend/sql/primaryKeys.js

@@ -7,6 +7,7 @@ select
 	key_column_usage.column_name as "column_name"
 from information_schema.table_constraints
 inner join information_schema.key_column_usage on table_constraints.table_name = key_column_usage.table_name and table_constraints.constraint_name = key_column_usage.constraint_name
+    and table_constraints.table_schema = key_column_usage.table_schema
 where 
 		table_constraints.table_schema !~ '^_timescaledb_' 
 		and table_constraints.constraint_type = 'PRIMARY KEY'

plugins/dbgate-plugin-postgres/src/frontend/Dumper.js

@@ -89,7 +89,9 @@ class Dumper extends SqlDumper {
       }
     }
     if (oldcol.notNull != newcol.notNull) {
-      this.fillNewNotNullDefaults(newcol);
+      if (!oldcol.notNull) {
+        this.fillNewNotNullDefaults(newcol);
+      }
       if (newcol.notNull) this.putCmd('^alter ^table %f ^alter ^column %i ^set ^not ^null', newcol, newcol.columnName);
       else this.putCmd('^alter ^table %f ^alter ^column %i ^drop ^not ^null', newcol, newcol.columnName);
     }

yarn.lock

@@ -2685,10 +2685,10 @@ accepts@~1.3.8:
     mime-types "~2.1.34"
     negotiator "0.6.3"
 
-ace-builds@^1.4.8:
-  version "1.33.1"
-  resolved "https://registry.yarnpkg.com/ace-builds/-/ace-builds-1.33.1.tgz#a37904fb2d37756733346c688dcb235662877d6e"
-  integrity sha512-pj5mcXV1n3s86UI4SWUt8X0ltN8cTaYcvF76cSmvy5i2ZDtXX9KkjVcYTGkCV7ox6VUrzqHByeqH0xRsMjXi4g==
+ace-builds@^1.36.5:
+  version "1.36.5"
+  resolved "https://registry.yarnpkg.com/ace-builds/-/ace-builds-1.36.5.tgz#ae9cc7a32eccc2f484926131c00545cd6b78a6a6"
+  integrity sha512-mZ5KVanRT6nLRDLqtG/1YQQLX/gZVC/v526cm1Ru/MTSlrbweSmqv2ZT0d2GaHpJq035MwCMIrj+LgDAUnDXrg==
 
 acorn-globals@^4.1.0:
   version "4.3.4"
@@ -12161,6 +12161,13 @@ wildcard@^2.0.0:
   resolved "https://registry.yarnpkg.com/wildcard/-/wildcard-2.0.1.tgz#5ab10d02487198954836b6349f74fff961e10f67"
   integrity sha512-CC1bOL87PIWSBhDcTrdeLo6eGT7mCFtrg0uIJtqJUFyK+eJnzl8A1niH56uu7KMa5XFrtiV+AQuHO3n7DsHnLQ==
 
+wkx@^0.5.0:
+  version "0.5.0"
+  resolved "https://registry.yarnpkg.com/wkx/-/wkx-0.5.0.tgz#c6c37019acf40e517cc6b94657a25a3d4aa33e8c"
+  integrity sha512-Xng/d4Ichh8uN4l0FToV/258EjMGU9MGcA0HV2d9B/ZpZB3lqQm7nkOdZdm5GhKtLLhAE7PiVQwN4eN+2YJJUg==
+  dependencies:
+    "@types/node" "*"
+
 wmf@~1.0.1:
   version "1.0.2"
   resolved "https://registry.yarnpkg.com/wmf/-/wmf-1.0.2.tgz#7d19d621071a08c2bdc6b7e688a9c435298cc2da"