Audit Rules

Tip

The system provides "audit rule" capabilities. It supports preset rules through ATT_AUDIT_RULE and uses strategyKey as the routing identifier.
This article explains how to add rule metadata, implement QualitySqlGenerator, integrate database dialects (ComponentRegistry / ComponentItem), and extend parameters in rule.getConfig(), helping you quickly complete audit rule extension and adaptation.

1. Overview

Audit rules are used to check and constrain data quality. They use an extensible architecture of "preset rule table + strategy identifier + SQL generator + database dialect".

Core goals

• Manage rule metadata through the ATT_AUDIT_RULE table. The key field strategyKey is used to route to the concrete implementation.
• Use the QualitySqlGenerator interface to uniformly generate rule SQL, error data SQL, and valid data pagination SQL.
• Use the ComponentRegistry factory to select a dialect implementation (ComponentItem) by data source type, adapting to multiple databases.
• Support rule parameter extension in rule.getConfig() to avoid entity changes.

Overall flow (brief)

1. Preset a rule in ATT_AUDIT_RULE and set a unique strategyKey.
2. Add a QualitySqlGenerator implementation annotated with @Component("strategyKey").
3. Call ComponentRegistry inside QualitySqlGenerator to obtain the corresponding ComponentItem (dialect).
4. Generate SQL fragments or complete SQL in the dialect, execute the audit, and return the result.

2. Table Structure (ATT_AUDIT_RULE)

Table name: ATT_AUDIT_RULE
Location: QDATA_TEST Schema

This table is used to configure and manage basic information for preset audit rules. The backend routes to the corresponding rule implementation based on the strategyKey field.

Field Name	Type	Description
ID	BIGINT	Primary key, auto-increment
NAME	VARCHAR2(128)	Rule name
QUALITY_DIM	CHAR(1)	Quality dimension: 1-completeness, 2-uniqueness, 3-validity, 4-consistency, 5-timeliness
TYPE	CHAR(1)	Rule type
LEVEL	CHAR(1)	Rule level: 1-field level, 2-table level
DESCRIPTION	VARCHAR2(512)	Rule description
VALID_FLAG	VARCHAR2(1)	Whether valid: 0-invalid, 1-valid
DEL_FLAG	VARCHAR2(1)	Deletion flag: 0-not deleted, 1-deleted
CREATE_BY	VARCHAR2(32)	Creator
CREATOR_ID	BIGINT	Creator ID
CREATE_TIME	DATETIME	Creation time
UPDATE_BY	VARCHAR2(32)	Updater
UPDATER_ID	BIGINT	Updater ID
UPDATE_TIME	DATETIME	Update time
REMARK	VARCHAR2(512)	Remarks
CODE	VARCHAR2(32)	Rule code
USE_CASE	VARCHAR2(512)	Use case
EXAMPLE	VARCHAR2(512)	Example
ICON_PATH	VARCHAR2(256)	Icon path
STRATEGY_KEY	VARCHAR2(256)	Strategy identifier, such as NOT_NULL_ID_CHECK; the backend uses it to locate the concrete audit rule implementation

Key Points

• STRATEGY_KEY: the unique identifier of the rule. It must remain consistent with @Component("xxx") in the code implementation.
• USE_CASE and EXAMPLE: used to help developers or users understand the rule's applicable scenarios and reference examples.

3. Rule Extension Steps

3.1 Configure basic rule information in the table

• Add a record to ATT_AUDIT_RULE; a unique STRATEGY_KEY (such as NOT_NULL_ID_CHECK) must be configured.
• Fill in other fields as needed, such as QUALITY_DIM, TYPE, LEVEL, USE_CASE, and EXAMPLE.

3.2 Implement QualitySqlGenerator

Location: qdata-quality/src/main/java/tech/qiantong/qdata/quality/utils/quality/QualitySqlGenerator.java

Interface:

public interface QualitySqlGenerator {
    String generateSql(QualityRuleEntity rule);
    String generateErrorSql(QualityRuleEntity rule);
    String generateValidDataSql(QualityRuleEntity rule, int limit, int offset);
}

Implementation and binding (example):

