Getting Started with the APEX Graph Visualization Plug-in

8.2 Getting Started with the APEX Graph Visualization Plug-in

This section helps you get started with the Graph Visualization plug-in in your APEX application.

Before you get started, ensure that your APEX workspace meets the following requirements:

The target application into which you want to import the plug-in exists.
The SQL property graph to be used for visualization exists in the default database schema.
Using the command editor in the SQL Workshop component, you can create a SQL property graph using the CREATE PROPERTY GRAPH DDL statement (see Using the Command Editor).

Download the Graph Visualization (Preview) plug-in from Oracle APEX GitHub repository.
Sign in to your APEX workspace (see Signing In to Your Workspace).
Import the downloaded plug-in script (region_type_plugin_graphviz.sql) file into your target APEX application (see Importing Plug-ins).
It is important to note that the plugin accepts the input SQL graph query in JSON format only. This is supported by the ORA_SQLGRAPH_TO_JSON PL/SQL function.
Create the ORA_SQLGRAPH_TO_JSON function in your APEX workspace.
1. Click Graph Visualization (Preview) on the Oracle APEX Plug-ins page.
2. Download the gvt_sqlgraph_to_json.sql file from the /optional-23c-only folder.
3. Upload and run the gvt_sqlgraph_to_json.sql script in your APEX workspace (see Uploading a SQL Script).
This step creates the ORA_SQLGRAPH_TO_JSON function in the database. Note that the ORA_SQLGRAPH_TO_JSON function accepts only CURSOR as input. Therefore you need to provide the SQL graph query into a CURSOR using a helper function.

Create a helper function (see Running a SQL Command) to provide the CURSOR for the SQL graph query and subsequently obtain the JSON output from the ORA_SQLGRAPH_TO_JSON function.

For example:

CREATE OR REPLACE FUNCTION bank_sqlgraph_json (
  QUERY VARCHAR2
) RETURN CLOB
  AUTHID CURRENT_USER IS
  INCUR    SYS_REFCURSOR;
  L_CUR    NUMBER;
  RETVALUE CLOB;
BEGIN
  OPEN INCUR FOR QUERY;
  L_CUR := DBMS_SQL.TO_CURSOR_NUMBER(INCUR);
  RETVALUE := ORA_SQLGRAPH_TO_JSON(L_CUR);
  DBMS_SQL.CLOSE_CURSOR(L_CUR);
  RETURN RETVALUE;
END;

Implement the plug-in in an application page to perform various graph visualizations.
The following basic example describes the steps to visualize a graph existing in your database using the Graph Visualization plug-in.
1. Open the application page in Page Designer.
2. Select the Rendering tab on the left pane of the Page Designer.
3. Right-click an existing component and add a new region component.
4. Select the new region and configure the following attributes in the Region tab of the Property Editor on the right pane of the Page Designer:
  1. Enter the Identification Title.
  2. Select Graph Visualization (Preview) as Identification Type.
  3. Select the source Location as Local Database.
  4. Select Type as SQL Query.
  5. Embed a SQL graph query to retrieve the graph data.
    For instance, the SQL graph query in the following example, is provided as input to the bank_sqlgraph_json helper function created in the preceding step:
```
SELECT bank_sqlgraph_json('SELECT id_a, id_e, id_b FROM GRAPH_TABLE (bank_sql_pg
  MATCH (a IS accounts) -[e IS transfers]-> (b IS accounts)
    WHERE a.id = 816
    COLUMNS (vertex_id(a) AS id_a, edge_id(e) AS id_e, vertex_id(b) AS id_b)
  )'
) AS result_column FROM DUAL;
```
    Note that a column alias need to be provided after the function.
5. Run the application page to visualize the graph rendered by the plugin.
  
  Figure 8-1 Visualizing a SQL Graph Query in an APEX Application
  
  Description of "Figure 8-1 Visualizing a SQL Graph Query in an APEX Application"
Optionally, you can import and run the Sample Graph Visualizations application from Oracle APEX GitHub repository.
See Importing the Sample Graph Visualizations Application in APEX for more information.

Importing the Sample Graph Visualizations Application in APEX
The Sample Graph Visualizations application demonstrates the use of the Graph Visualization plug-in.
Graph Visualization with Pagination
You can implement pagination when using the ORA_SQLGRAPH_TO_JSON function.

Parent topic: Visualizing SQL Graph Queries Using the APEX Graph Visualization Plug-in

8.2.1 Importing the Sample Graph Visualizations Application in APEX

The Sample Graph Visualizations application demonstrates the use of the Graph Visualization plug-in.

Perform the following steps to import the Sample Graph Visualizations application:

