在服务器上创建数据库(这里称为archive
)并将模式加载到其中。这只需要在第一次设置存档节点时执行。
createdb -h localhost -p 5432 -e archive psql -h localhost -p 5432 -d archive -f <(curl -Ls https://raw.githubusercontent.com/MinaProtocol/mina/master/src/app/archive/create_schema.sql)
- 在端口3086上启动归档进程,并连接步骤1中在端口5432上启动的postgres数据库。
coda-archive run -postgres-uri postgres://localhost:5432/archive -server-port 3086
- 启动守护进程,将其连接到我们刚刚在端口3086上启动的归档进程。如果您要连接到另一台机器上的存档进程,您还可以指定一个主机名,比如
localhost:3086
。
mina daemon ..... -archive-address 3086
使用归档节点
现在我们已经运行了归档节点,让我们看一下数据库中的表。
要列出数据库中的表,您可以在psql中运行dt
命令。在这里查看表的完整模式。
下面是每个表中一些值得注意的字段。
表1:user_commands
该表跟踪网络上的交易。
... user_command_type Type of transaction being made Possible values: `'payment', 'delegation' To see a specific type of transaction, i.e. payments or creating a token, specify this field in your queries. source_id public key of the sender receiver_id public key of the receiver amount amount being sent from the sender to the receiver token ID of a token If you are querying for different type of token transactions, specify this field.
表2:internal_commands
这个表记录了从snark工作或区块生产中获得的奖励。
... internal_command_type represents whether the command is a `fee_transfer` from snark work or a `coinbase` reward from block producing. Use this field for information about block rewards and snark rewards (there is also an extra fee_transfer added to support sending all the transaction fees summed together to the block_creator) receiver_id public key ID of the receiver fee amount being sent from the protocol to the receiver token ID of a token If you are querying for different type of token transactions, specify this field.
表3:blocks
... id parent_id ID of the previous block in the blockchain Use this field for information about block rewards and snark rewards (there is also an extra fee_transfer added to support sending all the transaction fees summed together to the block_creator) creator_id public key of the block creator
连接表
归档数据库中有两个连接表,它们将区块和交易相连接。通过链接区块表和命令表,这些表使得您可以识别区块中的特定交易。
JOIN TABLE 1: BLOCKS_USER_COMMANDS
... block_id ID of the block containing the user command user_command_id ID of the user command sequence_no 0-based order of the user command among the block transactions
JOIN TABLE 2: BLOCKS_INTERNAL_COMMANDS
... block_id ID of the block containing the internal command internal_command_id ID of the internal command sequence_no 0-based order of the internal command among the block transactions secondary_sequence_no 0-based order of a fee transfer within a coinbase internal command
尝试查询
现在我们已经了解了数据的结构,让我们尝试做一个查询。
例1:查找公钥创建的所有区块
SELECT * FROM blocks AS b INNER JOIN public_keys AS pk1 ON b.creator_id = pk1.id WHERE value = 'MY_PK'
例2:查找您通过公钥收到的所有付款
SELECT * FROM user_commands AS uc JOIN blocks_user_commands AS buc ON uc.id = buc.user_command_id JOIN public_keys AS pk ON uc.receiver_id = pk.id WHERE value = 'MY_PK' AND type = 'payment'
例3:查找合法链上高度为12的区块
WITH RECURSIVE chain AS ( (SELECT ... FROM blocks b WHERE height = (select MAX(height) from blocks) ORDER BY timestamp ASC LIMIT 1) UNION ALL SELECT ... FROM blocks b INNER JOIN chain ON b.id = chain.parent_id AND chain.id <> chain.parent_id ) SELECT ..., pk.value as creator FROM chain c INNER JOIN public_keys pk ON pk.id = c.creator_id WHERE c.height = 12
例4:列出每个公钥创建的区块的计数,并按降序排序
SELECT p.value, COUNT(*) FROM blocks INNER JOIN public_keys AS p ON creator_id = ip.id GROUP BY p.value ORDER BY count DESC;
例5:列出每个公钥创建的应用支付计数,并按降序排序
SELECT p.value, COUNT(*) FROM user_commands INNER JOIN public_keys AS p ON source_id = p.id WHERE status = 'applied' AND type = 'payment' GROUP BY p.value ORDER BY count DESC;