Graph

To create a graph, use the CREATE GRAPH command.

CREATE

CREATE GRAPH graphname;        

Several graphs can be created in a database. In order to specify which graph is to be used, the session parameter graph_path is used.

To show the current graph path, use the following command.

SHOW graph_path;        

When a graph is created using CREATE GRAPH, graph_path is set to the created graph if graph_path is not set. You can create multiple graphs and change graph_path to another graph using the following command:

SET graph_path = graphname;        

The graph_path is a session variable, so every client must set the graph_path before querying the graph. Only one graph name can be specified for graph_path. Querying over multiple graphs is not allowed.

If you set the graph_path for each user or database using the ALTER ROLE or DATABASE statement, you do not need to run the SET graph_path statement whenever you connect the database.

ALTER ROLE user1 IN DATABASE gdb SET graph_path TO graphname;
ALTER DATABASE gdb SET graph_path TO graphname;

DROP

DROP GRAPH graphname CASCADE;        

A graph has initial labels for vertices and edges. These labels cannot be deleted. To drop the graph, users must do so with the CASCADE option. If current graph_path is the deleted graph, then graph_path is reset to null.

Labels

CREATE

CREATE VLABEL person;
CREATE VLABEL friend inherits (person);

CREATE ELABEL knows;
CREATE ELABEL live_together;
CREATE ELABEL room_mate inherits (knows, live_together);

The keywords VLABEL and ELABEL are used for identifying vertices and edges respectively. CREATE VLABEL will create a vertex label. VLABEL can inherit VLABEL only (in other words, VLABEL cannot inherit ELABEL). inherits is an option to inherit a parent label. If not specified, the system sets the initial label as a parent label. Multiple inheritance is possible for creating complex labels.

DROP

DROP VLABEL friend;
DROP VLABEL person;

VLABEL friend inherits person, so VLABEL person cannot be dropped directly. VLABEL friend should be dropped first.

If a label is bound to any vertex or edge, you cannot delete this label by using DROP VLABEL or DROP ELABEL.

DROP VLABEL person;
ERROR:  cannot drop person because it is not empty.

You can use DROP ... CASCADE to delete a label regardless of whether it is bound to or not, and in this case, all data object that has this label is deleted along with it.

DROP VLABEL person CASCADE;
NOTICE:  drop cascades to vlabel friend
DROP VLABEL

DROP ELABEL knows CASCADE;

GRAPH

CREATE GRAPH

CREATE GRAPH [ IF NOT EXISTS ] graph_name [AUTHORIZATION role_name];

• IF NOT EXISTS
  • Do nothing if the same name already exists
• AUTHORIZATION role_name
  • The role name of the user who will own the new graph

ALTER GRAPH

ALTER GRAPH graph_name RENAME TO new_name;                 
ALTER GRAPH graph_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER };

• graph_name
  • The name of an existing graph.
• RENAME TO new_name
  • This form changes the name of a graph to new_name.
• OWNER TO new_owner
  • This form changes the owner of the graph.

LABEL

CREATE LABEL

The synopsis of both VLABEL and ELABEL is identical.

CREATE [ UNLOGGED ] VLABEL [ IF NOT EXISTS ] label_name [DISABLE INDEX]
       [ INHERITS ( parent_label_name [, ...] ) ]
       [ WITH (storage_parameter)]
       [ TABLESPACE tablespace_name ];

• UNLOGGED
  • Data written to an unlogged label is not recorded to the write-ahead log, which makes unlogged labels considerably faster than logged labels. However, unlogged labels are not crash-safe.
• IF NOT EXISTS
  • Do nothing if the same name already exists.
• label_name
  • The name of the vertex/edge label to be created.
• DISABLE INDEX
  • Create a label with invalid index. The invalid indexes can not be used for searching or inserting until reindexed.
• INHERITS ( parent_label [, …] )
  • The optional INHERITS clause specifies a list of vertex/edge labels. If it is empty, the new label inherits the initial label. Use of INHERITS creates a persistent edge between the new child label and its parent label(s). The data of the child label is included in scans of the parent(s) by default.
