Page tree
Skip to end of metadata
Go to start of metadata

This analyzer is the official successor to the "SQL Script" extension. If you have previously used "SQL Script" extension, see the special note.

Target audience:

Users of the extension providing source code analysis support for SQL files.

Summary: This document provides basic information about the extension that provides source code analysis support for SQL files.

What's new:

  • Added 11 New Quality Rules extending CISQ coverage
  • Minor bug fixes

Description

The new SQL Analyzer (successor to the "SQL Script" extension) provides support for database technologies using ANSI SQL-92/99 language. This extension uses the Universal Analyzer framework and is intended to analyse DDL, DML and SQL exports for a large variety of SQL variants:

  • This extension provides source code analysis support for DDL and DML *.sql files using an over language of the various sql variants.
  • This extension also accepts src and uaxDirectory files. Check here for more details about sqltablesize files. 

In what situation should you install this extension?

  • If you need to analyze PostgreSQL, MySQL, MariaDB and SQLite
  • If your application contains schemas from database vendors not supported "out of the box" by CAST AIP (see http://doc.castsoftware.com, Supported Technologies for more information) but, which are compliant with ANSI SQL-92/99
  • When you do not have access to the online dabatase to perform an extraction for use with CAST AIP and have instead been provided with DDL scripts

Vendor compatibility matrix

RDBMS VendorOfficial SupportCompatibleNotes
(tick)(tick) 
(tick)(tick) 
(tick)(tick) 
(tick)(tick) 
(error)(tick)CAST AIP provides "out of the box" analyzers for all these vendors.
(error)(tick)
(error)(tick)
(error)(tick)

Function Point, Quality and Sizing support

This extension provides the following support:

  • Function Points (transactions): a green tick indicates that OMG Function Point counting and Transaction Risk Index are supported
  • Quality and Sizing: a green tick indicates that CAST can measure size and that a minimum set of Quality Rules exist
Function Points
(transactions)
Quality and Sizing
(tick)(tick)

CAST AIP compatibility

This extension is compatible with:

CAST AIP release
Supported
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
7.3.x(error)

Supported DBMS servers used for CAST AIP schemas

This extension is compatible with the following DBMS servers used to host CAST AIP schemas

CAST AIP releaseCSS2OracleMicrosoft
All supported releases(tick)(tick)(tick)

Prerequisites

(tick)An installation of any compatible release of CAST AIP (see table above)

Download and installation instructions

Please see:

The latest release status of this extension can be seen when downloading it from the CAST Extend server.

Upgrade from the SQL Script extension

If you have previously used the "SQL Script" extension (com.castsoftware.sqlscript) on existing schemas, you should proceed as following :

  • In CAST Server Manager use the Manage Extensions option on the CAST AIP schemas in which the "SQL Script" extension is installed
    • Select Analyzer for SQL files and choose deactivate to remove the existing extension. No further actions are required.

Packaging, delivering and analyzing your source code

Please see: SQL Analyzer - 1.x - Packaging, delivering and analyzing your source code

What results can you expect?

Once the analysis/snapshot generation has completed, you can view the results in the normal manner (for example via CAST Enlighten):

You can also use the CAST Management Studio option View Analysis Unit Content to see the objects that have been created following the analysis:

Objects

The following objects are displayed in CAST Enlighten:

IconDescription
Schema
Table
View
Table Column
Index

Foreign Key

Unique Constraint
Procedure
Function
Trigger
Package 

Type

Event

Synonym
SQL Script
DLM Script

Note that:

  • Object identity is independent from the *.sql file the object comes from.
  • Object identity depends on the Analysis Unit's identity. Therefore, using a new Analysis Unit or deleting and then recreating an Analysis Unit will change the object's identity and will result in added/removed objects in the subsequent analysis results.
  • Typically a table will be identified by the Analysis Unit name, schema name and table name.
  • When no schema can be determined, the analyzer considers that a schema named "DEFAULT" is used. But generally, identifiers are qualified in CREATE TABLE statements.

Table deletion and renaming

DROP TABLE syntax is supported for table objects within the same file. When creating a table through CREATE TABLE tableName (colName int, ...) followed by a DROP TABLE tableName, the table will not be recorded and thus will not be displayed in CAST Enlighten. Similarly, if a table is renamed with a RENAME TABLE statement (or ALTER TABLE RENAME TO as in SQLite and PostgreSQL), this change will be reflected in CAST Enlighten. Presently we consider case-insensitive names, i.e., objects named tableName and TABLEname are considered to be the same object.

Links are created for transaction and function point needs.

DDL

You can expect the following links on the DDL side within the same sql file:

  • useSelect, useInsert, useUpdate, useDelete Links from Procedure / FunctionEvent  to Table / View
  • callLink from Procedure / Function / Event  to Procedure / Function
  • useSelect from View to Table / View used in the query of the view
  • callLink from View to Function
  • relyonLink from Index to the Table
  • relyonLink from Index to the Column implied in the index
  • referLink from:
    • Table / Table Column to a Table / Table Column referenced in a Foreign Key
    • Synonym to Table / View / Function / Procedure / Package aliased by Synonym
  • callLink to the correct Trigger where the tables is accessed in insert/update/delete
    • example a Trigger declared as BEFORE INSERT on a table, any insert to that table will call the trigger...

DML

You can expect the following links on the DML side :

  • Links from SQL Script to Table provided as dependencies
  • Links from client code to Table provided as dependencies

Quality Rules

 IDNameCritical?Client Side Support
 11634Avoid unreferenced Tables  
 27130Avoid Artifacts with High Depth of Nested Subqueries  
 37344Avoid "SELECT *" queries  
 47346Avoid redundant indexes  
 57348Avoid too many Indexes on one Table  
 67388Avoid artifacts having recursive calls  
 77390Avoid having multiple Artifacts inserting data on the same SQL Table  
 87394Avoid having multiple Artifacts updating data on the same SQL Table  
 97392Avoid having multiple artifacts deleting data on the same SQL table  
107404Avoid unreferenced views  
117424Avoid using SQL queries inside a loop(tick) 
127436 Prefer UNION ALL to UNION  
137760Avoid triggers, functions and procedures with a very low comment/code ratio NEW  
147762Avoid undocumented triggers, functions and procedures  
157766Avoid Artifacts with High Cyclomatic Complexity NEW  
167768Avoid Artifacts with High Depth of Code NEW  
177772Avoid Artifacts with High Essential Complexity NEW  
187774Avoid Artifacts with High Integration Complexity NEW  
197776Avoid Artifacts with High Fan-In  
207778Avoid Artifacts with High Fan-Out  
217784Avoid Artifacts with lines longer than X characters  
227808Avoid Artifacts with SQL statement including subqueries  
237814Avoid Tables not using referential integrity  
247816Avoid using GOTO statement  
257828Avoid Artifacts with High RAW SQL Complexity NEW  
267842Avoid large Artifacts - too many Lines of Code  
277856Avoid Tables with more than 20 columns on an OLTP system  
287860Avoid unreferenced Functions  
291101000Never use SQL queries with a cartesian product 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

301101002Never use SQL queries with a cartesian product on XXL Tables(tick)

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

311101004Avoid non-indexed SQL queries 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

321101006Avoid non-indexed XXL SQL queries(tick)

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

331101008Avoid non-SARGable queries 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

341101010Avoid NATURAL JOIN queries 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

351101012Specify column names instead of column numbers in ORDER BY clauses 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

361101014Avoid queries using old style join convention instead of ANSI-Standard joins 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

371101016Avoid Artifacts with too many parameters  
381101018Avoid using the GROUP BY clause  
391101020Avoid using quoted identifiers  
401101022Avoid Tables without Primary Key(tick) 
411101024Avoid using dynamic SQL in SQL Artifacts  
421101026 Always define column names when inserting values 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

431101028Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

441101030Avoid Artifacts with queries on too many Tables and/or Views  
451101032Avoid exists and not exists independent clauses NEW 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

461101034DISTINCT should not be used in SQL SELECT statements NEW 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

471101036Use ANSI standard operators in SQL WHERE clauses NEW (CLIENT ONLY) 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

481101038Replace OR conditions testing equality on the same identifier in SQL WHERE clauses by an IN test condition NEW 

COBOL PB VB .NET JAVA C/C++/OBJC IOS PYTHON

491101040Avoid empty catch blocks NEW(tick) 

Special Note about XXL/XXS support

See SQL Analyzer - working with XXL or XXS tables for a DB2 database for more information.

Special notes about Quality Rules on client side

Some Quality Rules are calculated on SQL queries on client side with some limitations:

  • Only Java queries found by inference engine are analysed, for example :
    • Queries that generate dynamic links (eligible to DLM) are not analysed
  • Queries to Hibernate or JPA objects or any other ORM are not analysed
  • COBOL EXEC SQL queries are analysed
  • SQL queries founded in python code

Limitations

  • General :
    • All name resolving is considered as case insensitive :
      •   may produce wrong links on case insensitive platform 'playing with case' :
        •     2 different tables with the same case insensitive name will be both called
    • Procedure resolution does not handle overriding : 
      • when calling an overridden procedure, all overrides will be called
    • ALTER TABLE ... ADD ... syntax is supported. All other syntaxes, like ALTER TABLE ... DELETE .. or ALTER TABLE ... DROP ... or ALTER TABLE ... MODIFY ... etc. are not supported.

    • Moving a table from one database/scheme to another is not supported through RENAME TABLE schema1.tableName1 TO schema2.tableName2.   
    • Sequences are not taken in account and that is not a limitation but a choice because they have no effect on transactions nor quality rules*
    • Oracle synonyms on packages are not taken in account.
    • For the QR 7156 Avoid Too Many Copy Pasted Artifacts, total values are displayed but no detail values.
    • For the QR 1101012 Specify column names instead of column numbers in ORDER BY clauses, the case when a function that returns a number or a numeric variable is used in order by is not reported to violate the rule.
  • Specific to Microsoft and Sybase
    • when the body of a stored procedure/function is not defined in a begin ... end block, the analysis result is not guaranteed, e.g. :
OK - With a BEGIN .. END block
CREATE PROCEDURE ....
BEGIN
  // violates the cartesian product rule, but not the XXL cartesian product rule because tables are below threshold
  SELECT * FROM SMALL_TABLE1, SMALL_TABLE2;

  ...

  // violates both XXL and non XXL avoid cartesian product rules
  SELECT * FROM HUGE_TABLE1, HUGE_TABLE2;

END
go
NOK - Without BEGIN ... END block
CREATE PROCEDURE ....

  // violates the cartesian product rule, but not the XXL cartesian product rule because tables are below threshold
  SELECT * FROM SMALL_TABLE1, SMALL_TABLE2;

  ...

  // violates both XXL and non XXL avoid cartesian product rules
  SELECT * FROM HUGE_TABLE1, HUGE_TABLE2;

go


 

  • No labels

1 Comment

  1. Anonymous

    Hi,

    Does this SQL extension supports Apache Derby database?

    The DERBY documentation https://db.apache.org/derby/ says that “Derby is based on the Java, JDBC, and SQL standards.”

    So I am guessing that there is a good chance that this SQL script analyzer supports Derby.

    Regards,