Contact Sales User Login
Plugin Reference Manuals

Plugins Documentation

Browse configuration schemas, route definitions, and integration guides for all official Uniflow dynamic plugins.

DB Link

DB Link

ID: uniflow.plugin.dblink • Category: Storage & Connectivity • Version: v1.4.0 • Min Uniflow Version: Uniflow ≥ v1.4.0

DB Link Plugin Reference Manual

1. Overview

Plugin Name: DB Link

Type: IT & Network

Identifier: uniflow.plugin.dblink

Description

Connect to MySQL, PostgreSQL, MSSQL, or Oracle databases. Browse tables and stored procedures, read data as collections, and perform INSERT/UPDATE/DELETE operations from rules.

2. Technical Architecture

The DB Link plugin provides relational database integration supporting SQL Server, PostgreSQL, MySQL/MariaDB, SQLite, and Oracle. It manages dynamic ADO.NET database connection pools, handles parameterized SQL query execution, transforms query result sets into JSON telemetry objects, and supports background polling timers as well as transactional INSERT, UPDATE, and DELETE action commands.

System Interaction & Exposed Catalog Routes

The DB Link plugin connects Uniflow to relational database engines (PostgreSQL, MySQL, Microsoft SQL Server, Oracle) via EF Core contexts. It supports periodic table polling, stored procedure execution, and CRUD operations triggered by rule workflows.

Catalog Routes & Node Integration

