Advanced data modelling from RDBMS

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

Data modelling in Gluesync makes use of what we called virtual entities, if you’re not familiar with it we suggest to have a look at this page, it will take less than 1 min of reading.

Advanced data modelling

When we designed the new data modelling feature we took in consideration few main aspects, like:

freedom to define relationships between tables;
choose either data should be represented nested in different level of deep or just flat;
compatibility across multiple sources: not only RDBMS but also NoSQL databases;
easyness to hand-write but also future-proof when it will be managed via an API / GUI;
fast to compute;
no new language to learn: SQL-like statements easy to recall;
easy to extend in the future;

Taking these as main principles we come-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 than 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 distinct 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 odf relationships between tables' foreing 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 statement. 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": [
      {
        "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 tables 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 result in your NoSQL database you’ll 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"
}

Compatibiliy matrix

RDBMS	Gluesync compatibility
Microsoft SQL Server	✅
Oracle Database	⏱ launching soon
PostgreSQL	⏱ launching soon
MariaDB	⏱ launching soon
MySQL	⏱ launching soon
Sybase	⏱ launching soon
DB2 for i series (AS/400)	✅
DB2	⏱ launching soon

RDBMS

Gluesync compatibility

Microsoft SQL Server

✅

Oracle Database

⏱ launching soon

PostgreSQL

⏱ launching soon

MariaDB

⏱ launching soon

MySQL

⏱ launching soon

Sybase

⏱ launching soon

DB2 for i series (AS/400)

✅

DB2

⏱ launching soon

As per our product roadmap relational databases are being added on monthly basis so expect to have Advanced data modelling support on more databases soon after the initial launch.