Try   HackMD

SQL table

tags: 計畫

SQL 格式內容

  • memory
    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →
  • disk
    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →
4/19 刪除 PLATE TABLE

Regular expression

顏色車型table
車牌

  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}

Event

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);
4/19
  • 資料庫修改:
    • PLATE table 合併:
    • 新增 :
      plateNum NVARCHAR(10) , – 車牌
      carColor INT, – 車色
      carType INT, – 車型
    • 刪除 :
      key : plateNUM
5/3
  • 資料庫修改
    • 資料庫自動產生流水號 AUTOINCREMENT
      • eventID INTEGER PRIMARY KEY AUTOINCREMENT (不行用 INT)
5/6
  • 刪除adress
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);
11/10 UNIQUE
  • 「- -」 表與上表不同之處
  • 用 UNIQUE 把多個 column 包起來，視為唯一
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
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參考資料

  1. cameraID 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. eventID
    改成流水號
  3. address
    不要有限制(提示:不要打特殊符號)
  4. startTime/endTime
    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]
4/19
  • 4/19 修改:
    • 已修改 camera ID REG
    • 已加上 time 的 REG

Camera

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 -- 監視器狀態
);
4/19 刪除 address
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:

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    第四碼表示第幾分局 ex:1->第一分局
    (若沒有則打0)

  7. policeStation:
    依照下表號碼
    南投警局代碼

  8. district
    REG :　[\u4e00-\u9fa5]+

警局機關分局的資訊應該是透過ID傳送
資料庫我們儲存的是警局單位的中文名字

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

old errorcode

資料庫error設計

11/11更改log名字成Errormap

存Errorcode





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

Errorcode map

Log

11/11 新增Log table

存指令錯誤訊息








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

資料庫error設計












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)
);
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;
  • 查詢次數 times 由 Flask 透過寫程式 每搜尋一次 +1，故 刪掉 column
11/11修改
  • 刪掉錯誤碼欄位
  • 把reasonDetail 的欄位從case table挪到Search table
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;
3/29 修改
  • 3/29 修改
    1. 修改 查詢人 ID searchID-> personID
    2. 刪除 column : 查詢人名稱、案由
    3. 新增 TABLE CASE、PERSON
5/3 資料庫修改
  • 5/3 資料庫修改
    • 資料庫自動產生流水號 AUTOINCREMENT
      • searchID INTEGER PRIMARY KEY AUTOINCREMENT (不行用 INT)

PoliceCase

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





CREATE TABLE PoliceCase(
    caseID 	           INTEGER	 PRIMARY KEY, -- 案號
    reason             NVARCHAR(200)   NOT NULL -- 案由  : 選單
);
案由類別
  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 --查詢人名稱
);

修改

4/26 修改
  1. 刪除 PLATE TABLE
  2. PLATE table 合併:
    新增
    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

plateNum NVARCHAR(10) , – 車牌
carColor INT, – 車色
carType INT, – 車型
刪除 :
key : plateNUM
3. 刪除 CAMERA TABLE 中的 addres(欄位)
4. EVENT

  • 已修改 camera ID REG
  • 已加上 time 的 REG
  1. 3/29 修改
    修改 查詢人 ID searchID-> personID
    刪除 column : 查詢人名稱、案由
    新增 TABLE TCASE、PERSON
  2. 已成功匯入資料
    CAMERA : 878
    TCASE : 100 million
    PERSON : 999
    SEARCH : 100 million
    EVENT : 0 (error : disk 已滿)
    有寫一個可以匯入特定筆數程式
  3. error type 已設計完成
  4. 問題 :
    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →
  5. 資料大小
    • EVENT : 14GB
    • CAMERA : 160 KB
    • SEARCH : 4.5 GB
    • CASE : 1.6 GB
    • PERSON : 8.7 KB
5/3 修改
Last changed by 
 
陳嘉璐
8
820
Published on HackMD