|
|
Line 52: |
Line 52: |
| | | |
| === Common Database Table Details === | | === Common Database Table Details === |
− |
| |
| Table Account | | Table Account |
− | | + | [[File:account tbl.png]] |
− | {| border="0" cellspacing="0" cellpadding="0" width="567" style="width:567px;"
| |
− | |-
| |
− | ! colspan="4" style="width:567px;" |
| |
− | Column
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">aid</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | bigserial
| |
− | | |
− | | style="width:266px;" |
| |
− | Primary Key, Account Id
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">rid</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | bigint
| |
− | | |
− | | style="width:266px;" |
| |
− | Reference Key, Role Id
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">name</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | text
| |
− | | |
− | | style="width:266px;" |
| |
− | Unique Key, Account Name
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">fullName</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | text
| |
− | | |
− | | style="width:266px;" |
| |
− | Account Full Name
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">firstName</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | text
| |
− | | |
− | | style="width:266px;" |
| |
− | Account First Name
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" | lastName
| |
− | | style="width:76px;" |
| |
− | text
| |
− | | |
− | | style="width:266px;" |
| |
− | Account Last Name
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">mail</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | text
| |
− | | |
− | | style="width:266px;" |
| |
− | Unique Key, Account E-mail
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">password</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | text
| |
− | | |
− | | style="width:266px;" |
| |
− | Account Password
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">loginTS</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | timestamp
| |
− | | |
− | | style="width:266px;" |
| |
− | Account Last Login Timestamp
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | *
| |
− | | |
− | | style="width:184px;" |
| |
− | <font color="#0782c1">createTS</font>
| |
− | | |
− | | style="width:76px;" |
| |
− | timestamp
| |
− | | |
− | | style="width:266px;" |
| |
− | Account Create Timestamp
| |
− | | |
− | |-
| |
− | | colspan="4" style="width:567px;" |
| |
− | Indexes
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− | Pk
| |
− | | |
− | | style="width:184px;" |
| |
− | PK_ACCOUNT_AID
| |
− | | |
− | | style="width:76px;" |
| |
− | ON aid
| |
− | | |
− | | style="width:266px;" |
| |
− | |-
| |
− | | style="width:41px;" |
| |
− | U
| |
− | | |
− | | style="width:184px;" |
| |
− | UNI_ACCOUNT_MAIL
| |
− | | |
− | | style="width:76px;" |
| |
− | ON mail
| |
− | | |
− | | style="width:266px;" |
| |
− | |-
| |
− | | style="width:41px;" |
| |
− | U
| |
− | | |
− | | style="width:184px;" |
| |
− | UNI_ACCOUNT_NAME
| |
− | | |
− | | style="width:76px;" |
| |
− | ON name
| |
− | | |
− | | style="width:266px;" |
| |
− | |-
| |
− | | colspan="4" style="width:567px;" |
| |
− | Foreign Keys
| |
− | | |
− | |-
| |
− | | style="width:41px;" |
| |
− |
| |
− | | |
− | | style="width:184px;" |
| |
− | FK_ACCOUNT_RID
| |
− | | |
− | | style="width:76px;" |
| |
− | ( rid ) ref role (rid)
| |
− | | |
− | | style="width:266px;" |
| |
− | |}
| |
− | | |
− | | |
| | | |
| Table Role | | Table Role |
− | | + | [[File:role tbl.png]] |
− | {| border="0" cellspacing="0" cellpadding="0" width="504" style="width:504px;"
| |
− | |-
| |
− | ! colspan="4" |
| |
− | <font color="#0782c1">Columns</font>
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">rid</font>
| |
− | | |
− | |
| |
− | bigserial
| |
− | | |
− | |
| |
− | Primary Key, Role Id
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">name</font>
| |
− | | |
− | |
| |
− | text
| |
− | | |
− | |
| |
− | Unique Key, Role Name
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">createTS</font>
| |
− | | |
− | |
| |
− | timestamp
| |
− | | |
− | |
| |
− | Role Create Timestamp
| |
− | | |
− | |-
| |
− | | colspan="4" |
| |
− | Indexes
| |
− | | |
− | |-
| |
− | |
| |
− | Pk
| |
− | | |
− | |
| |
− | PK_ROLE_RID
| |
− | | |
− | |
| |
− | ON rid
| |
− | | |
− | |
| |
− | |-
| |
− | |
| |
− | U
| |
− | | |
− | |
| |
− | UNI_ROLE_NAME
| |
− | | |
− | |
| |
− | ON name
| |
− | | |
− | |
| |
− | |}
| |
− | | |
− | | |
| | | |
| Table Group | | Table Group |
− | | + | [[File:group tbl.png]] |
− | {| border="0" cellspacing="0" cellpadding="0" width="605" style="width:605px;"
| |
− | |-
| |
− | ! colspan="4" |
| |
− | Columns
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">gid</font>
| |
− | | |
− | |
| |
− | bigserial
| |
− | | |
− | |
| |
− | Primary Key, Group Id
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">name</font>
| |
− | | |
− | |
| |
− | text
| |
− | | |
− | |
| |
− | Group Name
| |
− | | |
− | |-
| |
− | |
| |
− |
| |
− | | |
− | |
| |
− | <font color="#0782c1">desc</font>
| |
− | | |
− | |
| |
− | text DEFAULT ''::text
| |
− | | |
− | |
| |
− | Group Description
| |
− | | |
− | |-
| |
− | |
| |
− |
| |
− | | |
− | |
| |
− | <font color="#0782c1">did</font>
| |
− | | |
− | |
| |
− | bigint
| |
− | | |
− | |
| |
− | Reference Key, For Device Id
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">aid</font>
| |
− | | |
− | |
| |
− | bigint
| |
− | | |
− | |
| |
− | Reference Key, For Account Id
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">parentGid</font>
| |
− | | |
− | |
| |
− | bigint
| |
− | | |
− | |
| |
− | Parent Group Id (Reserved)
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">createTS</font>
| |
− | | |
− | |
| |
− | timestamp
| |
− | | |
− | |
| |
− | Group Create Timestamp
| |
− | | |
− | |-
| |
− | | colspan="4" |
| |
− | Indexes
| |
− | | |
− | |-
| |
− | |
| |
− | Pk
| |
− | | |
− | |
| |
− | PK_GROUP_GID
| |
− | | |
− | |
| |
− | ON gid
| |
− | | |
− | |
| |
− | |-
| |
− | | colspan="4" |
| |
− | Foreign Keys
| |
− | | |
− | |-
| |
− | |
| |
− |
| |
− | | |
− | |
| |
− | FK_GROUP_AID
| |
− | | |
− | |
| |
− | ( aid ) ref account (aid)
| |
− | | |
− | |
| |
− | |-
| |
− | |
| |
− |
| |
− | | |
− | |
| |
− | FK_GROUP_DID
| |
− | | |
− | |
| |
− | ( did ) ref device (did)
| |
− | | |
− | |
| |
− | |-
| |
− | |
| |
− |
| |
− | | |
− | |
| |
− | FK_GROUP_PARENTGID
| |
− | | |
− | |
| |
− | ( parentGid ) ref group (gid)
| |
− | | |
− | |
| |
− | | |
− | | |
− | |}
| |
− | | |
− | | |
| | | |
| Table Device | | Table Device |
− | | + | [[File:device tbl.png]] |
− | {| border="0" cellspacing="0" cellpadding="0" width="605" style="width:605px;"
| |
− | |-
| |
− | ! colspan="4" |
| |
− | <font color="#0782c1">Columns</font>
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">did</font>
| |
− | | |
− | |
| |
− | bigserial
| |
− | | |
− | |
| |
− | Primary Key, Device Id
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">agentId</font>
| |
− | | |
− | |
| |
− | text
| |
− | | |
− | |
| |
− | AgentId, UUID Format
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">name</font>
| |
− | | |
− | |
| |
− | text
| |
− | | |
− | |
| |
− | Device Name
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">isConnected</font>
| |
− | | |
− | |
| |
− | bool DEFAULT false
| |
− | | |
− | |
| |
− | Device Connect State
| |
− | | |
− | |-
| |
− | |
| |
− |
| |
− | | |
− | |
| |
− | <font color="#0782c1">coordinates</font>
| |
− | | |
− | |
| |
− | text DEFAULT '25.069439,121.582485'::text
| |
− | | |
− | |
| |
− | GPS Coordinates, latitude andlongitude
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">loginTS</font>
| |
− | | |
− | |
| |
− | timestamp
| |
− | | |
− | |
| |
− | Device Last Login Timestamp
| |
− | | |
− | |-
| |
− | |
| |
− | *
| |
− | | |
− | |
| |
− | <font color="#0782c1">createTS</font>
| |
− | | |
− | |
| |
− | timestamp
| |
− | | |
− | |
| |
− | Devie Create Timestamp
| |
− | | |
− | |-
| |
− | | colspan="4" |
| |
− | Indexes
| |
− | | |
− | |-
| |
− | |
| |
− | Pk
| |
− | | |
− | |
| |
− | PK_DEVICE_DID
| |
− | | |
− | |
| |
− | ON did
| |
− | | |
− | |
| |
− | |}
| |
| | | |
| == Document == | | == Document == |
Adopt Azure API Management/Open-source (WSO2) to integrate various SRP services API.
Basic Account/Device/Group/Role operation APIs, including Add/Edit/Delete/Search/Advance Search WISE-PaaS administration information (Product Support, version, capability…etc.)
Including device basic information and sensor data from broker. (Common MQTT Topic) For structure info (device Info) store into PostgreSQL For non-structure (sensor Info) store into MongoDB/DocumentDB/...etc.
Process RMM/IMM/OTA/SCADA self-MQTT topic message and store into self-database (RMM/IMM/OTASCADA DB)
Adopt RabbitMQ as default cluster broker, and plugin MQTT as communication protocol.
Define common MQTT topic handshake message and custom/SRP topic format.
PostgreSQL as default relation database and including Common/RMM /SCADA/IMM/etc. MongoDB/DoucmentDB for sensor information collect.