Input Event Triggers (Input Nodes):

  • dblink.query_result_received - Emitted when a database query completes.
  • dblink.row_count_changed - Emitted when table polling detects new rows.
  • dblink.table_poll_update - Periodic snapshot event from polled views.

    • Executable Actions (Action Nodes):

  • dblink.execute_query - Executes a custom SQL query or command.
  • dblink.insert_row - Inserts a record into a target table.
  • dblink.update_row - Updates existing database records.
  • dblink.execute_procedure - Invokes a stored procedure.

    • Architecture Diagram

    VISUAL ARCHITECTURE FLOW DIAGRAM
    Rendering Flow Architecture Diagram...

    3. Configuration Parameters

    The following configuration fields are available in the User Interface for this plugin:

    Configuration SettingDescription
    Database ProviderSpecifies the database provider.
    {Binding HostLabel}Specifies the {binding hostlabel}.
    Port (Optional)Numeric value specifying the port (optional).
    {Binding DatabaseLabel}Specifies the {binding databaselabel}.
    UsernameSpecifies the username.
    PasswordSpecifies the password (secure).
    Additional Parameters (Optional)Specifies the additional parameters (optional).
    Schema Filter (Optional)Specifies the schema filter (optional).
    Connection Pool SizeSpecifies the connection pool size.
    Max Rows Per QuerySpecifies the max rows per query.
    Command Timeout (Sec)Numeric value specifying the command timeout (sec).
    Poll Interval (Sec)Numeric value specifying the poll interval (sec).

    4. Exposed Routes & Data Types

    This plugin exposes catalog fields across the following rule graph nodes:

    Input Source

    The Input Source node reads real-time database table record counts and row metrics.

    Human-Readable Field NameData TypeDescription
    Table Row Count  Int64
    ↳Real-time record count metric for a discovered database table.

    Data Source

    The Data Source node queries database schema catalog metadata and table record sets.

    Human-Readable Collection NameData TypeExposed Fields & Data TypesDescription
    Tables  Json` (`collection`)
    ↳
    Name (String)
    Schema (String)
    RowCount (Int32)
    		List of discovered database tables.
    Stored Procedures  Json` (`collection`)
    ↳
    Name (String)
    Schema (String)
    Parameters (String)
    		List of discovered stored procedures and functions.
    Discovered Table Query  Json` (`collection`)
    ↳
    Table Column Schema (Int32
    Int64
    Double
    String
    Bool
    DateTime)
    		Queryable collection of table rows mapped dynamically to column schema types.

    Output Target

    The Output Target node acts as an action sink node to execute SQL CRUD operations (INSERT, UPDATE, DELETE) or invoke stored procedures.

    Write Operation Modes (STRICT vs WHERE)

    For UPDATE and DELETE actions, DB Link provides two execution modes configured via the UpdateMode parameter:

    1. STRICT Mode (Default - Primary Key Safety):

  • Purpose: Enforces deterministic, single-record modifications and prevents unintended multi-row updates or full table wipes.
  • Mechanism: Automatically inspects the table's database schema metadata to identify primary key columns (IsPrimaryKey).
  • UPDATE in STRICT mode: Requires primary key values. Non-primary key column values are routed to the SET col = @p... clause, while primary key values form the parameterized WHERE pk = @p... clause. Fails if no primary key is provided.
  • DELETE in STRICT mode: Requires primary key values. Generates DELETE FROM table WHERE pk = @p.... Fails if primary keys are missing.

    • 2. WHERE Mode (Flexible Multi-Row & Criteria-Based Execution):

  • Purpose: Allows updating or deleting records based on custom filter criteria without requiring primary key values (e.g., bulk status updates, timestamp-based purging).
  • Configuration & Parameters:
  • WhereFields: Comma-, semicolon-, or pipe-separated list of column names used in the WHERE condition (e.g. status, area_id). In the Rule Editor, DB Link dynamically generates dedicated input ports prefixed with where_ (and labeled WHERE <ColumnName>) for each filtered column.
  • WhereOperator: Logical operator combining multiple WhereFields (AND or OR, defaults to AND).
  • SetFields: Comma-, semicolon-, or pipe-separated list of columns updated in the SET clause (for UPDATE).
  • Where: Optional direct custom raw SQL WHERE condition expression (e.g. status = 'PENDING' AND retry_count < 3).
  • UPDATE in WHERE mode: Generates UPDATE <table> SET <SetFields> WHERE <WhereFields/Expression>.
  • DELETE in WHERE mode: Generates DELETE FROM <table> WHERE <WhereFields/Expression>.

    • Target Parameters Reference

    Target NameParameterTypeAllowed Values / FormatRequiredMode / VisibilityDescription
    Database Table Write Target  Action
    ↳StringINSERT, UPDATE, DELETEYesAll actionsSQL operation type.
    ↳UpdateModeStringSTRICT, WHERENo (default STRICT)`Action=UPDATE\DELETE`
    ↳SetFieldsStringComma-separated column listNoAction=UPDATE & UpdateMode=WHEREColumns updated in the SET clause. Exposes matching input ports for column values.
    ↳WhereFieldsStringComma-separated column listNo`Action=UPDATE\DELETE & UpdateMode=WHERE`
    ↳WhereOperatorStringAND, ORNo (default AND)`Action=UPDATE\DELETE & UpdateMode=WHERE`
    ↳WhereStringRaw SQL expressionNo`Action=UPDATE\DELETE & UpdateMode=WHERE`
    ↳Discovered Column PortsTypedMapped SQL data typesDependentINSERT or STRICT UPDATEInput ports corresponding to table columns.
    ↳where_<column> PortsTypedMapped SQL data typesDependentWHERE modeFilter value input ports for columns specified in WhereFields.
    Stored Procedure Target  Procedure Parameter Ports
    ↳TypedMapped SQL data typesDependentStored procedure executionInput arguments required by the target stored procedure.

    5. Usage Examples

    Scenario A: Avigilon ACC Analytics Match Inserts Record into PostgreSQL Audit Table

    Workflow Overview:

    When Avigilon ACC emits an analytics rule match (ANALYTICS_RULE_MATCH), Uniflow intercepts the camera alarm and executes a SQL INSERT query via DB Link into PostgreSQL table security_incidents (camera_name, rule_name, timestamp).

    Rule Node Configuration:

    1. Event Input Node: Avigilon ACC Listener

  • Event Code: ANALYTICS_RULE_MATCH
  • Exposed Fields: CameraName (String), RuleName (String), Timestamp (DateTime)

    • 2. Logic Pass-Through Node: SQL Parameter Builder

  • Mapping: CameraName, RuleName, Timestamp

    • 3. Output Target Node: DB Link Target

  • Action Target: Database Table Write Target (dblink.insert_row)
  • Action: INSERT
  • camera_name: ${CameraName}
  • rule_name: ${RuleName}
  • timestamp: ${Timestamp}

    • Logic Flow Diagram:

    VISUAL ARCHITECTURE FLOW DIAGRAM
    Rendering Flow Architecture Diagram...

    Scenario B: Database Work-Order Table Poller Triggers Modbus Batch Start Coil

    Workflow Overview:

    A DB Link table poller monitors MSSQL view vw_PendingWorkOrders (dblink.row_count_changed). When RowCount > 0, Uniflow executes a Modbus TCP coil write (Coil 3 = True) to trigger physical batch processing on the plant floor.

    Rule Node Configuration:

    1. Input Source Node: DB Link Table Poller

  • Target View: vw_PendingWorkOrders
  • Exposed Field: Table Row Count (Int64)

    • 2. Logic Filter Node: GreaterThan

  • Expression: RowCount > 0

    • 3. Output Target Node: Modbus Client Writer

  • Action Target: Direct Access
  • Type: Coils
  • Address: 3
  • Value: True

    • 4. Output Target Node: DB Link Status Updater

  • Action Target: Database Table Write Target
  • Action: UPDATE
  • UpdateMode: STRICT
  • order_id: ${OrderId} (Primary Key)
  • status: 'PROCESSING'

    • Logic Flow Diagram:

    VISUAL ARCHITECTURE FLOW DIAGRAM
    Rendering Flow Architecture Diagram...

    Scenario C: Criteria-Based Bulk Maintenance Update in WHERE Mode

    Workflow Overview:

    When an emergency perimeter evacuation alarm triggers, Uniflow executes a bulk database update on table access_turnstiles to set lock_state = 'EMERGENCY_OPEN' for all gates where building = 'HQ_MAIN' without needing individual primary keys.

    Rule Node Configuration:

    1. Event Input Node: Fire Alarm Trigger

  • Event Code: FIRE_ALARM_EVACUATION

    • 2. Output Target Node: DB Link Turnstile Updater

  • Action Target: Database Table Write Target
  • Action: UPDATE
  • UpdateMode: WHERE
  • SetFields: lock_state
  • WhereFields: building
  • WhereOperator: AND
  • lock_state Input Port: 'EMERGENCY_OPEN'
  • where_building Input Port: 'HQ_MAIN'

    • Logic Flow Diagram:

    VISUAL ARCHITECTURE FLOW DIAGRAM
    Rendering Flow Architecture Diagram...
    Architecture Flow Diagram — Full Preview