Extending the G/Technology metadata system

If you publish metadata, G/Technology creates a set of .raw- and .ddc files, the metadata to be stored in these tables/ddc-files is read from table G3E_METADATAKEYS. This gets truncated and filled during the metadata-publishing process by procedure MG3EPopulateMetadataKeys. This blog-entry describes how to extend the G/Technology metadata system with your own tables.

Key to extending the G/Technology metadata system is table G3E_ANALYSIS. When MG3EPopulateMetadataKeys runs, it fills the G3E_METADATAKEYS-table with various data sources, but it also enumerates table G3E_ANALYSIS, offering a change to plugin into the metadata system. Suppose you want to maintain a list with attribute numbers for a given component-feature combination, like this:

CREATE TABLE PLE_COMMONATTRIBUTES
(
  PLE_CONO NUMBER( 9) constraint PLE_COMMONATTRIBUTES_CONO primary key,
  G3E_FNO NUMBER( 5) NOT NULL,
  G3E_CNO NUMBER( 5) NOT NULL,
  G3E_ANO NUMBER( 9) NOT NULL,
  G3E_EDITDATE DATE NOT NULL,
  constraint t_uk unique ( G3E_FNO, G3E_CNO, G3E_ANO) 
);

This information then can be read by a custom command, but it should be published to avoid database roundtrips. The steps needed to include this table in the published metadata are these:

Create a row in G3E_ANALYSIS with the information needed for GTech to create optimized tables
Create needed tables
Create packages to create optimized tables

The first step looks like this :

insert into G3E_ANALYSIS (G3E_ADNO, G3E_USERNAME, G3E_TYPE, G3E_TABLE, G3E_OPTIMIZEDTABLE, G3E_TOOLTIP, G3E_EDITDATE)
select  max(G3E_ADNO)+1 , 'PLE Metadata','General','PLE_METADATASUPPORT','PLE_METADATASUPPORT_OPTABLE',	'Provides access to the PLE metadata.',  sysdate from G3E_ANALYSIS;

A row is inserted pointing to a table PLE_METADATASUPPORT, with an optimized table called PLE_METADATASUPPORT_OPTABLE. This table needs to hold the information about what tables to publish :

insert into PLE_METADATASUPPORT	( PLE_MSNO, G3E_TABLE, 	G3E_OPTIMIZEDTABLE, G3E_EDITDATE )
values	( 1,'PLE_COMMONATTRIBUTES',	'PLE_COMMONATTRIBUTES_OPTABLE',	sysdate);

This will cause the metadatapublishing process to create a .raw file called PLE_METADATASUPPORT.raw with all the rows in it. Of course, tables PLE_METADATASUPPORT & PLE_COMMONATTRIBUTES need to exist :

CREATE TABLE PLE_METADATASUPPORT
(
  PLE_MSNO NUMBER( 9) constraint M_N_PLE_METADATASUPPORT_MSNO primary key,
  G3E_TABLE VARCHAR2(30 BYTE) NOT NULL CONSTRAINT M_U_PLE_METADATASUPPORT_TABLE unique,
  G3E_OPTIMIZEDTABLE  VARCHAR2(30 BYTE)	NOT NULL CONSTRAINT M_U_PLE_METADATASUPPORT_OPTABL unique,
  G3E_EDITDATE DATE NOT NULL
);

CREATE TABLE PLE_COMMONATTRIBUTES
(
  PLE_CONO NUMBER( 9) constraint	PLE_COMMONATTRIBUTES_CONO 		primary key,
  G3E_FNO NUMBER( 5) NOT NULL,
  G3E_CNO NUMBER( 5) NOT NULL,
  G3E_ANO NUMBER( 9) NOT NULL,
  G3E_EDITDATE DATE NOT NULL,
  constraint t_uk unique ( G3E_FNO, G3E_CNO, G3E_ANO) 
);

Also, MG3EPopulateMetadataKeys expects all tables part of the metadata system to have a package with the following methods :

  PROCEDURE CreateOptimizedTable;
  FUNCTION MapfileKey RETURN VARCHAR2;    
  FUNCTION PrimaryKey RETURN VARCHAR2;    
  FUNCTION OrderByKey RETURN VARCHAR2;    
  FUNCTION SortAscending RETURN NUMBER;

These packages need to exist for both tables PLE_METADATASUPPORT & PLE_COMMONATTRIBUTES and need to follow the following naming convention : TABLE_NAME & “_PKG”, so this would be PLE_METADATASUPPORT_PKG & PLE_COMMONATTRIBUTES_PKG. See the attachment for these packages. If these are not present or doesn’t have the expected method/functions, publishing metadata will fail. At last, you need to fill your metadata table :

insert into PLE_COMMONATTRIBUTES( PLE_CONO, G3E_FNO, G3E_CNO, G3E_ANO, G3E_EDITDATE )
		values	( 1, 22, 2202, 220206,	sysdate);
insert into PLE_COMMONATTRIBUTES( PLE_CONO, G3E_FNO, G3E_CNO, G3E_ANO, G3E_EDITDATE )
		values	( 2, 22, 2202, 220207,	sysdate);
insert into PLE_COMMONATTRIBUTES( PLE_CONO, G3E_FNO, G3E_CNO, G3E_ANO,	G3E_EDITDATE )
		values	( 3, 23, 2302, 230206,sysdate);
insert into PLE_COMMONATTRIBUTES( PLE_CONO, G3E_FNO, G3E_CNO, G3E_ANO, G3E_EDITDATE )
		values	( 4, 23, 2302, 230207,sysdate);

If you now publish metadata, you will find .raw files PLE_METADATASUPPORT_OPTABLE.RAW & PLE_COMMONATTRIBUTES_OPTABLE.RAW where the latter contains the information needed. As always, metadata information is actually published twice, in the form of .raw files but also in one of the .ddc files. In this case, the information is also present in Analysis.ddc.