feat: switch to Atlas for DDL

RSS3-Network / Global-Indexer

In the vast RSS3 Network, the Global Indexer navigates requests with precision, it is a beacon in the byte-filled sea.

MIT License

13 stars 0 forks source link

feat: switch to Atlas for DDL #158

Closed HenryQW closed 2 months ago

HenryQW commented 3 months ago

Since Atlas does not support multiple schemas with gorm+crdb, I have moved all schemas to public.
Production workflow needs to be set up (not the scope of this PR, and ops is out of my league).
See internal/database/dialer/cockroachdb/README.md for more information.

A second CRDB container is needed as a temporary DB for Atlas to perform its magic.

Close #147

HenryQW commented 2 months ago

also it's worth paying attention to https://github.com/RSS3-Network/Node/issues/208

polebug commented 2 months ago

Got a dilemma：

When creating some tables, it needs to generate the "id" bigint GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 MINVALUE 0 START 0), such as average_tax_rate_submissions table.

But gorm's tag doesn't cover this requirement, its autoIncrement, autoIncrementIncrement don't work (docs: https://gorm.io/docs/models.html#Fields-Tags)

Candinya commented 2 months ago

add a reminder: GORM's tag cannot specify the name of Composite Primary Key, some database requires (manual) reconstruction

Candinya commented 2 months ago

Table name (schema) change

bridge
- bridge.events -> bridge_events
- bridge.transactions -> bridge_transactions
node
- node.events -> node_events
- node.min_tokens_to_stake_snapshots -> node_min_tokens_to_stake_snapshots
- node.count_snapshots -> node_count_snapshots
- node.operator_profit_snapshots -> node_operator_profit_snapshots
stake
- stake.chips -> stake_chips
- stake.events -> stake_events
- stake.transactions -> stake_transactions
- stake.count_snapshots -> stake_count_snapshots
- stake.profit_snapshots -> stake_profit_snapshots

Table field change

node_info: local -> location
epoch: total_reward_items -> total_reward_nodes
epoch_item:
- tax_amounts -> tax_collected
- request_counts -> request_count

Candinya commented 2 months ago

Change table commands: (Refer to https://www.cockroachlabs.com/docs/stable/alter-table )

Change table name:

ALTER TABLE ... RENAME TO ...

Change table schema:

ALTER TABLE ... SET SCHEMA ...

Change column name:

ALTER TABLE ... RENAME COLUMN ...

Candinya commented 2 months ago

keep manually maintained SQL + more tests