Unit Testing Databases

Ok, so maybe in a perfect world databases would simply be for storing data. In the real world, however, tons and tons of business logic has been shoehorned into stored procedures, views and functions.

So how should the pragmatic coder approach properly testing these guys?

The right answer lies in basic TDD methodology. Define the functional requirements for the procedure. Write a test for the procedure, then write the procedure itself. Part of the functional requirement should be a listing of all external objects (tables, views, other procedures) that the object uses. If the object suddenly, say, joins to another table which is not in that list - the test should fail, as the code does not meet the functional design any longer.

To enforce this, the best approach is to create a sandbox database, populate it with only the objects the stored procedure depends on - mocking those objects if need be - and running the stored procedure.

To illustrate: let's say we have a stored procedure "sp_GetPhoneNumber". This procedure takes a last and first name as an argument, and returns a phone number. Now let's imagine the database structure has a table called "contact_info", which contains columns with lastname, firstname, and phone number. Lets further imagine this contact_info table has a foreign key constraint on another table, called customers.

We then say the functional design of "sp_GetPhoneNumber" will be something like "selects a record in contact_info by matching on the columns "lastname" and "firstname", and returns the data in the column "phone_number". From this simple design, we see there is only one external reference needed - "contact_info".

Now, if we approach this as testing against a full "live" database, it means we would need to populate "customer". Maybe customer has a foreign key constraint on "account", we need to populate it, and so on, and so on. If the schema of "account" is changed now, the unit test will fail - even though we don't give a flying fig about it. If somebody comes along and dinks with the stored procedure, that references some other table - the test will pass, even though it no longer meets its design requirements.

Now, on top of that, lets imagine this database we're testing against is pre-populated with helper "baseline" data. Now we're really only testing in a perfect world scenario. What if there are no records in the contact_info table? It doesn't get tested. In this scenario, we end up testing the "baseline test data population" scripts, not so much the code itself. So you basically just pat yourself on the back knowing that your code works under ideal, and static, conditions. Hoorah.

A better approach for the above would to be to create a scratch database, create only the object "customer_info", without any foreign constraints. We could create it as a view which just spits out constant values, the stored proc doesn't care - it only wants a recordset returning object with the expected interface (in this case, the interface is the column names, and data types). Changes to unrelated schema don't affect the test, and the introduction of new objects to the stored procedure break the test.

What if this proc inserts into a table with a trigger on it, you ask? Well, the trigger is a separate unit of code, and should be tested separately.

It boils down to a philosophy of a test only being able to access the data and dependencies it needs.

One could take the "mocked objects" approach to the next level, and set up a expected/actual type of situation. The table could be mocked by a view, which upon execution, records in some metadata table somewhere when it was called. If the stored proc calls other functions or stored procs, they could be mocked to maybe return a constant, and log how and when they were called. They could be implemented entirely, with the logging code tacked on. The point is, these call-out stored procs aren't under test.

You don't run the entire application from it's entry point, sending keystrokes and mouse clicks, to unit test a particular function call in a particular assembly - you mock the objects that assembly uses, set up your expectations, and compare them to the results of the call. Why would you treat business logic on the database any differently?

As a side rant, people tend to look at failing unit tests as a bad thing. They're supposed to fail, they should be as fragile as you can possibly make them. If anything changes to affect the functional design of the code under test, it should break. If the metric managers use to gauge unit testing is "number of passing tests written that always pass no matter what happens", they really end up with a bunch of clever permutations of "Assert.IsTrue(MyFartsDontStink)" In a project of any complexity, it should be nigh impossible to hack around shooting from the hip without breaking a bunch of unit tests.

At least that's how I roll.

Nintendo news: I don't really have any, I've been screwing around with the MAME code wrt reading the instruction EPROM on the playchoice 10 board, hopefully I'll be able to program my own instruction screen and game title soon, so it will show up as "NES Cart" or something on the menu. I'm comitted to making it work without any changes to the board roms, or BIOS, if it's fair to call it that (it's not). I haven't abandoned the project, so be patient PC10 fans.

eBay is all a bunch of jerks

Rant:

I bought a lot of "untested" neo geo 1 slot boards last year, and finally got around to screwing with them. Now, we all know that untested is code for broken, because how do you prove its untested? Well, let me tell you how. This pinwheel who sold these to me obviously tried to fix them, and is an even worse solderer than I am.

A resistor pack is missing from one board, both main power filtering capacitors are missing, and it's laughably obvious that he tried to desolder the upright cartridge riser on this one and just got sick of it and decided to unload them.

