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

▶
slide slide uppercase only 1 6 using the table api
SMART_READER_LITE
LIVE PREVIEW

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

Mar 04, 2023 221 likes •273 views

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

slide-1
SLIDE 1

Slide 1 Using the Table API Slide 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

Slide 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

Slide 4 Specification Summary

Sequence Populated Denormalized Uppercase Domain Autogen

Slide 5 Force Uppercase

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

Slide 6 Uppercase Only

Uppercase Server Derived?

Slide 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

Slide 8 Autogen Columns

Server Derived? Autogen Type

Slide 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

Slide 10

S e r v e r P a c k a g e s

Validating Domains

GAME_CATEGORY ARC RV_DOMAIN RV_LOW_ VALUE GAME _CATEGORY ARC GAME _CATEGORY EDU GAME _CATEGORY ROL GAME _CATEGORY SIM Triggers CG_REF_CODES CG_REF_CODES DML TITLES TITLES

slide-2
SLIDE 2

Slide 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

Slide 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

Slide 13 Domain Validation Slide 14 Domain Validation

Server Derived? Domain

Slide 15 How to Populate with Sequence

  • Make sure sequence is defined
  • For Populated Column:

– Set ‘Sequence’ to desired sequence

  • Generate API

Slide 16 Populate with Sequence

Server Derived? Sequence Sequence Defined

Slide 17 Validating Arcs

GAMES # * PRODUCT_CODE . . . TITLES # * PRODUCT_CODE * TITLE . . .

  • MO_PRODUCT_CODE
  • GA_PRODUCT_CODE

. . . MOVIES # * PRODUCT_CODE . . .

DML Triggers S e r v e r P a c k a g e s

Slide 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 19 Performing Cascade Update

Triggers

PRODUCT_CODE 25 TI_PRODUCT_CODE 25 25 TITLES TITLES COPIES COPIES Update 30

S e r v e r P a c k a g e s

30 30

Slide 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
  • ne time
slide-3
SLIDE 3

Slide 21 Foreign Key Cascade

FK properties (NOT FK Column) Delete Rule Validate in Server

Slide 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

Slide 23

S e r v e r P a c k a g e s

Journaling Tables

PRODUCT_CODE . . . TITLES TITLES PRODUCT_CODE . . . TITLES_JN TITLES_JN Triggers DML 30 30

Slide 24 To Set Up Journaling

  • Table Property: ‘Journal’
  • Set to

– ‘Server’ – or ‘Client Calls Server Procedure’

  • Generate API

Slide 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)

Slide 26 Copying Denormalized Values

PRODUCT_CODE TITLE 30 TITANIC TITLES TITLES TI_PRODUCT_CODE TITLE COPIES COPIES By foreign key By foreign key DML 30

S e r v e r P a c k a g e s

TITANIC Triggers

Slide 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.

Slide 28 Denormalization

. . .

Slide 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

Slide 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
slide-4
SLIDE 4

Slide 31 Specification Summary

Sequence Populated Denormalized Uppercase Domain Autogen

Slide 32 Generate DB from Server Model Slide 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) ) /

Slide 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') / . . .

Slide 35 Generate Table API Slide 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' ...

Slide 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

Slide 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;

Slide 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

Slide 40 Test with Form