計畫
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}
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);
AUTOINCREMENT
.indexes
CREATE INDEX plateNumIndex ON EVENT (plateNum);
Run Time: real 14.708 user 0.707784 sys 1.030433
Run Time: real 0.395 user 0.366702 sys 0.027766
CREATE INDEX startTimeIndex ON EVENT (startTime);
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
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參考資料
63000_10901_00_123456_03
[0-9]{5}\_[0-9]{3}[0-9]{2}\_0[0-2]\_[0-9]{6}\_0[0-4]
2023-03-15 10:10:10
[0-9]{4}\-[01][0-9]\-[0-3][0-9]\ [0-2][0-9]\:[0-5][0-9]\:[0-5][0-9]
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 -- 監視器狀態
);
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
CREATE TABLE Policestation (
policeStationID INTEGER PRIMARY KEY AUTOINCREMENT, --for computer(流水號)
policeStation NVARCHAR(100) UNIQUE NOT NULL, -- 管理該監視器的派出所
precinct NVARCHAR(100) NOT NULL, -- 管理該監視器的分局
district NVARCHAR(50) -- 行政區
);
cameraName : Camera123456
REG : Camera[0-9]{6}
precinct
REG : [\u4e00-\u9fa5]+分局
policeStation
REG : [\u4e00-\u9fa5]+派出所
intersection
REG : [\u4e00-\u9fa5]+路
Status 0:「正常」、1「異常」、2「報修」
precinct:
第四碼表示第幾分局 ex:1->第一分局
(若沒有則打0)
policeStation:
依照下表號碼
南投警局代碼
district
REG : [\u4e00-\u9fa5]+
警局機關分局的資訊應該是透過ID傳送
資料庫我們儲存的是警局單位的中文名字
awk
將資料分開(逗號),並且取得所需資訊,針對 CAMERA.csv
/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
CREATE TABLE ErrorCode(
errorType INT, -- 錯誤類型
msg NVARCHAR(200) -- 錯誤訊息
);
存指令錯誤訊息
CREATE TABLE Log (
searchID INTEGER,
searchTime DATETIME, --發生錯誤時間
sqlCommand TEXT, --錯誤指令
FOREIGN KEY(searchID) REFERENCES Search(searchID)
);
PRAGMA foreign_keys=on;
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)
);
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;
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;
AUTOINCREMENT
CREATE TABLE PoliceCase(
caseID INTEGER PRIMARY KEY, -- 案號
reason NVARCHAR(200) NOT NULL -- 案由 : 選單
);
CREATE TABLE Person(
personID NVARCHAR(50) PRIMARY KEY, --查詢人 ID
name NVARCHAR(30) NOT NULL --查詢人名稱
);
plateNum NVARCHAR(10) , – 車牌
carColor INT, – 車色
carType INT, – 車型
刪除 :
key : plateNUM
3. 刪除 CAMERA TABLE 中的 addres(欄位)
4. EVENT