Tables with multiple primary keys (or Halle Berry hates World Peace)

In a perfect world Jews and Arabs would be best of buddies, wars would rage only behind the pixelated curtains of computer games, all hunger would be metaphorical rather than literal, the global recession would have been foreseen and avoided, and all database tables would be perfectly normalised. Oh, and Halle Berry would be in my bedroom now, dressed in nothing but a flirtatious look, waiting impatiently for me to finish this blog entry.

But no, we don't live in any sort of utopia and wars, hunger and debt scar the face of the globe, and most real-life databases are littered with poorly normalised tables.

(And Halle Berry still hasn't replied any of my emails!)

The database I work on is, perhaps, a little untidier than most, and one problem that I faced when I first started building an Apex application against it is the large number of tables with primary keys that are an unholy combination of multiple columns. Apex gives you some leeway (perhaps not enough) and will cope with two-column primary keys, but when you get to those miserable tables with four- and five-column primary key columns? You're on your own, my friend.

This is the point at which most experts would snootily tell you to simply sort out your database and change your tables. But we know that in real life that's not always an option. Which is why the generally recommended Plan B is to create a view based on your table and, with it, an instead-of trigger to manage the data. Your view should have a single column that represents the primay key (how about all your real primary key columns concatenated?) and your trigger will use this id to update the real table when it needs to.

However if, like me, you've got dozens of these messy tables all over the place it'd be great if you could run a script that'll find them and create the views and triggers for you. Well, today's your lucky day, cos I wrote such a script. It creates views called table_name_V with a column called view_id and triggers called table_name_V_TRG to manage them.

/*
** This procedure creates a view for all tables in the database that
** have more than 2 columns in their primary key. The views it creates
** are named [table_name]_V (unless the table name is longer than 24
** characters long in which case it only takes the first 24 characters).
**
** This procedure also creates INSTEAD OF triggers for the views that it
** creates. These triggers handle updates, insertion and deletion from these views.
**
** Finally, the procedure ensures that all the grants that the view needs
** are in place.
**
** 1    15/12/2008    David Njoku     * Created this script.
*/
DECLARE
vView VARCHAR2(32767);
vTrg VARCHAR2(32767);
vKey VARCHAR2(32767);
vKey1 VARCHAR2(32767);
vUpd VARCHAR2(32767);
vDel VARCHAR2(32767);
vIns VARCHAR2(32767);
vVals VARCHAR2(32767);
vPriv VARCHAR2(32767);
vWhr VARCHAR2(32767);


BEGIN

-- Find the tables.
FOR I IN (SELECT UC.TABLE_NAME, COUNT(UC.TABLE_NAME)
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
HAVING COUNT(UC.TABLE_NAME) > 2
GROUP BY UC.TABLE_NAME) LOOP

-- Start building our scripts.
vView  := 'CREATE OR REPLACE VIEW 'SUBSTR(I.TABLE_NAME,1,24)'_V AS 'CHR(10)' SELECT ';
vTrg := 'CREATE OR REPLACE TRIGGER 'SUBSTR(I.TABLE_NAME,1,24)'_V_TRG 'CHR(10)' INSTEAD OF UPDATE OR DELETE OR INSERT ON 'I.TABLE_NAME'_V '
 CHR(10)' /* '
 CHR(10)' ** This trigger has been automatically generated '
 CHR(10)' */ '
 CHR(10)'BEGIN '
 CHR(10)' IF UPDATING THEN '
 CHR(10)'   UPDATE 'I.TABLE_NAME' 'CHR(10);

-- Now let's find out what the columns are. First empty our variables.
vKey  := NULL;
vKey1 := NULL;
vUpd := NULL;
vDel := NULL;
vVals := NULL;
vWhr := NULL;


FOR J IN (SELECT COLUMN_NAME
 FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
 AND UC.CONSTRAINT_TYPE = 'P'
 AND UC.TABLE_NAME = I.TABLE_NAME) LOOP


 -- vKey will be the columns concatenated with 
 -- a period between them. You'll see why later.
 IF vKey IS NULL THEN
  vKey := J.COLUMN_NAME;
 ELSE
  vKey := vKey'''.'''J.COLUMN_NAME;
 END IF;

 -- vDel is for the delete bit.
 IF vDel IS NULL THEN
  vDel := CHR(10)'   DELETE 'I.TABLE_NAMECHR(10)'   WHERE 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 ELSE
  vDel := vDel'   AND 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 END IF;

 -- vWhr is there where clause for updating.
 IF vWhr IS NULL THEN
  vWhr := CHR(10)'WHERE 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 ELSE
  vWhr := vWhr' AND 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 END IF;
END LOOP;

-- Now lets get all the columns in the table.
FOR J IN (SELECT COLUMN_NAME
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = I.TABLE_NAME) LOOP

 -- vKey1 will the columns separated by commas.
 IF vKey1 IS NULL THEN
  vKey1 := J.COLUMN_NAME;
 ELSE
  vKey1 := vKey1', 'CHR(10)J.COLUMN_NAME;
 END IF;

 -- vVals are going to be used in the insert section.
 IF vVals IS NULL THEN
  vVals := '  :NEW.'J.COLUMN_NAME;
 ELSE
  vVals := vVals','CHR(10)'  :NEW.'J.COLUMN_NAME;
 END IF;

 -- vUpd is for the update bit.
 IF vUpd IS NULL THEN
  vUpd := '   SET 'J.COLUMN_NAME' = :NEW.'J.COLUMN_NAME' ';
 ELSE
  vUpd := vUpd', 'CHR(10)'   'J.COLUMN_NAME' = :NEW.'J.COLUMN_NAME' ';
 END IF;

END LOOP;


-- Now that we've got all the pieces all we have to do is stick em together.
vView  := vView' 'vKey' VIEW_ID, 'CHR(10)vKey1CHR(10)'FROM 'I.TABLE_NAME;
vTrg := vTrgvUpdvWhr'; 'CHR(10)' ELSIF DELETING THEN'vDel'   ; 'CHR(10)
 ' ELSE /* inserting */ 'CHR(10)'   INSERT INTO 'I.TABLE_NAMECHR(10)
 '   ('vKey1') 'CHR(10)'   VALUES ('vVals'); 'CHR(10)' END IF;'CHR(10)'END;';

-- Finally, execute the statements.
EXECUTE IMMEDIATE vView;
EXECUTE IMMEDIATE vTrg;

-- Before we leave we need to grant permissions on our view.
FOR J IN (SELECT GRANTEE, PRIVILEGE
 FROM DBA_TAB_PRIVS
 WHERE TABLE_NAME = I.TABLE_NAME
 AND PRIVILEGE IN ('SELECT','UPDATE','DELETE')) LOOP

 vPriv := 'GRANT 'J.PRIVILEGE' ON 'SUBSTR(I.TABLE_NAME,1,24)'_V TO 'J.GRANTEE;
 EXECUTE IMMEDIATE vPriv;
END LOOP;
END LOOP;
END;
/

Of course none of the above is exactly rocket science, but if you're at this particular stage of your Apex development I figured it might save you an hour or so. Now, go waste it on Digg. I'm off to email Halle Berry.

Again.

EDIT: It's been pointed out to me that all the concatenation pipes are missing from the script above. Which is kinda strange since I definitely put them in. If I can't sort it out you might have to put them back in yourself. I apologise.