Monthly Archives: July 2021

Adding data sources to freeboard is easy!

https://freeboard.io/ is a great tool to create dashboards and data visualization screens quickly and easily! Creators were nice to create is as a service and as an open source software (https://github.com/Freeboard/freeboard).

In order to easily test freeboard graphical components, I decided to create a data source that generates random numbers. It is simple and easy to use:

Source code:

https://github.com/rafaelaroca/freeboard-random-datasource

How to use:

  1. Download and install freeboard (works offline or on a server – simply download the files from freeboard github repository);
  2. Add the freeboardRandomDataSource.js file to js directory
  3. Edit index.html from freeboard main directory and add the following line before “Load more plugins here”:
        "js/freeboardRandomDataSource.js",
                // *** Load more plugins here ***

Done! Now you can add a random datasource, or several random data sources:

Result:

Online working demo (click on the wrench to customize / edit):

https://bipes.net.br/test/freeboard/index.html?load=random_dashboard.json

Congratulations and thanks for the creators of https://freeboard.io/.

BIPES and Web Bluetooth on ESP32 – first working test!

  • Get a generic ESP32. Used this one:
Wemos D1 Mini Módulo Esp32 Esp-32s Cp2104 - Frete Carta | Mercado Livre
  • Connect to the board and copy 3 files to the board:

ble_advertising.py,

ble_uart_peripheral.py and 

ble_uart_repl.py

There files are available here: https://github.com/micropython/micropython/tree/master/examples/bluetooth

You can use BIPES itself to easily copy the files to the board (network or USB )!

  • Enable the Bluetooth REPL Service with the code on BIPES Web USB / Serial terminal:
import ble_uart_repl
ble_uart_repl.start()

You can add these lines to main.py if you want this service to be enabled at every boot.

  • Use your operating system bluetooth options to connect to the mpy-repl bluetooth device
  • A connected message will be shown:

On the console tab, click on “Connect (Web Bluetooth)”

And you will get the Bluetooth REPL terminal using BIPES to your ESP32 generic board!

Thank you very much makerdiary. I based the Web Bluetooth code on his project, which can also be directly used from the Web to access the ESP32 Web Bluetooth REPL:

https://wiki.makerdiary.com/web-device-cli/

https://github.com/makerdiary/web-device-cli

Arduino and the real world

  • A system installed outdoors using an Arduino Mega + Ethernet shield to send DHT11 and other sensor data to a remote server
  • Completed continuous 2 year operation on July 12 2021
  • Server uses Linux+PHP+MySQL
  • 1826506 records on the database
  • Highcharts used to plot graph with data
  • Average time to query MySQL table with 1.8 Millions records takes about 1 second. Server is a virtual machine at 2GHz with 4GB RAM / standard virtual hard disk. Table has only the primary key, without additional indexes
  • Low cost! Parts cost was about 25 dollars
  • Based (with many modifications) on: https://github.com/rafaelaroca/PHP_SimpleIOT and https://github.com/rafaelaroca/Arduino_RFID_Web_Access_Control_With_Calendar
  • Today is very cold at São Carlos, the Brazilian capital of technology! 😉

ESP32, Camera, MicroPython and NO esptool!

Circuit

This post describes how to load MicroPython on ESP32-CAM module, without using esptool – just using a web browser, and then using the ESP32 camera with MicroPython.

You can use ESP32-CAM module with the esp-32-cam-mb board, allowing direct connection from ESP32-CAM to a PC using USB cable. If you don’t have a esp-32-cam-mb module, you can connect ESP32-CAM module to a PC using a USB-Serial converter, such as the setup shown on the image below:

ESP32 - CAM OV2640. Module. WebServer - Internet of Things - MIT App  Inventor Community
ESP32 connections to flash the MicroPython firmware.
Image source: https://community.appinventor.mit.edu/t/esp32-cam-ov2640-module-webserver/10776

Flash the firmware

Many people, specially beginners, report trouble installing and using esptool to flash ESP32 boards. Thanks to Adafruit (https://github.com/adafruit/Adafruit_WebSerial_ESPTool), now we can flash ESP32 boards directly from the web browser using Web Serial API! No setup or installation required. Just go to the website, select the program and upload to the board!

Download firmware from here:

https://github.com/shariltumin/esp32-cam-micropython

Tested specifically this one:

https://github.com/shariltumin/esp32-cam-micropython/blob/master/firmwares/20191213/firmware.bin

Go to:

https://adafruit.github.io/Adafruit_WebSerial_ESPTool/

Select 115200, Offset 0x1000 and the just downloaded file: firmware.bin. Click on Connect and then Program.

Be patient – it took about 20 minutes to flash on my environment, but it was worth every minute, as no software install was required! Moreover, you will only need to do this once! After some minutes, the process is complete:

MicroPython

After flashing MicroPython firmware, have to remove the jumper cable from pin IO0 to GND, to leave flash mode, and then reset the board. After that, the MicroPython prompt should be available over the serial port. You can easily access the board serial port using BIPES serial:

https://bipes.net.br/beta2serial/ui/

You can also press Ctrl+D to send a soft reboot command, and see the version. Note the “(camera)” word on the version description.

MPY: soft reboot
MicroPython v1.11-665-gfb0141559-kaki5 on 2019-12-13; ESP32 module (camera) wit2
>>>

Now let’s try to save a photo. On the terminal, type:

import camera
camera.init()
img = camera.capture()
imgFile = open("photo.jpg", "wb")
imgFile.write(img)
imgFile.close()
import os
os.listdir()

The code above will take a snapshot and save to the photo.jpg file!

Useful source:

https://github.com/marcielbp/micropython-esp32cam-to-googleDrive

BIPES IoT dashboard using freeboard

Searching the web today, I found freeboard – an amazingly wonderful and powerful open source platform to easily build dashboards. As I understand, it was created by Jim Heising and Bug Labs.

https://github.com/Freeboard/freeboard

freeboard is completely client site and so organized and well structured, that in 5 minutes I cloned github repository and got freeboard running on BIPES server:

http://bipes.net.br/test/freeboard/index.html?load=dashboard.json

From that URL above, anyone can easily add widgets, datasources, panes, move panes, etc. Moreover, it can easily get data from MQTT server, so we can integrate it with BIPES EasyMQTT! As I looked for examples and plugins, I quickly found this fork, with highly flexible and complete customization options, by Daniel Dunn:

https://github.com/EternityForest/freeboard

This fork by Daniel Dunn has many options, I decided to test it integrated with BIPES! It worked nicely! Just access http://bipes.net.br/beta2/ui/ or https://bipes.net.br/beta2serial/ui/, go to the IOT tab, and see it working.

In order to test freeboard integration with BIPES, here is a test program:

This program was loaded on an ESP8266 board wih a DHT11 sensor. If you want to access / edit the blocks, the full program is here:

http://bipes.net.br/beta2/ui/#4qzj9f

Now we can use BIPES EasyMQTT JSON API to get the latest data published by the ESP8266 board and update a freeboard dashboard. Here are the endpoints I used:

http://bipes.net.br/easymqtt/gettopic_last.php?session=vn28pd&topic=Humidity

http://bipes.net.br/easymqtt/gettopic_last.php?session=vn28pd&topic=Temperature

http://bipes.net.br/easymqtt/gettopic_last.php?session=vn28pd&topic=relay

On the IOT tab, which is an iframe to freeboard index.html, I simply had to add a JSON data source for each of the 3 links above, adding each URL to each data source.

After that, it is possible to create panes and add widgets to panes. I used text, gauge and buttons widgets. For the text and gauge widgets, it is easy to select the data source with auto-completion:

For the buttons, I had to use one nice and powerful feature of freeboard, which is to trigger a Javascript code when the button is pressed.

In that way, I used the JS Editor and added this code, which will make a request to BIPES MQTT server and change the value of the relay topic after the click. Here is the code I used for on ON Button:

function httpGetAsync(theUrl, callback)
{
    var xmlHttp = new XMLHttpRequest();
    xmlHttp.onreadystatechange = function() { 
        if (xmlHttp.readyState == 4 && xmlHttp.status == 200)
            callback(xmlHttp.responseText);
    }
    xmlHttp.open("GET", theUrl, true); // true for asynchronous 
    xmlHttp.send(null);
}

this.onClick = function(){
    httpGetAsync("http://bipes.net.br/easymqtt/publish.php?session=vn28pd&topic=relay&value=1");
};

The OFF Button has the same configurations, but changing value=1 to value=0.

And here is the result:

While program is running on the ESP8266 board, data can be seen in real time, and if we click on the “Relay ON” button, a message is shown on the terminal.

If you want to play with this specific dashboard, you can download it from here:

http://bipes.net.br/beta2/ui/freeboard/dashboard_4qzj9f.json

Then open the IOT tab, click on upload freeboard and upload this file. You can modify / customize / adjust it.

Another interesting possibility is to load only the dashboard, without BIPES. For example, you used BIPES to create your program, but you only want to share the dashboard with other people. In that way, you can share the dashboard with a link like this:

http://bipes.net.br/beta2/ui/freeboard/index.html?load=dashboard_4qzj9f.json

Which, by the way, is responsive and loads / adjusts nicely to several devices. Here are some prints from this link on my Android phone:

Please note, that this text / discussion is a first impression / test of BIPES + freeboard integration. There are bugs and features to make a seamless integration:

  • Zoom control: on some screens I tested, the freeboard toolbar stays out of the screen. Changing the zoom helps get access to the toolbar, but a better solution is needed;
  • Add a nice “Powered by freeboard” somewhere (I removed the logo because it was too big, bug would like to see it);
  • Implement storage / sharing of freeboard using BIPES style (link with code);
  • Automatic integration with BIPES: when EasyMQTT session is created, automatically populate freeboard datasources;
  • For BIPES Serial version, create a channel from the terminal to local datasources using Javascript variables, so that a serial protocol can send data to be viewed on the freeboard dashboard without Internet connection.

BIPES has a new site with User Forum

BIPES has a new website, based on WodPress (thanks WordPress!) with an integrated forum. As the project grows, and documentation / collaboration is needed each time more, this is an important step. Updating information and discussing with users and developers is easier now.

The new site is below, and some posts about BIPES that I have been doing here on this blog may probably go to the new website:

https://bipes.net.br/wp/

MySQL loses data after server restart

This week I had one strange problem with a MySQL server after reloading a dump into a fresh MySQL (MariaDB) install. After that, everytime I restarted a MySQL server, data from a specific table were lost. Only from that specific table! No data corruption, error, etc. Just rows from that table disappear after each restart!

After careful looking at the database structure, I saw something unexpected:

show create table data;
| data |CREATE TABLE `data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ts` datetime NOT NULL,
  `s1` float DEFAULT NULL,
  `sensor` int DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=Memory

Wow. ENGINE=Memory?

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

https://en.wikipedia.org/wiki/Memory_(storage_engine)

MEMORY writes table data in-memory. While the table structures are persisted on-disk, the rows in MEMORY tables are lost when MySQL stops.

Just changed the engine to InnoDB and problem solved! Strange that I never explicitly defined this table as using “memory engine”, but anyway, problem solved 😉

Restoring single database from a MySQL dump file

A quick and practical way to extract a single database dump from a MySQL dump file:

sed -n '/^-- Current Database: `database1`/,/^-- Current Database: `/p' all_databases.sql > database1.sql

After that, simply go to MySQL prompt and load database1.sql using \. command.

Source:

https://www.configserverfirewall.com/mysqladmin/restore-one-database-all-database-dump/

Accessing BIPES shared blocks XML from terminal

Command:

[http://bipes.net.br/beta2/ui/#etshwf]

$ curl 'http://bipes.net.br/storage' -H 'Connection: keep-alive'  -H 'User-Agent: Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.72 Mobile Safari/537.36'  -H 'Content-Type: application/x-www-form-urlencoded'  -H 'Accept: /'  -H 'Origin: http://bipes.net.br'  -H 'Referer: http://bipes.net.br/beta2/ui/'  -H 'Accept-Language: en-US,en;q=0.9,pt-BR;q=0.8,pt;q=0.7'  -H 'Cookie: _ga=GA1.3.1743619384.1593735250; _gid=GA1.3.661944759.1624293831; BIPES_Splash=BIPES_SplashC; last_ip=ws://192.168.0.205:8266/&last_pass=bipesFive' --data-raw 'key=etshwf' --compressed --insecure

Result:

<xml xmlns="https://bipes.net.br"><workspace><field name="DEVICE">RPI4</field><field name="TIMESTAMP">1625230628930</field></workspace><block type="text_print"><value name="TEXT"><shadow xmlns="https://bipes.net.br" type="text"><field name="TEXT">abc</field></shadow><block type="text"><field name="TEXT">Sharing test 2021 for RPI Linux</field></block></value></block></xml>

Command:

[http://bipes.net.br/beta2/ui/#9i49u7]

curl 'http:H 'User-Agent: Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.72 Mobile Safari/537.36'  -H 'Content-Type: application/x-www-form-urlencoded'  -H 'Accept: /'  -H 'Origin: http://bipes.net.br'  -H 'Referer: http://bipes.net.br/beta2/ui/'  -H 'Accept-Language: en-US,en;q=0.9,pt-BR;q=0.8,pt;q=0.7'  -H 'Cookie: _ga=GA1.3.1743619384.1593735250; _gid=GA1.3.661944759.1624293831; BIPES_Splash=BIPES_SplashC; last_ip=ws://192.168.0.205:8266/&last_pass=bipesFive' --data-raw 'key=9i49u7' --compressed --insecure

Result:

<xml xmlns="https://bipes.net.br"><workspace><field name="DEVICE">ESP32</field><field name="TIMESTAMP">1625230866735</field></workspace><block type="text_print"><value name="TEXT"><shadow xmlns="https://bipes.net.br" type="text"><field name="TEXT">abc</field></shadow><block type="text"><field name="TEXT">Sharing test 2021 for ESP32</field></block></value></block></xml>