1 / 40

Using the Table API

Using the Table API. Standard Uses of API. Populate Columns via Sequences Insert Default Values instead of nulls Populate Autogen Columns Date Created, Date Modified Who Created, Who Modified Sequence within Parent Force Uppercase Domain Validation Arc validation. Standard Uses of API.

odina
Download Presentation

Using the Table API

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using the Table API

  2. Standard Uses of API • Populate Columns via Sequences • Insert Default Values instead of nulls • Populate Autogen Columns • Date Created, Date Modified • Who Created, Who Modified • Sequence within Parent • Force Uppercase • Domain Validation • Arc validation

  3. Standard Uses of API • Foreign Key Cascades, Nullifies, Defaults, Restricted Delete/Update • Table Journaling • Derived Columns • Denormalization: • Copy Parent Value to Child • Aggregate Child Values to Parent

  4. Specification Summary Sequence Populated Denormalized Uppercase Domain Autogen

  5. Force Uppercase • Set ‘Uppercase’ to Yes • Set ‘Server Derived?’ to Yes • Generate Table API

  6. Uppercase Only Uppercase Server Derived?

  7. Autogen Code • For autogen column • Set Autogen Type • Date Modified/Created • Created/Modified By • Sequence within Parent • Set ‘Server Derived’ to Yes. • Generate API

  8. Autogen Columns Autogen Type Server Derived?

  9. Domain Validation • Via Check Constraint (non-API) • Via Lookup in CG_REF_CODES table • How? • Define Domain • Define Column using Domain, or • Define Column with ‘Allowable Values’ • Generate Table API

  10. DML Validating Domains TITLES GAME_CATEGORY Triggers ARC CG_REF_CODES RV_DOMAINRV_LOW_VALUE ServerPackages GAME _CATEGORY ARC GAME _CATEGORY EDUGAME _CATEGORY ROLGAME _CATEGORY SIM

  11. Advantages of REF_CODES • DBA must alter Check constraint • May have to bring down system • CG_REF_CODES table can be accessed thru Application

  12. Schema of CG_REF_CODES • RV_DOMAIN • Domain name, or • Table.column • RV_LOW_VALUE • One legal value, e.g., UT • Low value of a range • RV_HIGH_VALUE • RV_ABBREVIATION, e.g., UNIT • RV_MEANING, e.g., Unit Test

  13. Domain Validation

  14. Domain Validation Domain Server Derived?

  15. How to Populate with Sequence • Make sure sequence is defined • For Populated Column: • Set ‘Sequence’ to desired sequence • Generate API

  16. Populate with Sequence Sequence Sequence Defined Server Derived?

  17. DML Validating Arcs MOVIES # * PRODUCT_CODE . . . TITLES # * PRODUCT_CODE * TITLE . . . o MO_PRODUCT_CODE o GA_PRODUCT_CODE . . . GAMES # * PRODUCT_CODE . . . ServerPackages Triggers

  18. Arc Validation • Explicit style only: one column for each FK in Arc • For each FK in Table with Arc, • Set ‘Arc Number’ same for same arc • Set ‘Arc Mandatory’ to Yes or No • Generate Table API for Table

  19. Update Triggers Performing Cascade Update COPIES TITLES TI_PRODUCT_CODE PRODUCT_CODE 30 25 30 2525 30 ServerPackages

  20. Cascading Updates/Deletes • For foreign key, • Set ‘Update Rule’ • Set ‘Delete Rule’ • Set ‘Validate In’ to Server or Both • Generate Table API for Parent and Child at Same Time • Usually best to generate whole API at one time

  21. Foreign Key Cascade FK properties (NOT FK Column) Validate in Server Delete Rule

  22. Journal Tables • Have all fields of Base Table • Plus: operation, date, user, application • Contains only needed table data • Key for deletes • Key plus updated columns for updates

  23. DML Journaling Tables TITLES PRODUCT_CODE . . . 30 TITLES_JN Triggers PRODUCT_CODE . . . 30 ServerPackages

  24. To Set Up Journaling • Table Property: ‘Journal’ • Set to • ‘Server’ • or ‘Client Calls Server Procedure’ • Generate API

  25. Denormalization • Most Common: Bring Parent Value (e.g., Department Name) into Child Column (e.g., Employee Table) • Aggregate Children (Count, Sum, Average of Child Column)

  26. DML Copying Denormalized Values TITLES By foreign key PRODUCT_CODE TITLE 30 TITANIC COPIES TI_PRODUCT_CODE TITLE TITANIC 30 ServerPackages Triggers

  27. How To Denormalize into Child • In Child table, for target column • Set ‘From Column’ to column in Parent. • Set ‘Via Foreign Key’ as appropriate. • Set ‘Server Derived’ to Yes • Generate API for both parent and child.

  28. Denormalization . . .

  29. How to Aggregate into Parent • For target column in parent • Set ‘From Column’ • Set ‘Via Foreign Key’ • Set ‘Using Operator’ (Count, Sum, etc.) • Set ‘Server Derived’ to Yes • Generate API for Parent and Child

  30. Column Derivations • Calculate value based on columns in the same record • Area := Height * Width • Name := Lname || ‘, ‘ || Fname • For target column • set ‘Derivation Expression Type’ to SQL Expression • Enter ‘Derivation Expression’ • Set Server Derived to Yes • Generate API

  31. Specification Summary Sequence Populated Denormalized Uppercase Domain Autogen

  32. Generate DB from Server Model

  33. REF_CODES Table Created PROMPT Creating Table 'CG_REF_CODES' CREATE TABLE CG_REF_CODES (RV_DOMAIN VARCHAR2(100) NOT NULL ,RV_LOW_VALUE VARCHAR2(240) NOT NULL ,RV_HIGH_VALUE VARCHAR2(240) ,RV_ABBREVIATION VARCHAR2(240) ,RV_MEANING VARCHAR2(240) ) /

  34. REF_CODES Table Populated DELETE FROM CG_REF_CODES WHERE RV_DOMAIN = 'PCH_TYPE_TYPE' / INSERT INTO CG_REF_CODES (RV_DOMAIN, …) VALUES ('PCH_TYPE_TYPE', 'OT', NULL, 'OTHER', 'Other') / INSERT INTO CG_REF_CODES (RV_DOMAIN, …) VALUES ('PCH_TYPE_TYPE', 'EQ', NULL, 'EQUIP', 'Equipment over $500') / . . .

  35. Generate Table API

  36. Some Messages for Purchases Creating Table API Error Package CG$ERRORS ... Creating Table API Package Specification for Table 'PURCHASES' ... ... Creating Table API Package Body for Table 'PURCHASES' ... ... Creating Trigger Logic for Table 'PURCHASES' ... Creating Before Delete Statement Trigger on 'PURCHASES' ... Creating Before Delete Row Trigger on 'PURCHASES' ... Creating After Delete Row Trigger on 'PURCHASES' ... Creating After Delete Statement Trigger on 'PURCHASES' ...

  37. Package Spec • Trigger flag to avoid recursive call • Useful Constants • Row variable for Table • Table variable for Table • Procedures ins, upd, del, lck, slct • Procedures for validating arc, domains, cascading, denormalization

  38. Up_autogen_columns -- Code highlights IF (operation = 'INS') THEN BEGIN IF (cg$rec.PURCHASES_ID is NULL) THEN -- SEQUENCE SELECT PCH_SEQ.nextval INTO cg$rec.PURCHASES_ID FROM DUAL; END IF; EXCEPTION WHEN OTHERS THEN cg$errors.push(SQLERRM, ….) cg$errors.raise_failure; END; cg$rec.CREATOR := user; -- AUTOGEN cg$rec.CREATEDATE := trunc(sysdate); END IF; cg$rec.ITEM := upper(cg$rec.ITEM); -- UPPERCASE IF(cg$rec.PCH_DNUMBER IS NULL) THEN cg$rec.DNAME := NULL; ELSE SELECT DNAME INTO cg$rec.DNAME -- DENORMALIZATION FROM DEPARTMENTS WHERE DNUMBER = cg$rec.PCH_DNUMBER; END IF;

  39. Test With SQLPLUS SQL> insert into purchases( 2 PURCHASES_ID,PCH_DNUMBER,DNAME,ITEM,PCH_TYPE,QUANTITY,COST, 3 CREATOR,CREATEDATE) 4 values( 5 null, 10, null, 'soap', 'SU', 1, 10, 6 null,null); ID DNO DNAME ITEM PC QTY COST CREATOR CREATEDAT ---- ---- --------------- ----- -- ---- ---- ---------- --------- 5 10 ADMINISTRATION SOAP SU 1 10 IBL 16-APR-00

  40. Test with Form

More Related