SQL file categorization
Description
Some SQL files are considered as DDL, even if they have some DML statements externals to DDL statements. Others are even considered as data export like or script like. We explain here how SQL files are categorized.
How SQL files are categorized
Before start analyzing an SQL file we put it in a specific category :
| File scope | File category | Why | Logged message | |
|---|---|---|---|---|
| File content will contribute to analysis | DDL |
Analysis files are based on a uaxDirectory file, they could be only DDL | DEBUG: File <file_name> is
considered as DDL |
|
| For the SAP Sqlscript variant, files could be only DDL | ||||
Mandatory files *.pf, *.pf38, *.lf, *.lf38, *.sqlt, *.ddl, *.plsql, *.pgsql, *.mysql are considered DDL. Optional files *.bdy, *.db2, *.fnc, *.pck, *.pkb, *.pkg, *.pks, *.plb, *.pls, *.spc, *.tab, *.tpb, *.tps, *.trg, *.trigddl, *.tsql, *.udf, *.viewddl, *.viw, *.vw are considered DDL. |
||||
Otherwise, when a first DDL statement is detected, the file is considered to be fully DDL if no DML has been detected. From the list of DDL, the ALTER TABLE ... MODIFY is excluded because it could be used in a data export context. When we detected a single DML before DDL, we consider the file DDL. When detected a table / view / schema / database, even if we detected DML statements, we consider the file DDL. |
||||
| DML | Mandatory files *.dml are considered DML. No DDL statements is detected in the first 100 lines, the number of INSERTS is <=80% with only static data. |
DEBUG : File <file_name> is considered as DML | ||
| Mixed DDL DML | When we detect DML statements before DDL statements, in the first/last 100 lines and also in the last 5000 lines for files with more than 5000 lines, when no DDL has been detected in the first/last 100 lines. | DEBUG: File <file_name> is considered as a mix of DDL and DML | ||
| File content will be fully
ignored |
Data export/loading like file | The INSERTs statements are considered as more data export/loading like than DML. When in the first 100 lines we have only DML statements and when more than 80% are INSERTS, the file is considered as data export/loading file like.
If DDL is detected in the last 100/5000 lines, file is considered as DDL and data export/loading statements are ignored. |
INFO: The file <file_name> is considered as data export/loading and it will not be analyzed because in the first 100 lines, from the total number of DML statements, <percent_of_INSERTs%> are INSERT. | |
| Script like file | One of the first 100 lines starts with |
|||
| #! | The file is more a Shell script than SQL file, https://linux.goffinet.org/administration/scripts-shell/ | INFO: The file <file_name>
will be skipped because it is more a script like file than SQL
file. |
||
| ?tacl | The file is more Tacl script than SQL file, http://nonstoptools.com/manuals/Tacl-Guide.pdf | |||
| Empty file | One if the first 100 lines are empty | INFO: The file <file_name> will be skipped because is empty. | ||
| Unknown | Having DDL statements not in the list of supported DDLs : Having DML statements not in the list of supported DMLs, like INSERT/UPDATE/UPSERT/MERGE/TRUNCATE/DELETE/SELECT. When a call of a Function / Procedure is detected, they are counted in the list of DML statements, like CALL/EXEC/EXECUTE/PERFORM. |
INFO: The file <file_name> it will not be analyzed, because it is not DDL neither DML or none of the statements are supported. | ||
| XXL and XXS rules | .sqltablesize files | See SQL Analyzer - working with XXL or XXS tables | INFO: Using table size file <file_name> for XXL rules | |
| Data sensitivity indicators | .datasensitive files | INFO: Using General Data Protection Regulation safety indicator configuration file <file_name> for table columns INFO: Using Payment Card Industry Data Security Standards safety indicator configuration file <file_name> for table columns |
||
| See Data Column Access | INFO : Using Custom Data sensitivity indicator configuration file <file_name> for table columns | |||