Raw SQL is used for ad hoc query specifications, specifications that cannot be ``canned'' ahead of time using stored procedures. Raw SQL is required for condition evaluation since most condition specifications are more complex than retrieval by Oid, the basic retrieval parameter for stored procedures. Raw SQL is also required for ad hoc path or branch evaluation, paths or branches not specified in a schema and implemented by a derived composition attribute stored procedure. Lastly, raw SQL is required for retrofitted OPM databases operating without stored procedures.
Stored procedures provide an intermediate layer of abstraction between the OPM Query Translator and the underlying DBMS. They have the advantage of not needing compilation by the DBMS for execution. Use of stored procedures also reduces network traffic: instead of shipping a long complex SQL string across the net to the server, a stored procedure call is usually pretty terse. Stored procedure queries are useful for certain fixed queries, such as retrieval by Oid.
For updates, stored procedures also provide a valuable service in handling integrity constraints and version management.
Stored procedures currently follow certain name conventions,
documented in the
README.opmsp file. Insert procedures names,
begin with OPMI_. For example, the procedure for
inserting into class PERSON is called OPMI_PERSON. Delete
procedures begin with OPMD_. Differential updates begin
with OPMdU_. Procedures for retrieval
begin with OPMvG_. Set-valued attributes procedure names
are formed by concatenating the set valued attribute name to
the class name and stored procedure prefix. For example,
the insert procedure for the set valued attribute project in
class PERSON is OPMI_PERSON_project.
Stored procedure arguments usually begin with the input arguments @_oid, as well as @_version if versioning is involved. The other arguments usually involve OPM level attribute names. In the procedure OPMvG_ (``verbose get''), arguments can also involve REP attributes concatenated to the original attribute name. ( REP's serve a similar function as class ID's in the sense that they represent a class instance. They differ from ID's in that they may be versioned, they allow nulls, and they may have duplicate instances. They provide more human readable information for real world applications over a raw Oid number of an abstract attribute value.)