When Did Everyone Turn Against Stored Procedures

I forget when I first started working with Stored Procedures in databases, but it was probably back in 1996 when working on the Teletext Commercial System.

Back in those days we developed against Microsoft SQL Server (as far as I can remember this was version 6) and much of the business logic and database integrity was coded in SQL. I remember we had prefixed the name of the stored procedures with 'sys_'. That wasn't my choice, and I quickly discovered that only the in-built system stored procedures should be named as such. Still it was a small point and since it wasn't my choice and had no detrimental effect it was, by that time, best to leave alone. Regardless, this was a client/server application, and the stored procedures suited us particularly well.

My next position was on a multi tier project (Delphi-Java-Sybase) where we used stored procedures primarily for database integrity and serving the middle tier objects for data retrieval and persistence (that's being a little unfair, it did more than that, but this was the primary role).

On this project 95% of the business logic was stored in the middle tier. By contrast the database used stored procedures, via metadata enabled objects, to quickly retrieve and persist data reliably, and safely. The system, whilst not perfect, was a lesson in good design and worked extremely well. Stored procedures were used in many other places where the data was manipulated (reports spring to mind), after all, if you are relying on retrieving masses data to perform a task, sometimes it's more efficient if (at least the preliminary) processing is done near the source of that data without relying on factors such as network bandwidth getting in the way. Also, the fewer bits you have clogging up the network, the better, especially to a distributed user base and infrastructure.

The stored procedures may have also helped with replication, I don't clearly remember as it was a long time ago, what I do know is that it seemed to work very well, and whilst there was always debate between some of the middle tier developers and the database lead as to who should be doing what, mostly it was harmony between all.

In my next role I initially worked on a system where virtually all the business logic was contained in stored procedures. It was hideous, but this was mainly down to (in my opinion) very poor stored procedure design, and the fact the stored procedures themselves were written in an awful mid-way language (not quite SQL, but not quite any other programming language you've seen before) which was then compiled into very poor SQL code (imagine a stored procedure where the first several hundred lines may be blank lines and you'll get the idea).

The stored procedures in this code could run to thousands of lines and certain members of the team relished the fact they were very nearly un-maintainable. With better organisation (we call it refactoring these days) this diabolical situation could have been eased a little, the bad code wasn't entirely the fault of it being implemented via stored procedures.

This is of course my perception of things, although I doubt many would claim it was good.

The next company I worked for almost had a fear of using databases. Initially this upset me, but after a while you go with what you're given and don't rock the boat (again I say "if it works well enough, leave it alone"). We're talking here a situation where we were storing XML in the database as raw text. Yes, you did read that correctly. I'm going to almost certainly be unfair here (by which I mean I *may* be giving a misleading impression) by saying that one of the primary reasons given to me was that changes could be made to the code, without routinely having to alter the database schema. That's just lame. If one relies on a change in the other then so what? Mind you, this is the same mentality that said procedures running to several pages were "self documenting" and that comments in code were bad as they quickly become out of date (so surely part of making a modification is making sure they are updated... oh, never mind, I've posted about that before).

Essentially (and to cut a long gripe short) the project had moved from Paradox, so the team lead, in my opinion was both unaware of what databases could do (and how well they could do them) and was just plain scared of databases.

In my current position I'm working with, what appears to be, a good codebase. It's Java running against an Oracle Database. One thing has struck me though (and bear in mind I'm new to Oracle) is that I can't find any stored procedures?

I know the code uses Hibernate, with which I'm largely unfamiliar, so this may be why, but as an uneducated outsider I'm a little surprised (although not entirely saddened). If the implementation doesn't need stored procedures then that's fine (but where are the triggers? I know a lot of thinking sees these as evil too).

Oh well, new code, new concepts. That's what it's all about. I look forward to finding out more. Perhaps I'll search the web in a moment for Hibernate introductions.

To close with an aside, many projects are reluctant to use stored procedures as it ties you down to a particular back end technology. That's fair enough for some projects, for example I've written plenty of applications that needed to function against whatever back end they were placed against. The amusing thing is however, in the case of the Teletext system I wouldn't be surprised if the stored procedures outlived the application itself, which was coded in Delphi. I imagine that was converted to C# or Java some time ago (if it's still around). Of course I may be wrong, I've not been back since late 2000.

I wonder how this will pan out...

