How object identity is determined

Introduction

Object identity is independent from the *.sql file the object comes from.

  • Object identity depends on the Analysis Unit’s name. Therefore, using a new Analysis Unit or renaming 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

How a database is determined

For the following two situations, objects are identified by the Analysis Unit name, database name, schema name and object name.

IBM DB2

Support added starting with SQL Analyzer ≥ 3.0.0-alpha2, when the database name can be successfully identified in the CREATE TABLE statement

Microsoft SQL Server and Sybase ASE

Support added starting with SQL Analyzer ≥ 3.4.0-beta5 when the following appears before a schema’s objects or objects are double-dotted:

USE <database_name>

OR

CREATE DATABASE <database_name>

OR

CREATE <OBJECT_TYPE>*) <database_name>.<schema_name>.<object_name>

*) TABLE, VIEW, etc.

CockroachDB

Support added starting with SQL Analyzer ≥3.5.9-funcrel when the following appears before a schema’s objects or objects are double-dotted:

USE <database_name>

OR

SET [SESSION] DATABASE = <database_name>

OR

CREATE DATABASE [IF NOT EXISTS] <database_name>

OR

CREATE <OBJECT_TYPE>*) <database_name>.<schema_name>.<object_name>

*) TABLE, VIEW, INDEX / PK

NonStop SQL

Support added starting with SQL Analyzer ≥3.5.9-funcrel when the following appears before a schema’s objects or objects are double-dotted:

SET CATALOG <database_name>

OR

SET SCHEMA <database_name>.<schema_name>

OR

CREATE CATALOG <database_name>

OR

CREATE SCHEMA <database_name>.<schema_name>

OR

CREATE <OBJECT_TYPE>*) <database_name>.<schema_name>.<object_name>

*) TABLE, TRIGGER, PROCEDURE, VIEW, INDEX / PK/FK

How a schema is determined

  • When no schema can be determined, the analyzer considers that a schema named “DEFAULT” is used except for CockroachDB variant when the name of the default schema is “PUBLIC. But generally, identifiers are qualified in CREATE statements.
  • The schema identification, when objects are not prefixed with schema name, is based on the following statements:

MariaDB / MySQL

 
USE <DATABASE_NAME>
Microsoft SQL Server / Sybase ASE 
SETUSER 'USER_NAME'
Oracle Server 
ALTER SESSION SET CURRENT_SCHEMA = <SCHEMA_NAME>
IBM DB2 
SET CURRENT SCHEMA = <SCHEMA_NAME>
PostgreSQL 
SET SEARCH_PATH TO/= <SCHEMA_NAME>
CockroachDB 
CREATE SCHEMA [IF NOT EXISTS] <SCHEMA_NAME>

OR

SET SEARCH_PATH TO/= <SCHEMA_NAME>

OR

ALTER VIEW/TABLE <OBJECT_NAME> SET SCHEMA <SCHEMA_NAME>
NonStop SQL 
CREATE SCHEMA [<DATABASE_NAME>.]<SCHEMA_NAME>

OR

SET SCHEMA [<DATABASE_NAME>.]<SCHEMA_NAME>
  • When the analysis is based on a .castextraction file (i.e. the output of the CAST Database Extractor), schemas are retrieved from the .uaxDirectory file
  • When you move from .castextraction files (i.e. the output of the CAST Database Extractor) to DDL like files, to keep the same identity for your objects and in order to avoid added/removed objects, you should do the following:
All RDBMS

Prefix your objects with schema names, if they are not prefixed. 

OR use the following syntax before schema's objects (starting with SQL Analyzer 3.4.0-beta6):

CREATE SCHEMA <schema name>
Oracle Server only

OR add the following before schema's objects:

ALTER SESSION SET CURRENT_SCHEMA = <schema name>
Microsoft SQL Server / Sybase ASE

OR add the following before user's objects:

SETUSER 'USER_NAME'

Unresolved objects

Starting with 3.5.0-alpha1, we resolve database links. Sometimes, objects accessed via a database link is missing from analysis results and for those object we add an Unresolved Object. The identifiers of those objects are related to the database link that access them. So it looks like that :

