Example 4-4 Synchronizing Graphs Using CREATE PROPERTY GRAPH Statement

1. Assume you have the following Oracle Database tables, PERSONS and FRIENDSHIPS.

   CREATE TABLE persons (
       person_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
       name VARCHAR2(200),
       birthdate DATE,
       height FLOAT DEFAULT on null 0,
       CONSTRAINT person_pk PRIMARY KEY (person_id)
   );
    
   CREATE TABLE friendships (
     friendship_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
     person_a NUMBER,
     person_b NUMBER,
     meeting_date DATE,
     CONSTRAINT fk_person_a_id FOREIGN KEY (person_a) REFERENCES persons(person_id),
     CONSTRAINT fk_person_b_id FOREIGN KEY (person_b) REFERENCES persons(person_id)
     CONSTRAINT fs_pk PRIMARY KEY (friendship_id)
   );

2. You can add some sample data into these tables as shown:

   INSERT INTO persons (name, height, birthdate) VALUES ('John', 1.80, to_date('13/06/1963', 'DD/MM/YYYY'));
   INSERT INTO persons (name, height, birthdate) VALUES ('Mary', 1.65, to_date('25/09/1982', 'DD/MM/YYYY'));
   INSERT INTO persons (name, height, birthdate) VALUES ('Bob', 1.75, to_date('11/03/1966', 'DD/MM/YYYY'));
   INSERT INTO persons (name, height, birthdate) VALUES ('Alice', 1.70, to_date('01/02/1987', 'DD/MM/YYYY'));
    
   INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (1, 3, to_date('01/09/1972', 'DD/MM/YYYY'));
   INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (2, 4, to_date('19/09/1992', 'DD/MM/YYYY'));
   INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (4, 2, to_date('19/09/1992', 'DD/MM/YYYY'));
   INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (3, 2, to_date('10/07/2001', 'DD/MM/YYYY'));
   

3. Write the corresponding CREATE PROPERTY GRAPH statement which describes how to load those tables as a graph as shown in the following Java code example:

   session.executePgql(
       "CREATE PROPERTY GRAPH friends VERTEX TABLES ("
           + "  persons KEY (person_id) LABEL person PROPERTIES (name,height,birthdate)"
           + ")"
           + "EDGE TABLES ("
           + "  friendships "
           + "    KEY (friendship_id) "
           + "    SOURCE KEY (person_a) REFERENCES persons "
           + "    DESTINATION KEY (person_b) REFERENCES persons "
           + "    LABEL friendof PROPERTIES (meeting_date)"
           + ")"
   );
   PgxGraph graph = session.getGraph("friends");

   This creates a snapshot of the graph which is loaded into memory. You can now run algorithms and queries on the graph.

4. Now change the data in the input tables in the database. For example, add new persons to the PERSONS table and also add another edge.

   You can open a new JDBC connection to the database and run a few INSERT statements as shown in the following code:

   Connection conn = DriverManager.getConnection("<jdbc-url>", "<user>", "<pass>");
   conn.createStatement().executeQuery("INSERT INTO persons(name, birthdate, height) VALUES ('Mariana',to_date('21/08/1996','DD/MM/YYYY'),1.65)");
   conn.createStatement().executeQuery("INSERT INTO persons (name, birthdate, height) VALUES ('Francisco',to_date('13/06/1963','DD/MM/YYYY'),1.75)");
   conn.createStatement().executeQuery("INSERT INTO friendships (person_a, person_b, meeting_date) VALUES (1, 6, to_date('13/06/2013','DD/MM/YYYY'))");
   conn.commit();

   Committing the changes to the database causes the graph in memory to became out of sync with the database source tables.

5. You can synchronize the in-memory graph with the database by creating a new synchronizer object as shown in the following code:

   Synchronizer synchronizer = new Synchronizer.Builder<FlashbackSynchronizer>()
       .setType(FlashbackSynchronizer.class)
       .setGraph(graph)
       .setConnection(conn)
       .build();

   Internally, the graph server keeps track of the Oracle system change number (SCN) the current graph snapshot belongs to. The synchronizer is a client-side component which connects to the database, detects changes by comparing state of the the original input tables using the current SCN via the flashback mechanism and then sends any changes to the graph server using the changeset API. In order to do so, the synchronizer needs to know how to connect to the database (conn parameter) as well as which graph to keep in sync (graph parameter).
   • Alternatively, you can use this equivalent shortcut:

     Synchronizer synchronizer = graph.createSynchronizer(FlashbackSynchronizer.class, conn);

6. Call the sync() operation, to fetch the database changes and create a new in-memory snapshot:

   graph = synchronizer.sync();

   You will notice that the two new vertices and the new edge have been applied to the graph:

   graph ==> PgxGraph[name=FRIENDS,N=6,E=5,created=1594754376861]

   Splitting the Fetching and Applying of Changes

   The synchronizer.sync() invocation in the preceding code, fetches the changes and applies them in one call. However, you can encode a more complex update logic by splitting this process into separate fetch() and apply() invocations. For example:

   synchronizer.fetch() // fetches changes from the database
   if (synchronizer.getGraphDelta().getTotalNumberOfChanges() > 100) {  // only create snapshot if there have been more than 100 changes
     synchronizer.apply()
   }

Example 4-5 Synchronizing Graphs Created Via Graph Configuration Objects

Example 4-4 uses a CREATE PROPERTY GRAPH statement to create the graph which hides some of the more advanced graph configuration options.

Though synchronization of graphs created via graph configuration objects is supported in general, the following few limitations apply:

• Only partitioned graph configurations with all providers being database tables are supported.
• Each edge or vertex provider or both must specify the owner of the table by setting the username field. For example, if user SCOTT owns the table, then set the username accordingly in the provider block of that table:

  "username": "scott"

• In the root loading block, the snapshot source must be set to change_set:

  "loading": {
    "snapshots_source": "change_set"
  }

• It is highly recommended to set the "optimized_for" field to "updates" to avoid memory exhaustion when creating many snapshots:

  "optimized_for": "updates"

You can load the same graph shown in Example 4-4 using the following graph configuration (JSON) file:

{
  "name": "friends",
  "optimized_for": "updates",
  "vertex_id_strategy": "partitioned_ids",
  "edge_id_strategy": "partitioned_ids",
  "edge_id_type": "long",
  "vertex_id_type": "long",
  "jdbc_url": "<jdbc_url>",
  "username": "<username>",
  "keystore_alias": "<keystore_alias>",
  "vertex_providers": [
    {
      "format": "rdbms",
      "username": "<username>",
      "key_type": "long",
      "name": "person",
      "database_table_name": "persons",
      "key_column": "person_id",
      "props": [
        ...
      ],
      "loading": {
        "create_key_mapping": true
      }
    }
  ],
  "edge_providers": [
    {
      "format": "rdbms",
      "username": "<username>",
      "name": "friendOf",
      "source_vertex_provider": "person",
      "destination_vertex_provider": "person",
      "database_table_name": "friendships",
      "source_column": "person_a",
      "destination_column": "person_b",
      "key_column": "friendship_id",
      "key_type":"long",
      "props": [
        ...
      ],
      "loading": {
        "create_key_mapping": true
      }
    }
  ],
  "loading": {
    "snapshots_source": "change_set"
  }
}