Tag Archives: raspberry pi

sqlite.remote version 1.0

March 22, 2020

A while back I released some simple code to allow access to a SQLite database on my embedded devices. A python/html5 app that provided an HTML5 interface to a python api. My goal is a simple to use method that allows quick and easy access to  databases on raspberry pi (or similar) used for configuration and logging on my IoT devices. The requirements are minimal :

  1. User login via http auth
  2. The ability to change a user password.
  3. SSL via self signed certificates
  4. Basic database access via a browser based app

Today version 1.0 of the sqlite.remote tool is being released. Available via git hub, the app meets the goals specified above.

Some assembly is required — mainly in the form of generating self signed certificates for the devices and browsers that will be used to access them. Check out my previous post about securing local IoT devices.  Generate the certs that you need before installing the sqlite.remote tool.

Installation:

Since I use this on the Raspberry PI it is installed in the pi user’s home directory. Any system that supports python should work, as should any user.

First clone the git hub repository:

 git clone https://github.com/m2ag-labs/m2ag-sqlite-remote.git

Then cd to the install directory and chmod the installer script:

chmod +x install.sh

Then ./install.sh to install dependencies. This app requires flask and flask_httpauth. Depending on your current update status the dependencies may already be installed.

After the install is completed the install can be tested by changing to the project root and running:

python3  api.py sqlite-remote.sqlite

This should start the api server and show a  message like this in the console:

As you can see we are using the barebones development server to run our api. Despite the message to not use this in production, I am using this in production. My usage only calls for one or two users at a time infrequently hitting the api. I decided to trade a simple implementation for a more robust web server and more complicated install. Plus, the web server would generally sit idle for day to day use.

It should also be noted that the api is being served over https. This is requires a self signed certificate to be installed on the device. This is configured at the bottom of api.py:

Adjust the paths to the correct certificate files. The api can be run without ssl if desired. For me, I want to provide at least a minimal level of security to my IoT devices. I intend this to be a config and control interface, but the devices will generally be inside a controlled firewall. Self signed certs  and a basic login meets my needs.

As part of the install a service a file is and copied to the systemd system directory. If the default location (/home/pi/m2ag-sqlite-remote) is changed, change it in this file.  To enable the service:

sudo systemctl m2ag-sqlite-remote enable
and 
sudo systemctl m2ag-sqlite-remote start

After the api is running navigate to your device at:

https://your-device.local:5001  to get the app page:

The default user is “pi” and the password is “raspberry”. These can be set in the credentials popup:

Select close to get back to query screen. If the query button is pressed with an empty query an “Ok” message will be returned. This tells us the configuration is correct:

To change the password use e the credentials popup:

‘Ok’ will appear in the status on a successful change.

To add new users first select * from users and get the hash for pi’s password and then insert a user using the same password hash. You can then use the new user’s credentials and change the password with the set password dialog.

General usage:

This app should handle most updates and edits to tables. Since the services setup specifies the database at startup additional tables to be added to the sqlite-remote.sqlite database. The only requirement for the app is the user table with a text username and password fields. This table could be added to any database. Just insert the pi user (or any other user) into the user tables via the command line app or some other tool.

It is hoped that you find this app useful, please feel free to open issues on github or comment here if there are problems with the app. Keep in mind it is barebones on purpose, all I need it to do is update tables remotely for me. If more complex usage is envisioned it may be modifications will need to be made.

If you find this work helpful perhaps you would consider supporting m2ag.labs open source efforts by buying us coffee. Any amount would be appreciated. Please use this link to do so:

Small Donate Button

Thanks for stopping by.

I2C Interfacing on the Raspberry PI

In this post I want to demonstrate some techniques for figuring out how to interface with an i2c chip and some basic verification techniques. I will be using a Raspberry PI 3 B, but these techniques will apply to most Linux based embedded systems with an i2c bus.

I2C is short for Inter-Integrated Circuit, a two wire bus used for connecting components to each other on electronic devices. I2C is just one option used for interconnecting devices. Other options  include. SPI, 1-wire, and Serial. When using devices for an embedded project we generally find SPI or I2C on devices. I tend to go for I2C devices when I can because they allow me to get away with using less wires to hook up.

When we integrate a device it is handy to ensure the device is recognized by the system before we try to write code for it. There are tools available for this called  I2C tools. Formerly a part of the LM-Sensors project, I2C tools allow low level access to I2C busses and the devices connected to them. These tools are not installed on the PI by default but are available via apt:

sudo apt install i2c-tools 

Let’s get started. The four commands we are interested in are summarized in this table:

Bus scanningi2cdetect
Device register dumpingi2cdump
Device register readingi2cget
Device register settingi2cset

Bus scanning:  

We use the i2cdetect command to both find the busses available as well as the devices on the bus. If we execute

i2cdetect -l

we can get a list of the currently installed I2C busses. The output is as as follows:

