Exploring the ICON Blockchain: Aegis

Aegis SQL Schema

What is Aegis?

How Can I Use It?

For instance, here’s the SQL query that sorts ICON addresses ordered by transactions count sent to these addresses. Consequently, the top address returned by this query is the most used address of the ICON network:

And here’s a portion of the result returned by Aegis:

Looks like hx11de4e28be4845de3ea392fd8d758655bf766ca7 is the most used address of the ICON Network.

Want to see more? Let’s do some deeper investigation of the ICON blockchain.

Extracting Data From The ICON Blockchain

Aegis schema provides a SQL table containing all transaction messages, simply called transaction_message. Let’s say we want to query the Aegis Database in order to retrieve the transaction hash associated with every transaction message contained in the database.

Here’s what the request would look like:

And here’s what this request returns:

These results are ordered by transaction order, so what you’re looking at are the first transactions ever containing a transaction data attached to the transaction. There’s some funny stuff in there.

Let’s Go Deeper: Extracting Images

As these certificates are embedded as base64 images in the transaction data field, we can detect these images by looking for the pattern data:image/ at the start of the transaction data field. We can reuse and improve our previous query for that:

Now let’s say we want to write all these images to an HTML file, using a Python script:

Executing the previous script with a synchronized database will output the following images, all uploaded to the ICON blockchain:

Complex Queries: What Aegis Can And Can’t Do

Consequently, you’ll often need to do some post-processing with Aegis data if you’re looking for ICX transfers based on time.

You can still use the block timestamp and group them to a UNIX timestamp if you want a daily statistic, such as the number of transactions per day :

This query outputs the following result by plotting it in a plot library such as DyGraphs :

Installing Aegis

  • Download and install MySQL Server + MySQL Workbench: https://dev.mysql.com/downloads/installer/
  • Create a new SQL user called icon, with the password icon.
  • Download Aegis: https://github.com/iconation/aegis/releases/latest (if you’re not on Windows, sorry, you’ll need to compile it)
  • Please download both the executable and the SQL Schema (Aegis.SQL.Schema.zip) in the previous link!
  • Extract Aegis.SQL.Schema.zip anywhere on your disk.
  • Launch MySQL WorkBench, connect to your MySQL Server and create a new schema called iconation using the Navigator window on the left.
  • Imports the Aegis Schema using the contextual menu: Server > Data Import > Import from Dump Project Folder > Select the folder where you extracted the SQL Schema > Load Folder Contents > Start Import.
  • You’re now ready to use Aegis! You can now launch it anytime, and it will start filling your Aegis database. Depending on the Citizen node you’re using and your computer performance, it can take a few hours to download the entire blockchain and fill your database.
Aegis is downloading and inserting the ICON blocks at the same time.

Please refer to the Aegis schema if you’re looking for information about what data the Aegis Database contains, the fields should be very straight-forward to understand.

If you have questions or are interested in more, please visit the ICONation website or join our Telegram.

By ICONists, for ICONists

ICONation

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store