SteemSQL - Community directories available

Two days ago, at the same time as the official release of the Steem communities, I made an important update of SteemSQL. You can read the announcement here in the SteemSQL community

For the record, this update was not without generating a serious issue.
What happened is that people started to broadcast operations to the blockchain to describe their community using foreign languages, something I hadn't encountered during my tests
Unfortunately, some tables like TxCustoms, which stores custom_json operations, were not designed to store Unicode data. Therefore the database was not able to store community descriptions written in foreign languages.

I had to make a database update for the ~290 million rows in the table. This storage-intensive process took a few hours and burned more than 250GB of additional disk space. Meaning I will have to upgrade m infrastructure sooner than expected 😓

And now, the new features…, not one but 3!

3 new community directories

For all blockchain operations, communities are referenced by their owner account name, i.e. something like hive-NNNNNN where NNNNNN represents the community identifier. You will admit that it is not really user-friendly.

Moreover, if you want to get communities related information, you have to browse all operations in chronological order to know what happened within each community. Add to this that the community management operations are non-consensual, and therefore not validated by the blockchain. This will put you in front of a lot of ill-formatted operations you have to deal with and filter.

Luckily SteemSQL is now doing all that dirty work for you!

SteemSQL has now a new table Communities which stores a real-time updated list of all communities and their properties.

The second directory table is CommunitiesSubscribers, containing a realtime updated list of all subscribers for each community.

The third and last one directory is CommunitiesRoles, containing a realtime updated list of all accounts having a specific role or status within a community, whether administrators, moderators or even users who have been muted.

Attention: there is a trick with the last table given how communities work at the blockchain level: if a specific user is missing from the table, it is considered being a ”guest” because an account is only inserted in the table if it is explicitly assigned a role by an admin or moderator!

That’s all for today. If you appreciate these new features, do not forget to upvote this post.
You may also subscribe want to the SteemSQL community to stay updated.