22 June 2016
22 June 2016,
 Off

London, June 22, 2016

The latest version of OrientDB Teleporter makes it possible to define the configuration necessary in order to manage and customize your migration. Configuration is performed in order to affect the mapping between the schema of your source database and the schema of the target graph database in OrientDB. The first feature implementation enables one to intervene on the relationships of your source relational database and the resultant edges in the graph database.

Why is the configuration that maps the correspondence between relationships and edges so important? To answer this question it’s important to know how Teleporter works, in particular how it infers relationships present in the source database and how it builds a correspondent edge for each of them in the OrientDB graph schema. The first 2 steps performed by Teleporter are the mapping between the E-R Model of the source database and the building of a coherent Graph Model. If you want to know more about these steps you may refer to the first two blog posts or the official documentation.

 

E-R and Graph Model

 

All the info required to build the E-R model is fetched from the physical definition of the source database schema, then each element is translated semi-directly into the Graph Model as follows:

  • Each Entity is converted into a Vertex class.
  • Each Relationship between two Entities is converted into an Edge class.

Entities are inferred from tables defined in your schema, Relationships from foreign keys’ definitions. Therefore if you didn’t defined any foreign key between the tables on which you usually perform join operations, Relationships will not be recognized nor built and, as result, no Edge classes will be present in your final Graph Database. This can result in an incorrect or ineffective migration. For this reason, it’s essential to exploit this feature and provide proper configuration for Teleporter’s execution, thus ensuring that constraints missing in your database metadata are overcome.

Thanks to the configuration you can:

  • Add new Edge classes in the Graph Model even though relationships are missing.
  • Modify existing mappings between Relationships and Edge classes.
  • Define Edge classes for join tables during a migration exploiting the aggregation strategy.

In this article we will see how the first goal is achieved through the study of a simple user case. The other two scenarios will be analyzed in subsequent blog posts.

Let’s suppose we have the following E-R Model:

 

E-R Model

 

As you can see below, no foreign keys are defined at the physical layer between the Employee and Department tables, nor are there any defined between Department and City tables.

 

No Key Definition in Tables

 

With this approach, Relationships are lost during the Graph Model building, obtaining only the Vertex classes correspondent to the tables, but no Edge classes will be defined:

 

OrientDB Teleporter: no edge definition

 

So we must define relationships not declared in the database metadata and map them to the correspondent edges through the configuration. Let’s start creating a configuration file in JSON format.

The root field is edges, which contains an array of elements. Here we must define all the edges we want to have in our final graph. In fact, each element in the array is an Edge class definition, containing the mapping with a Relationship in the relational model.

 

This is the full configuration which must be submitted to Teleporter in order to achieve our goal.

{
  "edges": [{
   "LocatedIn": {
    "mapping": {
     "fromTable": "DEPARTMENT",
     "fromColumns": ["location"],
     "toTable": "CITY",
     "toColumns": ["id"],
     "direction": "direct"
   },
    "properties": {
     "since": {
      "type": "DATE",
      "mandatory": true,
      "readOnly": false,
      "notNull": false
    }
   }
  }
 }, {
   "WorksIn": {
    "mapping": {
     "fromTable": "EMPLOYEE",
     "fromColumns": ["department"],
     "toTable": "DEPARTMENT",
     "toColumns": ["id"],
     "direction": "direct"
  }
 }
}, {
  "IsManagerFor": {
   "mapping": {
    "fromTable": "DEPARTMENT",
    "fromColumns": ["manager"],
    "toTable": "EMPLOYEE",
    "toColumns": ["id"],
    "direction": "inverse"
   }
  }
 }]
}

 

Let’s take a closer look at each Relationship-Edge class mapping.

1. Relationship: Department-City, cardinality 1-N.

 

{
 "LocatedIn": {
  "mapping": {
   "fromTable": "DEPARTMENT",
   "fromColumns": ["location"],
   "toTable": "CITY",
   "toColumns": ["id"],
   "direction": "direct"
  },
  "properties": {
   "since": {
    "type": "DATE",
    "mandatory": true,
    "readOnly": false,
    "notNull": false
   }
  }
 }
}

 

