Graph Example¶

This example demonstrates the full workflow for loading the AkasicDB extension, defining and creating a graph, and running various retail-related queries.

Load the Extension¶

CREATE EXTENSION IF NOT EXISTS akasicdb;

SELECT akasicdb_admin.initialize();

Registers all data types and functions of AkasicDB in the current database.
Only a superuser can perform this operation.

Create Tables¶

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);

The retail dataset consists of store, item, customer, and order tables.

Populate Tutorial Data¶

SQL for populating 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);

Populate each table in the retail dataset using the SQL script above.

Define and Create the Graph¶

SELECT akasicdb.define_graph('retail_graph');

SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_item', 
  ARRAY['i_no integer', 'name varchar(50)', 'price decimal(7,2)'],
  'item'
);
SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_customer', 
  ARRAY['c_id integer', 'first_name varchar(20)', 'last_name varchar(30)'],
  'customer'
);
SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_store', 
  ARRAY['s_id integer', 'name varchar(50)'], 
  'store',
  'SELECT s_id, name FROM store'
);

SELECT akasicdb.define_edge(
  'retail_graph', 
  'buy',
  'v_customer', 'v_item',
  null,
  'SELECT null FROM customer c, orders o, item i '
  'WHERE c.c_id = o.c_id AND i.i_no = o.i_no',
  'customer c', 'item i'
);

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'
);

SELECT akasicdb.create_graph('retail_graph');

Defines and creates the graph retail_graph from the retail dataset.
Defines the v_item, v_customer, and v_store vertices from the item, customer, and store tables, respectively.
Defines the relationships between items and the stores that sell them as sell edges, and the relationships between items and the customers who purchased them as buy edges.

Graph Queries¶

-- Find the names and prices of items purchased by the customer 'Margaret Farias'
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)-[:buy]->(i:v_item)
WHERE c.first_name = 'Margaret' 
  AND c.last_name = 'Farias'
RETURN 
    i.name as product_name,
    i.price as price
$$) as (
    product_name varchar,
    price decimal
);

-- Find other customers who purchased items that Margaret Farias purchased
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c1:v_customer)-[:buy]->(i:v_item)<-[:buy]-(c2:v_customer)
WHERE c1.first_name = 'Margaret' 
  AND c1.last_name = 'Farias'
  AND c1.vertex_id <> c2.vertex_id
RETURN 
    c2.first_name as first_name,
    c2.last_name as last_name
$$) as (
    first_name varchar,
    last_name varchar
);

You have now created a graph from relational data and run graph queries in AkasicDB.