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 Vendor	Official Support	Compatible	Notes
			CAST AIP provides "out of the box" analyzers for all these vendors.

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

CAST AIP compatibility

This extension is compatible with:

CAST AIP release	Supported
8.2.x
8.1.x
8.0.x
7.3.x

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 release	CSS2	Oracle	Microsoft
All supported releases

Prerequisites

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

Download and installation instructions

Please see:

• http://doc.castsoftware.com/display/EXTEND/Download+an+extension
• http://doc.castsoftware.com/display/EXTEND/Install+an+extension

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:

Icon	Description
	Schema
	Table
	View
	Table Column
	Index
	Foreign Key
	Unique Constraint
	Procedure
	Function
	Trigger
	Package
	Type
	Event
	Synonym
	SQL Script
	DLM Script

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

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 / Function / Event  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

	ID	Name	Critical?	Client Side Support
1	1634	Avoid unreferenced Tables
2	7130	Avoid Artifacts with High Depth of Nested Subqueries
3	7344	Avoid "SELECT *" queries
4	7346	Avoid redundant indexes
5	7348	Avoid too many Indexes on one Table
6	7388	Avoid artifacts having recursive calls
7	7390	Avoid having multiple Artifacts inserting data on the same SQL Table
8	7394	Avoid having multiple Artifacts updating data on the same SQL Table
9	7392	Avoid having multiple artifacts deleting data on the same SQL table
10	7404	Avoid unreferenced views
11	7424	Avoid using SQL queries inside a loop
12	7436	Prefer UNION ALL to UNION
13	7760	Avoid triggers, functions and procedures with a very low comment/code ratio NEW
14	7762	Avoid undocumented triggers, functions and procedures
15	7766	Avoid Artifacts with High Cyclomatic Complexity NEW
16	7768	Avoid Artifacts with High Depth of Code NEW
17	7772	Avoid Artifacts with High Essential Complexity NEW
18	7774	Avoid Artifacts with High Integration Complexity NEW
19	7776	Avoid Artifacts with High Fan-In
20	7778	Avoid Artifacts with High Fan-Out
21	7784	Avoid Artifacts with lines longer than X characters
22	7808	Avoid Artifacts with SQL statement including subqueries
23	7814	Avoid Tables not using referential integrity
24	7816	Avoid using GOTO statement
25	7828	Avoid Artifacts with High RAW SQL Complexity NEW
26	7842	Avoid large Artifacts - too many Lines of Code
27	7856	Avoid Tables with more than 20 columns on an OLTP system
28	7860	Avoid unreferenced Functions
29	1101000	Never use SQL queries with a cartesian product		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
30	1101002	Never use SQL queries with a cartesian product on XXL Tables		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
31	1101004	Avoid non-indexed SQL queries		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
32	1101006	Avoid non-indexed XXL SQL queries		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
33	1101008	Avoid non-SARGable queries		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
34	1101010	Avoid NATURAL JOIN queries		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
35	1101012	Specify column names instead of column numbers in ORDER BY clauses		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
36	1101014	Avoid queries using old style join convention instead of ANSI-Standard joins		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
37	1101016	Avoid Artifacts with too many parameters
38	1101018	Avoid using the GROUP BY clause
39	1101020	Avoid using quoted identifiers
40	1101022	Avoid Tables without Primary Key
41	1101024	Avoid using dynamic SQL in SQL Artifacts
42	1101026	Always define column names when inserting values		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
43	1101028	Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
44	1101030	Avoid Artifacts with queries on too many Tables and/or Views
45	1101032	Avoid exists and not exists independent clauses NEW		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
46	1101034	DISTINCT should not be used in SQL SELECT statements NEW		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
47	1101036	Use ANSI standard operators in SQL WHERE clauses NEW (CLIENT ONLY)		COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
48	1101038	Replace 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++, PYTHON
49	1101040	Avoid empty catch blocks NEW

Special Note about XXL/XXS support

See SQL Analyzer - working with XXL or XXS tables 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. :

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

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