[STEEMSQL] A public SQL database with all blockchain data – UPDATE 1

[arcange]

One week ago, I publicly made available a MS-SQL database with all the blockchain data in it .

This post is an update to the introduction post

What’s new?

New savings transactions support

With hardfork 14.2 finally deployed few hours ago, a new set of transaction type have been introduced.

The Database Injector now manage the following new transactions :

transfer_to_savings
transfer_from_savings
cancel_transfer_from_savings

These transactions are stored in the TxTransfers table with the type field set accordingly.
A new request_id field has been added to the table to store this new information.
A new amount_symbol has been added to easily distinguish which currency has been transferred

Accounts table added

An Accounts table has been added and store all the information received from the get_accounts function.

This table is populated/updated has follow:

• Each time a transaction involving an unknown account is injected in the database, the corresponding account data are inserted in the database.
• Each time a transaction involving a known account is injected in the database, the corresponding account dirty field is set to true
• Every 10 minutes, all accounts flagged as "dirty" are updated.

The procedure has been implemented to :

• avoid overloading the database server with too many account updates
• lower the Steem node load by querying accounts information less often

So be careful when your query involves the Accounts table. If the account is flagged as dirty, this means new transactions imported in the database may have impacted some fields value, but those values have not been updated yet.

Performance improvement

Several indexes have been created to improve overall queries performances.

Support

If you need help, have any comment or request, please use SteemSQL channel channel on steemit.chat.

---

You like this post, do not forget to upvote or follow me

Comments

[faddat]

Do you sync at every block? If not, Happy to help you adapt STEEMJSON to do this for you :). I'm interested in using your DB for many things but I'd like to know how much abuse is too much abuse for the machines you have it hosted on?

[arcange]

Every block and every transaction are synced every 10 seconds.
You can freely use the DB for any development you have. I made it public and it has been designed to support heavy load. Infrastructure may be even scaled up if required. Go on ;)

[faddat]

I absolutely would, if the source code was released. Without the source in the public domain you could (die, disappear, become disinterested, etc) and the services relying on this would poof.

If the source code were public, I'd use your server in a heartbeat. till them I'll have to continue to schlep through this problem my own way :(.

[arcange]

I can't opensource steemsql right now because I used some non-opensource libs.
I'm working on replacing them. As soon it's done, I will make code public.

[andrew0]

Thank you for the great work!

[arcange]

Thanks

[gamgam]

Upvoted and following you.

[arcange]

Thanks

[steempowerwhale]

Thanks for update. I have upvoted and resteemed.

[arcange]

Awesome! Thanks

[lukestokes]

I may have to install a VM to figure out how to connect to this db via PHP, but I really am interested in figuring it out eventually. Thanks again for this hard work!

[arcange]

Thanks for your comment.
It's quite easy to connect using PHP :

<?php
$serverName = "sql.steemsql.com";
$connectionInfo = array( "Database"=>"DBSteem", "UID"=>"steemit", "PWD"=>"steemit");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

[lukestokes]

By default, on my mac, PHP doesn't come compiled with the drivers needed so you end up with this:

Fatal error: Call to undefined function sqlsrv_connect()

I spent a long time this past weekend at the RESTfest hackathon trying to get the PDO stuff to work on my mac. It turned into a big pain, unfortunately.

Are you running PHP on windows, already compiled with php_sqlsrv_53_ts.dll? If you could get this working on OSX, that would be fantastic.

[arcange]

Unfortunately not working on OSX. Contact me on SteemSQL channel and we will see if we can solve it.

[miko]

You can get Uniserver, it's a nice lightweight server with httpd & sql, Apache 2.0(which supports PHP, javascript and the likes) and MySql respectively. Which works on Windows. Nice security, but i wouldn't use it for a production website, i only use it for development and having my own intranet(for having a family website so we can write each other, post news, chores, appointments, etc.. that's just on our own network) . I don't work for Uniserver, i just like their product. I just read you have a mac, so um ya, windows isn't mac, but i'll keep the post up incase anyone else may find it useful.

[arcange]

There are several Mac alternative to SQL Server Management Studio:

DbVisualizer
RazorSQL
SQLPro for MSSQL

[soldier]

Great update! Thx

[arcange]

More to come ... stay tuned!