Well, I don't think Microsoft will be loosing much sleep over it, but it appears that two unrelated situations mean I'm going to start looking into developing Android applications rather than for Windows Phone, at least in the short term, allow me to explain:

The first culprits involve the decisions Microsoft are taking with the direction of Windows Phone 7. It started off with the removal (or is it actually more accurate to say omission?) of Cut and Paste functionality. From articles I've been reading recently this is down to a lack of time for getting it developed by release time, rather than not knowing how to implement it. As a friend from Nokia commented "What, were they worried they could do cut and not paste in the time available?".

The lack of C&P was originally sold to developers as a conscious decision ("A phone doesn't need C&P" - Really? Then why write Office Tools for it?), now we're told "it's coming later". Not the end of the world, but frustrating. Still, I agree it's better to get it right than put in something that's buggy or badly designed and then be stuck with it forever more.

The killer move, for me, is the apparent lack of any way of side-loading applications. As is the case with iOS, you get your applications from Windows Marketplace or not at all. That's essentially shut me out. It's all very well running anything I code in a desktop emulator, but then I may as well have just written a desktop application. If I can't actually get it onto a phone (and possibly those of my friends) what's the point? This is the number one reason why I've avoided Apple phones all this time.

Then there is the work related chain of events.

When I started out at my previous job the plan was to move into C# (as I believed the role required, it's certainly what was advertised and what I interviewed for). Previously I had been working in Java but I was happy to move to C#, it's a great langusge. Being a Java developer a large personal application I had been working on I was also written in Java. When I "moved" to C# I started porting my code over too. Doing such things works as a good aid in getting to grips with a new language or language features.

Now I'm going back the other way to Java as the primary language in my work role, so guess what else I'm now planning on doing? [Moving my application back to Java]

Here's the killer: Just as I was hoping the C# code would allow me to share core components of the application between Windows, Windows Mobile and Phone, and possibly web sites, it turns out that the primary development language for Android appears to be Java.

So now Java potentially gives me access to Android Phones, The Android OS (for Laptops, Slates, etc.), and with a little work (hopefully not too much) Windows, Linux, MacOS, possibly even MeeGo. Oh, and I believe web servers can also run something called servlets too. Oh, and just for fun, the Windows Mobile phone I currently have includes Java too... oh, and I know S60 used to run Java. Wow!

The killer which I'm brushing over is obviously that there will be an incredible amount of work to customise the user experience and display for each system, possibly this could be the lions share of the work, but I do at least take some comfort that at least some of the core business logic may be sharable between systems.

Looks like until my priorities change again this is the logical route for me to get back into Java in my spare time above the demands of work.

So now the new laptop has arrived one of the first things going onto it will be a shiny new install of Eclipse and the Android SDK. Oh, did I mention that's all free? That's possibly a little unfair a comparison, the Express versions of Visual Studio are excellent (that's one of the downsides to all this, I find the Visual Studio IDE an absolute joy to use. For Java development I prefer NetBeans, but since I'm on Eclipse at work, and most of the Android documentation references Eclipse I may as well use that).

Will you ever see any of these fruits of my labour? probably not (almost certainly not), but the point is the more I play, the more I learn, and as long as some of that is relevant I end up getting something out of it over and above the hobby side.

Now, of course, I'm reliant on Oracle not screwing things up with Java (well, no more than Sun managed at the very least).

Fingers crossed.

Testing Times

Looks like this is going to be one of those rare blog entries that will live in both my tech and personal blog since I don't know where to put it. I guess that's good. Anyway:

Well, it's week number four in my new job. i have a desk, a machine and I'm just about set up with my development environment. There's a few things I've not been briefed on or covered yet, some of them quite basic (like how about giving me a Jira account, that may be useful soon, or on what branch do these guys do all their development, surely not main_latest all the time?!!)

So what have I achieved so far?

Well, not a lot yet, and it's starting to bother me. I've so far been given a single 2 part task with the aim of speeding up the build tests:

• Go through a bunch of JUnit tests and remove anything not testing actual business logic (so for exampe if its a persist or retrieve operation, take it out).
• Investigate getting the JUnit tests to run in parallel.

That's not so bad for a first task on an unfamiliar system.

