In my last post, I outlined some of my thoughts about lookup
tables, and how they should be structured and organized. Now I want to write
some code generators, to make it easier to create and manage lookup tables.
Since I want lookup tables to have the same columns and follow some naming
conventions, why should I write the same basic code every time I create a new
lookup table? While my thoughts about lookup tables should probably apply to
just about any relational database, stored procedure code is not as portable.
I work in an Oracle database, so all the code I'm going to show you is written
in PL/SQL. But you may be able to do something similar in the procedural
language for your database.
An Index to the Lookup Tables
One thing I like to have is a table to store a list of all
the lookup tables in the schema. This isn't absolutely necessary, if you use a
naming convention for your lookup table names, since you could write a view of
a catalog view, like Oracle's USER_TABLES. However, I'm going to create a real
table named LOOKUP_TABLES:
CREATE TABLE lookup_tables (
Table_name VARCHAR2(30),
Decription VARCHAR2(255)
);
So my code generator is going to insert a row into this
table anytime that I create a new lookup table, and delete that row anytime I
drop a lookup table. Once you start using your lookup tables, and start having
foreign keys that reference them, you probably aren't going to drop any lookup
tables, but you might during development, so I'll include a drop.
The Code Generator Package
In PL/SQL, I tend to write packages for all of my stored
procedures, with few or no stand-alone functions or procedures. While I'm
going to show the procedures separately, you may assume that they are all
inside a package named LOOKUP_UTIL. There are two special characteristics of
LOOKUP_UTIL.
In all of my databases, I have created a schema named COMMON
to hold all database objects that are shared among many application schemas and
users. Most of these have EXECUTE or SELECT granted to PUBLIC, and many have
public synonyms. Since many applications may generate lookup tables,
LOOKUP_UTIL would correctly be created in the COMMON schema. By the way, some
lookup tables might be shared between applications – my lookup table for U.S.
States and Territories would be a prime example of this. It might also be best
created in COMMON.
The second characteristic of LOOKUP_UTIL is that it is an
invoker's rights package, using the "AUTHID CURRENT_USER" clause.
That means that it executes with the database privileges of the user (or
schema) that called it. It is going to create tables and other database
objects – it should only do that in the current user's schema, and using
privileges granted to that user, not COMMON, the owner of the package. Many of
the packages in the COMMON schema share this characteristic.
Creating and Dropping Lookup Tables
Here is the code for creating a new lookup table. It
creates the table with the standard columns as I explained in my previous post
and adds a row to LOOKUP_TABLES. The same description to be stored in
LOOKUP_TABLES is also used as the table comment. There are a few things that
are here for demonstration purposes that might not be in real code. For
instance, I am using DBMS_OUTPUT to show the generated code in case the code
gets an error upon execution. In the real code, these would probably be in a
call to a logging package. I use LOGGER (https://github.com/OraOpenSource/Logger),
and I highly recommend it. If you have users who create lookup tables in
schemas other than their own, with the CREATE ANY privilege, you may want to add
an "owner" parameter.
PROCEDURE new_lookup_table (
table_name lookup_tables.table_name%TYPE,
description lookup_tables.description%TYPE DEFAULT NULL,
tablespace_name user_tablespaces.tablespace_name%TYPE DEFAULT
NULL
)
AS
v_table_name lookup_tables.table_name%TYPE;
create_cmd VARCHAR2 (2000);
BEGIN
IF table_name IS NULL THEN
raise_application_error (
-20000,
'Table name of new lookup table may not be null.'
);
ELSIF UPPER (table_name) LIKE 'LKUP_%' THEN
v_table_name := UPPER (table_name);
ELSE
v_table_name := 'LKUP_' || SUBSTR (UPPER (table_name), 1,
25);
END IF;
BEGIN
INSERT INTO lookup_tables (table_name, description)
VALUES (v_table_name, description);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (
-20002,
'Lookup table named ' || v_table_name || ' already
exists.'
);
END;
create_cmd :=
'CREATE TABLE '
|| v_table_name
|| '(
code VARCHAR2(10) NOT NULL,
short_description VARCHAR2(30),
description VARCHAR2(255),
display_sequence NUMBER,
active CHAR(1) DEFAULT ''Y'' NOT NULL,
CONSTRAINT '
|| SUBSTR (v_table_name, 1, 27)
|| '_PK PRIMARY KEY (code))'
|| '
ORGANIZATION INDEX'
|| CASE
WHEN tablespace_name IS NOT NULL THEN
'
TABLESPACE ' || tablespace_name
ELSE
NULL
END;
BEGIN
EXECUTE IMMEDIATE create_cmd;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (create_cmd);
RAISE;
END;
EXECUTE IMMEDIATE
'COMMENT ON TABLE ' || v_table_name || ' IS ''' ||
description || '''';
END new_lookup_table;
The code for dropping a lookup table is similar. Note that
my version includes the CASCADE CONSTRAINTS clause, so that if there are
foreign keys referencing the lookup table being dropped, those constraints will
be dropped too. You might prefer not to use this clause and allow the drop to
fail if there are references to the table.
PROCEDURE drop_lookup_table (table_name
lookup_tables.table_name%TYPE)
IS
v_table_name lookup_tables.table_name%TYPE;
drop_cmd VARCHAR2 (2000);
BEGIN
IF table_name IS NULL THEN
raise_application_error (
-20001,
'Table name of lookup table to be dropped may not be null.'
);
ELSIF UPPER (table_name) LIKE 'LKUP_%' THEN
v_table_name := UPPER (table_name);
ELSE
v_table_name := 'LKUP_' || SUBSTR (UPPER (table_name), 1,
25);
END IF;
DELETE FROM lookup_tables
WHERE UPPER (table_name) = v_table_name;
IF SQL%ROWCOUNT = 0 THEN
raise_application_error (
-20003,
'Table named '
|| v_table_name
|| ' does not exist in the lookup table catalog.'
);
END IF;
drop_cmd := 'DROP TABLE ' || v_table_name || ' CASCADE
CONSTRAINTS';
BEGIN
EXECUTE IMMEDIATE drop_cmd;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (drop_cmd);
RAISE;
END;
END drop_lookup_table;
Next Time
That's enough generation code for now. I'll continue in my
next post with some reasons for simulating a single table of tables with a
view. I'll show you the code for generating that view, and for generating an API
to make the view updatable.
