Latest Posts

April 10, 2018C. Thomas Brittain (Ladvien) Reading time ~1 minute

Setup NodeJS Project Space on Raspberry Pi Zero W

Setup the Arch Linux SD Card

This article will be building off a previous article, where I walked through a headless setup of Arch Linux on the Raspberry Pi Zero W (rp0w). And if you aren’t familiar with the term “headless setup,” essentially, we are talking about setting up the SD card so you don’t have to plug it into a monitor. You can plug it in to your rp0w, boot it, and SSH in.

Installing Arch Linux on a Raspberry Pi with Immediate WiFi Access

Now you’ve setup the Arch Linux card and SSH’ed lets go through setting up a NodeJS environment on the rp0w. Luckily, there have been people smarter than me who’ve already done some heavy lifting for us.

Alright, start by SSH’ing into your Raspberry Pi.

Running a NodeJS Install Script

Now we are at the Raspberry Pi command prompt we are going to run a script which will pull down the latest version of NodeJS built for ARM and install it to the Raspberry Pi.

But before we can do that we need to install a few helper programs

At the command prompt type and say “yes” when prompted.

sudo pacman -S wget

Wget is a package which allows direct download of Internet content from the command prompt.

Now, we will run a command which pulls a NodeJS installation script of the Internet and run it. This script was written by audstanley and can be found at

NodeJS Raspberry Pi Github

If you like the script, you should go buy audstanley a coffee – the link to do so is the Github page.

As of this writing, the script downloads the latest version of NodeJS for your architecture (that’s the tricky part), installs it, then creates the appropriate symbolic links for NodeJS and npm to work correctly.

Ok, enough preamble.

To install NodeJS type

sudo wget -O - https://raw.githubusercontent.com/audstanley/NodeJs-Raspberry-Pi/master/Install-Node.sh | sudo bash
node -v

That’s it!

We can now create a new node project by typing

mkdir my_node_project
cd my_node_project
npm init

If you want to learn more about NodeJS, I recommend the Udemy course:

The Complete NodeJS Developer Course

I’m not getting kick backs; it’s the course I used to get and liked it. I actually used the Raspberry Pi for the code he walks through building and didn’t have a problem.

March 03, 2018C. Thomas Brittain (Ladvien) Reading time ~6 minutes

Lag() before MySQL 10.2

Recently I was required to work with MySQL 10.0. I was surprised to find MySQL 10.2 and less does not support some common Windowing Functions, specifically, Value Functions and Rank Functions.

Well, bummer. I really needed them.

On top of it, I only had read access to the database without the ability to create a stored procedure. Out of desperation, I found myself researching the possibility of creating my own functions using MySQL User Variables.

Slightly tweaking Dante, “Abandon all normal, ye who enter here.” User variables are weird.

I’ve spent some time researching on the interweb and about the best article I’ve found on the subject is:

Advanced MySQL User Variable Techniques

Which focuses on getting the desired behavior from user variables rather than understanding them. This article is going to stick with the same philosophy–I don’t need to necessarily understand them, however, I want to be able to predict their behavior.

One word of warning, the above article is not really a suggessted reading before this article–it’s more of required reading. If you don’t know how to force the user variables to be evaluated when you need them, then the results are unpredictable.

The TL;DR version: Order of operations matter a lot in user variables and wrap the user variable in a subquery or function to force evaluation.

At this bottom of the article I’ve included the data used in this article. You can insert it into a MySQL or MariaDB database and follow along. The goal is to convert these data into a start_date and stop_date which would greatly reduce the storage needs.

For id 1 the start_date and stop_date equivalents would look like:

id	date
1	*09/10/12*	start_1
1	09/11/12
1	09/12/12
1	09/13/12
1	*09/14/12*	stop_1
1	*10/11/12*	start_2
1	10/12/12
1	*10/13/12*	stop_2

We want to end up with a table like below.

id	start_date	end_date
1	09/10/12	09/14/2012
1	10/11/22	10/13/2012

To transform the data into this table it’s important to know user variables can hold a value from one row to the next.

SELECT
    id, date, @row_number:=@row_number + 1 row_num
FROM
    attendance
        CROSS JOIN
    (SELECT @row_number:=0) r;

This should produce the following table:

id	date	row_num
1	2012-09-10	1
1	2012-09-10	2
1	2012-09-11	3
5	2013-02-07	4
5	2013-02-07	5
5	2013-02-07	6
5	2013-02-07	7
5	2013-02-07	8
5	2013-02-07	9
…	…	…

Pretty cool, right? Now, if only we could get the row_number to reset whenever the id changes. No worries, let’s use another variable to store the id from the previous row so we can compare it to the current.

SELECT 
    id,
    date,
    @row_number:=@row_number + 1 row_number,
    IF(@previous_id = id,
        @row_number,
        @row_number:=0) calc1,
    @previous_id:=id cacl2
FROM
    attendance
        CROSS JOIN
    (SELECT @row_number:=0, @previous_id:=0) r;

This should give us the following output:

id	date	row_number	calc1	cacl2
1	2012-09-10	1	0	1
1	2012-09-10	1	1	1
1	2012-09-11	2	2	1
5	2013-02-07	3	0	5
5	2013-02-07	1	1	5
…	…	…	…	…

Notice, the calc1 and calc2 are not values you need. They are merely calculations used to reset the row_number whenever the id changes. Hmm, this is interesting–and, hopefully, you can see it has many possibilities.

Now, let’s go back and think about our problem a bit more.

id	date
1	09/10/12
1	09/11/12
1	09/12/12
1	09/13/12
1	09/14/12
1	10/11/12
1	10/12/12
1	10/13/12

We can save a value from one row to the next. Therefore, detecting the breaks in a range of attendance dates can be obtained by comparing the current row’s date value to the previous row’s date value. If the previous row is greater than the current row minus one, then we know there was a break in the range.

SELECT 
    id, date, range_selector
FROM
    (SELECT DISTINCT
        id,
            date,
            IF(@previous_id = id, @range_selector, @range_selector:=0) calc1,
            IF(DATEDIFF(@previous_date, date) = 1, @range_selector, @range_selector:=@range_selector + 1) range_selector,
            @previous_id:=id calc2,
            @previous_date:=DATE(date) calc3
    FROM
        (SELECT DISTINCT
        *
    FROM
        attendance
    ORDER BY id DESC , date DESC) order_sub
    CROSS JOIN (SELECT 
        @id_selector:=0,
            @previous_date:=0,
            @range_selector:=0,
            @previous_id:=0
    ) variable_initialization
    ORDER BY id , date DESC) date_ranges;

This should give the following table:

id	date	range_index
1	2012-10-13	1
1	2012-10-12	1
1	2012-10-11	1
1	2012-09-14	2
1	2012-09-13	2
1	2012-09-12	2
1	2012-09-11	2
1	2012-09-10	2
2	2012-09-23	1
2	2012-08-22	2
2	2012-08-17	3
2	2012-08-12	4
2	2012-08-11	4
2	2012-08-10	4
2	2012-08-09	4
4	2012-11-22	1
4	2012-11-03	2
4	2012-11-02	2
4	2012-11-01	2
4	2012-10-04	3
4	2012-10-03	3
4	2012-10-02	3
4	2012-10-01	3
5	2013-02-07	1
5	2013-02-06	1
5	2013-02-05	1
5	2013-02-04	1
5	2013-02-03	1
5	2013-02-02	1
5	2013-01-28	2
5	2013-01-24	3
5	2013-01-23	3
5	2012-12-07	4
5	2012-12-06	4
5	2012-12-05	4
5	2012-12-04	4
5	2012-12-03	4
5	2012-12-02	4
5	2012-12-01	4
5	2012-11-01	5

