AdHoc Queries

Fiber Cables

Introduction

G/Technology provides functionality to run dynamic queries, the so called ‘Ad-Hoc queries’. A Large telecom provider in the Netherlands is moving Fiber Cables from CRAMER to FOW and AdHoc-queries provide a great tool to analyze and visualize migrated data

At this provider Fiber Cables are contained by Fiber Inner Ducts who themselves are contained by Fiber Ducts. A fiber Duct has a Tag marked on it a.k.a. B217982 so workers can identify a Duct. Fiber Ducts go from Fiber Branch Enclosure to Fiber Branch Enclosure, Fiber Inner Ducts also do. Fiber Cables go from Fiber Splice Enclosures to Fiber Splice Enclosures. Fiber Branch Enclosures contain Fiber Splice Enclosures. This is illustrated by the following figure:

Fiber Cables

Fiber Cables

Click here for the legend.

This network is registered in 2 systems, CRAMER NIM (CRAMER Network Inventory Management) and GEOS FOW (GTechnology Fiber Optic Works), but Fiber Cables are registered only in NIM. This is illustrated by the next figures:

NIM-GEOS

NIM-GEOS

Fiber Cables are only registred in NIM, not in GEOS FOW. To enable better management of Fiber assets, registration of Fiber Cables need to be moved from NIM to GEOS FOW. Fiber Branch- and Fiber Splice Enclosures have an asset-id which is a number like ‘1001’ Fiber Cables have a name like ‘Hd-Hd 1’. This name is also maintained with Fiber Inner Ducts in NIM and is used to put Fiber Cables from NIM into the right Fiber Inner Duct in GEOS FOW. In the current GEOS FOW system where there are no Fiber Cables (yet) present, this looks like this.
The Fiber Inner Ducts with the Fiber Cable name on it are indicated in red and are contained in a Duct. You can also see that the ‘Contains’-node of the Fiber Inner Ducts do not have a value, but this is where migrated Fiber Cables will appear.

When all these assets are registered correctly, Fiber Cables can be migrated from NIM to FOW. Both systems NIM and GEOS FOW are running for several years so mismatches between the 2 systems exists and not all Fiber Cables can be migrated correctly. To analyze and visualize the quality of migration, Ad-Hoc queries play an important role.

Fiber Inner Ducts without cables

One of the queries giving insight in the data migration has the following requirements: Show all Fiber Ducts with a Fiber Inner Duct in it who don’t have a cable inside it. If the ‘CABLE_NAME’ attribute of these ducts had been set, then they should have a cable inside it and are not reserved stock aka. reserved for future use. In FOW Fiber Inner Ducts (fno=4100) have a ‘Contained By’ relation with Fiber Ducts (fno=4000), and Fiber Cables (fno=7200) also have a ‘Contained By’ relation with Fiber Inner Ducts. Using this relation, the following Ad-Hoc query lists all Fiber Ducts with Fiber Inner Ducts in it without a cable :

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
		and rownum < 100
	and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)

You can run this query using the ‘Ad Hoc’-query wizard (click here for a video) and after some scrolling the output may look like this :

Basic AdHoc Query

Basic AdHoc Query

The Query Name is added to the ‘Queries’-node in the Legend and the extends of the Netherlands is shown. The query has run successful, and there are many Fiber Inner Ducts without a Fiber Cable in it but we cannot really see them without turning off all the items in the legend and then zooming in into one of the items of the resultset.

Notes:

  • Before creating your first Ad-Hoc query, you need to define an ‘Area Of Interest’
  • We are limiting the output to 100 rows
  • Ad-Hoc queries always need to output at least the following attributes : G3E_FNO, g3E_FID, G3E_CNO & G3E_CID. These attributes enable selection of separate features from the result set and should point to a graphical components for selection in a Map window
  • AdHoc-queries are saved in a Workspace, when creating a new Workspace previous Ad-Hoc are not available
  • When creating AdHoc-queries the feature selected is just a placeholder, but the Wizard only continues to the second screen if you select a feature. Once you have done that you can query any feature
  • You can change the appearance of your result set even after running it using Display Control
  • Save and test your query outside G/Technology for easy development

Avoid joints

While the query used does its work, table GC_FDUCT_L is joined to get the Graphic Lines in the system representing Fiber Ducts. Since the Feature- & Component numbers for this feature don’t change and are required, non-repeating components we can rewrite the query as follows:

select  4000 g3e_fno
	, a.g3e_ownerfid g3e_fid
	, 4010 g3e_cno
	, 1 g3e_cid
	, a.g3e_fid	fid_inner_duct
	, gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
		and rownum < 100
	and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)

The necessary keys g3e_fno, g3e_cno & g3e_cid are still present but are now created using hardcoded value instead of SQL-joins, only the value for g3e_fid is variable but is now fetched from GC_CONTAIN. In this approach, table GC_FDUCT_L is no longer required.

Output results to a DataTable

This first queries showed we have some problems with our data migration, but the results are not really in a readable format. If you have a query with multiple results like this one, it may be convenient to output the results to a Datatable.

Output to datatable

If you output results to a datatable you can easily do the following:

  • Fit a feature
  • Select a feature in Feature Explorer
  • Export results

Note: If your result set is very large, it is better to export your results using a dedicated SQL-tool.

Custom ‘Areas Of Interest’

When creating an AdHoc-query, you can limit the results to be contained in a predefined area, the so called ‘Areas Of Interest’ (AOI). If you do so, G/Technology will first run a dedicated query to fetch features from your active AOI and then add some dedicated code after your query. If we take our first query:

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
	and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)

will be changed to:

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
	and not exists (select g3e_fid from gc_contain b , AOIQUERYRESULT AQR WHERE (GC_FDUCT.G3E_FID = AQR.G3E_FID AND AQR.G3E_USERNAME='GTFIBER' AND AQR.G3E_QUERYNAME='aa') AND (b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid))

This generates an Oracle error. Creating an Ad-Hoc query using an AOI can be hard to get working, but you can use Oracle Spatial to create your own ‘Areas Of Interest’. This feature is called ‘CLLI Boundary’ is used to create Areas of Interest, but we can also use it in Oracle Spatial to limit our query to only process a given area called ‘Hd-C’:

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
		, gc_bnd_p	
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
		and gc_bnd_p.feature_type = 'CLLI'		
		and gc_bnd_p.wc_clli = 'Hd-C'
		and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)
		and SDO_GEOM.RELATE( gc_bnd_p.g3e_geometry, 'ANYINTERACT', gc_fduct_l.g3e_geometry, 0.1) = 'TRUE'

The SDO_GEOM.RELATE Oracle Spatial operator is used in the where clause to get all the Fiber Ducts contained in area ‘Hd-C’.

Finding features with carriage returns in Attribute values

When users are entering attribute values using Feature Explorer they may be expecting G/Technology saving values when entering ‘Enter’. GTech will not do this, but will instead store a Carriage return/Line Feed with the attribute value. This will lead to pollution of attribute values in the database and give mismatches between tag-id’s from ducts coming from NIM and ducts in GTech. During the migration we might for example be looking for two ducts with Tag-id ‘B217982’, but what we find I ‘B217982’ and ‘B217982Chr(10)Chr(13)’. The following Ad-Hoc query will show all Fiber Duct features (fno=4000) who’s cable_name attribute has the ‘CHR(13)CHR(10)’ character combination (CHR(13)=Carriage Return, CHR(10)=Line Feed) :

select g3e_fno
     , g3e_fid
	 , g3e_cno
	 , g3e_cid
	 , cable_name
from gc_netelem	
where gc_netelem.g3e_fno = 4000
and regexp_like( trim(cable_name), CHR (13) || CHR(10) )

Click here for a Video of creating and running this Query.

Hope this Helps. Stephan

September 20, 2016 by G/Technology Intergraph 0

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:

  1. Create a row in G3E_ANALYSIS with the information needed for GTech to create optimized tables
  2. Create needed tables
  3. 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.

Custom raw files

Custom information in analysis.ddc

The scripts for this sample can be downloaded here.

Notes:

  • If you alter the structure of a metadata-table, you need to drop the optimized table for changes to be reflected

Hope this helps, Stephan

July 3, 2014 by G/Technology Intergraph Metadata 0

Netviewer tooltips

There are two ways to show tooltips in Netviewer, they both involve changing metadata. To enable tooltips in Netviewer, you need to add the following two lines to file Customize.inc, absolute file path “C:\Program Files\Intergraph\GTechnology\Program\GViewerApp\Customize\Customize.inc” :

var AllowToolTipsOnHover : boolean = true; 
var ToolTipsOnHoverDefault : boolean = true;

Once these two lines have been added the option ‘Tooltips on hover’ is added in Netviewer Options :

Tooltip option Netviewer

To enable tooltips in Netviewer when hovering over a feature you have two options, they both involve changing metadata :

  • Using an Identifier View
  • Use the same component for the primary graphic- and primary non graphic component

Let’s assume we have created a linear feature with 3 components, a line, a record and a label. The numbering scheme for this feature is shown in the following table :

Example feature

Example feature

This data was retrieved using the following query

select g3e_feature.g3e_fno
     , g3e_feature.g3e_username
     ,	g3e_feature.g3e_tooltip
     ,	g3e_component.g3e_cno
     ,	g3e_component.g3e_username
     ,	g3e_component.g3e_table
     ,	g3e_component.g3e_tooltip
     ,	g3e_component.g3e_lrno
     ,	g3e_component.g3e_type
   from g3e_featurecomponent
   inner join g3e_feature on g3e_feature.g3e_fno = g3e_featurecomponent.g3e_fno
   inner join g3e_component on g3e_component.g3e_cno = g3e_featurecomponent.g3e_cno
order by g3e_component.g3e_cno;

The feature has a record with a diameter-material attribute, and the label is showing the values. In G/Designer, it looks like this :

Initial view G/Designer

Initial view G/Designer

The cursor is positioned above the line component and the standard tooltip for this component as defined in G3E_COMPONENT.G3E_TOOLTIP concatenated with the fid is shown. Also, the feature has been selected and the standard Feature Explorer tooltip as defined in G3E_FEATURE.G3E_TOOLTIP with the fid is shown.

This is the initial Netviewer view :

Initial view Netviewer

Initial view Netviewer

Notice that the cursor is positioned above the line component but no tooltip is shown.

Metadata changes for Netviewer tooltips

Let’s try to create a Netviewer tooltip showing a fid together with custom attributes MATERIAL and DIAMETER.

To show tooltips in Netviewer when hovering over a feature, you have two options :

  • Use an Identifier View
  • Use the same component for the primary graphic- and primary non graphic component

Use an Identifier View

Using this option, you create a special type of component called a ‘attribute-only’ component.
The following steps are needed :

  1. Create a label
  2. Create an Identifier View
  3. Create the ‘attribute-only’ component
  4. Update the feature metadata to use the Identifier view
  5. Update all components to use the label
  6. Optional:Change component views

1. Create a label

We need to define what is shown in the tooltip, this is done using a regular label:

insert into g3e_label( g3e_lfno,g3e_username,g3e_content ) 
values ( 120000, 'Gas Identifier View Label','F[G3E_FID]:[MATERIAL]/[DIAMETER]');

insert into g3e_labelrule(g3e_lrrowno, g3e_lrno, g3e_rule, g3e_filter, g3e_filterordinal, g3e_lfno) 
values( 120001, 1, 'Identifier view',null, 1, 120000);

2. Create an identifier view

This label uses the G3E_FID, MATERIAL and DIAMETER attributes and we need to define a view to fetch it:

create or replace view V_GASLINE_FE as
  select gas_line.G3E_FID, MATERIAL, DIAMETER from gas_line
    inner join gas_record on gas_record.g3e_fid = gas_line.g3e_fid
    WHERE gas_line.G3E_FNO=12;

This view will later be defined as an Identifier view

3. Create the ‘attribute-only’ component

An attribute-only component is a special type of component not part of a feature. An example is G3E_JOB but it also used to define your Identifier View:

Insert into G3E_COMPONENT( G3E_CNO, G3E_USERNAME, G3E_NAME, G3E_TOOLTIP, G3E_TYPE, G3E_TABLE, G3E_DETAIL, G3E_DCNO, G3E_LRNO, G3E_LTT) 
values ( 1204, 'Gas Line Identifier', 'V_GASLINE_FE', 'Gas Line Identifier', 2048, 'V_GASLINE_FE', 0, 1, 1, 0);

Insert into G3E_ATTRIBUTE ( G3E_ANO, G3E_CNO,  G3E_FIELD, G3E_USERNAME,G3E_REQUIRED,  G3E_COPY, G3E_EXCLUDEFROMEDIT, G3E_DATATYPE, G3E_EXCLUDEFROMREPLACE,  G3E_BREAKCOPY, G3E_COPYATTRIBUTE, G3E_WRAPTEXT, G3E_UNIQUE, G3E_FUNCTIONALVALIDATION, G3E_ROLE) 
values( 120401, 1204, 'G3E_FID', 'Fid', 0, 0, 0, 4, 0, 0, 0, 1, 0, 1, 'EVERYONE');

Insert into G3E_ATTRIBUTE ( G3E_ANO, G3E_CNO,  G3E_FIELD, G3E_USERNAME,G3E_REQUIRED,  G3E_COPY, G3E_EXCLUDEFROMEDIT, G3E_DATATYPE, G3E_EXCLUDEFROMREPLACE,  G3E_BREAKCOPY, G3E_COPYATTRIBUTE, G3E_WRAPTEXT, G3E_UNIQUE, G3E_FUNCTIONALVALIDATION, G3E_ROLE) 
values( 120402, 1204, 'MATERIAL','Material', 0, 0, 0, 4, 0, 0, 0, 1, 0, 1, 'EVERYONE');

Insert into G3E_ATTRIBUTE ( G3E_ANO, G3E_CNO,  G3E_FIELD, G3E_USERNAME,G3E_REQUIRED,  G3E_COPY, G3E_EXCLUDEFROMEDIT, G3E_DATATYPE, G3E_EXCLUDEFROMREPLACE,  G3E_BREAKCOPY, G3E_COPYATTRIBUTE, G3E_WRAPTEXT, G3E_UNIQUE, G3E_FUNCTIONALVALIDATION, G3E_ROLE) 
values( 120403, 1204, 'DIAMETER', 'Diameter', 0, 0, 0, 4, 0, 0, 0, 1, 0, 1, 'EVERYONE');

4. Update the feature metadata to use the Identifier view

Our feature definition needs to be updated to use the identifier view :

update g3e_feature set g3e_identifierview = 'V_GASLINE_FE' where g3e_fno = 12;

5. Update all components to use the label

All components part of the feature need to have their G3E_LRNO point to the labelrule to be used

update g3e_component set g3e_lrno = 1 where g3e_cno in ( 1201, 1202, 1203 );

6. Optional : Change Component Views

For Netviewer tooltips to work using this approach, all attributes used in the label need to be present in our Identifier View, t.i. V_GASLINE_FE, so we’re done with that. Each time a user hovers over a component, Netviewer dynamically queries view V_GASLINE_FE in the database.
G/Designer on the other hand constructs tooltips using the label as specified in G3E_COMPONENT.G3E_LRNO and queries the component view for it. If it can’t find any attribute, it will display the label as a literal like ‘F10:[MATERIAL]/[DIAMETER]’. So for the G/Designer tooltip to display correctly we need to update the Component View for the LINE:

create or replace view V_GAS_LINE
as
	select GAS_LINE.G3E_ID
    	 ,	GAS_LINE.G3E_FNO
        ,	GAS_LINE.G3E_FID
        ,	GAS_LINE.G3E_CNO
        ,	GAS_LINE.G3E_CID
        ,	GAS_RECORD.DIAMETER
        ,	GAS_RECORD.MATERIAL       
        ,	GAS_LINE.G3E_GEOMETRY
         from GAS_LINE
         INNER JOIN GAS_RECORD on GAS_RECORD.G3E_FID = GAS_LINE.G3E_FID;

The result

After these changes, the result from the earlier query looks like this :

Metadata changed - aproach 1

Metadata changed – aproach 1

After publishing metadata, you now see the following picture when hovering over a feature in G/Designer:

G/Designer view - approach 1

G/Designer view – approach 1

When you hover of this feature in Netviewer, a tooltip is shown :

Netviewer - approach 1

Netviewer – approach 1

Use the same component for the primary graphic- and primary non graphic component

This approach is easier then the first one because it involves fewer steps. The only downside may be that both G3E_FEATURE. g3e_primarygeographiccno and G3E_FEATURE. g3e_primaryattributecno need to point to the same component.

The following steps are needed :

  1. Create a label
  2. Update the main graphic component to use the label
  3. Declare a single component to be the main graphic- en nongraphic record

1. Create a label

As in the previous example, we need to define the contents of our label :

insert into g3e_label( g3e_lfno, g3e_username,g3e_content ) 
 values  ( 120000,'V1','F[G3E_FID]:[MATERIAL]/[DIAMETER]');

insert into g3e_labelrule( g3e_lrrowno, g3e_lrno, g3e_rule, g3e_filter, g3e_filterordinal, g3e_lfno )
values ( 120001,1,'Identifier view',null, 1, 120000);

2. Update the main graphic component to use the label

update g3e_component set g3e_lrno = 1 where g3e_cno = 1201;

3. Declare a single component to be the main graphic- en nongraphic record

update g3e_feature set g3e_primarygeographiccno = 1201, g3e_primaryattributecno = 1201, g3e_identifierview='GAS_RECORD' where g3e_fno = 12;

The result again

After these changes, the result from the earlier query looks like this :

Metadata changed - aproach 2

Metadata changed – aproach 2


After publishing metadata, you’ll see the following picture in G/Designer :

G/Designer - approach 2

G/Designer – approach 2

The same feature looks like this in Netviewer :

Netviewer - approach 2

Netviewer – approach 2

Summary

  • Netviewer tooltips can be defined in 2 ways, the first one using an Identifierview and updating G3E_LRNO for each component, the second one by using one component as the maingraphic-/ nongraphic component
  • The second one is the easiest.
  • Both approached have consequences for the Tooltips shown in G/Designer

Scripts for this sample can be downloaded here.

Hope this helps, Stephan

April 18, 2014 by Metadata Netviewer Tooltips Uncategorized 0

Triggersets

Suppose we have a Cable feature with CABLE_TYPE, TOTAL_SIZE and FIBER_SIZE attributes. Whenever the CABLE_TYPE attribute is set, attributes TOTAL_SIZE and FIBER_SIZE need to be set as well. We need to implement the following list :

ID Cable type Total size Fiber Size
1 Corning 12F 12 4
2 BICC 16F 16 4
3 BICC 48F 48 4
4 PHILLIP 100F 100 6
5 Corning 48F 48 8
6 Corning 96F 96 10
7 Emtelle 12F 12 12
8 Emtelle 144F 144 12
9 Emtelle 240F 240 12

