Chapter 7. Building the Critical Components

NOTE: this notes are from “Build Your Own IoT Platform” by Anand Tamboli. And I can’t speak english very well, so some sentences or word might be inappropriate and might have some misunderstandings.

Creating DB

• Before creating DB, you have to access PHPMyAdmin with root account.

Database

1. Tap Databases
2. Set Database name to “tSeriesDB” with “Collaton
3. Press Create

User

1. In that database, press Privileges
2. Goto Add user account
3. Fill out login information (Generate password is optionary)
4. Check all Global privileges
5. Press Go btn.

Table

1. PressCreate table
2. Set name as “thingsData” and Number of data as 5
3. Set columns like:

Copycopy code to clipboard
1NAME        TYPE    LEN     DEFAULT     INDEX           A_I
2ID      INT     11      NONE        PRIMARY_KEY     ENABLE
3TOPIC       VARCHAR 1024    NONE
4PAYLOAD VARCHAR 2028    NONE
5TIMESTAMP   VARCHAR 15      NONE
6DELETED BINARY  1       AS DEFINED:0

4. And save

Installing MySQL Plugin for NodeRED

1. Run NodeRED
2. Goto Manage Palette in NodeRED menu
3. Goto Install Menu -> Install node-red-node-MySQL

Make “Publish Periodic Timestamp” Functionality

• This functionality is corresponded with M1 API.
• inject node: Make sure that msg.payload be “timestamp” and set repeatto interval with every 15 seconds.
• debug node: There’s no required configuration.
• MQTT out node:
  • Server:
    • Connection:
      • Server: localhost with port 1883
      • Protocol: MQTT V3.1.1
      • Client ID: node-red
      • Keep Alive: 60
    • Security: Enter your MQTT username and password.
  • Topic: timestamp

Make “Publish by ReST/POST” API

• This functionality is to publish some topic:payload with HTTP/ReST. This is neccesary because some circumstances, you can’t use MQTT to publish some data. And this is also helpful when you want to test the platform working as expected.
• HTTP in node: Set method to POST and URL to /pub/:topic/:payload
• function node:
  • Remember that msq.req stores all the parsed data from HTTP request
  • So, to use HTTP request parameters, you have to use msg.req.params
  • And also, return value for function node is msg. Thus, you have to store return value to msg.

Copycopy code to clipboard
1msg.topic = msg.req.params.topic.toString();
2msg.payload = msg.req.params.payload.toString();
3msg.qos = 2;
4msg.retain = false;
5
6return msg;

• MQTT out node: Basic server configurations are already setted. So, just fill out the topic to “mqtt”.
• Another function node:

Copycopy code to clipboard
1msg.payload = {
2    success: true,
3    message:"published: " +
4            msg.req.params.topic + 
5            "/" + 
6            msg.req.params.payload
7};
8
9return msg;

• HTTP response node: No configurations are neccesary.

Make “Subscribe & DB/INSERT Everything” Functionality

• This functionality is to store all the published data to the DB.
• MQTT in node: Set topic to # to subscribe all kind of topic and set QoS to 2 to filtering important data.
• function node: This node makes SQL query for DB.

Copycopy code to clipboard
1// Create query
2// get microtime
3var timestamp = new Date().getTime()/1000;
4// pad it with trailing zeroes
5timestamp = timestamp.toString() + "000";
6// trim to exact length 10 + 1 + 3
7timestamp = timestamp.substring(0, 14);
8
9var strQuery =  "INSERT INTO thingsData (topic, payload, timestamp, deleted)" +
10                "VALUES ('" + escape(msg.topic) + "','" + escape(msg.payload) + "','" + timestamp + "', 0);";
11msg.topic = strQuery;
12
13return msg;

• mysql node: Set database property to:
  • Host: 127.0.0.1
  • Port: 3306
  • User: user that can access to DB you made.
  • Password: password of that user.
  • Database: Database you made for this platform.
• debug node: No configurations required.

Make “REST/GET one or more records from DB” Functionality

• This functionality corresponses to D1 and D2 API.
• HTTP in node: Set method to GET and set URL to /record/:topic
• Another HTTP in node: Set method to GET and set URL to /records/:topic/last/:count
• function node:

Copycopy code to clipboard
1// Create query
2
3// if required record count is not specified
4// set default to 1
5if(!msg.req.params.count) {
6    msg.req.params.count = 1;
7}
8
9// build the sql query
10msg.topic =
11    "SELECT id,topic,payload,timestamp " +
12    "FROM thingsData " +
13    "WHERE topic='" + escape(msg.req.params.topic) + "' " +
14    "AND deleted=0 " +
15    "ORDER BY id DESC " +
16    "LIMIT " + msg.req.params.count + ";";
17
18return msg;

• mysql, function, http response node: No configuration required.

Auto-restart NodeRED on failure

• It can be accomplished with forever package. It’s available in NPM.

Copycopy code to clipboard
1npm install -g forever

• After installing, you can start NodeRED by:

Copycopy code to clipboard
1forever start -l node-red.log --append /usr/local/bin/node-red