.. ==================================================
.. SyncByRule - Quick Start Guide
.. ==================================================

.. _`syncByRule`:

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:

.. code-block:: sql

   {
      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:

.. code-block:: sql

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

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

.. _`syncByRule-syntax`:

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>


.. _`syncByRule-json-structure`:

JSON Structure
--------------

Export Rule
^^^^^^^^^^^

.. code-block:: javascript

   {
     "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
^^^^^^^^^^^^^^^

.. code-block:: javascript

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

.. _`syncByRule-identifier`:

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                         |
+-----------------------+--------------------------------------------------+

.. _`syncByRule-update-modes`:

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    |
+------------------+-----------------------------------------------+

.. _`syncByRule-relations`:

Relations (Nested Elements)
---------------------------

Export parent with children:

.. code-block:: sql

   {
      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"}``.

.. _`syncByRule-subrecord`:

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)

.. _`syncByRule-validation`:

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.

.. _`syncByRule-examples`:

Practical Examples
------------------

Export All Records with Condition
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code-block:: sql

   {
      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
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code-block:: sql

   {
      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:

.. code-block:: sql

   {
      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:

.. code-block:: sql

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

Use in report:

.. code-block:: sql

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

.. _`syncByRule-deployment`:

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``

.. code-block:: bash

   # 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:

.. code-block:: bash

   # 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:

.. code-block:: bash

   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 |
+---------------------+---------------------------+---------------------------+