DEV Community: Carla Sanches

How to Debug Apache AGE Source Code Using Apache Netbeans IDE

Carla Sanches — Sat, 22 Jul 2023 01:57:43 +0000

In the previous post, I demonstrated how to configure Netbeans for debugging PostgreSQL and Apache AGE. Now, in continuation of that topic, I will explain how to set project preferences and perform the debugging process. So, if you have already configured the PostgreSQL and Apache AGE projects in Netbeans, you can follow the next steps.

First, we need to create a new database. In the terminal, choose the directory of your preference. Then, start a new PostgreSQL service with the following commands:

initdb agedb
pg_ctl -D path/to/agedb -l logfile start
createdb agedb

Let's now return to the Netbeans projects. Specifically for the AGE project, there's no need to make any alterations from the last tutorial. You can leave it as it was created previously. However, we need to configure some preferences in the IDE for PostgreSQL.

In your PostgreSQL project, right-click and choose Properties. A new window will pop up. Choose the category Debug to set up the command to debug the code. Fill Debug command with psql agedb. This will start the command line tool to work with Apache AGE. Next, fill Working dir with the path to psql. In the source code, the psql executable is located in /src/bin/psql. This is the path Netbeans will use to run the debug command.

❗ Troubleshooting Tip 1: Even if you don't intend to use the Run command, it's essential to set it up. This is a problem that I spent a couple of hours to figure out. Set the Run command as psql agedb and the Run directory the same as the Debug Working dir, with the path to psql. Otherwise, Netbeans will show the message below, and the IDE will not recognize psql as an executable:

Figure 1 - “File is not an executable” issue.

You can let the remaining fields as default, and now the setup is ready to Debug. Right-click your Netbeans PostgreSQL project and choose Debug. The psql CLI tool will start. Connect the database with the AGE extension with the following commands:

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

The next step is to attach the debugger to the postgres process. Run select pg_backend_pid(); to find the process ID (pid). The terminal will return info similar to the following output:

pg_backend_pid 
----------------
          11587
(1 row)

Copy the number returned in the terminal and select Debug > Attach Debugger at the top toolbar. Paste the ID number in the Filter field. The system will return the postgres process info:

Figure 2 - Attach Debugger window.

For this tutorial, I left all the setups as default. The Debugger is Gdb Debugger, and the Host is localhost. I recommend not choosing a project because Netbeans will ask for the executable file, and you will face the same problem as Figure 2. So, select the postgres process and click OK.

❗ Troubleshoot Tip 2: You may face a debugger error when attaching the debugger to the postgres process, as illustrated by Figure 3.

Figure 3 - Ptrace “Operation not permitted” error.

The PostgreSQL Wiki says it may be due to Ubuntu introducing a patch to disallow the ptracing of non-child processes by non-root users. ⚠️ Warning: you can disable this restriction, but this is not recommended since this can cause vulnerabilities to the system. Do it at your own risk. I will update this post when I find a better solution for PostgreSQL. See the following thread to learn more: https://askubuntu.com/a/153970.

To temporarily disable the restriction, run:

echo 0 | sudo tee /proc/sys/kernel/yama/ptrace_scope

Note that it is necessary to run this command every time you start a new terminal session. While you can permanently allow it, this is not recommended. Edit /etc/sysctl.d/10-ptrace.conf and change the line:

kernel.yama.ptrace_scope = 1

kernel.yama.ptrace_scope = 0

Now, try to attach the postgres process again. If everything works, Netbeans will show this status:

Figure 4 - Netbeans Debug sessions.

To begin debugging, create a breakpoint on a line within the function you want to analyze. Afterward, call it via psql CLI. For instance, let's examine the create_graph() function, defined in age/src/backend/commands/graph_commands.c. The function header is Datum create_graph(PG_FUNCTION_ARGS). In Figure 5, I have added a breakpoint in the first function call:

Figure 5 - Breakpoint example.

In the CLI, call the create_graph() function using the following command:

SELECT * FROM create_graph('graph_name');

You can see that the Debug will stop at/close to the breakpoint:

Figure 6 - Debug stopped at a breakpoint at graph_commands.c file, line 69.

You can navigate through Debug buttons at the top (Step Over (F8), Step Into (F7), Step Out (Ctrl+F7), and Run to Cursor (F4)) to examine each line and see the variables at the Variables tab at the bottom:

Figure 7 - Debug “Step Over” navigation.

Figure 9 - Debug navigation buttons.

And when you are done with debugging the function, you can click Continue (F5) to proceed with the execution:

Figure 10 - Proceeding with program execution.

You can finish the Debugger session by clicking the red square button at the debug tools or pressing Shift+F5. Remember to run \q to quit the psql CLI.

And that’s it! This was a simple tutorial on how to Debug Apache AGE functions via Netbeans IDE. I hope you liked it and hope this can make the Debug process easier.

References

Ask Ubuntu. “What is the 'ptrace_scope' workaround for Wine programs and are there any risks?.” 2012. Available at https://askubuntu.com/q/146160. Accessed on 07/20/2023.

The PostgreSQL Wiki. Working with Eclipse. Available at https://wiki.postgresql.org/wiki/Working_with_Eclipse. Accessed on 07/20/2023.

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age

How to Configure Apache AGE and PostgreSQL for Debugging in Apache NetBeans IDE

Carla Sanches — Sat, 01 Jul 2023 04:17:09 +0000

In the previous post, I showcased 10 NetBeans IDE tools to accelerate the software development process. One of them is Debugging. I attempted to solely use GDB in the terminal for application debugging. However, it was challenging to find the exact line of the code I was debugging and determine which function to debug. This made the process extremely slow. With this tool, it is easier to visualize variables and identify the specific line where issues occur in the code. In this post, I will provide a step-by-step guide to configuring Apache AGE in NetBeans to utilize the debugging tool.

1. Prerequisites

For this tutorial, I used the following resources:

Ubuntu 22.04 LTS;
NetBeans 18;
Apache AGE 1.3.0;
PostgreSQL 13.

If you don't have Apache AGE and/or PostgreSQL installed yet, you can follow these guides to install from the source code according to your operating system:

2. C/C++ Environment Setup

By default, when creating a C++ project, NetBeans offers the following options:

To configure full support for C/C++ projects, we need to add a plugin. In the top toolbar, go to Tools > Plugins. Then, select the Settings tab and check the option NetBeans 8.2 Plugin Portal:

Next, select Available Plugins tab and click Check for Newest button. The option for C/C++ should appear. Check this option and click on the Install button. A new window will open. To proceed with the installation, click Next and accept the terms and license agreements. You will also see a selection button next to the word Plugins. This button indicates all the features that come with the installation. Finally, click on Install to complete the process.

The installation may take a few minutes to complete. Another pop-up window may appear at the end, asking for confirmation. Click on Continue, and the installation will finish successfully.

To configure the debugging and compilation tools, go to the top toolbar and select Tools > Options, then click on the C/C++ tab. In the Build Tools section, make sure that the directories for the tools are correctly specified:

3. Importing Projects in NetBeans

After installing the plugin, you will notice that new options have appeared for creating C/C++ projects. Go to File > New Project > C/C++ and select option C/C++ Project with Existing Sources. Next, I will explain how to create projects for the source code of PostgreSQL and Apache AGE.

3.1. PostgreSQL

Select the directory of the PostgreSQL repository. You can leave the options marked as default and only change the Select Configuration Mode to Custom.

NetBeans will automatically find the PostgreSQL configuration file. Keep the option Pre-Build selected, and then we can configure the compilation flags. In the Arguments section, set CC=${IDE_CC} to configure the compiler to the directory specified in the last step of the previous section. A very important step is to define the flags for debugging and installation. Set CFLAGS="-g3 -O0" --enable-cassert --enable-debug for debugging, and --prefix pointing to the installation directory of PostgreSQL. In my case, it is --prefix=/usr/local/postgresql/13.0.

In the next step, specify the working directory, the commands used for clean and build, and whether you want these commands executed as soon as the configuration is finished. Keep the option Clean and Build After Finish selected. The working directory should be the directory of the PostgreSQL source code. Use ${MAKE} to configure the make command to the directory set in the last step of Section 2.

The next step is to select the directories where the relevant source code for the project is. Since we are only working with PostgreSQL, you can leave the options as default.

Next, we select the directories to include. Choose the option Manual Configuration and click Edit in the Include Directories field. Click Add to add a new folder and browse for the directories /src/include and /src/fe_utils within the PostgreSQL source code directory.

Finally, let's define a name for the project and the directory where it will be saved. To avoid mixing the project with the PostgreSQL Git directory, I created a separate directory called netbeans-projects.

Click on Finish, and if you have selected the option to Clean and Build after Finish, you will see in the integrated terminal that the command will compile the source code and return the following message, indicating that PostgreSQL has been successfully compiled.

You can complete the installation through the integrated terminal by accessing the working directory. Click the Output tab and select the option on the left, Open Working Directory in Terminal. Then, run the command make install to finalize the installation.

At the end of the installation, the terminal will return the message Installation complete.

3.2. Apache AGE

Apache AGE follows the step-by-step process for configuring PostgreSQL. Therefore, I will only explain the different settings from the previous walkthrough.

After creating a new project and selecting the Apache AGE source code directory in the Pre-Build option, export the environment variable CFLAGS="-g3 -O0" to enable debugging.

Next, in the Build Actions option, configure the Clean Command as ${MAKE} PG_CONFIG=/usr/local/postgresql/13.0/bin/pg_config distclean and the Build Command as ${MAKE} PG_CONFIG=/usr/local/postgresql/13.0/bin/pg_config install. Note that the PG_CONFIG variable will point to the pg_config file in the PostgreSQL installation directory.

Skipping to the Code Assistance Configuration step, add the src/include folder inside the age source code directory, as well as the directories /src/include and /src/fe_utils from PostgreSQL. This will help prevent error suggestions when opening a code file in the IDE.

Continue with the remaining configurations following the step-by-step process for PostgreSQL. In the end, if you have selected the option to Clean and Build after Finish, you will see in the integrated terminal that the command will compile the source code and return the following message, indicating that AGE has been successfully compiled and installed.

4. Conclusion

In this tutorial, I have provided a step-by-step guide on how to configure a NetBeans project to debug the Apache AGE source code. If you found this helpful, don't miss the next tutorial, where I will walk you through the process of debugging.

5. Contribute to Apache AGE

Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age

6. References

This tutorial was based on the article Working with Eclipse, available at https://wiki.postgresql.org/wiki/Working_with_Eclipse.

Apache AGE + Apache NetBeans: 10 Functionalities to Speed up code development

Carla Sanches — Sat, 27 May 2023 00:07:36 +0000

Over the past month, I have been working on a new grammar for Apache AGE using the NetBeans IDE. As a result of this experience, I have identified several features that can speed up the code development process.

In this post, I will share 10 of these features essential for those who wish to take full advantage of the IDE environment and develop more quickly and efficiently. Over the next few days, I will publish other posts with more tips on using NetBeans so that you can further optimize your workflow.

1. Debug

You don't have to spend hours in the terminal looking for the correct line number to stop or typing each variable to print. Use the interface to monitor all variables and structures and evaluate the code step by step, quickly detecting errors.

Figure 1 - NetBeans 17 debug tool.

2. Accessing definitions with Ctrl + click

Are you studying a new code and need to review a structure definition? Have you found a bug in a function and want to see how it was defined? Navigate to where the structure, function, or variable was declared with Ctrl + click.

Figure 2 - Link to function declarations demonstration on NetBeans 17 using Ctrl + click.

3. Multiple cursors

Do you need to type several identical or similar lines within the same code? Speed up the process with multiple cursors. Hold down Ctrl + Shift and click elsewhere in the editor to create many cursors and type all at once.

Figure 3 - Using multiple cursors in NetBeans 17 with Ctrl + Shift.

4. Parameter passing

With an IDE, you don't have to pass commands to the terminal every time you run the program. Just set default commands for Run and Debug, then choose an option. The parameters will always be saved there.

Figure 4 - Configuring commands for Run and Debug in NetBeans 17.

5. Integration with Git/GitHub

These are some Git/GitHub features, among other functionalities, that you can use with the NetBeans IDE:

View the lines that were changed.
Monitor branches.
Clone a repository.
Create commits.

Figure 5 - Git Diff demonstration.

6. Spell-Checking

Have you ever had a typo corrected by a colleague? Don't take it personally. It happens! But you can avoid it by using the spell-checking tool.

Figure 6 - NetBeans 17 spell checking.

7. Search tool

Search for variables, structures, and any other type of text within the project using the search tool.

Figure 7 - NetBeans 17 search tool.

8. Autocomplete

Program faster using suggestions for autocomplete. Start typing the structure name, then hold down Ctrl + spacebar to view the autocomplete suggestions.

Figure 8 - NetBeans 17 autocomplete tool.

9. Code Assistance

Avoid unexpected compilation errors with Code Assistance. The assistant helps identify possible logic problems in your code and suggests corrections to prevent the code from breaking.

Figure 9 - Fixing error in NetBeans 17 with the Code Assistant.

10. Integrated Console

The integrated console makes it easier to organize the environment and program everything in one place. You can use the OS terminal within the IDE.