So I spent just under a day looking at a select bunch of JUnit test code trying to decipher what was and wasn't a business logic test. Sounds quite easy, but since a lot of methods are masking non-business logic, it's very easy to leave them in. Similarly, what looks like a persist operation may be an actual calculation of one variety of another. Then you have the methods that call both responsibilities, or rely on persisted data to pass. It's not as easy as it first looked, especially on an application with which you are not familiar.

The upshot of all this? I came to the conclusion that there probably wasn't any business logic being tested in the set of tests I was looking at. I was, as you might imagine, uneasy with this deduction, it felt wrong and I couldn't be 100% sure my findings were correct, however when I returned to my "mentor" for this task he didn't seem to surprised and commented "I've skimmed over this myself and I didn't see anything leap out either."

Oh thanks.

Actually one thing that did amuse me, I had flagged up one test as possibly being business logic, however as my mentor was looking into this I noticed a section of code that clearly suggested otherwise, so at least I beat him to that one.

With that task out of the way I started looking at making the JUnit tests run in parallel. A little research on the web revealed what was needed (including a version of Spring that's still in RC status). Undeterred I created a small project with a bunch tests to try this stuff out, and sure enough it worked as I expected (albiet without a hint of Spring in sight, unlike the real project). So how did it go once I started applying this new found knowledge to actual project... well I've been on it a week now and I'll be sure to let you know what I find when I finish (if you ask nicely).

I've been hit by numerous issues with my development environment, and on the odd occasion when things do work as expected, a run can take up to 26 minutes (usually closer to 19 for a full run - at least for what I'm covering). The worst thing is I'm only making changes to a handful of POM.xml files and so far the results show no avantage at all. At this rate my findinge will be: "Hey, I've spent over a week and can't get any speed increase". That hardly makes me fell very good and I don't actually feel I've achieved anything yet. Especially when for the most part my day consists of the following routine:

1. Start machine. Spend 1 to 2 hours getting the build to work properly.
2. Make a small, well trodden modification to all 4 instances of a command in 3 pom files. (5 mins max, probably closer to 2 mins)
3. Fire off build and wait to complete (up to 19 minutes if it completes)
4. Investigate any errors or note timings (2 to 10 mins).
5. Repeat from step 2 until time to go to lunch or home.

I can't help feeling this should have been finished some time ago. I know deep-down proving something doesn't work is as important as proving it does, but this seems to be dragging now. it feels like my fault, but frankly i don't know how to make it better, it's just the nature of this particular beast.

I guess deep down I feel the need to prove myself as the new boy, and with this task I feel like I'm coming up short, rightly or wrongly. Oh well, I guess that could be me worrying too much. Sometimes, that's just the way it is.

It’s not me, I’m perfect!

I think I'm in a minority here, but the state of comments in some code is truly appalling, and the worst offenders are those who think their code is so clear or logical it doesn't need commenting.

I've worked with many such delusional characters over the years, and it always seems to be he ones who should know better, the "above average developers", and you can measure that however you feel.

The thing is, they are correct, or at least they would be if they were working on one-man projects. The code is clear to the author as it comes from their thought process. What's obvious to them is often far from obvious to everyone around them, and this state of affairs is made worse when using call-backs, events or making use of things such as multi-threading.

The argument is usually either "the code is self commenting" or "adding comments will confuse the code" and "comments become out of date quickly and therefore misleading". I'll briefly address these points now:

The code is self commenting.

Indeed, the code ideally should be, but how about a general overview of what happens? And is it really? The people who claim the code in such are often the authors of long, convoluted methods, with no clean responsibility, lots of inner classes or methods (depending on the language).

This code is difficult for others not familiar with it (often that equates to everyone other than the original author) to follow, but amusingly it is often tricky for the original author to understand when returning to code after some absence.

The times I've seen such developers struggle to understand what they were trying to achieve in a piece of code is frightening, but they normally get it fairly quickly as it comes from their imagination. If only everyone else was looking at it from the same perspective, then you may have a point.

Yes, the code can be self commenting, but to me this means well named methods with a clear responsibility, not a 40+ line method that covers all branches of execution.

Adding comments confuses or detracts from the code.

Well, it's often subjective, but I agree that adding too many comments does detract from the code and make it more difficult to understand. Having said that, a comment that briefly describes what a particular method is trying to achieve (something akin to JavaDoc or the .Net triple slashes if available) can be an immense aid to a tired developer trying to understand how something is supposed to work. This can occasionally even be a reference to the functional specification if you're lucky enough to have one. Such comments can be easily ignored if you find them offensive.

