Learning › Ecsqlreference › Pragmas
Edit this page

Pragmas

PRAGMA help

Print out list of pragma supported by ECSQL.

PRAGMA help
pragma type descr
checksum global checksum([ecdb_schema|ecdb_map|sqlite_schema]) return sha3 checksum for data.
ecdb_ver global return current and file profile versions
experimental_features_enabled global enable/disable experimental features
explain_query global explain query plan
help global return list of pragma supported
integrity_check global performs integrity checks on ECDb
parse_tree global parse_tree(ecsql) return parse tree of ecsql.
disqualify_type_index class set/get disqualify_type_index flag for a given ECClass

PRAGMA checksum

Calculate and print out SHA3 256 hash for a subset of the ec_ tables or the sqlite schema

PRAGMA checksum('ecdb_map')
  • ecdb_schema - Includes only the ec definition tables but not the mapping tables

    ec_Schema, ec_SchemaReference, ec_Class, ec_ClassHasBaseClasses, ec_Enumeration, ec_KindOfQuantity, ec_UnitSystem, ec_Phenomenon, ec_Unit, ec_Format, ec_FormatCompositeUnit, ec_PropertyCategory, ec_Property, ec_RelationshipConstraint, ec_RelationshipConstraintClass, ec_CustomAttribute

  • ecdb_map - Includes only the ec mapping tables but not the ec definition tables

    ec_PropertyPath, ec_ClassMap, ec_Table, ec_Column, ec_Index, ec_IndexColumn, ec_PropertyMap

  • sqlite_schema - Includes information in the sqlite_master table

PRAGMA ecdb_ver

Print out ECDb current profile version supported by software and file profile version.

PRAGMA ecdb_ver
current file
4.0.0.4 4.0.0.2

PRAGMA experimental_features_enabled

Enable experimental feature in ECSQL on current connection.

PRAGMA experimental_features_enabled=true

to switch off

PRAGMA experimental_features_enabled=false

to check if flag is currently set.

PRAGMA experimental_features_enabled
experimental_features_enabled
False

PRAGMA explain_query

Prints out a high level description of the strategy or plan SQLite uses to implement a specific SQL query generated by the input ECSql query. For use in interactive debugging purposes, the output of this command may change in the future.

PRAGMA explain_query ('SELECT * FROM bis.GeometricElement3d')
id parent notused detail
3 0 215 SCAN main.bis_GeometricElement3d
5 0 45 SEARCH main.bis_Element USING INTEGER PRIMARY KEY (rowid=?)

PRAGMA integrity_check (experimental)

  1. check_ec_profile - checks if the profile table, indexes, and triggers are present. Does not check be_* tables. Issues are returned as a list of tables/indexes/triggers which were not found or have different DDL.
  2. check_data_schema - checks if all the required data tables and indexes exist for mapped classes. Issues are returned as a list of tables/columns which were not found or have different DDL.
  3. check_data_columns - checks if all the required columns exist in data tables. Issues are returned as a list of those tables/columns.
  4. check_nav_class_ids - checks if RelClassId of a Navigation property is a valid ECClassId. It does not check the value to match the relationship class.
  5. check_nav_ids - checks if Id of a Navigation property matches a valid row primary class.
  6. check_linktable_fk_class_ids - checks if SourceECClassId or TargetECClassId of a link table matches a valid ECClassId.
  7. check_linktable_fk_ids- checks if SourceECInstanceId or TargetECInstanceId of a link table matches a valid row in primary class.
  8. check_class_ids- checks persisted ECClassId in all data tables and makes sure they are valid.
  9. check_schema_load - checks if all schemas can be loaded into memory.
PRAGMA integrity_check ECSQLOPTIONS ENABLE_EXPERIMENTAL_FEATURES;

The output of integrety_check is a table with each test performed, the result and time took to run the test.

sno check result elapsed_sec
1 check_data_columns True 0.005
2 check_ec_profile True 0.001
3 check_nav_class_ids True 0.179
4 check_nav_ids True 0.403
5 check_linktable_fk_class_ids True 0.001
6 check_linktable_fk_ids False 0.003
7 check_class_ids True 0.039
8 check_data_schema True 0.000
9 check_schema_load True 0.000

PRAGMA parse_tree (experimental)

Generates a json representation parsed tree of the input ECSql.

PRAGMA parse_tree ('SELECT ECClassId, CodeValue FROM bis.GeometricElement3d') ECSQLOPTIONS ENABLE_EXPERIMENTAL_FEATURES
{ "id": "SelectStatementExp", "select": { "id": "SingleSelectStatementExp", "selection": [ { "id": "DerivedPropertyExp", "exp": { "id": "PropertyNameExp", "path": "ECClassId" } }, { "id": "DerivedPropertyExp", "exp": { "id": "PropertyNameExp", "path": "CodeValue" } } ], "from": [ { "id": "ClassNameExp", "tableSpace": "", "schemaName": "BisCore", "className": "GeometricElement3d" } ] } }

ECSql Syntax

Last Updated: 29 April, 2025