Download the Sample Graph Visualizations application from Oracle APEX GitHub repository.
Import the downloaded sample-graph-visualizations.zip into your APEX instance by following the steps in Importing an Application.
You can directly run the sample application once it is installed.

Figure 8-2 Sample Graph Visualization Home Page

Description of "Figure 8-2 Sample Graph Visualization Home Page"

Also, note that the sample application requires a secure HTTPS connection. If you want to disable secure connection, then perform the following steps:

Caution:
It is not recommended to disable secure connections in production deployment.
1. Navigate to the sample application home page in App Builder.
2. Click Shared Components.
3. Click Authentication Schemes under Security.
4. Click the Current authentication scheme.
5. Click the Session Sharing tab and turn off the Secure switch.
6. Click Apply Changes and then run the application.

To view the visualization for a SQL property graph query using the ORA_SQLGRAPH_TO_JSON function (shown highlighted in the preceding figure), you need to perform the following additional steps:

Create the ORA_SQLGRAPH_TO_JSON function if it is not already added in your APEX workspace.
1. Click Graph Visualization (Preview) on the Oracle APEX Plug-ins page.
2. Download the gvt_sqlgraph_to_json.sql file from the /optional-23c-only folder.
3. Upload and run the gvt_sqlgraph_to_json.sql script in your APEX workspace (see Uploading a SQL Script).

Create the SQL property graph used by the sample application.

Using the command editor in the SQL Workshop component, run the following

CREATE PROPERTY
                        GRAPH

DDL statement:

CREATE PROPERTY GRAPH EBA_SAMPLE_GRAPH
  VERTEX TABLES (
    eba_graphviz_countries
      KEY ( country_id )
      LABEL country PROPERTIES ( country_id, country_name, region_id ),
    eba_graphviz_departments
      KEY ( department_id )
      LABEL department PROPERTIES ( department_id, department_name, location_id, manager_id ),
    eba_graphviz_locations
      KEY ( location_id )
      LABEL location PROPERTIES ( city, country_id, location_id, postal_code, state_province, street_address ),
    eba_graphviz_job_history
      KEY ( employee_id, end_date, job_id, start_date )
      PROPERTIES ( department_id, employee_id, end_date, job_id, start_date ),
    eba_graphviz_jobs
      KEY ( job_id )
      LABEL job PROPERTIES ( job_id, job_title, max_salary, min_salary ),
    eba_graphviz_regions
      KEY ( region_id )
      LABEL region PROPERTIES ( region_id, region_name ),
    eba_graphviz_employees
      KEY ( employee_id )
      LABEL employee PROPERTIES ( commission_pct, department_id, email, employee_id, first_name, hire_date, job_id, last_name, manager_id, phone_number, salary )
  )
  EDGE TABLES (
    eba_graphviz_countries AS country_located_in
      SOURCE KEY ( country_id ) REFERENCES eba_graphviz_countries (country_id)
      DESTINATION KEY ( region_id ) REFERENCES eba_graphviz_regions (region_id)
      NO PROPERTIES,
    eba_graphviz_departments AS department_located_in
      SOURCE KEY ( department_id ) REFERENCES eba_graphviz_departments (department_id)
      DESTINATION KEY ( location_id ) REFERENCES eba_graphviz_locations ( location_id )
      NO PROPERTIES,
    eba_graphviz_locations AS location_located_in
      SOURCE KEY ( location_id ) REFERENCES eba_graphviz_locations ( location_id )
      DESTINATION KEY ( country_id ) REFERENCES eba_graphviz_countries ( country_id )
      NO PROPERTIES,
    eba_graphviz_employees AS works_as
      SOURCE KEY ( employee_id ) REFERENCES eba_graphviz_employees ( employee_id )
      DESTINATION KEY ( job_id ) REFERENCES eba_graphviz_jobs ( job_id )
      NO PROPERTIES,
    eba_graphviz_employees AS works_at
      SOURCE KEY ( employee_id ) REFERENCES eba_graphviz_employees  ( employee_id ) 
      DESTINATION KEY ( department_id ) REFERENCES eba_graphviz_departments ( department_id ) 
      NO PROPERTIES,
    eba_graphviz_employees AS works_for
      SOURCE KEY ( employee_id ) REFERENCES eba_graphviz_employees ( employee_id )
      DESTINATION KEY ( manager_id ) REFERENCES eba_graphviz_employees  ( employee_id )
      NO PROPERTIES,
    eba_graphviz_job_history AS for_job KEY ( employee_id, start_date )
      SOURCE KEY ( employee_id, start_date ) REFERENCES eba_graphviz_job_history ( employee_id, start_date )
      DESTINATION KEY ( job_id ) REFERENCES eba_graphviz_jobs ( job_id )
      NO PROPERTIES,
    eba_graphviz_job_history AS for_department KEY ( employee_id, start_date )
      SOURCE KEY ( employee_id, start_date ) REFERENCES eba_graphviz_job_history ( employee_id, start_date )
      DESTINATION KEY ( department_id ) REFERENCES eba_graphviz_departments ( department_id )
      NO PROPERTIES,
    eba_graphviz_job_history AS for_employee KEY ( employee_id, start_date )
      SOURCE KEY ( employee_id, start_date ) REFERENCES eba_graphviz_job_history ( employee_id, start_date ) 
      DESTINATION KEY ( employee_id ) REFERENCES eba_graphviz_employees  ( employee_id )
      NO PROPERTIES
  )

