Refactor aws database

[saratomaz]

NEW DATABASE STRUCTURE

OLD DATABASE STRUCTURE

(44 TABLES - GROWING EACH NEW ENV)

TO DO/THINK:

• We don't need a separate table for each environment since we can differentiate them using the env field.
• Redefine mandatory fields.

✅ mainnet / preprod / preview / shelley-qa / staging / testnet

Field	Type	Null	Key	Default	Extra
identifier	text	YES -> NO		NULL
env	text	YES -> NO		NULL
tag_no1	text	YES		NULL
tag_no2	text	YES		NULL
cli_version1 -> cli_revision1	text	YES -> NO		NULL
cli_version2 -> cli_revision2	text	YES		NULL
hydra_eval_no1 -> node_revision1	text	YES -> NO		NULL
hydra_eval_no2 -> node_revision1	text	YES		NULL
start_sync_time1	text	YES -> NO		NULL
end_sync_time1	text	YES -> NO		NULL
start_sync_time2	text	YES		NULL
end_sync_time2	text	YES		NULL
last_slot_no1	int	YES		NULL
last_slot_no2	text	YES		NULL
start_node_secs1	int	YES		NULL
start_node_secs2	int	YES		NULL
sync_time_seconds1	int	YES		NULL
sync_time1	text	YES		NULL
sync_time_seconds2	int	YES		NULL
sync_time2	text	YES		NULL
total_chunks1	int	YES		NULL
total_chunks2	text	YES		NULL
platform_system	text	YES		NULL
platform_release	text	YES		NULL
platform_version	text	YES		NULL
chain_size_bytes	bigint	YES		NULL
eras_in_test	text	YES		NULL
no_of_cpu_cores	int	YES		NULL
epoch_no_d_zero	int	YES		NULL
total_ram_in_GB	text	YES		NULL
start_slot_no_d_zero	int	YES		NULL
TO REMOVE
cli_git_rev1	text	YES -> NO		NULL
cli_git_rev2	text	YES		NULL
MOVE TO ANOTHER TABLE
alonzo_start_time	text	YES		NULL
alonzo_start_epoch	text	YES		NULL
alonzo_slots_in_era	text	YES		NULL
alonzo_start_sync_time	text	YES		NULL
alonzo_end_sync_time	text	YES		NULL
alonzo_sync_duration_secs	text	YES		NULL
alonzo_sync_speed_sps	text	YES		NULL
babbage_start_time	varchar(255)	YES		NULL
babbage_start_epoch	varchar(255)	YES		NULL
babbage_slots_in_era	varchar(255)	YES		NULL
babbage_start_sync_time	varchar(255)	YES		NULL
babbage_end_sync_time	varchar(255)	YES		NULL
babbage_sync_duration_secs	varchar(255)	YES		NULL
babbage_sync_speed_sps	varchar(255)	YES		NULL
conway_start_time	varchar(255)	YES		NULL
conway_start_epoch	varchar(255)	YES		NULL
conway_slots_in_era	varchar(255)	YES		NULL
conway_start_sync_time	varchar(255)	YES		NULL
conway_end_sync_time	varchar(255)	YES		NULL
conway_sync_duration_secs	varchar(255)	YES		NULL
conway_sync_speed_sps	varchar(255)	YES		NULL
byron_start_time	text	YES		NULL
byron_start_epoch	int	YES		NULL
byron_slots_in_era	int	YES		NULL
byron_start_sync_time	text	YES		NULL
byron_end_sync_time	text	YES		NULL
byron_sync_duration_secs	int	YES		NULL
byron_sync_speed_sps	int	YES		NULL
shelley_start_time	text	YES		NULL
shelley_start_epoch	int	YES		NULL
shelley_slots_in_era	int	YES		NULL
shelley_start_sync_time	text	YES		NULL
shelley_end_sync_time	text	YES		NULL
shelley_sync_duration_secs	int	YES		NULL
shelley_sync_speed_sps	int	YES		NULL
allegra_start_time	text	YES		NULL
allegra_start_epoch	int	YES		NULL
allegra_slots_in_era	int	YES		NULL
allegra_start_sync_time	text	YES		NULL
allegra_end_sync_time	text	YES		NULL
allegra_sync_duration_secs	int	YES		NULL
allegra_sync_speed_sps	int	YES		NULL
mary_start_time	text	YES		NULL
mary_start_epoch	int	YES		NULL
mary_slots_in_era	int	YES		NULL
mary_start_sync_time	text	YES		NULL
mary_end_sync_time	text	YES		NULL
mary_sync_duration_secs	int	YES		NULL
mary_sync_speed_sps	int	YES		NULL

