之前在《数字藏品发行平台的架构》里讲过，有一种架构希望以区块链的数据为核心。这样就需要将合约保存在区块链上的数据同步到数据库里，方便后续中间件接口的开发。
本次我们以同步ConfirmSale事件日志为例，介绍数据同步程序开发的相关知识点。
一、先上全部代码
```javascript
const { ethers } = require("hardhat");
var mysql = require("mysql");
let dotenv = require('dotenv')
dotenv.config({ path: "./.env" })
var connection = mysql.createConnection({
host: process.env.MYSQL_HOST,
port: process.env.MYSQL_PORT,
user: process.env.MYSQL_USER,
password: process.env.MYSQL_PASSWORD,
database: process.env.MYSQL_DATABASE,
charset: process.env.MYSQL_CHARSET
});
function DbQuery(sqlstr, values) {
return new Promise((resolve, reject) => {
connection.query(sqlstr, [values], (error, results) => {
if (error) {
reject(error);
} else {
resolve(results);
}
});
});
}
async function main() {
let addr = "salePlainAddress"; // 此处使用交易合约地址
let blockNum = await ethers.provider.getBlockNumber();
let topic = ethers.utils.id("ConfirmSale(address,uint256,address,address,address,uint256)");
let iface = new ethers.utils.Interface([
"event ConfirmSale(address indexed _contract, uint _tokenId, address indexed solder, address indexed buyer, address token, uint _price)"
]);
let values = [];
let filter = {
address: addr,
fromBlock: 11040000,
toBlock: blockNum,
topics: [topic]
}
let logs = await ethers.provider.getLogs(filter);
if (logs.length > 0) {
for (i in logs) {
let blockinfo = await ethers.provider.getBlock(logs[i].blockNumber);
let date = new Date(blockinfo.timestamp * 1000);
let dt = date.getFullYear() + "-" + (date.getMonth() < 9 ? '0' + (date.getMonth()+1) : (date.getMonth()+1)) + "-" + (date.getDate() < 10 ? '0' + date.getDate() : date.getDate());
let h = date.getHours();
let saleInfo = iface.decodeEventLog("ConfirmSale", logs[i].data, logs[i].topics);
values.push([saleInfo._contract, saleInfo._tokenId, saleInfo.solder, saleInfo.buyer, saleInfo.token, saleInfo._price, logs[i].blockNumber, logs[i].transactionHash, blockinfo.timestamp, dt, h]);
}
let sqlstr = "insert into meta_order (`contract`,`tokenID`,`seller`,`buyer`,`token`,`price`,`blocknum`,`txhash`,`timestamp`,`dt`,`h`) values ? on duplicate key update contract=values(contract), tokenID=values(tokenID), seller=values(seller), buyer=values(buyer), token=values(token), price=values(price), blocknum=values(blocknum), timestamp=values(timestamp), dt=values(dt), h=values(h)";
let s = await DbQuery(sqlstr, values);
console.log(s);
} else {
console.log("have no eventlogs");
}
}
main()
.then(() => {
connection.end();
process.exit(0);
})
.catch(error => {
console.error(error);
process.exit(1);
})
```二、相关知识点介绍
- event logs
在Solidity代码中，使用event关键字来定义一个事件，如：event EventName(address bidder, uint amount);
这个用法和定义函数式一样的，并且事件在合约中同样可以被继承。触发一个事件使用emit(说明，之前的版本里并不需要使用emit)，如：emit EventName(msg.sender, msg.value);
触发事件可以在任何函数中调用，如：
```javascript
function testEvent() public {
emit EventName(msg.sender, msg.value);
}
```在我们的实例程序中，数字藏品交易合约定义了5种event log，分别是：商品上架、更新商品信息、取消上架、订单确认和拍卖出价。
```javascript
interface ISales{
//events
event CreateSale(address indexed _contract, uint _tokenId, address seller, address token, uint _price, uint _due);
event UpdateSale(address indexed _contract, uint _tokenId, address seller, address token, uint _price, uint _due);
event CancelSale(address indexed _contract, uint _tokenId);
event ConfirmSale(address indexed _contract, uint _tokenId, address indexed solder, address indexed buyer, address token, uint _price);
event NewBid(address indexed _contract, uint _tokenId, address indexed bidder, address token, uint _price);
//other code
}
```我们需要将这5种事件从区块链同步下来，存入activity表中，作为基础数据，方便我们进行数据处理。
同时我们将event ConfirmSale单独拿出来，因为该事件数据作可以存入order为订单表，表示交易完成。
- 使用环境变量
dotenv是一个零依赖的模块，它将环境变量从.env文件加载到process.env中。这使得我们可以隐藏数据库密码细节到.env文件，同时通过git进行代码版本管理。
```javascript
let dotenv = require('dotenv')
dotenv.config({ path: "./.env" })
```- 使用mysql数据库
```javascript
var mysql = require("mysql");
var connection = mysql.createConnection({
host: process.env.MYSQL_HOST,
port: process.env.MYSQL_PORT,
user: process.env.MYSQL_USER,
password: process.env.MYSQL_PASSWORD,
database: process.env.MYSQL_DATABASE,
charset: process.env.MYSQL_CHARSET
});
function DbQuery(sqlstr, values) {
return new Promise((resolve, reject) => {
connection.query(sqlstr, [values], (error, results) => {
if (error) {
reject(error);
} else {
resolve(results);
}
});
});
}
```此处我们将query包装在DbQuery方法的Promise中，目的是为了在程序中同步运行并等待。防止程序停止后，数据来不及存入数据库丢失。
- 代码整体架构
```javascript
async function main() {
// some code here
}
main()
.then(() => {
connection.end();
process.exit(0);
})
.catch(error => {
console.error(error);
process.exit(1);
})
```使用异步方式方便使用wait语法
- 使用hardhat同步数据
```javascript
// 导入hardhat库
const { ethers } = require("hardhat");
async function main() {
let addr = "salePlainAddress"; // salePlainAddress
let blockNum = await ethers.provider.getBlockNumber(); // 获取最新区块号
let topic = ethers.utils.id("ConfirmSale(address,uint256,address,address,address,uint256)"); // 此处与合约event定义相对应，但只使用数据类型，不需要变量名
// iface会用户日志分析
let iface = new ethers.utils.Interface([
"event ConfirmSale(address indexed _contract, uint _tokenId, address indexed solder, address indexed buyer, address token, uint _price)"
]);
let values = [];
let filter = {
address: addr,
fromBlock: 11040000,
toBlock: blockNum,
topics: [topic]
}
let logs = await ethers.provider.getLogs(filter); //从链上获取数据
if (logs.length > 0) {
for (i in logs) {
let blockinfo = await ethers.provider.getBlock(logs[i].blockNumber);
let date = new Date(blockinfo.timestamp * 1000);
let dt = date.getFullYear() + "-" + (date.getMonth() < 9 ? '0' + (date.getMonth()+1) : (date.getMonth()+1)) + "-" + (date.getDate() < 10 ? '0' + date.getDate() : date.getDate());
let h = date.getHours();
let saleInfo = iface.decodeEventLog("ConfirmSale", logs[i].data, logs[i].topics);
values.push([saleInfo._contract, saleInfo._tokenId, saleInfo.solder, saleInfo.buyer, saleInfo.token, saleInfo._price, logs[i].blockNumber, logs[i].transactionHash, blockinfo.timestamp, dt, h]);
}
将数据存入数据库
let sqlstr = "insert into meta_order (`contract`,`tokenID`,`seller`,`buyer`,`token`,`price`,`blocknum`,`txhash`,`timestamp`,`dt`,`h`) values ? on duplicate key update contract=values(contract), tokenID=values(tokenID), seller=values(seller), buyer=values(buyer), token=values(token), price=values(price), blocknum=values(blocknum), timestamp=values(timestamp), dt=values(dt), h=values(h)";
let s = await DbQuery(sqlstr, values);
console.log(s);
} else {
console.log("have no eventlogs");
}
}
```此处mysql语法使用“on duplicate key update”，为防止重复录入数据库。
6.附加order表数据结构
```sql
CREATE TABLE `meta_order` (
`txType` smallint NOT NULL DEFAULT '1' COMMENT '交易类型：1.一口价；2.拍卖；',
`contract` char(42) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'nft合约地址',
`tokenID` int NOT NULL COMMENT 'tokenID',
`seller` char(42) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '卖家',
`buyer` char(42) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '买家',
`token` char(42) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '代币合约地址',
`price` decimal(65,0) NOT NULL COMMENT '价格',
`blocknum` int NOT NULL COMMENT '交易所在区块号',
`txhash` char(66) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '交易哈希',
`timestamp` int NOT NULL COMMENT '交易区块对应的时间戳',
`dt` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '""' COMMENT '交易日期',
`h` int NOT NULL COMMENT '交易小时数',
PRIMARY KEY (`txhash`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC
```
