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 and PostgreSQL variants. The name of the default CockroachDB schema is “PUBLIC” and “public” for PostgreSQL. Generally, identifiers are qualified in CREATE statements.
• The schema identification, when objects are not prefixed with schema name, is based on the following statements:

USE <DATABASE_NAME>

SETUSER 'USER_NAME'

ALTER SESSION SET CURRENT_SCHEMA = <SCHEMA_NAME>

SET CURRENT SCHEMA = <SCHEMA_NAME>

SET SEARCH_PATH TO/= <SCHEMA_NAME>

CREATE SCHEMA [IF NOT EXISTS] <SCHEMA_NAME>

SET SEARCH_PATH TO/= <SCHEMA_NAME>

ALTER VIEW/TABLE <OBJECT_NAME> SET SCHEMA <SCHEMA_NAME>

CREATE SCHEMA [<DATABASE_NAME>.]<SCHEMA_NAME>

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:

CREATE SCHEMA <schema name>

ALTER SESSION SET CURRENT_SCHEMA = <schema name>

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. 

When the schema is missing from DDL files and it is set via some kind of configuration files, there is a possible workaround to avoid the DEFAULT schema. You should simply add one of the statements mentioned above, in the chapter “How a schema is determined”. By example, you have some Oracle DDL and the schema is not mentoned in your SQL files and you want to set the schema to MYSCHEMA. Copy the following PowerShell script into a ps1 file:

function insert-content {
    param ( [String]$Path )
    process {
        $( , $_; Get-Content $Path -ea SilentlyContinue) | Out-File $Path
    }
}

$files = Get-ChildItem -Path .\*.sql
foreach ($file in $files) {
    try {echo $file
        'ALTER SESSION SET CURRENT_SCHEMA = MYSCHEMA;
/' | insert-content $file
    }
    catch {
    }
}

It will add the statement:

ALTER SESSION SET CURRENT_SCHEMA = MYSCHEMA;
/

at the beginning of each sql file you have in a folder.

Example

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

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:

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