Programming

A while ago I purchased a copy of BlogPress for my iDevices.. It's pretty full-featured, and seems to work pretty well. Problem was, I couldn't get it to work with my Serendipity-based blog. Oh well, a wasted purchase.

But not so fast! Every once in a while I go back and search for a possible solution. This past week I finally hit paydirt. I came across this post on the s9y forums.

This explained why BlogPress was crashing when I used it. In short, it was expecting to see a categoryName tag in the resulting XML from the Serendipity XMLRPC plugin. Serendipity, however, used description instead, likely because Serendipity has better support for the MetaWeblog API.

Fortunately, fixing this problem is very straightforward. All you really need to do is implement both APIs and return all of the necessary data for both APIs at the same time. To fix this particular problem, it's a single line addition to the serendipity_xmlrpc.inc.php file located in $S9YHOME/plugins/serendipity_event_xmlrpc. That addition is as follows :

if ($cat['categoryid']) $xml_entries_vals[] = new XML_RPC_Value(
    array(
      'description'   => new XML_RPC_Value($cat['category_name'], 'string'),
      // XenoPhage: Add 'categoryName' to support mobile publishing (Thanks PigsLipstick)
      'categoryName'  => new XML_RPC_Value($cat['category_name'], 'string'),
      'htmlUrl'       => new XML_RPC_Value(serendipity_categoryURL($cat, 'serendipityHTTPPath'), 'string'),
      'rssUrl'        => new XML_RPC_Value(serendipity_feedCategoryURL($cat, 'serendipityHTTPPath'), 'string')
    ),
    'struct'
);

And poof, you now have the proper category support for Movable Type.

I have a brain teaser for ya.. I'm looking for a way to solve a SQL problem efficiently, specifically using MySQL. The goal is to get a count of the number of unique rows returned for a complex query. It's actually for a pagination system so I can determine the limits necessary to efficiently query the database for the right amount of data rather than return everything and try to brute force it.

Let's say I have three tables as follows :

mysql> describe person;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| first | char(15)         | YES  |     | NULL    |       |
| last  | char(15)         | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> describe interests;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   | PRI | NULL    |       |
| interest | char(15)         | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe interest_link;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| person_id   | int(10) unsigned | NO   |     | NULL    |       |
| interest_id | int(10) unsigned | NO   |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Simple enough. I'm mapping interests to people. I've entered data into these tables as follows :

mysql> select * from person;
+----+-------+----------+
| id | first | last     |
+----+-------+----------+
|  1 | John  | Doe      |
|  2 | Bob   | Jones    |
|  3 | Joe   | Smith    |
+----+-------+----------+
3 rows in set (0.00 sec)

mysql> select * from interests;
+----+-----------+
| id | interest  |
+----+-----------+
|  1 | Computers |
|  2 | Music     |
|  3 | Food      |
|  4 | Beer      |
|  5 | Gaming    |
+----+-----------+
5 rows in set (0.00 sec)

mysql> select * from interest_link;
+-----------+-------------+
| person_id | interest_id |
+-----------+-------------+
|         1 |           1 |
|         1 |           2 |
|         1 |           4 |
|         2 |           1 |
|         2 |           5 |
|         2 |           4 |
|         3 |           3 |
|         3 |           2 |
|         3 |           4 |
+-----------+-------------+
9 rows in set (0.00 sec)

So far, so good. Now, I want to do a search to find which users are interested in music. Simple enough search, I'd do this with a simple select statement as follows :

mysql> select * from person as p left join interest_link as il on il.person_id = p.id where interest_id = 2;
+----+-------+--------+-----------+-------------+
| id | first | last   | person_id | interest_id |
+----+-------+--------+-----------+-------------+
|  1 | John  | Doe    |         1 |           2 |
|  3 | Joe   | Smith  |         3 |           2 |
+----+-------+--------+-----------+-------------+
2 rows in set (0.00 sec)

But what if I want to find out who's interested in music *and* beer?