@Component("NOT_NULL_ID_CHECK") // Consistent with STRATEGY_KEY in the table
public class NotNullIdCheckGenerator implements QualitySqlGenerator {
    // For illustration only; see 3.3 for the core logic
}

3.3 Select the database dialect through the factory and generate SQL

In the generate* methods, select the corresponding ComponentItem (database dialect implementation) by data source type through ComponentRegistry, and then call the concrete SQL generation method.

Example:

@Override
public String generateSql(QualityRuleEntity rule) {
    ComponentRegistry registry = new ComponentRegistry();
    ComponentItem item = registry.getComponentItem(rule.getDaDatasourceById().getDatasourceType());
    return item.generateCharacterValidationSql(rule); // Example: character validation
}

@Override
public String generateErrorSql(QualityRuleEntity rule) {
    ComponentRegistry registry = new ComponentRegistry();
    ComponentItem item = registry.getComponentItem(rule.getDaDatasourceById().getDatasourceType());
    return item.generateCharacterValidationErrorSql(rule);
}

@Override
public String generateValidDataSql(QualityRuleEntity rule, int limit, int offset) {
    ComponentRegistry registry = new ComponentRegistry();
    ComponentItem item = registry.getComponentItem(rule.getDaDatasourceById().getDatasourceType());
    return item.generateCharacterValidationValidDataSql(rule, limit, offset);
}

3.4 Dialect registration and default implementation

Location:
qdata-quality/src/main/java/tech/qiantong/qdata/quality/utils/qualityDB/ComponentRegistry.java

public class ComponentRegistry {
    private final Map<String, ComponentItem> componentItemMap = new HashMap<>();
    private final ComponentItem defaultImpl = new DefaultQuality();

    public ComponentRegistry() {
        this.componentItemMap.put(DbType.MYSQL.getDb(), new MySqlQuality());
        this.componentItemMap.put(DbType.DM8.getDb(), new DM8Quality());
    }

    public ComponentItem getComponentItem(String dbCode) {
        return componentItemMap.getOrDefault(dbCode, defaultImpl);
    }
}

Description:

• Built-in supported database types:

  • MySQL → MySqlQuality
  • DM8 → DM8Quality

• Steps to extend other database types:

  1. Create a new implementation class that implements the ComponentItem interface.

  2. Register it in the ComponentRegistry constructor:

     this.componentItemMap.put(DbType.ORACLE.getDb(), new OracleQuality());

• If no concrete database type is matched, the DefaultQuality default implementation is used.

4. SQL Writing Standards

4.1 SQL fragment interface

Location:
qdata-quality/src/main/java/tech/qiantong/qdata/quality/utils/qualityDB/ComponentItem.java

public interface ComponentItem extends QualityFragSql {

    // Common pagination
    default String addPagination(String sql, int limit, int offset) {
        return String.format("%s LIMIT %d OFFSET %d", sql, limit, offset);
    }
}

• Common logic, such as pagination, can be placed in default methods.
• Database-specific SQL is completed by different implementation classes.

4.2 Database-specific implementation

When the database type is not covered by the existing factory, implement it yourself.

Refer to MySqlQuality:

package tech.qiantong.qdata.quality.utils.qualityDB.dialect;

import tech.qiantong.qdata.quality.dal.dataobject.quality.QualityRuleEntity;
import tech.qiantong.qdata.quality.utils.qualityDB.ComponentItem;

public class MySqlQuality implements ComponentItem {

    @Override
    public String fragCharacter(QualityRuleEntity rule) {
        String column = rule.getRuleColumn();
        String regex = (String) rule.getConfig().get("regex");
        return String.format("BINARY %s REGEXP '%s'", column, regex);
    }
}

Description:

• The fragCharacter method implements the SQL fragment for character validation rules.
• Different databases, such as Oracle, PostgreSQL, Kingbase, and DM8, need their own adaptation logic.

5. Notes

1. Parameter extension

   • If an audit rule needs new parameters, store and retrieve them uniformly from rule.getConfig().
   • Avoid frequently modifying entity fields to improve extensibility.

2. Database type extension

   • SQL differences between databases are large, so customization is required in ComponentItem implementations.
   • For database type extension, refer to the database dialect implementation standards in Data Connection Development Docs to maintain consistency.