· 4 years ago · Sep 06, 2021, 07:06 PM
1const axios = require("axios");
2const sqlite3 = require("sqlite3").verbose();
3const db = new sqlite3.Database("./db");
4
5const API_URL =
6 "https://aged-crimson-water.solana-mainnet.quiknode.pro/be206f71a4733e43d0ff21e9179c6ed7d6802909/";
7
8function getSignaturesForAddress(address, before) {
9 const params = {
10 jsonrpc: "2.0",
11 id: 1,
12 method: "getConfirmedSignaturesForAddress2",
13 params: [
14 address,
15 {
16 limit: 1000,
17 },
18 ],
19 };
20 if (before) {
21 params["params"][1]["before"] = before;
22 console.info("Getting signatures before: ", before);
23 }
24 return axios
25 .post(API_URL, params)
26 .then((res) => {
27 try {
28 const results = res.data.result;
29 return results;
30 } catch {
31 throw "Cannot get signature";
32 }
33 })
34 .catch((error) => {
35 console.warn("Failed to getSignatures before: ", before);
36 return null;
37 });
38}
39
40function getAllSignaturesForAddress(address) {
41 return new Promise((resolve, reject) => {
42 if (!address) {
43 reject("Needs an address");
44 return;
45 }
46 db.get("SELECT signature FROM oldest_signature", async (err, res) => {
47 if (err) {
48 console.error("Failed to retrive oldest signature in db");
49 }
50 let before = res.signature === "0" ? undefined : res.signature;
51 console.info("Getting all signatures before ", before);
52 let sig_results = await getSignaturesForAddress(address, before);
53 while (sig_results.length > 0) {
54 console.info("Num of signatures: ", sig_results.length);
55 console.info("Inserting into DB");
56 await insertSignaturesIntoDB(sig_results);
57 const next = sig_results[sig_results.length - 1]["signature"];
58 sig_results = await getSignaturesForAddress(address, next);
59 }
60 resolve();
61 });
62 });
63}
64
65async function insertSignaturesIntoDB(signatures) {
66 return new Promise((resolve, reject) => {
67 if (signatures.length == 0) {
68 resolve();
69 return;
70 }
71 db.serialize(() => {
72 // do the signatures insert and old_sig update in a txn
73 // if this step, fails we can safely re-run the script
74 // without missing any signatures
75 db.exec("BEGIN");
76 const insert = db.prepare(
77 "INSERT INTO signatures VALUES ($sig, $block, $slot)"
78 );
79 for (let i = 0; i < signatures.length; i++) {
80 insert.run({
81 $sig: signatures[i]["signature"],
82 $block: signatures[i]["blockTime"],
83 $slot: signatures[i]["slot"],
84 });
85 }
86 insert.finalize();
87 console.log("Insert complete. Update oldest_signature");
88 db.run("UPDATE oldest_signature SET signature = $last_sig WHERE 1=1", {
89 $last_sig: signatures[signatures.length - 1]["signature"],
90 });
91 db.exec("COMMIT");
92 resolve();
93 });
94 });
95}
96
97(async () => {
98 db.serialize(() => {
99 db.run(
100 "CREATE TABLE IF NOT EXISTS signatures (signature TEXT, blockTime INTEGER, slot INTEGER)"
101 );
102 db.run("CREATE TABLE IF NOT EXISTS oldest_signature (signature TEXT)");
103 db.run(
104 "INSERT INTO oldest_signature (signature) SELECT '0' WHERE NOT EXISTS (SELECT * FROM oldest_signature)"
105 );
106 });
107
108 await getAllSignaturesForAddress(
109 "8749adNqCXzVjdYVCUFcjUUxsPcHuCW482roGqsxtMRX"
110 );
111 db.close();
112})();
113