[BUG] (Warn to) Recreate SELECT * queries if queried DE was changed (refreshQuery cmd) / CSCLSROZ-113

[JoernBerkefeld]

Background

SELECT * FROM xy queries are interally stored in an extended version with all field names of the data extension "xy". This storing happens when you save a query in automation studio --> opening and saving it in the UI does the trick. right now we discard this secondary field that stores the extended version  

Requirement

Solution Option 1

When a DE is deployed ( ?), check all existing queries if they happen to retrieve data from it AND include a "*" in it, indicating a select-all operation.

Output a list of potential hits as a multi-select list (using inquirer module like in selectTypes) and ask the user which ones we shall update. Delete followed by create should do it but maybe simply upserting it will also suffice? Needs to be tested. 

For SHARED data extensions, we would need to broaden our search for all BUs in the current credential set.

Solution Option 2

Upon retrieving queries that have a "*" in them, warn about that

Solution Option 3

Upon retrieving queries that have a "*" in them, warn about that AND process the extended version to replace * with the actual field list.

Solution Option 4 (Jörn: i'd kinda prefer this one)

Store both the normal an the extended version in the case of detecting a "*" only. This would allow us to search for field names in our folder, while not overwriting what the user did and still properly warn about it.

Also, keeping a central list of *-queries, would allow us to add a new "refreshQuery" command that goes through the current list of *-queries, automatically re-deploy them and let SFMC do its magic (re-create the internal extended version with the fields).

 

Note: Since there might be false-positives here given that we will likely not parse the SQL but instead search for the external key of the DE, the user should have the final say in what query is updated.