SQL::Functional Cookbook: ANDs and ORs

It seems like it should be easy, but I was always disappointed with how other solutions handle arbitrarily nested ANDs and ORs. Most SQL creation libraries seem to start by adding support for a list of AND statements. At some point, the authors realize they need ORs, so they slap that in. Much later, they realize they need to mix ANDs and ORs, and then all sorts of convolutions get written.

With SQL::Functional‘s approach, nesting ANDs and ORs together is as natural as straight SQL. First, we’ll back up a few steps and demonstrate the ANDs:

Notice that unlike SQL, the AND is a prefix rather than infix. This might take some getting used to, but it does mean you can pass it an arbitrary number of statements:

In the final SQL, all of these will be joined together with AND. The OR statement works the same way:

If we need to mix the two together, we just do that:

Thus, the nesting falls naturally out of the system, just like it should be.

SQL::Functional Cookbook: Simple Select

SQL::Functional now has reasonably complete converge of common SQL statements. There’s always going to be something else to add, but the main thing it needs now is a cookbook of standard cases. Along those lines, I thought I’d start up a series of blog posts that can be compiled into a complete cookbook.

We’ll start with a basic SELECT statement.

One of SQL::Functional‘s strong points is in making easy jobs easy. Its other strong point, making hard jobs possible, will be covered later.

Let's break down what’s actually going on. The SELECT function takes a scalar for all the fields, so we have to pass it as an arrayref. FROM takes a list of tables, which we might do for joins, but we'll just pass it one for now. WHERE takes a list of clauses.

Ideally, we could say something like:

But that would require deep Perl voodoo (e.g. source code filters), so we content ourselves with the match function to build the same thing. It also helps us here by having the matched data passed as a placeholder.

In the end, $sql will contain:

With @sql_params containing the data for all the placeholders. We can run this through DBI like any other statement:

Easy.

Callback Functions Between C and Perl

Everything you ever want to do with xs is documented somewhere in perlxs, perlguts, perlapi, perlxstypemap, and perlcall. Figuring out where it’s documented, and how it relates to everything else, is the hard part.

In my case, the part I wanted was in perlcall, in the section “Strategies for Storing Callback Context Information”. What I wanted to do was to glue together the callbacks for the Perl interface in Games::Chipmunk to the Chipmunk 2D physics library.

From Perl, we want to be able to say things like this:

The C version of cpBodySetVelocityUpdateFunc() does not know what a Perl sub is. It sees an SV, which happens to contain a bunch of things that the Perl interpreter can execute as a Perl sub. What we need to do is hand off a C function to the callback, and then use that to grab our SV and use call_sv() to call it.

If C supported closures, this would be easy. C does not support closures.

Some C libraries with callbacks have a parameter that will pass whatever data you give it directly into the callback later on. Chipmunk has this on a few functions, but not consistently.

One thing the Chipmunk libraries do give us is a pseduo-Object Oriented interface, where we pass the associated datastructure in as the first parameter on every function (Object Oriented languages move this parameter to the left of the function call). This gives us something we can grab on to for getting the SV we need that stores our Perl sub.

The “object” is a pointer, and pointers are just numbers. Numbers can be looked up in a hash. So we’ll make a bunch of global hashes, one for each set of callbacks, and use the address as the lookup key and the SV as the value.

The examples below use Perl’s context macros, which means those global hashes are still thread-safe. You can read about them in perlxs, under the section “Safely Storing Static Data in XS”. Their use won’t be detailed here.

Using cpBodySetVelocityUpdateFunc as an example, we start with the xs declaration:

We’re taking an SV* and assuming it holds a reference to the sub. We store it in the HV* bodyVelocityFuncs, which is initialized elsewhere. Using (char*)&body (C is a true bastion of type safety), we convert the address of the cpBody* into a char pointer, which the hash can use as a key. Lastly, we call the actual cpBodySetVelocityUpdateFunc() in the C library, and pass it our own C function as a callback.

That C function looks like this:

This goes at the top of your xs file, before any PACKAGE declarations, up with #include "EXTERN.h" and such. The perlxs doc doesn’t show this part in its examples very well, but you need to put the dTHX; call here to declare the context for a bunch of Perl macros that come later. Otherwise, you’ll get a bunch of cryptic compiler errors and spend a few hours scratching your head, until you finally come across the section in perlguts entitled “How multiple interpreters and concurrency are supported”. Like I said, everything is documented, you just won’t know where.

Going past the Perl macros, we get to hv_fetch(). This function should have gotten the same cpBody* as we got earlier, so we once again torture the type system and pretend it’s a pointer to a char array for the sake of a hash lookup key. The person who wrote this part of the interface was a two-star C programmer, so we better check that we actually got the SV we wanted by checking perl_func == (SV**) NULL.

