DIYFieldworkDatabase

Overview of the system

I put together a little DIY fieldwork database server using nearly entirely open source tools and software. It consists of a Raspberry Pi operating on a local network, which contains a MariaDB server, a SMB file sharing server, an R Shiny server, and automatic scheduled backups, but can be configured or extended to suit whatever situation you're in. This is a more collaborative and distributed setup than a typical Microsoft Access setup (or even worse, a series of scattered spreadsheets :z), since multiple users can simultaneously enter data stored in a unified and central directory, share files across the network, and browse what's going on across an entire project. Microsoft Access, on the other hand, is proprietary, expensive and windows-only software that has poor multi-user support and the learning curve is steeper than its worth (I could go on...).

Moreover, this is a very inexpensive and portable setup. In terms of hardware, you will need a 35$ Raspberry Pi, a 32GB SD card (also ~35$), a small router (this one is 40$ and tiny! https://amzn.to/2HoOM6W), a high ampage external battery (I recommend this one at 43$: https://amzn.to/2Ht8quv) and a couple USB flash drives with storage capacities that suit your needs.

Please refer to the diagram for further details regarding hardware configuration and overall setup.

This repo documents how I put things together and made things work. Please get in touch if you have any questions!

• Zack Batist
• @mtl_zack on twitter
• zackbatist on github

R code and SQL snippets coming soon!

Installing Raspbian

Insert microSD card into the adapter and insert into a laptop.

Format the microSD card as FAT32 using an SD Card Formatter tool (https://www.sdcard.org/downloads/formatter_4/).

Etch the Rasbian disk image to the SD card using Etcher (https://etcher.io/). Raspbian is available at the Rasoberry Pi downloads page (https://www.raspberrypi.org/downloads/raspbian/).

Configure the OS to enable SSH on first boot.

touch /Volumes/boot/ssh

Add network info to enable immediate connection to the network.

touch /Volumes/boot/wpa_supplicant.conf
cd /Volumes/boot
nano wpa_supplicant.conf

Paste this in to wpa_supplicant.conf, changing the country code, network SSID and network password to suit the situation:

country=CA
ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

network={
    ssid="<network name>"
    psk="<network password>"
}

These last three steps are crucial for working headless!

Eject the microSD card, place it in the Raspberry Pi and boot it up. Wait a couple minutes for it to finish booting up. Then SSH in.

Download updates

Once you SSH into the pi, update all software packages (requires internet connection).

sudo apt-get update -y
sudo apt-get upgrade -y

It is apparently not necessary to update the kernel or firmware. The most up-to-date versions are included in the raspbian disk images, and using sudo rpi-update may install bleeding-edge updates or untested firmware.

More info:

• https://www.raspberrypi.org/documentation/raspbian/updating.md

Shutting down (VERY IMPORTANT!)

__NOTE: IT IS EXTREMELY IMPORTANT THAT YOU DON'T YANK THE POWER CHORD TO POWER OFF!!! THAT CAN CORRUPT THE SD CARD AND YOU'LL HAVE TO RE-IMAGE IT OVER AND OVER AGAIN! I KNOW THIS FROM MY OWN PAINFUL AND STUPID EXPERIENCE SO DON'T EVEN DARE! USE THE FOLLOWING SHUTDOWN COMMANDS INSTEAD.

TO SHUTDOWN:

sudo shutdown -h now

More info:

• https://www.raspberrypi.org/forums/viewtopic.php?t=191061

Setting up static IP addresses

It is necessary to set static IP addresses as fallbacks in case wpa_supplicant.conf fails. We need to do this for both wireless and ethernet interfaces.

First we need to get the Raspberry Pi's IP address on each interface:

pi@raspberrypi:~ $ ip -4 addr show | grep global

The output should look something like this:

    inet 169.254.39.157/16 brd 169.254.255.255 scope global eth0
    inet 192.168.0.198/24 brd 192.168.0.255 scope global wlan0

eth0 refers to the ethernet interface, and wlan0 refers to the wireless interface.

Then we need to determine the router's gateway:

pi@raspberrypi:~ $ ip route | grep default | awk '{print $3}'

Should look something like this:

192.168.0.1

Then we need to determine the DNS server or namespace, which is often the same as the router's gateway:

pi@raspberrypi:~ $ nano /etc/resolv.conf

Should look something like this:

# Generated by resolvconf
nameserver 192.168.0.1

Next we have to add this information to /etc/dhcpcd.conf:

sudo nano /etc/dhcpcd.conf

Add the following, substituting the IP address values for the one's that were just determined above.

interface eth0
static ip_address=169.254.39.157/16
static routers=192.168.0.1
static domain_name_servers=192.168.0.1

interface wlan0
static ip_address=192.168.0.198/24
static routers=192.168.0.1
static domain_name_servers=192.168.0.1

Pro-tip: You may need to make a direct connection with your computer to download updates (I tend to tether my cullular connection to my laptop and share the connection over ethernet). In such cases, you will need to edit dhcpcd.conf and comment out the eth0 specifications to enable an alternative ethernet interface with your laptop that is not specific to the router that it is normally connected to. You may not even need an eth0 static IP, but it helps to have that direct ethernet connection in place if you are using the Pi as a network attached storage (NAS) device, i.e. sharing files over the network, as this gives a significant speed boost to file transfers.

SSH

SSH enables you to control the raspberry pi over the local network via the command line.

In Terminal.app run the following command:

ssh pi@<ip address>

This terminal window should now be considered a remote client to the raspberry pi. Everything you do here occurs on the raspberry pi.

If you get the following warning, just go and edit ~/.ssh/known_hosts and delete the key for the corresponding SSH connection. This might happen when trying to connect to a fresh install of the Raspbian OS.

Default password is raspberry.

The pi's IP address can be found in the router control panel, which can be accessed by entering the router's IP address into the browser's address bar, and entering the default username and password when prompted.

The pi's IP address, along with IP addresses for all devices connected to the network, will be listed under a page titled simething like 'DHCP client list'.

Pro tip: In your router settings, under the DHCP menu, you should be able to reserve an IP address for the Raspberry Pi. Find the Pi's MAC address by running ifconfig (it's after ether under the wlan0 heading), and assign it an IP towards the upper end of the range allocated to the local network. This helps keep things consistent and makes for a more efficient headless setup.

More info:

• https://www.routerdefaults.org/tp-link/tl-wr802n

Router's IP address: 192.168.0.254
Username: admin
Password: admin

SSH via direct ethernet connection (MacOS)

Raspbian is configured by default to receive an IP address from a DHCP server, a role that is typically fulfilled by a wireless router. This can be done without a router by configuring the sharing options in MacOS.

From System Preferences, go to the Sharing window.

From the box on the left, enable 'Internet Sharing'. You may have to select a 'Thunderbolt Ethernet' under the Wi-Fi dropdown before checking off the 'Internet Sharing' box.

In Terminal, use ifconfig. Under bridge100, note the IP address in the inet field, most likely 192.168.2.1. Add one to the final digit to determine the likely IP address of the Raspberry Pi (192.168.2.2 in this case).

Note: This will not work if the wireless network is secured by a 802.1x firewall, which are commonly implemented by most university, hospital and government networks.

More info:

• https://medium.com/@tzhenghao/how-to-ssh-into-your-raspberry-pi-with-a-mac-and-ethernet-cable-636a197d055
• https://www.jeffgeerling.com/blog/2016/ssh-raspberry-pi-only-network-cable-using-os-xs-internet-sharing

Mounting a USB drive

While Raspberry Pi does support auto USB mounting when starting the machine, it can be a bit flaky. Therefore we will use the device ID to ensure that there is more certainty of a proper mount. So we need to determine the UUID of the USB device by running this command:

ls -al /dev/disk/by-uuid

and cross checking it with the devices listed after entering:

lsblk

Infer the correct UUID based on the drive's capacity and through elimination of other possibilities, and note it down somewhere.

Now create the directory where we want to mount the drive:

sudo mkdir /media/backup

Give the directory adequate permissions:

sudo chmod -R 777 /media/TrenchData

Note: This command gives unrestricted read, write and execute permissions to all users! Configure your permissions to suit your needs!

Open the fstab file:

sudo nano /etc/fstab

At the end of that file, add the following command, replacing the UUID with the one pertaining to the specific device and the directory with the intended mount point:

UUID=783A-120B /media/backup auto noatime,nofail,umask=000 0 0

Note: This is the correct code for FAT32 formatted drives only. FAT32 handles permissions is a slightly different way than NTFS and linux-based filesystems (i.e. ext4) and you will need to configure them acco