1B1 Robot

Not too long ago there was a post on Hackaday about a little four-wheeled bot made with a Raspberry Pi and some eBay motor drivers.

Raspberry Pi Zero Drives Tiny RC Truck

I really liked the little chassis, ordered one, and was happy to find it was delivered with the motors already mounted. (As I become an aged hacker, it’s the little time savers which are genuinely appreciated.)

On buying the chassis I’d already decided to use one of my Raspberry Pi Zero W’s (rp0w) to control the bot. I really like Arch Linux on the rp0w. It’s light weight and the packages are well curated. Again, it’s the little time savers. I liked the combination even more since I found a way to set the rp0w headlessly, which meant I could go from SD card to SSH’ing into little Linux board.

Coincidentally, I purchased several DRV8830 modules from eBay. This is a sad story – I’ve played with the DRV8830 chip a long time ago:

Because Sparkfun did a great job of documenting the IC and creating an Arduino library to go with it. I was disheartened to find Sparkfun and EOL’ed the boards.

Probably because buttholes like me kept buying them off eBay. I’ve got some mixed feelings here – one of them is guilt.

Anyway, I was surprised to find the mounting holes on the DRV8830s matched a set on the chassis. I decided to attempt using one module to drive two motors, thereby only needing two DRV8830 modules to drive the entire bot.

I’ve had some thermal paste lying about for years–it works nicely as an adhesive. Also, I was hoping to use the chassis to heatsink the motor drivers.

A bit of a tangent. At work one of the skills which is useful for our team is being able to work with APIs. For awhile I’ve wanted to learn NodeJS, since it seems to be the goto framework for solid back-end business applications. It doesn’t hurt StackOverflow’s Developer Survey for the last few years has shown JavaScript is a solid language to stay sharp on. Specifically, being able to work within the NodeJS framework makes one pretty darn marketable.

Ok, for these reasons I decided to build this bot using NodeJS. I’ve written a separate article on setting up NodeJS, working with i2c-bus, and porting the DRV8830 Sparkfun library to NodeJS.

  • Not yet written (shesh, been busy. Judge much? :P)

It didn’t take time at all to get the little motor spinning using NodeJS largely due to Michael Hord’s (Sparkfun) MiniMoto library. (Again, some guilt here.)

I drove the motor shown using two series Li-Ion batteries connecting to a buck converter set to output ~5.0v. The motor spun nicely and pulled around 200mA. However, the real test would be connecting to two geared motors per DRV8830.

'use strict';
var i2c = require('i2c-bus'), i2c1 = i2c.openSync(1);
var sleep = require('sleep');
var drv8830 = require('./drv8830');

const motorAddressOne = 0x61;
const motorAddressTwo = 0x67;

var motor1 = new drv8830(motorAddressOne, i2c1);
var motor2 = new drv8830(motorAddressTwo, i2c1);

motor1.drive(50);
motor2.drive(50)
sleep.msleep(3500);
motor1.drive(-50);
motor2.drive(50);
motor1.stop()
motor2.stop()

It was time to wire up the chassis motors and create a test of the system. The wire used was some eBay single core aluminum wire (the cheap stuff). Wiring was pretty straightforward.

However, I did make a little i2c bus board from perfboard and JST connectors. Adding both ceramic and electrolytic decoupling capacitors for smoothing and to aid peak discharge.

Note the heaping amount of heatsink goop on the underside of the perfboard, this was a hacker’s solution to galvanically isolating the perfboard from the steel chassis.

One-B-One Schematic

