Skip to content

Instantly share code, notes, and snippets.

@wechain
Forked from skzap/importer.js
Created August 12, 2018 17:58
Show Gist options
  • Save wechain/940be9368ad0fd3ba86f7d1168203986 to your computer and use it in GitHub Desktop.
Save wechain/940be9368ad0fd3ba86f7d1168203986 to your computer and use it in GitHub Desktop.
// steemwhales.com data importer
// this was made in the early days of steem
//
// a lot of things are wrong in it:
//
// how it doesnt use steemjs (it uses a super old module called steem rpc)
// the way it refreshes every existing accounts without priority for active accounts
// the way it stores the data (especially the history table)
// the way it counts followers (theres a much better API function for it now)
// it doesnt count delegation numbers
// all this logic shouldnt be in a single file
var options = {
// user: "username",
// pass: "password",
url: "wss://steemd-int.steemit.com",
apis: ["database_api", "follow_api"]
//debug: true
};
var https = require('https');
var _require = require("steem-rpc");
var Client = _require.Client;
var Api = Client.reset(options);
var mysql = require('mysql');
var connection = mysql.createConnection({
host : '',
user : 'steemw',
password : '',
database : 'steemw'
});
connection.connect();
var toBeAdded = [];
var toBeDetail = [];
var alreadyAdded = [];
var start_author = null;
var start_permlink = null;
function mysql_real_escape_string (str) {
return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
switch (char) {
case "\0":
return "\\0";
case "\x08":
return "\\b";
case "\x09":
return "\\t";
case "\x1a":
return "\\z";
case "\n":
return "\\n";
case "\r":
return "\\r";
case "\"":
case "'":
case "\\":
case "%":
return "\\"+char; // prepends a backslash to backslash, percent,
// and double/single quotes
}
});
}
function encode_utf8(s) {
return unescape(encodeURIComponent(s));
}
function decode_utf8(s) {
return decodeURIComponent(escape(s));
}
Api.initPromise.then(response => {
console.log("Api ready:", response);
loadExistingAccs(function(){
console.log(alreadyAdded.length+' accounts loaded');
updateGlobals();
var timer1 = setInterval(function() {
checkTenAccounts();
}, 800)
var timer2 = setInterval(function() {
saveDailyHistory();
addNewUsernames();
addUsernamesFromWebsite();
refreshHundredAccs();
//addFromRichList();
}, 8500)
var timer3 = setInterval(function() {
updateGlobals();
}, 1000*60)
})
// GET A CONTENT
// Api.database_api().exec("get_content", ['lukestokes', 'the-steemit-usdusdusd-challenge-prove-to-yourself-why-you-are-here']).then(response => {
// console.log("get_content", response);
// })
});
function refreshHundredAccs() {
var query = 'SELECT name, moreUpdatedOn FROM accounts WHERE updatedOn < (NOW() - INTERVAL 70 MINUTE) ORDER BY updatedOn ASC LIMIT 1000';
connection.query(query, function(err, rows, fields) {
if (err) throw err;
for (var i = 0; i < rows.length; i++) {
toBeAdded.push(rows[i].name);
}
console.log('Refreshing '+rows.length+' existing accs');
rows = rows.sort(function(a,b) {
if (!a.moreUpdatedOn || a.moreUpdatedOn == '0000-00-00 00:00:00')
a.moreUpdatedOn = '1999-08-16T17:00:00Z'
if (!b.moreUpdatedOn || b.moreUpdatedOn == '0000-00-00 00:00:00')
b.moreUpdatedOn = '1999-08-16T17:00:00Z'
return new Date(a.moreUpdatedOn) - new Date(b.moreUpdatedOn)
})
for (var i = 0; i<20 && i < rows.length && toBeDetail.length < 100; i++) {
toBeDetail.push(rows[i].name);
//console.log(rows[i]);
}
});
}
function updateAccCount() {
var query = 'UPDATE globals SET accounts_tracked=(SELECT COUNT(*) FROM accounts)';
connection.query(query, function(err, result) {
if (err) throw err;
});
}
function updatePrice() {
Api.database_api().exec("get_order_book", [10]).then(response => {
var bestBid = parseFloat(response.bids[0].real_price);
var bestAsk = parseFloat(response.asks[0].real_price);
var real_price = (bestBid+bestAsk)/2;
console.log('Updating 1STEEM='+real_price+' SBD');
var query = 'UPDATE globals SET real_price='+real_price;
connection.query(query, function(err, result) {
if (err) throw err;
});
})
getPriceTicker(function(ticker) {
console.log('Updating 1STEEM=$'+ticker.price_usd);
var query = 'UPDATE globals SET steem_price_usd='+ticker.price_usd;
connection.query(query, function(err, result) {
if (err) throw err;
});
})
getPriceTickerSBD(function(ticker) {
console.log('Updating 1SBD=$'+ticker.price_usd);
var query = 'UPDATE globals SET sbd_price_usd='+ticker.price_usd;
connection.query(query, function(err, result) {
if (err) throw err;
});
})
}
function updateGlobals() {
console.log("Updating globals...");
Api.database_api().exec("get_dynamic_global_properties", []).then(response => {
var query = 'UPDATE globals SET total_vesting_shares='+parseFloat(response.total_vesting_shares);
query += ' ,total_vesting_fund_steem='+parseFloat(response.total_vesting_fund_steem);
connection.query(query, function(err, result) {
if (err) throw err;
});
})
updatePrice();
updateAccCount();
}
function addUsernamesFromWebsite() {
var query = 'SELECT name FROM search';
connection.query(query, function(err, rows, fields) {
if (err) throw err;
for (var i = 0; i < rows.length; i++) {
toBeAdded.push(rows[i].name);
}
console.log('Adding '+rows.length+' accs to be added from website');
var query = 'DELETE FROM search';
connection.query(query, function(err) {
if (err) throw err;
});
});
}
function addNewUsernames(cb) {
var request = {limit:20};
if (start_author && start_permlink)
request = {limit: 20, start_author: start_author, start_permlink: start_permlink}
Api.database_api().exec("get_discussions_by_hot", [request]).then(response => {
for (var i = 0; i < response.length; i++) {
addIfNew(response[i].author);
for (var y = 0; y < response[i].active_votes.length; y++) {
addIfNew(response[i].active_votes[y].voter);
}
if (i == response.length-1) {
start_author = response[i].author;
start_permlink = response[i].permlink;
}
}
console.log('Now '+toBeAdded.length+' accs to be added');
cb();
})
}
function checkTenAccounts() {
var manyAccs = [];
while (manyAccs.length < 100 && toBeAdded.length > 0) {
manyAccs.push(toBeAdded[0]);
if (alreadyAdded.indexOf(toBeAdded[0]) == -1)
alreadyAdded.push(toBeAdded[0]);
toBeAdded.splice(0,1);
}
checkAccounts(manyAccs);
console.log('Checking '+ manyAccs.length + ' accs');
// updateDetailUser(toBeDetail[0]);
// updateDetailUser(toBeDetail[0]);
// updateDetailUser(toBeDetail[0]);
// updateDetailUser(toBeDetail[0]);
}
function updateDetailUser(detailUser) {
if (!detailUser) return;
toBeDetail.splice(0,1);
getFollowing(detailUser, null, function(following) {
getFollowers(detailUser, null, function(followers) {
console.log(detailUser, followers.length, following.length);
var queryDetailUser = 'UPDATE accounts';
queryDetailUser += ' SET moreUpdatedOn=NOW(), followers='+followers.length+',following='+following.length;
queryDetailUser += ' WHERE name="'+detailUser+'"';
connection.query(queryDetailUser, function(err, result) {
if (err) throw err;
});
})
})
}
function loadExistingAccs(cb) {
var query = 'SELECT name FROM accounts';
connection.query(query, function(err, rows, fields) {
if (err) throw err;
for (var i = 0; i < rows.length; i++) {
alreadyAdded.push(rows[i].name);
}
cb();
});
}
function addIfNew(username) {
if (alreadyAdded.indexOf(username) == -1 && toBeAdded.indexOf(username) == -1) {
toBeAdded.push(username);
}
}
function checkAccounts(accounts) {
Api.database_api().exec("get_accounts", [accounts]).then(response => {
for (var i = 0; i < response.length; i++) {
var acc = response[i];
//if (acc.json_metadata)
//console.log(acc.json_metadata)
acc.balance = parseFloat(acc.balance);
acc.sbd_balance = parseFloat(acc.sbd_balance);
acc.vesting_shares = parseFloat(acc.vesting_shares);
acc.reputation = parseInt(acc.reputation);
acc.vesting_withdraw_rate = parseFloat(acc.vesting_withdraw_rate);
var lastActive = new Date(acc.last_vote_time+'Z');
var lastPost = new Date(acc.last_post+'Z');
if (lastPost > lastActive) lastActive = lastPost;
lastActive = lastActive.toISOString().substring(0,19);
var query = 'INSERT INTO accounts (name, post_count, balance, sbd_balance, vesting_shares, posting_rewards, curation_rewards, reputation, next_vesting_withdrawal, vesting_withdraw_rate, last_active, json_metadata, updatedOn)'
query += ' VALUES (\''+acc.name+'\','+acc.post_count+','+acc.balance+','+acc.sbd_balance+','+acc.vesting_shares+','+acc.posting_rewards+','+acc.curation_rewards+','+acc.reputation+',\''+acc.next_vesting_withdrawal+'\','+acc.vesting_withdraw_rate+',\''+lastActive+'\','+connection.escape(encode_utf8(acc.json_metadata))+',NOW())'
query += ' ON DUPLICATE KEY'
query += ' UPDATE post_count='+acc.post_count+',balance='+acc.balance+',sbd_balance='+acc.sbd_balance+',vesting_shares='+acc.vesting_shares+',posting_rewards='+acc.posting_rewards+',curation_rewards='+acc.curation_rewards+',reputation='+acc.reputation+',next_vesting_withdrawal=\''+acc.next_vesting_withdrawal+'\',vesting_withdraw_rate='+acc.vesting_withdraw_rate+',last_active=\''+lastActive+'\',json_metadata='+connection.escape(encode_utf8(acc.json_metadata))+',updatedOn=NOW()'
console.log(query)
connection.query(query, function(err, result) {
if (err) throw err;
});
}
})
}
function addFromRichList() {
var fetchUrl = require("fetch").fetchUrl;
fetchUrl("https://steemd.com/richlist", function(error, meta, body){
var html = body.toString();
while (html.indexOf('@') != -1) {
html = html.substr(html.indexOf('@')+1);
var nextUsername = html.substr(0,html.indexOf('\"'));
addIfNew(nextUsername);
}
});
}
function saveDailyHistory() {
var query = 'SELECT COUNT(*) as result FROM history WHERE date = CURDATE()';
connection.query(query, function(err, rows, fields) {
if (rows[0].result == 0) {
console.log('UPDATING DAILY HISTORY !!');
query = 'CALL dailyhistory()';
connection.query(query, function(err, result) {
if (err) throw err;
});
} else {
console.log('Still same day')
}
});
}
// function getAccountVotes() {
// Api.database_api().exec("get_account_votes", ['heimindanger', 'curator']).then(response => {
// console.log(response);
// })
// }
function getFollowers(username, followers, cb) {
var start_follower = "";
if (!followers) followers = [];
if (followers.length > 0) start_follower = followers[followers.length-1];
Api.follow_api().exec("get_followers", [username, start_follower, "blog", 100]).then(newfollow => {
var orig_length = followers.length;
for (var i = 0; i < newfollow.length; i++) {
if (followers.indexOf(newfollow[i].follower) == -1) {
followers.push(newfollow[i].follower);
}
}
if (followers.length-98 > orig_length) {
getFollowers(username, followers, function() {
cb(followers);
})
} else {
cb(followers);
}
})
}
function getFollowing(username, followers, cb) {
var start_follower = "";
if (!followers) followers = [];
if (followers.length > 0) start_follower = followers[followers.length-1];
Api.follow_api().exec("get_following", [username, start_follower, "blog", 100]).then(newfollow => {
var orig_length = followers.length;
for (var i = 0; i < newfollow.length; i++) {
if (followers.indexOf(newfollow[i].following) == -1) {
followers.push(newfollow[i].following);
}
}
if (followers.length-98 > orig_length) {
getFollowing(username, followers, function() {
cb(followers);
})
} else {
cb(followers);
}
})
}
function getPriceTicker(callback) {
https.get({
host: 'api.coinmarketcap.com',
path: '/v1/ticker/steem/'
}, function(response) {
var body = '';
response.on('data', function(d) {
body += d;
});
response.on('end', function() {
var parsed = JSON.parse(body);
callback(parsed[0]);
});
});
}
function getPriceTickerSBD(callback) {
https.get({
host: 'api.coinmarketcap.com',
path: '/v1/ticker/steem-dollars/'
}, function(response) {
var body = '';
response.on('data', function(d) {
body += d;
});
response.on('end', function() {
var parsed = JSON.parse(body);
callback(parsed[0]);
});
});
}
//connection.end();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment