Re: How to control developers writting better SQL

hause011_at_garnet.tc.umn.edu (Steven Hauser) writes:

> It seems to me that software engineering is more the subject of this
> thread.
>
> What engineering techniques can be used to make good systems?
> Well, read a book or two and check out people who successfully develop
> code.

What follows are good pieces advice. But they are all regarding the process by which the development is done. Process is important. But it's not everything. If you follow a good process but do not understand how to build a solid system it won't matter how good the process you followed was.

> SEPARATE DEVELOPMENT FROM PRODUCTION SYSTEMS. ...
This is something that evolves as a project grows. Early on it may make sense for development to happen on the same system. when a project is large and complex and mission critical it may make sense to have 3 or more levels of Q/A systems possibly costing more in total than the production systems.

> USE CONFIGURATION MANAGEMENT. Source code control...

Nit: Configuration Management and Revision Control are two different systems. One can be used as a tool to help implement the other, but configuration management is a much bigger topic than revision control.

But using CVS or something similar (Perforce, ClearCase etc depending on your budget) is an absolute 100% must-have for even small projects. Right from the start. Even if you're a single developer!

> REVIEW AND TEST CODE. ...
Well duh :)

But all the testing in the world isn't going to help unless the system you build is testable. In order for a system to be testable it must behave predictably and it must fail predictably. A system is fundamentally untestable if it is designed to muddle on when a bug is encountered. It means that usually bugs in the form of untested unstable queries are easily introduced and hard to detect until the day when the optimizer makes a mistake and takes your database down.

Another fundamental engineering principle is that consequences of a failure should be contained as much as possible. If the processes you mention above fail and a bug is introduced, it should always consistently result in a failure as soon as possible rather than having a system try to muddle through in unpredictable, poorly documented ways, possibly hiding the bug indefinitely or causing other components to fail in unpredictable ways.

My claim is that without some way to restrict a user to running only queries that have pre-existing outlines it's much harder to build systems that meet those two engineering principles.

In retrospect I would take the tack another poster mentioned, where every query is contained in a special API the implementation of which contains every database query in the system. However this still does not guarantee that a change might not be made in that system and fail to be caught by the Q/A process or a change in database structure might be made that changes the access plans in unpredictable ways.

If a system is to be robust it must be behave predictably when operating correctly and failures must be immediate and predictable as well. The only way to guarantee that for a database is to be able to promise that no query outside an approved set is run, and no access plan outside an approved set is used.

-- 
greg