We can turn this list into picklists with the following statement:

create table CABLE_TYPE
(
   id int primary key,
   material_id varchar( 20) not null,
   total_size integer not null,
   fiber_size integer	not null
);

insert into cable_type( id, material_id, total_size, fiber_size) values ( 1, 'Corning 12F', 12,  4);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 2, 'BICC 16F', 16,  4);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 3, 'BICC 48F', 48,  4);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 4, 'PHILLIP 100F', 100, 6);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 5, 'Corning 48F', 48,  8);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 6, 'Corning 96F', 96, 10);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 7, 'Emtelle 12F', 12, 12);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 8, 'Emtelle 144F', 144, 12);
insert into cable_type( id, material_id, total_size, fiber_size) values ( 9, 'Emtelle 240F', 240, 12);
 
insert into G3E_PICKLIST ( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD,G3E_VALUEFIELD, G3E_VALIDATION, 	G3E_ORDERBYKEY,	G3E_PUBLISH)
Values( 111, 'Cable Type', 'CABLE_TYPE', 'MATERIAL_ID', 'MATERIAL_ID','G3ERestricted', 'ID',0);               
insert into G3E_PICKLIST ( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION, 	G3E_ORDERBYKEY,	G3E_PUBLISH)
Values( 112,'Total Size', 'CABLE_TYPE', 'TOTAL_SIZE', 'TOTAL_SIZE', 'G3ERestricted','ID',0);               
insert into G3E_PICKLIST ( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD,G3E_VALUEFIELD, G3E_VALIDATION, 	G3E_ORDERBYKEY,	G3E_PUBLISH)
Values( 113, 'Fiber Size', 'CABLE_TYPE', 'FIBER_SIZE', 'FIBER_SIZE', 'G3ERestricted', 'ID', 0);

We can now achieve the desired functionality using ‘Triggersets’. A triggerset is a rule describing a way to set values on a set of given attributes whenever one special attribute (the ‘Triggering’-attribute) changes.
Suppose our attributes have the following characteristics, taken from G3E_ATTRIBUTE :

g3e_ano g3e_field g3e_pno
110206 CABLE_TYPE NULL
110207 TOTAL_SIZE 112
110208 FIBER_SIZE 113

And the following, taken from G3E_TABATTRIBUTE :

g3e_tano g3e_ano g3e_pno g3e_triggerpopulate
110201 110206 111 1
110202 110207 NULL 0
110203 110208 NULL 0

There’s only one field with a G3E_TRIGGERPOPULATE value of 1, the ‘Triggering attribute’. This means that whenever this attribute gets a different value, the GTech triggering mechanism kicks off.
Given the above attributes and menu-definition, you can create a triggerset by populating tables G3E_TRIGGERSET & G3E_AUTOPOPULATE :

insert into g3e_triggerset( g3e_tsno, g3e_setno, g3e_triggertano, g3e_triggerfilter)
values ( 2, 2, 110201, '(MATERIAL_ID=#110206)');

The syntax in the G3E_TRIGGERFILTER column tells GTech how to retrieve a row from the picklist table to select a single row, to select the values to be used for updating. The values to be updated are specified using G3E_AUTOPOPULATE :

insert into g3e_autopopulate( g3e_apno, g3e_setno, g3e_tano )
values	( 3, 2,	110202 ); 
insert into g3e_autopopulate( g3e_apno, g3e_setno, g3e_tano )
values	( 4, 2,	110203 );

The first statement tells GTech to update the attribute on the dialog with g3e_tano 110202 whenever the value for CABLE_TYPE changes. The value will be the value for picklist 112 t.i. CABLE_TYPE.TOTAL_SIZE, as defined in G3E_ATTRIBUTE. Note that for the autopopulate mechanism to work, you need to have your picklist defined on G3E_ATTRIBUTE.G3E_PNO, not G3E_TABATTRIBUTE.G3E_PNO.
The second statement tells GTech to update g3e_tano 110203 with the value found in picklist 113, which is column CABLE_TYPE.FIBER_SIZE.
When this all is set in place, you’ll get the following initial picture :

Triggersets initial menu

Triggersets initial menu

If you select ‘Cable Type’ the following drop down shows up :

Triggersets selection menu

Triggersets selection menu

If the ‘Cable Type’ field has lost focus the triggerset fires and you’ll see the following screen

triggersets firing

triggersets firing

Once you select field ‘Cable type’ and then leave it, fields ‘Total size’ and ‘Fiber size’ are set.
As you can see, fields Total Size’ & ‘Fiber size’ are made read-only, if the user would be able to change them it would be possible to select invalid cable types.
We now have triggersets in place, but one improvement would be some kind of visual indication for a user what values will be copied into our ‘Total Size’ & ‘Fiber Size’ attributes. This can be done using the following statement:

update g3e_picklist set G3E_ADDITIONALFIELDS = 'TOTAL_SIZE,FIBER_SIZE' where g3e_pno = 111;

If you now select the ‘Cable type’-field, you get a picklist with all the values that will be set using the selected value:

Triggersets additional fields

Triggersets additional fields

Summary

  • Triggersets can be used to set multiple codelisted attributes using a single selection
  • All these attributes used in triggersets need to be defined in the same picklist table

Notes

  • The same functionallity could also be arranged with functional interfaces, but that would require developing custom code, Triggersets are part of the Product
  • Triggersets & autopopulate values need to use columns from the same picklist. If one attribute for instance uses a column from a different picklist, you will get the following error
    Triggerset error

    Triggerset error

  • Triggersets cannot be used accross different components. If you need to set an attribute in a different component, you need to do it using functional interfaces

Hope this helps, Stephan

November 14, 2013 by G/Technology Metadata Uncategorized 0

Fieldvalue filters

Fieldvalue filters are used to limit possible choices for a picklist, you can define them by setting column G3E_FIELDVALUEFILTER on table G3E_TABATTRIBUTE.

Suppose you have the following table REF_FSHELF :

ID MANUFAC MODEL_NAME SLOTS POSITIONS
1 KPE KP-CWDM/2 2 1
2 KPE KP-CWDM/2 2 2
3 KPE KP-CWDM/2 3 6
4 KPE KP-CWDM/2 3 7
5 KPE KP-CWDM/4 3 10
6 KPE KP-CWDM/4 3 11
7 KPE KP-CWDM/4 4 15
8 Transmode RMP-9003/01 3 1
9 Transmode RMP-9003/01 3 2
10 Transmode RMP-9003/01 5 20
11 Transmode RMP-9014/01 11 1
12 Transmode RMP-9014/01 12 2
13 Transmode RMP-9014/01 14 3
14 Transmode RMP-9014/01 14 4

If you look at this table each single row is a unique combination of values for MANUFAC, MODEL_NAME, SLOTS & POSITIONS. You can use FieldValue-filters to enable only those values in a menu making up a valid combination. Let’s use this table to create 4 picklists on the following attributes :

G3E_ANO G3E_FIELD G3E_USERNAME
601 MANUFACTURER Manufacturer
602 MODEL Model
603 SLOT_COUNT Slot Count
604 POS_COUNT Position

You can create the picklists with the following statements :

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD,G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 101, 'MANUFACTURER', 'REF_FSHELF', 'MANUFAC', 'MANUFAC','G3ERestricted', 0);

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 102, 'MODEL','REF_FSHELF','MODEL_NAME', 'MODEL_NAME', 'G3ERestricted',  0);

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE,G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 103,'SLOT_COUNT', 'REF_FSHELF', 'SLOTS', 'SLOTS', 'G3ERestricted',0);

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 104,'POS_COUNT', 'REF_FSHELF', 'POSITIONS', 'POSITIONS', 'G3ERestricted',  0);

Notice that these picklists are created with equal values for G3E_KEYFIELD & G3E_VALUEFIELD, so GTech will show distinct values.
After creating this picklists, we create our edit menu with the following statements (there’s no need to use picklists on table G3E_ATTRIBUTE.G3E_PNO unless the picklist is part of a label)

INSERT INTO G3E_TABATTRIBUTE( G3E_TANO, G3E_ANO, G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600001, 601, 11,101,1, 0);

INSERT INTO G3E_TABATTRIBUTE( G3E_TANO, G3E_ANO, G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600002, 602, 11,102,2, 0);

INSERT INTO G3E_TABATTRIBUTE(G3E_TANO,G3E_ANO,G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600003, 603, 11,103,3, 0);

INSERT INTO G3E_TABATTRIBUTE(G3E_TANO,G3E_ANO,G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600004, 604, 11,104,4, 0);

When you select a feature, GTech will show a Review-menu for the selected feature, so let’s copy our edit-menu to a review menu :

insert into g3e_tabattribute ( G3E_TANO, G3E_ANO, G3E_DTNO,G3E_ORDINAL,G3E_READONLY, G3E_DEFAULT)
select( G3E_TANO+40), g3e_ano,12,G3E_ORDINAL,1,G3E_DEFAULT from g3e_tabattribute;

Upon this point, these are the values a user sees when editing the feature in Feature Explorer :

As you can see, GTech shows the whole distinct set of values for the picklists. For attribute MANUFACTURER/ano=601 it shows all distinct values we have defined for picklists MANUFAC/pno=101, “KPE” & “Transmode”. For attribute MODEL/ano=601 it shows all distinct values for picklists MODEL_NAME/pno=102 “KP-CWDM/2”, “KP-CWDM/4”, “RMP-9003/01” & “RMP-9014/01” and so on. If you have a business rule saying selected values on this menu need to be a single row in table REF_FSHELF, we would have wanted only values “KP-CWDM/2” and “KP-CWDM/4″ for model because they are in the same row as MANUFACTUR=”KPE”. Since this is not the case, a user may very well be able to select a set of values not being a single row. That’s where fieldvaluefilters enter, you can use them to limit the set of choices a user can make to allow only values present in rows the user has selected before using a picklist. Fieldvalue filters have the following syntax :
#ANO=PL_FIELD
Which is saying limit the current picklist values to be only those where the current value for a given ano exists. So if we want to limit the possible choices for model to be only those who exist for the selected manufacturer, we can use the following sql-statement to update table G3E_TABATTRIBUTE:

UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '#601=MANUFAC' WHERE G3E_TANO = 600002;

After that, attributes SLOT_COUNT & POS_COUNT should also be dependent on previous choices :

UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '#601=MANUFAC&#602=MODEL_NAME' WHERE G3E_TANO = 600003;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '#601=MANUFAC&#602=MODEL_NAME&#603=SLOTS' WHERE G3E_TANO = 600004;

After publishing metadata, you get the following results :

As you can see, the selection is filtered to the area indicated in red :

ID MANUFAC MODEL_NAME SLOTS POSITIONS
1 KPE KP-CWDM/2 2 1
2 KPE KP-CWDM/2 2 2
3 KPE KP-CWDM/2 3 6
4 KPE KP-CWDM/2 3 7
5 KPE KP-CWDM/4 3 10
6 KPE KP-CWDM/4 3 11
7 KPE KP-CWDM/4 4 15
8 Transmode RMP-9003/01 3 1
9 Transmode RMP-9003/01 3 2
10 Transmode RMP-9003/01 5 20
11 Transmode RMP-9014/01 11 1
12 Transmode RMP-9014/01 12 2
13 Transmode RMP-9014/01 14 3
14 Transmode RMP-9014/01 14 4

There’s one drawback with this approach though, these fieldvalue filters only work if the operator selects fields in the following order : MANUFACTURER, MODEL, SLOTS & POSITIONS. If a user first selects SLOTS, MODEL & MANUFACTURER have no values and the fieldvalue filters don’t work. To allow a user to select the attributes in random order, you need to extend the fieldvalue filters to accept null values and also to check all attributes making up a unique row:

UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#602=MODEL_NAME|#602=NULL)&(#603=SLOTS|#603=NULL)&(#604=POSITIONS|#604=NULL)' 		
WHERE G3E_TANO = 600001;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#601=MANUFAC|#601=NULL)&(#603=SLOTS|#603=NULL)&(#604=POSITIONS|#604=NULL)' 		
WHERE G3E_TANO = 600002;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#601=MANUFAC|#601=NULL)&(#602=MODEL_NAME|#602=NULL)&(#604=POSITIONS|#604=NULL)'	
WHERE G3E_TANO = 600003;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#601=MANUFAC|#601=NULL)&(#602=MODEL_NAME|#602=NULL)&(#603=SLOTS|#603=NULL)' 		
WHERE G3E_TANO = 600004;

