SyncByRule

SyncByRule enables export and import of relational database records via JSON. Records can be copied between browsers (clipboard) or deployed between databases.

Quick Start

Export Button

Create an export button in a QFQ Report:

{
   sql = SELECT CONCAT('export|t:Export||{"dataType":"PersonData","element":[{
             "table":"Person",
             "data":[{"id":', p.id, '}],
             "identifier":"email"
           }]}') AS _link
         FROM Person AS p
         WHERE p.id = {{id:S}}
}

Import Button

Create an import button:

{
   sql = SELECT 'import|t:Import||{"dataType":"PersonData"}' AS _link
}

The dataType must match between export and import to prevent accidental wrong imports.

Syntax

Export

export|t:<button text>||<JSON rule>

or using a stored rule:

export|t:<button text>||<ruleName>

Import

import|t:<button text>||<JSON override>

JSON Structure

Export Rule

{
  "dataType": "MyDataType",           // Optional: validation identifier
  "element": [
    {
      "table": "TableName",           // Required: table name
      "data": [{"id": 123}],          // Optional: filter by ID(s)
      "identifier": "email",          // Optional: duplicate check column(s)
      "condition": "status='active'", // Optional: SQL WHERE clause
      "update": "once",               // Optional: once|always|skipModified
      "element": [...]                // Optional: nested child elements
    }
  ]
}

Import Override

{
  "dataType": "MyDataType",           // Must match export dataType
  "element": [
    {
      "table": "TableName",
      "data": [{"targetColumn": "value"}]  // Values to override on import
    }
  ]
}

Identifier Options

Identifier	Behavior
email	Check single column for duplicates
firstName-lastName	Check multiple columns (dash-separated)
*	Check all columns (except id, created, modified)
null / omitted	Always insert new record

Update Modes

Mode	Behavior
once	Insert only if not exists (default)
always	Always overwrite existing record
skipModified	Update only if source is newer than target

Relations (Nested Elements)

Export parent with children:

{
   sql = SELECT CONCAT('export|t:Export Person||{
             "dataType":"PersonWithRoles",
             "element":[{
               "table":"Person",
               "data":[{"id":', p.id, '}],
               "identifier":"email",
               "element":[{
                 "table":"RoleAssign",
                 "relation":{"pId":"Person.id"},
                 "identifier":"pId-grId"
               }]
             }]}') AS _link
         FROM Person AS p
         WHERE p.id = {{id:S}}
}

The relation defines the foreign key mapping: {"childColumn": "ParentTable.column"}.

Subrecord Integration

SyncByRule integrates directly into FormElement subrecords via FE.parameter.

Parameters

Parameter	Description
copyRule	Export rule (enables checkboxes + export button)
copyRuleImport	Import rule (enables import button)

Example

In FormElement of type subrecord:

FE.parameter:
  copyRule = {"dataType":"RoleData","element":[{"table":"RoleAssign","data":{"id":{{id:P0}} },"identifier":"*","element":[{"table":"Grp","relation":{"id":"RoleAssign.grId"},"identifier":"reference"}]}]}
  copyRuleImport = {"dataType":"RoleData","element":[{"table":"RoleAssign","data":[{"pId":{{id:R0}} }]}]}

• {{id:P0}} - ID of current subrecord row

• {{id:R0}} - ID of parent record

This automatically generates:

• Checkbox column for row selection

• Export button (copies selected records to clipboard)

• Import button (imports clipboard data)

Validation

dataType Validation

Prevents importing data to wrong target. If dataType is defined in export or import, both must match.

Export dataType	Import dataType	Result
PersonData	PersonData	✓ Import allowed
PersonData	OtherData	✗ Exception
PersonData	(not defined)	✗ Exception
(not defined)	(not defined)	Table check

Checksum Verification

Prevents manipulation of exported JSON. Automatically generated during export, verified during import via API call to source server.

Practical Examples

Export All Records with Condition

{
   sql = SELECT 'export|t:Export Active Users||{
             "dataType":"UserExport",
             "element":[{
               "table":"Person",
               "data":null,
               "identifier":"email",
               "condition":"status=''active''"
             }]}' AS _link
}

Export Form with FormElements

{
   sql = SELECT CONCAT('export|t:Export Form||{
             "dataType":"FormExport",
             "element":[{
               "table":"Form",
               "data":[{"id":', f.id, '}],
               "identifier":"name",
               "element":[{
                 "table":"FormElement",
                 "relation":{"formId":"Form.id"},
                 "identifier":"name-formId"
               }]
             }]}') AS _link
         FROM Form AS f
         WHERE f.name = 'myForm'
}

Import with Override

Import roles to a specific person:

{
   sql = SELECT CONCAT('import|t:Import Roles||{
             "dataType":"RoleData",
             "element":[{
               "table":"RoleAssign",
               "data":[{"pId":', p.id, '}]
             }]}') AS _link
         FROM Person AS p
         WHERE p.id = {{id:S}}
}

Using Stored Rules

Store rules in DeployRule table:

INSERT INTO DeployRule (name, definition, description) VALUES (
    'personExport',
    '{"dataType":"PersonData","element":[{"table":"Person","identifier":"email"}]}',
    'Export person records'
);

Use in report:

{
   sql = SELECT 'export|t:Export||personExport' AS _link
}

Deployment Mode (CLI)

SyncByRule can be used for automated deployments between databases via command line.

Rules Management

To access the rules listing and management interface, add the following parameter to any QFQ page:

file=_syncByRule

This displays an overview of all stored DeployRule records with options to create, edit, and delete rules.

PHP Script

Location: typo3conf/ext/qfq/Classes/External/sync-by-rule.php

# Export using stored rules
php sync-by-rule.php export "RuleName1,RuleName2"

# Import from file
php sync-by-rule.php import @/path/to/export.json

# Import with overrides
php sync-by-rule.php import @/path/to/export.json @/path/to/overrides.json

Shell Script Usage

The syncByRule.sh script automates sync between servers:

# Basic usage
./syncByRule.sh "FormExport,WikiExport"

# With overrides (e.g., assign to different parent)
./syncByRule.sh "PersonExport" '{"element":[{"table":"Person","data":[{"id":600}]}]}'

Configuration via environment variables:

export G_SOURCE_HOST=source.example.com
export G_TARGET_HOST=target.example.com
export G_SOURCE_PATH=/var/www/html/app
export G_TARGET_PATH=/var/www/html/app-reference
./syncByRule.sh "MyRules"

UI vs Deployment Mode

Feature	UI Mode (Browser)	Deployment Mode (CLI)
Validation	Active (dataType,checksum)	Skipped (trusted source)
DeployRef Tracking	Disabled	Enabled
Duplicate Detection	Identifier-based	DeployRef + Identifier
Use Case	Copy/paste between users	Automated Dev → Prod sync