• TABLESPACE tablespace_name
  • The new label will be created in the tablespace whose name is tablespace_name.

ALTER LABEL

ALTER [ IF EXISTS ] VLABEL label_name RENAME TO new_name;        
ALTER [ IF EXISTS ] VLABEL label_name OWNER TO new_owner;        
ALTER [ IF EXISTS ] VLABEL label_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN };        
ALTER [ IF EXISTS ] VLABEL label_name SET TABLESPACE new_tablespace;        
ALTER [ IF EXISTS ] VLABEL label_name CLUSTER ON idxname;        
ALTER [ IF EXISTS ] VLABEL label_name SET WITHOUT CLUSTER;        
ALTER [ IF EXISTS ] VLABEL label_name SET LOGGED;        
ALTER [ IF EXISTS ] VLABEL label_name SET UNLOGGED;        
ALTER [ IF EXISTS ] VLABEL label_name INHERIT parent_label;        
ALTER [ IF EXISTS ] VLABEL label_name NO INHERIT parent_label;        
ALTER [ IF EXISTS ] VLABEL label_name DISABLE INDEX;        

• IF EXISTS
  • Do not throw an error if the label does not exist.
• label_name
  • The name of an existing vertex/edge label.
• RENAME TO new_name
  • Changes the name of a label to new_name.
• OWNER TO new_owner
  • Changes the owner of the label.
• SET STORAGE
  • This form sets the storage mode for property. This controls whether property is held inline or in a secondary TOAST table, and whether the data should be compressed or not.
  • PLAIN must be used for fixed-length, inline and uncompressed values.
  • MAIN is inline and compressed.
  • EXTERNAL is external and uncompressed.
  • EXTENDED is external and compressed.
• SET TABLESPACE
  • This form changes the label’s tablespace to the specified tablespace and moves the data files to the new tablespace. Indexes on the label are not moved.
• CLUSTER/WITHOUT CLUSTER
  • This form select/remove the default index for future cluster operation. See this.
• SET LOGGED/UNLOGGED
  • This form changes the label from unlogged to logged or vice-versa.
• INHERIT/NO INHERIT
  • This form adds/removes the target label to/from the parent label’s children list.
• DISABLE INDEX
  • This form changes all indexes of the label to invalid index. The invalid indexes can not be used for searching or inserting until reindexed.

Property Index

The property index provides a method to build indexes on the property values. Users can also create an index for an expression.

CREATE INDEX

CREATE [UNIQUE] PROPERTY INDEX [CONCURRENTLY] [IF NOT EXIST] indexname
ON labelname [USING method]
( attribute_expr | (expr) [COLLATE collation] [opclass] [ASC | DESC]
[NULLS {FIRST | LAST}]  )
[WITH] (storage_parameter = value [,...])
[TABLESPACE tablespacename]
[WHERE predicate];

• UNIQUE
  • Causes the system to check for duplicate values in the table when the index is created if the data already exists and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.
• CONCURRENTLY
  • With this option, the index will be built without taking any locks that prevent concurrent  
    CREATE, DELETE(DETACH), SET on the label. There are several caveats to be aware of when using this option. See this.
• IF NOT EXIST
  • Do nothing if the same name already exists
• indexname
  • The name of the index to be created. If it is omitted, AgensGraph chooses an adequate name.
• labelname
  • The name of the label to be indexed.
• USING METHOD
  • The name of the index method to be used. They are btree, hash, gist, spgist, gin and brin.  
    The default method is btree.
• attribute_expr
  • An expression which is pointing to the attribute of property.
• COLLATE collation
  • The name of the collation to use for the index. By default, the index uses the collation declared for the column to be indexed or the result collation of the expression to be indexed. Indexes with non-default collations can be useful for queries that involve expressions using non-default collations.
• opclass
  • The name of an operator class.
• ASC | DESC
  • Specified ascending/descending sort order. ASC is the default.
• NULLS { FIRST | LAST }
  • Specifies that nulls sort before/after non-nulls. LAST is default when ASC is specified.
