T1.5: Develops Object/Relational Applications

Knowledge Review - InterSystems IRIS SQL Specialist

1. Recalls default object/relational mapping behavior

Key Points

  • Package to Schema: Package name maps to SQL schema (default: User package = SQLUser schema)
  • Class to Table: Persistent class name becomes SQL table name (flattened inheritance model)
  • Property to Field: Class properties project as table columns, including inherited properties
  • Embedded Objects: SerialObject properties project as multiple columns with underscore notation (e.g., Home_Street, Home_City)
  • Collections: List properties become list fields; array properties become child tables

Detailed Notes

Overview

InterSystems IRIS provides automatic object-relational mapping for all persistent classes that extend %Library.Persistent (or %Persistent).

  • Bidirectional projection: Each class instance is simultaneously available as an object and as a row in a SQL table
  • Automatic creation: Projected table is created when persistent class is compiled

Default Mapping Rules

The mapping follows predictable default rules:

  • Package to Schema: Package name maps to SQL schema (default: User package = SQLUser schema)
  • Class to Table: Persistent class name becomes SQL table name
  • Property to Column: Class properties project as table columns, including inherited properties

Inheritance Handling

Because SQL lacks inheritance support:

  • Flattened representation: Each table contains all properties from the class and its superclasses
  • Extent model: All instances of a class and its subclasses compose the class extent
  • Query benefit: Queries against Person retrieve all person types (including Student) without complex joins

Special Property Projections

  • Embedded objects (SerialObjects): Expanded into multiple columns using underscore notation
  • Example: Home property of type Address with Street and City becomes Home_Street and Home_City columns
  • Stream properties: Project as BLOB or CLOB fields
  • List properties: Become list fields
  • Array properties: Create separate child tables
  • Class methods with SqlProc: Project to SQL stored procedures

Data Access

Both access methods operate on the same underlying data stored in globals:

  • Object access: Via %OpenId, %Save methods
  • SQL access: Via SELECT, INSERT, UPDATE statements

Documentation References

2. Applies SQL best practices when defining persistent classes

Key Points

  • USEEXTENTSET=1: Always set this parameter for efficient global storage organization
  • Bitmap Extent Index: Include `Index BitmapExtent [ Extent, Type = bitmap ]` for optimized counting and extent operations
  • Data Type Classes: Use %Library package data types (%String, %Integer, %Date) for SQL projection
  • Property Parameters: Apply MAXLEN, MINLEN, MAXVAL, MINVAL, VALUELIST, and PATTERN for data validation
  • SqlTableName Keyword: Override default table name when SQL naming differs from class name

Detailed Notes

Critical Best Practices

When defining persistent classes that project to SQL tables, follow these critical best practices:

USEEXTENTSET Parameter

Always specify `Parameter USEEXTENTSET = 1;` in the class definition.

  • Purpose: Organizes table storage into an efficient set of globals rather than a single global
  • Benefit: Significantly improves performance for large tables

Bitmap Extent Index

Create a bitmap extent index using `Index BitmapExtent [ Extent, Type = bitmap ];`

  • Purpose: Creates an index of all IDs in the extent set
  • Benefit: Makes COUNT queries and other extent-wide operations much faster

Data Type Classes and Validation

Use appropriate data type classes from the %Library package (%String, %Integer, %Date, etc.) with validation parameters:

  • MAXLEN / MINLEN: String length limits
  • MAXVAL / MINVAL: Numeric ranges
  • VALUELIST: Enumerated values
  • PATTERN: Pattern matching
  • TRUNCATE=1: Truncate rather than error on oversized strings

Property Keywords

Leverage property keywords for enhanced functionality:

  • Required: Mandatory field
  • InitialExpression: Default values
  • SqlComputed / SqlComputeCode: Calculated columns
  • SqlComputeOnChange: Recompute when dependencies change
  • Unique columns: Define both the property and a unique index (rather than UNIQUE keyword)

SerialObjects for Grouping

Use embedded SerialObjects to simplify table structure when multiple properties logically group together (like address components).

DDL Compatibility

  • [DdlAllowed]: Specify this when table definitions originate from classes to permit subsequent SQL DDL modifications
  • CREATE TABLE DDL: Automatically applies USEEXTENTSET and bitmap extent index settings to generated persistent class
  • Direct class creation: Must explicitly include these best practices

Documentation References

Exam Preparation Summary

Critical Concepts to Master:

  1. Object-SQL Mapping: Package=Schema, Class=Table, Property=Field, SerialObject=Multiple Fields
  2. Extent Model: Class extent includes all instances of class and subclasses (flattened inheritance)
  3. USEEXTENTSET Parameter: Always set to 1 for production tables
  4. Bitmap Extent Index: Required for efficient COUNT and extent operations
  5. Property Validation: MAXLEN, MINLEN, MAXVAL, MINVAL, VALUELIST, PATTERN parameters
  6. Data Type Classes: Must use %Library package types for SQL projection
  7. SerialObject Pattern: Access embedded object properties with underscore (Home_Street)

Common Exam Scenarios:

  • Identifying which object elements project to which SQL elements
  • Recognizing missing best practices in class definitions (USEEXTENTSET, bitmap extent index)
  • Understanding how SerialObject properties appear in SQL (underscore notation vs container field)
  • Applying appropriate property parameters for data validation requirements
  • Distinguishing between extent behavior and traditional relational table behavior
  • Knowing when data validation occurs (INSERT/UPDATE/Save, not on SELECT)

Hands-On Practice Recommendations:

  • Create persistent classes with and without USEEXTENTSET, compare storage structure
  • Define classes with SerialObject properties, query using underscore notation
  • Implement property validation using MAXLEN, VALUELIST, and PATTERN parameters
  • Test extent queries across class hierarchies (Person extent including Student instances)
  • Create tables via both CREATE TABLE DDL and persistent class definitions, compare results
  • Use SqlComputeCode for calculated columns with dependencies
  • Apply unique indexes rather than UNIQUE constraints
  • Examine global storage structure for persistent objects using direct global access

Report an Issue