Important Announcement
This forum will be discontinued.
The creation of new topics is disabled.
Please open GitHub issues for the corresponding topics/products in the following Trivadis repositories:
- PL/SQL & SQL Coding Guidelines
- PL/SQL Cop Command Line
- PL/SQL Cop for SonarQube
- PL/SQL Cop for SQL Developer
- PL/SQL Cop Validators
- PL/SQL Analyzer
- PL/SQL Unwrapper
A lot of users have already a GitHub account and the management of the issues is better than in this forum. For example, closing a forum topic is never related to a product change. Closing a GitHub issue for an accepted bug means that the bug is fixed. This should simplify the work for all involved parties.
See this blog post for more information.
Thank you for your understanding.
The naming conventions for PL/SQL described in your coding guidelines are more or less the ones I have been using for many years. Though, some recent reflexion brought me to the conclusion that they are not as perfect as they seem to be.
According to me, the idea behind these conventions is to allow to guess the following characteristics, just by looking at an identifier:
- is it a user-defined type or an instance (of a type)?
- its scope (global, local, parameter)
- its type (scalar variable, record, cursor, exception, table/array, object)
- its modifiability i.e. read/write/r-w (constant vs variable; in, out, in/out parameter)
The first characteristic is implemented via the "_type" suffix and the last three via the prefix.
The problem with your proposed naming conventions comes from the fact that you cannot combine the last 3 characteristics when using a single symbol prefix. To illustrate the problematic, how do you prefix the following:
- A global constant: "g_" or "co_"?
- A local exception: "l_" or "e_"?
- A record passed as an input parameter: "r_" or "in_"?
- A constant array (yes, it's possible): "t_" or "co_"?
The only way to solve this problem is to allow prefixes made up of several letters, each one describing one of the above-mentioned characteristic.
Here follow my naming conventions based on this principle.
The suffix is used to make a distinction between user-defined types and type instances:
|
Type vs Instance |
Suffix |
|
User-defined type |
"_type" |
|
Instance |
(none) |
The prefix is made-up of a series of letters indicating the following properties (in that specific order):
- Scope (mandatory) depending on where is the identifier declared
- Modifiability (optional, "variable" by default)
- Type (optional, "scalar" by default)
Allowed values for the "Scope" property are:
|
Scope |
Letter |
|
Global |
"g" |
|
Local |
"l" |
|
Parameter |
"p" |
Allowed values for the "Modifiability" property are:
|
Modifiability |
Letter(s) |
|
Variable (can be changed at any time = r/w) |
"v" (default => omitted) |
|
Constant (cannot be changed = read-only) |
"k" |
|
Input (read-only parameter) |
"i" (default => omitted) |
|
Output (write-only parameter) |
"o" |
|
Input Output (read-write parameter) |
"io" |
Notes:
- The last 3 values apply to parameters only;
- "k" is used for constants instead of "co" to keep a single letter;
- This "modifiability" property does not apply to "Type" declarations.
Allowed values for the "Type" property are:
|
Type |
Letter |
|
Scalar |
"s" (default => omitted) |
|
Cursor |
"c" |
|
Record |
"r" |
|
Exception |
"e" |
|
Table/Array |
"t" |
|
Object |
"o" |
Note: scalar means single value with no internal components like tables and records.
Here follow some examples of valid combinations (non-exhaustive list):
|
Example |
Prefix |
|
Local (Scalar) Variable |
"l_" ("v" and "s" are omitted) |
|
Global (Scalar) Variable |
"g_" ("v" and "s" are omitted) |
|
Local Cursor (Variable) |
"lc_" ("v" is omitted) |
|
Local Record (Variable) |
"lr_" ("v" is omitted) |
|
Global Array (Variable) |
"ga_" ("v" is omitted) |
|
Global Constant (Scalar) |
"gk_" ("s" is omitted) |
|
Global Constant Array |
"gka_" |
|
(Input) (Scalar) Parameter |
"p_" ("i" and "s" are omitted) |
|
(Input) Record Parameter |
"pr_" ("i" is omitted) |
|
(Input) Array Parameter |
"pa_" ("i" is omitted) |
|
Output (Scalar) Parameter |
"po_" ("s" is omitted) |
|
Output Table Parameter |
"pot_" |
You will notice that:
- Default "v", "s" and "i" letters have been omitted on purpose;
- Constant can apply to any data item (e.g. scalar but also record, array, etc.);
- Not all combinations are valid (e.g. "pt" and "pe" are not valid in PL/SQL);
- The conventions for scalar variables and parameters match the traditional simple conventions ("g_" for global, "l_" for local, "p_" for parameters).
The prefix can also be used for types:
|
Type example |
Prefix |
Suffix |
|
Global record type |
"gr_" |
"_type" |
|
Local table type |
"lt_" |
"_type" |
My question is: do you think a Cop validator could be built to support such multi-characteristics / multi-letters naming conventions?
Thanks,
Philippe
(db/data expert)
Hi Philippe,
My question is: do you think a Cop validator could be built to support such multi-characteristics / multi-letters naming conventions?
In principle, yes.
The scope of a validator is a single file. So for certain types you do not have all information. E.g. when an object/collection type is defined in a dedicated file. Hence, you cannot check it.
There are also other things, such as the modifiability. No way to check that in a package specification when the body is stored in a dedicated file. Even if they are stored in the same file you have to analyze the code. You might covers some cases, but certainly not all.
However, if you extend the scope of a validator, e.g. by accessing the data dictionary to query schemas that are compiled with PL/Scope, then you should be able to cover much more cases.
Regards,
Philipp