Description

The new SQL Analyzer (successor to the "SQL Script" extension) provides support for database technologies using ANSI SQL-92 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
• 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

DDL extraction

Before you can use the extension, you may need to use a third-party DDL extractor for your RDBMS and generate one or several *.sql files containing the required DDL. In other words, you need to "export" the database schema with a tool that generates DDL from the database or at least have DDL scripts that can be used to re-build the database. This export should contain exclusively *.sql files which can then be delivered via the CAST Delivery Manager Tool.

What about superfluous clauses exported in your SQL files?

How to extract DDL and DLM files using third-party tools

This is a non-exhaustive list. For general extraction you can also consider http://www.sql-workbench.net/dev-download.html.

RDMS	Sources
	CAST documentation: SQL Analyzer - postgreSQL schema export or extraction as operator See also http://stackoverflow.com/questions/1884758/generate-ddl-programmatically-on-postgresql
	http://stackoverflow.com/questions/6597890/how-to-generate-ddl-for-all-tables-in-a-database-in-mysql
	CAST documentation: SQL Analyzer - MariaDB and MySQL DDL example export or extraction See also https://mariadb.com/kb/en/mariadb/mysqldump
	http://sqlitebrowser.org/ (export database function)

src and uaxDirectory files

Alongside .SQL files, the extension will also accept also src and uaxDirectory files for analysis.

Packaging and delivery

Using the CAST Delivery Manager Tool:

• create a new Version
• create a new Package for your SQL Script (*.sql) files using the Files on your file system option:

• Enter a name for the Package and then define the root folder in which your .sql files are stored:

• Run the Package action: the CAST Delivery Manager Tool will not find any "projects" related to the .sql files - this is the expected behaviour. However, if your SQL script related source code is part of a larger application (for example a JEE application), then other projects may be found during the package action. The CAST Delivery Manager Tool, should, however, identify the .sql files:

• Deliver the Version

Analyzing

Using the CAST Management Studio:

• Accept and deploy the Version in the CAST Management Studio. No Analysis Units will be created automatically relating to the SQL Script files - this is the expected behaviour. However, if your SQL Script files are part of a larger application (for example a JEE project), then other Analysis Units may be created automatically:

• In the Current Version tab, add a new Analysis Unit specifically for your SQL Script files, selecting the Add new Universal Analysis Unit option:

• Edit the new Analysis Unit and configure in the Source Settings tab:
  
  • a name for the Analysis Unit
  • ensure you tick the SQL Analyzer option
  • define the location of the deployed SQL script files (the CAST Management Studio will locate this automatically in the Deployment folder):

• Run a test analysis on the Analysis Unit before you generate a new snapshot.
• Add dependencies between client technologies (Java, C#, Mainframe etc...) to that Analysis Unit in order to get client to server links.

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
	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	7762	Avoid undocumented triggers, functions and procedures
14	7776	Avoid Artifacts with High Fan-In
15	7778	Avoid Artifacts with High Fan-Out
16	7808	Avoid Artifacts with SQL statement including subqueries
17	7814	Avoid Tables not using referential integrity
18	7860	Avoid unreferenced Functions
19	1101000	Never use SQL queries with a cartesian product		COBOL, PB, VB, .NET, JAVA, C/C++
20	1101002	Never use SQL queries with a cartesian product on XXL Tables		COBOL, PB, VB, .NET, JAVA, C/C++
21	1101004	Avoid non-indexed SQL queries		COBOL, PB, VB, .NET, JAVA, C/C++
22	1101006	Avoid non-indexed XXL SQL queries		COBOL, PB, VB, .NET, JAVA, C/C++
23	1101008	Avoid non-SARGable queries		COBOL, PB, VB, .NET, JAVA, C/C++
24	1101010	Avoid NATURAL JOIN queries		COBOL, PB, VB, .NET, JAVA, C/C++
25	1101012	Specify column names instead of column numbers in ORDER BY clauses		COBOL, PB, VB, .NET, JAVA, C/C++
26	1101014	Avoid queries using old style join convention instead of ANSI-Standard joins		COBOL, PB, VB, .NET, JAVA, C/C++
27	1101016	Avoid Artifacts with too many parameters
28	1101018	Avoid using the GROUP BY clause
29	1101020	Avoid using quoted identifiers
30	1101022	Avoid Tables without Primary Key
31	1101024	Avoid using dynamic SQL in SQL Artifacts
32	1101026	Always define column names when inserting values		COBOL, PB, VB, .NET, JAVA, C/C++
33	1101028	Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries		COBOL, PB, VB, .NET, JAVA, C/C++

Special Note about XXL/XXS support

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

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
• 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