To do your job well, you need to be aware of, and to follow, both "little" best practicestips focused on particular coding techniquesand "big" best practices. This section offers some suggestions on the big picture: how to write your code as part of a high-quality development process .
In other words, if you (or your methodology) don't follow some form of the best practices in this section, you are less likely to produce high-quality, successful software.
|DEV-01: Set standards and guidelines before writing any code
These standards and guidelines might include many or all of the best practices described in this book. Of course, you need to make your own decisions about what is most important and practical in your own particular environment.
Key areas of development for which you should proactively set standards are:
By setting clear standards and guidelines for at least the areas we listed above (tools, SQL, error handling, and code review and testing), you ensure a foundation that will allow you to be productive and to produce code of reasonable quality. We offer detailed advice on most of these areas later in the chapter.
The deadline pressures of most applications mitigate against taking the time up front to establish standards, even though we all know that such standards are likely to save time down the line.
|DEV-02: Ask for help after 30 minutes on a problem
Following this simple piece of advice might have more impact on the quality of your code (and your productivity) than anything else in this book!
How many times have you stared at the screen for hours, trying this and that in a vain attempt to fix a problem in your code? Finally, exhausted and desperate, you call over your cubicle wall: "Hey, Melinda (or Jose or Farik or Lakshmi), could you come over here and look at this?" When Melinda reaches your cube she sees in an instant what you, after hours, still could not see (and she doesn't even know MySQL all that well!). Gosh, it's like magic!
Except it's not magic and it's not mysterious at all. Remember: humans write software, so an understanding of human psychology is crucial to setting up processes that encourage quality software. We humans like to get things right, like to solve our own problems, and do not like to admit that we don't know what is going on. Consequently, we tend to want to hide our ignorance and difficulties. This tendency leads to many wasted hours, high levels of frustration, and, usually, nasty, spaghetti code.
Team leaders and development managers need to cultivate an environment in which we are encouraged to admit what we do not know, and ask for help earlier rather than later. Ignorance isn't a problem unless it is hidden from view. And by asking for help, you validate the knowledge and experience of others, building the overall self-esteem and confidence of the team.
There is a good chance that if you have already spent 30 minutes fruitlessly analyzing your code, two more hours will not get you any further along to a solution. So get in the habit of sharing your difficulty with a coworker (preferably an assigned "buddy," so the line of communication between the two of you is officially acknowledged and doesn't represent in any way an acknowledgement of some sort of failure).
Programmers are a proud and noble people. We don't like to ask for help; we like to bury our nose in our screen and create. So the biggest challenge to getting people to ask for help is to change behaviors. Here are some suggestions:
Problems in code are identified and solved more rapidly. Fewer hours are wasted in a futile hunt for bugs.
Knowledge about the application and about the underlying software technology is shared more evenly across the development team.
The main challenge to successful implementation of this best practice is psychological: don't be afraid to admit you don't know something or are having trouble figuring something out.
Peopleware: Productive Projects and Teams, by Tom DeMarco and Timothy Lister (Dorset House). This is a fantastic book that combines deep experience in project management with humor and common sense.
|DEV-03: Walk through each other's code
Software is written to be executed by a machine. These machines are very, very fast, but they aren't terribly smart. They simply do what they are told, following the instructions of the software we write, as well as the many other layers of software that control the CPU, storage, memory, etc.
It is extremely important, therefore, that we make sure the code we write does the right thing. Our computers can't tell us if we missed the mark ("garbage in, garbage out" or, unfortunately, "garbage in, gospel out"). The usual way we validate code is by running that code and checking the outcomes (well, actually, in most cases we have our users run the code and let us know about failures). Such tests are, of course, crucial and must be made. But they aren't enough.
It is certainly possible that our tests aren't comprehensive and leave errors undetected. It is also conceivable that the way in which our code was written produces the correct results in very undesirable ways. For instance, the code might work "by accident" (two errors cancel themselves out).
A crucial complement to formal testing of code is a formalized process of code review or walk-through. Code review involves having other developers actually read and review your source code. This review process can take many different forms, including:
Overall quality of code increases dramatically. The architecture of the application tends to be sounder, and the number of bugs in production code goes way down. A further advantage is that of staff educationnot just awareness of the project, but also an increase in technological proficiency due to the synergistic effect of working together.
The development manager or team leader must take the initiative to set up the code review process and must give developers the time (and training) to do it right. Also, code review seems to be the first casualty of deadline crunch. Further, a new project involving MySQL stored programs might not have the language expertise available on the team to do complete, meaningful walk-throughs.
|DEV-04: Use independent testers for functional sign-off
Individual developers should and must be responsible for defining and executing unit tests on the programs they write. Developers should not, on the other hand, be responsible for overall functional testing of their applications. There are several reasons for this:
To improve the quality of code that is handed over to customers for testing, your team leader or development manager should:
This extra layer of testing, based on the customer's own requirements and performed before the handoff to customers for their "sign off" test, will greatly improve code quality and customer confidence in the development team.
We spend several days building a really slick application in PHP (or VB.NET or Java or . . . ). It allows users to manage data in a few different tables, request reports, and so on. We then devote most of a day to running the application through its paces. We click here, click there, enter good data, enter bad data, find a bunch of bugs, fix them, and finally hand it over to our main customer, Johanna. We feel confident in our application. We can no longer break it.
Imagine how crushed we feel (and we bet you can imagine it, because undoubtedly the same thing has happened to you) when Johanna sits down in front of the computer, starts up the application, and in no more than three clicks of the mouse causes an error window to pop up on the screen. The look she sends our way ("Why are you wasting my time?") is not rewarding.
There is no way for us to convince Johanna that we really, truly did spend hours testing the application. Why should she believe such a thing?
Quality of code handed to users for testing is higher, which means the end result moved to production is of correspondingly higher quality.
Customer confidence in the development organization remains high. This confidenceand the respect that comes with itmakes it easier for developers to negotiate with customers over the time-versus-quality dilemma so many of us face in software development.
Many small development groups can't afford (i.e., can't convince management to spend the money) to staff a separate QA organization. At a minimum, you must make sure that customers have defined a clear set of tests. Then distribute the functional testing load to the developers so that they do not test their own code.
http://www.well.com/~vision/sqa.html: A gathering place for references related to the theory and practice of Software Quality Assurance. This site is growing to include information on Standards and Development Procedures, Product Evaluation and Process Monitoring, Configuration Management Monitoring, the role of SQA in the Product Development Cycle, and Automated Testing Tools.
|DEV-05: Use source controlled files to maintain the "reference" copy of your stored routines
Source code control systems (SCCSs) allow us to keep copies of major revisions of our program source code, allowing us to roll back an application's source code to an earlier point in time or to examine the source code in use with an earlier version of the application (which might still be in use somewhere). Virtually all professional software developers couldor at least shouldemploy an SCCS to store their application code.
Unfortunately, developers often fail to source control the DDL code to create database objects and often neglect to include stored program code in the SCCS. To some extent, the ability to extract the source code for a stored program from the database encourages us to edit a stored program "in place"even when we would never dream of editing PHP code "in place" (e.g., directly editing the .php files in the Apache document directory).
If your stored programs are part of an application, then the source program code is just as much a part of the application source code as code written in other languages such as PHP or Java. You should therefore keep the "reference" copy of your stored program code in your version control system (such as CVS, ClearCase, BitKeeper, etc.). This means saving your stored program code as a text file and performing explicit check-in and check-out from your version control system.
Think of that text file as the original source code for your procedure. Applying the source code to the MySQL server is analogous to compiling that source as a binary. Extracting it from a server for editing is equivalent to decompiling a binary and is usually not how you obtain a copy of the source for editing. Instead, you should perform an explicit check-out of the source code from the SCCS, edit it in the MySQL Query Browser or other tool, and then apply it to a test database for unit testing. Later you can deploy the source code for the stored program to a production database by running a script that executes it inside of the MySQL command-line client.
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development