Skip to content

DD Changes 18.0

Data Dictionary Changes in 18.0

Changes include:

  • New DD SQL Filter Methods
  • New Constraint Metadata Methods

New DD SQL Filter Methods

  • Property: pbUseDDSQLFilters
  • Property: pbApplyGlobalSQLFilters
  • Property: psSQLFilter
  • Function: SupportsSQLFilters
  • Function: SQLStrLike
  • Function: SQLStrAppend
  • Function: SQLStrFileFieldName
  • Procedure: Data_Set_PreFind
  • Procedure: Data_Set_PostFind
  • Procedure: StoreDefaultSQLFilters
  • Procedure: RestoreDefaultSQLFilters
  • Procedure: ApplySQLFilter

New low-level and high-level DD messages have been added that make it possible to set SQL filters at the DDO level. When enabled, all DD record finds and reads may apply a custom DD SQL filter before the find/read and then restore the Table's SQL filter state back to its original state.

At the simplest level, enable SQL filtering by setting pbUseDDSQLFilters to True and define an SQL filter by setting psSQLFilter. Use pbApplyGlobalSQLFilters to determine if psSQLFilter should replace the table-level active SQL filter or if the two filters should be combined.

DD SQL filters are applied to most but not all DD find and read methods. They are applied to Find, Request_Find, Request_Read, and Locate_next. They are not applied to record-identity finds (Find_By_Recnum, Read_By_Recnum, FindByRowId, ReadByRowId) and they are not applied to parent-table relates associated with a find. Rereads which are applied as part of DD saves and deletes do not apply SQL filtering. Filters will be used when autofill-constrained finds require a child find (for example, you find an order record and it finds the first related child).

DD SQL filters are not inherited. An SQL filter defined in a parent is never applied to a child, because the SQL server can only apply the filter to the table it is querying.

Normally you would apply filters by setting psSQLFilter in OnConstrain, although in more static cases you could set the filter in the object declaration. These two examples work the same:

Object SalesP_DD1 is a Salesp_DataDictionary
    Set pbUseDDSQLFilters to True
    Set psSQLFilter to "SalesP.Name LIKE '%J%'"
End_Object

Object SalesP_DD is a Salesp_DataDictionary
    Set pbUseDDSQLFilters to True
    Procedure OnConstrain
        Set psSQLFilter to "SalesP.Name LIKE '%J%'"
    End_Procedure
End_Object

The second method is more flexible because it can be easily altered to let you dynamically change your DD constraints:

Property Boolean pbApply False
Object SalesP_DD is a Salesp_DataDictionary
    Set pbUseDDSQLFilters to True
    Procedure OnConstrain
        Boolean bApply
        Get pbApply to bApply
        If (bApply) Begin
            Set psSQLFilter to "SalesP.Name LIKE '%J%'"
        End
        Else Begin
            Set psSQLFilter to ""
        End
    End_Procedure
End_Object

SQL filters can be used alongside DF constraints. When used with DF constraints, the SQL filter will perform the filtered find on the server and possibly return a record. That record will then be tested by the DF constraints:

Set pbUseDDSQLFilters to True
Procedure OnConstrain
    Constrain Customer.State eq "CA"
    Set psSQLFilter to "Customer.Name LIKE '%J%'" // This will be called first
End_Procedure

One of the main purposes of using SQL filters is to speed up record finding by offloading filters to the server. This can dramatically increase performance. However, SQL query planning can change when additional WHERE clauses are added. The SQL engine may choose a different plan that it considers faster, which in some cases could slow down the find. As you provide SQL WHERE clause information via a filter, it becomes your responsibility to ensure the find is efficient.

Property: pbUseDDSQLFilters

Set pbUseDDSQLFilters to True

Set pbUseDDSQLFilters to true if you wish SQL DD filters to be used. When true the filter applied is determined by psSQLFilter and pbApplyGlobalSQLFilters.

  • Default: False
  • Only set to true if the driver supports SQL filters (for example, the Data Access MSSQL driver supports this; the embedded database driver does not). Using this with an unsupported driver will raise an error when attempting to find a record.

Property: pbApplyGlobalSQLFilters

Set pbApplyGlobalSQLFilters to True

Set pbApplyGlobalSQLFilters to true if you wish your DD filter (psSQLFilter) to be appended (ANDed) to the value in the global table filter. When pbUseDDSQLFilters is True and pbApplyGlobalSQLFilters is False, only psSQLFilter is applied to the find. When both are True, the DD filter is added to the global filter assuming that a global filter exists and is active (see df_file_sql_filter and df_file_sql_filter_active attributes).

  • Default: False

Property: psSQLFilter

Set psSQLFilter to "Customer.State = 'CA'"

psSQLFilter defines the SQL filter string to be applied and works similarly to the df_file_sql_filter attribute. This is only applied if pbUseDDSQLFilters is True.

  • If pbUseDDSQLFilters is True, pbApplyGlobalSQLFilters is False, and psSQLFilter is empty, then explicitly no SQL filter is applied. If a table-level filter exists and is active, it will not be used in the find.
  • You can use the SQL string helper functions such as SQLStrFileFieldName, SQLStrAppend to help assemble your SQL filter string.
  • Default: "" (empty string)

Function: SupportsSQLFilters

Get SupportsSQLFilters to bSupported

Call this to determine if this table is capable of supporting SQL filtering and the SQL DD filter interface. This is determined by the driver. If the driver does not support SQL filtering, leave pbUseDDSQLFilters set to false and do not use SQL filtering.

Note: pbUseDDSQLFilters is not tied to SupportsSQLFilters and is not automatically set true when SupportsSQLFilters is true. Enabling SQL filters can change record-finding strategies and should be a deliberate developer decision.

Function: SQLStrLike

Get SQLStrLike field Customer.Name "ab" to sFilter
Get SQLStrLike iColumn "ab" to sFilter

This helper function creates an SQL LIKE filter string for a field and a target value. For the first sample above it would return:

Customer.Name LIKE '%ab%'

Function: SQLStrAppend

Get SQLStrAppend sFilter1 sFilter2 to sNewFilter

This helper function takes two SQL filter strings and combines them using proper SQL AND syntax. If either filter string is empty, the function returns the non-blank value. You don't need to test for empty strings before calling this.

Function: SQLStrFileFieldName

Get SQLStrFileFieldName file_field Customer.Name to sSQLName
Get SQLStrFileFieldName iMyFile iMyField to sSQLName

This helper function creates a string that represents the SQL table and column in a format usable in an SQL WHERE clause. It bases the SQL table name on the underlying SQL table name, which may differ from the DataFlex logical name.

Example:

Get SQLStrFileFieldName file_field Customer.State to sSQLName
Move (sSQLName + "='CA' ") to sFilter

Procedures: Data_Set_PreFind and Data_Set_PostFind

Data_Set_PreFind and Data_Set_PostFind are sent to a DDO before and after finds and reads. They provide before-and-after hooks where DD SQL filters can be applied and removed. You can use these methods but do so carefully: if you do not forward-send these, the DD SQL filter process may break. If you use these functions, review the DataDictionary class code carefully.

These methods are called as part of DD find and read methods: they are called within Find, Request_Find, Request_Read, and Locate_next. They are not applied to record-identity finds (Find_By_Recnum, Read_By_Recnum, FindByRowId, ReadByRowId) and they are not applied to parent-table relates associated with the find. They are called right before and after the internal runtime record find.

General description (simplified):

Data_Set_PreFind
    If pbUseDDSQLFilters
        // store current table SQL filter information
        Send StoreDefaultSQLFilters
        // create new table SQL filter information based on
        // pbApplyGlobalSQLFilters and psSQLFilter
        Send ApplySQLFilter
    End

Data_Set_PostFind
    If pbUseDDSQLFilters
        // restore table SQL filter information
        Send RestoreDefaultSQLFilters
    End

You should rarely need to override these methods. DD SQL filtering is designed to be controlled by pbUseDDSQLFilters, pbApplyGlobalSQLFilters, and psSQLFilter.

Procedure: StoreDefaultSQLFilters

This stores any information about the table's current SQL filters. For the DAW SQL drivers this retrieves the attribute values of DF_FILE_SQL_FILTER, DF_FILE_SQL_FILTER_ACTIVE and DF_FILE_SQL_FILTER_EQ and stores them in private properties psOldFilter, pbOldFilterActive and pbOldFilterEQ. This is called by Data_Set_PreFind.

It is expected that these attributes will be restored by RestoreDefaultSQLFilters, which is called by Data_Set_PostFind.

This is never called if pbUseDDSQLFilters is False.

See: Data_Set_PreFind

Procedure: RestoreDefaultSQLFilters

This restores the global table SQL filters back to their original pre-find state. See StoreDefaultSQLFilters for more.

This is never called if pbUseDDSQLFilters is False.

Procedure: ApplySQLFilter

This is called before a DD find/read and is used to set DD SQL Filters. Data_Set_PreFind calls this.

ApplySQLFilter sets the table SQL filter to the value in psSQLFilter. If pbApplyGlobalSQLFilters is False, the value in psSQLFilter will replace the current global table SQL filter; if True, psSQLFilter is added to any active table SQL filter.

  • If psSQLFilter is empty, that empty filter is applied. If pbApplyGlobalSQLFilters is False, this means no SQL filter is used for this find. If pbApplyGlobalSQLFilters is True, nothing changes.
  • After the find, the filter information is restored by RestoreDefaultSQLFilters.

This is never called if pbUseDDSQLFilters is False.

New Constraint Metadata Methods

DDOs can provide information about their DF constraints (those built up in the OnConstrain event using the Constrain command). You can query all constraints for a DDO and query how the constraint engine uses these constraints for a particular index. Much of this information is technical and useful for advanced programming and debugging. The dd_debug package has been enhanced to present this information.

New functions:

Function DDOConstraints Returns tConstraintDef[]
Function DDOConstraintFindMeta Integer eMode Integer iOrder Returns tDDOConstraintFindMeta
Function DDOConstraintNeedsRelate Integer iOrder Returns Boolean

Constraints are built when Rebuild_Constraints is sent to a DDO. The DDO calls OnConstrain, which executes all Constrain commands. Each constraint is added to an array of constraints. If the DDO inherits constraints from its parent DDOs (determined by pbInheritConstraints), it will send OnConstrain to the parents and add those parent constraints to the array. Upon completion, this array of constraints (the DD's constraints plus any inherited constraints) is used to filter all finds for this DDO.

Get DDOConstraints

tConstraintDef[] Constraints
Get DDOConstraints of hoDD to Constraints

Call Get DDOConstraints of the DDO after constraints have been rebuilt. This returns an array of tConstraintDef types. tConstraintDef is defined as follows:

// Struct used to return current constraints in an array
Struct tConstraintDef
    Integer eType      // constraint Find type
    Integer eMode      // constrain file mode
    Integer iFile
    Integer iField
    String sValue
    Integer iOtherFile
    Integer iOtherField
End_Struct

eType - Constraint Type

eType determines the constraint type and can be one of the following:

  • CONST_VALUE
Constrain Customer.Name GT "J"

The DDO filters the value of iFile and iField based on the comparison mode (eMode) and the value in sValue. This is defined with Constrain File.field GT SomeValue. The value in sValue is evaluated when the Constrain command is executed (when Rebuild_Constraints calls OnConstrain). Therefore the value can be a constant, a variable or an expression — its value is determined when the constraint is built.

  • CONST_CHILD
Set Constrain_File to Customer.File_Number

The DDO relates to a parent DDO. This is usually defined with the Set Constrain_File interface or Constrain File relates to ParentFile. When this type is defined, the child file and relating-from field are stored in iFile and iField and the related parent file and field are stored in iOtherFile and iOtherField. If no relationship exists between the two files, this constraint will not be added to the array.

  • CONST_FIELD
Constrain Customer.ID EQ Region.ID

Similar to CONST_VALUE except the value being compared is another file.field. Usually defined as Constrain MyFile.MyField GT SomeOtherFile.SomeField. iFile and iField define the left side; iOtherFile and iOtherField define the right side. Unlike CONST_VALUE, the right-side value is not known until the record is found. The runtime may determine if the right-side record requires a relate; this decides if the comparison is made before or after the relate.

  • CONST_EXP
Constrain Customer as (LowerCase(Left(Customer.name,1)="a"))

Used with Constrain as constraints. Typically defined as Constrain File as (Expression). The runtime cannot provide additional details about the expression, so none of the struct members convey useful information.

  • CONST_RELATES

A legacy runtime value that should never appear; it is the same as CONST_CHILD.

eMode - Constraint Find Modes

eMode is the find comparison mode (used with CONST_VALUE and CONST_FIELD). Values include:

CONST_LT (0)         // Constrain Customer.sName LT sValue
CONST_LE (1)         // Constrain Customer.sName LE sValue
CONST_EQ (2)         // Constrain Customer.sName EQ sValue
CONST_GE (3)         // Constrain Customer.sName GE sValue
CONST_GT (4)         // Constrain Customer.sName GT sValue
CONST_NE (5)         // Constrain Customer.sName NE sValue
CONST_Matches (6)    // Constrain Customer.sName matches "?A*"
CONST_Contains (7)   // Constrain Customer.sName contains sValue
CONST_MatchesJumpIn (9) // Constrain Customer.sName matches "A*"

The difference between Matches and MatchesJumpIn is determined by the right-side value and whether it is structured so a jump can be performed. For example, "Dat*" can be used for jump-in while "?Dat*" cannot.

There is no single Between operator; using Between adds two constraints to the array (one for GE and one for LE).

Each DDO has its own array of constraints. When constraints are inherited, the inheriting DDO's array includes the inherited constraints.

Use of DDOConstraints is advanced and rarely needed. See dd_debug.dg for examples and debugging output.

Function: DDOConstraintFindMeta

tDDOConstraintFindMeta FindMeta
Get DDOConstraintFindMeta of hoDD (GE) iIndex to FindMeta

Before a constrained find occurs for a specified index and find mode, the runtime determines what optimizations can be applied. It examines every constraint and determines:

  • which segments can be seeded for Jump-in,
  • which constraints are Jump-out (if the constraint fails, the find is complete),
  • which constraints can be evaluated before the relate (pre-relate),
  • which constraints must be evaluated after the relate (post-relate).

DDOConstraintFindMeta returns this information as tDDOConstraintFindMeta:

Struct tDDOConstraintFindMeta
    Integer iJumpInSegments
    Boolean bJumpOut
    Boolean bPreRelate
    Boolean bPostRelate
End_Struct
  • iJumpInSegments: number of index segments filled when the find is initialized; more segments generally means a more efficient jump-in.
  • bJumpOut: true if any constraint, upon failing, guarantees there are no more valid records.
  • bPreRelate: true if any constraints can be evaluated after the record find but before a relate. If a pre-relate constraint fails, you avoid an unnecessary relate for that find.
  • bPostRelate: true if any constraints must be evaluated after the relate (post-relate). Post-relate constraints require a relate to determine validity and can be expensive if many records must be evaluated.

Constraint evaluation order during a find:

  1. Jump-out constraints (if they fail, the find is over).
  2. Pre-relate constraints (if they fail, no relate occurs but the search continues).
  3. Relate (if needed).
  4. Post-relate constraints (if they fail, the search continues).

Note: Inherited constraints almost always require post-relate tests. If you do not need inherited constraints, set pbInheritConstraints to False. Constrain as constraints always require post-relate tests because the runtime cannot evaluate the expression prior to relate.

This is technical debugging/profiling information; dd_debug.dg provides tools to view these details.

Function: DDOConstraintNeedsRelate

Get DDOConstraintNeedsRelate of hoServer iIndex to bNeedsRelate

When reading batches of records you may want to skip relates for performance. Use the No_Relate_State property to disable relates, but first determine whether current constraints require relates. You can inspect DDOConstraintFindMeta and bPostRelate, or call DDOConstraintNeedsRelate to get a direct boolean answer for the specified index.

Example: read totals without unnecessary relates

Move oOrderHea_DD to hoServer
Move 1 to iIndex
Get No_Relate_State of hoServer to bOldRelate
Get DDOConstraintNeedsRelate of hoServer iIndex to bNeedsRelate
Set No_Relate_State of hoServer to (not(bNeedsRelate))
Send Request_Read of hoServer FIRST_RECORD Orderhea.File_Number iIndex
Move (Found) to bOk
While (bOk)
    Move (Orderhea.Total + nTotal) to nTotal
    Send Request_Read of hoServer GT Orderhea.File_Number iIndex
    Move (Found) to bOk
Loop
Set No_Relate_State of hoServer to bOldRelate

This technique is useful when building classes that need to read records for a set of constraints.

See Also

  • What's New in DataFlex 2014 - 18.0: Whats_New_in_DataFlex_18.0.md