STEEMSQL - Update 13 - Virtual Operations available

in steemsql •  last year

SteemSQL is a public MS-SQL database with all the blockchain data.

Few days ago, I announced a major change in the design of the SteemSQL database. I also announced that, thanks to this new design, I would be able to implement long awaited features. It has been done and I’m glad to present you the next update of SteemSQL with Virtual Operation tables availability.

Previous posts

Virtual Operations

Since hardfork 16, a new set of virtual operations are accessible to external libraries. They were already stored in the blockchain, but you had to to use native code to access them. steemd exposes them through the get_ops_in_block API.

What are Virtual Operations?

Virtual operations are operations resulting from the activity of the blockchain but, unlike transactions, they are not initiated by users.

An example:

  1. You publish a high quality post with tons of useful information in it.
    This is a transaction. It will be stored in the blockchain (and in the table TxComments in SteemSQL)

  2. Because your post is excellent, a lot of people vote for it.
    These are transactions. They will be stored in the blockchain (and in the table TxVotes in SteemSQL)

  3. After 7 days, you receive a very decent payout for your post. Bingo!
    Congratulations, you made the right decision by choosing quality over mediocrity.
    You suddenly see you wallet full of SBD and your SP has raised.
    This is a Virtual Operation. You did nothing to trigger it, the system did it for you.

New tables in SteemSQL

No less than 12 tables have been added to to the database. These new tables a linked to Blocks, not to Transactions, because they are independent from normal transactions.

Here an updated diagram of the database:


This virtual operation is triggered after the payout period on posts and comments. It is the most awaited operation by all users.


This virtual operation is triggered after the payout period on posts and comments, when the original author has decided to share his reward with another user..


This virtual operation is triggered after payout period on posts and comments. This is for curators, who vote on posts or comments.


This virtual operation occurs when you convert your SBD to STEEM. Remember there is a delay between the time you initiate this conversion and when the conversion is performed. Hence the Virtual Operation nature of this activity.


This virtual operation occurs when you are trading on the internal Steem Exchange, i.e. when you sell or buy SBD or STEEM with other Steem users.


This virtual operation occurs after you remove values that have been put into your saving. Remember than when you initiate a transfer from your saving, it takes 3 days to complete.


This virtual operation occurs after you started a PowerDown. Each week, the system will convert a portion of your VESTS into STEEM and here is where this conversion is recorded.


You wallet is full of SBD, and you wisely decided to keep them. Good idea, because each month, you will receive some interest depending on the amount of SBD you left in your wallet.
This is a virtual operation initiated automatically by the system.


Liquidity reward was a reward mechanism for the on-chain market designed to pay users who provide liquidity to the market. This virtual operation is no more used.


This virtual operation occurs each time a block is produced. It contains the rewards that are given to witnesses (and previously miners) for their work.


This virtual operation occurs when the Steem Power that has been previously delegated by an account to another account comes back the the original owner. Remember that when a user cancel its Steem Power delegation, it take 7 days to complete.


Yes, sometimes happens that a witness has to shutdown its “witness node”, i.e. tell the platform that its node will no longer provide witness services to the network.

This can occurs if the witness is too lazy to make its witness node work perfectly or keep it up to date with hardforks. In this case, the system will whip him and deactivate its witness node.

I didn’t say this table contains a list of bad boys. Some trustful witnesses may have good reasons to temporary shutdown their node, because of maintenance or problem with their network provider for example.

All virtual operations ... all of them!

Even though Virtual Operations have only been available in SteemSQL for a few days, I told the Database Injector to reparse the whole blockchain (and it needed more than a day to complete.).

Just for history, I had to manage with a very special Virtual Operation : the first payout that occurred on 2016-07-04 00:00:06.000. It was a huge “Transaction” involving 60084 operations.

But the result is that EVERY virtual operation since Steem launched is now in the database.
I can already hear fingers hitting the keyboard of some statisticians …

Thanks for reading.

Help SteemSQL stay public and running 24/7/365.

All payout from this post will be allocated to SteemSQL infrastructure.

Thanks for your support!

footer created with steemitboard - click any award to see my board of honor

Support me and my work as a witness by voting for me here!

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Thanks @arcange. SteemSQL is bad ass and was HIGHLY necessary before Jussi came around. Even with Jussi certain applications benefit greatly from the SQL DB available.

This post is undervalued. Adding the ability to query virtual ops is extremely useful. Thanks again.


Glad you like this update. Thanks for your feedback.

Can you please reccomend a good guide to create a node?

awesome awesome awesome. what else can I say. Thank you!


what else can I say

You can say it one more time xD


Thank you, thank you, thank you!

You are a wonderful man, online and in person!


Me blush 😊

Thank you so much for all that you do with maintaining this! It is an amazing resource. I barely scratch the surface, but I always love the data that comes out of posts from people much smarter than I who can pull the data out and analyze it for us!

As one of the UN-initiated, I don't know how you work your magic, but thank you for the effort you put into making STEEM approach perfection.

This is a fantastic update! Great work, as always.


Thanks bro =)


All of the nerds are drooling :)

Thank you @arcange!

great information. no more guessing what the other tables are for 😎
first time i heard of virtual operations.
thanks for the details

Great work, arcange!

Maybe with this extended weekend I can finally make the time to access this SQL server and start playing around in Power BI.

I just found this today but when I try to log into the database it says the account is disabled... is it broken or have you stopped the project?


There's a charge for it now. 10SBD per month.