The reason I state “should”, if you modify the order of the user variables, it’ll break. If you change the ORDER BY, it’ll break. If you add a WHERE or HAVING clause, it’ll break. Pretty much, it’s as fragile a query as they come.

However, the clever bunch probably see where we are going with this. Now, it’s simply a matter of taking the MIN() and MAX() of of date and group by the id and range_index.

SELECT 
    id, min(date) start_date, max(date) end_date
FROM
    (SELECT DISTINCT
        id,
            date,
            IF(@previous_id = id, @range_selector, @range_selector:=0) calc1,
            IF(DATEDIFF(@previous_date, date) = 1, @range_selector, @range_selector:=@range_selector + 1) range_selector,
            @previous_id:=id calc2,
            @previous_date:=DATE(date) calc3
    FROM
        (SELECT DISTINCT
        *
    FROM
        attendance
    ORDER BY id DESC , date DESC) order_sub
    CROSS JOIN (SELECT 
        @id_selector:=0,
            @previous_date:=0,
            @range_selector:=0,
            @previous_id:=0
    ) r
    ORDER BY id , date DESC) date_ranges
    GROUP BY id, range_selector;

Which should provide us with output like:

id	start_date	end_date
1	2012-10-11	2012-10-13
1	2012-09-10	2012-09-14
2	2012-09-23	2012-09-23
2	2012-08-22	2012-08-22
2	2012-08-17	2012-08-17
2	2012-08-09	2012-08-12
4	2012-11-22	2012-11-22
4	2012-11-01	2012-11-03
4	2012-10-01	2012-10-04
5	2013-02-02	2013-02-07
5	2013-01-28	2013-01-28
5	2013-01-23	2013-01-24
5	2012-12-01	2012-12-07
5	2012-11-01	2012-11-01

And there we go. Not too amazing, but I couldn’t find this answer by Googling, so I figure I’d add it to the great Wiki in the Sky.

CREATE TABLE attendance(
   id   INTEGER  NOT NULL
  ,date DATE  NOT NULL
);
INSERT INTO attendance(id,date) VALUES (1,'2012-09-10');
INSERT INTO attendance(id,date) VALUES (1,'2012-09-11');
INSERT INTO attendance(id,date) VALUES (1,'2012-09-12');
INSERT INTO attendance(id,date) VALUES (1,'2012-09-13');
INSERT INTO attendance(id,date) VALUES (1,'2012-09-14');
INSERT INTO attendance(id,date) VALUES (1,'2012-10-11');
INSERT INTO attendance(id,date) VALUES (1,'2012-10-12');
INSERT INTO attendance(id,date) VALUES (1,'2012-10-13');
INSERT INTO attendance(id,date) VALUES (2,'2012-08-09');
INSERT INTO attendance(id,date) VALUES (2,'2012-08-10');
INSERT INTO attendance(id,date) VALUES (2,'2012-08-11');
INSERT INTO attendance(id,date) VALUES (2,'2012-08-12');
INSERT INTO attendance(id,date) VALUES (2,'2012-08-17');
INSERT INTO attendance(id,date) VALUES (2,'2012-08-22');
INSERT INTO attendance(id,date) VALUES (2,'2012-09-23');
INSERT INTO attendance(id,date) VALUES (4,'2012-10-01');
INSERT INTO attendance(id,date) VALUES (4,'2012-10-02');
INSERT INTO attendance(id,date) VALUES (4,'2012-10-03');
INSERT INTO attendance(id,date) VALUES (4,'2012-10-04');
INSERT INTO attendance(id,date) VALUES (4,'2012-11-01');
INSERT INTO attendance(id,date) VALUES (4,'2012-11-02');
INSERT INTO attendance(id,date) VALUES (4,'2012-11-03');
INSERT INTO attendance(id,date) VALUES (4,'2012-11-22');
INSERT INTO attendance(id,date) VALUES (5,'2012-11-01');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-01');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-02');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-03');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-04');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-05');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-06');
INSERT INTO attendance(id,date) VALUES (5,'2012-12-07');
INSERT INTO attendance(id,date) VALUES (5,'2013-01-23');
INSERT INTO attendance(id,date) VALUES (5,'2013-01-24');
INSERT INTO attendance(id,date) VALUES (5,'2013-01-28');
INSERT INTO attendance(id,date) VALUES (5,'2013-02-02');
INSERT INTO attendance(id,date) VALUES (5,'2013-02-03');
INSERT INTO attendance(id,date) VALUES (5,'2013-02-04');
INSERT INTO attendance(id,date) VALUES (5,'2013-02-05');
INSERT INTO attendance(id,date) VALUES (5,'2013-02-06');
INSERT INTO attendance(id,date) VALUES (5,'2013-02-07');

February 17, 2018C. Thomas Brittain (Ladvien) Reading time ~4 minutes

Setup i2c on Raspberry Pi Zero W using Arch Linux

This article builds on the previous, where I ran us through setting up Arch Linux for the Raspberry Pi Zero W.

Let’s not stop, let’s get I2C going so we can interact with some cool hardware.

1. Installing sudo

If you’ve followed my previous guide on installing Arch Linux on a Raspberry Pi then you’ll have ended up with a bare bones system, which is good. No unneeded fat. But sometimes fat is needed, it’s what gives us curves, and curves are beautiful….I feel this metaphor is breaking down. In short, we need extra packages to get work done.

The first package we need is sudo

Install Sudo on Arch Linux

It will allow us to easily manage file permissions.

First, make sure your system is up to date. To do this we are going to login as the root user. You can do this by typing su followed by the root user’s password, which for a barebone Arch Linux installation is root.

$ su
Password: root

Oh, and if you haven’t figured out yet, alarm stands for Arch Linux ARM.

Next we need to update the package libraries and system.

pacman -Syu

After hitting enter, it should look like this:

root@alarmpi alarm]# pacman -Syu
:: Synchronizing package databases...
 core                                                        179.0 KiB   448K/s 00:00 [#################################################] 100%
 extra                                                      1982.8 KiB  1279K/s 00:02 [#################################################] 100%
 community                                                     4.0 MiB  1689K/s 00:02 [#################################################] 100%
 alarm                                                        35.0 KiB   583K/s 00:00 [#################################################] 100%
 aur                                                           6.0 KiB  0.00B/s 00:00 [#################################################] 100%
:: Starting full system upgrade...
resolving dependencies...
looking for conflicting packages...

It should give you a list of packages with update and upgrade candidates and prompting you to confirm the updates. Go ahead and say yes.

Now we should be good to install sudo

$ pacman -S sudo

Even after sudo is installed, we still need to add the main user, which is alarm to the sudo’er group. This in effect gives the alarm user the superpowers of the root user.

Arch Linux Sudo Configuration

Now, the way sudo works is by adding a user to a special Linux group. Anyone added to this group will be given root superpowers. To get a list of those currently in the sudo group:

sudo -ll

You should get something like

User root may run the following commands on alarmpi:

Sudoers entry:
    RunAsUsers: ALL
    Commands:

Ok, let’s get the alarm user added to the sudoer group.

Type

EDITOR=nano visudo

This should allow you to edit the visudo file and add alarmpi to sudoers. Oh, the write permissions for the visudo file are limited to root, so if you have switched back from the root user to alarmpi you will need to run su again and log back in as root before editing this file.

Let’s find the entry for adding users to the sudo’er group.

Find the part which looks like this:

##
## User privilege specification
##
root ALL=(ALL) ALL

And add alarm ALL=(ALL) ALL right below the root entry. It should look like this after editing.

##
## User privilege specification
##
root ALL=(ALL) ALL
alarm ALL=(ALL) ALL

Then hit CTRL+O to write the changes and CTRL+X to exit.

Before we can check the changes took, we will need to exit our root session.

exit

This should land you back at your alarm session. To see you the alarm user is now added to the sudoer group type

sudo -ll

And if all went well, you’ll get this output

User alarm may run the following commands on alarmpi:

Sudoers entry:
    RunAsUsers: ALL
    Commands:
        ALL

Notice, we now have access to ALL commands. Booyah!

We can do a hard test by typing:

sudo ls

We should get

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for alarm:

Type the alarm user password (which is alarm, if you haven’t changed it).

2. Install needed packages

pacman -S git python2 i2c-tools base-devel python2-distribute python2-pip

Use Python’s Package Index (pip) to install Raspberry Pi GPIO support

sudo pip2 install RPi.GPIO

3. Install raspi-config

sudo pacman -S xorg-xrandr libnewt
git clone https://aur.archlinux.org/raspi-config.git
cd raspi-config
makepkg -i

Use the Raspi-Config tool to enable I2C

sudo raspi-config

Select “Interfacing Options” and enable I2C.

Note: Going back through these instructions I did notice when I started raspi-config I received this warning:

/usr/bin/raspi-config: line 997: warning: command substitution: ignored null byte in input

And when I attempted to enable I2C it gave this error.

* Failed to read active DTB

But it still seemed to do the job. I’ll investigate more when I’ve time.

Now, we need to reboot the system for everything to register.

4. Test the I2C Setup

We should be all setup. Try running

sudo i2cdetect -y 1

If all has went well then you should get

[alarm@alarmpi ~]$ sudo i2cdetect -y 1
1  2  3  4  5  6  7  8  9  a  b  c  d  e  f
         -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- --

Now, we just need to connect an I2C device to the bus and we should the hex address of where the device may be found.

February 16, 2018C. Thomas Brittain (Ladvien) Reading time ~4 minutes

Installing Arch Linux on Raspberry Pi with Immediate WiFi Access

Disclaimer: The easiest way to setup an SD card with Arch Linux for the Raspberry Pi Zero W (rp0w) is using Linux–and the following guide will assume you have access to Linux somewhere. For Mac and Windows users, it is possible to setup an SD card using Linux inside of a virtual machine. The interwebs will have more on the subject.

The hardest part of setting up Arch Linux for the rp0w is getting the WiFi working on boot. This allows accessing the OS through ssh immediately. This is known as a “headless setup.” I’ve created instructions on doing something similar in Kali. However, I was lucky when I hit Arch–as there is a fine fellow who has already written a script to setup the WPA Supplicant needed for a headless build.

Stasiana’s instructions script for setting up wpa_supplicant

1. Create an SD Card by following the Arch Linux instructions

Really, the only piece of information not provided by Arch Linux community is which ARM architecture you need for the rp0w. It’s armv6.

A few notes on using the installation instructions.

I had to run most of the commands as root (sudo)
We are going to insert a step afte the SD card is setup and before we boot our rp0w
MOST IMPORTANT NOTE: If you accidently select a different device instead of your SD card bad poop will happen. For real. To know which device is your card make heavy use of fdisk -l which will provide a list of all devices. Your SD card is approximately the same size as the card states. For example, this is the output I get when I run fdisk -l on my PC with the SD card in.

I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disklabel type: gpt

Device         Start       End   Sectors   Size Type
/dev/sda1         40    409639    409600   200M EFI S
/dev/sda2     409640 578929663 578520024 275.9G unkno
/dev/sda3  578929664 586480023   7550360   3.6G Micro
/dev/sda4  586480024 586742167    262144   128M Apple
/dev/sda5  586743808 976842751 390098944   186G Linux
/dev/sda6  976842880 977105023    262144   128M Apple

Mounting
Unmounting
Cleaning up

Disk /dev/sdb: 7.5 GiB, 8053063680 bytes, 15728640 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0xd0ca12f8

Device     Boot  Start      End  Sectors  Size Id Type
/dev/sdb1         2048   206847   204800  100M  c W95
/dev/sdb2       206848 15728639 15521792  7.4G 83 Linu

So, the main device path for my SD card is /dev/sdb. And to the first partition it’s /dev/sdb1

2. Create Script to Enable WiFi on Boot

We are going to need to create a script on the Linux OS you used to setup the SD card. This script will access the rp0w’s Arch Linux files and inject our WiFi information. This will allow the rp0w to automatically connect to your WiFi router when it boots, thus, giving you immediate access to it via SSH.

At the command prompt (of your PC, not the rp0w)

nano al-wpa-setup.sh

This will open a blank nano editor. Inside, paste the following, then save the file.

#!/bin/sh

set -e

if [[ $# -ne 3 ]] ; then
   echo "Usage: $0 </dev/disk> <ssid> <passphase>"
   exit 1
fi

DISK="$1"
SSID="$2"
PASS="$3"

if [[ ! -b "${DISK}" ]] ; then
   echo "Not a block device: ${DISK}"
   exit 1
fi

if [[ "${USER}" != "root" ]] ; then
   echo "Must run as root."
   exit 1
fi

echo Mounting
mkdir root
mount "${DISK}2" root

cat << EOF >> root/etc/systemd/network/wlan0.network
[Match]
Name=wlan0

[Network]
DHCP=yes
EOF

wpa_passphrase "${SSID}" "${PASS}" > root/etc/wpa_supplicant/wpa_supplicant-wlan0.conf

ln -s \
   /usr/lib/systemd/system/wpa_supplicant@.service \
   root/etc/systemd/system/multi-user.target.wants/wpa_supplicant@wlan0.service

echo Unmounting
umount root

echo Cleaning up
rmdir root

For those curious or wary, this script takes three parameters

The location of SD card in the PC’s device tree
SSID of your WiFi router
Password for the WiFi router

It then mounts the SD card, accesses the files needed to setup WiFi, and inserts the connection information appropriately.

Thanks again, Stasiana.

Let’s keep going.

Before we can run the script it must be given executable permissions.

chmod +x al-wpa-setup.sh

Note: If you execute the script in the same path as where you built the SD card then the script will complain

mkdir: cannot create directory ‘root’: File exists

That’s because the Arch Linux instructions didn’t mention removing the SD card paths.

To delete the paths root and boot which were required for setup run (make sure your not in the / path first).

sudo rm -R boot root

Now, let’s execute it, passing /dev/sdX, your_wifi_name, and your_wifi_password. Like so.

./al-wpa-setup.sh /dev/sdb wifi_name wifi_password

If all goes well, you should see.

Mounting
Unmounting
Cleaning up

Anything else, leave me a comment and I’ll help troubleshoot.

3. Connecting

Ok! That’s it. Now, put the SD card into the rp0w and fire it up. The green light should begin flashing.

The last tricky part is knowing what IP address has been assigned to the rp0w on boot. After waiting a few minutes for it to connect to the wifi, visit your router’s admin page. It’s usually 192.168.1.1.

You’ll need the router login information. But once in there should ba a section like “Attached Devices”. In there you should see an entry for “alarm” (which stands for Arch Linux ARM). This your rp0w.

Now, at the command line type:

ssh alarm@192.168.1.xxx

Replacing the xs with the address of your Raspberry Pi. If you don’t know the address of the Raspberry Pi you can log into router and look for ALARMPI.

Where the xxx is the address assigned to the Pi. You should be prompted with an EDSCA warning (say yes). Then, you will need to enter the password which is alarm.

Welcome to Arch Linux ARM

     Website: http://archlinuxarm.org
       Forum: http://archlinuxarm.org/forum
         IRC: #archlinux-arm on irc.Freenode.net
Last login: Thu Apr 12 12:18:05 2018 from 192.168.1.5
[alarm@alarmpi ~]$

Happy Arching.

Ladvien's Lab