Figure 10 - NetBeans 17 integrated terminal.

Conclusion

Using an IDE makes the developer's life much easier. It is possible to avoid unexpected errors and program more efficiently. The resources I use the most are Debug, terminal, parameter passing, and integration with Git/GitHub. Was any of these tips new to you? Do you know of any other advantages that I didn't mention? Feel free to share your thoughts in the comments below!

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

How an RDBMS works #5: PostgreSQL Database Cluster Logical Structure

Carla Sanches — Fri, 12 May 2023 23:11:28 +0000

1. Introduction

In our latest blog post, we explored the implementation of a graph database using PostgreSQL. We used the Apache AGE extension, which allows us to transform an SQL database into a graph database by combining SQL with Open Cypher. In this example, we started the PostgreSQL server as a service, using the command sudo service postgresql start. This command is available when installing PostgreSQL from official Linux distribution packages. The script checks if the database cluster exists in the default directory and creates a new one if it does not find. Finally, it starts the service to run the cluster.

In this article, we will introduce the concept of a cluster by presenting the logical structure of the PostgreSQL database cluster. Understanding this structure is fundamental for comprehending its operation, managing it effectively, simultaneously running separate databases, and optimizing performance with this tool.

2. The database cluster logical structure

In PostgreSQL, the database cluster is a collection of databases managed by a server that runs on a single host. This concept may seem confusing, so let's break it down. Let's go back to the example of our scientific article database. This database only stores articles and their information (such as metadata). Suppose we have a second database containing information about the authors of these articles and a third database storing information about the publication vehicles of these articles. We can represent a database as shown in Figure 1.

Figure 1 - Representation of a database that contains scientific papers. Elaborated by author.

Now, we can represent the cluster as a large structure that will store databases as objects, as shown in Figure 2.

Figure 2 - Representation of the PostgreSQL database cluster, that contains a collection of three databases. Elaborated by author.

In practice, if you installed PostgreSQL from the source code, it all starts with the following commands:

initdb
pg_ctl start -l log

The initdb command creates a new database cluster. With pg_ctl, we can manually control the server, passing parameters to start, restart, and stop, among others. When starting the server, it will manage the cluster and run on a single host, with localhost as the default. In other articles, I will present more details about the server. Basically, it is the parent of all processes related to cluster management. As explained in Section 1, if you installed PostgreSQL from Linux distribution packages, the same will happen when running sudo service postgresql start. This command creates a new cluster if there is none and starts the server that manages it.

3. Conclusion

In this article, we reviewed the example of the scientific article database and learned the concept of the PostgreSQL database cluster and its logical structure. We also learned the commands that create the cluster and start the server that manages it. In the next post, I will present the concepts related to the cluster's physical structure, where we can delve deeper into the practical part of its operation.

4. Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

5. Related Articles

How an RDBMS works #4: Creating a citation graph with PostgreSQL + Apache AGE

6. References

SUZUKI, Hironobu. The Internals of PostgreSQL for database administrators and system developers. Interdb. 2015. Available at https://www.interdb.jp/pg/. Accessed on 05/12/2023.

The PostgreSQL Global Development Group. PostgreSQL 13.11 Documentation, Chapter VI. Reference, PostgreSQL Server Applications, initdb. Available at https://www.postgresql.org/docs/13/app-initdb.html. Accessed on 05/12/2023.

7. Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

AGE Viewer: How to display the contents of your Apache AGE database

Carla Sanches — Sun, 30 Apr 2023 03:09:13 +0000

This blog post is just a brief introduction to the AGE Viewer, a tool for visualizing graphs created with Apache AGE. Specifically, I will show how to visualize the citation graph created in the last post of the "How an RDBMS works" series.

To use AGE Viewer, it will be necessary to download and install it. If you do not have the program on your machine yet, I recommend following this step-by-step guide: Installing apache age-viewer. It is very simple. I am using Ubuntu 22.04 LTS with AGE Viewer 1.0.

Through PostgreSQL CLI, it is possible to display all vertices of the graph with the query below, but it is not very easy to understand the output generated:



SELECT * FROM cypher('citation_graph', $$
MATCH (v)
RETURN v
$$) as (v agtype);

Output:



v                                                                                                                                                                                                                                             
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131970, "label": "Article", "properties": {"id": "williams2013towards", "year": 2013, "title": "Towards affective algorithmic composition", "author": ["Duncan Williams", "Alexis Kirke", "Eduardo R Miranda", "Etienne Roesch", "Slawomir Nasuto"], "publisher": "University of Jyväskylä, Department of Music"}}::vertex
 {"id": 844424930131971, "label": "Article", "properties": {"id": "schubert1999measurement", "year": 1999, "title": "Measurement and Time Series Analysis of Emotion in Music", "author": ["Emery Schubert"], "publisher": "University of New South Wales. Music & Music Education"}}::vertex
 {"id": 844424930131973, "label": "Article", "properties": {"id": "scherer2004which", "year": 2004, "title": "Which Emotions Can be Induced by Music? What Are the Underlying Mechanisms? And How Can We Measure Them?", "author": ["Klaus R. Scherer"], "publisher": "Journal of New Music Research"}}::vertex
 {"id": 844424930131974, "label": "Article", "properties": {"id": "gabrielsson2001emotion", "year": 2001, "title": "Emotion perceived and emotion felt: Same or different?", "author": ["Alf Gabrielsson"], "publisher": "Musicae Scientiae"}}::vertex
 {"id": 844424930131975, "label": "Article", "properties": {"id": "scherer2001emotional", "year": 2001, "title": "Emotional effects of music: Production rules", "author": ["Klaus Scherer", "Marcel Zentner"], "publisher": "Oxford University Press"}}::vertex
 {"id": 844424930131976, "label": "Article", "properties": {"id": "juslin2001communicating", "year": 2001, "title": "Communicating emotion in music performance: A review and a theoretical framework", "author": ["Patrik Juslin"], "publisher": "Oxford University Press"}}::vertex
 {"id": 844424930131977, "label": "Article", "properties": {"id": "williams2015dynamic", "year": 2015, "title": "Dynamic Game Soundtrack Generation in Response to a Continuously Varying Emotional Trajectory", "author": ["Duncan Williams", "Alexis Kirke", "Joel Eaton", "Eduardo Miranda", "Ian Daly", "James Hallowell", "Etienne Roesch", "Faustina Hwang", "Slawomir Nasuto"], "publisher": "Audio Engineering Society Conference: 56th International Conference: Audio for Games"}}::vertex
 {"id": 844424930131978, "label": "Article", "properties": {"id": "williams2015investigating", "year": 2015, "title": "Investigating Perceived Emotional Correlates of Rhythmic Density in Algorithmic Music Composition", "author": ["Duncan Williams", "Alexis Kirke", "Joel Eaton", "Eduardo Miranda", "Ian Daly", "James Hallowell", "James Weaver", "Asad Malik", "Etienne Roesch", "Faustina Hwang", "Slawomir Nasuto"], "publisher": "Association for Computing Machinery"}}::vertex
 {"id": 844424930131979, "label": "Article", "properties": {"id": "daly2015towards", "year": 2015, "title": "Towards human-computer music interaction: Evaluation of an affectively-driven music generator via galvanic skin response measures", "author": ["Duncan Williams", "Alexis Kirke", "Eduardo Miranda", "Ian Daly", "Faustina Hwang", "Slawomir Nasuto", "Asad Malik", "James Weaver"], "publisher": "2015 7th Computer Science and Electronic Engineering Conference (CEEC)"}}::vertex
 {"id": 844424930131980, "label": "Article", "properties": {"id": "kirke2013artificial", "year": 2013, "title": "Artificial affective listening towards a machine learning tool for sound-based emotion therapy and control", "author": ["Alexis Kirke", "Eduardo Miranda", "Slawomir Nasuto"], "publisher": "Proceedings of the Sound and Music Computing Conference"}}::vertex
 {"id": 844424930131981, "label": "Article", "properties": {"id": "kirke2012learningto", "year": 2012, "title": "Learning to Make Feelings: Expressive Performance as a part of a machine learning tool for sound-based emotion therapy and control", "author": ["Alexis Kirke", "Eduardo Miranda", "Slawomir Nasuto"], "publisher": "the 9th Intl Symp on Computer Music Modeling and Retrieval"}}::vertex
 {"id": 844424930131982, "label": "Article", "properties": {"id": "lopez2010real", "year": 2010, "title": "Real-Time Emotion-Driven Music Engine", "author": ["Alex Lopez", "Antonio Oliveira", "Amilcar Cardoso"], "publisher": "the 9th Intl Symp on Computer Music Modeling and Retrieval"}}::vertex
 {"id": 844424930131983, "label": "Article", "properties": {"id": "oliveira2008affective", "year": 2008, "title": "Affective-driven music production: selection and transformation of music", "author": ["Antonio Oliveira", "Amilcar Cardoso"], "publisher": "ARTECH"}}::vertex
 {"id": 844424930131984, "label": "Article", "properties": {"id": "oliveira2008modeling", "year": 2008, "title": "Modeling affective content of music: A knowledge base approach", "author": ["Antonio Oliveira", "Amilcar Cardoso"], "publisher": "Sound and Music Computing Conference"}}::vertex
 {"id": 844424930131985, "label": "Article", "properties": {"id": "livingstone2007controlling", "year": 2007, "title": "Controlling musical emotionality: an affective computational architecture for influencing musical emotions", "author": ["Steven R. Livingstone", "Ralf Mühlberger", "Andrew Brown", "Andrew Loch"], "publisher": "Digital Creativity"}}::vertex
 {"id": 844424930131986, "label": "Article", "properties": {"id": "livingstone2005dynamic", "year": 2005, "title": "Dynamic Response: Real-Time Adaptation for Music Emotion", "author": ["teven R. Livingstone", "Andrew Brown"], "publisher": "Creativity & Cognition Studios Press"}}::vertex
 {"id": 844424930131987, "label": "Article", "properties": {"id": "oliveira2009automatic", "year": 2009, "title": "Automatic manipulation of music to express desired emotions", "author": ["Antonio Oliveira", "Amilcar Cardoso"], "publisher": "Proceedings of the 6th Sound and Music Computing Conference"}}::vertex
 {"id": 844424930131969, "label": "Article", "properties": {"id": "williams2015affect", "year": 2015, "title": "Investigating affect in algorithmic composition systems", "author": ["Duncan Williams", "Alexis Kirke", "Eduardo R Miranda", "Etienne Roesch", "Ian Daly", "Slawomir Nasuto"], "publisher": "Psychology of Music"}}::vertex
 {"id": 844424930131988, "label": "Article", "properties": {"id": "russell1980circumplex", "year": 1980, "title": "A circumplex model of affect", "author": ["James Russel"], "publisher": "American Psychological Association"}}::vertex

To visualize and analyze the graph we built, we need to configure the connection with the database. We use nvm to start the AGE Viewer at localhost:3000, and this step must be executed within the age-viewer directory:



cd age-viewer
npm run setup
npm run start

When opening the page, provide the URL where the AGE Viewer is running, the port number of the database, database name, and the username and password for the database. Below is an example of the configuration. Then click on the "Connect" button.



Connect URL: localhost

Connect Port: 5432

Database Name: papersdb

User Name: postgres

Password: 12345

At the top of the page, there is a field to define queries. We will retrieve the data of the vertices and edges, and then we can visualize the graph we built. After specifying the query, click on the play button "Run Query" next to the editing field.



SELECT * FROM cypher('citation_graph', $$
MATCH (a)
OPTIONAL MATCH (a)-[e]->(b)
RETURN a, e, b
$$) as (a agtype, e agtype, b agtype);

Next, the system will return the graph with all vertices and edges. We can see in Figure 1 that all papers have connections, indicating that they have at least one citation made by a related research source.

Figure 1 - Citation graph. Elaborated by author and generated with AGE Viewer.

In addition to displaying the graph, we can explore other queries to perform different types of analysis. For instance, We can check for self-citation, filter articles by author names, and detect circular citations, among other things. However, for today's purposes, we will stop here and save more complex analyses for a future post!

Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

How an RDBMS works #4: Creating a citation graph with PostgreSQL + Apache AGE

Carla Sanches — Sun, 30 Apr 2023 01:14:27 +0000

In the previous post, I presented an example of an academic article database to explain the process of analyzing database requirements. Today, we'll take a more practical approach and explore this example by creating an academic paper database in PostgreSQL and a citation graph using Apache AGE. The queries for building this database are available in this GitHub gist.

1. What are graphs?

A graph G = (V, E) is a data structure composed of a set of vertices (V) and a set of edges (E) (Vilas Boas, 2016). We can represent a graph as shown in Figure 1. The letters from 'a' to 'e' represent the vertices, and the edges are the lines that connect them.

Figure 1 - Representation of a graph formed by five vertices and seven edges. Adapted from Vilas Boas (2016).

In the example of our database, each vertex represents an academic paper. The relationship between these papers is "cited by" since an article references (or cites) other articles. An edge represents a relationship.

2. Requirements

To follow this tutorial, you need to install PostgreSQL and Apache AGE. Below is a list of tutorials to install these programs:

In this tutorial, I created the database using PostgreSQL 13 and Apache AGE 1.3.0. At the time of this post, Apache AGE supports only PostgreSQL 11, 12, and 13. The operating system used is Ubuntu 22.04 LTS.

3. Building the database

Before we begin building the database, let's list some properties of scientific papers:

Title;
Author(s);
Year of publication;
Publication venue.

As defined in Section 1, the relationship between two papers can be defined as "cited by" or "cites" for simplicity. Since one paper cites another, we can represent this relationship as shown in Figure 2.

Figure 2 - Relationship between two papers. Elaborated by author.

Now we can create the database. Assuming that PostgreSQL has just been installed, the following commands will start the PostgreSQL service and call the command-line interface:

sudo service postgresql start
sudo -u postgres psql

Once in the interface, we create a database named papersdb and connect to it.

create database papersdb;
\c papersdb

To use the Apache AGE extension, it is necessary to install it on the server and load it for each session. And to simplify queries, we add ag_catalog to the search_path.

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

Next, we create a graph called citation_graph:

SELECT * FROM create_graph('citation_graph');

We now create some entries for the academic papers, which contain the properties defined at the beginning of this section. We will also define an ID to make it easier to connect the edges between each paper. The id structure is according to the last name of the first author, publication year, and the beginning of the publication title, as in the example oliveira2009automatic.

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'williams2015affect', title: 'Investigating affect in algorithmic composition systems', publisher: 'Psychology of Music',year: 2015, author:['Duncan Williams', 'Alexis Kirke', 'Eduardo R Miranda', 'Etienne Roesch', 'Ian Daly', 'Slawomir Nasuto']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'williams2013towards', title: 'Towards affective algorithmic composition', publisher: 'University of Jyväskylä, Department of Music', year: 2013, author:['Duncan Williams', 'Alexis Kirke', 'Eduardo R Miranda', 'Etienne Roesch', 'Slawomir Nasuto']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'schubert1999measurement', title: 'Measurement and Time Series Analysis of Emotion in Music', publisher: 'University of New South Wales. Music & Music Education', year: 1999, author:['Emery Schubert']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'scherer2004which', title: 'Which Emotions Can be Induced by Music? What Are the Underlying Mechanisms? And How Can We Measure Them?', publisher: 'Journal of New Music Research', year: 2004, author:['Klaus R. Scherer']})                          
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'gabrielsson2001emotion', title: 'Emotion perceived and emotion felt: Same or different?', publisher: 'Musicae Scientiae', year: 2001, author:['Alf Gabrielsson']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'scherer2001emotional', title: 'Emotional effects of music: Production rules', publisher: 'Oxford University Press', year: 2001, author:['Klaus Scherer', 'Marcel Zentner']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'juslin2001communicating', title: 'Communicating emotion in music performance: A review and a theoretical framework', publisher: 'Oxford University Press', year: 2001, author:['Patrik Juslin']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'williams2015dynamic', title: 'Dynamic Game Soundtrack Generation in Response to a Continuously Varying Emotional Trajectory', publisher: 'Audio Engineering Society Conference: 56th International Conference: Audio for Games', year: 2015, author:['Duncan Williams', 'Alexis Kirke', 'Joel Eaton', 'Eduardo Miranda', 'Ian Daly', 'James Hallowell', 'Etienne Roesch', 'Faustina Hwang', 'Slawomir Nasuto']})            
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$
CREATE (v:Article {id: 'williams2015investigating', title: 'Investigating Perceived Emotional Correlates of Rhythmic Density in Algorithmic Music Composition', publisher: 'Association for Computing Machinery', year: 2015, author:['Duncan Williams', 'Alexis Kirke', 'Joel Eaton', 'Eduardo Miranda', 'Ian Daly', 'James Hallowell', 'James Weaver', 'Asad Malik', 'Etienne Roesch', 'Faustina Hwang', 'Slawomir Nasuto']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'daly2015towards', title: 'Towards human-computer music interaction: Evaluation of an affectively-driven music generator via galvanic skin response measures',  publisher: '2015 7th Computer Science and Electronic Engineering Conference (CEEC)', year: 2015, author:['Duncan Williams', 'Alexis Kirke', 'Eduardo Miranda', 'Ian Daly', 'Faustina Hwang', 'Slawomir Nasuto', 'Asad Malik', 'James Weaver']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'kirke2013artificial', title: 'Artificial affective listening towards a machine learning tool for sound-based emotion therapy and control',  publisher: 'Proceedings of the Sound and Music Computing Conference', year: 2013, author:['Alexis Kirke', 'Eduardo Miranda', 'Slawomir Nasuto']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'kirke2012learningto', title: 'Learning to Make Feelings: Expressive Performance as a part of a machine learning tool for sound-based emotion therapy and control',  publisher: 'the 9th Intl Symp on Computer Music Modeling and Retrieval', year: 2012, author:['Alexis Kirke', 'Eduardo Miranda', 'Slawomir Nasuto']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'lopez2010real', title: 'Real-Time Emotion-Driven Music Engine',  publisher: 'the 9th Intl Symp on Computer Music Modeling and Retrieval', year: 2010, author:['Alex Lopez', 'Antonio Oliveira', 'Amilcar Cardoso']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'oliveira2008affective', title: 'Affective-driven music production: selection and transformation of music',  publisher: 'ARTECH', year: 2008, author:['Antonio Oliveira', 'Amilcar Cardoso']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'oliveira2008modeling', title: 'Modeling affective content of music: A knowledge base approach',  publisher: 'Sound and Music Computing Conference', year: 2008, author:['Antonio Oliveira', 'Amilcar Cardoso']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'livingstone2007controlling', title: 'Controlling musical emotionality: an affective computational architecture for influencing musical emotions',  publisher: 'Digital Creativity', year: 2007, author:['Steven R. Livingstone', 'Ralf Mühlberger', 'Andrew Brown', 'Andrew Loch']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'livingstone2005dynamic', title: 'Dynamic Response: Real-Time Adaptation for Music Emotion',  publisher: 'Creativity & Cognition Studios Press', year: 2005, author:['Steven R. Livingstone','Andrew Brown']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'oliveira2009automatic', title: 'Automatic manipulation of music to express desired emotions',  publisher: 'Proceedings of the 6th Sound and Music Computing Conference', year: 2009, author:['Antonio Oliveira', 'Amilcar Cardoso']})
RETURN v $$) as (v agtype);

SELECT * FROM cypher('citation_graph', $$        
CREATE (v:Article { id: 'russell1980circumplex', title: 'A circumplex model of affect',  publisher: 'American Psychological Association', year: 1980, author:['James Russel']})
RETURN v $$) as (v agtype);

Finally, we define the relationships between articles based on the citations found within each one. We can use the id to retrieve the vertices and connect them:

SELECT * FROM cypher ('citation_graph', $$ 
MATCH (a:Article), (b:Article)
WHERE a.id = 'williams2015affect' and b.id = 'williams2013towards'
CREATE (a)-[e:CITES]->(b)
RETURN e                 
$$) as (e agtype);

To avoid making this post too long, I will not repeat all the queries at this step. Unlike the entries with the information of the papers, which is only one for each paper, the relationships can be "one to many." This is the link to the GitHub gist containing all the queries to create the relationships between the papers. You can copy and paste on your PostgreSQL CLI to run the queries. With the definition of all edges, we have finished building our citation graph, presented in Figure 3:

Figure 3 - Citation graph. Elaborated by author and generated with AGE Viewer.

4. Conclusions

In this article, we have learned what graphs are and how to build a graph database using the PostgreSQL extension Apache AGE. In the next post, I will show you how to use the AGE Viewer to display the graph illustrated in Figure 3.

5. Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

6. Related Articles

Collaborate to Innovate: How a Social Network Can Help You Find Your Dream Pair Programming Team
Create a Word Guessing Game With Apache AGE
Step-by-Step Guide to Install PostgreSQL via Package Management for Apache AGE on Ubuntu
Easy guide to install and configure PostgreSQL with Apache AGE on Windows
Installing Apache AGE with Docker on Windows
Easy Installation of PostgreSQL, AGE & Age-viewer on Mac

7. References

Vilas Boas, Matheus Guedes. Graph Theory Class Notes - Basic Concepts on Graphs - Definitions and Theorems. Federal University of Ouro Preto. 2016. Unpublished.

Banner designed by Freepik.

8. Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

Step-by-Step Guide to Install PostgreSQL via Package Management for Apache AGE on Ubuntu

Carla Sanches — Wed, 26 Apr 2023 00:56:12 +0000

PostgreSQL is a robust and reliable open-source relational database that supports ACID and is highly scalable, making it one of the most popular choices for data storage. With Apache AGE, it's possible to query and analyze graph data in PostgreSQL using the OpenCypher technology in conjunction with SQL. For this tutorial, I am using Ubuntu 22.04 LTS and will provide a step-by-step guide to install the new version of Apache AGE 1.3.0 for PostgreSQL 13. If you're curious to know what the main features of this version are, you can check out this article.

Requirements

To install Apache AGE on Ubuntu, you need the following libraries:

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison

PostgreSQL Installation

To create your graph database with Apache AGE, you need PostgreSQL. Currently, the versions supported are 11, 12, and 13. You can obtain any of these versions from the Ubuntu package manager. You need to install both the postgresql library and the server:

sudo apt install postgresql-13 postgresql-server-dev-13

After installation, export the environment variable PATH to the executable and PostgreSQL configuration directories and PGDATA environment variable to the database data storage directory. When we install through the package manager, the files are installed by default in /usr/lib/postgresql/{version}, where {version} is the installed PostgreSQL version. In this case, it is version 13.

export PATH=/usr/lib/postgresql/13/bin:$PATH
export PGDATA=/usr/lib/postgresql/13/bin/data

Apache AGE

To install Apache AGE, download a compatible release with your version of PostgreSQL or clone the repository and switch to the branch compatible with your version of PostgreSQL. In this case, we will install version 1.3.0 for PostgreSQL 13:

git clone https://github.com/apache/age.git
git checkout release/PG13/1.3.0

To proceed with the installation, export the PG_CONFIG variable to pg_config directory or run the make command passing the variable as an argument:

sudo make PG_CONFIG=/usr/lib/postgresql/13/bin/pg_config install

You can also export the variable to the same directory and then proceed with the installation:

export PG_CONFIG=/usr/lib/postgresql/13/bin/pg_config
sudo make install

The pg_config is a utility that contains installation information about PostgreSQL, such as the version installed. When running pg_config for the current installation, the terminal returns the following information:

BINDIR = /usr/lib/postgresql/13/bin
DOCDIR = /usr/share/doc/postgresql-doc-13
HTMLDIR = /usr/share/doc/postgresql-doc-13
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/13/server
LIBDIR = /usr/lib/x86_64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/13/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/13/man
SHAREDIR = /usr/share/postgresql/13
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/13/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/13/man' '--docdir=/usr/share/doc/postgresql-doc-13' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/13' '--bindir=/usr/lib/postgresql/13/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu 13.10-1.pgdg22.04+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-14' 'CLANG=/usr/bin/clang-14' '--with-systemd' '--with-selinux' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-14/lib -Wl,--as-needed
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm 
VERSION = PostgreSQL 13.10 (Ubuntu 13.10-1.pgdg22.04+1)

To verify if the installation is correct, run the regression tests:

make PG_CONFIG=/src/lib/postgresql/13/bin/pg_config installcheck

Se todos passarem, o console vai retornar a seguinte frase: All 24 tests passed.

Testing the Installation

Let's test our installation by creating a graph in a database with the default user postgres. Start the PostgreSQL service and check the status:

sudo service postgresql start
service postgresql status

If everything is correct, a similar message will appear in the terminal indicating that the service is active:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Tue 2023-04-25 19:34:59 -03; 41min ago
   Main PID: 9761 (code=exited, status=0/SUCCESS)
        CPU: 1ms

abr 25 19:34:59 carla-linux systemd[1]: Starting PostgreSQL RDBMS...
abr 25 19:34:59 carla-linux systemd[1]: Finished PostgreSQL RDBMS.

Now start the command-line tool:

sudo -u postgres psql

Use the following command to install the extension on the server:

CREATE EXTENSION age;

Load the extension for each AGE connection:

LOAD 'age';

Use the following command to create a graph:

SELECT * FROM ag_catalog.create_graph('new_graph');

To simplify queries, add ag_catalog to your search_path with SET search_path = ag_catalog, "$user", public;. In this case, you could use the following command to create the graph:

SELECT * FROM create_graph('new_graph');

If everything is correct, the console will return:

NOTICE:  graph "new_graph" has been created
 create_graph 
--------------

(1 row)

Conclusion

In this tutorial, we have seen how to install PostgreSQL via package manager and use it with Apache AGE. If you want to learn more about this project, visit https://age.apache.org/. If you want to know how to contribute, visit https://github.com/apache/age. All kinds of contributions are very welcome ❤️.

Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

Easy guide to install and configure PostgreSQL with Apache AGE on Windows
Apache AGE 1.3 Release for PostgreSQL 13
Easy Installation of PostgreSQL, AGE & Age-viewer on Mac
Installing apache age-viewer

References

Apache AGE. Apache AGE Documentation - Setup. 2023. Available at https://age.apache.org/age-manual/master/intro/setup.html. Accessed on 04/25/2023.

How an RDBMS works #3: Database Life Cycle Overview

Carla Sanches — Sat, 01 Apr 2023 16:30:52 +0000

Introduction

Data available on the internet has been growing fast in recent years. Therefore, we have seen the importance of studying the design of an RDBMS. In this post, you will discover how its life cycle works, having an overview of the stages from modeling to data storage. By understanding this operation, we can more closely examine each part of its architecture.

Database Life Cycle

The database life cycle consists of basic steps from the conceptual model to implementation for designing a database and maximizing its performance. This definition applies to relational databases, such as PostgreSQL. Other types, such as distributed databases, are beyond the scope of this article.

The authors of the book "Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more" describe the database life cycle in four parts, as defined in Figure 1:

Figure 1 - Database life cycle. Elaborated by author.

1. Requirements Analysis

To ensure that your database meets the needs of your business, it is essential to define which data will be stored, how they relate to each other, their characteristics, and additional important information. This process usually takes the form of an interview with those who produce and use this data.

It's also at this stage that the choice of the right platform for implementation comes into play. So, suppose that we need to create a database to store articles for a research project. Each article has:

Title;
At least one author;
Publication date;
Publication location;
The number of citations.

Each author and place of publication also have their characteristics and relationships, but to simplify the example, let's focus only on the articles. Their relationships are through citations. The articles must strongly correlate through them to ensure well-founded research. Therefore, we can say that articles cite (refer to) other articles. An excellent platform to implement this database is PostgreSQL. In addition to storing data securely and efficiently, we can use the Apache AGE extension to visualize the citation graph and evaluate if the research is well-founded and if there are "loose" references without relationships, among other important information.

2. Logical Design

In the logical design phase, all requirements transform into a model, which can be either Conceptual or Logical. The Conceptual model represents the data and its relationships in an abstract form, while the Logical model provides a more detailed and implementation-oriented representation. It is important to note, however, that the purpose of this text is not to detail each model but to present its role in the database design process.

The logical design captures the reality of the user's world in terms of data elements and their relationships. In the example from the previous section, we would represent all the relationships between the articles in the form of a diagram. We will explore this example further in an upcoming post. The design aims to facilitate query programming and data updating. At this stage, the normalization of SQL tables already occurs. If you want to learn more about normalization, this article is a good example.

3. Physical design

The focus of the physical design is on operating data with high efficiency. This stage involves defining methods for storing and accessing tables on disk. The goal is to maximize the database's performance. Therefore, it is essential to have a clear understanding of how the database system works. We can measure database performance in two ways:

Through the time it takes for the database to respond to a query or complete an update for a specific application;
Through throughput, which is the number of transactions per second that the database system can handle in a specified period, considering all applications that use the database.

Physical resources that can cause delays when executing database applications include CPU, I/O (such as a disk), and Computer networks.

4. Implementation, Monitoring and Maintenance

This is the final stage of the database system life cycle, where we create and maintain the database. We can briefly describe it as follows:

Implementation: Creation of tables in the database using Data Definition Language (DDL) and updating, performing queries, and configuring indexes and constraints with Data Manipulation Language (DML). The SQL language encompasses both languages.

Example of DDL: CREATE TABLE command;
Example of DML: SELECT command.

