|
|
| 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.
PostgreSQL as default relation database and including Common/RMM /SCADA/IMM/etc. MongoDB/DoucmentDB for sensor information collect.
