DTGenFunctionality
Given a basic entity-relational model and required model meta-data, DTGen generates Oracle SQL, PL/SQL, and APEX source code. The input for DTGen is data saved to a DTGen schema in an Oracle database. The data can be entered from a user built SQL*Loader Control file (an example is included) and/or the DTGen application in APEX. The output from DTGen is line oriented text data stored in the same DTGen schema. The text data can be queried from the DTGen schema and/or downloaded as a file via generated URLs in APEX. DTGen runs on Oracle's Free Database. See Compatibility for more details on software versions.
The transition from Entity/Relationship model to SQL schema requires some not-so-obvious functions that are typically provided by application developers. Augmented schema functionality generates the needed SQL and PL/SQL source code to enable functions like update-able natural keys, case folding, and full path hierarchical data. The following sections describe this augmented functionality in more detail.
Much has been said about WWW about the use of surrogate keys. However, it is a useful tool for allowing update-able natural keys. It also works nicely for the ID of an instantiated object in Java. All DML and queries on the schema generated with DTGen can be done using natural keys only. The surrogate keys are completely automated and don't need to be used by an application.
This has always been a prime function issue with basic SQL schema. If you need to update a natural key that is used as primary and foreign keys, there are some real hoops to jump through. The use of surrogate keys allow the primary and foreign key values to remain unchanged as the natural keys change. (NOTE: Changing natural keys can reek havoc on an application, use with care!) Additionally, DTGen requires natural keys to be defined on each table and generates the needed PL/SQL and SQL to enforce a unique constraint on those keys.
Once foreign keys and natural keys have been defined, DTGen automatically generates the SQL to create those indexes on those columns.
While this appears to be an ordinary function of the basic check constraint in the Oracle database, there is an architectural switch (glob.db_constraints) in the PL/SQL generated by DTGen that allows this enforcement to occur on the mid-tier, instead of the database. This is part of the configurable mutl-tier deployment of the generated code.
As with anything, enforced discrete domains is a mixed blessing. These discrete domains allow simple domains to be defined and enforced without the need to additional tables or the need for a common "references" table that has all the problems of accurate integrity enforcement. The down side of these discrete domains is that they are almost impossible to change on a deployed system because they are essentially "hard-coded" into the PL/SQL generated by DTGen. These discrete domains are invaluable for things like Yes/No, but may not be appropriate for other domains that may change.
Getting the correct case (UPPER/lower/Init Cap) on string data is crucial to providing quality reports. DTGen generates the PL/SQL code needed to ensure the case of string data is correct. Additionally, a switch (fold_case) is included in the generated code that will correct that case error without generating an error. The corrected data is inserted/updated in the database, even if the call is an SQL insert or update statement. If the call was a DML package call, the corrected data is returned to the calling program for further use by the calling program without re-querying the database.
A variety of APIs is generated by DTGen to handle the following functions for each table:
- Retrieve the next table sequence number
- Retrieve the current table sequence number
- Retrieve the surrogate key for a given natural key
- Retrieve the natural key for a given surrogate key
- Retrieve the hierarchical path of surrogate keys for a given surrogate key
- Retrieve the hierarchical path of surrogate keys for a given surrogate key
- Retrieve a surrogate key for a given full path of surrogate keys
- Retrieve a surrogate key for a given full path of natural keys
- Convert a table record into a col_type object for a given surrogate key
- Clear the contents of a given transactional view record
- Clear the contents of a given table record
- Insert data from a given set of data
- Insert data from a given transactional view record
- Insert data from a given table record
- Update data from a given set of data
- Update data from a given transactional view record
- Update data from a given table record
- Delete data for a given surrogate key
When a table has a foreign key to it's own primary key, the table is self-referencing. An example of a self-referencing table is the employee records that have a foreign key to their supervisor's employee record. When data is organized in this way, an implied "path" exists from the company president down though every employee. DTGen generates the PL/SQL code to return these hierarchical paths for both surrogate keys and natural keys.
"In computer science, cross-cutting concerns are aspects of a program which affect other concerns."
http://en.wikipedia.org/wiki/Cross-cutting_concern
There are situations where a function requires exclusive execution to avoid entanglement with another execution of itself. The PL/SQL code generated by DTGen includes a function to ensure this exclusive execution. Additionally, this global lock is enforced across both database-tier and mid-tier.
Any generator "worth it's salt" has a logging function. The PL/SQL code generated by DTGen includes the creation of a simplified logging table (no constraints, minimal structure) that is used to capture set of strings from an application. The location of the logging call is automatically captured during the logging call. Additionally, a special error logging call can be used to capture the location of the error from the error call stack in the database.
One of the most difficult concerns for a database designer is whether to save data history, how much to save, and how to save it. For instance, an employee's position will change with a promotion. Is it important to save the employee's previous position? What about previous salary data? Should there be a dedicated history table for each employees' position history and another for salary history? DTGen allows the designer to pick a table type that will automatically capture all DML history on a table. This capability can then be expanded into the separation of rapidly changing attributes into their own tables with saved history. Along with the history capture, DTGen also generated the PL/SQL code to capture who made the changes and when the changed were made. Since each of these changes are recorded in the native table record format, querying these audit trails is easier and more intuitive.
As data changes in the database, it is useful to query the data as it was at some previous point in time. While Oracle currently provides this functionality by using saved ROLLBACK data, DTGen generates PL/SQL and SQL that creates tables to store this data in the database. Views on this data, coupled with supporting PL/SQL packages, enable database-wide query of data at any previous point in time. The table storage of this data also allows data correction in the rare event that historical data must be corrected.
Oracle currently provides an AS OF functionality by using saved ROLLBACK data in the database. However, this data is not transportable to another database in it's native format, except only under very strict conditions. Also, the queries needed to capture this data in a native format for transport are very complicated. DTGen generates the PL/SQL and SQL code needed to create tables with historical data that will allow this AS OF functionality to be replicated on another database.
With the recording of historical data in tables in the database, a POP or UNDO function becomes available to undo insert, update, and/or delete operations on a table. DTGen generates the PL/SQL and SQL necessary to create the tables and PL/SQL packages to accomplish this POP function and record an audit trail of who did what to the table. Since this data is recorded in the native table format, reconstruction of DML and POP events on a given record is easier and more intuitive.
One of the most tedious programming tasks is to create basic maintenance forms. DTGen generates an APEX export file that can be imported into an APEX application to create these basic maintenance forms. Additional capabilities are listed below.
The grid edit is an elusive form in APEX. There is no native generation for it. It is a combination of a table report and custom processing of the arrays returned from the browser. DTGen generates an APEX export file that includes everything necessary for these grid edit forms on each table in an application. Additionally, discrete domain filtering is included as well.
PL/SQL (and APEX) will work with a VARCHAR2 string as large as 32K. However, the database limits the maximum length of a VARCHAR2 to 4000 characters. DTGen handles this conversion automatically when string lengths are between 4001 and 32K characters. CLOB storage is used in the database while VARCHAR2 storage is used in PL/SQL. Since LOBs can require special handling in PL/SQL (and APEX), this is an simpler solution than using CLOBs for everything between 4001 and 32K characters in length.
With Audit and POP data stored separately from the online or active data, the OMNI view provides a single view of all data related to a surrogate key of a table. DTGen generates the APEX export file that has an OMNI view form defined for each table with history data.
Each of the ASOF view and OMNI view forms include filtering that allows column specific filtering of data. DTGen generates the APEX export file that has this ASOF view form and OMNI view form filtering defined for each table with history data.
One of the second most difficult concerns for a database designer is dealing with potential deployment issues when more than one database server is involved. The following includes details of some of the deployment options handled by DTGen.
One option available to the database designer is the deployment of Oracle databases at the mid-tier. Much of the integrity constraint enforcement can be off-loaded from the database-tier to the mid-tier. DTGen generates and assembles the scripts necessary to deploy SQL and API access directly onto mid-tier servers.
Another option available to the database designer is the deployment of the central schema across multiple database-tier nodes through the use of Oracle RAC. Fortunately, RAC deployment is transparent to the database schema and requires not special functionality from DTGen.
There is a switch (db_constraints) that allows integrity constraint enforcement to be dynamically moved between the mid-tier and database-tier servers. DTGen generates the PL/SQL code needed to deploy this functionality.
Another option available is the execution of DML and POP on either the database servers or mid-tier servers. DTGen generates that PL/SQL and SQL code needed to allow this access at both tiers.
Part of the deployment option is the caching of data at the mid-tiers through the use of materialized views. Slow changing tables can be identified for mid-tier caching. Also, a refresh rate can be specified for each of these tables. DTGen generates the PL/SQL and SQL code needed to deploy this caching and automated refresh to the mid-tier.
These functions should be avoided due to the difficulty of maintaining foreign key transaction integrity until issue #2 is resolved.
Oracle and Java are registered trademarks of Oracle Corporation and/or its affiliates.