mysql> select * from person as p left join interest_link as il on il.person_id = p.id where interest_id in (2,4);
+----+-------+----------+-----------+-------------+
| id | first | last     | person_id | interest_id |
+----+-------+----------+-----------+-------------+
|  1 | John  | Doe      |         1 |           2 |
|  1 | John  | Doe      |         1 |           4 |
|  2 | Bob   | Jones    |         2 |           4 |
|  3 | Joe   | Smith    |         3 |           2 |
|  3 | Joe   | Smith    |         3 |           4 |
+----+-------+----------+-----------+-------------+
5 rows in set (0.00 sec)

That's a problem, now I have 5 rows.. How do I make this a unique list? Well, I'm merely interested in names and ids, so I can do this :

mysql> select p.id, p.first, p.last from person as p left join interest_link as il on il.person_id = p.id where interest_id in (2,4);
+----+-------+----------+
| id | first | last     |
+----+-------+----------+
|  1 | John  | Doe      |
|  1 | John  | Doe      |
|  2 | Bob   | Jones    |
|  3 | Joe   | Smith    |
|  3 | Joe   | Smith    |
+----+-------+----------+
5 rows in set (0.00 sec)

but that's still 5 rows.. so what now?

mysql> select distinct p.id, p.first, p.last from person as p left join interest_link as il on il.person_id = p.id where interest_id in (2,4);
+----+-------+----------+
| id | first | last     |
+----+-------+----------+
|  1 | John  | Doe      |
|  2 | Bob   | Jones    |
|  3 | Joe   | Smith    |
+----+-------+----------+
3 rows in set (0.00 sec)

Aha! perfect. That's what I need.. almost. For this particular application, I want to paginate, so I need a total number of matching rows so I can properly identify the limits as well as the upper bound on page numbers. So, I'll just replace the specific field names with a count(*) :

mysql> select distinct count(*) from person as p left join interest_link as il on il.person_id = p.id where interest_id in (2,4);
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

And here is where I'm stuck. I need the total count of DISTINCT names, not the total number of rows returned. I tried a GROUP BY, but that didn't help much :

mysql> select count(*) from person as p left join interest_link as il on il.person_id = p.id where interest_id in (2,4) group by p.id;
+----------+
| count(*) |
+----------+
|        2 |
|        1 |
|        2 |
+----------+
3 rows in set (0.00 sec)

Sure, I get 3 rows, but what I'm looking for here is a single row with the total number of items. ... So, what if I count the number of returned rows! :

mysql> select count(*) from (select count(*) from person as p left join interest_link as il on il.person_id = p.id where interest_id in (2,4) group by p.id) as foo;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

BUT... at what cost? This seems like a rather complex query that might break down, significantly, when there's a lot of data.. And the examples above are rather simplistic. In reality, we're talking about more fields and more tables, so the simpler query gets a little complex to begin with. I'm open to ideas on how to do this properly via SQL. Yes, I am aware of indexing and how that speeds things up. I use indexing, I just eliminated it from the above example to simplify things. I'm open to ideas on how to do this properly via SQL.

I can simply return all the rows with the distinct clause, count them programmatically, and then proceed with the rest of the program, but depending on the selections made by the user, there could be a significant amount of data returned. I'm worried about both memory exhaustion on the part of the scripting language, as well as the processing and transmission time required to pass all of that data back to the program from the SQL database. Besides, this is the sort of problem that SQL was designed to solve.

I don't think this is a unique problem, so someone out there has a solution. Perhaps the subselect *is* the better solution, but I don't think so. I'm open to ideas. You can leave a comment here, or hit me up on twitter.

Apparently I'm always the last to know... But.. I found out today that phpTodo, the todo list manager I wrote (and continue to write) has been included in Fedora. In fact, it seems it's been in there since Fedora 7. It's not in the main distribution, nor should it be, but apparently it's a maintained package. Thanks, Marc!

Honestly, I'm truly honored. I wrote this on a whim and it has served me well. I use it every day! And since writing it, I've had a handful of people make suggestions and offer patches. I think it's been pretty successful for a small project.

