Missing tables and procedures for Python - 1.0

Extension ID

com.castsoftware.python.missingtable

What’s new?

Please see Missing tables and procedures for Python - 1.0 - Release Notes for more information.

Description

This extension is designed to analyze SQL queries embedded in Python code and create “missing” SQL and Python table / procedure objects to represent SQL objects that have not been delivered for analysis. Links to these objects are also created. The primary goal of the extension is to ensure that a full transaction is identified by CAST, resulting from the SQL queries embedded in the Python code, even if none or only part of the referenced SQL objects are delivered for analysis.

AIP Core compatibility

This extension is compatible with:

AIP Core release Supported
8.3.x (tick)

Supported client languages

Language Supported?
Python (tick)

Prerequisites

Item Description
(tick) An installation of any compatible release of AIP Core (see table above)
(tick) An installation of com.castsoftware.sqlanalyzer (from version ≥ 3.6.8-funcrel)

Download and installation instructions

The extension will be automatically downloaded and installed in CAST Console when Python source code is delivered for analysis. You can manage it using the Application - Extensions interface.

What results can you expect?

Objects

Icon Object Type Description Metamodel Type

Missing Schema

 Parent of the missing tables and procedures, attached to the Python Project. CAST_Python_MissingTable_Schema

Missing Table A table or a view selected/updated/deleted/inserted in a Python Query missing from the DDL file. CAST_Python_MissingTable_Table

Missing Procedure A procedure or a function called in a Python Query missing from the DDL file. CAST_Python_MissingTable_Procedure

SQL Missing Table A table or a view selected/updated/deleted/inserted in a Python Query missing from the DDL file, but we have a SQL Schema, and only one. SQLScript_Missing_Table

SQL Missing Procedure A procedure or a function called in a Python Query missing from the DDL file but we have a SQL Schema, and only one. SQLScript_Missing_Procedure

Links are created for transaction and function point needs. You can expect the following links on the Python Embedded SQL queries:

  • useSelect/useUpdate/useDelete/useInsert from Python Query  to Missing Table/SQL Missing Table
  • call from Python Query to Missing Procedure/SQL Missing Procedure

Examples

When DDL is partially delivered

In this example, a single table is delivered for analysis called “authors”, belonging to a single SQL schema called “dbo”:

USE Adventure
GO
CREATE TABLE [dbo].[authors] (
    [au_id] id NOT NULL,
    [au_lname] varchar(40) NOT NULL,
    [au_fname] varchar(20) NOT NULL,
    [phone] char(12) NOT NULL,
    [address] varchar(40) NULL,
    [city] varchar(20) NULL,
    [state] char(2) NULL,
    [country] varchar(12) NULL,
    [postalcode] char(10) NULL
)
;

On the Python side, the following is delivered for analysis:

import sqlite3

a.execute('SELECT * FROM authors, TOTO where TOTO_ID = 3 or TOTO_ID = 2')
## function called in the where clause
b.execute('select * from authors where name = get_author_name(id)')
## exec function
c.execute('exec get_author_name(id);')
## only the function
d.execute('get_author_name(id);')

Based on the Python queries and the delivered SQL DDL, the extension will add a SQL Missing Table called “TOTO” and a SQL Missing Procedure called “get_author_name”. They are attached directly to the schema “dbo”.  These two objects are linked with the Python queries:

When DDL is totally missing

In this example no DDL is delivered at all. On the Python side, the same Python queries as in the previous example are delivered for analysis. Based on the Python queries, the extension will add:

  • A Missing Schema (representing “dbo”)
  • Two Missing Tables (representing “authors” and “TOTO”)
  • A Missing Procedure (representing “get_author_name”).

These objects are linked with the Python queries: