Database-to-Middleware Generic Results Set Interface

Pseudo-code.

List levels;
Vector errors = new Vector();
Vector warnings = new Vector();
Hash root = new Hash();
Hash branch = root;
levels[0] = root; // this hash added at the index 0

foreach row {
  if row.TYPE is 'data' { // this row is a hash
    if row.HASH_LEVEL > -1 {
      branch = new Hash();
      Hash parent = levels.getAt(row.HASH_LEVEL);
      parent.put(row.KEY, branch);
      levels[row.HASH_LEVEL+1] = branch;
    }

    foreach row.other.columns {
       if row.other.column.value is not null {
          if row.HASH_LEVEL == -1
             root.put(LowerCase(row.other.column.name), row.other.column.value);
          else
             branch.put(LowerCase(row.other.column.name), row.other.column.value);
       }
    }

  } else if row.TYPE is 'key' {
     case row.VALUE_TYPE {
       when 'string'   : branch.put(row.KEY, row.VALUE_STRING); break;
       when 'integer'  : branch.put(row.KEY, row.VALUE_INT); break;
       when 'boolean'  : branch.put(row.KEY, row.VALUE_INT==0?false:true); break;
       when 'timestamp': branch.put(row.KEY, row.VALUE_TIMESTAMP); break;
     }

  } else if row.TYPE is 'warning' {
    warnings.add(row.VALUE_STRING);

  } else if row.TYPE is 'error' {
    errors.add(row.VALUE_STRING);
  }
}

if warnings is not empty {
  root.put('warnings', warnings);
}

if errors is not empty {
  root.put('errors', errors);
}

Notes

• Do not use 'warning' and 'error' for column names or in a KEY column.
• In a hash every key must have a value even when we are not interested e in values, such as in example bellow. In which case we to provide some value for such keys, which would be typically String. However in Oracle empty string is equivalent to NULL. Thus you should provide string with a sole space in it.
• For extra columns, i.e. not show in a generic result set above, if TYPE is 'data' their names in lower case will be used is a keys with values coming from corresponding columns.
• HASH_LEVEL is used only in conjunction with TYPE 'data'. -1 indicates that extra columns in this row will be added to a root hash. When increasing HASH_LEVEL must do so gradually starting from 0, as it points to a parent hash in a stack like structure. It may jump when decreasing.
• For TYPE 'key' extra columns are ignored. VALUE_TYPE must not be null. Key and value pairs are added to a current branch. At the beginning root is considered to be current branch.
• Order of columns is not important.
• Columns not used in a hash generation may be omitted.

Examples

TYPE  KEY                 HASH_LEVEL  VALUE_TYPE  VALUE_STRING  OBJECT  CREATED_AT
-----------------------------------------------------------------------------------------------
data  NULL                -1          NULL        NULL          node    2005-02-13 22:17:35.0
data  groups              0           NULL        NULL          NULL    NULL
key   ASSU                NULL        string                    NULL    NULL
key   Aero/Astro          NULL        string                    NULL    NULL
key   Alumni Association  NULL        string                    NULL    NULL
key   Anthropology        NULL        string                    NULL    NULL
key   Art                 NULL        string                    NULL    NULL
key   Asian Languages     NULL        string                    NULL    NULL

(Hash)
<object>        -> [node]
<created_at>    -> [2005-02-13 22:17:40.0]
<groups>
  <Alumni Association>    -> []
  <ASSU>                  -> []
  <Art>                   -> []
  <Aero/Astro>            -> []
  <Asian Languages>       -> []
  <Anthropology>          -> []