20160121_jeffrey - silenceuncrio/diary GitHub Wiki

Index

• 1130 - Using SQLite - study
• 1305 - Define tables
• 1325 - SQLite tutorial
• 1730 - IoT 週會

1130

臨時抱佛腳還算有用
還好抱的是 Using SQLite - Chapter 6 Database Design

1305

先拿 Boxes 這 table 來開刀

Table - Boxes

Column	Data Type	Length	Indexed	Required(Default)
(PK)BoxId	Number	NA	Yes, Primary Key	Yes
BoxName	Character	16	No	Yes
BoxLocation	Character	32	No	Yes
BoxConfig_	Character	32	No	Yes

照著書走的話 table definition 應該是這樣

CREATE TABLE Boxes (
    BoxId         INTEGER   PRIMARY KEY   NOT NULL,
    BoxLocation   TEXT   NOT NULL,
    BoxConfig_    TEXT   NOT NULL
);

不用管長度真好

再來是 Things 這個 table

Table - Things

Column	Data Type	Length	Indexed	Required(Default)
(PK)ThingId	Number	NA	Yes, Primary Key	Yes
ThingType	Number	NA	No	Yes
ThingStatus_	Character	32	No	No
(FK)BoxId	Number	NA	Yes, Foreign Key	Yes

注意這個 table 有個 foreign key 是 BoxId 參考自 Boxes 這個 table

CREATE TABLE Things (
    ThingId        INTEGER   PRIMARY KEY,
    ThingType      INTEGER   NOT NULL,
    ThingStatus_   TEXT   NOT NULL,
    BoxId          INTEGER   NOT NULL   REFERENCES Boxes( BoxId )
);

趁勝追擊

Table - OpenDetectors

Column	Data Type	Length	Indexed	Required(Default)
(PK)OpenId	Number	NA	Yes, Primary Key	Yes
OpenConfig_	Character	32	No	Yes
OpenStatus_	Character	32	No	Yes
(FK)ThingId	Number	NA	Yes, Foreign Key	Yes

CREATE TABLE OpenDetectors (
    OpenId        INTEGER   PRIMARY KEY,
    OpenConfig_   TEXT   NOT NULL,
    OpenStatus_   TEXT   NOT NULL,
    ThingId       INTEGER   NOT NULL   REFERENCES Things( ThingId )
);

1325

實際玩玩看吧

不過還是先參考 SQLite tutorial 走一遭也不錯

公司的進度應該是交代得過去

1730

剛剛跟總經理開了 IoT Gateway 相關的會議

我可能要先幫忙加速一下 Gateway 管理 O'SMART Plug 的事宜
Jammy 目前在幫忙 IPv6 的事情

我可以先找個容易開發的 BLE Module
有現成的 AT Command 可以讓我叫它做事