murdering your baby

[caption id="attachment_356" align="alignnone" width="370" caption="don\'t worry, no babies were harmed"][/caption]

Yesterday my friend Ian sent me a tweet about prosper

@ihumanable I'd be more interested in using Prosper if it provided support for true prepared statements instead of concatenating strings.
6:29 AM Dec 7th - pian0

With that my baby was born, the baby was a version of prosper that utilized prepared statements where appropriate. Prosper's main functionality has been coded for a while and the bulk of the work I've been doing as of late has been testing, improving backend support, and adding new functionality. The nice thing about this kind of work is that you don't have to go mucking around in working code, you are just fixing broken stuff and adding new functionality. The idea of adding prepared statements made me a little queasy, I had briefly looked at it when I was starting prosper but decided that they were so varied that for the first attempt I would just avoid the headache. What's the problem you ask, let's take a look at this feature table real quick.

Database	Supported by Prosper	Can Support Prepared Statements
dBase	YES	NO
DB++	NO	NO
FrontBase	YES	NO
filePro	NO	NO
Firebird / Interbase	YES	YES
Informix	YES	YES
IBM DB2	YES	YES
Ingres	YES	YES
MaxDB	YES	YES
Mongo	NO	NO
mSQL	YES	NO
MS-SQL	YES	YES
MySQL	YES	YES
OCI8 (Oracle)	YES	YES
Ovrimos	YES	YES
Paradox	YES	NO
PostgreSQL	YES	YES
SQLite	YES	NO
Sybase	YES	NO
Tokyo Tyrant	NO	NO

As you can see the support for prepared statements varies in the Vendor Specific Database Extensions of php. There is also the fact that prepared statement support is poorly implemented in several extensions, they can be coded around but it adds a good deal of complexity. Despite all the reasons not to implement prepared statements, safety, speed, and correctness dictate that I should at least give it a go.

Yesterday and today were my go, and I met with some success. Prepared statements are not all that difficult to implement for a given backend, throw in some question marks, call the correct bind function, and you're off and running. The real difficulty is the cross-platform nature of prosper. I wanted prepared statements to be a first class citizen, I didn't want to decrease any existing functionality, I wanted to not overly complicate the way prosper works, and because there are 11 adapters that need to be upgraded, I wanted it to be simple to add this functionality to a given adapter.

I worked and toiled and toiled and worked, I tried hacking something up to make the MySQL adapter work, ran into the bind_results function and screamed at it for a while. Then I got out some paper and drew out some diagrams, thought about sample code. Then I went to sleep. Then I woke up and in the shower pondered the proper syntax for passing around typing information and the division of labor for transformation. Then I hacked on it some more and wrote a quick test. Then and this is the most important part I realized I had done it absolutely backwards.

I had some clues that stuff wasn't working out right, I hit a point where I would either need to embed an if statement in every function of an adapter or have a duplicate function. I thought that with some clever coding I could shove this down into the base class, it wasn't great but it was working. Then I saw another red flag when I realized that the processing in the where clause was identical to the processing in the values clause, and I applied some refactoring. This is when I realized that there was a single function that I could have modified and had the whole thing work much more elegantly.

That is when I had to come to the decision to murder my baby. I had painstakingly worked out the kinks for two days, traveling far down this road, touching a bunch of code, and now I realize the best way forward is back. The best thing I can do right now is to revert my changes to Monday morning and lose 2 days of work, and that is great!

It's not the best outcome, in an ideal world I would have seen this solution before and applied it and not wasted 2 days. The thing is that I've learned a great deal, I've come up with a much better implementation, and I've been reminded of an important lesson, never be afraid to throw away code. The code I wrote is adequate, it works well enough, but it is far from optimal. I could keep going, head down, plowing away making as many problems as I solve trying to build a house on an unsound foundation. The point here is to pay attention to those red flags.

The other important point is that sometimes you have to actually try something to find the thing that will work. You may lose some time, but even after sitting down with pencil and paper, thinking long and hard, the optimal path didn't come to me until I walked down the wrong road. That's just life sometimes as a software developer, you have to learn to live with it.

I will continue working on prepared statements for prosper, and I hope that by 0.7 or 0.8 they will be implemented and in there. I still question the syntax and think that I can clean it up, right now the plan is for prosper to support unnamed parameters (?), named parameters (:name), unnamed typed parameters (%i), and named typed parameters (:name%s), but all this seems like a little much. Good thing prosper hasn't had a 1.0 release yet ;-)