cofacts/rumors-db

Refactor DB mapping relations

MrOrz opened this issue · 6 comments

MrOrz commented

From the conversation:

我會找時間整理一下現在 DB mappings 之間的 relation。
之前為了要讓使用者可以比較好 filter 一些東西，所以 foreign key 擺放得有點奇怪（非傳統 RDBMS 的結構），但當初設計的 foreign key 擺放方式，對『按照「最後有人回報」的間來排序』沒有助益 orz

整理完 mapping 之後，一併整理 query 的需求（例如說要按照啥排序、要做出一個列表來列出「有 non-article」的文章等等），希望能與 @darkbtf 或 @sayuan  一起討論一下要怎麼調整 mapping 比較好 @@
  • Update UML diagram
  • Conduct discussions & reach concensus
  • Implement as index
  • Change API server, make unit tests pass
MrOrz commented

filter 的需求：
cofacts/rumors-api#35

另外，這次的 DB mapping relation 也可以加入 segment 設計。
http://beta.hackfoldr.org/cofacts/https%253A%252F%252Fhackmd.io%252Fs%252FrJQaJ9wwl

MrOrz commented

Segment 可以塞進 ReplyConnection

MrOrz commented

cofacts/rumors-api#36 說 replyConnection 應該要加個 delete flag (status flag)

MrOrz commented

DB Migration Process

Ref: https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-snapshots.html

1. Construct snapshots from legacy DB

  1. Load DB from production site to local machine and spin it up inside elasticsearch:5.6.4 docker container
  2. Add a snapshot repo for the legacy ES:
PUT /_snapshot/my_backup
{
  "type": "fs",
  "settings": {
    "location": "/usr/share/elasticsearch/data/bkup"
  }
}
  1. Do backup
PUT /_snapshot/my_backup/snapshot_1?wait_for_completion=true

Upon complete, the DB backup should be within the bkup folder, along with elasticsearch's nodes directory.

We can then turn of the legacy ES server. It has no use now.

2. Restore the snapshot in new DB with renamed indexes

  1. Spin up new DB with the new mapping
# In rumors-db project directory
$ docker-compose up

# In another shell
$ npm run schema
  1. Move bkup alongside with the new elasticsearch's nodes directory
# In rumors-db project directory
$ cp -R <SOURCE> esdata/
  1. Run recover migration script:
$ node_modules/.bin/babel-node db/migrations/201712-000-restore-backups.js

3. run migration script to re-index from legacy indexes to the new indexes

$ node_modules/.bin/babel-node db/migrations/201712-001-reindex.js

3. Remove legacy indexes

DELETE *_legacy