Extracting metadata from Oracle with SoProMach

We Generate Your Software.

Products

Services

Company

Full list of in-depth articles - Feedback and questions - Request demo -

Extracting metadata from Oracle with SoProMach

1	Mining and analyzing metadata: CSV vs. XML

2	Model generation: XML and Somusar EF (Entity Files)

3	Mining Oracle metadata with SoProMach

Appendix A

Further information

Appendix B

Summary of metadata extracted in CSV format

Rearchitecting or rebuilding existing applications with code generation may happen for several reasons, such as changing part of the original technology or adding a new architectural layer. Independently of the reason, in most cases a key requirement concerns the existing databases: if their performance has been satisfactory, it is required that they be used without changes as the foundation for the new or rearchitected applications. In some cases, a project of this kind also provides an opportunity for rearranging the architectural style originally applied, and to build a new model that extends the expected life span of the applications.

This article describes a model generation scheme utilizing SoProMach that allows model architects to effectively acquire knowledge and gain control of existing databases.

1 - Mining and analyzing metadata: CSV vs. XML

The experience gathered so far has shown that a two-stage approach to model generation provides some significant advantages in comparison with a one-stage approach:

the first stage may provide detailed (even redundant) information in a format suitable for automatic processing and analysis;

the second stage may abstract from the unrelevant or redundant details of the first stage, and produce a base model in a format more appropriate for the model architect's work.

In our case, comma-separated value (CSV) files have proven to be a better alternative than XML in the first stage for the following reasons:

non-developers are familiar with row-column spreadsheets: CSV makes it thus easier to involve business reviewers in the analysis stage;
CSV files can be easily processed with ad-hoc Unix-style filters, producing statistics on existing databases' internal details.

Moreover, the low-level engine of SoProMach (Tefigel: see overview and reference) provides agile means to process CSV files and perform the second model-generation stage.

2 - Model generation: XML and Somusar EF (Entity Files)

The analysis of the intermediate CSV files and the new or extended architecture provide the guidelines for the second stage of model generation. These guidelines have been realized in our case in the form of Tefigel scripts to be applied on the CSV files. Outcome of these scripts can be XML, EF (Somusar Entity Files - see examples), or both.

If the software generator chosen for the actual development project is SoProMach, as in our case, then the obvious choice is EF, that provides robust type-checking and flexible entity aggregation mechanisms. But XML is an excellent metadata exchange medium, so in our case it was decided to generate an EF model, and export XML metadata from EF at software-generation time.

3 - Mining Oracle metadata with SoProMach

Chapter "Extracting Metadata" from Kathleen Dollard's "Code Generation in Microsoft .NET" provides an excellent introduction to metadata extraction. Particularly relevant to the contents of this article is paragraph "Extracting Metadata from Databases Such As SQL Server".

The specific solution described in this article allows to extract metadata from Oracle9i Sample Schemas HR, OE, QS and SCOTT (see "Oracle's Sample Schemas: Saying Goodbye to Scott" and "Oracle9i Sample Schemas - Part Number A96539-01"). By means of SoProMach and SQL*Plus this solution extracts metadata about the following classes of constructs:

tables and constraints;

views;

stored procedures and functions;

user-defined types.

The overall process of this specific solution is shown in the figure below.

Figure 1 - Extracting metadata from Oracle9i

The numbers in the figure correspond to the steps described below. Steps 1 to 7 (continuous lines) are project-independent; steps 8-10 (dashed lines) are project-specific.

The Model Architect runs the desired metadata extraction job on the desired schema(s). Note that the Model Architect could be an automated tool;
SoProMach generates on the fly a set of scripts, similar to the queries that an Oracle DBA would type into a SQL*Plus console;
SoProMach submits each script to SQL*Plus;
SQL*Plus interacts with the RDBMS and retrieves the metadata;
The raw metadata is written by SQL*Plus into a set of text files;
SoProMach parses the raw metadata, understanding the required amount of SQL code output by SQL*Plus; note that steps 2 to 6 are automatically repeated to fetch metadata about tables, views, and stored procedures (and possibly more);
The interesting part of the metadata is rearranged and stored into CSV files;
CSV files can be examined and analyzed by the Model Architect as plain spreadsheets, or processed by record-oriented tools such as "awk";
Depending on the project target, the Model Architect runs SoProMach to generate new software, or to produce a new model (XML, or .ef, for instance) for later processing;
According to the target of step #9, SoProMach transforms the uniform metadata into more complete metadata, or directly into software.

Appendix A - Further information

This solution has been tested with Oracle9i Release 2 running on Linux, and SQL*Plus running on both Windows and Linux. It has been implemented with SoProMach/Community Edition.

SoProMach/Community Edition and a set of proofs-of-concept are available for free download. All documentation on SoProMach is available on line. For more information please contact us.

Appendix B - Summary of metadata extracted in CSV format

The uniform CSV metadata files produced in step #7 are listed below.

Written on 6 December 2004 - Updated on 6 September 2005
Full list of in-depth articles - Feedback and questions - Request demo -