How many of you are familiar with K.I.S.S? Lick it up? God of Thunder? Cold gin? Any of these sound familiar? If so, I applaud your good taste in bad music. But, I am talking about the Keep It Simple Stupid mantra many of those in IT have forgotten or eschew in favor of overly complex systems that they can gloat about designing and generally fuck up in one way or another. I am reminded of a guy I used to work with that loved to design and implement overly complex systems because he sounded smart. His white board designs invariably resembled His Holiness The Flying Spaghetti Monster. This is the guy that didn't trust the native windows management stuff so wrote his own which resulted in an application that docked in the upper left corner and could not be moved. Yeah, that is way better than a regular Windows window dude! Not.
The new systems we write and the old systems we maintain and improve grow in complexity naturally. Some of them are needfully complex from day one, but some can start quite simple and grow complex over time as features are added and new systems come up that need to be utilized. For the love of everything holy, do not artificially create complexity. Make stuff as stupid simple as you can from the beginning. In all that you do, this will pay dividends in the future.
I was tasked recently with comparing databases for an application we have with a couple dozen separate instances. Ideally, each of these instances would be identical in structure (or nearly so) and differ on data mainly (Customer 1 in Database A is not the same as Customer 1 in Database B). Unfortunately, we have only recently gotten into structured deployments using Hudson, not to mention a rogue DB Developer we had on staff back in the day (holla!). Over the years, there has been divergence. Some of them are easy to identify as useless or no longer useful items that we can clean up, but some, oh, some are hard to identify as such. In mature systems in mature organizations, the keys to the databases are heavily guarded and formal processes are in place to keep most people out. Some start ups even into their adolescent phase have thrown the doors wide open. How this starts is small businesses or business units that have either no formal DBA or only a couple of coders and a DBA that is wearing a few different hats. In strange cases you may have a boss that is making decisions that are poorly thought out. Whichever happens, what you end up with is objects that you have no idea who created, why they were created, if they are still in use, or what other objects might depend on those. What you end up with is a crazy Jenga game where the wrong table drop can mean long night spent bailing the system out, grumbles and finger pointing from your peers and maybe even the loss of gainful employment. Tread carefully.
This little statement can be a lifesaver in this situation:
SELECT name, line, text
FROM dba_source
WHERE upper(text) like upper('%<Object name to search>%');
Replace <Object name to search> with the table or procedure name you want to check. If you do this and get a table or view does not exist error, you need to get your DBA involved to grant you the appropriate data dictionary privileges. This statement will search stored procedures, functions, packages and the like for a reference to the searched text. If the table is one-off for a specific reason, probably due diligence was not used during its creation so the dependencies tab in a DB browsing tool or the dependency data dictionary table would not have any information to assist you in making a decision regarding the object's worth. You may be able to deduce after some inspection whether it is a useful and still actively used object. But you may not, and individual inspection of items doesn't scale. What if there are 50 objects that do not match? Individual inspection would be a month long task if not longer.
Now, consider table constraints for a moment. Let's say when we built our recipe table from earlier posts we made sure the recipe id could never be null. One way, and a very popular way of doing this is directly in the table definition:
Create Table RECIPE (
RECIPE_ID NUMBER NOT NULL
.
.
.
);
Now, let's say you want to monetize your recipe application. Every new subscriber gets their own database. A year down the road you have 50 subscribers so you have 50 database schemas with roughly identical copies of the recipe database structure. Now, you hire a neighborhood kid to do some light tech work that is beneath you as the King of the new Recipe Database Empire FaceCookBook or CookFaceBook or CookBook? You are too busy with discussions of your impending IPO and talks with Bono and what not to compare your databases. Your new hire compares everything, including constraints. What would you say if I told you that your 50 databases have 50 different constraints for each original constraint? Surprised? When you think about it, it makes sense but I never took the time to think about it until it was too late. My comparison of 27 databases have 120,000+ collisions on constraints. Every table that had an in line NOT NULL declaration on a column or a DEFAULT value declaration had a different name in each database. Oracle has an automatic sequence that is behind the scenes and when you declare a column to be NOT NULL or DEFAULT 0 or whatever, the way Oracle implements and enforces that is by creating a system named constraint in the form of SYS_C00000000001 or something like that where the 1 is the value from the system constraint sequence. Since you are letting each individual system name the constraints whatever it wants, there is no guarantee that SCHEMA1.SYS_C0000000001 is identical to SCHEMA2.SYS_C0000000001. Basically, all hell breaks loose. You get these false positives or probable false positives that muddy the waters so you cannot see immediately that TABLE C in SCHEMA 48 is missing a constraint on COLUMN X.
Let's consider an alternative. Try this instead:
CREATE TABLE recipe
(
recipe_id NUMBER CONSTRAINT recipe_recipe_id_nn NOT NULL
.
.
.
);
Now, the naming convention lacks something but IMHO it is far superior to SYS_C0001869645. If you ever tried to insert a recipe with a recipe id, instead of an error message that says SYS_C000689345 violated you would see RECIPE_RECIPE_ID_NN violated. You immediately know that the recipe id column in the recipe table should be not null. And, when the neighborhood kid does the compare for you, he doesn't interrupt your IPO meeting with Jon Bon Jovi and Curt Schilling to tell you that there are 120,000+ discrepancies between databases and WTF is going on!?!!?! It could get ugly.
For more on constraints, check out docs.oracle.com, www.oraclefaq.com, http://jonathanlewis.wordpress.com (I like this guy), http://asktom.oracle.com, or Burleson at http://www.dba-oracle.com/. If you find a page with Cowboy Burleson on it you have to take a shot but if you find a page with Uncle Touchy Pedo Burleson on it you have to put yourself on a list and seek therapy - extra points if you take three shots, do the tuck and exclaim, "It puts the lotion on its skin, or it gets the hose again!"
No comments:
Post a Comment