Well guess what, jokes on you buddy. I see how you screwed up a couple traces, trying to add a capacitor to "fix your startup issue", but desoldering the whole thing? Here's a pro tip: check the roms first, the bios was dead - you didn't need to go butcher at all. 15 minutes from someone with a clue and this one works fine.

I think if you sell something as "untested" it should at least imply that it hasn't obviously been screwed around with by an idiot.

End Rant, on to stuff:

First, Neo Geo news.. I got two of these boards working, and one left. I got so excited about Neo Geos that I bought a 6-slot board on eBay, to upgrade from the 2-slot in my cabinet. I got more Neo Geos than any one man could possibly need. I'll unload the single slots, I have no use for them. I don't know if I'll part with my 2-slot.

My PC10 project is on hold while I wait for some components. Everything works, and for the most part a NES cart coexists peacefully with regular games, but not all of them do. I'm going to try and scratch build an adapter, and mess with running certain lines through a switch, and using a solid state relay to power the cart when it's selected. It may never be perfect, NES games were never designed to be sharing a bus with other NES games. Maybe the only really right way would be a whole assload of buffer drivers, or 4066 switches.

I've also been playing with PCB designs for an adaptor I could have made. I still promise to post a pinout once I've figured out the last couple of issues, and if I get to the point I can have some made, I hope to be able to do it for about 50 bucks a unit, though thats more of a target than any sort of promise.

Some games will always look weird, the PC10s PPU handles some color codes a little differently, the emphasis bit, I beleive, the ones programmers weren't really supposed to use but did.

This also lead me to discover that some PC10 graphics have adjusted pallettes, because the intro screen to Mike Tyson Punch Out NES cart looks like a plate of diarreah on the PC10, but the PC10 game pak looks right. It's not a big deal, it's mostly noticable when mega man is a bright baby blue, instead of his usual mega-man blue. Most games look much better with a true RGB pallette.

As for decoding the instruction prom, I've made a little success... I think, I've ordered a couple serial eproms to test a theory.

I figured something out, though. The PC10 single board version I have, has the z80 side outputting inverted RGB natively, the NES outputs RGB natively, and it is inverted on board. The three pot's on board are for the inverter on the NES side, which looks like a typical inverting amplifier op-amp circuit. The signals are switched via 4066's, and I hope to see if I cant strap the z80 side to the inverter, the nes side to output natively, and thus have a RGB native PC10. I'd much rather not have the NES video signal mussed with thus.

I looked into/at the VS sytem a little more. There are two nes-like systems onboard, and it can output to two monitors simultaneosly. The two boards can share a RAM through an interrupt somehow, this feature was used by baseball and tennis, maybe others. Most single-game boards only have one side populated, but one could populate both, and rig a 4066 based board to switch whats on-screen.

My board had a hidden find, a 2A04 CPU is socketed on the empty side, the normal nes CPU being the 2A03. Someone probably switched game chips, and forgot to pull it.

This perhaps solves my Duck Hunt mystery, that is, why Duck Hunt eproms play with screwed up sound. The VS games used some different PPUs, with different color pallettes. This was partially the copy protection, and is well known. I had read the Duck Hunt sound issue as having to do with the custom PPU, but it never made sense. The PPU's don't make sound, the CPU does. I now know there were at least two CPUs used, and I'm guessing Duck Hunt uses this one. I have everything I need to thus test my Super Mario / Duck Hunt board as soon as I burn some eproms for it. I'll steal the PC10's PPU for the test, since it's the same pallette Duck Hunt used, and try it with the 2A04 I have. It'll be Duck Hunt and Super Mario Bros hooked to a pushbutton switch, and if I'm feeling froggy one day maybe I'll bankswitch some other games on there. Ice Climbers can share space with mario, and excitebike (I think) can live with Duck Hunt.

I fiddled with my Donkey Kong board, I put in a new Z80 and at least this one doesn't get hot to the touch, but still no joy. I dumped all the eproms and checked them, except for the two 2532s. I can't read those directly, but I should be able to repin a socket to a 2732, and at least be able to read it, though probably not write them. I should be getting something onscreen, even without them, though. There's a 74193 near the CPU on there that looks munged, it's a good candidate for replacement.

That reminds me, here's a tip for willem design eprom pcb owners: disable windows plug n play while dumping roms. It can poll for printers, and when it does it can set VPP (programming voltage), and you can actually be programming the devices you think your reading, and that could suck. It's also good practice IMO to run the hardware test, since it'll clear the ports, and make sure the VPP led isn't lit up. I don't think you can kill eproms this way, but I'm sure other things you could.