What is Aegis?
Basically, Aegis is the ICON blockchain stored into a MySQL database. It lets you query quickly and efficiently any information from the ICON blockchain for analysis purposes. Or just because being curious is fun.
How Can I Use It?
Aegis is designed for developers with prior SQL experience. If you don’t know SQL queries at all, you won’t be able to use Aegis. However, as Aegis SQL schema is quite simple, it isn’t hard to craft simple queries that retrieve interesting results.
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:
Want to see more? Let’s do some deeper investigation of the ICON blockchain.
Extracting Data From The ICON Blockchain
The ICON blockchain supports attaching up to 125KB of raw data in any transaction. So if you want to share a small file with the rest of the world as long as the ICON blockchain exists, you can.
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
ICON has started to use their own blockchain in order to issue timestamped certificates images about a month ago, such as this one.
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
With Aegis, you can query any data written in the blockchain. However, let’s say you want to determine the balance of an account at a precise time. This information is not directly written in the blockchain, but determined from the transaction history of the account; sum all the deposits and the withdrawals of the account, and you will get its balance.
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
Want to try Aegis? Here are some simple steps for using 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.
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
