Farrago DMV

This document provides an overview of Farrago's Dependency/Dataflow Metadata Visualization (DMV) support. The DMV framework makes it possible to build advanced visualization interfaces for any repository metadata stored or accessible by Farrago. This page walks through an example using Graphviz to render dataflow views into a set of related table/view schemas.

The reader is encouraged to browse through all the diagrams first to get a sense for what DMV is good for, and then make a second pass to study the mechanisms used to create them.

DMV is based on the LURQL metadata query language, so that link is required reading for understanding the details; it also covers the FEM metadata model used in the examples here.

Processing Overview

Here's a diagram for typical DMV processing:

Example Metadata

The example repository used as visualization input is defined by the DDL script below (part of farrago/examples/dmv/test.sql), with the domain being, um, a travel agency. The table and view definitions are only intended to simulate meaningful dependencies at table-level and schema-level; the actual details of the definitions are bogus.


create schema accounts;

create table accounts.customers(customer_id int not null primary key);

create table accounts.addresses(address_id int not null primary key);

create view accounts.customer_addresses 
as select * from accounts.customers, accounts.addresses;

create schema car_rentals;

create table car_rentals.cars(car_id int not null primary key);

create table car_rentals.contracts(contract_id int not null primary key);

create view car_rentals.customer_rentals as
select * from accounts.customers, car_rentals.contracts, car_rentals.cars;

create schema lodging;

create table lodging.hotels(hotel_id int not null primary key);

create table lodging.cabins(cabin_id int not null primary key);

create view lodging.locations as
select * from lodging.hotels
union all
select * from lodging.cabins;

create view lodging.registrations as
select * from accounts.customers, lodging.locations;

create schema billing;

create view billing.events as
select * from car_rentals.customer_rentals, lodging.registrations;

create view billing.all_addresses as
select * from accounts.addresses, lodging.locations;

Executing a Visualization

Once the example input has been defined, a few more setup steps are required in order to start executing visualizations:


create schema dmv_test;

create procedure dmv_test.dmv_render_graphviz(
    foreign_server_name varchar(128),
    lurql_filename varchar(1024),
    transformation_filename varchar(1024),
    dot_filename varchar(1024))
language java
no sql
external name 'class net.sf.farrago.test.DmvTestUdr.renderGraphviz';

The DDL above registers the Graphviz transformation procedure, making it callable from SQL. The procedure takes four input parameters:

foreign_server_name: location of metadata to be queried; in this doc, we'll always pass null here to indicate that we want to query the metadata in Farrago's own catalog (in particular, the schema definitions we created above)
lurql_filename: path to a file containing a LURQL query which selects the objects to be visualized
transformation_filename: path to a file containing an XML specification of how to transform the LURQL query results into a dependency graph; the details of this file will be covered below (see JmiDependencyMappedTransform for the underlying implementation)
dot_filename: path to the .dot file to be created as a result of executing the procedure

Here's our first invocation:


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/schemaDependencies.lurql',
    '${FARRAGO_HOME}/examples/dmv/schemaDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/schemaDependencies.dot');

Before looking at the input files, view the resulting .dot file using Graphviz to see what came out. On Linux, use the following command after first installing GraphViz:


cd farrago/examples/dmv/results
dot -Tpng schemaDependencies.dot > schemaDependencies.png

Then open the .png file in a web browser. (Or if you have ImageMagick installed, use a command like

dot -Tpng schemaDependencies.dot |
display

to view the .dot file directly.) You should see this:

What you have just created is a dataflow diagram which rolls up the individual view/table dependencies to the schema level. How does that work? First, here's the query from schemaDependencies.lurql:


select *
from class LocalSchema
where name in ('ACCOUNTS', 'CAR_RENTALS', 'LODGING', 'BILLING') then (
    follow composite forward destination class ColumnSet then (
        follow origin end client
    )
);

This query starts with the schemas of interest, collects the tables and views contained by those schemas, and also retrieves dependencies of which those objects are clients. However, it does not explicitly retrieve the suppliers of those dependencies. The reason is that we only want to visualize the dependencies between the schemas of interest (instead of dragging in other schemas such as system schemas).

The result of that query is then transformed into a dependency graph using the rules in schemaDependencies.xml:


<DmvTransformationRuleSet>
  <SetAllByAggregation 
    requestedKind="composite" 
    mapping="CONTRACTION"/>
  <SetAllByAggregation 
    requestedKind="none" 
    mapping="COPY"/>
  <SetByAssoc
    assoc="DependencyClient"
    mapping="CONTRACTION"/>
</DmvTransformationRuleSet>

Here's the interpretation:

The first rule says that all composite associations in the UML model are to be transformed via graph CONTRACTION. For example, the two views EVENTS and ALL_ADDRESSES are both composite children of the BILLING schema, so the corresponding vertices are all rolled together into one vertex, with the outermost composite (schema BILLING) serving as the representative for all of them.
The second rule says that all non-composite associations in the UML model are to be preserved (COPY) as edges in the dependency graph. This includes the client and supplier associations of CwmDependency, which is what gives the resulting diagram the desired connectivity (after rolling them up to the schema level).
Finally, the last rule says that the specific association named DependencyClient is to be treated as a CONTRACTION rather than a COPY. This is actually superfluous because in FEM, each Dependency object already has a composite association with the client (ElementOwnership) in addition to the non-composite ClientDependency. But it illustrates how to override the settings for a particular association, and it is necessary for subsequent examples to work correctly when the mapping for composite associations is changed.

