Using the Table API

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
Validating Domains
TITLES
GAME_CATEGORY
Triggers
ARC
CG_REF_CODES
RV_DOMAIN RV_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
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
Performing Cascade Update
COPIES
TITLES
TI_PRODUCT_CODE
PRODUCT_CODE
30
25
2525
30
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
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
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 CGERRORS
... 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 (cgrec.PURCHASES_ID is NULL) THEN
-- SEQUENCE SELECT
PCH_SEQ.nextval INTO cgrec.PURCHASES_ID
FROM DUAL END IF
EXCEPTION WHEN OTHERS THEN cgerrors.push(SQLERRM,
.) cgerrors.raise_failure END
cgrec.CREATOR user
-- AUTOGEN cgrec.CREATEDATE
trunc(sysdate) END IF cgrec.ITEM
upper(cgrec.ITEM) --
UPPERCASE IF(cgrec.PCH_DNUMBER IS NULL) THEN
cgrec.DNAME NULL ELSE SELECT DNAME
INTO cgrec.DNAME -- DENORMALIZATION
FROM DEPARTMENTS WHERE
DNUMBER cgrec.PCH_DNUMBER END IF
39
Test With SQLPLUS
SQLgt insert into purchases( 2
PURCHASES_ID,PCH_DNUMBER,DNAME,ITEM,PCH_TYPE,QUANT
ITY,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