Trivadis PL/SQL & SQL Coding Guidelines Version 3.1

The latest version 3.1 of the Trivadis PL/SQL & SQL Coding Guidelines has 150 pages. More than 100 additional pages compared to version 2.0 . Roger Troller did a tremendous job in updating and extending an already comprehensive document while making it simpler to read and easier understand. In this post I will emphasise some changes I consider relevant.

New Guideline Categorisation Scheme

In version 2.0 coding guidelines are categorised by icons for information, caution, performance relevance, maintainability and readability. A guideline is associated exactly with one icon. Here’s an example:

In version 3.1 the characteristics for changeability, efficiency, maintainability, portability, reliability, reusability, security and testability as defined by the Software Quality Assessment based on Lifecycle Expectations (SQALE) methodology are used to categorise guidelines. A guideline is associated with one or more SQALE characteristics. Additionally a guideline is assigned to a severity (blocker, critical, major, minor, info). So guidelines are categorised in two dimensions: SQALE characteristics and severity. These categorisations are used to filter guidelines in SonarQube or PL/SQL Cop to be enabled or disabled. It’s not by chance that SonarQube is using exactly these categorisations.

Here’s the same example as above using this new guideline categorisation scheme:

In this excerpt you see other changes as well. The reference to the CodeXpert rule is gone, the guideline 12 got a new identifier 2150 and an there is a good and bad example.

Good and Bad Examples for Every Guideline

In version 2.0 some guidelines had no examples, some just an excerpt of an example, some just a good and some just a bad example. Now in version 3.1 almost every guideline has a complete bad and a complete good example. With complete I mean that they are executable in SQL*Plus, SQLcl or within your IDE of choice. Why “almost”? For example, there is this guideline 65/7210 which says “Try to keep your packages small. Include only few procedures and functions that are used in the same context”. So, in some cases it is just not feasible/helpful to include a complete example.

For me as the guy who is responsible to write rules to check the compliance of the guidelines, good and bad examples are essentials for unit testing. Such examples also help the developer to understand guidelines. That’s why we include these examples in PL/SQL Cop.

New Guidelines

Beside some changes in categorisation and presentation of the guidelines, there are some new guidelines which I’d like to mention here:

IDGuidelineSeveritySQALE Characteristics
2230Try to use SIMPLE_INTEGER datatype when appropriate.MinorEfficiency
3150Try to use identity columns for surrogate keys.MinorMaintainability, Reliability
3160Avoid virtual columns to be visible.MajorMaintainability, Reliability
3170Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.MajorReliability
3180Always specify column names instead of positional references in ORDER BY clauses.MajorChangeability, Reliability
3190Avoid using NATURAL JOIN.MajorChangeability, Reliability
5010Try to use a error/logging framework for your application.CriticalReliability, Reusability, Testability
7460Try to define your packaged/standalone function to be deterministic if appropriate.MajorEfficiency
7810Do not use SQL inside PL/SQL to read sequence numbers (or SYSDATE)MajorEfficiency, Maintainability
8120Never check existence of a row to decide whether to create it or not.MajorEfficiency, Reliability
8310Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.MinorMaintainability, Reliability, Reusability, Testability
8410Always use application locks to ensure a program unit only running once at a given time.MinorEfficiency, Reliability
8510Always use dbms_application_info to track programm process transientlyMinorEfficiency, Reliability

Deprecated Guidelines

The guideline 54 “Avoid use of EXCEPTION_INIT pragma for a -20,NNN error” is not part of the document anymore.

New Guideline Identifiers

All guidelines got a new identifier. The first digit identifies the chapter of the document, e.g. “1” for “4.1 General”, “2” for “4.2 Variables & Types”, etc. The second digits is reserved for the sub-chapters and the remaining digits are for just ordering purposes. The gaps in the numbering scheme should allow to add future guidelines at the right place without renumbering everything (again).

There is an appendix to map old guideline identifiers to new ones. This should simplify the change to version 3.1 . Here’s an excerpt:

Tool Support

PL/SQL Cop is mentioned in the guidelines. However, currently only the Trivadis PL/SQL & SQL Guidelines Version 2.0 are supported. But sometime in Q4 of 2016 an update supporting version 3.1 should be available.

Download

Get your copy of the Trivadis PL/SQL & SQL Guidelines Version 3.1 from here .

稿源:Philipp Salvisberg's Blog (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Trivadis PL/SQL & SQL Coding Guidelines Version 3.1

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录
切换注册

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录
切换登录

注册