Offensive Coding with SQL
Programmers are often advised to code defensively, checking the validity of all parameters before proceeding. In reality, when accessing a database, there is a real advantage in coding offensively, trying to do several things simultaneously.
A good example is a succession of various checks, designed to flag up an exception whenever the criterion required by any of these checks fails to be met. Let's assume that some kind of payment by a credit card has to be processed. There are a number of steps involved. It may be necessary to check that the customer id and card number that have been submitted are valid, and that they are correctly associated one with the other. The card expiration date must also be validated. Finally, the current purchase must not exceed the credit limit for the card. If everything is correct, the debit operation may proceed.
An unskilled developer may write as follows:
select count(*) from customers where customer_id = provided_id
and will check the result.
Then the next stage will be something like this:
select card_num, expiry_date, credit_limit from accounts where customer_id = provided_id
These returns will be checked against appropriate error codes.
The financial transaction will then proceed.
A skillful developer will do something more like the following (assuming that today( ) is the function that returns the current date):
update accounts set balance = balance - purchased_amount where balance >= purchased_amount and credit_limit >= purchased_amount and expiry_date > today( ) and customer_id = provided_id and card_num = provided_cardnum
Then the number of rows updated will be checked. If the result is 0, the reason can be determined in a single operation, by executing:
select c.customer_id, a.card_num, a.expiry_date, a.credit_limit, a.balance from customers c left outer join accounts a on a.customer_id = c.customer_id and a.card_num = provided_cardnum where c.customer_id = provided_id
If the query returns no row, the inference is that the value of customer_id is wrong, if card_num is null the card number is wrong, and so on. But in most cases this query will not even be executed.
The essential characteristic of "aggressive coding " is to proceed on the basis of reasonable probabilities. For example, there is little point in checking whether the customer existsif they don't, they won't be in the database in the first place! Assume nothing will fail, and if it does, have mechanisms in place that will address the problem at that point and only that point. Interestingly, this approach is analogous to the "optimistic concurrency control " method adopted in some database systems. Here update conflicts are assumed not to occur, and it is only when they do that control strictures are brought into play. The result is much higher throughput than for systems using pessimistic methods.
Code on a probabilistic basis. Assume the most likely outcome and fall back on exception traps only when strictly necessary.