A few weeks ago I was charged with refactoring and adding some new features to a VB script in a MS Access document connected to an Oracle SQL database which is used by a notable number of employees all over the world.
The script had been written by an external consulting company, and the code was just horrible.
The records had a country field, and the Access form should only display records with the same country as the user, whose country was read from a MS Active Directory.
There were about 7 to 10 getters and global variables only for the country. The getCountry function checked if the country variable (a string) was empty and called another getCountry function if so. I can't remember what the second function did, but it was pretty similar to the first one and called another function too. Afterwards the return value of the second function was checked too, and if the string was empty, it read the value of the global DEFAULT_COUNTRY variable. If that one was empty, it called another function.
Those methods should return just the name of the country, but the country code was used too. So, all of those functions again, just with the country code instead of the country name. And this bunch of functions was interwoven with the first bunch, some of them called each other...
The database contained not a single primary key, I shit you not. The main table which contained the records for the Access form had an id field, but there were no constraints. They just looked if the id existed in the VB script when a record was added or edited and displayed an error message if so.
So of course, the main tables country field actually stored the country names. A workmate told that their had been a typo in the country table once, so they had to edit all references in the other tables, which had caused several problems. I think that was the first thing I did, adding primary key ids.
I can't remember most of the details from the remaining code, but it was a mess.
Using the VB editor is a pain in the ass too. If you have a syntax error in a line and attempt to move the cursor to another one, the line is not underlined with an angular red line or something like that, but you get a fucking modale error dialog. Occasionally makes me wanna yell out of rage.
To check for some semantic errors, you can compile the whole script, but instead of showing a table with all error descriptions and line numbers, you get a modale dialog again. It shows the error description of the first found error and puts the cursor to the corresponding line if you click ok. Fix it, compile again, message box about the second error. Fix it, compile again, and so on.
The following paragraphs are no rant but a funny anecdote, again about the country id stuff.
Without an ORDER BY clause, the sort order of the results of a SQL query is usually undefined. When adding the country ids, I wanted the alphabetically first country to have the id 1, the second 2 and so on. Of course there's no reason to do so, but it just looks nice in the database...
Curiously, I tried the following statement:
UPDATE (select * from countries order by country_name asc) SET id = country_id_seq.nextVal;
And it worked! Did not you could use selects instead of table names in an update statement.
So I continued my work refactoring the script to use the ids instead.
Some users had extended rights, they were able to view and edit records for other countries other than their own too. Previously, this was handled in the VB script.
IF user_country=A AND (record_country=A OR record_country=B record_country=C)
...
ELSEIF user_country=B AND (record_country=B OR record_country=D record_country=E)
...
Awesome, yes. So I created a table for user rights, consisting of a column for the country id of the editing user and one for the country ids he is allowed to edit.
I dumped the CREATE TABLE and INSERT statements into a file to add the data to the test and production databases.
Everything was working flawlessly, so after verifying it worked in the test environment too, it went into production.
A few days later we got a call from another employee abroad. She couldn't access her records. Alright, so I had a look into the database. The country id in the RIGHTS table was off by one. Figured a typo, fixed it and right before I closed the window, I noticed that EVERY SINGLE COUNTRY ID IN THE WHOLE TABLE WAS OFF BY ONE. Meaning, not a single user would have been able to get access to his/her records.. luckily no one seemed to have used it for days, or else we would have got dozens of emails and calls.
I was new at the company back then, and I panicked. I didn't even try to correct the ids via UPDATE statements, I fixed them manually line by line.
Still slightly shaking, I investigated how this could be possible. The reason was this:
UPDATE (select * from countries order by country_name asc) SET id = country_id_seq.nextVal;
The first country in the country table was "Åland Islands". In the dev enviroment, the 'Å' was regarded as a special character, so the country was sorted behind 'Z' and got the last id.
In the test and production environments, which I guess are using a different Oracle version, it was regarded as an 'A' and got the id 3.
So, in the dumped INSERT script of the RIGHTS table, all country ids greater than 2 were too low by one...