同步合约数据到数据库经典案例1

之前在《数字藏品发行平台的架构》里讲过，有一种架构希望以区块链的数据为核心。这样就需要将合约保存在区块链上的数据同步到数据库里，方便后续中间件接口的开发。

本次我们以同步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'