I'd also go so far at to say an occasional comment before a section of "non trivial" code can help both the initial development (I often use comments to help provide a skeleton and program into them) and future maintenance.

Comments become out of date quickly and therefore misleading

Then you're not treating them with the respect they deserve. Isn't that a bit like saying "it compiles so it will run?". Of course, tracing comments that need updating can be a chore, but if they are finely grained and focused on what is actually happening  in the particular concern they are attached to, code and comment modifications can be performed hand in hand. Anything else, go to the specification.

In my opinion you should target your comments at others not yourself (although having said that my memory is sufficiently poor that I will happily use my own comments as a reminder). Assume little and ensure you give a decent starting point for further investigation at the very least. And for the love of god comment any gotchas or what you did to avoid them. Often we add code that looks out of place or redundant just to get around such things (yes, I know there may be a design flaw, but we'll code with a view to make a refactor as soon as we can go more easily and actually get the product out of the door (and that's a whole other post).

I could go on with this post, but in a nutshell I'm just sick of trawling through thousands of lines of uncommented code that should make perfect sense, but are complex enough to only be apparent to the author, and even then the author often struggles.

Apple Rules!

I’m at once both loving and despairing over the spat between Apple and, primarily, Adobe.

In cutting off Adobe and other companies with their rules limiting of the source code requirements of applications (which as a developer strikes me as being quite strange) other tools apart from Flash have been affected. The nature of the development beat these days relies on a variety of tools to get applications delivered, not just a few chosen languages with massive do-it-all libraries.

Add to that the fact that Apple takes advantage of many closed APIs in the OS which other developers are expressly forbidden to utilise (for example the iBooks application uses closed APIs to do things as simple as adjust screen brightness, from what I’m lead to believe), and a lot of developers are getting… frustrated.

If the iPhone/iPad apps keep selling, then they’ll keep being developed, but as more and more competing operating systems rise through the ranks, the appeal will start to waver, developers who have been rejected or withdrawn from the App store will start to question where their time is better spent, and the flow of new apps may (just may) be directed to alternative devices.

Slowly the iPhone will be less appealing, sell fewer units, and the process will enter a cycle like Microsoft is now trying to get out of on their mobile platform (although this was for different reasons, mainly neglect of the OS at Microsoft).

I doubt there will be any measurable effect on Apple sales any time soon, it may not happen at all, but I suspect Apple may currently be sowing the seeds of its own destruction. That would actually be a bad thing as fewer players may lead to less innovation. Having said that, the mobile economy is so large at the moment that this is unlikely to happen any time soon.

Footnote: Don’t give me any rubbish about keeping the iPhone interface consistent over applications in the OS, have you seen iTunes on a PC? Even Sun introduced a Windows look and feel for Java.

Scrum

The guys over at AxoSoft, creators of the rather good OnTime software have produced a video Scrum in Under 10 Minutes as a quick introduction to Scrum.

Scrum is nothing earth shattering, and it’s not going to be a silver bullet for many people, but it’s well worth a look, and if followed correctly I’ve no doubt it will help a lot of projects run more smoothly.

Of course, the video is a gentle advert for the AxoSoft OnTime application, but it’s not forced at you so is worth a watch.

As it stands the company I work for has started using OnTime, but at the moment it’s just performing a to-do list and time logging role for me, and I suspect most of the other team. We’re yet to take the bull by the horns and make effective use of the many features.

Oh well, hopefully we’ll get there. Certainly on the task I’m working on at the moment a burn down chart (or even some smaller milestones) may have provided a morale boost that we are actually making progress.

Never mind.

Disappearing Code (WHS Update 3)

In my previous post I talked about Mesh and mentioned the pitfalls of the current implementation, specifically the fact that if a file ‘goes bad’ in one location, it will quickly go bad in all synchronized locations, with no option (that I can see) to revert to an earlier version or undelete.

One of the folders I synchronize on Mesh contains code for a personal project I’ve been working on for a few years. Fortunately, most of the work I’ve been doing is getting my head around what I wanted to achieve, and devising the core of a design and an implementation approach to achieve this, meanwhile I’ve cut very little code.

