Caché Programming Orientation Guide
Persistent Objects and Caché SQL
[Home] [Back] [Next]
InterSystems: The power behind what matters   

A key feature in Caché is its combination of object technology and SQL. You can use the most convenient access mode for any given scenario. This chapter describes how Caché provides this feature and gives an overview of your options for working with stored data. It discusses the following topics:

Caché provides what is sometimes called an object database: a database combined with an object-oriented programming language. As a result, you can write flexible code that does all of the following:
That is, you can choose the access mode that suits your needs at any given time.
Internally, all access is done via direct global access, and you can access your data that way as well when appropriate. (If you have a class definition, it is not recommended to use direct global access to make changes to the data.)
Caché SQL
Caché provides an implementation of SQL, known as Caché SQL.
Caché SQL supports the complete entry-level SQL-92 standard with a few exceptions and several special extensions. Caché SQL also supports indices, triggers, BLOBs, and stored procedures (these are typical RDBMS features but are not part of the SQL-92 standard). For a complete list, see Using Caché SQL.
Where You Can Use Caché SQL
You can use Caché SQL within routines and within methods. To use SQL in these contexts, you can use either or both of the following tools:
Also, you can execute Caché SQL directly within the SQL Shell (in the Terminal) and in the Management Portal. Each of these includes an option to view the query plan, which can help you identify ways to make a query more efficient.
Object Extensions to SQL
To make it easier to use SQL within object applications, Caché includes a number of object extensions to SQL.
One of the most interesting of these extensions is ability to follow object references using the reference (“–>”) operator. For example, suppose you have a Vendor class that refers to two other classes: Contact and Region. You can refer to properties of the related classes using the reference operator:
SELECT ID,Name,ContactInfo->Name
FROM Vendor
WHERE Vendor->Region->Name = 'Antarctica'
Of course, you can also express the same query using SQL JOIN syntax. The advantage of the reference operator syntax is that it is succinct and easy to understand at a glance.
Special Options for Persistent Classes
In Caché, all persistent classes extend %Library.Persistent (also referred to as %Persistent). This class provides much of the framework for the object-SQL correspondence in Caché. Within persistent classes, you have options like the following:
SQL Projection of Persistent Classes
For any persistent class, each instance of the class is available as a row in a table that you can query and manipulate via SQL. To demonstrate this, this section uses the Management Portal and the Terminal, which are introduced later in this book.
Demonstration of the Object-SQL Projection
Consider the Sample.Person class in SAMPLES. If we use the Management Portal to display the contents of the table that corresponds to this class, we see something like the following:
(This is not the same data that you see, because this sample is repopulated at each release.) Note the following points:
In the Terminal, we can use a series of commands to look at the first person:
SAMPLES>set person=##class(Sample.Person).%OpenId(1)
SAMPLES>write person.Name
Van De Griek,Charlotte M.
SAMPLES>write person.FavoriteColors.Count()
SAMPLES>write person.FavoriteColors.GetAt(1)
SAMPLES>write person.SSN
These are the same values that we see via SQL.
Basics of the Object-SQL Projection
Because inheritance is not part of the relational model, the class compiler projects a “flattened” representation of a persistent class as a relational table. The following table lists how some of the various object elements are projected to SQL:
Object Concept SQL Concept
Package Schema
Class Table
Data type property Field
Embedded object Set of fields
List property List field
Array property Child table
Stream property BLOB
Index Index
Class method marked as stored procedure Stored procedure
The projected table contains all the appropriate fields for the class, including those that are inherited.
Classes and Extents
Caché uses an unconventional and powerful interpretation of the object-table mapping.
All the stored instances of a persistent class compose what is known as the extent of the class, and an instance belongs to the extent of each class of which it is an instance. Therefore:
Indices automatically span the entire extent of the class in which they are defined. The indices defined in Person contain both Person instances and Student instances. Indices defined in the Student extent contain only Student instances.
The subclass can define additional properties not defined in its superclass. These are available in the extent of the subclass, but not in the extent of the superclass. For example, the Student extent might include the FacultyAdvisor field, which is not included in the Person extent.
The preceding points mean that it is comparatively easy in Caché to write a query that retrieves all records of the same type. For example, if you want to count people of all types, you can run a query against the Person table. If you want to count only students, run the same query against the Student table. In contrast, with other object databases, to count people of all types, it would be necessary to write a more complex query that combined the tables, and it would be necessary to update this query whenever another subclass was added.
Object IDs
Each object has a unique ID within each extent to which it belongs. In most cases, you use this ID to work with the object. This ID is the argument to the following commonly used methods of the %Persistent class:
The class has other methods that use the ID, as well.
How an ID Is Determined
Caché assigns the ID value when you first save an object. The assignment is permanent; you cannot change the ID for an object. Objects are not assigned new IDs when other objects are deleted or changed.
Any ID is unique within its extent.
The ID for an object is determined as follows:
Accessing an ID
To access the ID value of an object, you use the %Id() instance method that the object inherits from %Persistent.
In SQL, the ID value of an object is available as a pseudo-field called %Id. Note that when you browse tables in the Management Portal, the %Id pseudo-field is displayed with the caption ID:
Despite this caption, the name of the pseudo-field is %Id.
Each persistent class definition includes information that describes how the class properties are to be mapped to the globals in which they are actually stored. The class compiler generates this information for the class and updates it as you modify and recompile.
A Look at a Storage Definition
It can be useful to look at this information, and on rare occasions you might want to change some of the details (very carefully). For a persistent class, Studio displays something like the following as part of your class definition:
<Storage name="Default">
<Data name="PersonDefaultData"><Value name="1">
<Value name="2">
<Value name="3">
<Value name="4">
Globals Used by a Persistent Class
The storage definition includes several elements that specify the globals in which the data is stored:
By default, with default storage:
If the complete class name is long, the system automatically uses a hashed form of the class name instead. So when you view a storage definition, you might sometimes see global names like ^package1.pC347.VeryLongCla4F4AD. If you plan to work directly with the data global for a class for any reason, make sure to examine the storage definition so that you know the actual name of the global.
Informally, these globals are sometimes called the class globals, but this can be a misleading phrase. The class definitions are not stored in these globals.
For more information on how global names are determined, see Globals in Using Caché Objects.
Default Structure for a Stored Object
For a typical class, most data is contained in the data global, which includes nodes as follows:
Node Node Contents
Where full_class_name is the complete class name including package, hashed if necessary to keep the length to 31 characters. Also, id is the object ID as described in Object IDs.”
List of the format returned by $ListBuild.
In this list, stored properties are listed in the order given by the name attribute of the <Value> element in the storage definition.
By definition, transient properties are not stored. Stream properties are stored in the stream global for the class.
For an example, see A Look at Stored Data,” later in this chapter.
Note the following points:
Options for Creating Persistent Classes and Tables
To create a persistent class and its corresponding SQL table, you can do any of the following:
Accessing Data
To access, modify, and delete data associated with a persistent class, your code can do any or all of the following:
Caché SQL is suitable in situations like the following:
Object access is suitable in situations like the following:
A Look at Stored Data
This section demonstrates that for any persistent object, the same values are visible via object access, SQL access, and direct global access.
In Studio, if we view the Sample.Person class, we see the following property definitions:
/// Person's name.
Property Name As %String(POPSPEC = "Name()") [ Required ];


