Slide Slide Uppercase Only 1 6 Using the Table API Uppercase - PDF document

Slide Slide Uppercase Only 1 6 Using the Table API Uppercase Server Derived? Slide Slide Standard Uses of API 2 7 Autogen Code • Populate Columns via Sequences • Insert Default Values instead of nulls • For autogen column • Populate Autogen Columns – Set Autogen Type – Date Created, Date Modified • Date Modified/Created • Created/Modified By – Who Created, Who Modified • Sequence within Parent – Sequence within Parent – Set ‘Server Derived’ to Yes. • Force Uppercase • Generate API • Domain Validation • Arc validation Slide Slide 3 8 Standard Uses of API Autogen Columns • Foreign Key Cascades, Nullifies, Defaults, Restricted Delete/Update • Table Journaling • Derived Columns • Denormalization: – Copy Parent Value to Child – Aggregate Child Values to Parent Autogen Type Server Derived? Slide Slide 4 9 Specification Summary Domain Validation Sequence Populated • Via Check Constraint (non-API) • Via Lookup in CG_REF_CODES table Denormalized • How? Uppercase – Define Domain Domain – Define Column using Domain, or Autogen – Define Column with ‘Allowable Values’ – Generate Table API Slide Slide 5 10 Validating Domains Force Uppercase TITLES TITLES GAME_CATEGORY • Set ‘Uppercase’ to Yes Triggers DML ARC • Set ‘Server Derived?’ to Yes • Generate Table API CG_REF_CODES CG_REF_CODES RV_DOMAIN RV_LOW_ VALUE e r v e r S GAME _CATEGORY ARC s e a g k c P a GAME _CATEGORY EDU GAME _CATEGORY ROL GAME _CATEGORY SIM

Slide Slide Populate with Sequence 11 16 Advantages of REF_CODES • DBA must alter Check constraint • May have to bring down system • CG_REF_CODES table can be accessed thru Application Sequence Sequence Defined Server Derived? Slide Slide Schema of CG_REF_CODES 12 17 Validating Arcs • RV_DOMAIN MOVIES TITLES – Domain name, or # * PRODUCT_CODE DML # * PRODUCT_CODE . . . – Table.column * TITLE . . . GAMES • RV_LOW_VALUE o MO_PRODUCT_CODE o GA_PRODUCT_CODE # * PRODUCT_CODE – One legal value, e.g., UT . . . . . . – Low value of a range • RV_HIGH_VALUE r v e r S e s e g • RV_ABBREVIATION, e.g., UNIT k a c Triggers a P • RV_MEANING, e.g., Unit Test Slide Slide Domain Validation 13 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 Slide Slide Domain Validation 14 19 Performing Cascade Update TITLES TITLES COPIES COPIES TI_PRODUCT_CODE PRODUCT_CODE Domain Update 25 30 25 30 25 30 r e v e r S s e g a Triggers k c a P Server Derived? Slide Slide 15 20 How to Populate with Sequence Cascading Updates/Deletes • Make sure sequence is defined • For foreign key, • For Populated Column: – Set ‘Update Rule’ – Set ‘Delete Rule’ – Set ‘Sequence’ to desired sequence – Set ‘Validate In’ to Server or Both • Generate API • Generate Table API for Parent and Child at Same Time • Usually best to generate whole API at one time

Slide Slide Foreign Key Cascade 21 26 Copying Denormalized Values TITLES TITLES By foreign key By foreign key PRODUCT_CODE TITLE FK properties 30 TITANIC (NOT FK Column) COPIES COPIES TI_PRODUCT_CODE TITLE TITANIC Validate in Server 30 DML r e v e r S s e Delete Rule g a k c Triggers P a Slide Slide 22 27 Journal Tables How To Denormalize into Child • Have all fields of Base Table • In Child table, for target column • Plus: operation, date, user, application – Set ‘From Column’ to column in Parent. – Set ‘Via Foreign Key’ as appropriate. • Contains only needed table data – Set ‘Server Derived’ to Yes – Key for deletes • Generate API for both parent and – Key plus updated columns for updates child. Slide Slide Journaling Tables 23 28 Denormalization TITLES TITLES PRODUCT_CODE . . . DML 30 . . . TITLES_JN TITLES_JN Triggers PRODUCT_CODE . . . r e 30 v e r S s e g a k c a P Slide Slide 24 29 To Set Up Journaling How to Aggregate into Parent • Table Property: ‘Journal’ • For target column in parent • Set to – Set ‘From Column’ – Set ‘Via Foreign Key’ – ‘Server’ – Set ‘Using Operator’ (Count, Sum, etc.) – or ‘Client Calls Server Procedure’ – Set ‘Server Derived’ to Yes • Generate API • Generate API for Parent and Child Slide Slide Column Derivations 25 30 Denormalization • Calculate value based on columns in the same record – Area := Height * Width • Most Common: Bring Parent Value – Name := Lname || ‘, ‘ || Fname (e.g., Department Name) into Child • For target column Column (e.g., Employee Table) • Aggregate Children (Count, Sum, – set ‘Derivation Expression Type’ to SQL Expression Average of Child Column) – Enter ‘Derivation Expression’ – Set Server Derived to Yes • Generate API

Slide Slide Some Messages for Purchases 31 36 Specification Summary Creating Table API Error Package CG$ERRORS ... Sequence Populated Creating Table API Package Specification for Table 'PURCHASES' ... Denormalized ... Creating Table API Package Body for Table 'PURCHASES' ... Uppercase ... Creating Trigger Logic for Table 'PURCHASES' ... Domain Creating Before Delete Statement Trigger on 'PURCHASES' ... Creating Before Delete Row Trigger on 'PURCHASES' ... Autogen Creating After Delete Row Trigger on 'PURCHASES' ... Creating After Delete Statement Trigger on 'PURCHASES' ... Slide Slide 32 37 Generate DB from Server Model 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 Slide Slide Up_autogen_columns 33 38 REF_CODES Table Created -- Code highlights IF (operation = 'INS') THEN BEGIN PROMPT Creating Table 'CG_REF_CODES' IF (cg$rec.PURCHASES_ID is NULL) THEN -- SEQUENCE SELECT PCH_SEQ.nextval INTO cg$rec.PURCHASES_ID CREATE TABLE CG_REF_CODES FROM DUAL; (RV_DOMAIN VARCHAR2(100) NOT NULL END IF; ,RV_LOW_VALUE VARCHAR2(240) NOT NULL EXCEPTION WHEN OTHERS THEN cg$errors.push(SQLERRM, ….) cg$errors.raise_failure; ,RV_HIGH_VALUE VARCHAR2(240) END; ,RV_ABBREVIATION VARCHAR2(240) cg$rec.CREATOR := user; -- AUTOGEN ,RV_MEANING VARCHAR2(240) 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; Slide Slide 34 39 REF_CODES Table Populated Test With SQLPLUS DELETE FROM CG_REF_CODES SQL> insert into purchases( WHERE R V _DOMAIN = 'PCH_TYPE_TYPE' 2 PURCHASES_ID,PCH_DNUMBER,DNAME,ITEM,PCH_TYPE,QUANTITY,COST, 3 CREATOR,CREATEDATE) / 4 values( INSERT INTO CG_REF_CODES (RV_DOMAIN, …) 5 null, 10, null, 'soap', 'SU', 1, 10, VALUES ('PCH_TYPE_TYPE', 'OT', NULL, 'OTHER', 'Other') 6 null,null); / INSERT INTO CG_REF_CODES (RV_DOMAIN, …) ID DNO DNAME ITEM PC QTY COST CREATOR CREATEDAT VALUES ('PCH_TYPE_TYPE', 'EQ', NULL, ---- ---- --------------- ----- -- ---- ---- ---------- --------- 'EQUIP', 'Equipment over $500') 5 10 ADMINISTRATION SOAP SU 1 10 IBL 16-APR-00 / . . . Slide Slide 35 40 Generate Table API Test with Form