Creating an OPM Database

We will describe the process of creating an OPM database using Sybase as an underlying DBMS. OPM databases can also be created using Oracle or some other DBMS, or an existing relational database can be retrofitted with an OPM view using the OPM retrofitting tools.

First an OPM schema is specified using an Ascii text editor or the OPM Schema Editor ( oped). For example, suppose the Ascii file shotgun.OPM contains an OPM schema. Then the OPM Schema Translator opm2dbms (see [4]) can be used to generate files containing the Sybase database definitions and procedures from this .OPM file. These files can be loaded into the database with isql.

        % opm2dbms shotgun.OPM
        ...
        % isql -Uusername -Ppassword < shotgun_relations.SYB11
        % isql -Uusername -Ppassword < shotgun_datarules.SYB11
        ...

The customizable PERL script opm2syb can also be used to automate this process. opm2syb is a driver script that runs opm2dbms and isql and is usually sufficient for most database creation cases.

        % opm2syb -u username -p password -r shotgun.OPM

The user can choose to load the file containing the referential integrity triggers later with isql, after bulk loading data from other sources. (The '-r' suppresses loading referential integrity files.)

Next, stored procedures can be created and loaded into the database. The opmsp utility takes the .OPM file and .MAP file (the latter generated by opm2dbms) and generates .opmsp files for Sybase.

    % opmsp shotgun.OPM shotgun.MAP
    ...
    % isql -Uusername -Ppassword < ASSEMBLE.opmsp
    % isql -Uusername -Ppassword < CONSTRAINT.opmsp
    % isql -Uusername -Ppassword < CONNECTION_TABLE.opmsp
    % isql -Uusername -Ppassword < CONTIG_MAP.opmsp
    ...

Besides generating the .opmsp stored procedure files, opmsp generates a .spmap file. The .spmap file contains stored procedure mapping information for the metadata. A similar procedure may be used with Oracle's sqlplus utility. As with opm2syb, a driver script, osp2syb, can be used for running opmsp and isql to automate the above process.

    % osp2syb -u username -p password shotgun.OPM 
    ...