SteemSQL - Database update for custom_json operations

Custom_json type operations are gaining momentum into the Steem blockchain. As they are cheap and versatile, they are more and more used by Steem (d)Apps.

Something interesting is that, while the blockchain enables (d)Apps to sign custom_json operations with multiple authorities, this feature hasn’t been used… until recently.

Indeed, I was recently in contact with @starkerz (developer for the #3speak project he runs with @theycallmedan) when I got error notifications from SteemSQL’s database injector because of custom_json operations with multiple signing authorities generated by their platform.

If you want to read more on why they are generating such operations, check this post from @wehmoen published a few days ago.

On the SteemSQL side, the main problem was that the TxCustoms was designed to store only one authority and not an array of authorities. This enables the creation of an index to speed up queries.

Changes implemented in the TxCustoms table

The columns required_posting_auths and required_auths type has been changed from varchar(16) to varchar(MAX) and now contain a JSON array of strings.

As mentioned in a previous SteemSQL update post, the database design will no more be a pure 1 to 1 match between the blockchain format and the SQL table design.

Therefore, I have added 2 new columns in the TxCustoms table:

• required_posting_auth: this column will contain the first authority found in the required_posting_auths column if any
• required_auth: this column will contain the first authority found in the required_auths column if any

If you are an analyst or have developed some applications relying on the previous type of data, the easiest way to adapt your existing process(es) is to remove the "s" at the end of the name of the column(s).

I plan to do more and more database revamping in the future to improve performances, resources consumptions and ease of use from the SteemSQL user’s perspective.

Thanks for reading!

Support me and my work for the Steem community.

Vote for my witness