Friday, November 25, 2005

Code Generation Frenzy

Today's WTF moment is a bit of a monster, so I will try to explain what it does, and you can skim the actual code (this is probably a good idea) and still appreciate it.

Imagine that you have some static reference tables, let's say ORDER_STATUSES and LOCATIONS (amongst others), and you decide it would be a good idea to build an interface so that any application code can simply retrieve, say, the default order status or the head office location.

Being a bit clever, you notice that all the tables have the same structure, consisting of an ID and a description, and it occurs to you that if you UNIONed them all together, with the table name as a "type" key, you would have that generic lookup table you always wanted, along with a generic cursor to query it. (It avoids having one cursor per table, after all, so there is an efficiency gain already, right?) So you write this:

/* -------------------------------------------------------------------------------------
Name:    xxxx
Author   xxxx
Description: Script to create the types functions dynamically during schema creation
Input parameters: None
----------------------------------------------------------------------------------------*/
set serveroutput on size 1000000

variable v_destination_dir varchar2(256);
variable v_errorfilename varchar2(256);
variable v_datafile_name varchar2(256);

BEGIN
    :v_destination_dir := '&1';
    :v_errorfilename := 'type_model_functions.err';
    :v_datafile_name := 'type_model_functions.lst';
END;
/

DECLARE
    CURSOR c_tables IS 
        SELECT DISTINCT table_name
        FROM   types_functions
        ORDER BY table_name;

    CURSOR c_types IS 
        SELECT table_name, function_name, REPLACE(type_value,'''','''''') type_value 
        FROM   types_functions
        WHERE  function_name IS NOT NULL
        ORDER BY table_name,function_name;

    v_prev_rec number; 
    file_handle  UTL_FILE.FILE_TYPE :=
                 UTL_FILE.FOPEN (:v_destination_dir,  :v_datafile_name, 'W'); 

BEGIN
    UTL_FILE.PUT_LINE(file_handle, 'set define off');
    UTL_FILE.PUT_LINE
    (file_handle, '/*----------------------------------------------------------------');
    UTL_FILE.PUT_LINE
    (file_handle, ' Name: type_model_functions.lst');
    UTL_FILE.PUT_LINE
    (file_handle, ' Description: Functions to return the id from the types tables');
    UTL_FILE.PUT_LINE
    (file_handle, ' (Package Specifications)');
    UTL_FILE.PUT_LINE
    (file_handle, ' Input parameters: None');
    UTL_FILE.PUT_LINE
    (file_handle, ' Author: xxxx');
    UTL_FILE.PUT_LINE
    (file_handle, chr(10));
    UTL_FILE.PUT_LINE
    (file_handle, '-----------------------------------------------------------------*/');
    UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'CREATE OR REPLACE VIEW types_view AS ');
    v_prev_rec := 0;

    FOR r_tables IN c_tables LOOP
        IF v_prev_rec <> 0 THEN
            UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'UNION');
        END IF;

        UTL_FILE.PUT_LINE
        ( file_handle
        , chr(9)|| 'SELECT typeid, typelabel, ''' ||
          r_tables.table_name||''' tablename from '||r_tables.table_name );

        v_prev_rec := c_tables%rowcount;
    END LOOP;

    UTL_FILE.PUT_LINE(file_handle, '/');
    UTL_FILE.PUT_LINE(file_handle, 'show err');
    UTL_FILE.PUT_LINE(file_handle, 'drop public synonym types_view ;');
    UTL_FILE.PUT_LINE(file_handle, 'create public synonym types_view for types_view ;');
    UTL_FILE.PUT_LINE(file_handle, 'grant select on types_view to public;');

    UTL_FILE.PUT_LINE(file_handle, 'CREATE OR REPLACE PACKAGE types AS ');

    FOR r_types IN c_types LOOP
        UTL_FILE.PUT_LINE
        ( file_handle
        , chr(9) ||
          '-- Function to return the typeid for ' ||
          r_types.type_value || ' ' || r_types.table_name );

        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'FUNCTION '||r_types.function_name||' RETURN NUMBER;');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'PRAGMA RESTRICT_REFERENCES ('||r_types.function_name||', WNDS);');
    END LOOP;

    UTL_FILE.PUT_LINE(file_handle, 'END types;');
    UTL_FILE.PUT_LINE(file_handle, '/');
    UTL_FILE.PUT_LINE(file_handle, 'show err');
    UTL_FILE.PUT_LINE(file_handle, chr(10));

    UTL_FILE.PUT_LINE
    (file_handle, 'CREATE OR REPLACE PACKAGE BODY types AS ');
    UTL_FILE.PUT_LINE
    (file_handle, chr(9)||'CURSOR c_types_view(p_typelabel varchar2, p_tablename varchar2) IS ');
    UTL_FILE.PUT_LINE
    (file_handle, chr(9)||'SELECT typeid from types_view ');
    UTL_FILE.PUT_LINE
    (file_handle, chr(9)||'WHERE  typelabel = p_typelabel and tablename = p_tablename ;');

    FOR r_types IN c_types LOOP
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'FUNCTION '|| r_types.function_name||' RETURN NUMBER IS ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'v_typeid'||chr(9)||'NUMBER; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'BEGIN');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'OPEN  c_types_view (''' ||
                                        r_types.type_value||''','''||r_types.table_name||''');');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'FETCH c_types_view INTO v_typeid;');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'IF c_types_view%NOTFOUND THEN ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| chr(9)|| 'RAISE no_data_found; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'END IF; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'CLOSE  c_types_view;');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'RETURN v_typeid; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'EXCEPTION');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'WHEN OTHERS THEN');

        UTL_FILE.PUT_LINE
        ( file_handle
        , chr(9)|| chr(9)|| chr(9) ||
          'RAISE_APPLICATION_ERROR(-20001, ''Function types.' ||
          r_types.function_name || ': '''||'||sqlerrm );' );

        UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'END '|| r_types.function_name|| ';');
    END LOOP;

    UTL_FILE.PUT_LINE(file_handle, 'END types;');
    UTL_FILE.PUT_LINE(file_handle, '/');

    UTL_FILE.PUT_LINE(file_handle, 'show err');

    UTL_FILE.PUT_LINE(file_handle, 'drop public synonym types;');
    UTL_FILE.PUT_LINE(file_handle, 'create public synonym types for types;');
    UTL_FILE.PUT_LINE(file_handle, 'grant execute on types to public;');

    UTL_FILE.PUT_LINE(file_handle, 'exit');

    UTL_FILE.FCLOSE (file_handle);
END;
/

@&1/type_model_functions.lst

exit
Now, given a set of values in TYPES_VALUES like this:
TABLE_NAME      FUNCTION_NAME                TYPE_VALUE
--------------- ---------------------------- ---------------
order_statuses  get_default_order_status     Default
order_statuses  get_order_cancelled_status   Cancelled
order_statuses  get_order_dispatched_status  Dispatched
order_statuses  get_order_closed             Closed
locations       get_head_office_location     Head Office
locations       get_accounts_office_location Accounts
the script generates something like this:
set define off
/*----------------------------------------------------------------------------
Name: type_model_functions.lst
Description: Packaged functions to return the typeid from the types tables
Input parameters: None
Author: xxxx
-----------------------------------------------------------------------------*/

CREATE OR REPLACE VIEW types_view AS 
SELECT typeid, typelabel, 'locations' tablename from locations
UNION
SELECT typeid, typelabel, 'order_statuses' tablename from order_statuses
/
show err
drop public synonym types_view ;
create public synonym types_view for types_view ;
grant select on types_view to public;

CREATE OR REPLACE PACKAGE types AS 
    -- Function to return the typeid for Accounts locations
    FUNCTION get_accounts_office_location RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_accounts_office_location, WNDS);

    -- Function to return the typeid for Head Office locations
    FUNCTION get_head_office_location RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_head_office_location, WNDS);

    -- Function to return the typeid for Default order_statuses
    FUNCTION get_default_order_status RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_default_order_status, WNDS);

    -- Function to return the typeid for Cancelled order_statuses
    FUNCTION get_order_cancelled_status RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_order_cancelled_status, WNDS);

    -- Function to return the typeid for Closed order_statuses
    FUNCTION get_order_closed RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_order_closed, WNDS);

    -- Function to return the typeid for Dispatched order_statuses
    FUNCTION get_order_dispatched_status RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_order_dispatched_status, WNDS);
END types;
/
show err


CREATE OR REPLACE PACKAGE BODY types AS 
    CURSOR c_types_view(p_typelabel varchar2, p_tablename varchar2) IS 
    SELECT typeid from types_view 
    WHERE  typelabel = p_typelabel and tablename = p_tablename ;

    FUNCTION get_accounts_office_location RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Accounts','locations');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_accounts_office_location: '||sqlerrm );
    END get_accounts_office_location;

    FUNCTION get_head_office_location RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Head Office','locations');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_head_office_location: '||sqlerrm );
    END get_head_office_location;

    FUNCTION get_default_order_status RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Default','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_default_order_status: '||sqlerrm );
    END get_default_order_status;

    FUNCTION get_order_cancelled_status RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Cancelled','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_order_cancelled_status: '||sqlerrm );
    END get_order_cancelled_status;

    FUNCTION get_order_closed RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Closed','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_order_closed: '||sqlerrm );
    END get_order_closed;

    FUNCTION get_order_dispatched_status RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Dispatched','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_order_dispatched_status: '||sqlerrm );
    END get_order_dispatched_status;
END types;
/
show err
drop public synonym types;
create public synonym types for types;
grant execute on types to public;
exit

Now all you have to do is ensure that there are values in the LOCATIONS table with typelabels 'Head Office', 'Accounts' and so on, and whenever you run the script it will create or replace a whole new package implementing whatever you put in TYPES_FUNCTIONS, and application code can simply invoke types.get_accounts_office_location().

Now, I wonder whether there is a way to generate the TYPES_FUNCTIONS table...

3 comments:

Thai Rices said...

Brilliant.

I once worked on a system where the application coders had written a program to parse the application source code and automatically generate pl/sql packages to return the constants that they declared in the source code.

That way they "could generate triggers to validate the data as it was inserted" by issuing the standard that all column names storing source declared constants must be called the same thing as the source code constant.

Rjamya said...

Time to stop accepting comments without 'word verification' ... you guys are gettin spammed !@!@

Raj

William Robertson said...

Thanks Raj. So far we've just had the odd one or two spams and I've been deleting them manually. I might have to switch on word verification if we start getting a lot.