oracle 在自动构建环境中进行 PL/SQL 预编译和代码质量检查？

PL/SQL pre-compile and Code Quality checks in an automated build environment?

提问by Lars Corneliussen

We build software using Hudson and Maven. We have C#, java and last, but not least PL/SQL sources (sprocs, packages, DDL, crud)

我们使用 Hudson 和 Maven 构建软件。我们有 C#、java 和 last，但并非最不重要的 PL/SQL 源代码（sprocs、包、DDL、crud）

For C# and Java we do unit tests and code analysis, but we don't really know the health of our PL/SQL sources before we actually publish them to the target database.

对于 C# 和 Java，我们进行单元测试和代码分析，但在将 PL/SQL 源实际发布到目标数据库之前，我们并不真正了解它们的健康状况。

Requirements

要求

There are a couple of things we wan't to test in the following priority:

我们不想在以下优先级中测试一些事情：

1. Are the sources valid, hence "compilable"?
2. For packages, with respect to a certain database, would they compile?
3. Code Quality: Do we have code flaws like duplicates, too complex methods or other violations to a defined set of rules?

1. 来源是否有效，因此“可编译”？
2. 对于包，相对于某个数据库，它们会编译吗？
3. 代码质量：我们是否存在代码缺陷，如重复、过于复杂的方法或其他违反一组定义规则的行为？

Also,

还，

• the tool must run head-less (commandline, ant, ...)
• we want to do analysis on a partial code base (changed sources only)

• 该工具必须无头运行（命令行、蚂蚁等）
• 我们想对部分代码库进行分析（仅更改源代码）

Tools

工具

We did a little research and found the following tools that could potencially help:

我们做了一些研究，发现以下工具可能会有所帮助：

• Cast Application Intelligence Platform (AIP):Seems to be a server that grasps information about "anything". Couldn't find a console version that would export in readable format.
• Toad for Oracle:The Professional version is said to include something called Xpert validates a set of rules against a code base.
• Sonar+ PL/SQL-Plugin:Uses Toad for Oracle to display code-health the sonar-way. This is for browsing the current state of the code base.
• Semantic Designs DMSToolkit:Quite general analysis of source code base. Commandline available?
• Semantic Designs Clones Detector:Detects clones. But also via command line?
• Fortify Source Code Analyzer:Seems to be focussed on security issues. But maybe it is extensible? more...

• Cast Application Intelligence Platform（AIP）：似乎是一个掌握“任何”信息的服务器。找不到可以以可读格式导出的控制台版本。
• Toad for Oracle：据说专业版包括一种叫做 Xpert 的东西，它根据代码库验证一组规则。
• Sonar+ PL/SQL-Plugin：使用 Toad for Oracle 以声纳方式显示代码健康状况。这是用于浏览代码库的当前状态。
• Semantic Designs DMSToolkit：对源代码库的相当一般的分析。命令行可用？
• 语义设计克隆检测器：检测克隆。但也通过命令行？
• Fortify Source Code Analyzer：似乎专注于安全问题。但也许它是可扩展的？更多的...

So far, Toad for Oracle together with Sonar seems to be an elegant solution. But may be we are missing something here?

到目前为止，Toad for Oracle 与 Sonar 一起似乎是一个优雅的解决方案。但也许我们在这里遗漏了什么？

Any ideas? Other products? Experiences?

有任何想法吗？其他产品？经验？

Related Questions on SO:

SO的相关问题：

• Any Static Code Analysis Tools for Stored Procedures?
• https://stackoverflow.com/questions/839707/any-code-quality-tool-for-pl-sql
• Is there a static analysis tool for Python, Ruby, Sql, Cobol, Perl, and PL/SQL?

• 任何用于存储过程的静态代码分析工具？
• https://stackoverflow.com/questions/839707/any-code-quality-tool-for-pl-sql
• 是否有针对 Python、Ruby、Sql、Cobol、Perl 和 PL/SQL 的静态分析工具？

采纳答案by Hanno

I think that this blog describes the needed process:

我认为这个博客描述了所需的过程：

http://www.theserverlabs.com/blog/?p=435

http://www.theserverlabs.com/blog/?p=435

Please check and let me know what you think about it.

请检查并告诉我您对此的看法。

回答by ninesided

Our approach is to keep each database object (tables, views, functions, packages, sprocs etc) in its own file under source control and have an integration server (TeamCity, Hudsonetc) do a nightly build of the database - from source - where it drops and recreates the schema before checking for compilation errors in the user_errorssystem table. This lets you know when someone has introduced compilation errors into the build.

我们的方法是在源代码控制下将每个数据库对象（表、视图、函数、包、sprocs 等）保存在自己的文件中，并让集成服务器（TeamCity、Hudson等）每晚构建数据库 - 从源代码 - 在哪里在检查user_errors系统表中的编译错误之前，它会删除并重新创建模式。这让您知道何时有人在构建中引入了编译错误。

The next step is to use something like PLUTOto add unit tests to your PL/SQL code and add those into the nightly build task. For us, this has involved having sample test datasets (also under source control) that allow us to get the database to a "known state" for the purposes of testing.

下一步是使用类似PLUTO 的东西将单元测试添加到您的 PL/SQL 代码中，并将它们添加到夜间构建任务中。对我们来说，这涉及拥有样本测试数据集（也在源代码控制下），允许我们将数据库置于“已知状态”以进行测试。

I've not found anything that helps us much with any of the above so it's mainly a collection of Ant tasks, custom shell scripts and wizardry, which basically apply the required DDL to an empty database and use DBMS_UTILITY.COMPILE_SCHEMA()to, uh, compile the schema. You can add more fancy stuff later, like back-tracing objects which fail to compile or fail tests to a specific submitin source control, and issue "blame mail".

我没有找到任何对上述任何一项有很大帮助的东西，因此它主要是 Ant 任务、自定义 shell 脚本和向导的集合，它们基本上将所需的 DDL 应用于空数据库并用于DBMS_UTILITY.COMPILE_SCHEMA()，呃，编译模式。您可以稍后添加更多花哨的东西，例如回溯对象，这些对象无法编译或无法通过源代码管理中的特定提交进行测试，并发出“责备邮件”。

I'd be really interested to see if anyone else has a better approach or if there's an off-the-shelf product that does this for me!

我真的很想知道是否有人有更好的方法，或者是否有现成的产品可以为我做到这一点！

回答by Ira Baxter

Our DMS Software Reengineering Toolkitis the foundation for arbitrary customizable tools. It has a PL/SQL front end that can be used to build arbitrary source code quality checks. Yes, it has a command-line version.

我们的DMS 软件再造工具包是任意可定制工具的基础。它有一个 PL/SQL 前端，可用于构建任意源代码质量检查。是的，它有一个命令行版本。

There are a variety of PL/SQL COTS toolsbased on DMS that could be used to check quality:

有多种基于 DMS的PL/SQL COTS 工具可用于检查质量：

• Formatter - Cleans up layout. Side effect: static check for legal PL/SQL syntax
• Source Code Search Engine - enables fast search of indexed source code base. Computes Halstead and Cyclomatic metrics as a side effect of setting up the index.
• CloneDR - finds and reports duplicated PL/SQL code
• Test Coverage - determines part of PL/SQL code not executed by tests (ad hoc, unit, or functional tests)

• 格式化程序 - 清理布局。副作用：静态检查合法的 PL/SQL 语法
• 源代码搜索引擎 - 支持快速搜索索引源代码库。计算 Halstead 和 Cyclomatic 指标作为设置索引的副作用。
• CloneDR - 查找并报告重复的 PL/SQL 代码
• 测试覆盖率 - 确定未由测试（即席、单元或功能测试）执行的 PL/SQL 代码部分

All these have command line versions.

所有这些都有命令行版本。