Create Graph¶
This section explains how to create graphs, which is a prerequisite for graph queries in AkasicDB. It describes how to define a graph schema using SQL and create graphs from relational data.
Prepare Tables¶
The following queries define only the relational schema. To populate each table with data, expand the “Populate Table Data” section below.
CREATE TABLE store (
s_id integer PRIMARY KEY,
name varchar(50),
manager varchar(40)
);
CREATE TABLE item (
i_no integer PRIMARY KEY,
name varchar(50),
price decimal(7,2)
);
CREATE TABLE customer (
c_id integer PRIMARY KEY,
first_name varchar(20),
last_name varchar(30)
);
CREATE TABLE orders (
c_id integer,
i_no integer,
s_id integer
);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (c_id) REFERENCES customer(c_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_item
FOREIGN KEY (i_no) REFERENCES item(i_no);
ALTER TABLE orders ADD CONSTRAINT fk_orders_store
FOREIGN KEY (s_id) REFERENCES store(s_id);
Populate Table Data
INSERT INTO store (s_id, name, manager)
VALUES
(1, 'ought', 'William Ward'),
(2, 'able', 'Scott Smith'),
(3, 'able', 'Scott Smith'),
(4, 'ese', 'Edwin Adams'),
(5, 'anti', 'Edwin Adams');
INSERT INTO item (i_no, name, price)
VALUES
(1, 'ought', 27.02),
(2, 'able', 1.12),
(3, 'pri', 7.11),
(4, 'ese', 1.35),
(5, 'anti', 4.00),
(6, 'cally', 0.85),
(7, 'ation', 9.94),
(8, 'eing', 2.76),
(9, 'n st', 4.46),
(10, 'barought', 8.94),
(11, 'oughtought', 54.87),
(12, 'ableought', 6.54),
(13, 'priought', 8.76),
(14, 'eseought', 1.85),
(15, 'antiought', 2.57),
(16, 'callyought', 0.31),
(17, 'ationought', 6.49),
(18, 'eingought', 0.87),
(19, 'n stought', 10.61),
(20, 'barable', 29.35);
INSERT INTO customer (c_id, first_name, last_name)
VALUES
(1, 'Javier', 'Lewis'),
(2, 'Amy', 'Moses'),
(3, 'Latisha', 'Hamilton'),
(4, 'Michael', 'White'),
(5, 'Robert', 'Moran'),
(6, 'Brunilda', 'Sharp'),
(7, 'Fonda', 'Wiles'),
(8, 'Ollie', 'Shipman'),
(9, 'Karl', 'Gilbert'),
(10, 'Albert', 'Brunson'),
(11, 'Betty', 'Williams'),
(12, 'Margaret', 'Farias'),
(13, 'Rosalinda', 'Grimes'),
(14, 'Jack', 'Wilcox'),
(15, 'Margie', 'Browning'),
(16, 'Lee', 'Stovall'),
(17, 'Brad', 'Lynch'),
(18, 'Andre', 'Moore'),
(19, 'Stanton', 'Dallas'),
(20, 'Naomi', 'Barnett'),
(21, 'Victor', 'Martinez'),
(22, 'Paul', 'Morris'),
(23, 'Nancy', 'Mccormick'),
(24, 'Monique', 'Baker'),
(25, 'Shawn', 'Prather'),
(26, 'Edith', 'Hernandez'),
(27, 'Margaret', 'Collins'),
(28, 'Pamela', 'Luna'),
(29, 'William', 'Craig'),
(30, 'Kenneth', 'Wood');
INSERT INTO orders (c_id, i_no, s_id)
VALUES
(9,7,4),
(11,14,1),
(16,12,3),
(17,17,3),
(6,14,4),
(18,14,4),
(27,14,4),
(12,17,4),
(28,8,1),
(22,5,2),
(23,14,4),
(24,14,1),
(24,3,5),
(4,8,3),
(10,8,4),
(4,18,3),
(28,15,5),
(12,2,2),
(15,18,1),
(12,20,1),
(16,10,3),
(17,1,4),
(24,19,3),
(6,10,5),
(30,6,1),
(19,12,4),
(21,5,4),
(7,17,3),
(26,9,2),
(25,16,3),
(13,9,5),
(17,15,1),
(29,7,2),
(25,4,2),
(5,6,3),
(18,9,2),
(10,1,4),
(22,16,4),
(12,10,5),
(20,15,3),
(19,7,2),
(10,11,1),
(3,6,2),
(17,12,5),
(18,5,5),
(19,18,5),
(23,8,1),
(6,13,1),
(9,18,1),
(3,5,1),
(17,6,4),
(9,11,4),
(14,3,4),
(23,12,1),
(25,6,3),
(7,10,4),
(14,4,5),
(11,4,1),
(9,10,5),
(17,16,3),
(11,7,4),
(26,4,2),
(7,18,2),
(19,2,5),
(22,18,2),
(26,7,3),
(27,3,5),
(8,17,4),
(26,2,4),
(6,6,5),
(27,6,3),
(22,20,3),
(27,6,2),
(30,2,5),
(13,18,3),
(6,18,2),
(28,7,1),
(27,12,5),
(5,8,1),
(27,11,3),
(9,10,5),
(27,2,2),
(8,18,5),
(22,5,2),
(27,16,5),
(29,9,5),
(30,2,4),
(30,8,5),
(29,3,4),
(30,20,4),
(22,5,2),
(4,1,1),
(29,8,3),
(19,6,2),
(15,17,5),
(15,15,3),
(6,20,1),
(14,15,4),
(18,13,3),
(18,15,4);
Define the Graph Schema¶
Add a Graph Definition¶
- Add a new graph definition
Function arguments
- Copy an existing graph definition, including vertex and edge definitions
-- Define a new graph named 'retail_graph2' by copying the graph definition of 'retail_graph'
SELECT akasicdb.copy_graph(
'retail_graph',
'retail_graph2'
);
Function arguments
akasicdb.copy_graph(
src_graph_name, -- Name of the graph definition to copy
dst_graph_name -- Name of the new graph to define
);
Warning
akasicdb.copy_graph copies only the graph schema, including vertex and edge definitions.
Even if the source graph has already been created with akasicdb.create_graph(), graph data is not copied and a new graph is not created automatically.
Add Vertex Definitions¶
-- Add a vertex definition named 'v_city' to the 'retail_graph' graph definition.
-- Each vertex has 'name' and 'population' properties, stored as text and integer values, respectively.
SELECT akasicdb.define_vertex(
'retail_graph',
'v_city',
ARRAY['name text', 'population integer']
);
-- Add a vertex definition named 'v_item' to the 'retail_graph' graph definition.
-- Vertices are extracted from each tuple in the item table.
SELECT akasicdb.define_vertex(
'retail_graph',
'v_item',
ARRAY['i_no integer', 'name varchar(50)', 'price decimal(7,2)'],
'item'
);
-- Add a vertex definition named 'v_store' to the 'retail_graph' graph definition.
-- 'v_store' vertices are extracted from each tuple returned by a query on the store table.
SELECT akasicdb.define_vertex(
'retail_graph',
'v_store',
ARRAY['s_id integer', 'name varchar(50)'],
'store',
'SELECT s_id, name FROM store'
);
Function arguments
akasicdb.define_vertex(
graph_name, -- Name of the graph definition to add the vertex definition to
vertex_label, -- Label of the vertex to add
property_list, -- List of vertex properties. If null, vertices are stored without properties.
vertex_table, -- (Optional) Table containing tuples to be converted into vertices.
query -- (Optional) Query used to filter tuples and assign properties to vertices.
);
Adding a vertex definition is similar to defining a table in a relational database. vertex_label and property_list correspond to the table name and column list in a relational database.
vertex_table and query parameters are used to create vertices from table data.
When only vertex_table is specified, all columns in the table are stored as vertex properties. In this case, the property names specified in property_list must match the column names in the source table.
When query is also specified, vertices are extracted from the tuples returned by the query. Since the columns of each tuple are stored as properties, the property names specified in property_list must match the expressions in the SELECT clause of the query. The query parameter must also follow these rules:
- The query must be a
SELECT-FROM-WHEREquery over a single table specified byvertex_table. - Other clauses, including
ORDER BY,GROUP BY, andWITH, are not supported. - In the
WHEREclause, onlyANDcombinations of simple binary expressions (such asA.x = B.yANDA.x = 100) are supported. - The
SELECTclause supports simple column references, such asA.x; arithmetic expressions, such asA.x + B.y * C.z; and function calls, such asabs()andsubstr().
Note
AkasicDB stores vertex_id for each vertex alongside the user-defined properties. This value is an internal identifier used in graph operations. If you need to use primary key values from the source table directly in the graph when using the vertex_table or query parameter, specify the primary key as a property when defining the vertex.
Add Edge Definitions¶
-- Define a 'located_in' edge between 'v_store' and 'v_city' vertices.
-- Each edge has a 'since' property, stored as a timestamptz value.
SELECT akasicdb.define_edge(
'retail_graph',
'located_in',
'v_store', 'v_city',
ARRAY['since timestamptz']
);
-- Define a 'sell' edge between 'v_store' and 'v_item' vertices.
-- Each tuple returned by the query is extracted as a 'sell' edge.
-- The edge has no properties, and the 'v_store'/'v_item' vertices are matched
-- to tuples from the 'store' and 'item' tables in the query.
SELECT akasicdb.define_edge(
'retail_graph',
'sell',
'v_store', 'v_item',
null,
'SELECT null FROM store s, orders o, item i
WHERE s.s_id = o.s_id AND i.i_no = o.i_no',
'store s', 'item i'
);
Function arguments
akasicdb.define_edge(
graph_name, -- Name of the graph definition to add the edge definition to
edge_label, -- Label of the edge to add
src_vertex_label, -- Label of the source vertex
dst_vertex_label, -- Label of the destination vertex
property_list, -- List of edge properties. If null, edges are stored without properties.
query, -- (Optional) Query that defines the relationship between source and destination vertices.
src_table, -- (Optional) Table in the query parameter that corresponds to the source vertex.
dst_table -- (Optional) Table in the query parameter that corresponds to the destination vertex.
);
edge_label) and a property list (property_list), which correspond to a table name and column list in a relational database.
When defining an edge, you must also specify the source and destination vertex labels that the edge connects. This is similar to specifying referenced tables when defining foreign keys in a relational database.
The query, src_table, and dst_table parameters are used to create edges from table data.
When the query parameter is specified, edges are extracted from the tuples returned by the query. Since the columns of each tuple are stored as edge properties, the property names specified in property_list must match the expressions in the SELECT clause of the query.
The src_table and dst_table parameters identify the source and destination vertices in the edge definition query. They are required when the query parameter is used. Each value must match the vertex_table used in the corresponding vertex definition. If an alias is used in the query, include the alias as well, such as store s or item i in the example above.
The query parameter must follow these rules:
- The query must be a
SELECT-FROM-WHEREquery that includes a join relationship betweensrc_tableanddst_table. - Aggregation using
GROUP BYandHAVINGis supported. - Other clauses, including
ORDER BY,WITH, andLIMIT, are not supported. - In the
WHEREandHAVINGclauses, onlyANDcombinations of simple binary expressions (such asA.x = B.y AND A.x = 100) are supported. - The
SELECTandGROUP BYclauses support simple column references, such asA.x; arithmetic expressions, such asA.x + B.y * C.z; aggregate functions, such asCOUNT()andAVG(); and function calls, such asabs()andsubstr().
Note
AkasicDB stores src_vertex_id, dst_vertex_id, and edge_id properties for each edge alongside the user-defined properties. The src_vertex_id and dst_vertex_id values identify the source and destination vertices connected by the edge, while edge_id identifies the edge itself. These values are internal identifiers used in graph operations. If you need to use primary key values from the source table directly in the graph, specify the primary key as an edge property when defining the edge.
Delete a Graph Definition¶
Function arguments
When a graph definition is deleted, all vertex and edge definitions included in that graph definition are also deleted.
Delete a Vertex Definition¶
-- Delete the 'v_item' vertex definition from the 'retail_graph' graph definition
SELECT akasicdb.undefine_vertex('retail_graph', 'v_item');
Function arguments
akasicdb.undefine_vertex(
graph_name, -- Graph definition from which to remove the vertex definition
vertex_label -- Label of the vertex definition to remove
);
Delete an Edge Definition¶
-- Delete the 'sell' edge definition from the 'retail_graph' graph definition
SELECT akasicdb.undefine_edge('retail_graph', 'sell');
Function arguments
akasicdb.undefine_edge(
graph_name, -- Graph definition from which to remove the edge definition
edge_label -- Label of the edge definition to remove
);
Create and Delete Graphs¶
Create a Graph¶
-- Create a graph based on the `retail_graph` graph definition
SELECT akasicdb.create_graph('retail_graph');
-- If retail_graph already exists, delete it and recreate it
SELECT akasicdb.create_graph('retail_graph', replace_if_exists:=true);
Function arguments
akasicdb.create_graph(
graph_name, -- Name of the graph to create.
-- This must match the graph definition used to create the graph.
replace_if_exists -- (Optional) true or false. The default is false.
-- If true, deletes the existing graph and recreates it.
-- If false, returns an error if the graph already exists.
);
You can modify a graph definition after a graph has been created by using the graph schema definition queries. However, this modifies only the graph definition and does not update any graph that has already been created.
To apply the modified graph definition to the graph data, recreate the graph using the replace_if_exists option.
Warning
AkasicDB Community Edition limits the number of vertices and edges in a graph. You can create up to 1,000,000 (1M) vertices or edges per vertex label or per edge label.
Delete a Graph¶
-- Delete only the created graph named 'retail_graph'
SELECT akasicdb.delete_graph('retail_graph');
-- Delete both the created graph and its graph definition
SELECT akasicdb.delete_graph('retail_graph', remove_definition:=true);
Function arguments