meta data for this page
  •  

ibec_psql_Analyze

Performs analysis of a PSQL object.

Syntax

 ibec_psql_Analyze(PSQLObject : variant; Connection : variant; Options : string) : string;
PSQLObject PSQL object created with ibec_psql_Parse function.
Connection Active connection object.
Options Analysis options. Possible options are:
Select (or just S) Process SELECT/WITH statements.
Insert - I Process INSERT/MERGE statements.
Update - U Process UPDATE/MERGE statements.
Delete - D Process DELETE statements.
Plan - P Include plan into the result report and analyze plan for NATURAL scans.
Type - T Analyze datatype compatibility issues.
Warning - W Include compiler warnings into the result report.

If an empty string is passed as an option string all possible options will be applied.

The function returns a report in JSON format.

Example

Following example retrieves all procedure DDL's from a database, analyzes them and saves reports in separate files: 

execute ibeblock 
as
begin
 db = ibec_GetDefaultConnection();

 -- Directory to save analysis reports
 sDir = 'D:\Temp\Procs';
 ibec_ForceDirectories(sDir);

 for select rdb$procedure_name
     from rdb$procedures
     -- Comment the next line if your server doesn't support packages
     where rdb$package_name is null
     order by 1
     into :ProcName
  do
  begin
    ProcName = ibec_Trim(ProcName);

    -- Pass an empty string as an option string to get pure single CREATE statement without SET TERM etc.
    sPSQL = ibec_GetObjectScript(db, ProcName, __dboProcedure, '');

    ObjPSQL = ibec_psql_Parse(sPSQL, 3, __svFB30);
    try
      s = ibec_psql_Analyze(ObjPSQL, db, '');
      ibec_SaveToFile(sDir || '\' || ProcName || '.sp.json', s, __stfOverwrite);
    finally
      ibec_psql_Free(ObjPSQL);
    end
  end;
end;

Example of output: 

 {
   "SyntaxWarnings": [
     {
       "Message": "Empty BEGIN...END statement",
       "StartPosition": 516,
       "Length": 0
     },
     {
       "Message": "Value assigned to 'INTVAR1' never used",
       "StartPosition": 57,
       "Length": 7
     }
   ],
   "Statements": [
     {
       "Statement": "for select emp_no,\r\n
             dense_rank() over (order by salary),\r\n 
             salary / count(salary) over ()\r\n 
             from employee\r\n
             into :intvar1,  :intvar2, :dpVar3\r\n  do",
       "StartPosition": 322,
       "Length": 182,
       "Plan": "PLAN SORT (EMPLOYEE NATURAL)",
       "PlanNatural": true,
       "TypeWarnings": [
         "DENSE_RANK BIGINT  ->  INTVAR2 INTEGER",
         "DIVIDE NUMERIC(18,2)  ->  DPVAR3 INTEGER"
       ],
       "Operations": [
         {
           "Action": "SELECT",
           "AffectedObject": "EMPLOYEE"
         }
       ]
     },
     {
       "Statement": "for select emp_no,\r\n
              salary / count(salary) over (),\r\n
              NTH_VALUE(salary,  2) FROM FIRST over (order by salary)\r\n
              from employee\r\n
              into :intvar1,  :intvar2, :dpVar3\r\n  do",
       "StartPosition": 527,
       "Length": 204,
       "Plan": "PLAN SORT (EMPLOYEE NATURAL)",
       "PlanNatural": true,
       "TypeWarnings": [
         "DIVIDE NUMERIC(18,2)  ->  INTVAR2 INTEGER",
         "NTH_VALUE NUMERIC(18,2)  ->  DPVAR3 INTEGER"
       ],
       "Operations": [
         {
           "Action": "SELECT",
           "AffectedObject": "EMPLOYEE"
         }
       ]
     },
     {
       "Statement": "for select emp_no,\r\n
             dept_no,\r\n
             salary / count(salary) over (partition by dept_no ORDER by dept_no nulls first) rank\r\n
             from employee\r\n
             order by emp_no\r\n
             into :intvar1,  :intvar2, :partition\r\n  do",
       "StartPosition": 753,
       "Length": 234,
       "Plan": "PLAN SORT (SORT (EMPLOYEE NATURAL))",
       "PlanNatural": true,
       "TypeWarnings": [
         "DEPT_NO CHAR(3)  ->  INTVAR2 INTEGER",
         "RANK NUMERIC(18,2)  ->  PARTITION INTEGER"
       ],
       "Operations": [
         {
           "Action": "SELECT",
           "AffectedObject": "EMPLOYEE" 
         }
       ]
     }
   ]
 }