Monitoring: Monitoring the performance of the database. PostgreSQL implements the EXPLAIN command (Section 3.2 of the book "The Internals of PostgreSQL”), which helps monitor the performance of a query by providing its cost.

Maintenance: Modifications to the database to meet performance requirements and changes in user requirements, for example. In this phase, there are design adjustments, which continue the life cycle of the DBMS.

Conclusion

In this article, we understood the importance of studying the resources and implementation of an RDBMS to ensure proper management of its life cycle. We also saw that this consists of a requirements analysis, logical design, physical design, implementation, monitoring, and maintenance. In the next post, I will apply some of these concepts by implementing the article database given as an example in the requirements analysis section, using PostgreSQL with Apache AGE for graph visualization. So, if you like to get your hands dirty, don't miss the next post!

Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

How an RDBMS works #1: Lessons from “The Internals of PostgreSQL”

How an RDBMS works #2: Reasons to study its design

Cost Estimation In PostgreSQL

Collaborate to Innovate: How a Social Network Can Help You Find Your Dream Pair Programming Team

References

Lightstone, Sam S., Toby J. Teorey, and Tom Nadeau. Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more. Morgan Kaufmann, 2010.

Microsoft. Description of the database normalization basics. 2023. Available at https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description. Acessed on 03/31/2023.

SUZUKI, Hironobu. The Internals of PostgreSQL for database administrators and system developers. Interdb. 2015. Available at https://www.interdb.jp/pg/. Acessed on 03/25/2023.

Souza, Wander Inácio de. Database I Class Notes - Concepts and Architecture of Database Systems. Federal University of Ouro Preto. 2016. Unpublished.

Banner designed by Freepik.

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

How an RDBMS works #2: Reasons to study its design

Carla Sanches — Sat, 18 Mar 2023 23:17:29 +0000

This is the second article of the series about the operation of a Relational Database Management System (RDBMS) based on PostgreSQL. After reading the book "Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more" by Lightstone, Teorey, and Nadeau (2010), I found it relevant to write about why understanding database design is very important.

In a brief summary of what the authors bring as motivation, the volume of data in the world tends to grow exponentially. As the volume of data increases, so does the demand for more robust data structures to securely store data and return queries faster and faster.

According to Andre (2023), the amount of data created daily on the internet is 1.134 trillion MB. Lately, this increase has been mainly due to the popularization of social media and streaming services. If in 2010 we were talking about petabytes, Figure 1 presents the estimate of data consumption in zettabytes since 2021, which is expected to double by 2024.

Figure 1 - Estimated data consumption from 2021 to 2024. Adapted from Andre (2023).

In addition, new technologies are continuously emerging to help make sense of this data. For example, Bitnine Global Inc. has Apache AGE to provide graph data analysis for relational databases. G-FDS is a graph-based solution for fraud detection, and G-PAS focuses on predictive analysis for Big Data. There are other products and more new developments on the horizon! Therefore, understanding the foundation and needs of database structures is essential to deliver quality solutions. In the next post, I will discuss the life cycle of an RDBMS and explain the architecture of PostgreSQL in practice.

Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

References

Lightstone, Sam S., Toby J. Teorey, and Tom Nadeau. Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more. Morgan Kaufmann, 2010.

Andre, Louie. 53 Important Statistics About How Much Data Is Created Every Day. Finances Online. 2023. Available at https://financesonline.com/how-much-data-is-created-every-day/. Acessed on 03/15/2023.

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

How an RDBMS works #1: Lessons from “The Internals of PostgreSQL”

Carla Sanches — Thu, 09 Mar 2023 04:01:16 +0000

Introduction

Hi! My name is Carla, and I am a software engineering intern at Bitnine Global, where I work with the Apache AGE team, developing the plugin for graph search in PostgreSQL. With this post, I am starting a series to talk about the architecture of database management systems using PostgreSQL as an example.

To do this, I will use the book "The Internals of PostgreSQL" by Hironobu Suzuki as a basis. My goal is to simplify the language of the book so that the content is friendly to students who wish to understand how a Relational Database Management System (RDBMS) works and to present use cases so that the content is useful for database administrators too.

In the future, I will talk more about Apache AGE. But if you're already curious, you can explore the #apacheage hashtag here on Dev.to to learn more. There are several community posts ranging from installation to use cases in real applications.

PostgreSQL is an open-source project for a multipurpose relational database. In other words, you can store and manage various types of data for multiple applications. The system is complex and has several integrated subsystems, which will also be presented in this series of posts. Its main features are robustness, reliability, and scalability. These, among other characteristics, make it one of the most popular databases in the world.

Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

References

SUZUKI, Hironobu. The Internals of PostgreSQL for database administrators and system developers. Interdb. 2015. Available at https://www.interdb.jp/pg/. Acessed on 03/08/2023.

The PostgreSQL Global Development Group. PostgreSQL: The World's Most Advanced Open Source Relational Database. Available at https://www.postgresql.org/. Acessed on 03/08/2023.

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/

Apache AGE Github: https://github.com/apache/age

Easy guide to install and configure PostgreSQL with Apache AGE on Windows

Carla Sanches — Fri, 24 Feb 2023 16:45:01 +0000

What is PostgreSQL

PostgreSQL is an open-source relational database system, created in 1986. The system uses SQL language and has several features for data storage. Its main characteristics are robustness, reliability, and scalability. Another important feature is its support for ACID properties (Atomicity, Consistency, Isolation, and Durability). This makes it one of the most popular databases in the world!

What is Apache AGE

Apache AGE is an extension for PostgreSQL that provides data querying in graphs. This is important because it enables the use of optimization and graph search algorithms for data analysis. The extension uses OpenCypher technology, which allows the use of Cypher language along with SQL to query the relational database using graphs.

How to configure PostgreSQL with Apache AGE on Windows

Unfortunately, Apache AGE does not have a Windows installation yet. However, it is possible to use it by using Windows Subsystem for Linux (WSL). If you have not set it up on your machine yet, follow Step 1 to set it up. If you have already done it, you can skip to Step 2.

1. Setting up WSL

To enable WSL, go to Control Panel > Programs and Features > Turn Windows features on or off and check the option "Windows Subsystem for Linux". Then, restart your computer to apply the changes.

If you are using Windows 10 version 2004 and higher (Build 19041 and higher) or Windows 11, installing WSL will only require a single command. Open the command prompt or PowerShell as an administrator and enter the following command:

wsl --install

This will install Ubuntu by default. This will be the Linux distribution used throughout the rest of the tutorial. If you want to install another distribution or have a different version of Windows, consult the complete documentation here.

You can use the Linux terminal through the apps available in the Windows Store. Just search for the desired distribution and click Install. In this tutorial, I'm using Ubuntu 20.04.5 LTS. When you open the app, it may take a few minutes to complete the installation. Then, a username and password will be requested. Keep the password in a safe place. It will be requested every time a command is run as an administrator with sudo.

With WSL installed, it becomes easy to configure PostgreSQL and Apache AGE. Basically, just open the Ubuntu terminal and follow the same steps as the Linux configuration. The next steps teach how to do this configuration.

2. Setting up PostgreSQL

Currently, Apache AGE is only compatible with versions 11 and 12 of PostgreSQL. If you already have one of these versions installed on your machine, you can skip to Step 3, where I teach how to configure Apache AGE. If you don't have it, an easy way to install PostgreSQL 12 in a customized way is through the source code. To follow this tutorial, you need to have git and some PostgreSQL dependencies installed. You can install them through the following command:

sudo apt install git libreadline-dev zlib1g-dev bison flex build-essential

Create a directory in your workspace and clone the repository.

mkdir age_project
cd age_project
git clone https://git.postgresql.org/git/postgresql.git

Access the postgresql directory and switch to version 12.
```
cd postgresql
git checkout REL_12_STABLE
```
Then, compile the code by passing the directory where the binaries will be installed.
```
./configure --prefix=/usr/local/pgsql-12
```
Compile the PostgreSQL code. The first time, all files will be compiled. This may take a little while.
```
make
```
After compilation, create the installation directory, add user permission to install by replacing [user] with your username and install PostgreSQL.
```
sudo mkdir /usr/local/pgsql-12
sudo chown [user] /usr/local/pgsql-12
make install 
```

Add the environment variables.

export PATH=/usr/local/pgsql-12/bin/:$PATH
export PGDATA=/usr/local/pgsql-12/bin/data

Great! PostgreSQL is now installed. To test it, initialize the cluster with the following command:
```
initdb
```
Start the server.
```
pg_ctl start -l log
```
Finally, start PostgreSQL.
```
psql postgres
```
To exit and stop the server, type \q and run the following command:
```
pg_ctl stop -l log
```

For more details about the installation, you can check the official PostgreSQL documentation.

3. Configuring Apache AGE

You can also configure AGE through the source code using the following steps:

Clone the AGE repository inside the age-project directory created in Step 2:
```
git clone https://github.com/apache/age.git
```
Switch to the latest release. Currently, the latest stable release is 1.1.0.
```
cd age
git checkout release/PG12/1.1.0
```

Set the PG_CONFIG environment variable.

export PG_CONFIG=/usr/local/pgsql-12/bin/pg_config

Finally, install the extension.
```
make install
```

All set! To test the installation, create a database instance using steps 7-9 in Step 2 and use the following command to install AGE on the server:

CREATE EXTENSION age;

For each AGE connection, you must load the extension.

LOAD 'age';

If everything is correct, the system will return something similar to the following messages:

psql (12.14)
Type "help" for help.

postgres=# CREATE EXTENSION age;
CREATE EXTENSION
postgres=# LOAD 'age';
LOAD
postgres=#

For more details, you can consult the official Apache AGE documentation.

Here are some articles from AGEDB community that may be useful for your first projects with Apache AGE:

What is APACHE AGE?

Exploring the Functions of Apache AGE: A Comprehensive Guide (To Be Continued)

Apache AGE Tutorial - Creating Graphs, Nodes and Edges

References

PostgreSQL
Apache AGE
Setup — Apache AGE master documentation

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age