SteemSQL Tutorial – Can we Really Recover Deleted Comments/Posts on STEEMIT?

MSSQL Database

@nationalpark posted a simple SQL to list the deleted comments (which can be posts as well), however, the SQL output information is quite limited. The STEEMSQL table has the following structure:

steemsql-txdeletecomments-table

In my case, it lists the following 5 comments I deleted (comments permlink start with re-)

steemsql-txdeletecomments-linqpad

As you probably noticed, the tx_id acts as a foreign key to Transactions table:

transactions-table-steemsql

And, this table stores the basic activities in the steem blockchain e.g. like account update, funds transfer, votes etc all of these are represented as ‘transactions’.

The table Transactions can further be linked to Blocks via block_num key.

steemsql-blocks-table

So, if we link all these three tables, what data can we get about the deleted-stuffs that people don’t want others to see?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

select 
    TxDeleteComments.tx_id, 
    TxDeleteComments.permlink,
    TxDeleteComments.timestamp,
    Transactions.block_num,
    Transactions.transaction_num,
    Transactions.ref_block_num,
    Transactions.ref_block_prefix,
    Transactions.expiration,
    Transactions.type,
    Blocks.previous,
    Blocks.witness,
    Blocks.witness_signature,
    Blocks.transaction_merkle_root
    
from 
    TxDeleteComments, 
    Transactions, 
    Blocks 
    
where 
    TxDeleteComments.author = 'justyy' and
    TxDeleteComments.tx_id = Transactions.tx_id and
    Transactions.block_num = Blocks.block_num

select 
    TxDeleteComments.tx_id, 
    TxDeleteComments.permlink,
    TxDeleteComments.timestamp,
    Transactions.block_num,
    Transactions.transaction_num,
    Transactions.ref_block_num,
    Transactions.ref_block_prefix,
    Transactions.expiration,
    Transactions.type,
    Blocks.previous,
    Blocks.witness,
    Blocks.witness_signature,
    Blocks.transaction_merkle_root
    
from 
    TxDeleteComments, 
    Transactions, 
    Blocks 
    
where 
    TxDeleteComments.author = 'justyy' and
    TxDeleteComments.tx_id = Transactions.tx_id and
    Transactions.block_num = Blocks.block_num

The results show:

show-delete-posts-query

Can these hash values (transaction IDs, block numbers) be used to recover the deleted comments/posts in the STEEM blockchain e.g. from the witness servers? I don’t know.. as currently I don’t see such possibility simply using STEEMSQL.

Do you have other better ways? please share yours by commenting below. Innovative (better) solutions will be rewarded with 1 SBD.

You may also like: STEEMSQL 系列之 STEEMIT真的可以恢复删除的文章或评论么？

STEEM SQL Tutorial

• SteemSQL Tutorial: How to Get Random Posts on SteemIt?
• SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?
• SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?
• SteemSQL Tutorial: How to Calculate Monthly Income on STEEMIT?
• SteemSQL Tutorial: Can we Really Recover Deleted Comments/Posts on STEEMIT?
• SteemSQL Tutorial: What are the Outgoing Votes for Big Whales?
• SteemSQL Tutorial: Count Total Interests Sent
• SteemSQL Tutorial: Finding Inactive Steemians that You Follow
• SteemSQL Tutorial: How to Fix “JSON text is not properly formated. Unexpected character ‘.’ is found at position 0.”?
• SteemSQL Tutorial: How to Get the Most Payout Authors in History?
• SteemSQL Tutorial: Get Most Single Payout Authors
• SteemSQL Tutorial: How to Avoid SQL Injection?
• SteemSQL Tutorial: I have spent 800 SBD (7000+ USD) buying votes!
• SteemSQL Tutorial: How to Check If SteemSQL is Synchronized with Steem Blockchain?

–EOF (The Ultimate Computing & Technology Blog) —