# 資料庫設計

## 主系統與資料庫對接

### Flag

**FlagTable**
- 當前的flag

|TeamID|MachineID|flag|Round|
|--|--|-|-|
|int|int|char(32)|int|

**FlagRecord**
- 紀錄過去的flag

|no|TeamID|MachineID|flag|Round|
|-|-|-|-|-|
|int|int|int|char(32)|int|

*FlagSubmitRecord*
|TeamID|flag|Round|ReportUrl|
|--|--|--|--|
|int|char(32)|int|string|

Round 為計分網頁查詢FlagTable中當前的Round所寫入此表

### DefenceScore

**PatchRecord**
|TeamID|MachineID|ServiceID|isPatched|
|--|--|--|--|
|int|int|int|bool|

### Report

- 要能識別提交組別、目標組別、目標機器、Round

*AttackReportSubmitRecord*

|AtkTeamID|TgtMachineID|ReportUrl|approval|Round|submittime|
|--|--|--|--|--|--|

*DefenceReportSubmitRecord*

|Num|TeamID|machineID|ServiceID|ReportUri|approval|submit_Round|pass_Round|comments|
|--|--|--|--|--|--|--|--|--|
|int|int|int|varchar|int|int|int|int|nvarchar(MAX)|

approval:
0:未驗 gold
1:審核中 blue
2:通過 green
3:未通過 red

### ScoreCalculation

**ScoreRecord**
|no|TeamID|AtkScore|DefScore|Round|
|--|--|--|--|--|
|int|int|int|int|int|

**ScoreTotal**
|TeamID|AtkScore|DefScore|
|--|--|--|
|int|int|int|

### **Log**

每組為一個資料表

- TableName:Team[TeamID]_Log
- 資料表結構:

|no|MachineID|Event|Round|Turn|
|-|-|-|-|-|
|int|int|varchar|int|int|

Event定義

EventType-serviceID-result

:::success
EventType

**0**:flag lost
- serviceID:0
- result:
- 0:flagcheckloss
- 1:flagchecknormal
- 2:flagupdatesuccess
- 3:flagupdtefail

**1**:ServiceCheck:
- result:
- 0:normalcheckfail
- 1:normalchecksuccess
- 2:patchchecksuccess
- 3:patchcheckfail

**2**:VpnSpy:

:::

utf-8 \n