About a year ago I created a few of the basic objects and tests in Java, and have recently been in the process of converting this Java code to C# with the intention that not only would I be able to use this project to aid in my C# education, but that also by the fact that the app would be better solved (at least initially) by a Windows desktop application (and possibly a Windows Mobile incarnation), rather than a generic Java Swing application. My ultimate goal is to make a Java Servlet web based implementation followed by a generic Java client; what better way to show off your skills to prospective employers than a solution implemented in two languages targeting multiple different clients?

Anyway, I’ve strayed a little, the key is that the Java and C# code is kept in folders replicated by Mesh. One evening late last week before setting off home from the office I decided to load the C# version to see what suggestions ReSharper would have to clean things up (ReSharper is an excellent tool – take a look if you work in any of the .NET languages, it’s good enough that it’s changed my style of programming in a few areas due to its suggestions). So I navigated my way to my replicated C# folder and… well you can probably guess: My source files were missing!!!

Bugger.

There’s only a couple of hours work there, but it’s a pain to write such things a second time. I don’t know who deleted (or hopefully moved the files) yet – me or the Mesh. Hopefully it’s me who has moved them (although the fact that some files still exist in the directory makes me wonder), but it does highlight the fragility of the system for longer term work.

So then I set to thinking about this less than ideal situation. I’ve been using Subversion for a couple of years now, and this is exactly what that system is for – source control of code. I did spend several hours trying to set up a subversion server on my (still currently dead) Ubuntu box, but failed, however after a quick search on Google I see several people are running subversion on Windows Home Server with it externally accessible from other locations. This down side of this is that it does open the machine up to the outside world, but I was intending to do this for such things as music streaming anyway, so it looks like the way I’m going to go.

I’m taking a risk here in as much as I haven’t done a test install on my trial version of WHS (which only has a few days of trial time remaining), but there’s enough going for Windows Home Server now that I know I’ll be buying a licence soon (especially since the 120 day activation key has been withdrawn from the Microsoft site – thanks guys!). You can do all this stuff for free in Ubuntu, but frankly it’s not worth the hassle to me, at least for the moment.

I also did some quick investigations into the power requirements of running the old Ubuntu box (Biostar iDeq 210) verses my old PC running the test WHS install (Shuttle SN45V3). Both machines have 2 HDD, but the Shuttle has a graphics card while the Biostar is using the internal graphics chipset (after all it was only running Linux in terminal mode). The Shuttle idles at about 102 Watts, while the BioStar was about 90 Watts. Aside from the fact they both have different processors, Hard Drives and probably memory, the extra power draw on the shuttle can probably be explained by the graphics card (and it has an AMD Athlon XP 3200+ as opposed to the BioStar 3000+). The Biostar would be of ample horsepower to run WHS, but it never properly managed to run XP without problems, it would periodically crash with a display driver warning on the BSOD, a problem I never managed to rectify either with re-installation of the OS and Drivers, or by installing an external graphics card, so I’m cautious of running WHS on this box.

Considering everything, and for the sake of an extra 10% power draw, it looks like the Shuttle box will get the WHS build. 100 Watts is more than the 35-50 Watts of the HP MediaSmart box I was looking at, and it won’t have the expandability of that box, but that machine costs over £400, so even factoring in the WHS operating system (at £66) it would still take several years to recoup the money, based on current energy prices.

The next steps are that I need to take the box apart next to see if it’s feasible to get 4 hard drives in there (3 should work as I’m going to remove the broken DVD drive, but 4 would be better if I can find the space).

The box is going to live in the garage, so I also need to drill a bigger hole in the wall to get a Cat6 cable out there (the box only has a 100 Base-T connection, but I don’t want to be running a cable twice). When I’ve done all that I’ll order the OS, a 10 meter Cat-6 cable and possibly a new bigger HDD and take it from there.

I wonder if WHS will run headless… a quick search suggests it will so I suppose that will probably be my next test (after a re-install of the trial I suppose), after all, if I can take the graphics card out the power draw may drop a little, and as they sat at Tesco “Every little helps!”. By headless I mean without a graphics card at all – not just without a monitor attached. That may sound odd, but I remember the early versions of Java would fail if certain operations were performed without any graphics card present (I think it was something to do with rendering AWT components even if they were to be served up remotely, but I’m probably wrong, this was a long time ago).

Oh, and now I see Power Pack 3 is out!

Stay tuned.