HOWTO: Use Farrago SQL to Access Metadata Managed by MDR

The Farrago DBMS platform makes all of this possible via its SQL/MED extensibility feature. This document describes how to make use of the MDR plugin.

Getting Started with Farrago

After you have installed Farrago, make sure you're able to execute simple queries via iSQL-Viewer.

Farrago Talks MDR

create server mof_repository
foreign data wrapper sys_mdr
options(
    "org.netbeans.mdr.persistence.Dir" 'unitsql/ddl/mdr',
    extent_name 'MOF',
    schema_name 'MODEL'
);

• mof_repository is the name we will give the repository in Farrago. It will appear as a top-level catalog name.
• sys_mdr is the name of the foreign data wrapper used to access MDR. Farrago allows you to define and plug in your own, but this isn't necessary here because the system already provides one for MDR.
• "org.netbeans.mdr.persistence.Dir" tells MDR the directory in which to find the btree storage. This option name has to be quoted (with double-quotes as an identifier) in order for its case to be preserved correctly. Any properties understood by MDR can be passed in this way (allowing you to choose a storage representation other than btree, for example). The path is relative to the working directory; depending on where you ran iSQL-Viewer from, you may have to modify it accordingly. Using an absolute path is usually the best approach. Note that option values are always single-quoted (as string literals, not identifiers like option names).
• extent_name tells MDR what extent name to look for in the repository
• schema_name is bit of a hack. Normally, in a repository with a nested package structure, the root_package_name option would be specified instead, and all child packages of the root package would appear as schemas. However, the MOF repository has no nested packages at all, so in such cases the schema_name option should be specified to make the namespace conform to the standard catalog.schema.table convention. Choosing an upper-case name will allow you to avoid quoting it later.

select * from mof_repository.model."Class";

Now we know the column names, so we can issue a more meaningful query, like finding the names of all abstract classes. Since the isAbstract column has type BOOLEAN, we can use it directly in the WHERE clause.

select "name" from mof_repository.model."Class" where "isAbstract";

Result Set Details

select "name","mofId" from mof_repository.model."Package";

Notice that the mofId for the row with name=Model is the same as the container ID for all of the classes in the first result set. So this means all MOF classes are defined in the Model package. You can use a join to navigate the association; try the query below:

select 
    n."name" as container_name,
    e."name" as element_name
from
    mof_repository.model."Package" n
inner join
    mof_repository.model."ModelElement" e
on n."mofId" = e."container";

Some other result set details:

• Besides the mofId column, there is an additional meta-column mofClassName associated with every table. When you query a leaf-level class in an object hierarchy, this value will always be the same (the name of the class you queried). However, when you query a non-leaf class (e.g. select "name","mofClassName" from mof_repository.model."Namespace"), the mofClassName column can be used to determine the actual leaf type of each object returned.
• Datatypes of result columns are inferred automatically using repository metadata. All columns show up as nullable (even when the underlying attribute is mandatory).
• MDR does not supply any precision information for string datatypes, so Farrago (arbitrarily) chooses VARCHAR(128) for the representation. This will probably change in the future; for now, a mechanism is available (described in the next section) for overriding the default with a different precision.

Local Metadata

1. Use the SQL/MED CREATE FOREIGN TABLE command to import metadata for individual tables into the Farrago catalog, specifying column names and types explicitly.
2. Use the SQL/MED CREATE FOREIGN TABLE command to import metadata for individual tables into the Farrago catalog, allowing the system to infer the column names and types.
3. Use the SQL/MED IMPORT FOREIGN SCHEMA command to import metadata for a collection of tables all at once. This is not yet implemented by Farrago, so it won't be discussed here.
4. Use the CREATE VIEW command to define views directly against the repository.
5. Use the CREATE VIEW command to define views against metadata imported via one of the first three methods.

create schema mof_schema;

create foreign table mof_schema.exceptions(
    name varchar(20),
    annotation varchar(1),
    container char(54),
    "SCOPE" varchar(20),
    visibility varchar(20),
    exception_id char(54),
    unused_name varchar(20))
server mof_repository
options (class_name 'Exception');

• We first create a new schema mof_schema under the default LOCALDB catalog. This schema will contain our imported metadata. This imported metadata will remain available even if the repository becomes unavailable.
• In this case, the CREATE FOREIGN TABLE command overrides the default names and datatypes known to the repository. The names are defined as case insensitive, so no identifier quotes will be required when querying this table, except for the scope column whose name happens to be a SQL reserved word. And the datatypes are more reasonable than the defaults (e.g. we know that the MOFID's for this repository are fixed-length 54-character strings).
• The table name exceptions is never seen by the repository at all. Instead, we explicitly tell the repository the desired class_name via the options clause at the end.
• If the column names and types had been omitted, they would have been derived automatically from the repository metadata. This may be undesirable when you need control over case-sensitivity or datatypes.

select name,visibility
from mof_schema.exceptions;

create view mof_schema.exception_visibility as
select name,visibility
from mof_schema.exceptions;

create view mof_schema.exception_visibility as
select "name" as name,"visibility" as visibility
from mof_repository.model."Exception";

Try This At Home

• Repository Storage: If you're not using the default btree storage, you'll need to specify different options to the create server command. For example, if you are using JDBC storage, you might specify something like:

  
  options(
      "org.netbeans.mdr.storagemodel.StorageFactoryClassName" = 'org.netbeans.mdr.persistence.jdbcimpl.JdbcStorageFactory',
      "MDRStorageProperty.org.netbeans.mdr.persistence.jdbcimpl.driverClassName" = 'org.hsqldb.jdbcDriver',
      "MDRStorageProperty.org.netbeans.mdr.persistence.jdbcimpl.url" = 'jdbc:hsqldb:/home/hsqldb/stored_repos'
  )
  

• Package Structure: If your repository model has no nested packages, you can use the schema_name option as in the MOF example. Otherwise, you'll need to specify the root_package_name option instead. All packages under this root package will appear as schemas under your repository catalog. If your package nesting structure has a depth greater than 2, you'll probably have to choose multiple roots and issue multiple create server statements accordingly.

• Concurrent Access: MDR's default btree storage does not allow more than one process at a time to open the btree files. This means Farrago and your application have to take turns accessing the data, which is probably impractical. MDR's JDBC storage may provide a better solution, although it needs improvements in performance/concurrency control. So for now, usage of Farrago for MDR querying is probably limited to proof-of-concept.

TODO