9eZGXEx5

1const axios = require("axios");
2const sqlite3 = require("sqlite3").verbose();
3const async_helpers = require("async");
4const cliProgress = require("cli-progress");
5
6const db = new sqlite3.Database("./db");
7
8const API_URL =
9  "https://aged-crimson-water.solana-mainnet.quiknode.pro/be206f71a4733e43d0ff21e9179c6ed7d6802909/";
10
11function getTransactionForSignature(signature) {
12  const params = {
13    jsonrpc: "2.0",
14    id: 1,
15    method: "getConfirmedTransaction",
16    params: [signature, "jsonParsed"],
17  };
18  return axios
19    .post(API_URL, params)
20    .then((res) => {
21      try {
22        const result = res.data.result;
23        return result;
24      } catch {
25        throw "Cannot get transaction";
26      }
27    })
28    .catch((error) => {
29      console.warn("Failed to getTransaction: ", signature);
30      return null;
31    });
32}
33
34async function insertTransaction(txn, sig) {
35  return new Promise((resolve, reject) => {
36    try {
37      const insert = db.prepare(
38        "INSERT INTO transactions VALUES (json($txn), json($meta), $sig, $block, $slot)"
39      );
40      insert.run({
41        $txn: JSON.stringify(txn["transaction"]),
42        $meta: JSON.stringify(txn["meta"]),
43        $sig: sig,
44        $block: txn["blockTime"],
45        $slot: txn["slot"],
46      });
47      insert.finalize();
48      resolve();
49    } catch (e) {
50      reject(e);
51    }
52  });
53}
54
55async function getSignaturesToRetrive() {
56  const select = `
57    SELECT s.signature
58    FROM   signatures s
59          LEFT JOIN transactions t
60                  ON s.signature = t.signature
61    WHERE  t.signature IS NULL
62    LIMIT  1000; 
63  `;
64  return new Promise((resolve, reject) => {
65    db.all(select, (err, results) => {
66      if (err) {
67        reject(err);
68        return;
69      }
70      resolve(results.map((row) => row["signature"]));
71    });
72  });
73}
74
75async function getAllTransactions(progress) {
76  try {
77    let sigs = await getSignaturesToRetrive();
78
79    // keep processing 1000 signatures
80    while (sigs.length > 0) {
81      console.info("\nNum of transactions to retrieve: ", sigs.length);
82      progress.update(0);
83      progress.setTotal(sigs.length);
84      await async_helpers.mapLimit(sigs, 50, async (sig) => {
85        const txn = await getTransactionForSignature(sig);
86        if (txn) {
87          await insertTransaction(txn, sig);
88          progress.increment();
89        }
90      });
91      sigs = await getSignaturesToRetrive();
92    }
93  } catch (e) {
94    console.error("Failed to get transactions");
95  }
96}
97
98(async () => {
99  db.serialize(() => {
100    db.run(
101      "CREATE TABLE IF NOT EXISTS transactions (txn TEXT, meta TEXT, signature TEXT, blockTime INTEGER, slot INTEGER)"
102    );
103  });
104
105  const progress = new cliProgress.SingleBar(
106    {},
107    cliProgress.Presets.shades_classic
108  );
109  progress.start(0, 0, {
110    speed: "N/A",
111  });
112  await getAllTransactions(progress);
113  progress.stop();
114
115  // clean up db conn
116  db.close();
117})();
118