My work to prepare SteemSQL for the coming SMT and to revamp some table design is now complete.
In my previous post about SteemSQL database changes, I announced I will prepare SteemSQL's database for SMTs and taking the opportunity to also make additional modifications to improve the performance of SteemSQL for (d)Apps and processes that use SteemSQL.
1. New SMT transactions tables
On top of running on the mainnet, SteemSQL Database Injector has been running in parallel on the testnet to process SMT's operations.
A set of new tables has been created, each one corresponding to the different SMT related transactions that one will be able to issue:
- TxSMTCreates
- TxSMTSetRuntimeParameters
- TxSMTSetSetupParameters
- TxSMTSetupEmissions
- TxSMTSetupICOTiers
- TxSMTSetups
These tables will start to be populated as soon as SMT will be released. In the meantime, you can already take a look at it to get used to their structure.
For more information on the different fields contained in these tables, I invite you to read the excellent posts written by @howo on this subject here and here
2. Existing tables revamped
One of SteemSQL's weak points in terms of performance is the fact that many fields containing value for assets are stored in the database as character strings. This is mainly because, during the initial design of the database, I wanted to stick 1:1 to the structure of the data returned by the Steem nodes. And they return amounts as a numeric value followed by its symbol (ex: "1234.123 STEEM" or "654.456 SBD")
In its updated design, the database will now store assets value in two separate columns, the first one with the same name containing the numerical value and a second one suffixed with _symbol which will contain... the asset symbol ;)
Before:
| column | type | value |
|---|---|---|
| vesting_shares | varchar(50) | 12345.678 VESTS |
After:
| column | type | value |
|---|---|---|
| vesting_shares | numeric(19,6) | 12345.678 |
| vesting_shares_symbol | varchar(5) | VESTS |
This change will allow me to create additional indexes on assets columns to speed up the user's queries and lower resource usage on the server.
3. When these changes will occurs?
The new SMT tables are already available.
The switch to the new fields for assets columns will be put into production on 2020-01-31 at 20:00:00 UTC.
This gives about a week to anyone who relies on SteemSQL to adapt their processes.
If this deadline is still too short for you, SteemSQL will make the old content of the fields available for a week in an additional column prefixed with an x_ (ex: x_vesting_shares). This means that you can still run your processes, for a maximum of one week, by simply changing the name of the columns in your code. After one week, these x_ columns will be deleted!
Support
If you have any questions or need assistance with SteemSQL, support is provided on the dedicated SteemSQL channel on steem.chat,
You can also contact me directly on steem.chat, Discord or Telegram
Thanks for reading!
Support me and my work for the Steem community.
###