The first field, LocatedIn, will be used as the name for the Edge class in the OrientDB graph. Inside it, we have two fields: mapping and properties. The mapping field enables the mapping of the Relationship with the LocatedIn Edge class on the basis of 4 essential values:

  • fromTable: The foreign entity that imports the primary key of the parent table. See the “DEPARTMENT” table in our example.
  • fromColumns: The attributes involved in the foreign key. See the “location” column in the “DEPARTMENT” table from our example.
  • toTable: The parent entity whose primary key is imported by the foreign table. See the “CITY” table in our example.
  • toColumns: The attributes involved in the primary key imported. See the “id” column in the “CITY” table from our example.

As a direction we chose direct, because we want edges to have the same direction of the Relationship, starting from Department and ending in City.

Moreover, we can define additional properties for the final Edge class, LocatedIn, by using the properties field.

 

"properties": {
    "since": {
    "type": "DATE",
    "mandatory": true,
    "readOnly": false,
    "notNull": false
  }

 

In this case, we have to set the name of the property (“since” in our example), and its attributes:

  • type: The OrientDB type. This value is mandatory. If not declared, the property is not added to the Edge.
  • mandatory: Adds the mandatory constraint to the property and applies to it the specified value (true or false).
  • readOnly: Adds the readOnly constraint to the property and applies to it the specified value (true or false).
  • notNull: Adds the notNull constraint to the property and applies to it the specified value (true or false).

So, in the example above we added a property named “since” of type OType.DATE to our Edge class.

2. Relationship: Employee-Department, cardinality 1-N.

 

{
  "WorksIn": {
   "mapping": {
    "fromTable": "EMPLOYEE",
    "fromColumns": ["department"],
    "toTable": "DEPARTMENT",
    "toColumns": ["id"],
    "direction": "direct"
  }
 }
}

 

Here we defined an Edge class for the Relationship between Employee and Department, choosing WorksIn as name. We did not declare any property for this Edge class.

3. Relationship: Department-Employee, cardinality 1-1.

 

{
 "IsManagerFor": {
  "mapping": {
   "fromTable": "DEPARTMENT",
   "fromColumns": ["manager"],
   "toTable": "EMPLOYEE",
   "toColumns": ["id"],
   "direction": "inverse"
  }
 }
}

 

In this case we are mapping the opposite Relationship between Department and Employee: this relationship specifies who is the manager for a specific department.
Suppose we want the Edge class to represent the opposite direction with respect to the Relationship. In order to achieve this, we use the inverse value in the direction field as well as coherent semantics for the Edge class name: thus, the edges belonging to the IsManagerFor class will start from the Employee vertices and will end into Department vertices.

Thanks to this configuration, we obtain the following Graph Model:

 

configured graph model with OrientDB Teleporter

 

That’s all, now we must submit the configuration file to Teleporter indicating the path of the JSON file through the argument -conf:

 

./oteleporter.sh -jdriver postgresql -jurl jdbc:postgresql://localhost:5432/testdb
          -juser username -jpasswd password
          -ourl plocal:$ORIENTDB_HOME/databases/testdb
          -conf /tmp/migration-config.json

 

After the first migration, the graph database will be built and the configuration you passed as argument to Teleporter will be copied into the database folder in a path such as the one below:

 

$ORIENDB_HOME/databases/testdb/teleporter-config/migration-config.json 

 

In the following executions, the new configuration in your database will be processed automatically, making it coherent and simplifying the synchronization procedure. If you want to change any settings you can modify the file directly.
In fact Teleporter, at execution time, sequentially looks for:

  1. the configuration file migration-config.json in the database directory $ORIENDB_HOME/databases/testdb/teleporter-config/
  2. if no config file is found, then a potential input config will be considered
  3. if no config file is passed as argument, the migration will be performed without any configuration

In the next blog post we will see how to modify existing relationships and use the configuration to our advantage with the aggregation strategy, so stay tuned!

References

Teleporter documentation index:
https://github.com/orientechnologies/orientdb-docs/blob/master/Teleporter-Index.md

Teleporter configuration:
https://github.com/orientechnologies/orientdb-docs/blob/master/Teleporter-Import-Configuration.md

Gabriele Ponzi
OrientDB LTD

Comments are closed.