Need a Place to Run Queries Against Environments - LAB?

[CathyC]

In EASYProcess 4.8, there was a RunSQL screen which allowed queries to be ran against the environment. I get why this is a security issue in PD, but was really useful for development in just DV/QA.

In 5.1 EP, we have made methods called DNU so there is no confusion that they are not part of the application (no real good way to tell this otherwise).
Then we promote and demote to DV and QA to get it to run. The result looks like this:

AW Test Project.PNG (38.41 KiB) Viewed 748 times

AW Test Project.PNG (38.41 KiB) Viewed 748 times

The problem is that this is time consuming to promote/demote. I had a scenario where the data in DV is not useful and I have to test against QA.
Doing this meant I kept getting logged out of DV because I havent been in DV for a while, but then in making the changes I go back to QA and by then I am logged out there too.

So the next solution I had was having a RunQuery which takes the input "QueryToRun" and runs it. It feels like a workaround though

Neither of the above solutions seem intuitive. I would think LAB is where I would be able to run things and use as a scratch paper. That would also remove the need for methods named "CathyTest" and "DoNotUse". The last time I used LAB though it would not work to run the same method imported from EASYCommerce because it has references to app db table paths which point to Lab in that context.
I dont remember if I wouldve been able to just update all those references to EASYCommerce_Data table path and have that work, but it was a large enough obstacle that I just kept working in the application in "DNU" methods.

Can LAB have a feature to work as if it were running within a certain application?
Does LAB work to run methods in QA environment? and if not, can it/should it?
I think this needs some discussion on what the feature should be

[CathyC]

I forgot to add that a drawback of working in a DNU method is that I didnt realize the queries were limited to return only 50 records, so I spent time trying to figure out why my results were not as expected. I was running in step and checking NoOfResults and scrolled through and thought, "yeah that looks like a lot".

I had someone help so instead of specifying 50 results to return, to select -1 which makes it return all. Is that a recommended value to explain that? might not be intuitive

[JefferyD]

I feel that the LAB environment is a little more volatile and isolated than I'd like for a feature like this. LAB is intended to be a place to experiment without risk of messing with anything in the existing environments and even has a feature to wipe everything and start from scratch. I believe it's even user specific, so each user has their own dedicated LAB environment.

Instead, I would like to suggest a new TOOLS environment to adapt these ideas to. It could be made to be only accessible to specific user roles and allow interactivity with DV/QA/PD environments.

Some suggested features

• Ability to copy source directly from DV/QA/PD (if checked out, then this can allow testing sources before/after changes)
• Ability to edit copy of sources outside of SCM (like LAB)
• Ability to run source through DV/QA/PD connections (to replace having to hardcode tablepaths)
• Ability to checkout source in DV and import changes from version in TOOLS (i.e. change made in TOOLS resolves issue and it's desirable to export and then import it into DV)
• Allow custom tools to be created without risk of access via DV/QA/PD (if custom tool is made via widgets, then the developer needs to make sure to lock it down if in DV/QA/PD)
• Advanced IDE dev features that shouldn't be in DV/QA/PD but are acceptable if locked down separately (like perhaps having a RunSQL feature that's too powerful to have in DV/QA/PD)

[SumanPrasad]

Here is another Idea. How about we have a read only SQL user and use that user to build a query builder just like inquire service. You can write your query, join in app, join across apps in tenant. And we only use read only user behind the scene for this UI. So even if you try to write any kind of drop table statement, delete query, it will (should) not work.
Jeff, do you think this will have any security risk?

[JefferyD]

I believe the security risk is equal to being able to write custom queries in a RunQuery service. It might seem like a potential security concern, but I don't think there's anything in my suggestions that we cannot already do as developers just with more steps.

The benefits I see with a dedicated environment

• Keeps risky code out of DV/QA/PD where the security is only as tight as the developer makes it.
• Simplifies situations where developers are not blocked but are greatly slowed by having to navigate around obstacles to complete a task (like Cathy's example).
• Promotes keeping all of the riskier code together where I believe we can more easily lock it down and prevent accidental or malicious execution or access from unintended 3rd parties.
• Opens up a lot of potential for developers to add custom developer tools to the IDE (maybe even add to the gallery).

[JustinVanRegenmorter]

Task 11370 has been created to track this discussion.