✅ mainnet_db_sync / preprod_db_sync / preview_db_sync / shelley-qa_db_sync / testnet_db_sync

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO	PRI	NULL
env	varchar(255)	NO		NULL
node_cli_version -> cli_revision	varchar(255)	NO		NULL
node_git_revision -> node_revision	varchar(255)	NO		NULL
db_sync_git_rev -> db_sync_revision	varchar(255)	NO		NULL
start_test_time	varchar(255)	NO		NULL
end_test_time	varchar(255)	NO		NULL
total_sync_time_in_sec	int	YES		NULL
total_sync_time_in_h_m_s	varchar(255)	YES		NULL
last_synced_epoch_no	int	YES		NULL
last_synced_block_no	int	YES		NULL
platform_system	varchar(255)	NO		NULL
platform_release	varchar(255)	NO		NULL
platform_version	varchar(255)	NO		NULL
no_of_cpu_cores	int	YES		NULL
total_ram_in_GB	int	YES		NULL
total_database_size	varchar(100)	YES		NULL
last_synced_slot_no	int	YES		NULL
rollbacks	varchar(10)	YES		NULL
errors	varchar(10)	YES		NULL
cpu_percent_usage	decimal(10,0)	YES		NULL
total_rss_memory_usage_in_B	bigint	YES		NULL
TO REMOVE
node_branch	varchar(100)	YES		NULL
node_version	varchar(100)	YES		NULL
db_version	varchar(100)	YES		NULL
db_sync_branch	varchar(255)	YES		NULL
db_sync_version	varchar(255)	NO		NULL
node_pr	varchar(255)	NO		NULL

✅ mainnet_db_sync_snapshot_creation / preprod_db_sync_snapshot_creation / preview_db_sync_snapshot_creation / shelley-qa_db_sync_snapshot_creation

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO	PRI	NULL
env	varchar(255)	NO		NULL
db_sync_branch	varchar(255)	YES		NULL
db_version	varchar(100)	YES		NULL
db_sync_version	varchar(255)	NO		NULL
db_sync_git_rev	varchar(255)	NO		NULL
start_test_time	varchar(255)	NO		NULL
end_test_time	varchar(255)	NO		NULL
snapshot_creation_time_in_sec	int	YES		NULL
snapshot_creation_time_in_h_m_s	varchar(255)	YES		NULL
snapshot_size_in_mb	int	YES		NULL
stage_2_cmd	varchar(255)	NO		NULL
stage_2_result	varchar(255)	NO		NULL
platform_system	varchar(255)	NO		NULL
platform_release	varchar(255)	NO		NULL
platform_version	varchar(255)	NO		NULL
no_of_cpu_cores	int	YES		NULL
total_ram_in_GB	int	YES		NULL

✅ mainnet_db_sync_snapshot_restoration / testnet_db_sync_snapshot_restoration

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO	PRI	NULL
env	varchar(255)	NO		NULL
node_pr	varchar(255)	NO		NULL
node_branch	varchar(255)	NO		NULL
db_sync_branch	varchar(255)	YES		NULL
node_cli_version	varchar(255)	NO		NULL
node_git_revision	varchar(255)	NO		NULL
db_sync_version	varchar(255)	NO		NULL
db_sync_git_rev	varchar(255)	NO		NULL
start_test_time	varchar(255)	NO		NULL
end_test_time	varchar(255)	NO		NULL
node_total_sync_time_in_sec	int	YES		NULL
node_total_sync_time_in_h_m_s	varchar(255)	YES		NULL
db_total_sync_time_in_sec	int	YES		NULL
db_total_sync_time_in_h_m_s	varchar(255)	YES		NULL
snapshot_url	varchar(255)	YES		NULL
snapshot_name	varchar(255)	YES		NULL
snapshot_epoch_no	int	YES		NULL
snapshot_block_no	int	YES		NULL
snapshot_slot_no	int	YES		NULL
last_synced_epoch_no	int	YES		NULL
last_synced_block_no	int	YES		NULL
last_synced_slot_no	int	YES		NULL
cpu_percent_usage	decimal(10,0)	YES		NULL
total_rss_memory_usage_in_B	bigint	YES		NULL
total_database_size	varchar(255)	NO		NULL
rollbacks	varchar(10)	NO		NULL
errors	varchar(10)	NO		NULL
platform_system	varchar(255)	NO		NULL
platform_release	varchar(255)	NO		NULL
platform_version	varchar(255)	NO		NULL
no_of_cpu_cores	int	YES		NULL
total_ram_in_GB	int	YES		NULL
node_version	varchar(100)	YES		NULL
db_version	varchar(100)	YES		NULL

✅ mainnet_epoch_duration / preprod_epoch_duration / preview_epoch_duration / shelley_qa_epoch_duration / staging_epoch_duration / testnet_epoch_duration

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO		NULL
epoch_no	int	YES		NULL
sync_duration_secs	int	YES		NULL

✅ mainnet_epoch_duration_db_sync / preprod_epoch_duration_db_sync / preview_epoch_duration_db_sync / shelley-qa_epoch_duration_db_sync / testnet_epoch_duration_db_sync

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO		NULL
epoch_no	int	YES		NULL
sync_duration_secs	int	YES		NULL

✅ mainnet_logs / preprod_logs / preview_logs / shelley_qa_logs / staging_logs / testnet_logs

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO		NULL
timestamp	varchar(255)	YES		NULL
slot_no	int	YES		NULL
ram_bytes	varchar(255)	YES		NULL
cpu_percent	varchar(255)	YES		NULL
rss_ram_bytes	varchar(255)	YES		NULL

✅ mainnet_performance_stats_db_sync / preprod_performance_stats_db_sync / preview_performance_stats_db_sync / shelley-qa_performance_stats_db_sync / testnet_performance_stats_db_sync

Field	Type	Null	Key	Default	Extra
identifier	varchar(255)	NO		NULL
time	int	YES		NULL
slot_no	int	YES		NULL
cpu_percent_usage	decimal(10,0)	YES		NULL
rss_mem_usage	bigint	YES		NULL

✅ mainnet_tx_count

Field	Type	Null	Key	Default	Extra
epoch_no	int	NO		NULL
tx_count	int	NO		NULL

❌ node_cli

Field	Type	Null	Key	Default	Extra
build_id	text	YES		NULL
build_no	int	YES		NULL
build_started_at	text	YES		NULL
build_finished_at	text	YES		NULL
build_duration	text	YES		NULL
build_status	text	YES		NULL
build_web_url	text	YES		NULL
test_branch	text	YES		NULL
node_branch	text	YES		NULL
node_rev	text	YES		NULL
cluster_era	text	YES		NULL
tx_era	text	YES		NULL

❌ dbsync_cli

Field	Type	Null	Key	Default	Extra
build_id	text	YES		NULL
build_no	int	YES		NULL
build_started_at	text	YES		NULL
build_finished_at	text	YES		NULL
build_duration	text	YES		NULL
build_status	text	YES		NULL
build_web_url	text	YES		NULL
test_branch	text	YES		NULL
node_branch	text	YES		NULL
node_rev	text	YES		NULL
dbsync_branch	text	YES		NULL
dbsync_rev	text	YES		NULL
cluster_era	text	YES		NULL
tx_era	text	YES		NULL

❌ node_nightly

Field	Type	Null	Key	Default	Extra
build_id	text	YES		NULL
build_no	int	YES		NULL
build_started_at	text	YES		NULL
build_finished_at	text	YES		NULL
build_duration	text	YES		NULL
build_status	text	YES		NULL
build_web_url	text	YES		NULL
test_branch	text	YES		NULL
node_branch	text	YES		NULL
node_rev	text	YES		NULL
cluster_era	text	YES		NULL
tx_era	text	YES		NULL
details	varchar(255)	YES		NULL

❌ dbsync_nightly

Field	Type	Null	Key	Default	Extra
build_id	text	YES		NULL
build_no	int	YES		NULL
build_started_at	text	YES		NULL
build_finished_at	text	YES		NULL
build_duration	text	YES		NULL
build_status	text	YES		NULL
build_web_url	text	YES		NULL
test_branch	text	YES		NULL
node_branch	text	YES		NULL
node_rev	text	YES		NULL
dbsync_branch	text	YES		NULL
dbsync_rev	text	YES		NULL
cluster_era	text	YES		NULL
tx_era	text	YES		NULL
details	text	YES		NULL