Kbase P13836: What does the Adjust Schema option do and why use it?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/4/2008 |
|
Status: Verified
GOAL:
What does the Adjust Schema option do?
GOAL:
Why use the Adjust Schema option?
FACT(s) (Environment):
Oracle DataServer
MS SQL DataServer
ODBC DataServer
All Supported Operating Systems
FIX:
With Oracle DataServer, the Adjust Schema utility allows you to migrate schema changes from an ORACLE database and compare it to the same schema in a Progress database. If you make changes in both your Progress database and the same changes in ORACLE using ORACLE tools, pull those changes into the schema holder. The Adjust Schema utility will compare the Progress database to the schema image and update the Progress attributes necessary for your 4GL code to run against both data sources.
With the ODBC & SQL Server DataServer, the Adjust Schema utility allows you to compare your ODBC & SQL Server Schema holder information with the original Progress database, updating any Progress Attributes in the schema holder. These attributes include field names, labels, help, validation expressions and messages. You must be connected to the Progress database and the schema holder that you want to compare to before running this utility.
This utility allows you to migrate schema changes from an foriegn datasource and compare it to the same schema in a Progress database. Essentially, the utility will look for all matching tables and add the necessary metaschema information so that the Progress application (currently being run against a Progress db), works and runs identical to the foreign datasource. Thus, no code manipulation is needed.
Review the following case study. **Please note that the example provided is for ProtoOra (Progress to Oracle Migration).
When ProtoOra is run for the ORDER table in SPORTS database, the SQL generated;
CREATE TABLE order_ (
order_num number DEFAULT 0,
cust_num number DEFAULT 0,
order_date date DEFAULT SYSDATE,
ship_date date,
promise_date date,
carrier varchar2 (20),
instructions varchar2 (20),
po varchar2 (20),
terms varchar2 (20) DEFAULT 'Net30',
U##sales_rep varchar2 (4),
sales_rep varchar2 (4),
progress_recid number null
Note that the "ORDER" table in ORACLE is generated as "order_". If this table is seperately PULLED into an empty schema holder, note that the schema holder defintions appear as:
ADD TABLE "ORDER_" TYPE ORACLE
AREA "Schema Area"
DUMP-NAME "ORDER_"
FOREIGN-NAME "ORDER_"
FOREIGN-OWNER "PROGRESS"
FOREIGN-TYPE "TABLE"
PROGRESS-RECID 12
By default, basic 4GL developed against a Progress db, is no longer functional against a newly created pulled table within a schema holder.
FOR EACH order.
DISPLAY order.
END.
However, the following 4GL is successful.
FOR EACH order_.
DISPLAY order_.
END.
By adjusting the schema, running the Adjust Schema Utility, all metaschema defintions within the schema holder match and become consistent with the schema definitions of the Progress database. And basic 4GL developed against a Progress db functions against a foriegn datasource.
Here is a sample definition file of a DataServer PULL:
ADD TABLE "ORDER_" TYPE ORACLE
AREA "Schema Area"
DUMP-NAME "ORDER_"
FOREIGN-NAME "ORDER_"
FOREIGN-OWNER "PROGRESS"
FOREIGN-TYPE "TABLE"
PROGRESS-RECID 12
ADD FIELD "CARRIER" OF "ORDER_" AS character
FORMAT "x(20)"
INITIAL ?
POSITION 2
LENGTH 20
ORDER 1060
CASE-SENSITIVE
FOREIGN-POS 6
FOREIGN-NAME "CARRIER"
FOREIGN-TYPE "CHAR"
FOREIGN-MAXIMUM 20
FIELD-MISC13 20
FIELD-MISC14 1
Here is a sample definition file after a Adjust Schema is run. **ADDITION/CHANGE** allows you to see changes within metaschema.
ADD TABLE "Order" TYPE ORACLE <------**CHANGE**
AREA "Schema Area"
DESCRIPTION "Order header information" <------**ADDITION**
VALEXP "1 = 1" <------**ADDITION**
DUMP-NAME "order"
FOREIGN-NAME "ORDER_"
FOREIGN-OWNER "PROGRESS"
FOREIGN-TYPE "TABLE"
PROGRESS-RECID 12
TABLE-TRIGGER "CREATE" NO-OVERRIDE PROCEDURE "sports/crord.p" CRC "?" <------**ADDITION**
ADD FIELD "Carrier" OF "Order" AS character.
DESCRIPTION "Should lookup valid carriers." <------**ADDITION**
FORMAT "x(20)"
INITIAL ""
POSITION 2
ORDER 120
FOREIGN-POS 6
FOREIGN-NAME "CARRIER"
FOREIGN-TYPE "CHAR"
FOREIGN-MAXIMUM 20
FIELD-MISC13 20
FIELD-MISC14 1
Note the following changes:
1. Table "order_" is now "order"
2. Addition of "TABLE-TRIGGER" is added
3. All validation code is kept
4. Table and column "descriptions" are added
5. "COLUMN-LABEL" are added
6. Other Metaschema fields that may not apply to DataServer, such as "LENGTH" is removed..