Metaprogramming in SQL (Part 4)

In the past three posts, we have written metaprograms to create functions for comparing objects of built-in types, user-defined distinct types, ROW types, and ordinary ARRAY types. In today’s post, we will write a single metaprogram that can create comparison functions for all four.

The only obstacle to writing a generic metaprogram for built-in, UDDT, ROW, and ordinary ARRAY types is that we do not know how to determine which of the four it is from the name that is passed to our metaprogram. It turns out that there is a column named METATYPE in the SYSCAT.DATATYPES catalog view that we can use. Let’s look at a short example:

CREATE TYPE SIMPLETYPE AS INTEGER WITH COMPARISONS@
CREATE OR REPLACE TYPE ROWTYPE AS ROW (ONE INTEGER, TWO INTEGER)@
CREATE OR REPLACE TYPE ARRAYTYPE AS INTEGER ARRAY[]@

SELECT VARCHAR(TYPENAME, 10) TYPENAME, METATYPE 
FROM SYSCAT.DATATYPES 
WHERE TYPENAME IN ('SIMPLETYPE', 'ROWTYPE', 'ARRAYTYPE', 'INTEGER')@

This produces a result like the following:

TYPENAME   METATYPE
---------- --------
ROWTYPE    F       
ARRAYTYPE  A       
SIMPLETYPE T       
INTEGER    S

  4 record(s) selected.

We can use this to write a generic function that inspects this SYSCAT.DATATYPES view before deciding which kind of comparison function to create (implementations have been elided for readability, but are simply the code from the previous three articles in this series):

CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(SCHEMA_NAME VARCHAR(128),
                                                           MODULE_NAME VARCHAR(128),
                                                           TYPE_NAME VARCHAR(128))
BEGIN
  DECLARE META_TYPE CHAR(1);
  ...
  SELECT METATYPE INTO META_TYPE
  FROM SYSCAT.DATATYPES
  WHERE TYPESCHEMA = SCHEMA_NAME
    AND (TYPEMODULENAME IS NULL AND MODULE_NAME IS NULL OR TYPEMODULENAME = MODULE_NAME)
    AND TYPENAME = TYPE_NAME;
  IF (META_TYPE = 'F') THEN
    -- Create ROW type EQ function
  ELSEIF (META_TYPE = 'T' OR META_TYPE = 'S') THEN
    -- Create UDDT/built-in type EQ function
  ELSEIF (META_TYPE = 'A') THEN
    -- Create ARRAY type EQ function
  ELSE
    SIGNAL SQLSTATE VALUE '75000' SET MESSAGE_TEXT = 'Unrecognized metatype. Cannot create comparison function.';
  END IF;
END@

Now, we have a generic metaprogram that can create EQ functions for all four metatypes. The following example demonstrates its use on the built-in, row, and array metatypes:

SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TESTSCH.ROWTYPE AS ROW (ONE INTEGER, TWO INTEGER)@
CREATE OR REPLACE TYPE TESTSCH.ARRAYTYPE AS INTEGER ARRAY[]@
CALL CREATE_EQ_FUNCTION('SYSIBM', NULL, 'INTEGER')@
CALL CREATE_EQ_FUNCTION('TESTSCH', NULL, 'ROWTYPE')@
CALL CREATE_EQ_FUNCTION('TESTSCH', NULL, 'ARRAYTYPE')@
BEGIN
  DECLARE SIMPLE1 INTEGER;
  DECLARE SIMPLE2 INTEGER;
  DECLARE ROW1 TESTSCH.ROWTYPE;
  DECLARE ROW2 TESTSCH.ROWTYPE;
  DECLARE ARRAY1 TESTSCH.ARRAYTYPE;
  DECLARE ARRAY2 TESTSCH.ARRAYTYPE;
  SET SIMPLE1 = 1;
  SET SIMPLE2 = 1;
  SET ROW1.ONE = 1;
  SET ROW1.TWO = 1;
  SET ROW2.ONE = 1;
  SET ROW2.TWO = 1;
  SET ARRAY1[1] = 1;
  SET ARRAY2[1] = 1;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(SIMPLE1, SIMPLE2): ' || EQ(SIMPLE1, SIMPLE2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ROW1, ROW2): ' || EQ(ROW1, ROW2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ARRAY1, ARRAY2): ' || EQ(ARRAY1, ARRAY2));
  SET SIMPLE2 = 2;
  SET ROW2.TWO = 2;
  SET ARRAY2[1] = 2;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(SIMPLE1, SIMPLE2): ' || EQ(SIMPLE1, SIMPLE2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ROW1, ROW2): ' || EQ(ROW1, ROW2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ARRAY1, ARRAY2): ' || EQ(ARRAY1, ARRAY2));
END@

This should result in output like the following:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT  SYSIBM.INTEGER, RIGHT SYSIBM.INTEGER)
SPECIFIC EQ_SIMPLE CONTAINS SQL RETURNS INTEGER 
BEGIN
   DECLARE RESULT INTEGER;
   IF (LEFT = RIGHT) THEN
     SET RESULT = 1;
   ELSE
     SET RESULT = 0;
   END IF;
   RETURN RESULT;
END


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT TESTSCH.ROWTYPE, RIGHT TESTSCH.ROWTYPE)
SPECIFIC EQ_ROW READS SQL DATA RETURNS INTEGER
BEGIN
  DECLARE RESULT INTEGER;
  IF (LEFT.ONE = RIGHT.ONE AND LEFT.TWO = RIGHT.TWO) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT  TESTSCH.ARRAYTYPE, RIGHT TESTSCH.ARRAYTYPE)
RETURNS INTEGER SPECIFIC EQ_ARRAY READS SQL DATA
BEGIN
  DECLARE ID INTEGER DEFAULT 1;
  DECLARE RESULT INTEGER;
  IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
    SET RESULT = 1;
    WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
       SET RESULT = EQ(LEFT[ID], RIGHT[ID]);
       SET ID = ID + 1;
    END WHILE;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END 

DB20000I  The SQL command completed successfully.

EQ(SIMPLE1, SIMPLE2): 1
EQ(ROW1, ROW2): 1
EQ(ARRAY1, ARRAY2): 1
EQ(SIMPLE1, SIMPLE2): 0
EQ(ROW1, ROW2): 0
EQ(ARRAY1, ARRAY2): 0

That ends this series on metaprogramming for now. For the next two weeks, I am on vacation. Hopefully, I will have interesting new content to share when I get back.

If you enjoyed this post, make sure you subscribe to my RSS feed!
Share
  • Search

  • Related Posts