Defining a Table by Creating a Persistent Class

The primary way to define tables within Caché is to use Studio to create persistent class definitions. When these classes are saved and compiled within the Caché database, they automatically projects to a relational table that corresponds to the class definition: each class represents a table; each property represents a column, and so on. The maximum number of properties (columns) definable for a class (table) is 1000.

For example, the following defines the persistent class MyApp.Person:

Class MyApp.Person Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
}

When compiled, this creates the MyApp.Person persistent class and the corresponding SQL table, Person within the MyApp schema. For details on how to perform these operations, refer to “Defining and Compiling Classes” in the Defining and Using Classes manual.

In this example, the package name MyApp is specified. When defining a persistent class, an unspecified package name defaults to User; this corresponds to the default SQL schema name SQLUser. For example, defining a table named Students as a persistent class creates the class User.Students, and the corresponding SQL schema.table name SQLUser.Students.

In this example, the persistent class name Person is the default SQL table name. You can use the SqlTableName class keyword to supply a different SQL table name.

The same MyApp.Person table could have been defined using the DDL CREATE TABLE statement, specifying the SQL schema.table name. Successful execution of this SQL statement generates a corresponding persistent class with package name MyApp and class name Person:

CREATE TABLE MyApp.Person (
    Name VARCHAR(50) NOT NULL,
    SSN VARCHAR(15) DEFAULT 'Unknown',
    DateOfBirth DATE,
    Sex VARCHAR(1)
)

CREATE TABLE does not specify an explicit StorageStrategy in the corresponding class definition. It instead takes the defined default storage strategy.

By default, CREATE TABLE specifies the Final class keyword in the corresponding class definition, indicating that it cannot have subclasses.

For an introduction to how the object view of the database corresponds to the relational view, see “Introduction to the Default SQL Projection” in the chapter “Introduction to Persistent Objects” of Using Caché Objects.

Note that a persistent class definition such as the one shown above creates the corresponding table when it is compiled, but this table definition cannot be modified or deleted using SQL DDL commands (or by using the Management Portal Drop action), which give you the message “DDL not enabled for class 'schema.name'...”). You must specify [DdlAllowed] in the table class definition to permit these operations:

Class MyApp.Person Extends %Persistent [DdlAllowed]

You can specify %Populate in the class definition to enable automatic populating of the table with test data.

Class MyApp.Person Extends (%Persistent,%Populate) [DdlAllowed]

This provides a Populate() method for the class. Running this method populates the table with ten rows of test data.

Defining Data Value Parameters

Every property (field) definition must specify a data type class, which specifies the class that the property is based on. A specified data type limits a field’s allowed data values to that data type. When defining a persistent class that projects to a table you must specify this data type using a class in the %Library package. This class can be specified as either %Library.Datatype or as %Datatype.

Many data type classes provide parameters that allow you to further define allowed data values. These parameters are specific to individual data types. The following are some of the more common data definition parameters:

Data Value Limits

For numeric data types, you can specify MAXVAL and MINVAL parameters to limit the range of allowed values. By definition, a numeric data type has a maximum supported values (positive and negative). You can use MAXVAL and MINVAL to further limit the allowed range.

For string data types, you can specify a MAXLEN and MINLEN parameters to limit the allowed length (in characters). By definition, a string data type has a maximum supported length. You can use MAXLEN and MINLEN to further limit the allowed range. By default, a data value that exceeds MAXLEN generates a field validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE. You can specify TRUNCATE=1 to permit string data values that exceed MAXLEN; the specified string is truncated to the MAXLEN length.

Permitted Data Values

You can limit the actual data values in two ways:

A list of allowed values (Enumerated Values with VALUELIST and DISPLAYLIST).
A match pattern for allowed values (PATTERN).

Enumerated Values

Defining a table as a persistent class allows you to define properties (fields) that can only contain certain specified values. This is done by specifying the VALUELIST parameter. VALUELIST (which specifies a list of logical storage values) is commonly used with DISPLAYLIST (which specifies a list of corresponding display values). Both lists begin with the list delimiter character. Several data types can specify VALUELIST and DISPLAYLIST. The following example defines two properties with enumerated values:

Class Sample.Students Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property ChoiceStr As %String(VALUELIST=",0,1,2",DISPLAYLIST=",NO,YES,MAYBE");
Property ChoiceODBCStr As %EnumString(VALUELIST=",0,1,2",DISPLAYLIST=",NO,YES,MAYBE");
}

If VALUELIST is specified, an INSERT or UPDATE can only specify one of the values listed in VALUELIST, or be provided with no value (NULL). VALUELIST valid values are case-sensitive. Specifying a data value that doesn’t match the VALUELIST values results in a field value failed validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE.

The %String and the %EnumString data types behave differently when displayed in ODBC mode. Using the example above, when displayed in Logical mode, both ChoiceStr and ChoiceODBCStr display their VALUELIST values. When displayed in Display mode, both ChoiceStr and ChoiceODBCStr display their DISPLAYLIST values. When displayed in ODBC mode, ChoiceStr displays VALUELIST values; ChoiceODBCStr displays DISPLAYLIST values.

Pattern Match for Values

Several data types can specify a PATTERN parameter. PATTERN restricts allowed data values to those that match the specified ObjectScript pattern, specified as a quoted string with the leading question mark omitted. The following example defines a property with a pattern:

