Difference between revisions of "WISE-PaaS/SRP Services"

From ESS-WIKI
Jump to: navigation, search
Line 1: Line 1:
 +
== Overview ==
 +
 +
[[File:Wise-paas 2.0.png|RTENOTITLE]]
 +
 +
*API Management
 +
 +
Adopt Azure API Management/Open-source (WSO2) to integrate various SRP services API.
 +
 +
*Common Service APIs/Lib
 +
 +
Basic Account/Device/Group/Role operation APIs, including Add/Edit/Delete/Search/Advance Search WISE-PaaS administration information (Product Support, version, capability…etc.)
 +
 +
*Custom/SRP Service
 +
 +
RMM/IMM/OTA/SCADA self-service APIs
 +
 +
*Common Data Workers
 +
 +
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.
 +
 +
*Custom/SRP Data Workers
 +
 +
Process RMM/IMM/OTA/SCADA self-MQTT topic message and store into self-database (RMM/IMM/OTASCADA DB)
 +
 +
*Message Broker (RabbitMQ)
 +
 +
Adopt RabbitMQ as default cluster broker, and plugin MQTT as communication protocol.
 +
 +
*Device Agent
 +
 +
Define common MQTT topic handshake message and custom/SRP topic format.
 +
 +
*Database (SQL/NoSQL)
 +
 +
PostgreSQL as default relation database and including Common/RMM /SCADA/IMM/etc. MongoDB/DoucmentDB for sensor information collect.
 +
 +
== Common Database Scheme ==
 +
 +
=== Concept & Design Rules ===
 +
 +
*Device belong to multi-groups or not to any group.
 +
*Each account have non or multi-groups.
 +
*Each group must belong to someone account.
 +
*Group of group (1-3 Levels)
 +
*Each account must belong to someone role.
 +
 +
[[File:Group mgmt.png|RTENOTITLE]]
 +
 +
=== Common Database Scheme ===
 +
 +
[[File:Common db scheme.png|RTENOTITLE]]
 +
 +
=== Common Database Table Details ===
 +
 +
Table Account
 +
 +
{| 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;" |
 +
&nbsp;
 +
 +
| style="width:184px;" |
 +
FK_ACCOUNT_RID
 +
 +
| style="width:76px;" |
 +
( rid ) ref role (rid)
 +
 +
| style="width:266px;" |
 +
|}
 +
 +
 +
 +
Table Role
 +
 +
{| 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
 +
 +
{| 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
 +
 +
|-
 +
|
 +
&nbsp;
 +
 +
|
 +
<font color="#0782c1">desc</font>
 +
 +
|
 +
text DEFAULT ''::text
 +
 +
|
 +
Group Description
 +
 +
|-
 +
|
 +
&nbsp;
 +
 +
|
 +
<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
 +
 +
|-
 +
|
 +
&nbsp;
 +
 +
|
 +
FK_GROUP_AID
 +
 +
|
 +
( aid ) ref account (aid)
 +
 +
|
 +
|-
 +
|
 +
&nbsp;
 +
 +
|
 +
FK_GROUP_DID
 +
 +
|
 +
( did ) ref device (did)
 +
 +
|
 +
|-
 +
|
 +
&nbsp;
 +
 +
|
 +
FK_GROUP_PARENTGID
 +
 +
|
 +
( parentGid ) ref group (gid)
 +
 +
|
 +
 +
 +
|}
 +
 +
 +
 +
Table Device
 +
 +
{| 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
 +
 +
|-
 +
|
 +
&nbsp;
 +
 +
|
 +
<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 ==

Revision as of 06:32, 16 January 2017

Overview

RTENOTITLE

  • API Management

Adopt Azure API Management/Open-source (WSO2) to integrate various SRP services API.

  • Common Service APIs/Lib

Basic Account/Device/Group/Role operation APIs, including Add/Edit/Delete/Search/Advance Search WISE-PaaS administration information (Product Support, version, capability…etc.)

  • Custom/SRP Service

RMM/IMM/OTA/SCADA self-service APIs

  • Common Data Workers

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.

  • Custom/SRP Data Workers

Process RMM/IMM/OTA/SCADA self-MQTT topic message and store into self-database (RMM/IMM/OTASCADA DB)

  • Message Broker (RabbitMQ)

Adopt RabbitMQ as default cluster broker, and plugin MQTT as communication protocol.

  • Device Agent

Define common MQTT topic handshake message and custom/SRP topic format.

  • Database (SQL/NoSQL)

PostgreSQL as default relation database and including Common/RMM /SCADA/IMM/etc. MongoDB/DoucmentDB for sensor information collect.

Common Database Scheme

Concept & Design Rules

  • Device belong to multi-groups or not to any group.
  • Each account have non or multi-groups.
  • Each group must belong to someone account.
  • Group of group (1-3 Levels)
  • Each account must belong to someone role.

RTENOTITLE

Common Database Scheme

RTENOTITLE

Common Database Table Details

Table Account

Column

aid

bigserial

Primary Key, Account Id

rid

bigint

Reference Key, Role Id

name

text

Unique Key, Account Name

fullName

text

Account Full Name

firstName

text

Account First Name

lastName

text

Account Last Name

mail

text

Unique Key, Account E-mail

password

text

Account Password

loginTS

timestamp

Account Last Login Timestamp

createTS

timestamp

Account Create Timestamp

Indexes

Pk

PK_ACCOUNT_AID

ON aid

U

UNI_ACCOUNT_MAIL

ON mail

U

UNI_ACCOUNT_NAME

ON name

Foreign Keys

 

FK_ACCOUNT_RID

( rid ) ref role (rid)


Table Role

Columns

rid

bigserial

Primary Key, Role Id

name

text

Unique Key, Role Name

createTS

timestamp

Role Create Timestamp

Indexes

Pk

PK_ROLE_RID

ON rid

U

UNI_ROLE_NAME

ON name


Table Group

Columns

gid

bigserial

Primary Key, Group Id

name

text

Group Name

 

desc

text DEFAULT ::text

Group Description

 

did

bigint

Reference Key, For Device Id

aid

bigint

Reference Key, For Account Id

parentGid

bigint

Parent Group Id (Reserved)

createTS

timestamp

Group Create Timestamp

Indexes

Pk

PK_GROUP_GID

ON gid

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

Columns

did

bigserial

Primary Key, Device Id

agentId

text

AgentId, UUID Format

name

text

Device Name

isConnected

bool DEFAULT false

Device Connect State

 

coordinates

text DEFAULT '25.069439,121.582485'::text

GPS Coordinates, latitude andlongitude

loginTS

timestamp

Device Last Login Timestamp

createTS

timestamp

Devie Create Timestamp

Indexes

Pk

PK_DEVICE_DID

ON did

Document

Retrieved from "https://ess-wiki.advantech.com.tw/wiki/index.php?title=WISE-PaaS/SRP_Services&oldid=5112"