SteemPlus API Refractor and new features

stoodkev(71)

Published in

#utopian-io

Words

354

Reading

2 min

Listen

Play

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.