DEV Community: Emmanuel Allison

Useful Apache AGE Functions

Emmanuel Allison — Sun, 01 Oct 2023 21:43:34 +0000

Apache AGE is an extension for PostgreSQL which adds graph database functionalities to the relational database. Here are some AGE functions you should know.

`create_graph`

Creates a new graph.

-- You can write it this way.
SELECT create_graph('cities');
-- Or this way.
SELECT * FROM create_graph('cities', true);

`drop_graph`

Deletes a graph. The optional third parameter indicates if the delete operation should be cascaded (forced). If it is, all relations dependent on the graph will be deleted too.

-- You can write it this way.
SELECT drop_graph('cities', true);
-- Or this way.
SELECT * FROM drop_graph('cities', true);

`alter_graph`

Use this to rename you graphs.

-- Renames the graph 'CITIES' to 'cities'.

-- You can write it this way.
SELECT alter_graph('CITIES', 'RENAME', 'cities');
-- Or this way.
SELECT * FROM alter_graph('CITIES', 'RENAME', 'cities');

`create_vlabel`

Creates a new vertex label in the given graph.

SELECT * FROM create_vlabel('cities', 'City');

`create_elabel`

Creates a new edge label in the given graph.

SELECT * FROM create_elabel('cities', 'CONNECTED');

`_label_Id`

Returns the ID of the label of an edge or vertex.

SELECT * FROM _label_id('cities', 'CONNECTED');
(1 row)

`drop_label`

Drops an edge's or vertex's label. The optional third parameter indicates if the delete operation should be cascaded (forced). If it is, all relations dependent on the label will be deleted too.

SELECT drop_label('cities', 'City', true);

`cypher`

With the cypher() function, you can execute Cypher queries in a graph.

SELECT * FROM cypher('cities', $$
  CREATE (:City {name: 'New York', size_in_km: 783.8})
$$) as (v agtype);

`get_cypher_keywords`

Returns a list of Cypher grammar keywords.

SELECT * FROM get_cypher_keywords();

`create_complete_graph`

There are times you may need a graph in which all vertices are interconnected (that is, there is an edge between all vertices). This type of graph is called a complete graph, and this function can help you accomplish that quickly. All you need to do is supply the name of the new graph to be created, the number of vertices you want, the label of the edges and the label of the vertices.

-- You can write it this way.
SELECT create_complete_graph('cities', 5, 'CONNECTED', 'City');
-- Or this way.
SELECT * FROM create_complete_graph('cities', 5, 'CONNECTED', 'City');

Using Apache AGE Viewer, we can see the newly created complete graph.

I hope these will come in handy for you. You can learn more about Apache AGE in the official website and read its documentation here.

Using AGE Viewer to View Graphs

Emmanuel Allison — Sun, 30 Jul 2023 17:04:06 +0000

A graph is a data structure containing vertices (or nodes) and edges. The vertices represent objects or entities, while the edges represent the relationship between these vertices.

Using Apache AGE, PostgreSQL can be given the functionalities of a graph database. We will be able to create graph databases and manipulate them in PostgreSQL, and AGE Viewer gives us a visual representation of these graphs.

Installation

Apache AGE requires node (version 14.16.0 recommended) and npm to run. To download node, we'll be using Node Version Manager (nvm).

Installing `nvm`

Run the following command in your terminal.

sudo apt install curl 
curl https://raw.githubusercontent.com/creationix/nvm/master/install.sh | bash

After the installation is completed, reload the terminal to effect the changes.

Installing `node`

Install node version 14.16.0 using nvm.

nvm install 14.16.0

To check your installation, run the following commands to view the versions of node and npm.

node -v
npm -v

Installing AGE Viewer

Clone AGE Viewer from its repository.

git clone https://github.com/apache/age-viewer.git

Now, install the required node modules and start the viewer. To do that, go into the AGE Viewer folder and run:

npm run setup
npm run start

AGE Viewer will run on localhost:3000 if port 3000 is free.

Viewing a Graph Database

For this, we'll be using the same data used in my article, Six Degrees of Separation Using Apache AGE.

Connect to your database in AGE Viewer using the following parameters:

Connect URL: localhost
Connect Port: 5432 (PostgreSQL's port by default)
Database Name:
User Name:
Password: The username and password for the connection, if not set, will be the name and password of the current user.

Run the following code in AGE Viewer.

SELECT * FROM cypher('social_network', $$
    MATCH (a:Person)-[e]-(b:Person)
    RETURN a, e, b
$$) AS (a agtype, e agtype, b agtype);

The graph will be displayed.

Inside PostgreSQL #1

Emmanuel Allison — Sun, 30 Jul 2023 15:44:04 +0000

This article will be the first of a series of posts where we'll be learning about the PostgreSQL database and its internals. I admit that this is quite a vast topic, but I'm on a journey to learn it, and this series is a concise version of what I've learnt (Be my guest).

Before we begin this long adventure, it is beneficial to answer the question, "Why study the internals of PostgreSQL?"

10,000 Reasons

Having no thought, we all know the topic of database internals is not negotiable for those who are/aim to be database developers. An understanding of how databases work is paramount in the development of a database. Creating improvements to existing databases will also require understanding how it works presently.

What about those who aren't database developers? Maybe you're a developer who has to deal with databases (and most do), or perhaps a database administrator. In the dynamic world of databases, you will stand an advantage by knowing the trends. It will increase your understanding of the peculiarities of the different databases and aid the decision process of which one better suits your business needs.

Also, understanding the underlying infrastructure of databases will enable you to track down performance issues and optimise queries.

Conclusion

Although studying databases can be intimidating at first, learning it benefits the hobbyist, student, software developer, or database developer.

Debugging Apache AGE using Visual Studio Code in Ubuntu

Emmanuel Allison — Sun, 30 Jul 2023 13:04:08 +0000

Apache AGE (short for A Graph Extension) is an extension to PostgreSQL, giving it graph database functionality. It is open source, and contributions to the project are welcomed. An invaluable tool for this is being able to debug the AGE project, and that's what we'll be doing today.

This article is specifically written for Ubuntu, but can be applied to other Linux distributions.

Prerequisite

To debug AGE, we'll need the following.

PostgreSQL installed from source code.
Download and install AGE from the source code.
Download and install Visual Studio Code from here.
A C compiler. For this article, gcc is used.

Setup

Open the AGE source code in VS Code. You can do that either by opening the folder in VS code or by running the following in the terminal.

cd ~/age
code .

Replace ~/age with the path to your AGE source.

To configure the project for debugging, we create a folder .vscode in the project's root folder, and add the following files:launch.json, settings.json, and c_cpp_properties.json.

launch.json

{
  "version": "0.2.0",
  "configurations": [
    {
      "name": "(gdb) Attach",
      "type": "cppdbg",
      "request": "attach",
      "program": "/home/hallixon/postgres/bin/postgres",
      "MIMode": "gdb",
      "setupCommands": [
        {
          "description": "Enable pretty-printing for gdb",
          "text": "-enable-pretty-printing",
          "ignoreFailures": true
        },
        {
          "description": "Set Disassembly Flavor to Intel",
          "text": "-gdb-set disassembly-flavor intel",
          "ignoreFailures": true
        }
      ]
    }
  ]
}

The configuration above enables attaching VS Code's debugger to the postgres process. Replace /home/hallixon/postgres/bin/postgres with the path to your PostgreSQL installation.

settings.json

{
    "files.associations": {
        "postgres.h": "c",
        "genam.h": "c",
        "heapam.h": "c",
        "tableam.h": "c",
        "table.h": "c",
        "htup_details.h": "c",
        "skey.h": "c",
        "pg_operator.h": "c",
        "namespace.h": "c"
    }
}

This associates the header files with C language, and informs VS Code to use the same syntax highlighting for C on those files for this workspace.

c_cpp_properties.json

{
  "configurations": [
    {
      "name": "Linux",
      "includePath": [
        "/usr/include/**/**",
        "${workspaceFolder}/**",
        "/usr/include/**",
        "/home/hallixon/postgres/include/**",
        "/home/hallixon/postgres/include/server/**",
        "/home/hallixon/postgresql-13.11/src/**",
        "/home/hallixon/postgresql-13.11/**",
        "/home/hallixon/postgresql-13.11/src/backend/**",
        "/home/hallixon/postgres/lib/**",
        "${workspaceFolder}/src/include/**"
      ],
      "defines": [],
      "compilerPath": "/usr/bin/gcc",
      "cStandard": "c17",
      "cppStandard": "gnu++17",
      "intelliSenseMode": "linux-gcc-x64"
    }
  ],
  "version": 4
}

Replace all /home/hallixon/postgresql-13.11 with the path to your PostgreSQL source code, and /home/hallixon/postgres with the path to your PostgreSQL installation.

You're now set to go.

Running PostgreSQL and Apache AGE

You can check if PostgreSQL is running by running the following command.

ps -e | grep postgres

You should receive an output similar to the following.

18813 ?        00:00:00 postgres
18815 ?        00:00:00 postgres
18816 ?        00:00:00 postgres
18817 ?        00:00:00 postgres
18818 ?        00:00:00 postgres
18819 ?        00:00:00 postgres
18820 ?        00:00:00 postgres

If there was no output, then you can start PostgreSQL by running the code below in the folder containing your PostgreSQL installation.

./pg_ctl -D pgdata -l pg_logfile start

Where pgdata will be the path to PostgreSQL's database configuration files. By default, the environment variable PGDATA is used. pg_logfile is a log file for PostgreSQL to append its logs. If it doesn't exist, PostgreSQL will create it.

Now, create a new database and connect to it.

./createdb age_test
./psql age_test

Now, add the AGE extension to the database.

CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

To run cypher commands, we need a graph database. To create one, run the following code:

SELECT create_graph('test_graph');

Attaching the Debugger

We need the ID of the postgres process so we can debug it while it runs. To find the process ID, run the following SQL command in the database.

SELECT pg_backend_pid();

In VS Code, with the AGE project open, press F5. A popup will be opened for you to select the process to attach to. Now, type the process ID of the running postgres backend process, and select the matching process. The integrated terminal in VS Code will open, requiring Superuser access to process.

Enter y and put in your password.

Debugging the Cypher `reverse()` Method

Open agtype.c in the src/backend/utils/adt folder, and search for age_reverse in the code using Ctrl + F. This is the method that handles the reverse() cypher method.

Add a breakpoint like you see in the picture.

Now, go to your terminal and run the SQL query in your database.

SELECT *
FROM cypher('test_graph', $$
    RETURN reverse('everywhere')
$$) AS (reverse agtype);

The breakpoint will be hit.

You can view the values of the local variables by hovering over them, or in the Run and Debug View in VS Code, which is preferable.

You can step into the extract_variadic_args method, by pressing F11.

Now, step out of the method by pressing Shift+F11. Press F11 to move to the next line. In the Run and Debug View, you'll find out that the variable nargs, which is the number of arguments passed into the reverse() cypher method, is changed to 1.

Press F5 to continue running. In the terminal, you should see the result of query displayed.

   reverse    
--------------
 "erehwyreve"
(1 row)

Conclusion

In this article, we were able to debug the AGE source code. This step taken will be one the most important in the journey to understanding and contributing to the Apache AGE project.

Please feel free to comment below if you have questions or if this helped you. Thanks!

Six Degrees of Separation Using Apache AGE

Emmanuel Allison — Thu, 20 Jul 2023 17:35:51 +0000

Have you ever heard of Six degrees of separation? It is an idea introduced by Frigyes Karinthy which says that people are six or fewer social connections away from each other. Yes, the idea suggests that you are six or less phone calls away from Elon Musk 😃.

This concept is of great importance to social networks like Facebook, LinkedIn, Twitter, etc. For example, Twitter suggests that you follow certain accounts because one or some of the people you follow follows them. LinkedIn can tell the social distance between two people by their network.

How can we represent these in computers? The most effective way to represent this kind of data is using the graph data structure. A graph consists of nodes/vertices and edges. Every node represents an object, and edges connect nodes to show the relationship between them.

In a social network, people are nodes, and edge represent the relationship between them.

Social Network

Let's create a social network using Apache AGE, a graph extension for PostgreSQL, and with it, find out the degree of separation between people.

Prerequisites

To begin, you will need to have PostgreSQL, Apache AGE, and AGE Viewer installed.

To begin, create a new database and connect to it.

To be able to use the AGE extension, it needs to be loaded for every session. Also, to simplify our queries, we will add ag_catalog to PostgreSQL's search path.

CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

People

To simplify this example, each node will contain only one property, which will the name of the person

SELECT * from cypher('social_network', $$
    CREATE (:Person {name: 'Joseph'}),
     (:Person {name: 'Alice'}),
     (:Person {name: 'Ade'}),
     (:Person {name: 'Mariam'}),
     (:Person {name: 'Ryan'}),
     (:Person {name: 'Fred'})
$$) AS (v agtype);

Now, we create a graph.

SELECT create_graph('social_network');

Friends

Next, we will represent their relationships by creating an edge between them. So if Joseph and Alice are friends, an edge with the
label is_friends_with is created between them.

SELECT * from cypher('social_network', $$
    MATCH (x:Person), (y:Person) WHERE x.name = 'Joseph' AND y.name = 'Alice'
    CREATE (x)-[:Is_friends_with]->(y)-[:Is_friends_with]->(x)
$$) AS (e agtype);

To prevent this post from being too long, you can see the full source code used in the GitHub gist I created. Running the script will give you the network below.

Degree of Separation

To find out the degree of separation between two people, we look for the shortest path between them. For example, to find the degree of separation between Joseph and Ade, we use:

SELECT * from cypher('social_network', $$
    MATCH paths = (x:Person {name: 'Joseph'})-[:Is_friends_with * 1..6]->(y:Person {name: 'Ade'})

    RETURN min(length(paths)) as degree
$$) AS (degree agtype);

We receive an output which tells us that Joseph is 3 people away from Ade.

 degree 
--------
 3

Conclusion

Today, we learnt about six degrees of separation and how it could be implemented using Apache AGE. We could use this build recommender systems, evaluate the strength of social networks and in marketing analysis.

DEV Community: Emmanuel Allison

Useful Apache AGE Functions

create_graph

drop_graph

alter_graph

create_vlabel

create_elabel

_label_Id

drop_label

cypher

get_cypher_keywords

create_complete_graph

Using AGE Viewer to View Graphs

Installation

Installing nvm

Installing node

Installing AGE Viewer

Viewing a Graph Database

Inside PostgreSQL #1

10,000 Reasons

Conclusion

Debugging Apache AGE using Visual Studio Code in Ubuntu

Prerequisite

Setup

Running PostgreSQL and Apache AGE

Attaching the Debugger

Debugging the Cypher reverse() Method

Conclusion

Six Degrees of Separation Using Apache AGE

Social Network

Prerequisites

People

Friends

Degree of Separation

Conclusion

See also

`create_graph`

`drop_graph`

`alter_graph`

`create_vlabel`

`create_elabel`

`_label_Id`

`drop_label`

`cypher`

`get_cypher_keywords`

`create_complete_graph`

Installing `nvm`

Installing `node`

Debugging the Cypher `reverse()` Method