Storage and SQL Projection of Collection Properties

Default Storage and Projection of List Properties

By default, a list property is stored in the same table as the object it belongs to, and is projected to SQL as a single column consisting of a %ListOpens in a new tab structure that contains multiple values. To find an item in this column, use the FOR SOME %ELEMENT predicate. For example, this query returns the rows in which the FavoriteColors column, which is a projection of the FavoriteColors list property, contains the element 'Red'.

SELECT * FROM Sample.Person 
WHERE FOR SOME %ELEMENT (FavoriteColors) (%VALUE = 'Red')

Alternatively, you can use the %INLIST predicate to either find an element in a list or find a list that does not contain a certain element. For example, this query returns the rows in which the FavoriteColors column does not contain the list element 'Red'.

SELECT * FROM Sample.Person 
WHERE NOT ('Red' %INLIST (FavoriteColors))

If the list for a particular instance contains no elements, it is projected as an empty string (and not an SQL NULL value).

Default Storage and Projection of Array Properties

By default, an array property is stored in a child table and is projected as that child table. The child table is in the same package as the parent table. The name of this child table is as follows:

tablename_fieldname

where:

• tablename is the SqlTableName of the parent class (if specified) or the short name of the parent class (if SqlTableName is not specified).

• fieldname is the SqlFieldName of the array property (if specified) or the name of the array property (if SqlFieldName is not specified).

For example, consider a Person class with an array property called Siblings. The projection of the Siblings property is a child table called Person_Siblings.

The child table contains these columns:

1. A column containing the IDs of the corresponding instance of the parent class. This column acts as a foreign key to the parent class containing the array and is named after that class. In the projected Person_Child table, this column is named Person.

2. A column named element_key that contains the identifier for each array member.

3. A column containing the value of each array member. This column is named after the array property. In the projected Person_Child table, this column is named Siblings.

This table shows sample entries and the generated column names of the Siblings child table.

If an instance of the parent class holds an empty collection (one that contains no elements), the ID for that instance does not appear in the child table, such as the instance above where ID equals 11.

Notice that there is no Siblings column in the parent table.

For the column(s) containing the array members, the number and contents of the column(s) depend on the kind of array:

• The projection of an array of data type properties is a single column of data.

• The projection of an array of reference properties is a single column of object references.

• The projection of an array of embedded objects is as multiple columns in the child table. The structure of these columns is described in Embedded Object Properties.

Together, the ID of each instance and the identifier of each array member comprise a unique index for the child table. Also, if a parent instance has no array associated with it, it has no associated entries in the child table.

Controlling Storage of Collection Properties

You can store a list property as a child table, and you can store an array property as a $LIST. In both cases, you specify the STORAGEDEFAULT parameter of the property:

• For a list property, STORAGEDEFAULT is "list" by default. If you specify STORAGEDEFAULT as "array", then the property is stored and projected as a child table. For example:

  Property MyList as list of %String (STORAGEDEFAULT="array");
  

  

  For details on the resulting projection, see Default Storage and Projection of Array Properties.

• For an array property, STORAGEDEFAULT is "array" by default. If you specify STORAGEDEFAULT as "list", then the property is stored and projected as a list. For example:

  Property MyArray as array of %String (STORAGEDEFAULT="list");
  

  

  For details on the resulting projection, see Default Storage and Projection of List Properties.

Controlling the SQL Projection

However a collection property is actually stored, the property can be projected as a column in the parent table, as a child table, or in both ways (as of release 2022.1, this is true for both list and array properties). To control this, you specify the SQLPROJECTION parameter of the property. This parameter can have any of the following values:

• "column" — project this property as a column.

• "table" — project this property as a child table.

• "table/column" — project this property as a column and a child table.

For example, consider the following class definition:

Class Sample.Sample Extends %Persistent
{

Property Property1 As %String;

Property Property2 as array of %String(SQLPROJECTION = "table/column");

}

In this case, the system generates two tables for this class: Sample.Sample and Sample.Sample_Property2

The table Sample.Sample_Property2 stores the data for the array property Property2, as in the default scenario. Unlike the default scenario, however, a query can refer to the Property2 field in the Sample.Sample table. For example:

MYNAMESPACE>>SELECT Property2 FROM Sample.Sample where ID=7
13.     SELECT Property2 FROM Sample.Sample where ID=7
 
Property2
"1     value 12       value 23       value 3"

The SELECT * query, however, does not return the Property2 field:

MYNAMESPACE>>SELECT * FROM Sample.Sample where ID=7
14.     SELECT * FROM Sample.Sample where ID=7
 
ID      Property1
7       abc

Controlling the Name of the Projected Child Table

If a collection property is projected as a child table, you can control the name of that table. To do so, specify the SQLTABLENAME parameter of the property. For example:

Property MyArray as array of %String(SQLTABLENAME = "MyArrayTable");

Property MyList as list of %Integer(SQLTABLENAME = "MyListTable", STORAGEDEFAULT = "array");

The SQLTABLENAME parameter has no effect unless the property is projected as a child table.

See Also

• Introduction to the Default SQL Projection

• Working with Collections