Now we’ll need to grow the stack so we can push the subref’s arguments there. In this case, the callback received four arguments, and we want to pass all four to the subref. In this particular case, the last two arguments are easy. They’re double precision floats, which we can pass directly into a Perl SV that contains a number.

The first two are the tricky ones. They’re pointers to complex structs. In my typemap, I made cpBody* into a T_PTROBJ. This means it converts the ‘*’ into ‘Ptr’, collapses any whitespace, and uses the resulting name as the Perl class. You can make an SV contain a pointer to this C object and then carry it around in Perl. The cpVect* comes out the same way (since we take a pointer to the gravity struct).

Finally, we can call call_sv() to call the actual subref.

The good news is that Games::Chipmunk v0.3 has most of the callbacks implemented. There’s some in cpSpatialIndex that aren’t there because they don’t follow the same conventions. Still, this should be good enough to write real physics systems for games in Perl.

Adventures in Code Generation — Graphics::GVG

Vector graphic games, like Battlezone or Asteroids, are old favorites of mine, and I’ve been wanting to make a game with that same style. Partially, that’s because I’m not that artistic, and it’s easy to make the style look cool. Just make everything come together at hard angles and let it go.

I considered SVG for the job, and leaned towards it for a while just for the sake of not falling into Not Invented Here. The problem is that SVG is incredibly complicated, especially for rendering. In a game where I’d likely be writing my own rendering of the SVG standard, I just didn’t want to do it. What’s more, for any kind of complicated effect, I’d probably have to use CSS, which is a second really complicated standard to implement.

So I went off to do it myself. When I inevitably end up having to reimplement some feature of SVG, I’ll just live with that.

Anyway, this led me to make Graphics::GVG. It uses a simple scripting language (parsed by Marpa::R2) to define how to draw your vector art:

The drawing commands inside the glow { ... } block will be rendered with a glow effect. Exactly what that means is up to the renderer.

There’s an included OpenGL renderer, which is where the real fun starts. The script above would be parsed into an Abstract Syntax Tree (AST), and then renderers compile that into the system of their choice. In the OpenGL case, it compiles the AST into a Perl package, which has a draw() method that does a series of OpenGL functions.

For example, a simple rectangle GVG script:

Gets turned into the Perl code below by the OpenGL renderer:

Which isn’t going to win any formatting awards, but it’s not meant to be edited by humans, anyway.

The above gets returned as a string, so you can compile it right into the running program using eval(STRING) (see Dynamic Code Loading for why you shouldn’t be afraid of this sort of thing). Alternatively, you could save it as a .pm file and load it up that way.

Either way, you get yourself an object from that package with new(), and then call draw() on it for each frame.

The generated code could be improved, for certain. For performance, it’ll probably move to vertex buffers. There should also be a way to make predictable package names rather than the UUID. If the overhead of calling all those OpenGL functions ends up being an issue, it could be compiled to a C function that can be called from XS.

In the future, there will be other renderers, which I hope can combine to output one set of package code. Meaning you would call $obj->draw_opengl for the OpenGL renderer, or $obj->init_chipmunk to setup the geometry for the Chipmunk2D physics library.

New module: SQL::Functional

If you have an application with simple database needs, writing the SQL strings directly in the program works fine. If it’s bigger, an Object-Relational Mapper like DBIx::Class starts to make sense. I find the additional boilerplate of an ORM isn’t worth the effort for simple needs, but it doesn’t take much more for the overhead to be worthwhile.

Then there’s another point beyond that where the ORM doesn’t know how to effectively and efficiently run a request. When you get there, it’s time to write raw SQL again, perhaps giving the result set back to your ORM so you can continue on as if nothing happened.

How do you write that SQL? Probably with strings again, or a helper library like SQL::Abstract.

The problem with raw strings is brought up in Epigrams in Programming:

  • It’s difficult to extract sense from strings, but they’re the only communication coin we can count on.
  • The string is a stark data structure and everywhere it is passed there is much duplication of process. It is a perfect vehicle for hiding information.

The nature of our database problem is that we’ve hit on a very complicated case, and now we’re stuck using the most ad-hoc way of structuring data. Not a good combination!

SQL::Abstract tries to address this by providing a structured way of building a string. We still have to output a string (” . . . they’re the only communication coin we can count on”), but at least we don’t have to deal with it directly. The problem is that, again, we’re getting to the most complicated cases of SQL, and SQL::Abstract abuses syntax tricks for these cases. Consider this case I pulled out of its docs:

Why are we taking references to scalars? And references to arrayrefs, for that matter? Could you copy-and-paste this to a DBA and explain what’s going on?

When you drill down a bit, the reason for these issues is that SQL::Abstract uses an Object Oriented interface to build this string, and OO is just not a good fit for this problem.

On the other hand, the problem can be solved more cleanly by combining functions that look vaguely like SQL:

This is exactly what SQL::Functional does. Version 0.1 is now up on CPAN.

New Module: Linux::IRPulses

I’ve been playing around with Infrared Remote data lately. I wanted to use LIRC to parse the data, but the problem was that I was working with a lot of custom IR protocols, and I didn’t want to use the usual LIRC data flow of mapping codes to buttons to executable programs. If you want the usual way, try Lirc::Client.

Instead, I decided to parse the pulse/space data right out of the “mode2″ program. This is what’s done by Linux::IRPulses.

This will probably change at some point to parsing data directly from /dev/lirc*, but that will require a few C bindings.

Programming Quadcopters, Part I

Video of my presentation for the Madison Perl Mongers about programming quadcopters. Part II will be given for the Millwaukee Perl Mongers on March 16th.

The quality isn’t up to snuff. I grabbed an action cam with a fisheye lens and a microphone that isn’t meant for this sort of thing. The brightness of the projector washes out the image of most of the slides. I’m putting it up for posterity, but I think I’ll redo it at home so people who weren’t there can tell what the hell is going on.

Low Latency FPV Streaming with the Raspberry Pi

One of the biggest challenges in running my local quadcopter racing group has been overlapping video channels. The manual for the transmitters list a few dozen channels, but in practice, only four or five can be used at the same time without interference.

The transmitters being used right now are analog. Digital video streams could make much more efficient use of the spectrum, but this can introduce latency. Of late, I’ve been noticing a few posts around /r/raspberry_pi about how to do an FPV stream with an RPi, and I’ve been doing some experiments along these lines, so I thought it was a good time to share my progress.

It’s tempting to jump right into HD resolutions. Forget about it; it’s too many pixels. Fortunately, since we’re comparing to analog FPV gear, we don’t need that many pixels to be competitive. The Fatshark Dominator V3s are only 800×600, and that’s a $600 set of goggles.

You’ll want to disable wireless power management. This will tend to take the wireless interface up and down a lot, introducing a delay each time. It’s not saving you that much power; consider that an RPi takes maybe 5 watts, while on a 250-sized quadcopter, the motors can easily take 100 watts or more each. So shut that off by adding this anywhere in /etc/network/interfaces:

And reboot. That should take care of that. Check the output of iwconfig to be sure. You should see a line that says “Power Management:off”.

You’ll want to install GStreamer 1.0 with the rpicamsrc plugin. This lets you take the images directly off the RPi camera module, without having to use shell pipes to have raspivid to go into GStreamer, which would introduce extra lag.

With GStreamer and its myriads of plugins installed, you can start this up on the machine that will show the video:

This will listen on UDP port 5000, waiting for an RTSP h.264 stream to come in, and then automatically display it by whatever means works for your system.

Now start this on the RPi:

Modify that last line to have the IP address of the machine that’s set to display the stream. This starts grabbing 640×480 frames off the camera with h.264 encoding, wraps them up in the RTSP protocol, and sends them out.

On a wireless network with decent signal and OK ping times (80ms average over 100 pings), I measured about 100ms of video lag. I measured that by displaying a stop watch on my screen, and then pointing the camera at that and taking a screenshot:

rpi_cam_latency

This was using a RPi Model 2, decoding on a fairly modest AMD A8-6410 laptop.

I’d like to tweak that down to the 50-75ms range. If you’re willing to drop some security, you could probably bring down the lag a bit by using an open WiFi network.

I’ll be putting together some estimates of bandwidth usage in another post, but suffice it to say, a 640×480@30fps stream comes in under 2Mbps with decent quality. There will be some overhead on that for things like frame and protocol headers, but that suggests a 54Mbps wireless connection will take over 10 people no problem, and that’s on just one WiFi channel.

New Module: Device::Spektrum — Control Common RC Quadcopter Flight Controllers

I’ve been wanting to put a Raspberry Pi on a quadcopter ever since I realized that the AR.Drone was too limited and poorly implemented.

It’s a bad idea to run everything off a Raspberry Pi running Linux. There are a lot of fine tuning PID-related adjustments every fraction of a second, and Linux could potentially delay the running process and cause you to crash (in the physical sense as well as the computational sense). So you need a flight controller like the Naze32, MultiWii, or AeroQuad that takes care that stuff in a dedicated processor.

How to communicate with a flight controller? The typical quadcopter sends PPM or Combined PPM to the FC. PPM sends pulses of a specific length, with one wire for each channel. Combined PPM is the same idea, but sends everything together and only needs one wire total. This is the oldest, most analog way of sending RC signals, coming from the days of brushed motors, NiCad batteries, and 27MHz radios with ten foot long antennas.

PPM/CPPM won’t work with a Raspberry Pi with Linux, either, for similar reasons as above. A delay in the timing of the signal throws the whole system off. It has been done on Arudino and Teensy.

The modern way is to use a digital serial protocol. One of these protocols is called Spektrum, and it’s implemented on the Naze32 FC, among others.

The Spektrum protocol has been implemented in Perl with Device::Spektrum. Should be hitting the CPAN mirrors shortly.