STEEMSQL 系列之 STEEMIT真的可以恢复删除的文章或评论么？ STEEMSQL Tutorial - Can we R...

Thank you @arcange for creating STEEMSQL!

STEEM SQL Tutorial Series：

@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:

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

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

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.

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?

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:

Can these hashes (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.

Image Credit: Pixabay.com

感谢 @arcange 创造了 STEEMSQL!

STEEM SQL 系列：

@nationalpark 兄在这篇帖子里列出了被删除评论或者文章所存的STEEMSQL 语句。老实说，我很久之前也注意过这个表，但是当时还在纳闷说怎么没有列出我比较关心的，被删除内容的原文。这个 TxDeleteComments 意思就是被删除的评论表，结构如下：

我把我的ID代了进去，发现我曾经删除过5个评论，评论的 permlink 以re- 开始。

我们还注意到，tx_id 就是 Transactions 表的外键。

这个Transactions 表应该存着STEEM 区块链上所有发生的动作，包括转帐、帐户更新、投票等。同时Transactions 这个表可以通过 block_num 这个外键联结到 Blocks块表。

然后我们很容易的把这三个表串起来

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