Using these fieldvaluefilters, a user can select any attribute in a random order and GTech will show a distinct set of values if there are any previous selections. Putting it all together, our G3E_TABATTRIBUTE joined with G3E_ATTRIBUTE table now looks like this :

G3E_ANO G3E_FIELD G3E_PNO G3E_ORDINAL G3E_FIELDVALUEFILTER
601 MANUFACTURER 101 1 (#602=MODEL_NAME|#602=NULL)&(#603=SLOTS|#603=NULL)&(#604=POSITIONS|#604=NULL)
602 MODEL 102 2 (#601=MANUFAC|#601=NULL)&(#603=SLOTS|#603=NULL)&(#604=POSITIONS|#604=NULL)
603 SLOT_COUNT 103 3 (#601=MANUFAC|#601=NULL)&(#602=MODEL_NAME|#602=NULL)&(#604=POSITIONS|#604=NULL)
604 POS_COUNT 104 4 (#601=MANUFAC|#601=NULL)&(#602=MODEL_NAME|#602=NULL)&(#603=SLOTS|#603=NULL)

This is the query for the table above :

select	g3e_tabattribute.g3e_ano
    ,	g3e_attribute.g3e_field
    ,	g3e_tabattribute.g3e_pno    
    ,	g3e_tabattribute.g3e_fieldvaluefilter
    from g3e_tabattribute
    	inner join g3e_attribute on g3e_attribute.g3e_ano = g3e_tabattribute.g3e_ano 
     order by g3e_dtno, g3e_ordinal;

Summarized, fieldvalue filters can be of great help in validating user input, but can also be quite a hassle from a developers point of view to set up. It’s very error prone and little type mistakes can be very consuming to fix. But if all in place, they work just nice.

Hope this helps, Stephan

October 14, 2013 by G/Technology Metadata 0

Identifierviews

Custom identifierview

If you select a feature, GTech shows the selected feature in Feature Explorer. The right area of feature explorer shows attributes of the selected feature, the left area shows the identification of the selected feature. This will show something like ‘Feature name – Fid’ (G3E_FEATURENAME – G3E_FID), in the picture below you can read ‘Point – 61’.

G/Technology identfierview

identfierview

If you’re not happy with this convention or need a quick way to distinguish features, you can create a custom label for this identification, a so called ‘Identifier View’ :

Custom identifierview

Custom identifierview

In the above picture, the red rectangle is showing a custom identifierview showing the material and the fid of the selected feature.

Lets assume your main non-graphic table looks like this :

CREATE TABLE B$POINT_RECORD
(
  G3E_ID NUMBER(10) NOT NULL,
  G3E_FNO NUMBER(5) NOT NULL,
  G3E_FID NUMBER(10) NOT NULL,
  G3E_CNO NUMBER(5) NOT NULL,
  G3E_CID NUMBER(10) NOT NULL,
  LTT_ID NUMBER DEFAULT 0 NOT NULL,
  LTT_STATUS VARCHAR2(6 BYTE),
  LTT_DATE DATE,
  LTT_TID NUMBER,
  A1 VARCHAR2(10 BYTE)
)

Then these are the steps needed for creating a custom identifierview using the FID and [A1]-attribute value:

  1. Create a Label
  2. Create a labelrule
  3. Update G3E_COMPONENT to reference the label
  4. Create the actual view
  5. Update G3E_FEATURE to reference the identifierview
  6. Create a ‘Attribute-Only’-component
  7. Define attributes for the Identifier View
  8. Update the component view to have all the attributes used in the identifierview

1. Create a Label

Insert into G3E_LABEL( G3E_LFNO, G3E_USERNAME, G3E_CONTENT) 
values( 1, 'Feature Explorer demo','[G3E_FID]/[A1]');

2. Create a Labelrule

Insert into G3E_LABELRULE( G3E_LRROWNO, G3E_LRNO, G3E_RULE, G3E_FILTER,	G3E_FILTERORDINAL, G3E_LFNO) 
values( 1, 1,	'R1', null, 99, 1);

3. Update G3E_COMPONENT to reference the label

update G3E_component set G3E_lrno=1 where G3E_cno in ( 5, 6);

Note:If you don’t update the components for the feature with the LRNO for the labelrule to be used, the label will not be used for Feature Explorer. It will only be used when hovering over the components who use the LRNO and will take the form of [G3E_COMPONENT.G3E_USERNAME][LABEL] where [LABEL] is the label referenced by G3E_LABELRULE.

4. Create the actual view

create or replace view V_POINT_FE as
select A.G3E_FID, B.A1 from point a, point_record b 
WHERE A.G3E_FNO=3 and a.G3E_FID=b.G3E_FID;

5. Update G3E_FEATURE to reference the identifierview

update g3e_feature set g3e_identifierview = 'V_POINT_FE' where g3e_fno = 3;

6. Create a ‘Attribute-Only’-component

Insert into G3E_COMPONENT( G3E_CNO, G3E_USERNAME, G3E_NAME, G3E_TOOLTIP, G3E_TYPE, G3E_TABLE, G3E_DETAIL, G3E_DCNO, G3E_LRNO, G3E_LTT) 
     values ( 7, 'Point Identifier', 'V_POINT_FE', 'Point Identifier', 2048, 'V_POINT_FE', 0, 1, 1, 0);

An Attribute-only-component is a special kind of component used to idenfiy components not part of features. Examples are job-attributes and identifierviews. You don’t need to create a row in G3E_FEATURECOMPONENT.

7. Define attributes for the Identifier View

Insert into G3E_ATTRIBUTE ( G3E_ANO, G3E_CNO,  G3E_FIELD, G3E_USERNAME, G3E_REQUIRED,  G3E_COPY, G3E_EXCLUDEFROMEDIT, G3E_DATATYPE, G3E_EXCLUDEFROMREPLACE,  G3E_BREAKCOPY, G3E_COPYATTRIBUTE, G3E_WRAPTEXT, G3E_UNIQUE, G3E_FUNCTIONALVALIDATION, G3E_ROLE) 
values( 700, 7, 'G3E_FID', 'fid', 0, 0, 0, 4, 0, 0, 0, 1, 0, 1, 'EVERYONE');

Insert into G3E_ATTRIBUTE( G3E_ANO, G3E_CNO,  G3E_FIELD, G3E_USERNAME, G3E_REQUIRED,    G3E_COPY, G3E_EXCLUDEFROMEDIT, G3E_DATATYPE, G3E_EXCLUDEFROMREPLACE,  G3E_BREAKCOPY, G3E_COPYATTRIBUTE, G3E_WRAPTEXT, G3E_UNIQUE, G3E_FUNCTIONALVALIDATION, G3E_ROLE) 
values( 701, 7, 'A1', 'A1', 0, 0, 0, 10, 0, 0, 0, 1, 0, 1, 'EVERYONE');

8. Update the component view to have all the attributes used in the identifierview

CREATE OR REPLACE VIEW V_POINT_FE
(
   G3E_ID,
   G3E_FNO,
   G3E_FID,
   G3E_CNO,
   G3E_CID,
   G3E_GEOMETRY,
   A1
)
AS
   SELECT A.G3E_ID,
          A.G3E_FNO,
          A.G3E_FID,
          A.G3E_CNO,
          A.G3E_CID,          
          A.G3E_GEOMETRY,
          B.A1       
     FROM POINT A,
     	  POINT_RECORD B
    WHERE     (B.G3E_FNO = 3)
          AND (A.G3E_FID = B.G3E_FID);

Notes:

  • The feature used in this example has feature # 5 and consists of two components, components # 5 & 6
  • If you search for G3E_IDENTIFIERVIEW in the help you’ll find some entries about it.
  • Thanks to Christoper de Bruyn for support with this entry.
August 28, 2013 by G/Technology Metadata Oracle PL/SQL 0

Don’t use import-css directives when bundling

Consider the following code:

public ActionResult Index( )
{
  StyleBundle b = new StyleBundle( "~/b1");
  b.Include( "~/Content/base.css");
  BundleTable.Bundles.Add( b);
  return( View( "~/Src/b.cshtml"));
}

This Controller method creates a bundle and includes a css-file located at ~/Content/base.css and then returns the view. The css file looks like this :

@import "b2.css";
body
{
  font-family: Verdana;
  font-size: small;
}

This file imports another css file b2.css. This includes a style to render the ‘important’-div red:

#important
{
  color: #FF0000;
}

The last line in our C#-code renders file ~/Src/b.cshtml :

@System.Web.Optimization.Styles.Render( "~/b1")
<div id="important">This is important</div>

This file calls the Render from System.Web.Optimization.Styles. The purpose of the Optimization library is to minimize css-files and scripts when running a release build, saving you the hassle of manually creating minimized files. If you call on this Action in Visual Studio, optimisation is disabled because you’re running in debug-mode resulting in the following view :

result in debug mode

result in debug mode

You see a nice red sentence, just like expected. If you now publish a release build of this code, you’ll see the following :

release-mode

release-mode

The red sentence has turned black. What happened ? If you inspect the network traffic using F12-network, you see the following :

release-build traffic

release-build traffic

The imported css file is not found on the network, caused by optimisation failing to fetch the correct paths for imported css files.

Ergo : Don’t use bundling with imported css-files.

If in full control of your css-files, then don’t import css files using the ‘import’-directive. If you’re not in control of your css files (using jQuery, Openlayers, etc..) then don’t optimize your css files using bundling.

Notes:

  1. Optimisation failing isn’t noticed until you deploy a release build. Even if you run a release build local before deploying, a cached css maybe read thus fooling you. If you would inspect network traffic on a release build locally before deploying, you’d see it fails
  2. Optimization is turned on by the following line in Web.Config : 
    <compilation debug="false" targetFramework="4.0"/>

    If you publish a release build, the attribute debug="false" is absent which results in ASP.Net using the default which is false:

    <compilation targetFramework="4.0"/>
  3. To quickly see if your optimized styles are rendering correct, you can also use the following statement in your C#-code : 
    BundleTable.EnableOptimizations = true;
June 19, 2013 by Bundling Minification MVC4 Visual Studio 2012 0

Using SSL during a certificate request

Creating a temporary website

If you need to request a certificate to use for a website, you need to generate a certificate request resulting in a certreq.txt you need for buying the actual certificate. Once the certreq.txt is generated, you can not use another certificate for your website because your request is still pending. The below figure shows the dialog you’ll see when trying to setup secure connections for a website for which a certificate request is pending:

request-pending

request-pending

There can be days, even weeks between the actual request for buying a certificate and the moment it is delivered, which voids SSL-communications for your website. To avoid this, you can create a new website, then use a development certificate and use that for your site until your actual certificate is delivered.

Creating a temporary website

Creating a temporary website

If your actual certificate is delivered, you need to delete your temporary website and you can use the actual certificate.

June 12, 2013 by Certificate MVC4 0

Intergraph issues database will be public available

Intergraph has an internal database for keeping track of all kinds of software issues, if you file a Trouble- or Change Request, it will end up in this database. This database will become public available in the 3rd quarter of 2013 according to Intergraph sources at Hexagon 2013. Also, if a problem gets fixed, the solution will be described so you know if applies to you. All this information will be public accessible and indexed by Google, so if you are facing a problem in G/Technology (or any other Intergraph product) google will find it and it will show up in the results of your search.

June 4, 2013 by G/Technology Intergraph 0

Netviewer extensions

It doesn’t take much to create a Netviewer extension:

  1. Derive a class from Microsoft.Practices.CompositeUI.ModuleInit
  2. Create a constructor with a [ServiceDependency()] WorkItem argument
  3. Modify your ProfileCatatalog.xml

The class would look like this:

namespace NetviewerLibrary
{
	#region -- Using directives --
	using System;
	using System.Reflection;
	using Microsoft.Practices.CompositeUI;
	using d = System.Diagnostics.Debug;
	#endregion

	public class Init: ModuleInit
	{
	    public Init([ ServiceDependency()] WorkItem rootWorkItem)
	    {
		   d.WriteLine( "Init");
	    }
	}
}

This code is using a pattern from the Microsoft Enterprise Library version 2.0 (no longer supported by Microsoft) for setting up an external library to be invoked by an application. The external library in this case is your extension, the application is Netviewer. All it takes for Netviewer to invoke the extension is a class derived from Microsoft.Practices.CompositeUI.ModuleInit and create a constructor with an Microsoft.Practices.CompositeUI.ServiceDependency argument. In line 10 a Microsoft.Practices.CompositeUI.ModuleInit-derived class is declared and at line 12 you can see the constructor. If your extension is compiled to an assembly called ‘NetviewerLibrary1’, you include your extension in the ProfileCatalog.xml file and add your command to IngrViewer.exe.config, Netviewer will invoke your extension.

This is the ProfileCatalog.xml :

<SolutionProfile xmlns="http://schemas.microsoft.com/pag/cab-profile/2.0">
  <Section Name="Apps">
    <Modules>
      <ModuleInfo AssemblyFile="GTechnology" />
      <ModuleInfo AssemblyFile="NetviewerCommand.dll" />
    </Modules>
  </Section>
</SolutionProfile>

This is not very useful other then illustrating what it takes for Netviewer to invoke your extension. Netviewer works with commands, and each extension can have one more or of it. The ProfileCatalog.xml is where you define your extensions, the IngrViewer.exe.config is where you define your commands. In order to do something useful, we need to add our command to IngrViewer.exe.config and extend our code with a command handler:

This is the line in IngrViewer.exe.config which adds the command:

<command name="NetviewerCommand1" autocreate="true" tooltip="NetviewerCommand1" label="CustomCommand1" />

This is the code:

namespace NetviewerLibrary
{
  #region -- Using directives --
  using System;
  using System.Reflection;
  using Microsoft.Practices.CompositeUI;
  using Microsoft.Practices.CompositeUI.Commands;
  using Intergraph.OneMobile.Infrastructure.Interface;
  using d = System.Diagnostics.Debug;
  #endregion

	public class Init: ModuleInit
	{
		public WorkItem WorkItem{   get; set;   }

		public Init([ ServiceDependency()] WorkItem rootWorkItem)
		{
			d.WriteLine( "Init");
			this.WorkItem = rootWorkItem;
		}

		public override void Load()
		{
			d.WriteLine( "Load");

			base.Load();

			ControlledWorkItem workItem = this.WorkItem.WorkItems.AddNew();
			workItem.Controller.Run();
		}
	}

	public partial class MyController :
		Intergraph.OneMobile.Infrastructure.Interface.WorkItemController
	{
		[ CommandHandler( "NetviewerCommand1")]
		public void MyCommand_Handler( object sender, EventArgs eventArgs)
		{
			d.WriteLine( "MyCommand_Handler");
		}
	}
}

If you now run Netviewer, there will be an extra button with a label “NetviewerCommand1” and if you click it, your commandhandler will be invoked.

Netviewer command

Netviewer command

Now let’s turn this extension in something useful, for instance placing a pinpoint. We want our command to ask the user to click in the Window and then place a pinpoint at the cursur. To do so, we need to extend our code with the following:

  1. A reference to the Netviewer MapviewService instance for setting up eventhandlers
  2. A Commandhandler for a left-mouse click
  3. Infrastructure code

The modified code looks like this:

namespace NetviewerLibrary
{
  #region -- Using directives --
  using System;
  using System.Reflection;
  using Microsoft.Practices.CompositeUI;
  using Microsoft.Practices.CompositeUI.Commands;
  using Microsoft.Practices.CompositeUI.EventBroker;
  using Intergraph.OneMobile.Infrastructure.Interface;
  using Intergraph.OneMobile.Map.Interface.Constants;
  using Intergraph.OneMobile.Map.Services;
  using Intergraph.Controls.oneViewer;
  using d = System.Diagnostics.Debug;
  #endregion

  public static class Global
  {
    public static int CallCount {   get; set;   }
  }

 public class Init: ModuleInit
 {
    public WorkItem WorkItem      {   get; set;   }

      public Init([ ServiceDependency()] WorkItem rootWorkItem)
      {
        d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

        this.WorkItem = rootWorkItem;
      }

      public override void Load()
      {
        d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

        base.Load();

        ControlledWorkItem workItem = this.WorkItem.WorkItems.AddNew();
        workItem.Controller.Run();
      }
  }

  public partial class MyController :
    Intergraph.OneMobile.Infrastructure.Interface.WorkItemController
  {
    #region -- Properties --
    public IMapViewService  MapviewService    {   get; set; }
    public string      OldMapLocateMode  {   get; set; }
    #endregion

    [ CommandHandler( "NetviewerCommand1")]
    public void MyCommand_Handler( object sender, EventArgs eventArgs)
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      this.MapviewService.SetStatusBar( "Click to place pinpoint");

      SwitchMapMode  ( );
      SetupEventSink  ( );
    }

    [ EventSubscription( EventTopicNames.MapViewLoaded)]
    public void MapViewLoaded_Handler( object sender, EventArgs eventArgs)
    {
        d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

        this.MapviewService = WorkItem.Services.Get();
    }

    private void SwitchMapMode( )
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      this.OldMapLocateMode = this.MapviewService.GetMapMode( );
      this.MapviewService.SetMapMode( Intergraph.Controls.oneViewer.MapModes.Custom);
    }

    private void SetupEventSink( )
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      this.MapviewService.RedlineLButtonDown    += new Intergraph.Controls.oneViewer.RedlineButtonEventHandler( MapviewService_RedlineLButtonDown );
    }

    void MapviewService_RedlineLButtonDown( object sender, Intergraph.Controls.oneViewer.RedlineButtonEventArgs e )
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      string  point    = string.Format( "{0}, {1}", e.XWorldPos, e.YWorldPos);
      string  pointType  = "UOR";
      string  fontName  = "G_MapPins";
      short  fontSize  = 24;
      string  fontColor  = "FF0000"; // --- Red
      string  charValue  = "F";    // --- Solid filled push pin

      this.MapviewService.PlaceLocationPinAtPoint(
                    point,
                    pointType,
                    fontName,
                    fontSize,
                    fontColor,
                    charValue,
                    "POI");
      StopCommand( );
    }

    private void StopCommand()
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      RestoreMapMode  ( );
      ReleaseEventSink( );

      this.MapviewService.SetStatusBar( string.Empty);
   }

    private void RestoreMapMode( )
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      this.MapviewService.SetMapMode( this.OldMapLocateMode);
    }

    private void ReleaseEventSink( )
    {
      d.WriteLine( string.Format( "{0}.{1} ({2}):{3}", GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name, Global.CallCount++, string.Empty));

      this.MapviewService.RedlineLButtonDown -= new Intergraph.Controls.oneViewer.RedlineButtonEventHandler( MapviewService_RedlineLButtonDown );
    }
  }
}

After running this command, a pinpoint is placed to indicate some point of interest :

pinpoint

pinpoint

You can download the complete source for this example over here.

March 27, 2013 by Netviewer Visual Studio 6