Class Sample.Students Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property Telephone As %String(PATTERN = "3N1""-""3N1""-""4N");
}

Because a pattern is specified as a quoted string, literals specified in the pattern need to have their enclosing quotes doubled. Note that pattern matching is applied before MAXLEN and TRUNCATE. Therefore, if you are specifying a pattern for a string that may exceed MAXLEN and be truncated, you may wish to end the pattern with “.E” (an unlimited number of trailing characters of any type).

A data value that does not match PATTERN generates a field validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE.

Unique Values

CREATE TABLE allows you to define a field as UNIQUE. This means that every field value is a unique (non-duplicate) value.

Defining a table as a persistent class does not support a corresponding uniqueness property keyword. Instead, you must define both the property and a unique index on that property. The following example provides for a unique Num value for each record:

  Class Sample.CaveDwellers Extends %Persistent [ DdlAllowed ]
  { 
  Property Num As %Integer;
  Property Troglodyte As %String(MAXLEN=50);
  Index UniqueNumIdx On Num [ Type=index,Unique ];
  }

The index name follows the naming conventions for properties. The optional Type keyword specifies the index type. The Unique keyword defines the property (field) as unique.

Having a unique value field is necessary for using the INSERT OR UPDATE statement.

Computed Values

The following class definition example defines a table that includes a field (Birthday) that uses SqlComputed to compute its value when you initially set the DateOfBirth field value and SqlComputeOnChange to recompute its value when you update the DateOfBirth field value. The Birthday field value includes the current timestamp to record when this field value was computed/recomputed:

Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
  Property Name As %String(MAXLEN=50) [Required];
  Property DateOfBirth As %Date;
  Property Birthday As %String 
          [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DateOfBirth},9),",")_
                              " changed: "_$ZTIMESTAMP},
                              SqlComputed, SqlComputeOnChange = DateOfBirth ];
}

Note that an UPDATE to DateOfBirth that specifies the existing DateOfBirth value does not recompute the Birthday field value. For the corresponding SQL code, refer to the COMPUTECODE section of the CREATE TABLE reference page.

For reference material on class property keywords, refer to the “Property Keywords” chapter of Class Definition Reference.

Embedded Object (%SerialObject)

You can simplify the structure of a persistent table by referencing an embedded serial object class that defines properties. For example, you want the MyData.Person to contain address information, consisting of street, city, state, and postal code. Rather than specifying these properties in MyData.Person, you can define a serial object (%SerialObject) class that defines these properties, and then in MyData.Person specify a single Home property that references that embedded object. This is shown in the following class definitions:

Class MyData.Person Extends (%Persistent) [ DdlAllowed ]
{  Property Name As %String(MAXLEN=50);
   Property Home As MyData.Address;
   Property Age As %Integer;
}

Class MyData.Address Extends (%SerialObject)
{  Property Street As %String;
   Property City As %String;
   Property State As %String;
   Property PostalCode As %String;
 }

You cannot access the data in a serial object property directly, you must access them through a persistent class/table that references it:

To refer to an individual serial object property from the persistent table, use an underscore. For example, SELECT Name, Home_State FROM MyData.Person returns the State serial object property value as a string. Serial object property values are returned in the order specified in the query.
To refer to all of the serial object properties from the persistent table, specify the referencing field. For example, SELECT Home FROM MyData.Person returns values of all of the MyData.Address properties as a %List structure. Serial object property values are returned in the order specified in the serial object: Home_Street, Home_City, Home_State, Home_PostalCode. In the Management Portal SQL interface Catalog Details, this referencing field is referred to as a Container field. It is a Hidden field, and therefore not returned by SELECT * syntax.
A SELECT * for a persistent class returns all of the serial object properties individually, including nested serial objects. For example, SELECT * FROM MyData.Person returns Age, Name, Home_City, Home_PostalCode, Home_State, and Home_Street values (in that order); it does not return the Home %List structure value. Serial object property values are returned in collation sequence. SELECT * first lists all of the fields in the persistent class in collation sequence (commonly alphabetical order), followed by the nested serial object properties in collation sequence.

Note that an embedded serial object does not have to be in the same package as the persistent table that references it.

Defining embedded objects can simplify persistent table definitions:

A persistent table can contain multiple properties that reference different records in the same embedded object. For example, the MyData.Person table can contain a Home and an Office property, both of which reference the MyData.Address serial object class.
Multiple persistent tables can reference instances of the same embedded object. For example, the MyData.Person table Home property and the MyData.Employee WorkPlace property can both reference the MyData.Address serial object class.
An embedded object can reference another embedded object. For example, the MyData.Address embedded object contains the Phone property that references the MyData.Telephone embedded object, containing CountryCode, AreaCode, and PhoneNum properties. From the persistent class you use multiple underscores to refer to a nested serial object property, for example Home_Phone_AreaCode.

For further details, refer to Introduction to Serial Objects in Defining and Using Classes.

For information on creating an index for a serial object property, refer to Indexing an Embedded Object (%SerialObject) Property.

Class Methods

You can specify class methods as part of a table definition, as shown in the following example:

Class MyApp.Person Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
  {
   QUIT 123
  }
}

In a SELECT query you can invoke this method as follows:

SELECT Name,SSN,MyApp.Numbers() FROM MyApp.Person