Making an API for a dataset using CouchDB

I’ve been working on a project to republish some interesting data (more later…) and had planned to use MySQL for storage. However, after figuring out the (somewhat horrid) data structure, I realised this would require >10 tables, and be generally horrible.

So, I wondered if there might be a better option. I started by asking the lazyweb if there was an easy, no-effort option. Unsurprisingly there wasn’t, so I didn’t make much progress — but then, we started using CouchDB for a work project, and it occurred to me that that might be a good fit. Queryable and efficient for a reasonably large dataset (>140k records) but with a flat, schema-free structure, and supporting revisions natively. Winning.

I installed CouchDB and managed to add some data to it, and view it. I also had a play with design documents, or views, or whatever the right term is (the nomenclature is a bit confusing). I understand MapReduce but I’m not sure how it’ll apply to this, if at all. In any case, this is my todo list:

  1. Display all the records in a gigantic paginated list
  2. Allow all the data to be downloaded in a giant, horrible CSV file
  3. Launch version 1!
  4. Allow records to be searched simply (keyword search over all the fields)
  5. Allow records to be searched complicatedly (with multiple conditions operating on fields separately)
  6. Allow records which are matched to be exported to a slightly less giant but still quite horrible CSV file
  7. [Maybe] Add other data formats
  8. [Maybe] Add email alerts and other useful bits to notify people of changes to things they care about

All of this should be easy, but since I’m finding CouchDB a bit confusing at the moment, I’m not sure it will be — at least, not until I’ve gotten over the initial hump of learning something new. This is as far as I got towards making some actual progress — the lightbulb moment being that I can pass ?key=, ?startkey= and ?endkey= straight into this query without having to do anything special:

  "_id" : "_design/example",
  "views" : {
    "by_gender" : {
      "map" : "function(doc) { emit(doc._id, doc.gender)}"

I did find some useful resources for CouchDB:

Beginning attempts at a PIC-powered LED die

I thought a good step up from the blinking LED would be to make a die with an LED display, having seen someone do something similar.

I wasn’t sure from that diagram how the PIC was driving the LEDs, but I am sure that a naive implementation using 7 outputs would be a bit wasteful. And dull. I wondered if  it might be possible to output a 3-bit binary number between 1 and 6 and use the external electronics to determine which LEDs should be lit, based on the outputs. This might not be the best way to do it but it seemed fun.

I started by writing out a table of inputs and outputs, assuming a die consisting of 7 LEDs  laid out like this:

Each LED has a number, making a table with 3 inputs and 7 outputs:

I1 I2 I3   O1 O2 O3 O4 O5 O6 O7
0  0  0    ?  ?  ?  ?  ?  ?  ?  INVALID
0  0  1    0  0  0  0  0  0  1
0  1  0    1  0  0  0  0  1  0
0  1  1    1  0  0  0  0  1  1
1  0  0    1  1  0  0  1  1  0
1  0  1    1  1  0  0  1  1  1
1  1  0    1  1  1  1  1  1  0
1  1  1    ?  ?  ?  ?  ?  ?  ?  INVALID

It’s pretty clear from this table that the problem is actually simpler, as many of the outputs mirror each other, and O7 mirrors I3:

I0 I1 I2   O1,6 O2,5 O3,4 O7
0  0  0    ?    ?    ?    ?  INVALID
0  0  1    0    0    0    1
0  1  0    1    0    0    0
0  1  1    1    0    0    1
1  0  0    1    1    0    0
1  0  1    1    1    0    1
1  1  0    1    1    1    0
1  1  1    ?    ?    ?    ?  INVALID

I was going to make a Karnaugh Map to simplify this further, but then realised that I’d have to make one for each output, which would be tedious, and that actually the simplifications are easy to spot anyway — all the more so because we can ignore outputs for 000 and 111 which the PIC will not generate.

O7 can be connected directly to I3, which is to be expected, since the middle LED only lights up for odd numbers. O2,5 is similarly simple, and can be connected directly to I0. O1,6 should be on when either I0 or I1 is high, and O3,4 should be on when both I0 and I1 are high. So: a couple of easy connections, an OR and an AND to implement! Neat.

I started sketching out a schematic for the circuit, but I don’t think I’ve got it right yet — either that, or my breadboard implementation is wrong.

I’m using 2 NPN transistors to form an AND gate for O3,4. I0 and I1 are both connected directly to O1,6, and I2 directly to O7. O2,5 is connected to I0 behind a diode, to prevent a high signal on I1 illuminating O2,5 via their shared connection on O1,6.

However: this doesn’t work properly. The transistor pair doesn’t stop the LED lighting up when I0 is low and I1 is high — the LED remains on, albeit dimmer. Also, the diode arrangement for O1,6 and O2,5 doesn’t work with the schematic above. When I replace the connection from I0 to O2 with another diode, it does work correctly. I also added D2 as an experiment — it doesn’t work without it — and I don’t really understand why that’s necessary either. I suspect voltage drop(s) are at fault, somehow.

I ended up going round in circles trying to get this working properly, and ran out of things to try. Am also not sure if this is the right basic approach or if I’m going down a blind alley. Might have another go at it another day, or perhaps start with a sucky 7-output wasteful (but functional) design, and iterate from there!

Edited to add: Obviously, now that I’ve simplified it down to 4 outputs, I wouldn’t need to make something with 7 outputs. Duh. And this seems like an awful lot of work just to have one less output. So I think this approach is a bit silly, and I’m just making work for myself. Next, I will try making something with 4 outputs, and get on with writing the PIC program. That said, it would be nice to understand why this circuit doesn’t work. Perhaps I’ll meet someone I can ask.

Starting out with a PIC programmer

Spent some time today trying to remember how to use the PIC16F873 ICs that we used at Uni – I have two knocking about still. I was roughly following this very useful tutorial.

The PIC programmer I built years ago still works — thankfully — so I installed MPLAB and WxPIC and tested the chips I have to see if they still work, which they did.

I took a 4Mhz crystal out of an old circuit and built the simplest thing I possibly could:

I’ve reconstructed that from memory, so it might not be completely right – but hopefully you get the picture. It’s using the crystal as a clock signal and an LED to indicate when RA1 is high. I haven’t used any pull-up resistors even though I know I should have (naughty).

I loaded this up with a very simple program (I’ve snipped out the boilerplate code from the MPLAB template file, see below for a full, updated file):

 bsf STATUS,5   ; Switch to Bank 1
 movlw 0x00     ; Make the Port A pins into outputs
 movwf TRISA
 bcf STATUS,5   ; Switch back to Bank 0
 movlw 0x2      ; Turn on the LED: put 0x00010 into W
 movwf PORTA    ; Turn on the LED: and then move W into port A

This worked first time, which was pretty excellent. So I decided to try and make the LED flash. I added some more code to turn the LED back off, and put a loop in between those calls to create a delay. Later, I added some more delay, and moved the loops into a subroutine. Here’s the full program.

Unfortunately, this didn’t work at all. I spent quite some time trying to debug the program, assuming that was at fault. I did find a couple of bugs, including one where I hadn’t disabled the AD convertor by putting 0x07 into ADCON1. This made me a bit suspicious that the LED lighting up the first time was a fluke, but I didn’t investigate.

After a while, I noticed that if I touched OSC1 with my finger, the circuit worked. And stopped working when I took my finger away. This made me suspicious that the clock signal was broken in some way. I thought I might have broken the crystal, so I tried to check it with my scope — I couldn’t see anything except noise, but that is perhaps to be expected.

I didn’t have another crystal, so I decided to replace it with an RC oscillator and see if that solved the problem:

I used this handy calculator to pick values for C and R — 33pF and 10K respectively. This does seem to be a different kind of oscillator but these values worked, so I wasn’t too worried.

As soon as I turned this on, it worked perfectly — hooray. The LED flashes quite quickly – at about 31Hz –  which I learned how to calculate here. With the scope’s time/div set to 2ms, the on period and off period were both 8 divisions long, making a duty cycle of 16 divisons, or 32 ms. f = 1/T = 1/0.032 = 31.25Hz.

Using the same method, I tried to calculate the frequency of the oscillator — but its waveform is quite different from the LED output’s square wave, and certainly not a sine wave, and not exactly triangular either — as expected for a capacitor charging up, it curves:

Scope trace for PIC RC osciallator

The results of this calculation also differ quite a lot from the handy calculator above, so something’s obviously squiffy. Probably my maths. But anyway. The duty cycle is 4.5 divisions long with time/div set to 0.5µs, making it 2.25µs long. f = 1/T = 1/0.00000225 = 444444.4Hz = 444Khz — whereas the handy calculator says I should be getting 1694Khz.

I think I need a second pair of eyes, preferably in the head of someone who knows what they’re talking about.

Finally, I found — but ultimately didn’t use — this document from Microchip about common PIC problems. I think it might come in handy another day though.



Since I’m spending some more time now working on fun-making-of-things projects, I thought I’d try and get into the habit of keeping a diary of what I’m doing. I’ve done this before for previous projects and it’s often been interesting/useful to look back over the things I was thinking about.

So, this isn’t really a blog. More of a diary. And we’ll see how it goes.