• WITH storage_parameter
  • The name of an index-method-specific storage parameter. See Index Storage Parameters for details.
• TABLESPACE tablespacename
  • The tablespace in which to create the index.
• WHERE predicate
  • The constraint expression for a partial index.

DROP INDEX

DROP PROPERTY INDEX indexname [CASCADE | RESTRICT];        

• indexname
  • The indexname can be checked with ag_property_indexes or \dGi.
• CASCADE
  • Automatically drop objects that depend on the graph.
• RESTRICT
  • Refuse to drop the index if any objects depend on it. This is the default.

Constraints

This section provides information about how to use constraints on properties. Users can create two types of constraints; UNIQUE constraint and CHECK constraint.

CREATE CONSTRAINT constraint_name ON label_name ASSERT field_expr IS UNIQUE;
CREATE CONSTRAINT constraint_name ON label_name ASSERT check_expr;
DROP CONSTRAINT constraint_name ON label_name;

• field_expr
  • This form represents a JSON expression. This JSON can be a nested JSON.
• UNIQUE
  • This form specified that json_expression can contain only unique values.
• check_expr
  • The check expression returns a boolean result for new or updated properties. The result must be TRUE or UNKNOWN for an insert or update to succeed. Should any property of an insert or update operation produce a FALSE result, an error exception is raised and the insert or update will rolled back.

Creating the Labels

Before creating graph data, generating a label is basic. Although this is the default, label is generated automatically when you specify label in the CREATE statement in cypher(VLABEL/ELABEL can both be created).

note: Be careful not to confuse the label with the lable. Unintentional new labels can be created.

All graph elements have one label. For vertex, if no label is specified, have ag_vertex as a default label. For edge, the label can not be omitted. ag_edge label also exists but is used for other purposes.

AgensGraph supports DDL’s to create such labels.

The following statements create a vertex label person and an edge label knows.

CREATE VLABEL person;
CREATE ELABEL knows;
CREATE (n:movie {title:'Matrix'});

Creating the Vertices and Edges

Now, we can create vertices for person and edges for knows by using Cypher’s CREATE clause. The CREATE clause creates a pattern that consists of vertices and edges. A vertex has the form: (variable:label {property: value, ...}), and an edge has: -[variable:label {property: value, ...}]-. An additional < on the leftmost side or > on the rightmost side is used to denote the direction of the edge. variable can be omitted if created vertices and edges are not to be referenced.

note: AgensGraph does not support -- grammar for an edge in a pattern because -- means a comment to the end of the line.

The following statements create three simple patterns: “Tom knows Summer”, “Pat knows Nikki” and “Olive knows Todd”.

CREATE (:person {name: 'Tom'})-[:knows {fromdate:'2011-11-24'}]->(:person {name: 'Summer'});
CREATE (:person {name: 'Pat'})-[:knows {fromdate:'2013-12-25'}]->(:person {name: 'Nikki'});
CREATE (:person {name: 'Olive'})-[:knows {fromdate:'2015-01-26'}]->(:person {name: 'Todd'});
MATCH (p:Person {name: 'Tom'}),(k:Person{name: 'Pat'}) 
CREATE (p)-[:KNOWS {fromdate:'2017-02-27'} ]->(k);

To store properties of vertices and edges, AgensGraph uses PostgreSQL’s jsonb type. Properties can have nested JSON objects as their values. Since AgensGraph uses PostgreSQL’s type system, any data type supported by PostgreSQL can be stored into the properties of vertices and edges.

Cypher in SQL

Since the result of a Cypher query is a relation, you can use a Cypher query in the FROM clause of SQL as if it is a subquery. It is possible to use Cypher syntax inside the FROM clause to utilize a dataset of vertex or edge stored in graph DB as data in a SQL statement.

Syntex :

SELECT [column_name]
FROM ({table_name|SQLquery|CYPHERquery})
WHERE [column_name operator value];

Example :

SELECT n->>'name' as name 
FROM history, (MATCH (n:dev) RETURN n) as dev 
WHERE history.year > (n->>'year')::int;
  name