Create the following helper function (see Running a SQL Command) used by the sample application to provide the CURSOR for the SQL graph query.

CREATE OR REPLACE FUNCTION CUST_SQLGRAPH_JSON (
  QUERY VARCHAR2
) RETURN CLOB
  AUTHID CURRENT_USER IS
  INCUR    SYS_REFCURSOR;
  L_CUR    NUMBER;
  RETVALUE CLOB;
BEGIN
  OPEN INCUR FOR QUERY;
  L_CUR := DBMS_SQL.TO_CURSOR_NUMBER(INCUR);
  RETVALUE := ORA_SQLGRAPH_TO_JSON(L_CUR);
  DBMS_SQL.CLOSE_CURSOR(L_CUR);
  RETURN RETVALUE;
END;

Navigate to App Builder and from the sample application home page open the ORA_SQLGRAPH_TO_JSON Query page in Page Designer.
1. Select the Graph Using ORA_SQLGRAPH_TO_JSON Function component in the Rendering tab on the left pane of the Page Designer.
2. Uncomment the SQL query (using the CUST_SQLGRAPH_JSON helper function) under Source in the Region tab on the right pane of the Page Designer (Property Editor) .
  Also, ensure that the SQL Query input contains only one query as shown:
```
SELECT CUST_SQLGRAPH_JSON('SELECT employee, e, manager
    FROM GRAPH_TABLE ( EBA_SAMPLE_GRAPH
    MATCH (worker is employee) -[w is works_for]-> (boss is employee)
    COLUMNS (vertex_id(worker) AS employee, edge_id(w) AS e, vertex_id(boss) AS manager )
)') as result_column FROM DUAL;
```
3. Click Save and run the application page to view the visualization for the SQL graph query as rendered by the plug-in.

Parent topic: Getting Started with the APEX Graph Visualization Plug-in

8.2.2 Graph Visualization with Pagination

You can implement pagination when using the ORA_SQLGRAPH_TO_JSON function.

The instructions in Getting Started with the APEX Graph Visualization Plug-in guides you to set up the Graph Visualization plug-in in your APEX application. However, to use pagination with the ORA_SQLGRAPH_TO_JSON function, you must ensure the following:

Switch ON the SQL Query Supports Pagination setting in the Attributes tab of the Property Editor for the graph visualization component in your APEX application.

Add the page_start and page_size parameters in the helper function as shown in the following example code:

CREATE OR REPLACE FUNCTION bank_sqlgraph_json (
  QUERY VARCHAR2,
  PAGE_START NUMBER DEFAULT -1,
  PAGE_SIZE NUMBER DEFAULT -1
  ) RETURN CLOB
  AUTHID CURRENT_USER IS
  INCUR    SYS_REFCURSOR;
  L_CUR    NUMBER;
  RETVALUE CLOB;
BEGIN
  OPEN INCUR FOR QUERY;
  L_CUR := DBMS_SQL.TO_CURSOR_NUMBER(INCUR);
  RETVALUE := ORA_SQLGRAPH_TO_JSON(L_CUR, PAGE_START, PAGE_SIZE);
  DBMS_SQL.CLOSE_CURSOR(L_CUR);
  RETURN RETVALUE;
END;

Bind variables to page_start and page_size when calling the helper function in the SQL Query input in the Region tab of the Property Editor:
```
SELECT bank_sqlgraph_json('SELECT id_a, id_e, id_b FROM GRAPH_TABLE (bank_sql_pg
  MATCH (a IS accounts) -[e IS transfers]-> (b IS accounts)
    WHERE a.id = 816
    COLUMNS (vertex_id(a) AS id_a, edge_id(e) AS id_e, vertex_id(b) AS id_b)
  )',
  :page_start,
  :page_size
) AS result_column FROM DUAL;
```
Note that the page_start value is automatcially set. You can set the value of page_size in the Attributes tab of the Property Editor.

Parent topic: Getting Started with the APEX Graph Visualization Plug-in