So, how about an update? Well, I've been working on phpTodo in my spare time, which, unfortunately, has been relatively lacking as of late. I have been able to add in a number of fixes and new features, however. The biggest change in the next release will be the removal of categories in favor of having tags. In using phpTodo over the years, I've noticed a number of times where I'd like to be able to put an item in multiple categories, and display multiple categories at once. While this may have been possible with categories as they were implemented, I think tags works a bit better. I've borrowed an idea from the Serendipity blogging platform to implement tags in a user-friendly manner, so I think the implementation works pretty well. I still have some more work to tie it all together, but it is coming along.

Another change is the addition of the Prototype and Scriptaculous javascript frameworks. There are a few reasons I decided to go this route. First and foremost, it significantly reduces the amount of work necessary to perform cross-platform javascript operations. To date, I've used relatively simple javascript functions, mostly for front-line input validation, but with the addition of tags, I wanted to move into some more advanced techniques. I'm striving to keep it simple and not overdo it, so don't worry.

And, of course, there are the various bug fixes that need to be added. Overall, I'm excited about the next release of phpTodo. I don't have a timetable as of now, but I'm hopeful that my free time will increase shortly, giving me more time to work on it. If so, then I'm optimistic about a new release sometime in the next 3-4 months. We'll see what happens.

If you're using phpTodo, I'd like to hear from you. I'm interested in what you like and what you dislike about the program, the interface, the workflow, etc. What features would you like to see? What features would you hate to see?

Thanks!

I've been using CVS for a number of years and it has served me well. I have looked at Subversion a number of times, but never really had the time to deal with it. That has changed somewhat and I have had the chance to use SVN a bit more recently. SVN feels a bit more elegant, and, in most cases, faster than CVS. But, I'm also having a bit of trouble as well. Perhaps someone out there can provide me with some insight into my problems.

Most, if not all, of my recent coding has been in languages such as Perl and PHP. Additionally, I mainly code alone, so my use of a revisioning system is purely for historical data rather than proper merging. I also use CVS to handle updates of deployed code. This alone has proven to be the strongest reason to continue using a revisioning system.

With CVS, I develop code until I'm ready to deploy it. At that point, I tag the current revision, usually with a tag of RELEASE. Code is then deployed by checking out the code currently tagged as RELEASE. From here, when I update the code for a new release, I use the -F flag to force the RELEASE tag onto the new code. A simple cvs update handles updating the deployed code to the latest release. If the deployed code was changed for some reason, as sometimes happens, CVS handles merging and I can make and necessary adjustments. Overall, this has worked quite well for some time. There are hiccups here and there, but overall it has been pretty flawless.

Recently, I used cvs2svn to convert my existing CVS repositories over to SVN. After some false starts, some research, and a few minor headaches, I have all of my code converted over to SVN. I was able to get websvn running as well, which is a nice change as I can browse the repositories freely. I started playing around a bit and noticed that all of the imports have three additional directories, trunk, tags, and branches. More research and I discovered that SVN doesn't handle tags the same way that CVS does... This concerned me as I used tags pretty heavily for deployment.

So now we come to my problem. I have identified how to create new tags using svn copy. This works great for the first copy of a given tag, but it breaks down when updating a tag. A second copy fails because the files already exist. I can use svn delete to remove the files before copying the new ones, but that's an additional step I have no desire to do. After all, the purpose of moving to SVN is to make life easier, not harder.

After some more reading, I find that I can merge releases. Presumably, I can check out the tagged version and then merge changes from the trunk version. However, this is still more complicated as I have to merge the code and then commit it back to the repository. So, again, we have more steps than I want to deal with.

I think I understand the reason behind not being able to copy twice. I'm also aware that the way I was using CVS was fairly non-standard, but it worked for me. The code base I normally worked on could have multiple features I'm implementing at any given time, and deployment of one feature may get prioritized. So, merely copying the base to a new tag doesn't quite work as not everything in that code may be complete at a given time.

So what are my options here? SVN has some advantages that I really like, including the web view and better handling of authentication and permissions. However, being unable to re-tag is kind of a pain. One way or another, I think I'll be using SVN anyway, but I was kind of hoping to find a decent way to handle everything... Anyone out there have any suggestions?

John Carmack is something of an idol to me. He's an incredible programmer that has created some of the most advanced graphical games ever seen on the PC. He also dabbles in amateur rocketry with his rocketry company, Armadillo Aerospace, whom I've written about before.

I joined the Amateur Rocketry mailing list a couple years ago. The aRocket list is a great place to read about what's going on in the amateur rocketry scene. The various rocket scientists on the list openly discuss designs, fuel mixtures, and a host of other topics. There's also a lot of advice for both those getting into the game, as well as those who have been in a while.

Recently, John posted a note about the Rocket Racing League and some advice about the programming controlling vital components of the jets. Unfortunately, the mailing list archives require you to be a member of the list to view, but I'll include some snippets of his post here.

The test pilot for the rocket racing league project made the suggestion that we should not allow the computer to shutdown the engine during the critical five to fifteen second period where the plane is at takeoff speed, but too close to the ground to make the turn needed to get backdown on a runway. We currently shut the engine down whenever a sensor goes out of expected range, and there are indeed plausible conditions where this can happen even when the engine is operating acceptably, such as a pressure transducer line cracking from vibration. On the other hand, there are plausible conditions where you really do want the computer to shut the valves immediately, such as a manifold explosion blowing the chamber off.



Disregarding the question of whether it was a good idea or not, this seems a really straightforward thing to implement. However, I cautioned everyone that this type of modification has all the earmarks of something that will almost certainly cause some problems when implemented.

Shutting off the engines on a regular plane is bad enough, but we're talking about a full-blown rocket with wings here. I can imagine that a sudden loss of engines is enough to cause a good deal of stress for any pilot, but losing the engines just as the plane is taking off could be devastating. Of course, the engine exploding could be pretty devastating too.

We did implement it, and guess what? It caused a problem. We caught it in a static test and fixed it, and haven't seen another problem with it since, but it still fell into the category of "risky to implement". If we weren't operating at a high testing tempo, I wouldn't have done it. I certainly wouldn't have done it if we only got one testing opportunity a year (of course, I wouldn't undertake a project that only got one testing opportunity a year...).



Our flight control code really isn't all that complicated, the change was only a few lines of code, and I'm a pretty good programmer. The exact nature of why I considered it a bit risky deal with internal details, but the point is that even fairly trivial sounding changes aren't risk free to make. There are certainly some classes of changes that I make to our codebase regularly that I don't bat an eyelash at, but you can't usually tell the difference without intimate knowledge of the code.

I've found similar situations in my own programs. There are areas of the code that I'll change, knowing it will have no real effect on anything else, and then there are those areas where changes are trivial, but they cause odd problems that come back to bite you later. Testing is, of course, the best way to find these problems, but testing isn't always possible. But then, I'm not writing code that could mean the difference between life and death for a pilot. Not *that* has to be some serious stress.

Many software guys do not have a reasonable gut check feel for the assessment of software changes in an aerospace project. My normal coding practice has over an order of magnitude more test cycles than Armadillo does physical tests, and Armadillo does over an order of magnitude more tests than most other aerospace companies. Things are fundamentally different at different orders of magnitude.

John's team probably runs tests more than any other team out there. He has successfully married the typical programming cycle with aerospace engineering. They constantly make incremental improvements and then run out to test them. And as surprising as it sounds, it seems to cost them less to do this. By making incremental improvements, they can control, to some degree, the impact on the system. What this means in the end is that they don't spend an inordinate amount of time building this huge, complex system, only to have it explode on the first test. Not that they haven't had their share of failures, but they've been a bit less spectacular than some.

John also presented some additional info from his other job.

As another cautionary tale, I recently had the entire codebase for our current Id Software project analyzed by a high end static analysis company. I was very pleased when they reported that our discovered defect rate was well under half the average that they find on codebases of comparable size. However, there were still over a hundred things that we could look at and say, "yes, that is broken". Sure, most of them wouldn't really be a problem, but it illustrates the inherent danger of large software solutions. The best defense, by far, is to be as small and simple as possible.

Small and simple is definitely the best. The more complexity you add, the more bugs and odd behavior pop up. Use the KISS principle!