Note that the dependency graph data structure is set up to discard self-loops, otherwise every schema vertex would have one to represent its internal dependencies.

Now let's drill down to the object level, but keeping the schemas as grouping constructs in the diagram for context:


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/schemaDependencies.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/objectDependenciesGrouped.dot');

To get this diagram, the same query was used; only the transformation rules changed. objectDependencies.xml is almost the same as schemaDependencies.xml, except that the mapping for composite associations has been changed to HIERARCHY instead of CONTRACTION. This means that rather than rolling up the dependencies, the composite container is converted into a grouping construct in the dependency graph.

Graphviz didn't do such a great job with the layout in the previous example due to the schema grouping. What if we don't care about the schemas at all, and only want to see the individual objects?


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/objectDependencies.dot');

For the visualization above, we left the transformation rules the same as in the previous example, but changed the query:


select o, d
from class LocalSchema
where name in ('ACCOUNTS', 'CAR_RENTALS', 'LODGING', 'BILLING') then (
    follow composite forward destination class ColumnSet as o then (
        follow origin end client as d
    )
);

The only difference is that this one omits the schemas from the results (so the HIERARCHY mapping is ignored as irrelevant).

It's also possible to treat the same model association differently depending on the actual object types to which it applies. The next visualization shows the tables grouped by schema, with the views "floating" (as if they had no schema):


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/schemaDependencies.lurql',
    '${FARRAGO_HOME}/examples/dmv/viewsFloating.xml',
    '${FARRAGO_HOME}/examples/dmv/results/viewsFloating.dot');

This was accomplished in viewsFloating.xml by adding an additional rule to objectDependencies.xml:


<DmvTransformationRuleSet>
  <SetAllByAggregation 
    requestedKind="composite" 
    mapping="HIERARCHY"/>
  <SetAllByAggregation 
    requestedKind="none" 
    mapping="COPY"/>
  <SetByAssoc
    assoc="DependencyClient"
    mapping="CONTRACTION"/>
  <SetByAssoc
    assoc="ElementOwnership"
    targetClass="LocalView"
    mapping="REMOVAL"/>
</DmvTransformationRuleSet>

The last rule says that when the child of an ElementOwnership association is a LocalView, delete the corresponding edge entirely (REMOVAL). One side-effect is that the parent schema BILLING is left in the diagram as a dangling vertex (upper right). Either DMV could be enhanced to express the corresponding vertex removal, or else this situation has to be handled via post-processing.

Here's a visualization for a single schema by itself:


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/carRentalsOnly.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/carRentalsOnly.dot');

Now, suppose we want to see the immediate periphery of this schema (objects in other schemas which either reference or are referenced by objects in schema CAR_RENTALS):


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/carRentalsPlusPeriphery.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/carRentalsPlusPeriphery.dot');

Here's the LURQL which does this:


select *
from class LocalSchema
where name = 'CAR_RENTALS' then (
    follow composite forward destination class ColumnSet then (
        follow origin end client then (
            follow destination end supplier destination class ColumnSet
        )
        union
        follow origin end supplier then (
            follow destination end client destination class ColumnSet
        )
    ) gather with parent then (
        follow composite backward destination class LocalSchema
    )
);

It's also possible to flip the direction of the dependency edges:


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/carRentalsOnly.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependenciesReversed.xml',
    '${FARRAGO_HOME}/examples/dmv/results/carRentalsReversed.dot');

The flip is accomplished by adding the following transformation rule:


  <SetByAssoc
    assoc="DependencySupplier"
    mapping="REVERSAL"/>

LURQL is also good for recursively traversing object-level dependencies. Here's a diagram of all objects downstream of the CUSTOMERS table:


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/customersDownstream.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/customersDownstream.dot');

And the LURQL:


select *
from class ColumnSet where name='CUSTOMERS' then (
    recursively (
        follow origin end supplier then (
            follow destination end client destination class ColumnSet
        )
    ) then (
        follow composite backward destination class LocalSchema
    )
);

All objects upstream of the EVENTS view:


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/eventsUpstream.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/eventsUpstream.dot');

LURQL:


select *
from class ColumnSet where name='EVENTS' then (
    recursively (
        follow origin end client then (
            follow destination end supplier destination class ColumnSet
        )
    )
) gather with parent then (
    follow composite backward destination class LocalSchema
);

Finally, how about both directions at the same time?


call dmv_test.dmv_render_graphviz(
    null, 
    '${FARRAGO_HOME}/examples/dmv/registrationsUpAndDownStream.lurql',
    '${FARRAGO_HOME}/examples/dmv/objectDependencies.xml',
    '${FARRAGO_HOME}/examples/dmv/results/registrationsUpAndDownStream.dot');

LURQL:


select *
from class ColumnSet where name='REGISTRATIONS' then (
    recursively (
        follow origin end supplier then (
            follow destination end client destination class ColumnSet
        )
    )
    union
    recursively (
        follow origin end client then (
            follow destination end supplier destination class ColumnSet
        )
    )
) gather with parent then (
    follow composite backward destination class LocalSchema
);