synle / sqlui-native Goto Github PK

We should not allow multiple context menu on top of each other

Issue

• cassandra
• mongodb
• redis (will not be supported)
• cosmosdb
• aztable

The promise in getColumn is not properly returned / resolved.

Undo does not work in the code editor after applying any query

• Right now after you apply the query, the undo stack is completely wiped out (editor.setValue) will destroy the undo stack.
• Need to execute edits and push undo stop to simulate regular edits and persist the undo operation.

Below is the sample code

const newValue = props.value || '';

// https://stackoverflow.com/questions/60965171/not-able-to-do-undo-in-monaco-editor
// NOTE we can't do setValue here because it will wipe out the undo stack
// Select all text
const fullRange = editor.getModel()?.getFullModelRange();

if(fullRange !== undefined){
  // Apply the text over the range
  editor.executeEdits(null, [{
    text: newValue,
    range: fullRange
  }]);

  // Indicates the above edit is a complete undo/redo change.
  editor.pushUndoStop();
} else {
  // fall back to use setValue if we can't find the range
  editor.setValue(newValue);
}

It's nice to be able to

• Replicate the table creation script from one dialect into another dialect.
• Copy data from one source into another source

Screenshots

https://github.com/felixrieseberg/electron-windows-store

https://www.electronjs.org/docs/latest/tutorial/windows-store-guide

https://www.electron.build/configuration/appx.html

This is iffy, not sure if it's a good idea to support and if it is, should it be done as a map for table vs columns?

Sample PR code

The code for this is ready here, but will not merge until we can find a better UX.
https://github.com/synle/sqlui-native/pull/331/files

Sample code used to scan the iterator

for await (const key of client.scanIterator(iteratorParams)) {
        res.push({
          name: key,
          columns: [],
        })

        if(res.length === 15){
          // top it off at max records
          break;
        }
      }

Ability to lock / pin queries which will not be closed

Screenshots

Will allow users to generate starter code.

• Added sample code snippets to let you write your node js code.
• This basically generates starter code where you can use to run in your language of choice. At the moment only node js is supported. We'll plan to add more snippets like Java and Python.

To be supported Language

• Node JS (using Sequelize)
• Python (using Sqlalchemy)
• Java

To be supported Database Engine

• Relational Database (with Sequelize)
  • mysql
    • Node JS
    • Python
    • Java
  • mariadb
    • Node JS
    • Python
    • Java
  • mssql
    • Node JS
    • Python
    • Java
  • postgres
    • Node JS
    • Python
    • Java
  • sqlite
    • Node JS
    • Python
    • Java
• cassandra
  • Node JS
  • Python
  • Java
• mongodb
  • Node JS
  • Python
  • Java
• redis
  • Node JS
  • Python
  • Java
• cosmosdb
  • Node JS
  • Python
  • Java
• aztable
  • Node JS
  • Python
  • Java

Screenshots

@cicorias found a bug in the Cassandra setup from within CosmosDB

I haven't tested that scenario, most of my locals are with docker container for Cassandra and as for CosmosDB (Core SQL). I can take a look

Potential Issues:

SSL Check

Refer to this for more info: https://docs.microsoft.com/en-us/azure/developer/javascript/how-to/with-database/use-cassandra-as-cosmos-db#use-native-sdk-packages-to-connect-to-cassandra-db-on-azure

const client = new cassandra.Client({
  ...clientOptions,
  ...{
    sslOptions: {
      rejectUnauthorized: false,
    }
  }
});

Connection string needs be properly escaped.

Needs to use encodeURIComponent

• Can't render array of primitives properly inside of the table

Sample query to generate this behavior

Use any one of the select distinct inside of MongoDB

db.collection('sy-collection-1a')
  .distinct(
    'location', {
    }
  )

Issue

Raw JSON input

This is the raw JSON that causes this issue

[
  "",
  "Bay Area",
  "Morgan Hill",
  "San Jose, CA",
  "sdfgdsf"
]

