Skip to content
/ ITpings Public

The Things Network -TTN : HTTP Integration ► PHP ► your MySQL database

11 stars 7 forks Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

Danny-Engelman/ITpings

BranchesTags

Repository files navigation

I now longer maintain this Repo

ITpings : Just Give Me The Data

For beginner and advanced developers:

  • ITpings Is the PHP HTTP Integration from The Things Network to your own MySQL database
  • ITpings Creates the (normalized) MySQL Database Schema for you
  • ITpings Is the (simple) Dashboard API for your Web Front-end (written in ES6, so Chrome only for now)
  • ITpings has no dependencies on any Framework

Difference between MQTT and the HTTP Intergration

MQTT (publish/subscribe) was developed for low bandwidth, low power communication between devices. The MQTT Broker pushes data to the client on an (always) open TCP connection

► MQTT requires authentication PER device

See: How to get started with MQTT

With an HTTP (request/response) Integration the (TTN) Broker pushes all data to a Webhook/Endpoint.

► No authentication required

MQTT Front-End

A Front-End build using MQTT gives immediate responses (but again requires authentication for every device)

ITpings uses traditional short-polling, so has some latency. (see more below)
It does not require any authentication for devices, so any device/application configured to use the HTTP Integration can send data.

Use ITpings HTTP Integration and Dashboard in (under) 5 minutes

Read this README, it will give you more insight in technology choices.

Want to get started asap?!? Install and Configure ITpings in 5 steps:

If you want screenshots, see the 5 steps in detail

  1. Create a 'ITpings' Database in your MySQL server

  2. Edit the file ITpings_access_database.php

    • update MySQL Account & Password
    • update your Private Key (so others can not abuse your HTTP Integration)

  3. Upload all ITpings_*.* files to your WebServer

  4. Create a HTTP Integration in the The Things Network Application console
    pointing to the YOURWEBSERVER/ITpings_connector.php?key=YOURKEY
    Yes! That is it!
    ITpings will create the Database Schema once it receives the first (HTTP Integration) Ping

  5. Now open ITpings_dashboard.html on your WebServer

Version History

  • Version 2 - may 29, 2018
    • Back End:
      • split PHP code in seperate files
      • sensor processing in (fairly) readable triggers.php file
      • added SQL Table: ITpings__data_accelerometer
    • Front End:
      • Refactored Table display
      • Refactored Graph display
  • Version 1 - april 2018
    • POC W3C Custom Elements for Table and Graph
  • Version 0 - February 2018
    • POC HTTP Integration PHP to MySQL Database Schema

Why ITpings was created

I have been a script kiddie since learning BASIC in the late 70s, hardware just is not my thing

-- Danny Engelman

My (TTN) The Things Network Gateway and 2 handfull of TTN Nodes arrived late december 2017.
I am not a Hardware Guy, UART is nothing but a 4 letter acronym to me, so I spent the Holidays playing with all Software related technologies.

myDevices, ThingsBoard, Amazon Web Services, IBM's Node-Red and several more are by themselves great technologies to get started.
(and I do encourage you to at least get acquainted with them)

But neither met my growing list of requirements (or took too long to build):

  • plug & play in any new (pilot project) environment, without registering any Node info
  • Track all my Nodes (=Sensors) in one View
  • In a generic dataformat, ready for any future application (or conversion to AWS, Cayenne or other SAAS providers)
  • Get (almost) live updates of new information
  • Issue an alarm when a device was not seen for over an hour

From a simple SPA page it evolved into a labour of love,
especially since I develop by the motto: Use only necessary dependencies

So I ditched Bootstrap, React and Angular, considered Vue, and eventually went with Native ES6 WebComponents
Hey! I am in charge now!
I spent the last 12 years in the Microsoft Front-End world.. feels good to break free :-)
Using only CSS4, HTML5 and ES6, so my Dashboard does not work in crappy old-fashioned browsers.

For simplicity sake I didn't go fancy with NodeJS, socket.io and loads more dependencies;

ITpings is just PHP, SQL and HTML5 (ES6) So runs on any xxMP stack; That is about 83% of ALL websites out there.

I hereby donate her with a MIT License to the Open Source community, ... be gentle but just with her

ITpings - MIT license

Copyright 2018 Danny Engelman

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

► The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software ◄

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

ITpings Front-End Dashboard with Live data

The MySQL Database Schema is the major part of this application.

I have included a simple Dashboard so something is shown the moment you install this application.

To make this work on any LAMP (or xxMP) stack, the Dashboard uses oldskool AJAX short-polling for (almost live) updates.

Instead of using MQTT (requires registration of every device) ITpings Dashboard polls the MySQL database every 500ms (return value is a single ping id, so including Headers is only 257 Bytes of traffic)
Only when the database has new values the IT-pings Table and Chart (HTML5 WebComponents/Custom Elements) are updated

For your own more complex Dashboard I suggest looking into MQTT and WebSockets

The Dashboard was built without any Frameworks
Using only native CSS4 HTML5 and ES6;
So (for now) runs in Chrome only (other Browsers do not support new W3C standards yet)

Working with JSON data

Note: Use Chrome, and install a decent JSON Viewer extension

ITpings uses a custom API at /ITpings_connector.php?query=[TABLE/VIEW NAME]

Returning JSON data:

Tracing the HTML5 Dashboard

Open the F12 Dev Console to see what the Dashboard does:



ITpings : Under the hood

(My)SQL Database schema

The TTN HTTP Intergration sends a JSON formatted POST request about Applications, Devices, Gateways and Sensor data to the PHP script.
This is Relational Data; so a SQL database seems a logical choice. Fine for a couple of Megabytes a day.

I considered NoSQL solutions (have used several in projects)
But in the end the hardest part with SQL is creating the Schema... which ITpings does for you

For tracking thousands of devices, all generating Gigabytes a day, you really want to use another database type: When do I need a Time Series database

List of Time Series databases

All these Tables start with lowercase letters, but you will hardly use Table references, as ITpings creates Views (with Capitals):

Forget the Tables, Learn to live with Views

Just to scare you with the work that has been done; you do NOT have to learn and execute SQL scripts like:

SensorValues VIEW:

SELECT P._pingid AS _pingid
	,P.created AS created
	,S._sensorid AS _sensorid
	,AD._appdevid AS _appdevid
	,AD.app_id AS app_id
	,AD.dev_id AS dev_id
	,S.sensorname AS sensorname
	,SV.sensorvalue AS sensorvalue 
FROM ITpings__sensorvalues SV 
	JOIN ITpings__sensors S ON S._sensorid = SV._sensorid
	JOIN ITpings__pings P ON P._pingid = SV._pingid
	JOIN ITpings__ApplicationDevices AD ON AD._appdevid = S._appdevid 
ORDER BY P.created desc,SV._sensorid

This SQL script is one (of many) predefined Views, available as: SensorValues

Outputting data as:

by ITpings predefined Views:

  • Devices
  • ApplicationDevices
  • PingedGateways
  • SensorValues
  • Gateways
  • Events

And you can add your own in the ITpings_connector.php PHP script.

Retrieving data from the MySQL database

  • The ITpings HTTP integration Creates data in your MySQL database
  • The ITpings Dashboard Reads data

So ITpings does not require a RESTfull (CRUD) interface.
If you really want one for your custom application, I suggest RESTer

The Client contacts the Server every second to check if there are new (higher) ID values in the Database Tables.
This "Short Polling" is much easier to implement than WebSockets (which is better for multiple Clients,
but for this application it is only you and your Dashboard)

Query URI Examples

