"The Repository's Database" SQL Connection Consistently Available

In the SQL Script Execution SAT there is a default SQL connection called "The Repository's Database", which - as the name clearly says - provides a connection the repository's database. This is a very handy feature, because it make the SQL connection environment independent, meaning that you do not need to change the SQL connection configuration when you move the repository to a different environment. Unfortunately, this is the only place where this feature is available.

When using the Database Export and Database Import SAT, you need to use an SQL connection defined in Manage Connections. These connections are environment dependent, thus needs to be change when you move the repository to a different environment. Even when we have static environments such as dev, test, and prod, after each merge you need to remember the configure the SQL connections correctly. It would be very useful to have a standard "The Repository's Database" SQL connection for these SATs as well. Thus making "The Repository's Database" SQL connection consistently available.

Here a couple of use cases:

From a button, I need to create multiple elements. Based on 2-3 input element lists, I need to create an element for each permutation of the input, with a relationship to each type of input element. First I create an SQL Script Execution SAT that take the 2-3 input element lists as input, produce the cartesian product for these lists (this is something SQL is very efficient at), and then store the result in a database table. Secondly I create a Database Import SAT that imports from the same database table.

Daily, I need to update field values on a list of elements, where MooDs aggregation facts fall short, or where it is just more efficient to use SQL. To do this, I setup a scheduled SAT group. The first SATs are Database Export and SQL Execution SATs that export the list of elements to be updated and any other needed elements or information. Then there may be an SQL Execution SAT that calculates a result. Then there is a Database Import SAT to import the result.

Common for these two use cases is that I use a combination of SQL Execution, Database Import, and Database Export SAT that use the same database tables, but use different SQl connections.

Regards,

Peter