Search docs.molo17.com
    A newer version of this documentation is available.
    View Latest (v2.3)

    Advanced data modeling from RDBMS

    In this chapter you will understand the principles of the newly introduced feature available starting from version 1.4 specifically designed for a wide range of data modeling/data transformation applications: we named this feature Advanced Data Modeling. All of the operations involved in that feature are performed on the fly without involving local caches or any form of persistence inside Gluesync, this makes the overall process faster, more secure and consistent.

    Data modeling in Gluesync makes use of what we call virtual entities, if you’re not familiar with it we suggest having a look at this page, it will take less than 1 minute to read.

    Advanced data modeling

    When we designed the new data modeling feature we took into consideration a few main aspects, like:

    • freedom to define relationships between tables;

    • Choose if data should be represented as nested in different level of deep or just flat;

    • compatibility across multiple sources: not only RDBMS but also NoSQL databases;

    • easiness to hand-write but also future-proof when it will be managed via an API / GUI (coming soon);

    • fast to compute;

    • no new language to learn: SQL-like statements easy to recall;

    • easy to be extended in future releases;

    Taking these as main principles we came up with a meta-description language that is now the core foundation of that functionality.

    See it in action here in this video:

    The meta description language

    The description language helps you define what will be the content of your virtual entity that will then become a JSON object.

    Here following is the list of available commands and their description:

    Key Description Accepted values

    level

    Level of deep of parenthood for JSON objects

    1 or 2

    name

    Defines the name of the entity

    string

    tables

    Array of tables that are involved in a JOIN-like statement, each table is described using these elements here on the left
    Key Description Accepted values

    table

    Name of the table involved

    string

    fields

    Object representing the list of columns that will selected and used for the output. The mapping works exactly as per the basic config of Gluesync: values are the resulting target’s names used in your JSON

    		 
    {
  "id": "id",
  "order_number": "order_number",
  "notes": "notes"
}

    alias

    (optional) Value used by the query engine to distinguish between repeated usages of the same table in the same context

    string

    where

    (optional) Same per standard SQL language, specifies the clause applied to the filter

    string, ex. status = 1

    fromTable

    Represent the master table name that is involved in your query

    boolean (true or false), defaults to false

    join (if foreign keys are in place)

    Recalls standard SQL-like JOIN statement, specifies the table that has to be joined together in this context. It automatically manages the traverse of relationships between tables' foreign keys (if any).

    		 
    {
  "with": "orders_headers"
}

    join (when NO foreign key is involved)

    Recalls standard SQL-like JOIN statement, specifies the table that has to be joined together in this context. It makes use of "on" clauses to force the engine to aggregate data between involved tables in specified statements. This can be used when your table has NO foreign keys declared. Note: don’t forget to use defined aliases.

    		 
    {
  "with": "orders_headers",
  "on": [
    "oh.id = c.id",
    ...
  ]
}

    keys

    Map of keys/values representing the keys and their aliases for the involved table

    		 
    {
  "id_customer": "customerId"
}

    Bring everything together

    Following a virtual entity defined using the above keys:

    "order": {
  // You need to define which feature you intend to enable when parsing the following object. Here we are declaring that our virtual entity will be treated as a "dataModeling" entity
  "dataModeling": [
      {
        "level": 1,
        "name": "orders",
        "tables":
        [
          {
            "table": "orders_headers",
            "keys":
            {
              "id": "id"
            },
            "fields":
            {
              "id": "id",
              "order_number": "order_number",
              "notes": "notes"
            },
            "alias": "oh",
            "where": "status = 1",
            "fromTable": true
          },
          {
            "table": "customers",
            "keys":
            {
              "id": "id"
            },
            "fields":
            {
              "name": "name",
              "surname": "surname",
              "phone": "phone"
            },
            "alias": "c",
            "join":
            {
              "with": "orders_headers",
              // Use only if foreign keys are not defined on table schemas
              "on": [
                "oh.id = c.id"
              ]
            }
          }
        ]
      },
      {
        "level": 1,
        "name": "address",
        "tables":
        [
          {
            "table": "addresses",
            "keys":
            {
              "id": "id"
            },
            "fields":
            {
              "street": "street",
              "street_number": "street_number",
              "city": "city",
              "postal_code": "postal_code"
            },
            "alias": "ad",
            "join":
            {
              "with": "orders_headers"
            }
          }
        ]
      },
      {
        "level": 2,
        "name": "order_rows",
        "tables":
        [
          {
            "table": "orders_rows",
            "keys":
            {
              "id": "row_id"
            },
            "fields":
            {
              "id": "row_id",
              "order_id": "order_id",
              "quantity": "quantity",
              "notes": "row_notes"
            },
            "alias": "o",
            "join":
            {
              "with": "orders_headers"
            }
          },
          {
            "table": "articles",
            "keys":
            {
              "id": "id"
            },
            "fields":
            {
              "article_name": "article_name",
              "description": "description"
            },
            "alias": "a",
            "join":
            {
              "with": "orders_rows"
            }
          }
        ]
      }
    ]
}

    as a JSON document results in your NoSQL database, you’ll then have:

    {
  "id": 1,
  "order_number": "SO-55052681",
  "notes": "this is a test",
  "name": "Bernard",
  "surname": "Worham",
  "phone": "+46 758 348 3574",
  "street": "Rigney",
  "street_number": 53,
  "city": "Dubai",
  "postal_code": "33170",
  "order_rows": [
    {
      "row_id": 38,
      "order_id": 1,
      "quantity": 898,
      "row_notes": "Total foreground function",
      "article_name": "Beans - Navy, Dry",
      "description": "Re-contextualized full-range service-desk"
    },
    {
      "row_id": 98,
      "order_id": 1,
      "quantity": 172,
      "row_notes": "Enterprise-wide attitude-oriented complexity",
      "article_name": "Muffin Mix - Blueberry",
      "description": "Right-sized high-level paradigm"
    },
    {
      "row_id": 230,
      "order_id": 1,
      "quantity": 810,
      "row_notes": "Horizontal non-volatile core",
      "article_name": "Papayas",
      "description": "Stand-alone responsive standardization"
    },
    {
      "row_id": 393,
      "order_id": 1,
      "quantity": 742,
      "row_notes": "Optimized executive hub",
      "article_name": "Gooseberry",
      "description": "Devolved bifurcated moratorium"
    },
    ...
  ],
  "type": "order",
  "scope": "myscope"
}

    Key fields involved in Advanced data modeling

    • dataModeling: the key that tells the engine how to treat the following object, specifying that key enables Advanced data modeling over the object;

    • type: every modeled document stored on the target database will have a key called type with the given custom value;

    • scope (optional): will be used as source/target scope in NoSQL databases supporting this kind of data tenancy, like Couchbase for example.

    type and scope keys have been recently introduced with version 1.5, to learn more please take a look at our version changelog by following this link: Changes in Advanced data modeling.

    Compatibility matrix

    RDBMS Gluesync compatibility

    DB2 for i series (AS/400)

    ✅

    DB2 w/ GDC (triggers)

    ⏱ launching soon

    MariaDB w/ GDC (triggers)

    -

    Microsoft SQL Server w/ Change Tracking

    ✅

    Microsoft SQL Server w/ CDC

    ✅

    MySQL w/ GDC (triggers)

    -

    Oracle w/ Xstream

    ⏱ launching soon

    Oracle w/ GDC (triggers)

    ✅

    PostgreSQL w/ GDC (triggers)

    -

    PostgreSQL w/ CDC

    ✅

    Sybase w/ GDC (triggers)

    -
    As per our product roadmap relational databases are being added every month so expect to have Advanced data modeling support on more databases soon after the initial launch.