The script of AGE (ch. 01)
Tags: apacheage, database, postgres, sql
Published: 2023-04-22 13:05:37

## Introduction

The AGE extension for PostgreSQL is a powerful tool that can be used to store and query graph data. It is easy to use and learn, and it is compatible with PostgreSQL.

In this blog post, we will take a deep dive into the internals of the AGE extension. We will discuss how it works, how it is implemented, and how it can be used to store and query graph data.

## How it works

### Introduction to Graph Databases

To understand the internals of the AGE extension, it is important to first understand the basics of graph databases.

A graph database is designed to store and manipulate data as a set of nodes and edges, where nodes are entities and edges are the relationships between them. Graph databases are particularly useful when working with complex data models with many relationships, allowing for faster queries and simpler data models.

Graph databases can be contrasted with traditional relational databases, which store data in rows and columns. While relational databases excel at storing data with regular, well-defined structures, graph databases are better suited to storing and querying data with complex and irregular relationships.

**Introduction to PostgreSQL**

PostgreSQL is a popular relational database management system (RDBMS) that is noted for its support of advanced features, such as stored procedures, triggers, and transactions. One of the reasons for PostgreSQL's popularity is its flexibility, since it can be used for a wide variety of applications.

**Internals of AGE Extension**

The AGE extension for PostgreSQL builds on top of the relational database foundations of PostgreSQL and adds graph database functionality by implementing a powerful and efficient graph storage engine. This section will outline the most important internals of the AGE extension.

## The Script of the AGE
AGE has a sql script that is being called when it is loaded in top of postgresql, each version has its script and when the same major version it is updated we get a inter-update script which takes over the last version to the next one and the latest version is the last one with not preceding versions.

Here I will explain the current script [age--1.2.0.sql](https://github.com/apache/age/blob/master/age--1.2.0.sql)

It starts with the APACHE license as expected
Then it creates a tables for the age **catalog** we can call it the menu of AGE

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u48tvphx8jx5kb6hpvhk.png)

Explanation of the first created table:
```SQL
CREATE TABLE ag_graph (
  name name NOT NULL,
  namespace regnamespace NOT NULL
) WITH (OIDS);
```
The CREATE TABLE statement in PostgreSQL is used to create a new SQL table. In the example you provided, we are creating a new table called ag_graph.

CREATE TABLE ag_graph (

This is where we specify the name of the new table we're creating.

name name NOT NULL,

The first column we're creating is called name. The name datatype is a built-in Postgres data type that allows you to store names or identifiers. We've also added a NOT NULL constraint to ensure that every row in the table has a value for this column.

namespace regnamespace NOT NULL

The second column we're creating is called namespace. Here we're using the regnamespace data type, which stands for registered namespace. This data type is used in Postgres to store the OID (object identifier) of a namespace. We've also added a NOT NULL constraint to ensure that every row in the table has a value for this column.

The WITH (OIDS) clause is an option that was available in previous versions of Postgres to enable the use of object identifiers (OIDs) in the table. However, OIDs have been deprecated since version 12 and this clause is no longer necessary. If you're running a version of Postgres that is 12 or later, you can omit this clause.

To summarize, the CREATE TABLE statement in the example creates a new table called ag_graph with two columns called name and namespace. The name column has a name data type and the namespace column has a regnamespace data type, and both columns have a NOT NULL constraint.

So that, when we create a new graph entry using the following query
```SQL
SELECT * from create_graph('test');
```
A new entry gets inserted on that ag_graph table

```SQL
select * from ag_graph;
 name | namespace 
------+-----------
 test | test
(1 row)
```

```SQL
rrr=# \d ag_graph;
                Table "ag_catalog.ag_graph"
  Column   |     Type     | Collation | Nullable | Default 
-----------+--------------+-----------+----------+---------
 name      | name         |           | not null | 
 namespace | regnamespace |           | not null | 
Indexes:
    "ag_graph_name_index" UNIQUE, btree (name)
    "ag_graph_namespace_index" UNIQUE, btree (namespace)
    "ag_graph_oid_index" UNIQUE, btree (oid)


```

We have another table for the labels:
```SQL

CREATE TABLE ag_label (
  name name NOT NULL,
  graph oid NOT NULL,
  id label_id,
  kind label_kind,
  relation regclass NOT NULL,
  seq_name name NOT NULL
) WITH (OIDS);
```

Other staff are the definitions of the functions like cypher function which is the starting point of the AGE the postgresql considers that a function like other functions but AGE considers that as the entry point 
Here is the definition of them 
```SQL
--
-- query functions
--
CREATE FUNCTION ag_catalog.cypher(graph_name name = NULL,
                                  query_string cstring = NULL,
                                  params agtype = NULL)
RETURNS SETOF record
LANGUAGE c
AS 'MODULE_PATHNAME';
```



## Conclusion

The AGE extension for PostgreSQL provides a powerful and flexible framework for working with graph databases. By building on top of the solid foundations of PostgreSQL, the AGE extension allows users to combine the benefits of a graph database with the scalability and efficiency of a relational database. Whether you are working on a small project or a large enterprise application, the AGE extension provides the tools you need to manage complex data models with ease.

## References and resources

-     https://github.com/apache/age
-     https://age.apache.org/