<Database Link Indentifier>.<Unresolved Schema Name>.<Unresolved Object>

Example: 

 CREATE SHARED PUBLIC DATABASE LINK DBLINK.DOMAIN
  CONNECT TO TOTO
  USING 'TOTO.db.toto.com'
/
Create Procedure MYPROC_UNRESOVLED_DBLINK
Is
Begin
  select MYREMOTESCHEMA.MYREMOTEPROC@DBLINK.DOMAIN()
  from dual;
End;
 /

The procedure MYREMOTESCHEMA.MYREMOTEPROC accessed via the public database link DBLINK.DOMAIN is considered unresolved because is missing from analysis result. The identifier it will be based on the database link identifier : PUBLIC.DBLINK.DOMAIN and his own identifier : MYREMOTESCHEMA.MYREMOTEPROC, and it is : PUBLIC.DBLINK.DOMAIN.MYREMOTESCHEMA.MYREMOTEPROC.

DEFAULT schema

When no schema can be determined, the analyzer considers that a schema named “DEFAULT” but  when a table is altered, renamed or dropped and the object name is not prefixed by the schema name and the schema name is not set, via one of the supported statements https://doc.castsoftware.com/display/TECHNOS/SQL+Analyzer+-+How+object+identity+is+determined#SQLAnalyzerHowobjectidentityisdetermined-Howschemaisdetermined 

We have two situations:

  1. When another DDL statement exists in the same file, for the schema “DEFAULT” we consider “DEFAULT” as the correct schema
  2. Otherwise we try to find another best candidate instead the schema “DEFAULT”, in the most close SQL like file. 

Example

In the example_application folder we have at least 2 similar SQL like folders:

one two
Having Oracle incremental like files in the folder : resources\META-INF\one\db.  Having Oracle incremental like files in the folder : resources\META-INF\two\db
With only a Mysql file in the folder web\WEB-INF With only a Mysql file in the folder web\WEB-INF

Inside DB folders we have multiples subfolders, e.g. 2005.01, 2005.02, etc, with many SQL files, that looks like incremental SQL scripts, starts with CREATE statements and continues with ALTER/RENAME, etc. Both db folders are +/- similar, only the schema name is changing. In files with ALTER table statements, sometimse the schema name is missing leading to loss of some columns or constraints (primary keys). With the solution described before, all those alters are now correctly resolved, columns and primary keys are correctly added.

Inside the web\WEB-INF folder we have a single SQL DDL file, schema is never set and object names are never prefixed with schema name. For that case, the schema it will stay “DEFAULT”. 

Similar table / procedure like objects

Starting with SQL analyzer 3.6.3-funcrel, table / procedure like objects are now seen sometimes as similar and merged.

When a similar objects will be found in another schema the one without schema (DEFAULT) will be merged with the one with schema.

The merge of similar objects is decided on a “normalized” CRC - for which the text is always in lower case and not only. Blanks and comments are ignored. It is about a temporary property, never materialized, available on the fly, only when symbols are created.

Some keywords / strings are simply ignored:

  • or followed by replace / alter
  • language, $$, $body$ are ignored
  • the schema name when prefixing the object name in the CREATE statement

And the following are considered as similar:

Shortname Equivalent
int integer
int4  integer
varchar character varying
proc procedure
func function

Oracle PL/SQL nested function / procedure objects

Starting with SQL analyzer 3.6.8-funcrel, Oracle PL/SQL nested subprograms are supported in packages and function/procedure.

When a nested function/procedures is detected, the parent will be the current function / procedure. Typically a nested function will be identified by the Analysis Unit name, schema name, parent procedure/function name and the function name.

Limitations

Limitation removed in 3.6.6-funcrel.

Dots “.” are not supported in the name of a Database / Schema. Normally we should not use dots in SQL objects because dots are for identifying objects, usually in the .. patterns.

If your database / schema name have dots, then as a workaround you should replace them with underscores.

USE [Toto.titi]
GO

should be replaced with :

USE [Toto_titi]
GO