+--------------+                    +------------------+           +------------------+
|              |                    |                  |           |                  |
|              +--+LEAD1+----+OUT1+-+                  |VCC----+5V-+                  |
|              |                    |                  |           |                  |
| Motor 1      +--+LEAD2+----+OUT2+-+   DRV8830+A      +----GND----+  Buck Regulator  |
|              |                    |                  |           |                  |
|              |                    |                  |           |                  |
|              |                    |                  |           |                  |
+--------------+                    +-----+---+--------+           +--+--+------------+
                                          |   |                       |  |
                                      SDA1|   | SCL1               5V |  | GND
                                          |   |                       |  |
                                          |   |                       |  |
                                          |   |                       |  |
                                          |   |                       |  |
                                     +----+---+--------+              |  |
                                     |                 |              |  |
                                     |                 |              |  |
                        +----+VCC2+--+  ADUM1250ARZ    ++VCC1+--------+  |
                        |            |                 |                 |
                        |   ++GND2+--+                 ++GND1+-----------+
                        |   |        |                 |
                        |   |        +----+--+---------+
                        |   |             |  |
                        |   |         SDA1|  | SCL2
                        |   |             |  |
                        |   |             |  |
                        |   |             |  |
                  +-----+---+-------------+--+-------+

                            Raspberry Pi Zero W

The ADUM1250ARZ is a bi-directional galvanic isolator for digital communication up to 1mbs. It’s the first chip I ever designed a PCB for and it’s still my favorite. Essentially, the ADUM1250 seperates the rp0w from the noisy motors – and more importantly, if I screw something up on the motor side, won’t kill my rp0w. The ADUM1250 is not necessary for most people, just me

The last bit I had to figure out was the the Raspberry Pi’s power. I attempted to use a single Li-Ion battery and a boost regulator to power it, but the regulator’s I bought were DOA.

Then I remembered the load-sharing and boost converter circuit salvaged from a battery bank. The charge circuit was built for Li-Po chemistry and the only Li-Po I had lying about was a 350mA. I wired it up and was surprised the whole thing worked, with the added benefit of being able to charge the rp0w battery without disconnecting it. Booyah!

The last bit I did for the video. I pulled the npm package keypress and wrote this little program.

'use strict';
var i2c = require('i2c-bus'), i2c1 = i2c.openSync(1);
var sleep = require('sleep');
var drv8830 = require('./drv8830');
var keypress = require('keypress');

const motorAddressOne = 0x61;
const motorAddressTwo = 0x67;

var motor1 = new drv8830(motorAddressOne, i2c1);
var motor2 = new drv8830(motorAddressTwo, i2c1);

// var speed = 63;

var turnSpeed = 33;
var driverSideSpeed = 63;
var passangerSideSpeed = 63; 

// make `process.stdin` begin emitting "keypress" events 

keypress(process.stdin);
 
// listen for the "keypress" event 

process.stdin.on('keypress', function (ch, key) {  
  if (key && key.ctrl && key.name == 'c') {
    process.stdin.pause();
  }
  switch(key.name) {
        
    case 'w':
        motor1.drive(driverSideSpeed);
        motor2.drive(passangerSideSpeed);
        break;
    case 's':
        var motors = [motor1, motor2];
        setDriveWithAcceleration(motors, driverSideSpeed, 10);
        break;
    case 'd':
        motor1.drive(turnSpeed);
        motor2.drive(turnSpeed*-1);
        break;
    case 'a':
        motor1.drive(turnSpeed*-1);
        motor2.drive(turnSpeed);
        break;
    default:
        motor1.stop();
        motor2.stop();
  }

});
process.stdin.setRawMode(true);
process.stdin.resume();

var setDriveWithAcceleration = function(motors, desiredSpeed, accelTimeMilliSec) {
    for(var i = 0; i < desiredSpeed; i++){    
        motors[0].drive(i);
        motors[1].drive(i);
        sleep.msleep(accelTimeMilliSec);
    }
}

Then, I shot the following video and called it donesies.

Editing Raspberry Pi Code Remotely from Visual Studio Code

I’m spoiled. I love the pretty colors of modern text IDEs. My favorite among them being Visual Studio Code.

I know it’ll engender a lot of bad rep with the old-timers, but I prefer the one on the right.

However, when working on a headless (no monitor) Raspberry Pi it felt like I was pretty much stuck with the nano.

Until! I discovered Visual Studio Code’s remote extension.

This allowed me to edit my Raspberry Pi files from within Visual Studio Code. So, I get all the joys of writing code directly on my Raspberry Pi, but with all the bells-and-whistles of Visual Studio Code (VSC).

For the most part, setup is pretty straightforward. But the Pi side can get tricky, so I’m going to walk us through the process.

1. Get Visual Studio Code

Download the version of VSC for your PC. Note, you aren’t running this from the Raspberry Pi–instead, you’ll be running it from the PC and connecting it to the Raspberry Pi.

After it’s downloaded and installed open it up.








Once open, click here




Ok, now search for the extension called

Remote VSCode

And hit the Install button. Once it finishes hit the reload button.

The extension works by creating a server which listens for incoming calls from the Raspberry Pi. Once we finished setting up the Raspberry Pi we will use a special command which sends the file on the Raspberry Pi to Visual Studio Code. However, when it’s all done, it’ll look pretty seamless.

Back to setup.

In Visual Studio Code type F1 and type Preferences: Open Workspace Settings

Find the section labeled

remote.onStartup: false

We need to change it to true by clicking on the pencil next to its name. This sets the listening server to start every time you open Visual Studio Code.

Almost there. Now to setup the Raspberry Pi. We need to install a program on the Pi which will send a file of our choosing to Visual Studio Code to be edited. RMate was my choice.

Start by SSH’ing into your Raspberry Pi.

Then type

sudo pacman -S ruby
cd ~
gem install rmate
PATH="$PATH:$(ruby -e 'print Gem.user_dir')/bin"
echo 'PATH="$PATH:$(ruby -e 'print Gem.user_dir')/bin"' >> /etc/profile
echo 'export GEM_HOME=$HOME/.gem' >> /etc/profile

The above commands install Ruby, moves to to the user’s directory, uses the Ruby package manager to install rmate, then adds Ruby and it’s Gems (packages) executables to the environment variables. All of this is necessary to get Rmate working on Arch Linux.

Ok, let’s test it. Stop SSH’ing into your Pi by typing exit until it brings you back to your PC’s prompt. Now we are going to SSH into the Pi while listening for incoming files to be displayed in Visual Studio Code.

Open Visual Studio Code and open the integrated terminal (if it’s not showing hit CTRL + `).

At the terminal type

ssh -R 52698:localhost:52698 alarm@192.168.1.x

Replace the x with your Pi’s ip address.

This should SSH into the Pi while listening for files.

At the pi command prompt, type

rmate test.js

This should open a new file called test.js in your Visual Studio Code.

Now you get all the goodness of the VSC IDE, such as syntax highlighting, linting, etc!

A few notes. File permissions still apply, so if you want to be able to save a file the user you logged into on the Raspberry Pi and rmated the file must have write permission on the file.

However, if you do have write permissions, then the “File Save” function in the VSC editor will update the Raspberry Pi file with your modifications. Booyah!




One last annoyance to address. Whenever you want to use VSC to edit your file you have to log into the Pi using

ssh -R 52698:localhost:52698 alarm@192.168.1.x

This annoyed me a bit. I could never remember all that. Instead, I created a small bash script to help.

On my PC (for Mac and Linux, Windows, you’re on your own) I created in my home user directory called

vs

And added the following to the file.

echo $1
ssh -R 52698:localhost:52698 "$1"

Essentially, this script takes your Pi’s login information and logs in to your Pi using the VSC Remote Extension listening.

To get it to work you’ve got to make the file executable

sudo +x chmod vs

Then login in your Pi like this

./vs alarm@192.168.1.x

Hope you enjoy.

Oh, and for you web-devs, this also works for remote servers. Just replace the Pi with the server.

Porting DRV8830 I2C Motor Driver Code to NodeJS

Earlier in this article series I showed how to install NodeJS – it was pretty simple with an install script. However, I thought I better show how I actually worked with NodeJS to create my little 1b1 driver code.

Again, simple, I used others hard work. Specifically, Michael Hord with Sparkfun’s MiniMoto library.

Really, all I did was tweak the code a little bit to fit JavaScript syntax.

The result

'use strict';
var i2c = require('i2c-bus');
var sleep = require('sleep');

// Commands

const FAULT_CMD         = 0x01;

// Fault constants

const CLEAR_FAULT       = 0x80;
const FAULT             = 0x01;
const ILIMIT            = 0x10;
const OTS               = 0x08;
const UVLO              = 0x04;
const OCP               = 0x02;

// Direction bits

const FORWARD           = 0b00000010;
const REVERSE           = 0b00000001;
const HI_Z              = 0b00000000;
const BRAKE             = 0b00000011;

module.exports = class Motor {

    /** 1. Add "inverse" motor option
     *  2. Add option to clear fault on each motor call.
     *  
     */

    constructor(address, i2cbus, options = undefined) {        
        this.address = address
        this.i2cbus = i2cbus
        this.options = options
    }

    getFault() {

        var fault = {
            message: '',
            code: 0
        }
    
        var faultCode;
        try {
            this.i2cbus.readByteSync(this.address, FAULT_CMD);
        } catch (e) {
            console.log(`Read fault failed: ${e}`)
        }
        
        fault.code = faultCode;
    
        if (faultCode !== undefined) {
            console.log(faultCode);
            fault.message = 'Unknown fault.';
            switch (faultCode){
                case FAULT:
                    fault.message = 'Unknown fault.'
                    break;
                case ILIMIT:
                    fault.message = 'Extended current limit event'
                    break;
                case OTS:
                    fault.message = 'Over temperature.'
                    break;
                case UVLO:
                    fault.message = 'Undervoltage lockout.'
                    break;
                case OCP:
                    fault.message = 'Overcurrent lockout.'
                    break;
                default:
                    fault.message = 'Unknown fault.'
                    break;
            }
            return fault;
        } else {
            fault.message = 'No fault';
            return fault;
        }
    }
    
    clearFault() {
        var fault = this.getFault(this.address);
        if (fault.code) {
            try {
                var success = this.i2cbus.writeByteSync(this.address, FAULT_CMD, CLEAR_FAULT);
                if (success) { return true; }
            } catch (e) {
                console.log(`Failed to clear faults: ${e}`)
            }
        }
        return false;
    }
    
    drive(speed = 0, direction = undefined, checkFault = false) {
        // The speed should be 0-63.

        if (checkFault) { this.clearFault();}
        if (direction === undefined) {        
            direction = speed < 0;
            speed = Math.abs(speed);
            if (speed > 63) { speed = 63; }
            speed = speed << 2 ;
            if (direction) { speed |= FORWARD; }
            else           { speed |= REVERSE; }
        } else {
            speed = speed << 2 ;
            speed |= direction;
        }
        try {
            this.i2cbus.writeByteSync(this.address, 0x00, speed);
        } catch (e){
            console.log('Drive command failed.')
        }
    }
    
    brake() {
        try {
            this.drive(0, HI_Z);
        } catch (e) {
            console.log('Brake command failed.')
        }
    }
    
    
    stop() {
        try {
            this.drive(0, BRAKE);
        } catch (e) {
            console.log('Brake command failed.')
        }
    }
}

There’s a lot left to do, but it works.

Todo List:

  1. Have the constructor accept an options object
  2. Add read() to get the current speed which a motor is set.
  3. Refactor option to clear faults on write to be determined during construction
  4. Add acceleration and deceleration algorithms add functions.
  5. Create an async polling of fault codes.

But! For now it works.

Also, or those who are like, “You stole code, dewd! Not cool.” Mhord’s code has a beerware license. I sent this email to Sparkfun in regards to the license and how I might pay Sparkfun back for their work.

Hey Mr. Hord,

I’m in the process of porting your DRV8830 library to Node–I wanted to make sure I give appropriate credit.

https://github.com/Ladvien/drv8830

Also, was going to ship some beer to Sparkfun–in respect of the beerware license. Just let me know what kind.

Lastly, I wanted to make sure Sparkfun benefits. It looks like the DRV8830 TinyMoto board has been discontinued. > Should I recommend people roll their own…or gasp get something off a slow ship from China? —Thomas aka, Ladvien

But I didn’t hear back. C’est la vie

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.

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

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:

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.

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');