Skip to main content

Defining a Table by Creating a Persistent Class

Defining a Table by Creating a Persistent Class

Although the standard way to define SQL tables is through using the CREATE TABLE DDL command, either from a SQL prompt or over a JDBC or ODBC connection, you can also create a persistent class table definition through an IDE such as VS Code or Studio. A class must either be defined as %Persistent or inherit from a superclass that is defined as %Persistent. When these classes are saved and compiled within the InterSystems IRIS database, they automatically project 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 
{
Parameter USEEXTENTSET = 1;

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

Index BitmapExtent [ Extent, Type = bitmap ];
}

The class has these characteristics:

  • When compiled, this definition creates the MyApp.Person persistent class and the corresponding SQL table, Person within the MyApp schema. For details on how to perform these operations, see Defining Classes.

  • The class definition includes a package name, MyApp. When defining a persistent class, an unspecified package name defaults to User. This name 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.

  • The persistent class name, Person, is used as the SQL table name. To supply a different SQL table name, you can use the SqlTableName class keyword.

  • The USEEXTENTSET class parameter is defined and set to 1. This parameter organizes table storage into a more efficient set of globals. As a best practice, specify the USEEXTENTSET parameter for all persistent classes that project to SQL tables.

  • The bitmap extent index creates an index of all IDs in the extent set. This settings makes counting and other operations more efficient. As a best practice, create a bitmap extent index for all persistent classes that project to SQL tables.

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

When defining a table using DDL commands, you do not need to specify USEEXTENTSET or create a bitmap extent index. InterSystems SQL applies these settings automatically and includes them in the projected persistent class.

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

To learn how the object view of the database corresponds to the relational view, see Introduction to the Default SQL Projection.

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]

Defining Property Parameters

When defining a table in a persistent class, the properties you define project to columns of the table. Every property definition must specify a data type class, which specifies the class that the property is based on. A specified data type limits a property’s allowed 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 property values. These parameters are specific to individual data types. The following are some of the more common data definition parameters:

When data is inserted or updated in a field, InterSystems SQL automatically validates the data, enforcing data type and referential integrity constraints. If you use other means to populate a table with data, you should validate the table data.

Property 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. The length limits imposed by the MAXLEN and MINLEN parameters are applied only when data is being stored in the database, as queries assume all data in the database is valid. Specifically, the limits are enforced when using INSERT or UPDATE (or %Save()Opens in a new tab in ObjectScript) to add or edit data in the database. By default, a property value that exceeds MAXLEN generates a validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE. You can specify TRUNCATE=1 to permit string values that exceed MAXLEN; the specified string is truncated to the MAXLEN length. The default maximum length of a string is 4096; this limit can be configured with the ODBCVarcharMaxlen field in the CPF.

Permitted Property Values

You can limit the actual property values in two ways:

  • A list of allowed values (Enumerated Values with VALUELIST and DISPLAYLIST).

  • A match pattern for allowed values (PATTERN).

Enumerated Property Values

Defining a table as a persistent class allows you to define properties (columns) 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 
{
Parameter USEEXTENTSET = 1;

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");

Index BitmapExtent [ Extent, Type = bitmap ];
}

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. For required properties, specifying a value that doesn’t match the VALUELIST values results in a validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE. For non-required properties, non-matching VALUELIST values are converted to NULL values.

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 Matching for Property Values

Several data types can specify a PATTERN parameter. PATTERN restricts allowed 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 
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property Telephone As %String(PATTERN = "3N1""-""3N1""-""4N");

Index BitmapExtent [ Extent, Type = bitmap ];
}

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 value that does not match PATTERN generates a validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE.

Unique Property Values

CREATE TABLE allows you to define a column 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 ]
  { 
  Parameter USEEXTENTSET = 1;

  Property Num As %Integer;
  Property Troglodyte As %String(MAXLEN=50);

  Index UniqueNumIdx On Num [ Type=index,Unique ];
  Index BitmapExtent [ Extent, Type = bitmap ];
  }

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

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

Computed Property Values

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

Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
  Parameter USEEXTENTSET = 1;

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

  Index BitmapExtent [ Extent, Type = bitmap ];
}

Note that an UPDATE to DateOfBirth that specifies the existing DateOfBirth value does not recompute the Birthday value.

The SqlComputeCode property keyword contains the ObjectScript code used to compute the value. Alternatively, you can specify the compute code in a PropertyComputation method, where Property is the name of the property you want to compute. This method enables you to specify the compute code in languages other than ObjectScript, such as Python.

In this class, the AgeComputation method computes the Age property based on the DOB (date of birth) property. The cols input argument is a %Library.PropertyHelperOpens in a new tab object. You can use the getfield method of this object to reference other properties.

Class Sample.MyStudents Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN = 50) [ Required ];
Property DOB As %Date;
Property Age As %Integer [ Calculated, SqlComputed, SqlComputeOnChange = DOB ];

Index BitmapExtent [ Extent, Type = bitmap ];

