SteemPlus API Refractor and new features

We have been refractoring all the SQL requests thanks to the generous help of @arcange, creator of SteemSQL.

Read on to see how we improved theses requests, and what s new in our backend.

SQL Request enhancement:

@cedricguillas and myself were not proficient enough in SQL to really optimize our requests. Thanks to @arcange's help, we improved it by applying the following changes:

• Use of Pools for better simultaneous requests handling.
• Taking out the NOLOCKs since we are using a view of the real database.
• Better JOIN syntax.
• Use of brackets rather than quotation marks when a field has a keyword name (for example [from]).
• Change GETDATE to GETUTCDATE.
• Using DATEADD rather than DATEDIFF.
• No SELECT * in a subquery when some fields are unused in the main query.
• Replacing true/false booleans by 0/1to avoid useless casting.

New endpoints :

• /api/get-rewards/:usernamewill fetch the rewards information for the account username. These rewards information contain pending and paid (the last 7 days) author rewards, paid benefactor rewards and paid curation rewards.

Result example:

[{"timestamp":"2018-05-09T06:19:51.000Z","author":"stoodkev","permlink":"re-everittdmickey-re-stoodkev-re-everittdmickey-re-stoodkev-re-everittdmickey-re-stoodkev-re-everittdmickey-re-stoodkev-steemplus-delegation-details-20180509t061950365z","pending_payout_value":0.05,"reward":-1,"sbd_payout":-1,"steem_payout":-1,"vests_payout":-1,"beneficiaries":"[]","type":"pending_author"},{"timestamp":"2018-05-09T05:34:39.000Z","author":"stoodkev","permlink":"re-jeffandhisguitar-re-steem-plus-steemsql-powered-mentions-and-various-bug-fixes-20180502t053437714z","pending_payout_value":-1,"reward":-1,"sbd_payout":0.428,"steem_payout":0,"vests_payout":248.3505,"beneficiaries":"","type":"paid_author"},{"timestamp":"2018-05-09T05:32:15.000Z","author":"stoodkev","permlink":"re-jeenger-re-steem-plus-steemsql-powered-mentions-and-various-bug-fixes-20180502t053213653z","pending_payout_value":-1,"reward":-1,"sbd_payout":0.028,"steem_payout":0,"vests_payout":18.3209,"beneficiaries":"","type":"paid_author"}...

• /job/welcome-users/:key: a job protected by a key that I will execute every ten minutes using a scheduler. It is used to welcome new users on introduceyourselfwith a comment explaining them briefly why and how they should SteemPlus for a better Steem experience. It will be improved over time to better select the posts to comment and upvote some of them.

Coming soon

• User statistics
• Better welcoming bot

Repository, Pull requests and Commits

• Repository : SteemPlus-API
• Main Pull Request : Refractor API
• Additionnal commit : Adding masterkey to protect the welcoming job

Wanna contribute?

Join us on Discord

Installation procedures

• Subscribe to SteemSQL (10 SBD/month)
• Clone this repository
• npm install
• Set the environment variables (information received by transfer when you subscribe to SteemSQL). These environment variables are LOGIN, PASSWORD, SQL_API and DB.
• npm run start
• You should be able to test it on localhost:3000.

Big shoutout to @jerrybanfield and his budgets project that allow us the speed up the development of SteemPlus!

@stoodkev and @cedricguillas for @steem-plus

Powered by @adsactly

Powered by @utopian-io

Like what I do? Vote @stoodkev for witness!