STEEMSQL - A public SQL database with all blockchain data – Update 8

in steemsql •  10 months ago

SteemSQL is a public MS-SQL database with all the blockchain data in it.

Previous posts:

What’s new?

1. Several tables have been updated to reflect the last changes in the blockchain data structure

Here the changes in details:

Changes in table Account

The field children_rshares2 is no longer used and has been deleted.

Changes in table Comments

The field post_bandwidth is no longer used and has been deleted.
The field blog_category is no longer used and has been deleted.
Two new fields have been added : tags_usage and guest_blog

2. The block's timestamp field has been duplicated in related tables

To retrieve the date and time associated with a transaction, it was necessary to make a join between the tables Tx… and the table Transactions and another join between the table Transactions and the table Blocks.

Despite the fact that best practices in database design advocate not duplicating a data field across different tables, it was clear that this design was a bit cumbersome when writing complex queries.

This is why I decided to duplicate the timestamp field from each block to all tables containing transactions (Tx…). This will make queries easier to write and to understand. It will also speed up a little bit the processing of these queries.

3. Performance improvement

Several indexes have been created to improve overall queries performances.

Support

If you need help, have any comment or request, you can contact me in SteemSQL channel on steemit.chat.

Thanks for reading.


All payouts from this post will be dedicated to keep SteemSQL running.
Thanks for your support.


footer created with steemitboard - click any award to see my board of honor

Support my work as a witness by voting for me here!

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  Trending

By the way, how much does it cost you to run this? I guess it is hosted on Amazon Web Services or something similar?

What you are doing with this project is awesome, I scratched the surface yesterday and can already see the potential.

Is there any documentation on the field names? A few things do not quite make sense to me as a newbie!

Also, I spotted loads of user posts created in 1970? Is that some Steemit API error?

·

Thanks for your comment.

Is there any documentation on the field names?

Not really, but you can have a look at the Steem API. All fields are the same you get when browsing the blockchain using the API.

Also, I spotted loads of user posts created in 1970? Is that some Steemit API error?

I have to check, but I guess these are users created before the created field appeared in the blockchain.
1970-01-01 is the replacement value in MS-SQL when a date field has no value and is not nullable.

·
·

Thanks, makes sense!

I have voted for you as a witness.

Steem On.

·

Really appreciated. Thanks!

·

Also going to do this :-)

Thank you for providing this service =)

If I ever get to the point where I need reading data from Steem blockchain, this is the one I'm going to use with my projects. First I need to learn how to write on it though! Thanks for providing this great service though.

Is there a Port for this DB?