Getting Started

You have an Oracle AI Database 26ai and want to display SQL Property Graphs? Great! Let's see how to achieve that with Ogma, a powerful and blazing fast graph visualization library.

Create your Oracle AI Database 26ai Free instance

You can have a look at our example, which allows you to visualize a sample SQL Property Graph in minutes using Podman.

Oracle provides great resources on how to create and use Property Graphs in your Oracle AI Database:

Functions in Oracle AI Database 26ai to return graph query results as JSON

OGMA accepts the result set from SQL graph query (returned nodes, edges, and their properties) in JSON format only. The transformation to JSON relies on the DBMS_GVT PL/SQL package available on GitHub. The package and a PL/SQL helper function, CUST_SQLGRAPH_JSON, are created upon the creation of the Oracle Database container. (See the scripts in this folder).

GVT is the abbreviation for Graph Visualization Toolkit.

Further details are available in:

Retrieve your nodes/edges from the database in Node.js

First, install the Ogma, the Oracle AI Database 26ai connector and ogma-oracle-parser:

npm i oracledb @linkurious/ogma @linkurious/ogma-oracle-parser

Create your DB connection:

const connectString = host + ":" + port + "/" + service;
oracledb.getConnection({
  user,
  password,
  connectString,
});

Then run your first command:

app.get("/nodes/:type", (req, res) => {
  const query = `select v
          from graph_table (
            openflights_graph
            match (v1 is ${req.params.type})
            columns (
              VERTEX_ID(v1) as v
            )
          )`;
  return conn.execute(query).then((r) => res.json(r));
});

You can see that the result should look like

{
  metadata: {
    name: 'V',
    dbType: [DbType DB_TYPE_JSON],
    nullable: true,
    dbTypeName: 'JSON',
    fetchType: [DbType DB_TYPE_JSON]
  },
  rows: [
    {
      GRAPH_OWNER: 'GRAPHUSER',
      GRAPH_NAME: 'OPENFLIGHTS_GRAPH',
      ELEM_TABLE: 'CITIES',
      KEY_VALUE: { ID: 1 }
    }
  ]
}

Now, we can use the CUST_SQLGRAPH_JSON function to retrieve the nodes and edges along with their properties from the IDs we got from the previous request:

import { parseLob } from "@linkurious/ogma-oracle-parser";
...

app.get("/nodes/:type", (req, res) => {
  const query = `select v
          from graph_table (
            openflights_graph
            match (v1 is ${req.params.type})
            columns (
              VERTEX_ID(v1) as v
            )
          )`;
  const pageStart = 0;
  const pageLength = 3200;
  return conn
    .execute<Lob[]>(
      `SELECT CUST_SQLGRAPH_JSON('${query}', ${pageStart}, ${pageLength}) AS COLUMN_ALIAS FROM DUAL`
    )
    .then((result) => {
      const { numResults, nodes, edges } = parseLob(result.rows[0][0]);
      return { nodes, edges };
    });
});

Now, what you get is this:

{
  nodes: [
    {
      id: 'vlabel-id',
      data: {
        ...
      }
    },
    ...
  ],
  edges: [
    {
      id: 'elabel-id',
      source: 'vlabel-id',
      target:'vlabel-id'
      data: {
        ...
      }
    },
    ...
  ]
}

vlabel and elabel are the labels you have passed to SQL in your CREATE PROPERTY GRAPH call. -id is the ID of your element in the table. And that's it ! You have retrieved the nodes and edges of a Property Graph in the Ogma format

The plugin also provides a getRawGraph function that does all the work for you. You can use it like this:

import { getRawGraph } from "@linkurious/ogma-oracle-parser";
...

app.get("/nodes/:type", (req, res) => {
  const query = `select v
          from graph_table (
            openflights_graph
            match (v1 is ${req.params.type})
            columns (
              VERTEX_ID(v1) as v
            )
          )`;
  return getRawGraph(conn, query).then(({ nodes, edges }) => {
    return { nodes, edges };
  });
});

Display your nodes in Ogma

Let' s assume you already have a client side project. Just install Ogma:

npm install @linkurious/ogma

Create your Ogma instance:

import { Ogma } from "@linkurious/ogma";
import axios from "axios";
const ogma = new Ogma({
  container: "id-of-your-container",
});

axios.get("http://url-to-node-server:port/nodes/VLABEL").then(({ data }) => {
  ogma.setGraph(data);
  return ogma.layouts.force();
});

And you are done !

Customize your node/edge IDs

By default, the plugin transforms the label:{"ID": id} into label-id. You can customize this behaviour by creating an instance of the OgmaOracleParser class"

import { OgmaOracleParser } from "@linkurious/ogma-oracle-parser";

const { parse, parseLob, getRawGraph } = new OgmaOracleParser({
  SQLIDtoId: (label, id) => `${label}-${id}`,
  SQLIDfromId: (id) => {
    const [label, id] = id.split("-");
    return `${label}:{"ID": ${id}}`;
  },
});

Node and edge data types

You can type the data of your nodes and edges by passing ND and ED value in the OgmaOracleParser constructor:

type NodeDataType = { name: string; id: number };
type EdgeDataType = { score: number };
const { parse, parseLob, getRawGraph } = new OgmaOracleParser<
  NodeDataType,
  EdgeDataType
>();

Getting Started ​

Create your Oracle AI Database 26ai Free instance ​

Functions in Oracle AI Database 26ai to return graph query results as JSON ​

Retrieve your nodes/edges from the database in Node.js ​

Display your nodes in Ogma ​

Customize your node/edge IDs ​