ClassMethod AgeComputation(cols As %Library.PropertyHelper) As %Integer
{
    set today = $zdate($horolog,8) 
    set bdate = $zdate(cols.getfield("DOB"), 8)
    return $select(bdate = "":"", 1:(today - bdate) \ 10000)
}

}
Class Sample.MyStudents Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN = 50) [ Required ];
Property DOB As %Date;
Property Age As %Integer [ Calculated, SqlComputed, SqlComputeOnChange = DOB ];

Index BitmapExtent [ Extent, Type = bitmap ];

ClassMethod AgeComputation(cols As %Library.PropertyHelper) As %Integer [ Language = python ]
{
    import datetime as d
    iris_date_offset = d.date(1840,12,31).toordinal()
    bdate = d.date.fromordinal(cols.getfield("DOB") + iris_date_offset).strftime("%Y%m%d")
    today = d.date.today().strftime("%Y%m%d")
    return str((int(today) - int(bdate)) // 10000) if bdate else ""
}

}

If ComputeLocalOnly is specified in addition to SqlComputed and SqlComputeCode, then the compute code is only executed when the data is stored on the same server that the query is issued from. This option is used primarily in sharded environments to return only locally-stored computed data.

For more details on specifying compute code, see Computed Columns.

For more details on class property keywords, see Property Keywords.

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 ]
{  Parameter USEEXTENTSET = 1;
   Property Name As %String(MAXLEN=50);
   Property Home As MyData.Address;
   Property Age As %Integer;
   Index BitmapExtent [ Extent, Type = bitmap ];
}
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. The SqlCategory for %Library.SerialObject (and all sub-classes of %SerialObject that do not define the SqlCategory explicitly) is STRING.

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.

Compiling a serial object class generate a data specification in the storage definition. The compiler assigns this specification a data name by appending the word “State” to the serial object class name. Therefore, MyData.Address is assigned <Data name="AddressState">. If this name (AddressState in this example) is already used as a property name, the compiler appends an integer to create a unique data name: <Data name="AddressState1">.

Refer to Introduction to Serial Objects.

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 
{
Parameter USEEXTENTSET = 1;
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);
Index BitmapExtent [ Extent, Type = bitmap ];
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
  {
   QUIT 123
  }
}

The SqlProc keyword is required to project the class method to a SQL procedure. In a SELECT query, you can call this method by using the defined SqlName value you defined for the method. For example:

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

Defining a Sharded Table by Creating a Persistent Class

Before you can define a persistent class that projects as a sharded table, you must establish a sharding environment. Then, to define a sharded persistent class, specify the class keyword Sharded=1, along with any optional shard-related class attributes.

Caution:

Define sharded tables only for new persistent classes that contain no data. Applying sharding to an existing class can make your data inaccessible.

This class defines a sample sharded persistent class with optional shard-related class attributes set:

Class Sample.MyShardT Extends %Persistent [ Sharded = 1 ]
{
    Parameter DEFAULTCONCURRENCY As BOOLEAN = 0;
    Parameter USEEXTENTSET = 1;
    Index BitmapExtent [ Extent, Type = bitmap ];
}

The following class attributes apply:

  • The Sharded = 1 keyword defines the projected table for this class as sharded. With this setting, the sharding infrastructure manages the sharded table storage, including data distribution. Therefore, you cannot customize the default storage definition generated and maintained by InterSystems IRIS. Any changes made to the storage definition in this class definition are ignored.

  • The DEFAULTCONCONCURRENCY class parameter is set to 0 (no locking). Due to the distributed nature of sharded tables, the parameter value of 0 is required. This value is used as the default in object methods such as %Open and %OpenId, so that you do not have to pass the concurrency argument to every method call.

  • The USEEXTENTSET class parameter is set to 1, which organizes table storage into a more efficient set of globals. When defining a sharded table using DDL commands, InterSystems SQL applies this setting automatically.

  • The bitmap extent index creates an index of all IDs in the extent set. This settings makes counting and other operations more efficient. When defining a sharded table using DDL commands, InterSystems SQL applies this setting automatically.

You can then define the shard key index. When you create a sharded table, an abstract shard key index is generated automatically. The shard key index determines the shard in which a row resides. For more details on defining a shard key index and creating sharded tables, see Creating Sharded Tables and Loading Data.

Sharded Class Restrictions

  • Class parameters that are not supported for sharded classes: CONNECTION, DEFAULTGLOBAL, DSINTERVAL, DSTIME, IDENTIFIEDBY, OBJJOURNAL.

  • Class keywords that are not supported for sharded classes: Language, ViewQuery.

  • Super classes that are not supported for sharded classes: %Library.IndexBuilder, %DocDB.Document.

  • Property data types not supported for sharded classes: %Library.Text.

  • Relationship properties are not supported for sharded classes.

  • Projections are not supported for sharded classes.

  • Any methods with a Language other than "objectscript" are not supported for sharded classes.

  • Any class queries not of Type %SQLQuery are not supported for sharded classes.

An attempt to compile a sharded class using any of these features results in a compile-time error.

FeedbackOpens in a new tab