We can see we have 1 I2C bus on the PI.  If we wanted to see the devices on bus 1, we would enter:

i2cdetect -r 1

and get something like the following output:

The result of i2cdetect -r 1. This gives us a dump listing the devices on i2c bus 1.
The result of i2cdetect -r 1. This gives us a dump listing the devices on i2c bus 1.

When executing this command we get the standard warning that executing this command may jack up our device. It’s true, we can cause errors if we run this command on some busses on other devices (like the obsolete Intel Edison), but it has always cleared up for me with a power cycle of the Device. Usually the device just hangs.

Looking at the output we can see we have four devices on bus 1. At 0x18 we have a MCP9808 temperature sensor, 0x3C is a SSD1306 display, 0x48 is an ADS1819 ADC , and 0x77 is a BMP085 barometric pressure and temperature sensor.

Device register dumping:

Now that we know what devices are on the board (or more accurately all the devices we wired to the PI are recognized by our system) we can start working with the devices. Each device has internal registers that control the device and provide output from the device. We use the i2cdump command to take a look at these registers. Let’s take a look at the MCP9808

i2cdump 1 0x18 w

The form of the command is 12cdump (bus | device address | option). The ‘w’ option give us word output which makes it easier (for me at least) to read the registers. Executing the command gives us output that looks like this:

i2cdump of our MCP9808 on i2c bus 1 in word format. There is a lot of register space for devices, but we see we only have 13 registers in our device.
i2cdump of our MCP9808 on i2c bus 1 in word format. There is a lot of register space for devices, but we see we only have 13 registers in our device.

To understand what we are seeing in the dump we need to study the data sheet for our MCP9808.

If we study the data sheet for the MCP9808 we can decipher the registers we dumped.
If we study the data sheet for the MCP9808 we can decipher the registers we dumped.

Keeping in mind that the MCP9808 is a little endian device, we see the 16 bit data registers storing their values with the least significant  byte (LSB) first and most significant byte (MSB) second. We just need to do a quick byte swap to look at register 0 to see the value is 0x001d. According to the spec sheet the MSB should always be 0x00 as well as the LSB bits 7-4. Bits 3-0 in the LSB are the pointer used to select which register we read and write to when communicating to the device. The MCP9808 is a surprisingly complex device, so we won’t look at all the registers. But a thorough explanation of the registers and their function begins on page 16 of the data sheet.

Read a specific register:

We could certainly read the registers from the output of  i2cdump but there is a specific command for reading a single register. If we wanted to read the the current temperature the data sheet tells us the register containing this value is 0x05. To read this register we would issue this command:

i2cget 1 0x18 0x05 w

The command is in the form i2cget (bus | device address | register | option) . This gives is the following output:

Use i2cget to read the current temperature from our MCP9808.
Use i2cget to read the current temperature from our MCP9808.

We get the requisite warning and then the little endian contents of the register. Swapping the bytes to 0xC152 and referencing page 25 of the data sheet we can get the current temperature in Celsius. Briefly —   Bits 15-13 are flags that are used when we set the device to monitor a high and low set point. They can tell us if a threshold was passed, and which one. We don’t have those set so the values are not significant to us in this case. Bit 12 of the word is the sign of the two’s compliment value, since it is 0 our value is positive. Stripping those four bits out we are left with 0x0152 which is the temperature value. The rest is a base conversion and a little math — the MSB is 1 decimal, the LSB is 82. So using the data sheet formula  –> 1 * 16 + 82 / 16  = 21 degrees Celsius. Thats a cool 69.8 F in the office today.

Set a register:

If we want to manually control the MCP9808 we can set its registers with the i2cset command. Before we use the command a little back ground.

The MCP9808 has two modes of operation — continuous conversion and shutdown mode. Shutdown mode puts the device into a low power state and , as you might have guessed, stops updating the temperature measurement. If we read the temperature register while the device is in shutdown we will retrieve the last measurement made. In a battery powered device it might be a good idea to shutdown the device and only power it up when we need to update the temperature. This is controlled by bit 8 of the config register located at 0x01.

We can use the following command to enter into shutdown mode:

 i2cset 1 0x18 0x01 0x0001 w

Remember that we need to swap the bytes in the word due to endianness. This can be tested by reading the temperature while heating up the sensor (I just put my finger on it). You can use i2cget or i2cdump to read register 0x05 of the device. To set the sensor to continuous conversion mode use this command:

i2cset 1 0x18 0x01 0x0000 w

This post covered the basics of verifying a device is detected on an I2C bus and determining if the device is functioning properly. We used the four I2C tools commands to detect the busses and devices installed, dump a device registers, read a specific register, and to write a register. With these four commands we can certainly see if our devices are working correctly as we implement an embedded system.

The i2c tools do have other modes of operation that I did not touch on here. These modes can be read about on the I2C tools docs page. Armed with these tools and the data sheet for a device we are ready to get coding.