Bug

Expected

Will render the column for these primitives as standalone

• When we close a connection or query, we should show a toast with the ability to undo that changes.

Screenshots

Apparently -1 is not the page size to tell react-table to show all data. Need to make the initiation to properly handle the pagesize rendering to show all data

Some engine like Azure CosmosDB supports both the javascript and SQL syntax, it's good to allow user to override the detected syntax mode.

Sample JSON

Query

>>> cat /home/syle/.sqlui-native/folders.bookmarks.json
{
  "bookmarks.1656457990125.7306698064745569": {
    "id": "bookmarks.1656457990125.7306698064745569",
    "type": "Query",
    "data": {
      "id": "queryId.1656386963631.7337545015452436",
      "name": "Sy test 123",
      "sql": "SELECT\n  *\nFROM\n  albums_snapshot4\nLIMIT\n  100",
      "connectionId": "connection.1656340917546.3396115637536507",
      "databaseId": "Sqlite"
    }
  }
}

Add query to bookmark screenshots

Add connection to bookmark screenshots

bookmarks link

Bookmark landing page

A custom route / modal to make read and edit / delete record easier

Limitations

• Currently we don't automatically apply the query for inserts and update and requires one additional step to verify if the generated query is correct.
• For update queries, we will attempt to only do partial update queries, but if none of the fields are updated by the user, we will attempt to generate the update on all fields.

Screenshots

Click to Show Record Details in Modal

New Record Page

Edit Record Page

Issue is caused by us not support SSL for Redis and no option to enter the password.

How to construct the Connection for Azure Redis

• Go to Access Keys on Azure Redis
• Connection will look like
  rediss://:<password>@<your_redis_host>:<redis_port>
• Sample connection will look like this
  rediss://:[email protected]:6380

Screenshots

Changes:

Azure CosmosDB required that we provides the tableID, so we'll change the connection selector to add the optional tableId when the dialect is detected as cosmosdb

Connection String

cosmosdb://AccountEndpoint=some_cosmos_endpoint;AccountKey=some_cosmos_account_key

Screenshots

Syntax

Use this syntax

db.create('new-database-name')

Screenshots

• cassandra
• mongodb
• redis (will not be supported)
• cosmosdb
• aztable

Az Table

Create

Edit

Az CosmosDB

Create

Edit

Mongodb

Create

Edit

Cassandra

Create

Edit

• Don't allow using the same session in different windows due to conflicts of Save operations
• Attached windowId to each of the opened window so we can keep track of opened session id and properly free up closed session id.
• Introduce a windowId that mapped to sessionId
• New window will be presented with an option to select a choice of sessions on page load
• Re-useable session selection components
• Disabled state for sessions you can't select (either opened by another window or not)
• No longer requires a concept of DEFAULT_SESSION_ID which can't be deleted.

Confirmed this issue is isolated to sqlite. Other dialects support multiple statements.

I got a steam deck recently. It runs on arch-linux. Will spend the time to create the flatpak and make a runnable package for steam deck (run on arch linux).

sudo pacman -U ./sqlui-native*.pacman

Features:

• Should be able to make a backup and restore for connection and query before it's deleted.
• Should allow a global flag to bypass recycle bin and delete everything for goods.
• Add a UI to restore connection / connection query

Sample JSON for Recycle Bin Item

>>> cat /home/syle/.sqlui-native/folders.recycleBin.json

Queries Backup

{
  "recycleBin.1656299786451.7141772469794720": {
    "id": "recycleBin.1656299786451.7141772469794720",
    "name": "Tab 2",
    "type": "Query",
    "data": {
      "id": "queryId.1656299775866.9765773352545586",
      "name": "Tab 2",
      "sql": ""
    }
  },
  "recycleBin.1656299786760.4011666588716109": {
    "id": "recycleBin.1656299786760.4011666588716109",
    "name": "Tab 3",
    "type": "Query",
    "data": {
      "id": "queryId.1656299776525.193067786210257",
      "name": "Tab 3",
      "sql": ""
    }
  }
}

Connection Backup

{
  "id": "recycleBin.1656300821267.4191296173010423",
  "name": "mysql Connection - 6/26/2022",
  "type": "Connection",
  "data": {
    "id": "connection.1656300705160.7654608070047222",
    "connection": "mysql://root:password@localhost:3306",
    "name": "mysql Connection - 6/26/2022"
  }
}

Screenshots

Should show dedicated icon to indicate if something is a primary key or clustering key

Screenshots

{ "code": "ER_BAD_DB_ERROR", "errno": 1049, "sqlState": "42000", "sqlMessage": "Unknown database ':undefined/users_dev'" }

Mac Smooth Scrolling feels unresponsive. Disable smooth scrolling seems to help

app.commandLine.appendSwitch('disable-smooth-scrolling

• electron/electron#11583
• electron/electron#28537 (comment)
• revoltchat/desktop#94

Soft deletes are not available yet for sessions, it is currently available only for queries and connections.

It will be nice to add soft delete supports for sessions.

Add Connection Actions - Create Database, etc...

• Some brand new connections without any databases might be hard to use when it doesn't have a database. Most actions are tied to databases and tables.

MongoDB cannot reference ObjectId for use in find and update

• Right now only one toaster is shown even though the stack has multiple items

Potential Solutions:

Use https://github.com/iamhosseindhv/notistack

Screenshots

Sample connection string

aztable://DefaultEndpointsProtocol=https;AccountName=<your_account_name>;AccountKey=<your_account_key>;EndpointSuffix=core.windows.net

Screenshots

There is no place to delete a session.

• A place to delete session.
• electron-default should not be allowed to be deleted.
• Special handling for cases where users open a deleted session.

screenshots

Delete session in command palette

Error for default session delete

Added a new setting option that allows us to open the new query in a new tab or replace the current tab with the selected query

Screenshots

Sort order of the column headers always sorted alphabetically

• We should use the actual sort order from the query for the column headers instead of always sorting it alphabetically

Sequelize Interface : https://sequelize.org/api/v6/class/src/dialects/abstract/query-interface.js~queryinterface#instance-method-getForeignKeysForTables

Sample code to get foreignKeys for relational database

try {
  const res1 = await this.getConnection(database)
    .getQueryInterface()
    .getForeignKeyReferencesForTable(table);

  console.log(this.dialect, database, table, res1);
} catch (err) {}

Sample query to create table with foreignKeys

CREATE TABLE users (
  id serial,
  username VARCHAR(25) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE addresses (
  user_id int NOT NULL,
  street VARCHAR(50) NOT NULL,
  city VARCHAR(50) NOT NULL,
  state VARCHAR(50) NOT NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);

Sample foreignKey responses from relational databases in Sequelize

Postgres

{
  "constraintName": "fk_user_id",
  "constraintSchema": "public",
  "constraintCatalog": "og_music_store",
  "tableName": "addresses",
  "tableSchema": "public",
  "tableCatalog": "og_music_store",
  "columnName": "user_id",
  "referencedTableSchema": "public",
  "referencedTableCatalog": "og_music_store",
  "referencedTableName": "users",
  "referencedColumnName": "id"
}

Mysql

{
  "tableName": "addresses",
  "columnName": "user_id",
  "referencedTableName": "users",
  "referencedColumnName": "id",
  "tableCatalog": undefined,
  "referencedTableCatalog": undefined
}

Recommendation:

Maybe consider visualize it with this library
https://github.com/projectstorm/react-diagrams/tree/master/diagrams-demo-project

Was addressed as bookmarks feature. Check #387

• MongoDB doesn't render findOne nicely
• It does render, but it shows up as a raw JSON object, will be nice to render it as a table row.

Issue

Expected

Should render it in the table format