---------
 someone
(1 row)

SQL in Cypher

When querying the content of the graph DB with the cypher queries, you can use the match and where clause when you want to search using specific data of the RDB. However, the resulting dataset in the SQL queries must be configured to return a single row of results.

Syntex :

MATCH [table_name]
WHERE (column_name operator {value|SQLquery|CYPHERquery})
RETURN [column_name];

Example :

MATCH (n:dev)
WHERE n.year < (SELECT year FROM history WHERE event = 'AgensGraph')
RETURN properties(n) AS n;
                 n
-----------------------------------
 {"name": "someone", "year": 2015}
(1 row)

Get the Driver

You can download the precompiled driver(jar) from Download link or use maven as follows:

<dependencies>
  ...
  <dependency>
    <groupId>net.bitnine</groupId>
    <artifactId>agensgraph-jdbc</artifactId>
    <version>1.4.2</version>
  </dependency>
  ...
</dependencies>

You can search the latest version on The Central Repository with Groupld and Artifactld.

Connection

To connect to AgensGraph using the Java Driver, we need two things: the name of the class to be loaded into the Java Driver and the connection string.

• The name of the class is net.bitnine.agensgraph.Driver.
• The connection string consists of the sub-protocol, server, port, database.
  • jdbc:agensgraph://is the sub-protocol to use a particular Driver and a hold value.
  • It is written as jdbc:agensgraph://server:port/database, including the sub-protocol.

The following code is an example of how to connect AgensGraph. It connects to AgensGraph through the Connection object and is ready to be queried.

import java.sql.DriverManager;
import java.sql.Connection;

public class AgensGraphTest {
  public static void main(String[] args) {
  try{
    Class.forName("net.bitnine.agensgraph.Driver");
    String connectionString = "jdbc:agensgraph://127.0.0.1:5432/agens";
    String username = "test";
    String password = "test";
    Connection conn = DriverManager.getConnection(connectionString, username, password);
    ...
  } catch (Exception e) {
          e.printStackTrace();
  }
  }
}

Retrieving Data

This example illustrates how to export Graph data using MATCH.

A Cypher query is executed using executeQuery(). The output is a ResultSet object, which is the same output format found in the JDBC driver. The output of the query is the ‘vertex’ type of AgensGraph. Java Driver returns this output as a Vertex instance. Because the Vertex class is a sub-class of Jsonb, users can obtain information from the property fields.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;

import net.bitnine.agensgraph.graph.Vertex;

public class AgensGraphTest {
  public static void main(String[] args) {
  try{
    ...
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(
        "MATCH (:Person {name: 'John'})-[:knows]-(friend:Person)" +
        "RETURN friend");
    while (rs.next()) {
      Vertex friend = (Vertex)rs.getObject(1);
      System.out.println(friend.getString("name"));
      System.out.println(friend.getInt("age"));
    } catch (Exception e) {
            e.printStackTrace();
    }
  }
  }
}

Creating Data

The following example illustrates how to insert a vertex with a label Person into AgensGraph. Users can input a property of a vertex using strings in Cypher queries. They can also be bound after making a Jsonb.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;

import net.bitnine.agensgraph.util.Jsonb;
import net.bitnine.agensgraph.util.JsonbUtil;

public class AgensGraphTest {
  public static void main(String[] args) {
    ...
    PreparedStatement pstmt = conn.prepareStatement("CREATE (:person ?)");
        Jsonb john = JsonbUtil.createObjectBuilder()
       .add("name", "John")
       .add("from", "USA")
       .add("age", 17)
       .build();
    pstmt.setObject(1, john);
    pstmt.execute();
  }
}

The following is generated as a string:

"CREATE (:Person {name: 'John', from: 'USA', age: 17})"

[Reference]
In JDBC, the question mark (?) is the placeholder for the positional parameters of a PreparedStatement. There are, however, a number of SQL operators that contain a question mark. To avoid confusion, when used in a prepared statement, it must be used with spaces.