#!/usr/bin/env python
# coding: utf-8

# # PostgreSQL Demo

# In[1]:


get_ipython().run_line_magic('load_ext', 'sql')


# In[2]:


get_ipython().run_line_magic('sql', 'postgresql://postgres:changeme@agensgraph_postgres_container/postgres')


# In[3]:


get_ipython().run_cell_magic('sql', '', 'SELECT * from pg_database\n')


# # Agensgraph Demo

# In[4]:


get_ipython().run_line_magic('sql', 'postgresql://agens:agens@agensgraph_agens_container/postgres')


# In[5]:


get_ipython().run_cell_magic('sql', '', 'SELECT * from pg_database\n')


# In[6]:


get_ipython().run_line_magic('sql', 'postgresql://agens:agens@agensgraph_container/agens')


# In[7]:


get_ipython().run_cell_magic('sql', '', "SELECT * FROM pg_catalog.pg_tables WHERE schemaname !='pg_catalog' AND schemaname !='information_schema';\n")


# In[8]:


get_ipython().run_cell_magic('sql', '', 'CREATE EXTENSION IF NOT EXISTS file_fdw;\n')


# In[9]:


get_ipython().run_cell_magic('sql', '', 'CREATE SERVER northwind FOREIGN DATA WRAPPER file_fdw;\n')


# In the tutorial, paths are set to `D:\northwind\` that need changing, eg to `/home/agens/AgensGraph/shareddata` (the path the data was mounted to in the setup). Note that the path is the path *inside the `agens` container*. that For convenience, we also mount that volume across to the notebook `/home/jovyan/agens` directory so that we can pass files from the notebook container to the `agens` container.

# In[18]:


#The path is path we mounted the data onto in the agensgraph container
PATH = '/home/agens/AgensGraph/shareddata'

setup='''
-- Go defensive
DROP FOREIGN TABLE IF EXISTS categories;
DROP FOREIGN TABLE IF EXISTS customers;
DROP FOREIGN TABLE IF EXISTS employees;
DROP FOREIGN TABLE IF EXISTS employee_territories;
DROP FOREIGN TABLE IF EXISTS orders_details;
DROP FOREIGN TABLE IF EXISTS orders;
DROP FOREIGN TABLE IF EXISTS products;
DROP FOREIGN TABLE IF EXISTS regions;
DROP FOREIGN TABLE IF EXISTS shippers;
DROP FOREIGN TABLE IF EXISTS suppliers;
DROP FOREIGN TABLE IF EXISTS territories;

CREATE FOREIGN TABLE categories (
CategoryID int,
CategoryName varchar(15),
Description text,
Picture bytea
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/categories.csv', delimiter ',', quote '"', null '');

CREATE FOREIGN TABLE customers (
CustomerID char(5),
CompanyName varchar(40),
ContactName varchar(30),
ContactTitle varchar(30),
Address varchar(60),
City varchar(15),
Region varchar(15),
PostalCode varchar(10),
Country varchar(15),
Phone varchar(24),
Fax varchar(24)
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/customers.csv', delimiter ',', quote '"', null '');

CREATE FOREIGN TABLE employees (
EmployeeID int,
LastName varchar(20),
FirstName varchar(10),
Title varchar(30),
TitleOfCourtesy varchar(25),
BirthDate date,
HireDate date,
Address varchar(60),
City varchar(15),
Region varchar(15),
PostalCode varchar(10),
Country varchar(15),
HomePhone varchar(24),
Extension varchar(4),
Photo bytea,
Notes text,
ReportTo int,
PhotoPath varchar(255)
) 
SERVER northwind 
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/employees.csv', delimiter ',', quote '"', null '');

CREATE FOREIGN TABLE employee_territories (
EmployeeID int,
TerritoryID varchar(20)
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/employee_territories.csv', delimiter ',', quote '"', null ''); 

CREATE FOREIGN TABLE orders_details (
orderID int,
ProductID int,
UnitPrice money,
Quantity smallint,
Discount real
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/orders_details.csv', delimiter ',', quote '"', null ''); 

CREATE FOREIGN TABLE orders (
orderID int,
CustomerID char(5),
EmployeeID int,
orderDate date,
RequiredDate date,
ShippedDate date,
ShipVia int,
Freight money,
ShipName varchar(40),
ShipAddress varchar(60),
ShipCity varchar(15),
ShipRegion varchar(15), 
ShipPostalCode varchar(10), 
ShipCountry varchar(15) 
) 
SERVER northwind 
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/orders.csv', delimiter ',', quote '"', null ''); 

CREATE FOREIGN TABLE products (
ProductID int, 
ProductName varchar(40),
SupplierID int, 
CategoryID int, 
QuantityPerUnit varchar(20),
UnitPrice money, 
UnitsInStock smallint, 
UnitsOnorder smallint, 
ReorderLevel smallint, 
Discontinued bit 
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/products.csv', delimiter ',', quote '"', null '');

CREATE FOREIGN TABLE regions (
RegionID int,
RegionDescription char(50)
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/regions.csv', delimiter ',', quote '"', null ''); 

CREATE FOREIGN TABLE shippers (
ShipperID int,
CompanyName varchar(40),
Phone varchar(24)
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/shippers.csv', delimiter ',', quote '"', null ''); 

CREATE FOREIGN TABLE suppliers (
SupplierID int,
CompanyName varchar(40),
ContactName varchar(30),
ContactTitle varchar(30),
Address varchar(60),
City varchar(15),
Region varchar(15),
PostalCode varchar(10),
Country varchar(15),
Phone varchar(24),
Fax varchar(24),
HomePage text
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/suppliers.csv', delimiter ',', quote '"', null '');

CREATE FOREIGN TABLE territories (
TerritoryID varchar(20),
TerritoryDescription char(50),
RegionID int
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/territories.csv', delimiter ',', quote '"', null '');
'''.format(path=PATH)


# In[19]:


get_ipython().run_line_magic('sql', '$setup')


# In[20]:


get_ipython().run_cell_magic('sql', '', '-- Go defensive\nDROP GRAPH IF EXISTS northwind_graph CASCADE;\n\nCREATE GRAPH northwind_graph;\nSET graph_path = northwind_graph;\n')


# Loading the data in requires the data to be mounted in the database container (unless we can help `psycopg2` load files stored in the notebook container into the `agensgraph` container?

# In[21]:


get_ipython().run_cell_magic('sql', '', 'LOAD FROM categories AS source CREATE (n:category=to_jsonb(source));\nLOAD FROM customers AS source CREATE (n:customer=to_jsonb(source));\nLOAD FROM employees AS source CREATE (n:employee=to_jsonb(source));\ncreate vlabel if not exists "order";\nLOAD FROM orders AS source CREATE (n:"order"=to_jsonb(source));\nLOAD FROM products AS source CREATE (n:product=to_jsonb(source));\nLOAD FROM regions AS source CREATE (n:region=to_jsonb(source));\nLOAD FROM shippers AS source CREATE (n:shipper=to_jsonb(source));\nLOAD FROM suppliers AS source CREATE (n:supplier=to_jsonb(source));\nLOAD FROM territories AS source CREATE (n:territory=to_jsonb(source));\n')


# In[ ]: