Write your DB schema

Communicating complex ideas is always a challenge. That applies especially to technical descriptions. What if I told you I want a database that stores information from a scan? I might say there are Deb packages, users, and files, and you should store the state of each. You might ask me how they should be connected. We might spend lots of time typing back and forth, using English. But why spend that time, when “a picture is worth a thousand words”?

Behold our first attempt at documenting a database schema!

A bit small, isn't it?

Yes, that is a bit hard to read. Fear not, we used draw.io to generate XML for this schema. See instructions in our docs/README to open this file in the draw.io editor. The file you want to open is DB_Tables.xml.

Jumping into containers

Reviewing where we are now with A-Squared:

  • Start up Postgres db
  • Start up savant server (Flask-based app)

It seems we can benefit already from using Docker. For instance, if I can bring up my entire stack with one command, it will make it far easier for everyone to follow along. That gives benefits for demos, and for anyone on the internet to try out our code. Also, we can integrate this into our Travis builds and do a full stack test.

I have started this (see Github issue), and immediately run into a snag with a stack trace. I’m guessing some code is missing somewhere. No matter, we’ll fix that with a second Github issue.

A Year of Updates

A lot has been going on in the past year, and yet I have neglected to update!

Here’s a summary:

  • We moved all our code into a single repository. This is a “monorepo”, which is all the rage nowadays 🙂
  • We hired a new dev. Welcome Josiah!
  • We completed our prototype
  • We are working toward a demo
  • We are tracking demo progress using Github issues
  • We are adding new Youtube demos

Target Objective

I posted my target objective here on doveps last 2 weeks. With this one

Now creating a simple python code to achieve a perfect test for GraphDatabase

With this output on neo4j

Still studying on py2neo and python flask. Next top is putting all information like id, name, product name and etc. in different nodes with it’s API.

Please make a comment.

NEO4J Sample Query and Flask

Last week I posted a neo4j database sample for a small management system. Now we have to try run a sample query for that. Here’s the sample queries.

Basic Query: Who do people report to?

MATCH
(e:Employee)<-[:REPORTS_TO]-(sub:Employee)
RETURN
*

Who is in Robert(any name written in employee) reporting?

MATCH
p=(e:Employee)<-[:REPORTS_TO]-(sub:Employee)
WHERE
sub.firstname=”Robert”
RETURN
p

Who is the Big Boss?

MATCH
p=(e:Employee)
WHERE
NOT (e)-[REPORTS_TO]->()
RETURN
e.firstname as bigBoss


Companies Cross-Sell .

MATCH
(choc:Product {productName: ‘Chocolade’})
<-[:PRODUCT]-(:Order)<-[:SOLD]-(employee),
(employee)-[:SOLD]->(o2)-[:PRODUCT]->(other:Product)
RETURN
employee.firstname, other.productName, count(distinct
o2) as count
ORDER BY
count DESC
LIMIT 5;

 

I am learning to about the neo4j Flask. Neo4j flask is like django of python.

Continues learning about neo4j will be a must. I see now the importance of a NoSql or a graphdb unto Sql databases. Indeed it is much useful but is hard to query.

Next will be purely learning Flask and py2neo.

Querying Neo4j With Sql

Yes, that is really true. If you are not that good in neo4j because it is in whole different level than any other languages. Although it has not been specified and keep this as an secret by neo4j developers. It can be written  along side with other languages like Sql. I now that is really ridiculous, but yes it’s true.

In my side, we are on python parsing along with neo4j query. In short, we are doing neo4j output through python query by py2neo or maybe rest api client. But that is on whole different level.

To ensure that your project my project is doing write, my way is to check it first using the language of Sql to create neo4j nodes and relationship. Because it is very hard to take measures if you will undergo python parsing to create neo4j nodes and relationship and then you found out that you are doing it wrong. It is very critical to start again with new query because it is not that easy. Like I said, it is on different level.

One example is in this repository. https://github.com/Doveps/sample_neo4j

All tables are on csv file. It has been made on libre calc on linux  or excel on windows. And then write a code in sql form.

If you wanted to run it, here is the sample:

For Creating Customer:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/customers.csv” AS row

CREATE (:Customer {companyName: row.CompanyName, customerID: row.CustomerID, fax: row.Fax, phone: row.Phone});

For Creating Product:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/products.csv” AS row

CREATE (:Product {productName: row.ProductName, productID: row.ProductID, unitPrice: toFloat(row.UnitPrice)});

For Creating Suppliers:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/suppliers.csv” AS row

CREATE (:Supplier {companyName: row.CompanyName, supplierID: row.SupplierID});

For Creating Employees:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/employees.csv” AS row

CREATE (:Employee {employeeID:row.EmployeeID,  firstName: row.FirstName, lastName: row.LastName, title: row.Title});

For Creating Categories:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/categories.csv” AS row

CREATE (:Category {categoryID: row.CategoryID, categoryName: row.CategoryName, description: row.Description});

For Creating Orders:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/orders.csv” AS row

MERGE (order:Order {orderID: row.OrderID}) ON CREATE SET order.shipName =  row.ShipName;

For Creating Indexes:

CREATE INDEX ON :Product(productID);

CREATE INDEX ON :Product(productName);

CREATE INDEX ON :Category(categoryID);

CREATE INDEX ON :Employee(employeeID);

CREATE INDEX ON :Supplier(supplierID);

CREATE INDEX ON :Customer(customerID);

CREATE INDEX ON :Customer(customerName);

 

For Creating a Relationships:

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/orders.csv” AS row

MATCH (order:Order {orderID: row.OrderID})

MATCH (customer:Customer {customerID: row.CustomerID})

MERGE (customer)-[:PURCHASED]->(order);

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/products.csv” AS row

MATCH (product:Product {productID: row.ProductID})

MATCH (supplier:Supplier {supplierID: row.SupplierID})

MERGE (supplier)-[:SUPPLIES]->(product);

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/orders.csv” AS row

MATCH (order:Order {orderID: row.OrderID})

MATCH (product:Product {productID: row.ProductID})

MERGE (order)-[pu:PRODUCT]->(product)

ON CREATE SET pu.unitPrice = toFloat(row.UnitPrice), pu.quantity = toFloat(row.Quantity);

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/orders.csv” AS row

MATCH (order:Order {orderID: row.OrderID})

MATCH (employee:Employee {employeeID: row.EmployeeID})

MERGE (employee)-[:SOLD]->(order);

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/products.csv” AS row

MATCH (product:Product {productID: row.ProductID})

MATCH (category:Category {categoryID: row.CategoryID})

MERGE (product)-[:PART_OF]->(category);

USING PERIODIC COMMIT

LOAD CSV WITH HEADERS FROM “https://github.com/Doveps/sample_neo4j/blob/master/employees.csv” AS row

MATCH (employee:Employee {employeeID: row.EmployeeID})

MATCH (manager:Employee {employeeID: row.ReportsTo})

MERGE (employee)-[:REPORTS_TO]->(manager);

 

Note: If there is any error that occur. Please have a comment on issues in https://github.com/Doveps/sample_neo4j. Thanks!

 

Neo4j Relationships

I already finish all relationship at my database using cypher query, Here is the result.

Relationship of Category and Product
Relationship of Category and Product
Relationship of Customer and Order
Relationship of Customer and Order
Relationship of Product and Supplies
Relationship of Product and Supplies

 

Relationship of Employee, Order, Reports and the Sold Product
Relationship of Employee, Order, Reports and the Sold Product
Relationship of Order and Product
Relationship of Order and Product
Relationship of Employee reports to Sales Manager and Vice President
Relationship of Employee reports to Sales Manager and Vice President

Tomorrow i will test the codes, I have to create another data queries like the net and gross income. After that I will now start to mark up python query for neo4j using api rest client or the py2neo database.

 

Summary of the Objective for Neo4j Example

Objective:

Neo4j had a very different type of query language to any other database like MySql, Sql and etc. That’s why I am trying to practice different query of neo4j and create a simple relationship examples. And every relationship in any databases need tables. In neo4j, tables are nodes. Querying neo4j is not that easy, as a backend developer I must secure that nodes are compatible with other nodes for the relationship connections. Like creating an CustomerID in node customer which is primary key of the node customer and another CustomerID in node Order which is the foreign key to connect the two tables.

  • So the first objective is to create a simple Nodes and secure that the nodes are link to each other for the relationship connection.
  •  Next is to create Indexes of data for performance. 

    CREATE INDEX ON :Product(productID);

    CREATE INDEX ON :Product(productName);

    CREATE INDEX ON :Category(CategoryID);

    CREATE INDEX ON :Employee(employeeID);

    CREATE INDEX ON :Supplier(supplierID);

    CREATE INDEX ON :Customer(customerID);

    CREATE INDEX ON :Customer(customerNAME);

     

    • Then the relationships of one node to anotherAnd lastly, I will try to create an API or REST API

       

Neo4j Query

Im am still on creating neo4j nodes, property keys, indexes, and relationships. And I am in my 3rd week of studying now. Here is the sample of neo4j nodes and property keys.

CUSTOMER
CUSTOMER
Category
Category
EMPLOYEE
EMPLOYEE
ORDERS
ORDERS
PRODUCT
PRODUCT
SUPPLIER
SUPPLIER

 

As you can see. This are an example of a simple management database without index and relationships.

Next I’m going to do is to create the indexes and relationships.

-Mark Doctor