Only changes to the query sheet text are considered

As a pseudo-SQL language, parsed and translated to :

• document creation for insert statements
• query-then-update for update statements
• query-then-delete for delete operations

e.g.:

insert into c { name: 'foo', price: 123.0 }

update c set c.answer = 42 where c.question = 'life, the universe and everything'

delete c where c.expirationDate < '2019-01-01T00:00:00'

(exact syntax tbd)

Request options should be supported, as specified in #2 (e.g. partition key, pre/post triggers...)

Bonus: optionally, create a temporary stored procedure to perform the operations atomically in a transaction (would fail if execution exceeds the provisioned throughput, e.g. if too many documents are affected)

Bug Description

When using the Cosmos DB Studio, it is possible to encounter transient errors, and the application cannot handle such errors (like 503 Server Busy) now. When the transient error happens, the application would even have an inconsistent view with the real service. For instance, if a database deletion request (use the SDK API DeleteAsync to delete database A) arrived the Cosmos DB server but the response never returned or a 408 Timeout returned, then the database name ("A") will still remain in the list of Cosmos DB Studio, but user cannot operate on that database anymore (because there is no database A in the Cosmos server anymore).

Discussion

As this document shown, there are some transient error codes (408, 410, 429, 449 and 503) that we can retry on:
https://docs.microsoft.com/en-us/azure/cosmos-db/sql/conceptual-resilient-sdk-applications#should-my-application-retry-on-errors

Additional Context

We are willing to discuss the transient error handlings and write a PR for this issue.

Currently the only way to configure new database connections is to edit the connections.json file in app data folder

e.g.

@category = "food"

select c.id, c.ProductName from c where c.category = @category

Actual syntax to be defined

and create / edit / delete them

Maybe with YAML comments or directives above each statement, e.g.:

# partitionKey: "blah"
select * from c where c.name = 'foo'

Global options that apply to the whole query sheet would be useful as well

Options to support:

• partition key
• cross partition query (maybe by default if no partition key specified)
• pre/post triggers (for update/delete/insert statements described in #8)
• collection (assuming database-scoped query sheets are supported)
• database (assuming account-scoped query sheets are supported)

The query results should show the documents separately, with an option to modify or delete them.

Problem: how do we know if the results contain actual raw documents, not projections? Testing for the presence of system properties such as _self or _rid isn't fool proof, since they can be included in a projection.

Some features (e.g. create/edit container or database) seem to be broken when connecting to a Cosmos account in Serverless mode. Need to investigate.

I have two sheets I leave open in Studio when I shut it down. After shutting down, I moved the folder where the sheets were being stored. The next time I attempted to open Studio, nothing happened. I checked the event log and found the load failed because it couldn't find those two sheets.

The work-around was to move the folder back to its original location, open Studio, and do a Save As for each sheet, moving it to its new location.

Here is the message from the event log:

• 1026 0 2 0 0 0x80000000000000 51998 Application DESKTOP-EQGSM0V
• Application: CosmosDBStudio.exe CoreCLR Version: 4.700.20.36602 .NET Core Version: 3.1.7 Description: The process was terminated due to an unhandled exception. Exception Info: System.IO.DirectoryNotFoundException: Could not find a part of the path 'D:\Dev\SQL Queries\Cosmos Queries\Last 20 events.cdbsqs'. at System.IO.FileStream.ValidateFileHandle(SafeFileHandle fileHandle) at System.IO.FileStream.CreateFileOpenHandle(FileMode mode, FileShare share, FileOptions options) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options) at System.IO.StreamReader.ValidateArgsAndOpenPath(String path, Encoding encoding, Int32 bufferSize) at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks) at System.IO.File.InternalReadAllText(String path, Encoding encoding) at System.IO.File.ReadAllText(String path) at CosmosDBStudio.Services.Implementation.QueryPersistenceService.Load(String path) at CosmosDBStudio.ViewModel.MainWindowViewModel.LoadWorkspace() at CosmosDBStudio.ViewModel.MainWindowViewModel..ctor(IViewModelFactory viewModelFactory, IMessenger messenger, IDialogService dialogService, IQueryPersistenceService queryPersistenceService, AccountCommands accountCommands) at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions) at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitRootCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitRootCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope) at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.b__0(ServiceProviderEngineScope scope) at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope) at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType) at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType) at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType) at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider) at CosmosDBStudio.Program.Main(String[] args)

e.g. PRODUCTION, STAGING, DEV...
Maybe lock modifications when the label is PRODUCTION
TBD

In case the app (or the computer) crashes without closing the app properly

Like VS Code or Azure Data Studio

I don't know why they don't come that way from the API, but it makes it hard to pick out a container when you have more than a handful.

The option to use the selected container only appears when the container node itself is selected

It would be better to show it when any of the container node's descendants is selected

Currently the statement to be executed has to be selected in the editor. Automatically finding the start and end of the current statement (based on the cursor position) and selecting it would make the workflow smoother.

Hurdle: there's no easy way to list all distinct partition keys from a container. It's possible to use DISTINCT in a cross-partition query, but it's an expensive query.

Currently possible through UI only

Errors returned by Cosmos DB are pure cr*p. Here's an exemple of the body of an error response (formatted):

{
        "code": "BadRequest",
        "message": "Gateway Failed to Retrieve Query Plan: Message: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":36,\"end\":36},\"code\":\"SC1002\",\"message\":\"Syntax error, unexpected end-of-file.\"}]}\r\nActivityId: 3d7a2504-ea2b-4075-a957-0ca8e4ae05f8, Microsoft.Azure.Documents.Common/2.11.0, Microsoft.Azure.Documents.Common/2.11.0"
}

The message content is plain text, with some JSON in the middle of it. It's unreadable, and the actual error data is difficult to extract.

It would be nice to parse this mess, display a clear error message, and show error squiggles in the editor at the specified location.

Specifically, "close other tabs" on right-click, but rearranging would be nice too.

Some SQL and Javascript keywords are not recognized

And possibly persist unsaved query sheets, like in Azure Data Studio or VS Code (split to #13)

Maybe show the most important properties (configurable) as a table, and the raw JSON document in a popup? TBD

Currently automatic updates are only available from the Microsoft Store

Hi,
I tested your project and greatly appreciated it : many thanks !

A colleague of mine wanted to used it, but faced an error message while launching CosmosDb Studio be it installed through the App store or by sideloading.
His Windows 10 version is 19041, so should be okay with your requirements.

Is there any kind of redistributable that we should be aware of to make it run properly ?

e.g. right click the "foo" folder in the account explorer, and add an account with "foo/bar" as the folder

It'd be nice to have a tab similar to the "Query Stats" pane in cosmos.azure.com.

The stats are easily available from the SDK:

• https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-query-metrics#query-execution-metrics
• https://docs.microsoft.com/en-us/azure/cosmos-db/find-request-unit-charge?tabs=dotnetv3#use-the-net-sdk

Show squiggles at error location

This is because connections are pooled; the connection should be cleared from the pool when the account key is changed.

To make it easier to view large documents and stored procedures

Switching containers via "pick a container" is slower than it should be, because you have to drill down through the accounts and databases to find your container, but there are usually (often?) loaded on-demand.

It seems that the app would be faster if all "pick a container" trees and the tree on the left-hand side of the program shared a common state, so that if a node is loaded in one it does not need to be reloaded in the other. Like in SSMS, a "refresh" option would be available on each node for if/when needed.

Once this is available, the "pick a container" tree could be unfolded on opening to the current location.