Code Generation for Lookup Tables - Part 1

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.