Steven Feuerstein Perth Review - Session 2 - Writing Maintainable Code

Steven's second session remained light on for technical details, which was great for a Friday morning. He dealt with a topic that can be applied to any language and one that every developer faces on a day to day basis - the balancing act between deadlines and writing maintainable code.

As I write this I remember the old adage from Microsoft Secrets, and probably numerous software engineering books - every step a bug makes towards production, the exponentially more expensive it will be to fix.

Steven mentioned a few basic mantras and elaborated on each. I think this was well received by the audience and it always makes a difference to hear the argument from authority.
Without elaborating too much, some I managed to note down include:

• Construct and enforce coding standards - even to the extent of when to utilise SQL. Steven pointed us to a set of standards he follows and has put out for public comment.
• Personally, I'm in favour of using hungarian notation, including the datatype in the prefix of a variable, eg: ld_dob DATE;
• - Utilise your development tool's template facilities. Whether it be Toad, SQL Developer, Textpad... they have have facilities to generate say a package with all the stubs, exception handlers, comment headers etc you need to avoid the White Canvas of Death, as he calls a blank .sql file. This helps with the enforcement of coding standards.
• Encapsulate "stuff" to make it more readable
• define a package for constants, and don't hardcode ANY value. If there is a slim chance in 100 years it could change, it should be defined somewhere. This also goes for formulas such as a full name evaluation from numerous fields.
• Use pragma exceptions, so we see pkg_exceptions.e_collection_empty instead of -22166
• Use PL/SQL APIs to each table (elaborated later in this post)
• If you ever say Never or Always, it's a bug. Tom Kyte always says ;-)
• Boolean functions read best when they return a boolean expression such as
  RETURN (this='TEST');
as opposed to
  

  if this = 'TEST' then
  return true;
  else
  return false;
  end if;

• Functions in general should work like a funnel. This way you can avoid ORA-06503 - Function returned without a value. PL/SQL warnings can help with this. Steven's words with this comment obviously help describe what a funnel looking function looks like!
• Always (is this a bug?) return a subtype - never a specific datatype, ie: VARCHAR2(100)
• Visit Refactoring.com
• Use instrumentation/assertions. (Hint - I'll be elaborating on this later in the year)
• And the fastest way to get the job done is to take your time. Don't rush. Mistakes are made when people rush, and experts from any industry will tell you that if you take your time, the job will get done quicker with fewer mistakes. The best analogy I heard on this was (I think) from a lecturer at uni -
  "Two carpenters were asked to make the same cabinet. The first carpenter, and old man, spent the first 2 hours thinking - studying the plans, laying out his tools.
  The second carpenter, an apprentice, (put his cowboy hat on) and got cracking straight away - sawing wood, drilling holes, occasionally swearing and looking for his hammer. He looked over at the old man thinking (chuckling), crikey, I'm halfway through this cabinet already and he's still thinking about the job!
  The old man then starts making the cabinet. All the relevant bits of timber were within arms reach as he built the cabinet, every tool never more than a step away. Actual construction of his cabinet took 30 minutes. Meanwhile, four hours in, the apprentice is still putting 'finishing touches' on his cabinet (working out how to put the doors on straight).
  The old man beamed at his work. Every join flush, every screw lined up. When the apprentice finally finished his, he figured he'd do better next time. Practice makes perfect."
  

As for coding practices, I had a flick through the book Steven was giving away on the day, and it looks well worth the read. Perhaps a book like that should be a standard text in a university curriculum?

Now to my first major criticism for the day. The concept of PL/SQL APIs to every table. I noticed Tim Hall also describes this sort of utopian development environment where all DML (including SELECT) for a table is done via PL/SQL APIs and any results returned via associative arrays. As I understand it, utopia is like the speed of light, or absolute zero - theoretically you can never reach it. And I think the reason in this particular case is the balance required between a tuned application and an abstracted/encapsulated one. To his credit, Steven himself said his focus is on PL/SQL performance and integrity, he's never been a SQL tuning fanatic.

Steven also mentioned Tom Kyte's mantra of "if it can be done solely in SQL, then do it in SQL". If there is no need to introduce PL/SQL for a solution, then don't.
E-Business Suite has been utilising table APIs for years, and quite successfully. It creates a well placed barrier and internalises all the business logic, turning say the insertion of a new HR employee into a procedure call whose black box touches a dozen tables to manage the setup. It also allows customisations to be neater and separate from the main install.

Software development theory aside, Steven did demonstrate a very nifty facility in his Toad suite to automatically generate all the typical APIs that may be required for a set of tables. Very tidy indeed.

There are many ideals in this session that need to be reiterated to every developer, and every newbie (I feel I should stipulate I think this is not a derogatory term, we should all remember we were all there once) should also cover it thoroughly. 4/5 for this session. Points lost because I think Hungarian notation should be in the major standard ;-), and further elaboration should be made on the utopian ideal of PL/SQL APIs - although time is always pressing for such a theological debate.