Some Ideas

[alansbraga]

Hello, I’m using Verso and I’d like to congratulate you for the amazing work. I would like to check a few possible changes with you:

I looked at the source code of the SchemaCache class and, from what I understood, it is limited to SQL Server and SQLite. I was thinking about making modifications to support FirebirdSQL and sending you a PR, but it will end up full of ifs. Could you structure it in a more separated/decoupled way so we can add new database providers without mixing everything together, before I start making these changes?

Depending on the situation, I like using the SQL cell approach, and in other cases I prefer using Dapper. Some options would help me a lot — I’m not sure which one works best for you:
a) expose the internal connection of SQL cells;
b) allow the connection string in SQL cells to accept variables;
c) allow SQL cells to accept a preconfigured DbConnection.
Note: I was having issues with Dapper and dynamic variables, but I found out that you just need to add the Microsoft.CSharp NuGet package.

This is pure nitpicking, but here it goes: in Polyglot, when we executed a cell, it was clear that it was running and when it finished, even if it was very fast. In Verso, we click Run and nothing seems to happen — sometimes I click several times just to be sure it actually executed, both in cells that return nothing and in cells that return something but didn’t visibly change anything.

Thank you very much for your work!

[TorreyBetts]

Hi and thank you for that nice compliment!

I looked at the source code of the SchemaCache class and, from what I understood, it is limited to SQL Server and SQLite. I was thinking about making modifications to support FirebirdSQL and sending you a PR, but it will end up full of ifs. Could you structure it in a more separated/decoupled way so we can add new database providers without mixing everything together, before I start making these changes?

SchemaCache has two paths currently, one is for SQLite and the other is for databases that follow the ISO/IEC 9075 standard supported by SQL Server, PostgreSQL, MySQL, MariaDB, and others. I'll add a Firebird code path in that class. In the future we may need to add an ISchemaQueryProvider so these code paths for non-standard catalogs wouldn't get out of hand.

Depending on the situation, I like using the SQL cell approach, and in other cases I prefer using Dapper. Some options would help me a lot — I’m not sure which one works best for you:
a) expose the internal connection of SQL cells;
b) allow the connection string in SQL cells to accept variables;
c) allow SQL cells to accept a preconfigured DbConnection.
Note: I was having issues with Dapper and dynamic variables, but I found out that you just need to add the Microsoft.CSharp NuGet package.

Good call on this, as of right now the connection created by #!sql-connect is being stored internally and not exposed through the variables. What I'm considering for my fixes later is to expose that using the name, for example, #!sql-connect --name db would publish a variable with the name of db and has a type of DbConnection.

This is pure nitpicking, but here it goes: in Polyglot, when we executed a cell, it was clear that it was running and when it finished, even if it was very fast. In Verso, we click Run and nothing seems to happen — sometimes I click several times just to be sure it actually executed, both in cells that return nothing and in cells that return something but didn’t visibly change anything.

Currently, there are two visual indications that determine if a cell is running. 1) When you click "Run All" the cells are executed in order and an outline is shown on each one executing. 2) When you click on a cell's run icon it will display a continuous progress in the location where the run button is. I'd assume your code must be execution pretty fast, but is there another place you'd suggest that is shown?

[alansbraga]

SchemaCache has two paths currently, one is for SQLite and the other is for databases that follow the ISO/IEC 9075 standard supported by SQL Server, PostgreSQL, MySQL, MariaDB, and others. I'll add a Firebird code path in that class. In the future we may need to add an ISchemaQueryProvider so these code paths for non-standard catalogs wouldn't get out of hand.

If you’re not going to reorganize the code right now, I can implement Firebird and create a PR for you. That way, you can focus on other things. Just let me know.

Good call on this, as of right now the connection created by #!sql-connect is being stored internally and not exposed through the variables. What I'm considering for my fixes later is to expose that using the name, for example, #!sql-connect --name db would publish a variable with the name of db and has a type of DbConnection.

I tried accessing it through the __verso_ado_connections variable, but it didn’t work very well. I was in a hurry and gave up. Exposing that variable would already help me a lot.
If it's possible to allow variables in the provider and in the connection string in the magic command, that would also help me a lot. I access some databases that share the same structure but are in different servers, and I usually build the connection strings dynamically.
Either way, just exposing the variable would already let me define the connection in a single place, which would help quite a bit.

Currently, there are two visual indications that determine if a cell is running. 1) When you click "Run All" the cells are executed in order and an outline is shown on each one executing. 2) When you click on a cell's run icon it will display a continuous progress in the location where the run button is. I'd assume your code must be execution pretty fast, but is there another place you'd suggest that is shown?

I don’t think it needs to be shown somewhere else, but as you can see in the image below, in Polyglot—even if it takes 0.0s—the visual execution effect lasts just long enough for you to be sure it actually happened. Again: it's pure nitpicking, but I've already clicked near the button instead of on the button, thought it had executed and it hadn't… lol.

Thanks again!

[TorreyBetts]

After a little more research I concluded it's not good to expose the DbConnection because Verso could run into a few problems:

• lifetime conflicts - one cell disposes it, another breaks
• thread safety - concurrent cells corrupt the state
• state mutation - one cell could change the database or start a transaction that affects other cells

The safest option was to allow for variables. The credential resolver already checked for $env: and $secret: - I ended up adding $var: support. Usage example:

// Cell 1
var connStr = config.GetConnectionString("Sales");

// Cell 2
#!sql-connect --name sales --connection-string "$var:connStr"

Edit: I forgot to add that the fixes discussed in this thread will be part of the 1.0.9 release when it gets pushed out. If you're using source, it's already in the repository.

[TorreyBetts]

All suggestions mentioned in this thread have been addressed, including the nitpick that asked for cell execution status and elapsed time. Here's an example of how that looks:

The updates are already on this repository. If anyone reading this uses the VS Code extension, these updates will appear in the upcoming 1.0.9 release.