filter lt, le, eq, ge, gt

  • URI: /ITpings_connector.php?query=SensorValues&filter=sensorname eq 'temperature_5'
    SQL: SELECT * FROM SensorValues WHERE sensorname='temperature_5'

  • URI: /ITpings_connector.php?query=SensorValues&filter=sensorname eq 'luminosity_6',sensorvalue gt 700
    SQL: SELECT * FROM SensorValues WHERE sensorname='luminosity_6' AND sensorvalue > 700

  • URI: /ITpings_connector.php?query=SensorValues&_sensorid=6,7
    SQL: SELECT * FROM SensorValues WHERE _sensorid IN(6,7)

Paramaterized Queries

To prevent SQL Injection the Database must not accept bare SQL requests.

Note: ITpings is a basic API and very.. very.. strict when processing Query String parameters.

If ITpings can't execute your (complex) query
you can dive into the ITpings_connector.PHP source-code at function: process_Query_with_QueryString_Parameters

Display Tables and Graphs using HTML Custom Elements

The Dashboard is built using ES6 and W3C standard Custom Elements (supported in Chrome, even FireFox is not up to par yet)
Meant to be (my) a Developer Dashboard I didn't use Polymer to make it work in other browsers.

Custom Elements (a subset of Web Components)

Make it possible te create new HTML elements, encapsulating all the logic

HTML:

<itpings-table query="SensorValues"></itpings-table>

Displays the whole Table (and keeps it up to date):

Applying CSS

ITpings adds HTML content with loads of data-attributes:

This makes it possible to use just CSS for extra layout

In the Table screenshot above Columns and Rows are hidden based on data-attributes:

<style>
    /* Hide Columns */
    itpings-table[query='SensorValues'] [data-column$='sensorid'],
    itpings-table[query='SensorValues'] [data-column$='appdevid'],
    itpings-table[query='SensorValues'] [data-column='app_id'],
	itpings-table[query='SensorValues'] [data-column='hardware_serial'] {
	    display: none;
	}

    /* Hide TR rows*/
    itpings-table[query='SensorValues'] [data-sensorname^='accelerometer'] {
        display: none;
    }
</style>

More, see: 30 CSS Selectors you must memorize

# Misc

MySQL Database management alternatives

These tools can be helpfull but are NOT required; ITpings does all the work for you

PHPMyAdmin is the default tool for MySQL on the LAMP/WAMP stack.

If you can access your MySQL server remotely (you may have to ask your ISP to open the (default) 3306 port)
Oracle's MySQL WorkBench (GPL license) or Toad Edge ($$$) can be installed on your local machine.

RESTer (or a fork) (MIT license) Not only adds a RESTfull API (remember: is NOT required to use ITpings), but also provides a fast and good enough Admin interface for managing your MySQL database. (it includes an older version of Adminer)

Alternative technologies:

Further reading

Build an ITpings Fork

No complex Grunt like builts.

Edit the files, upload them and your ready to go.

If you do want to minify code, use uglify-es (for ES6 code):

Uglify-ES options

(if not defined yet) add ES6 CustomElements to ../tools/domprops.json file:

"customElements", "define", "observedAttributes", "attributeChangedCallback", "connectedCallback", "isConnected", "disconnectedCallback", "adoptedCallback"

file: uglify-config.json

	{
	  "compress": {
	    "drop_console": true,
	    "dead_code": true,
	    "unused": true,
	    "passes": 2,
	    "hoist_funs": true,
	    "hoist_props": true,
	    "ecma": 6,
	    "sequences": true,
	    "properties": true,
	    "conditionals": true,
	    "comparisons": true,
	    "booleans": true,
	    "evaluate": true,
	    "loops": true,
	    "if_return": true,
	    "join_vars": true,
	    "collapse_vars": false
	  }
	}

Adding Triggers to ITpings_sensor_triggers.php

Process your own sensor_name and sensor_value here

todo: document

About

The Things Network -TTN : HTTP Integration ► PHP ► your MySQL database

Topics

javascript mysql php ttn custom-elements-v1 ttn-gateway

Resources

Readme
Activity

Stars

11 stars

Watchers

5 watching

Forks

7 forks
Report repository

Releases

No releases published

Packages

No packages published

Contributors 2

  •  
  •  

Languages