Who needs modular code?

Wouldn’t it be nice if when people wrote some useful code, they tried to make it suitably modular and reusable? This is what I have to contend with at the moment. We have a large, complex system written in Oracle Forms that we are now partially re-writing in HTMLDB (hurrah!) One function I want to replicate is the ability to change your own Oracle password; the current Forms application has a form to do this that looks like this:

Old Password: [ ] New Password: [ ] Confirm New Password: [ ]

So that should be a moment’s work to redo, right? Wrong. There is a package of procedures for user maintenance that contains the following 2 procedures that are relevant:

PROCEDURE change_password_validate
(p_username         IN VARCHAR2,
 p_old_password     IN VARCHAR2,
 p_new_password     IN VARCHAR2,
 p_confirm_password IN VARCHAR2,
 p_profile_name     IN VARCHAR2,
 p_mode             IN VARCHAR2 DEFAULT 'N',
 p_mask             IN  VARCHAR2) ;

PROCEDURE change_password_process
(p_username IN VARCHAR2,
 p_new_password IN VARCHAR2,
 p_confirm_password IN VARCHAR2,
 p_mask           IN VARCHAR2,
 p_profile_name IN VARCHAR2,
 p_encrypt_pw IN VARCHAR2,
 p_admin_mode IN VARCHAR2 DEFAULT NULL) ;

Some design flaws are immediately evident:

• Validation is totally separate from processing. If I choose to, I can skip the validate routine altogether and call the process routine to change the password to anything I like, regardless of whether I get the old password right or confirm it correctly. (Actually, this foolish separation of validation from processing is a company standard!)
• I get to choose whether the password is to be stored (in our own application’s USERS table) in encrypted form or not. HTF do I know whether it should be encrypted or not?
• I need to supply something called p_mask, which I think may be something to do with the encryption process, or maybe the validation process – none of this is documented of course, or at least nobody knows where any such documentation may be found. I have tried passing the word ‘mask’ and it seems to work, except that all subsequent attempt to change the password then fail on the validation of the “old” password – perhaps because it has been encrypted in an unexpected manner.
• I also need to supply something called p_profile_name, which I do happen to know is a user attribute something like a role, stored in the USERS table. Well excuse me, but if I’m passing in the username as a parameter, why should I have to go look up the USERS record and obtain the profile_name value just to pass it into this lazy procedure?
• I don’t fully understand p_mode and p_admin_mode either, but at least they have defaults which I assume (for now) I can live with.

So instead of being a 5 minute job, this is probably going to occupy about a day of my time: locating source code (the packages are wrapped in the database), studying source code to see what it is doing. And if all that fails, trying to find someone from the team that wrote the code and ask them to tell me what I should be doing.

EAV nightmare

My charitable Christmas mood only goes so far. I'm looking through a spec which reads like a "database design nightmare!" theme advent calendar. Each page reveals a potential disaster more frightening than the one before. This is my favourite new year hang over inducing cocktail of entity attribute values and generic application design all wrapped up in a gloriously mal-specified mess.

Table: Parameters

id  VARCHAR2(50) PRIMARY KEY  --The application requesting the value
identifier NUMBER       PRIMARY KEY  --The name of the parameter
type  CHAR(1) --‘I’, ‘S’ or ‘B’ is type of value for the parameter
string_val VARCHAR2(50) --‘Y’ or ‘N’ or NULL if BOOLEAN or the string 
integer_val NUMBER       -- The integer value

note: The id holds a value comprising the IP address, type of
application and the instance at that IP.

Bracketing frenzy

I just found myself decyphering this:

SELECT ...
FROM report_results rpt
WHERE NOT ( (substr(rpt.report_type,2,1) in ('1','2','3','4','5')) and
          (rpt.sig_type = 'ISDNMA') and
          ((rpt.group_ctn != rpt.ctn_prefix||rpt.ctn_suffix) and
           (length(rpt.group_ctn) != length(rpt.ctn_prefix||rpt.ctn_suffix)) and
           ((length(rpt.group_ctn) != (length(rpt.ctn_prefix||rpt.ctn_suffix)-1)
           ))));

Which turns out to mean this:

SELECT ...
FROM   report_results rpt
WHERE  NOT (     SUBSTR(rpt.report_type,2,1) IN ('1','2','3','4','5')
            AND  rpt.sig_type = 'ISDNMA'
            AND  rpt.group_ctn != rpt.ctn_prefix||rpt.ctn_suffix
            AND  LENGTH(rpt.group_ctn) != LENGTH(rpt.ctn_prefix||rpt.ctn_suffix)
            AND  LENGTH(rpt.group_ctn) != LENGTH(rpt.ctn_prefix||rpt.ctn_suffix)-1 );

No wonder there are no brackets left in the shops.