# SQL table
###### tags: `計畫`
[toc]
## [SQL 格式內容](/zrwkrYnyTvCSG9KTnfz2Xg)
- memory
- disk

:::spoiler 4/19 刪除 PLATE TABLE
:::
### Regular expression
[顏色車型table](https://hackmd.io/MOjhfICcQE2rOtR5z-5V4w)
[車牌](https://ws.thb.gov.tw/001/Upload/OldFile/resource/html/doc/%E7%9B%A3%E7%90%86%E6%A5%AD%E5%8B%99/%E7%89%8C%E7%85%A7/2.%E5%8E%9F%E5%9E%8B%E5%BC%8F%E8%99%9F%E7%89%8C%E8%88%87%E6%96%B0%E5%BC%8F%E8%99%9F%E7%89%8C%E5%90%84%E8%BB%8A%E5%88%A5%E7%A4%BA%E6%84%8F%E5%9C%96.pdf)
1. platenum
REG
`AAA-8888` : `[A-Z]{3}\-[0-9]{4}`
`AA-88888` :`[A-Z]{2}\-[0-9]{5}`

- 舊式

`AA-88(88)`: `[A-Z0-9]{2}\-[0-9A-Z]{2,4}`
`AAA-888`|`888-AAA`|... :`[A-Z0-9]{3}\-[A-Z0-9]{3}`
`888-AA`| `8888-AA` : `[A-Z0-9]{3,4}\-[A-Z0-9]{2}`

- [reference](https://cynthiachuang.github.io/Taiwan-License-Plate-Rules-for-LPR/)
- 短編碼英數混用
- [plate rule](https://ws.thb.gov.tw/Download.ashx?u=LzAwMS9VcGxvYWQvNDM2L3JlbGZpbGUvMTIyNzEvMzIvZjAyZTIyMzktNjk3ZC00OGYyLWExOTEtY2QzYTFlNGE0NmRhLnBkZg%3d%3d&n=MS7ljp%2flnovlvI%2fjgIHmlrDlvI%2fjgIHjgIzkuIDou4rkuIDomZ%2fjgI3mlrDnt6jnorzmlrnlvI%2fomZ%2fniYzljYDliIblsI3nhafooagucGRm&icon=.pdf)

## Event
::: spoiler old version

CREATE TABLE EVENT (
    eventID	 INTEGER PRIMARY KEY AUTOINCREMENT, -- 事件編號
    plateNum    NVARCHAR(10) NOT NULL, -- 車牌
    carColor	INT,  -- 車色
    carType	INT,    -- 車型
    cameraID    NVARCHAR(40) NOT NULL, -- 監視器編號
    startTime 	DATETIME  NOT NULL, -- 起
    endTime     DATETIME  NOT NULL,  -- 末
    getFileURI  NVARCHAR(250)         NOT NULL, -- 影片連結
    getSnapshotURI NVARCHAR(250)      NOT NULL, -- 快照連結
    FOREIGN KEY(cameraID) REFERENCES CAMERA(cameraID)
);
CREATE INDEX plateNumIndex ON EVENT (plateNum);
CREATE INDEX startTimeIndex ON EVENT (startTime);

::: spoiler 4/19
* 資料庫修改:
* PLATE table 合併:
* 新增 :
plateNum NVARCHAR(10) , -- 車牌
carColor INT, -- 車色
carType INT, -- 車型
* 刪除 :
key : plateNUM
:::
::: spoiler 5/3
* 資料庫修改
* 資料庫自動產生流水號 `AUTOINCREMENT`
* eventID INTEGER PRIMARY KEY AUTOINCREMENT (不行用 INT)
:::
::: spoiler 5/6
* 刪除adress
:::
::: spoiler 6/30 新增 INDEX(plateNum、startTime)
* 將 plateNum 建立 index 表 (搜尋條件無須修改，此index可提升搜尋速度)
* 查看現有的 index 表 : `.indexes `
* `CREATE INDEX plateNumIndex ON EVENT (plateNum);`
* 測試結果 「時間」 : 搜尋 plateNum LIKE 'A%'
* 無 INDEX -> `Run Time: real 14.708 user 0.707784 sys 1.030433`
* 有 INDEX -> `Run Time: real 0.395 user 0.366702 sys 0.027766`
* `CREATE INDEX startTimeIndex ON EVENT (startTime);`
:::spoiler 11/10 UNIQUE
* 「- -」 表與上表不同之處
* 用 UNIQUE 把多個 column 包起來，視為唯一
:::
:::spoiler 12/13
Add insertTime to record the creating time of every single capture.
:::

CREATE TABLE Event (
    eventID	 INTEGER PRIMARY KEY AUTOINCREMENT,  -- 11/10
    plateNum    NVARCHAR(10) NOT NULL, 
    carColor	INT, 
    carType	INT, 
    cameraID    NVARCHAR(40) NOT NULL, 
    startTime 	DATETIME  NOT NULL, 
    endTime     DATETIME  NOT NULL, 
    getFileURI  NVARCHAR(250)     NOT NULL, 
    getSnapshotURI NVARCHAR(250)  NOT NULL, 
    insertTime   DATETIME  NOT NULL,        -- 12/13
    UNIQUE(plateNum, startTime, cameraID),  -- 11/10
    FOREIGN KEY(cameraID) REFERENCES CAMERA(cameraID)
);
CREATE INDEX plateNumIndex ON EVENT (plateNum);
CREATE INDEX startTimeIndex ON EVENT (startTime);
PRAGMA foreign_keys=on; -- 11/10

:::spoiler 11/10 UNIQUE
* 為什麼不用 primary key 綁 plateNum + startTime + cameraID 確保唯一性呢?
1. 沒有這種寫法 "[name] INTEGER AUTOINCREMENT"
* 應為 "[name] INTEGER **PRIMARY KEY** AUTOINCREMENT"
* 除非用 ALTER，否則 PRIMARY KEY 產生應寫在同一個地方
2. 那如果寫 "PRIMARY KEY(eventID, plateNum, startTime, cameraID)"?
* "PRIMARY KEY" 經常會用來和 "FOREIGN KEY" 搭配使用，也就是說當另一個 TABLE 要 FOREIGN 本 TABLE 時，需要一次 FOREIGN 本 TABLE 所有 PRIMARY KEY。
3. 建議可以用 "UNIQUE" 即可，如此一來 eventID 依然可以維持自動產生流水號
* "PRAGMA foreign_keys=on" ，建議每個有用到 foreign key 的地方都加上這行。
:::
### Regular expression
**cameraID** (ex, 63000_10901_00_123456_03)
縣市編號 : 5 碼，參考內政部戶政司公告之縣市代碼
建置年份 : 3 碼，民國年
當年建置期數： 2 碼，不足碼補 0。
監視器樣態： 2 碼，==全景監視器 00、車道監視器 01、車道車辨監視器 02==
監視器流水號： 6 碼，不足碼補 0。
監視器影像儲存處： 2 碼，==路口機箱00、派出所 01、分局 02、警察局 03、租賃機房 04==
[Camera ID參考資料](https://www.dgbas.gov.tw/public/data/dgbas03/bs1/%E8%A1%8C%E6%94%BF%E5%8D%80%E5%9F%9F%E5%8F%8A%E6%9D%91%E9%87%8C%E4%BB%A3%E7%A2%BC/%E5%90%84%E7%B8%A3%E5%B8%82%E5%8F%8A%E7%9B%B4%E8%BD%84%E5%B8%82/%E6%9D%91%E9%87%8C-all.pdf)
1. <font color='red'>cameraID</font> `63000_10901_00_123456_03`
REG : `[0-9]{5}\_[0-9]{3}[0-9]{2}\_0[0-2]\_[0-9]{6}\_0[0-4]`
2. <font color='red'>eventID</font>
==改成流水號==
3. <font color='red'>address</font>
==不要有限制(提示:不要打特殊符號)==
4. <font color='red'>startTime/endTime</font>
ex:`2023-03-15 10:10:10`
REG : `[0-9]{4}\-[01][0-9]\-[0-3][0-9]\ [0-2][0-9]\:[0-5][0-9]\:[0-5][0-9]`
:::spoiler 4/19
* 4/19 修改:
* 已修改 camera ID REG
* 已加上 time 的 REG
:::

## Camera
:::spoiler old camera table

CREATE TABLE CAMERA (
    cameraID      NVARCHAR(100)   PRIMARY KEY , -- 監視器編號
    cameraName    NVARCHAR(20), -- 監視器名稱
    
    district          NVARCHAR(50),  -- 行政區
    precinct          NVARCHAR(100)  NOT NULL, -- 管理該監視器的分局
    policeStation     NVARCHAR(100)  NOT NULL, -- 管理該監視器的派出所
    intersection      NVARCHAR(100)  NOT NULL, -- 管理該監視器的路口
    precinct_store  NVARCHAR(5)  NOT NULL,       --for computer
    policeStation_store  NVARCHAR(5)  NOT NULL,  --for computer
    intersection_store  NVARCHAR(5)  NOT NULL,   --for computer
    
    latitude      REAL  NOT NULL, 
    longitude     REAL  NOT NULL,
    azimuthAngle  INT CHECK( azimuthAngle>=0 AND  azimuthAngle<=360), -- 方位角,
    status 	   INT CHECK(status>=0 AND status<=2)  NOT NULL -- 監視器狀態
);

:::spoiler 4/19 刪除 address
:::
:::spoiler 5/6
* 5/6
* event table 的 address 移到 camera table
* 把中文的分局路口派出所三個欄位刪掉
:::

CREATE TABLE Camera (
    cameraID      NVARCHAR(40)   PRIMARY KEY , -- 監視器編號
    cameraName    NVARCHAR(20), -- 監視器名稱
    intersection     NVARCHAR(200)  NOT NULL,   --路口
    policeStationID  INTEGER  NOT NULL,  --for computer
    latitude      REAL, 
    longitude     REAL,
    azimuthAngle  INT CHECK( azimuthAngle>=0 AND  azimuthAngle<=360), -- 方位角,
    status 	   INT CHECK(status>=0 AND status<=2)  NOT NULL, -- 監視器狀態
    FOREIGN KEY(policeStationID) REFERENCES POLICESTATION(policeStationID)
);
PRAGMA foreign_keys=on;

//0805改為一個table，且舊路口位址存在address
## Policestation

CREATE TABLE Policestation (
    policeStationID  INTEGER  PRIMARY KEY AUTOINCREMENT,  --for computer(流水號)
    policeStation     NVARCHAR(100)  UNIQUE  NOT NULL, -- 管理該監視器的派出所
    precinct          NVARCHAR(100)  NOT NULL, -- 管理該監視器的分局
    district          NVARCHAR(50)  -- 行政區    
);

### Regular expression
1. cameraName : `Camera123456`
REG : `Camera[0-9]{6}`
2. precinct
REG :　`[\u4e00-\u9fa5]+分局`
3. policeStation
REG :　`[\u4e00-\u9fa5]+派出所`
4. intersection
REG :　`[\u4e00-\u9fa5]+路`
5. Status ==0:「正常」、1「異常」、2「報修」==

6. precinct:

第四碼表示第幾分局 ex:1->第一分局
(若沒有則打0)
7. policeStation:
依照下表號碼
[南投警局代碼](https://data.nantou.gov.tw/dataset/ncpb-02/resource/ef053b17-cf36-4213-b698-d2104324e7b4)
8. district
REG :　`[\u4e00-\u9fa5]+`
:::info
警局機關分局的資訊應該是透過ID傳送
資料庫我們儲存的是警局單位的中文名字
:::
::: spoiler 11/10 camera_split.sh
* 這是用`awk`將資料分開(逗號)，並且取得所需資訊，針對 `CAMERA.csv`
* 使用者選擇特定行列數將資料做處理
* path : `/home/louise/1109/camera_split.sh`

#!/bin/bash

while true; do
    echo "請問你要分割出哪些列的資料(請用數字，並以空格分隔)"
    read columns
    # 把要分割的內容整理成完整的指令
    awk_command="awk -F ',' '{ print "
    for col in $columns
    do
        awk_command+="$""$col, "
    done
    # 向使用者確認是否是正確格式
    awk_command=${awk_command::-2}" }' CAMERA.csv | head -n 3"
    eval $awk_command

    echo "這是您要的樣式嗎？ (T/F)"
    read answer
    if [ "$answer" == "T" ]; then
        break
    fi
done

# 選擇要輸出的檔案名稱
echo "請問您要將結果寫入哪一份檔案名稱中"
read filename
awk_command="awk -F ',' '{ print "
for col in $columns
do
    awk_command+="$""$col, "
done
# 輸出檔案並把重複的刪除
awk_command=${awk_command::-2}" }' CAMERA.csv | sort | uniq> $filename"
eval $awk_command

echo "程式執行完畢，結果已寫入 $filename"

:::

## ErrorCode
:::spoiler old errorcode
[資料庫error設計](https://hackmd.io/@jennyChi/ryyZR-f12)
:::
::: spoiler 11/11更改log名字成Errormap
存Errorcode
:::

CREATE TABLE ErrorCode(
    errorType    INT,       -- 錯誤類型
    msg          NVARCHAR(200)      -- 錯誤訊息
);

[Errorcode map](https://hackmd.io/BJKPHATlTwObI69mBoirJA)

## Log

:::spoiler 11/11 新增Log table
存指令錯誤訊息
:::

CREATE TABLE Log (
    searchID INTEGER,        
    searchTime DATETIME,        --發生錯誤時間
    sqlCommand TEXT,            --錯誤指令
    FOREIGN KEY(searchID) REFERENCES Search(searchID)
);
PRAGMA foreign_keys=on;

[資料庫error設計](https://hackmd.io/@jennyChi/ryyZR-f12)

## Search

CREATE TABLE Search (
    searchID INTEGER PRIMARY KEY AUTOINCREMENT,        --搜尋ID
    eventID	 INT  NOT NULL, -- 事件編號
    personID           NVARCHAR(50)    NOT NULL, --查詢人 ID
    caseID 	       INTEGER	 NOT NULL, -- 案號
    searchTime 	DATETIME        NOT NULL, -- 時間
    reasonDetail       NVARCHAR(1000)   NOT NULL, -- 案由 : 用填寫
    query        JSON    NOT NULL,                -- 0704
    FOREIGN KEY(personID) REFERENCES PERSON(personID)
    FOREIGN KEY(caseID) REFERENCES PoliceCase(caseID)
);

:::spoiler old

CREATE TABLE Search (
    --searchID           NVARCHAR(40)   NOT NULL, -- 每次搜尋產生的ID
    searchID INTEGER PRIMARY KEY AUTOINCREMENT,
    --eventID	        NVARCHAR(40)   NOT NULL, -- 事件編號
    eventID	 INT  NOT NULL, -- 事件編號
    personID           NVARCHAR(50)    NOT NULL, --查詢人 ID
    caseID 	        NVARCHAR(30)	 NOT NULL, -- 案號
    searchTime 	DATETIME        NOT NULL, -- 時間
    errorType    INT,                        --錯誤類型
    FOREIGN KEY(eventID) REFERENCES EVENT(eventID)
    FOREIGN KEY(personID) REFERENCES PERSON(personID)
    FOREIGN KEY(caseID) REFERENCES TCASE(caseID)
    FOREIGN KEY(errorType) REFERENCES LOG(errorType)
);
PRAGMA foreign_keys=on;

:::info
* 查詢次數 times 由 Flask 透過寫程式 每搜尋一次 +1，故 刪掉 column
:::
:::spoiler 11/11修改
* 刪掉錯誤碼欄位
* 把reasonDetail 的欄位從case table挪到Search table
:::
:::spoiler Sqlite before july

CREATE TABLE Search (
    searchID INTEGER PRIMARY KEY AUTOINCREMENT,        --搜尋ID
    eventID	 INT  NOT NULL, -- 事件編號
    personID           NVARCHAR(50)    NOT NULL, --查詢人 ID
    caseID 	       INTEGER	 NOT NULL, -- 案號
    searchTime 	DATETIME        NOT NULL, -- 時間
    reasonDetail       NVARCHAR(1000)   NOT NULL, -- 案由 : 用填寫
    FOREIGN KEY(eventID) REFERENCES EVENT(eventID)
    FOREIGN KEY(personID) REFERENCES PERSON(personID)
    FOREIGN KEY(caseID) REFERENCES PoliceCase(caseID)
);
PRAGMA foreign_keys=on;

:::

:::spoiler 3/29 修改
* 3/29 修改
1. 修改 查詢人 ID searchID-> personID
2. 刪除 column : 查詢人名稱、案由
3. 新增 TABLE CASE、PERSON
:::
:::spoiler 5/3 資料庫修改
* 5/3 資料庫修改
* 資料庫自動產生流水號 `AUTOINCREMENT`
* searchID INTEGER PRIMARY KEY AUTOINCREMENT (不行用 INT)
:::

## PoliceCase

:::spoiler 11/11修改
* 將名稱改為Table Case
* 刪除Detail欄位(移至Search table)
:::

CREATE TABLE PoliceCase(
    caseID 	           INTEGER	 PRIMARY KEY, -- 案號
    reason             NVARCHAR(200)   NOT NULL -- 案由  : 選單
);

:::spoiler 案由類別
1. 殺人
2. 強盜搶奪
3. 傷害
4. 強制性交
5. 公共危險
6. 毒品
7. 一般竊盜
8. 車禍
9. 廢土廢物
10. 詐欺
11. 查捕逃犯
12. test
13. 其他
:::

* 5/3 caseID personID =>REG?

## PERSON

CREATE TABLE Person(
    personID           NVARCHAR(50)    PRIMARY KEY, --查詢人 ID
    name	        NVARCHAR(30)    NOT NULL --查詢人名稱
);

## 修改
:::spoiler 4/26 修改
1. 刪除 PLATE TABLE
2. PLATE table 合併:
新增 :abc:

plateNum NVARCHAR(10) , – 車牌
carColor INT, – 車色
carType INT, – 車型
刪除 :
key : plateNUM
3. 刪除 CAMERA TABLE 中的 addres(欄位)
4. EVENT
* 已修改 camera ID REG
* 已加上 time 的 REG
5. 3/29 修改
修改 查詢人 ID searchID-> personID
刪除 column : 查詢人名稱、案由
新增 TABLE TCASE、PERSON
5. 已成功匯入資料
CAMERA : 878
TCASE : 100 million
PERSON : 999
SEARCH : 100 million
EVENT : 0 (error : disk 已滿)
有寫一個可以匯入特定筆數程式
6. error type 已設計完成
7. 問題 : 
8. 資料大小
* EVENT : 14GB
* CAMERA : 160 KB
* SEARCH : 4.5 GB
* CASE : 1.6 GB
* PERSON : 8.7 KB
:::
:::spoiler 5/3 修改
:::