Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Modify the history_trades table to be friendlier for price aggregation #103

Closed
tomerweller opened this issue Oct 16, 2017 · 1 comment · Fixed by #125
Closed

Modify the history_trades table to be friendlier for price aggregation #103

tomerweller opened this issue Oct 16, 2017 · 1 comment · Fixed by #125
Assignees
Labels

Comments

@tomerweller
Copy link
Contributor

tomerweller commented Oct 16, 2017

In preparation for #94, modifying the history_trades table can assist in simpler aggregation.
The main two issues to handle:

  1. history_trades has no time of creation, only operation id. This requires joining with the history_ledgers to get exact times.
  2. The separation of buyers and sellers is not relevant for aggregation and forces every query to handle both possible directions.
@tomerweller
Copy link
Contributor Author

tomerweller commented Oct 17, 2017

A proposition for this table, assuming there's a history_assets lookup_table. (stellar-deprecated/horizon#378)

CREATE TABLE history_trades (
  history_operation_id BIGINT NOT NULL,
  ledger_closed_at TIMESTAMP NOT NULL,
  "order" INTEGER NOT NULL,
  offer_id BIGINT NOT NULL,
  base_asset_id BIGINT NOT NULL REFERENCES history_assets(id),
  base_volume BIGINT NOT NULL CHECK (base_volume > 0),
  counter_asset_id BIGINT NOT NULL REFERENCES history_assets(id),
  counter_volume BIGINT NOT NULL CHECK (counter_volume > 0),
  direction BIT, -- 0: base is seller, 1: counter is seller
  CHECK(base_asset_id < counter_asset_id) -- Check that convention is followed.
);

CREATE INDEX htrd_pair_time_lookup ON history_trades USING BTREE(base_asset_id, counter_asset_id, ledger_closed_at);
CREATE INDEX htrd_time_lookup ON history_trades USING BTREE(ledger_closed_at);
CREATE INDEX htrd_counter_lookup ON history_trades USING BTREE(counter_asset_id);
-- Are the following indexes actually used?
CREATE UNIQUE INDEX htrd_pid ON history_trades USING btree (history_operation_id, "order");
CREATE INDEX htrd_by_offer ON history_trades USING btree (offer_id);

@tomerweller tomerweller self-assigned this Oct 17, 2017
tomerweller added a commit that referenced this issue Oct 20, 2017
update ingestion and /trades endpoint to reflect changes
closes #103
tomerweller added a commit that referenced this issue Oct 26, 2017
* m trades table
modify db schema
update ingestion 
modify /trades resource endpoint
eliminate previous trades endpoints
closes #103
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants