Preserve Comments in SQL Query
I would like to follow up this post regarding preserving comments while editing a query in the QueryEditor. There it is stated that this will be implemented in the next major release. Is this still valid?
Replies 11
Hello, Christian.
I must admit that we haven't done this feature so far. I am terribly sorry for the inconvenience this cause everybody.
The major SQL parser update is delayed. Hopefully, we will get back to this feature in the second half of this year, but I can't promise you this.
It is possible to add a custom column to the QueryColumnListControl, there's a special demo project that illustrates this feature. For now it is not possible to save comments in layout XML, but we'll think about the best way to achieve this.
It's not a problem of finding the place in UI to show comments. The main part of this task is to parse and then insert comments in result SQL query text. This task will need the time that we can't afford to spend now.
We have another approach that we assume is less invasive and easier to implement for you:
Add a "Comment" text-column in the GridView for editing the (sub-)query columns. Store the value of this comment in the LayoutXml.
That could be an intermediate step that would already help us a lot.
Next step could be to parse/render this from/to the final SQL as well, as discussed above.
What do you think?
Thank you for sharing your thoughts. We will think once again about the possiblity to implement this feature.
Oracle hints are preserved right now.
In Oracle, optimizer hints are realized as comments. You may take a look at this documentation. If I didn't miss anything, then it would only require some extensions to the existing SQL parser and no rework. The context of optimizer hints (i.e. to which item of the query they belong) is clearly specified.
At least this special kind of comment is indispensable for us. They reduce execution time by orders of magnitudes.
However, the specification shows a pattern about how to assign comments to an item in the SQL statement. From that pattern you could derive a general approach to assign comments to a part of a SQL statement on a best-effort basis.
Finally, even if you at least collect all comments at one place and render them at the beginning/end of the actual statement would already help us. Everything would be better then just forgetting them. :)
Here is a simple SQL using hints for my comments and they "stick".
WITH c1 AS (SELECT /*+ -- CTE comments */ To_Char(:ENVIRO_NAME) AS ENVIRO_NAME,
To_Char(SYSDATE, 'YYYY-MM-DD') AS RUN_DATE
FROM DUAL)
SELECT /*+ -- MAIN comments */ c1.ENVIRO_NAME,
c1.RUN_DATE,
d0.CURR_DT
FROM (SELECT /*+ -- DERIVED comments */ SYSDATE AS CURR_DT
FROM c1) d0,
c1