/// Person's age.<br> 
/// This is a calculated field whose value is derived from <property>DOB</property>. 
Property Age As %Integer [ details removed for this example ]; 

/// Person's Date of Birth.
Property DOB As %Date(POPSPEC = "Date()");
In the Terminal, we can open a stored object and write its property values:
SAMPLES>set person=##class(Sample.Person).%OpenId(1)
SAMPLES>w person.Name
Newton,Dave R.
SAMPLES>w person.Age
SAMPLES>w person.DOB
Note that here we see the literal, stored value of the DOB property. We could instead call a method to return the display value of this property:
SAMPLES>write person.DOBLogicalToDisplay(person.DOB)
In the Management Portal, we can browse the stored data for this class, which looks as follows:
Notice that in this case, we see the display value for the DOB property. (In the Portal, there is another option to execute queries, and with that option you can control whether to use logical or display mode for the results.)
In the Portal, we can also browse the global that contains the data for this class:
Or, in the Terminal, we can write the value of the global node that contains this instance:
zw ^Sample.PersonD("1")
^Sample.PersonD(1)=$lb("","Newton,Dave R.","384-10-6538",58153,$lb("6977 First Street","Pueblo","AK",63163),
$lb("9984 Second Blvd","Washington","MN",42829),"",$lb("Red"))
For reasons of space, the last example contains an added line break.
Storage of Generated Code for Caché SQL
For Caché SQL (except when used as embedded SQL), the system generates reusable code to access the data.
When you first execute an SQL statement, Caché optimizes the query and generates and stores code that retrieves the data. It stores the code in the query cache, along with the optimized query text. Note that this cache is a cache of code, not of data.
Later when you execute an SQL statement, Caché optimizes it and then compares the text of that query to the items in the query cache. If Caché finds a stored query that matches the given one (apart from minor differences such as whitespace), it uses the code stored for that query.
You can view the query cache and delete any items in it.
For More Information
For more information on the topics covered in this chapter, see the following:

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA