Invalid G-9010 reference in Appendix B

[PhilippSalvisberg]

With this commit for issue #93 a wrong G-9010 was created in Appendix B. See 42a86a1#r43256829

[kibeha]

I missed that I ran the generator awk on my local work copy, where G-9010 exists, but as yet un-committed.
I am in the process of creating section 9 - Function Usage with rules G-9010, G-9020, G-9030.

I did not know about G-9xxx being reserved. Is that documented?
In that case I will need to put function usage as a sub-section somewhere else.

I had considered sub-sectioning section 1, so it would contain 11xx-general and 12xx-function usage.
But that would require that I renumber existing rules G-10xx to G-11xx.
Would that be a problem to do so? That way I could keep G-9xxx unused?

[PhilippSalvisberg]

I did not know about G-9xxx being reserved. Is that documented?

It's an undocumented convention. It's based on the discussion during renaming the guidelines from 2-digits to 4-digits. We agreed back then not to change rule identifiers anymore and I claimed the 0nnn and 9nnn ranges. In our validator's repository we use them for our example custom validators:

• TrivadisPlsqlNaming: 9001-9015
• GLP: 9001-9003 (an alternative for TrivadisPlsqlNaming, therefore overlap is OK)
• SQLInjection: 9501
• Hint: 9600-9603

I know that some customers have written their own validator checks, but I have no overview what identifiers they have used.

In PL/SQL Cop we used the following identifiers (prefix is irrelevant since internally the identifier is a simple integer):

• G-00/G-0000: Avoid using the NOSONAR marker.
• E-01/E-0001: Timeout occurred (after n seconds) during load/parse/validation of resource.
• E-02/E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment.
• E-03/E-0003: License limit reached.

I had considered sub-sectioning section 1, so it would contain 11xx-general and 12xx-function usage.
But that would require that I renumber existing rules G-10xx to G-11xx.
Would that be a problem to do so? That way I could keep G-9xxx unused?

I understand. However, changing the number for an existing guideline leads to confusion. After some time it does not matter, but in the beginning it is a problem. In SonarQube might exist comments per issue (e.g. to document why something is a false positive). These comments are stored based on their keys. I guess this shows the potential problems when renaming guideline identifiers beside the additional (unnecessary) work to update related products.

I suggest the following for the PL/SQL & SQL Coding Guidelines

• They can use any number between 1000 and 8999.
• Once a guideline is released, the number of a rule must not change.
• If a rule number is changed (for whatever reason), it is documented (as we've done so in Appendix B, when changing the numbering scheme from V2 und V3).

The problem is that the guideline numbers have a meaning. The first number represents the main chapter (general, variables-and-types, etc.). The second number the sub-chapter (numeric-data-type, character-data-types, etc) and the complete number represents the order in the document.

Maybe we should use the guideline number as a simple identifier. This would allow to reorganise the chapters without having to rename guideline numbers. In those cases where default alphabetic name of a guideline (G-nnnn.md) does not lead to the indented sort order, a prefix could be added. Of course, this has some impact on some conventions (scripts) in this repository. The price for stable guideline identifiers and the freedom to restructure the guidelines.

[kibeha]

I agree it would be good to separate structure and guideline number. I'll make issue(s) to that effect, but I'll leave them for next milestone, not v.4.0.

For incorporating my "Function Usage" within 4.0 I have two workarounds I'm considering:

1. Rename chapter 4 to something more "generic" instead of "Control Structures" and add sub-chapter 4 (G-44xx) with my "Function Usage". Not really nice, but chapter 4 already has sub-chapters that I can add an extra one.

2. Rename chapter 1 to "Overall Language" with two (so far) sub-chapters: 0-General (G-10xx) and 1-Function Usage (G-11xx). Having a sub-chapter "zero" is only visible in the source - chapter numbers are not in the deployed site or PDF, so this would also meet my needs without needing to re-number any guidelines.

I'm leaning toward workaround 2) for version 4.0, and then do the "proper" solution in a future version.

[PhilippSalvisberg]

Something was bothering me and I had to think a bit more about it. I see the following areas which are related:

• These guidelines (this repo)
• PL/SQL Cop tool suite which implements the majority of the guidelines as validator checks
• Additional validator checks for other guidelines (maybe based on a fork of this repo or documented somewhere else)
• Results of validation runs on software artifacts (output of the PL/SQL Cop stored as HTML, Excel or in a SonarQube repository)

In all these areas we work with the guideline number as an identifier. It reminds me of error numbers. When I look at the Oracle Database Messages then I see that the messages have a prefix and a number. And for the number also some ranges are defined. I think we can apply the same principles for the guidelines.

We should add a chapter describing the principles/conventions regarding the identifier of a coding guideline. Something like this

• A guideline identifier (GID) identifies a coding guideline.
• A GID is a string of an arbitrary length.
• A GID defined in the Trivadis PL/SQL & SQL Coding Guidelines starts with a G- prefix.
• It's possible to change the GID for a coding guideline as long as the newly assigned GID was never used for another guideline (this is a very important convention IMO)
• New, changed an removed GID are documented in the appendix with the the related version (could replace Appendix B)
• We strongly recommend that guidelines defined outside of the Trivadis PL/SQL & SQL Coding Guidelines use other prefixes than G- to avoid conflicts.

The current PL/SQL Cop toolchain cannot deal with an alphanumeric GID of an arbitrary length. However, it should be relatively easy to change that. As a result we can name our example validators with a custom prefix, e.g. C-.

I plan to do the following anyway:

• For PL/SQL Cop CLI v3 (to be released in 2020, based on JDK8 with grammar and validator fixes) we use the coding guidelines version 3.6 as the base. We will also support the old V2 guidelines as a dedicated validator
• For PL/SQL Cop CLI v4 (to be released in early 2021, based on JDK11 with 19c/20c grammar) we will use the most current version 4 of the coding guidelines as base. There will be no dedicated validators for v2 or v3. The custom validators will be changed accordingly.

Therefore you can proceed with your original plan and use G-9010, G-9020, etc.

BTW, the .pages file is used in the directories to override the default name of a chapter in the HTML site and PDF. Therefore sorting directories and subdirectories is not a problem at all.