Tutorial : create a custom SQL collector

Introduction

The core folder provides an abstract class SQLCollector which can serve as the basis for quickly creating collectors that retrieve their data via a SQL query.

To create such a collector you need to:

Create a class derived from SQLCollector
Create the json definition file for the data synchro source
Add a configuration parameter (in params.local.xml) to define the SQL query to run
Register your collector in collectors/main.php

Collector class

Let's create a very simple SQL collector which copies the “Notes” documents (class DocumentNote) from one iTop instance to another. Since the collector inherits all its behavior from the base class, the PHP code for the collector is simply:

collectors/DocumentNotesCollector.class.inc.php

<?php
class DocumentNoteCollector extends SQLCollector
{
}

Starting with version 1.0.10, the framework provides a new class of collector: MySQLCollector. This class is identical to SQLCollector except that it forces the retrieved data to be encoded in UTF-8 by issuing the SQL command

SET NAMES
'utf8

' at the beginning of the each connection to the database. To avoid any problem with the character set of the data, it is recommended to use this new class for all connections to a MySQL/MariaDB database.

Synchro data source definition file

collectors/DocumentNotesCollector.json

{
        "name": "$prefix$DocumentNote",
        "description": "",
        "status": "production",
        "user_id": "$synchro_user$",
        "notify_contact_id": "$contact_to_notify$",
        "scope_class": "DocumentNote",
        "database_table_name": "",
        "scope_restriction": "",
        "full_load_periodicity": "604800",
        "reconciliation_policy": "use_attributes",
        "action_on_zero": "create",
        "action_on_one": "update",
        "action_on_multiple": "error",
        "delete_policy": "update",
        "delete_policy_update": "status:obsolete",
        "delete_policy_retention": "0",
        "attribute_list": [
                {
                        "attcode": "cis_list",
                        "update": "0",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "row_separator": "|",
                        "attribute_separator": ";",
                        "value_separator": ":",
                        "attribute_qualifier": "'",
                        "finalclass": "SynchroAttLinkSet",
                        "friendlyname": "cis_list"
                },
                {
                        "attcode": "contracts_list",
                        "update": "0",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "row_separator": "|",
                        "attribute_separator": ";",
                        "value_separator": ":",
                        "attribute_qualifier": "'",
                        "finalclass": "SynchroAttLinkSet",
                        "friendlyname": "contracts_list"
                },
                {
                        "attcode": "description",
                        "update": "1",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "finalclass": "SynchroAttribute",
                        "friendlyname": "description"
                },
                {
                        "attcode": "documenttype_id",
                        "update": "1",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "reconciliation_attcode": "",
                        "finalclass": "SynchroAttExtKey",
                        "friendlyname": "documenttype_id"
                },
                {
                        "attcode": "name",
                        "update": "1",
                        "reconcile": "1",
                        "update_policy": "master_locked",
                        "finalclass": "SynchroAttribute",
                        "friendlyname": "name"
                },
                {
                        "attcode": "org_id",
                        "update": "1",
                        "reconcile": "1",
                        "update_policy": "master_locked",
                        "reconciliation_attcode": "",
                        "finalclass": "SynchroAttExtKey",
                        "friendlyname": "org_id"
                },
                {
                        "attcode": "services_list",
                        "update": "0",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "row_separator": "|",
                        "attribute_separator": ";",
                        "value_separator": ":",
                        "attribute_qualifier": "'",
                        "finalclass": "SynchroAttLinkSet",
                        "friendlyname": "services_list"
                },
                {
                        "attcode": "status",
                        "update": "1",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "finalclass": "SynchroAttribute",
                        "friendlyname": "status"
                },
                {
                        "attcode": "text",
                        "update": "1",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "finalclass": "SynchroAttribute",
                        "friendlyname": "text"
                },
                {
                        "attcode": "version",
                        "update": "1",
                        "reconcile": "0",
                        "update_policy": "master_locked",
                        "finalclass": "SynchroAttribute",
                        "friendlyname": "version"
                }
        ],
        "user_delete_policy": "nobody",
        "url_icon": "",
        "url_application": ""
}

Configuration

Then in conf/params.local.xml, add the following entries:

conf/params.local.xml

  <sql_database>test</sql_database>
  <sql_login>root</sql_login>
  <sql_password>s3cret</sql_password>
  <documentnotecollector_query>SELECT id as primary_key, name, text, description, status, '2.0' as version, documenttype_id, 1 as org_id FROM view_DocumentNote</documentnotecollector_query>
  <documentnotecollector_ignored_attributes type="array">
    <attribute>location_id</attribute>
    <attribute>version_id</attribute>
  </documentnotecollector_ignored_attributes>

Registering collector

Finally, in collectors/main.php add the following lines:

collectors/main.php

<?php
require_once(APPROOT.'collectors/DocumentNoteCollector.class.inc.php');
Orchestrator::AddCollector(1 /* $iRank */, 'DocumentNoteCollector');

Usage

To launch the data collection and synchronization with iTop, run the following command (from the root directory where the application is installed):

php exec.php

Sidebar