Other Options for Persistent Classes
This topic describes other options that are available for persistent classes.
Defining a Sharded Class
If you are using sharding for horizontal scaling of data storage, you can define a sharded class by using the Sharded class keyword in the class definition. A sharded class is a persistent class where the data is spread among the data nodes of a sharded cluster, while the application accesses the data as if it were local.
Set the keyword Sharded = 1 to create a sharded class, as in the following example:
Class MyApp.Person Extends %Persistent [ Sharded = 1 ]
Until sharded classes are fully implemented, InterSystems recommends creating sharded tables from SQL, not from the object side.
For more information on sharding, see Horizontally Scaling InterSystems IRIS for Data Volume with Sharding.
Defining a Read-Only Class
It is possible to define a persistent class whose objects can be opened but not saved or deleted. To do this, specify the READONLY parameter for the class as 1:
Parameter READONLY = 1;
This is only useful for cases where you have objects that are mapped to preexisting storage (such as existing globals or an external database). If you call the %Save() method on a read-only object, it will always return an error code.
Using Columnar Storage
Columnar Storage is available as an Experimental Feature in release 2022.2. This means it is not supported for production environments and users may have to reload columnar table data when upgrading to a future release.
When you use persistent classes to store data in InterSystems IRIS, the data is typically stored in rows. This storage layout is appropriate in cases where you are doing online transaction processing, with frequent inserts, updates, and deletes. However, storing data in columns can be more appropriate for online analytical processing, where you are aggregating data in specific columns across the database, and real-time inserts, updates, and deletes are less common. For example, using when row storage, calculating the average of all values for a given property requires you to load all of the rows in the database. Columnar storage lets you calculate the average by loading only the column that contains the values for that property.
To use columnar storage for a class, set the parameter STORAGEDEFAULT = "columnar":
Class Sample.BankTransaction Extends %Persistent [ DdlAllowed, Final ]
{
Parameter STORAGEDEFAULT = "columnar";
.
.
.
}
Classes that use columnar as the default storage layout must specify either the Final class keyword or the NoExtent class keyword, with any immediate subclasses defined explicitly as Final.
When you are using transaction-based processing, but you have a few properties you frequently perform analytical queries on, you can use columnar storage for just those properties.
To use columnar storage for a single property in a class, set the parameter STORAGEDEFAULT = "columnar" on that property:
Class Sample.BankTransaction Extends %Persistent [ DdlAllowed ]
{
// Line below is optional
Parameter STORAGEDEFAULT = "row";
Property Amount As %Numeric(SCALE = 2, STORAGEDEFAULT = "columnar");
Index BitmapExtent [ Extent, Type = bitmap ];
.
.
.
}
You can use also use row storage for a class and create a columnar index for a property that is frequently queried.
To create a columnar index on a property in a class, use the keyword type = columnar on that index:
Class Sample.BankTransaction Extends %Persistent [ DdlAllowed ]
{
// Line below is optional
Parameter STORAGEDEFAULT = "row";
Property Amount As %Numeric(SCALE = 2);
Index AmountIndex On Amount [ type = columnar ];
.
.
.
}
For more information on indexes in general, see Adding Indexes, below.
For more information on columnar storage, see Choose an SQL Table Storage Layout in Defining a High-Performance Schema.
Adding Indexes
Indexes provide a mechanism for optimizing searches across the instances of a persistent class; they define a specific sorted subset of commonly requested data associated with a class. They are very helpful in reducing overhead for performance-critical searches.
Indexes automatically span the entire extent of the class in which they are defined. If a Person class has a subclass Student, all indexes defined in Person contain both Person objects and Student objects. Indexes defined in the Student class contain only Student objects.
Indexes can be sorted on one or more properties belonging to their class. This allows you a great deal of specific control of the order in which results are returned.
In addition, indexes can store additional data that is frequently requested by queries based on the sorted properties. By including additional data as part of an index, you can greatly enhance the performance of the query that uses the index; when the query uses the index to generate its result set, it can do so without accessing the main data storage facility. (See the Data keyword below.)
The only way to simultaneously add an index definition to the class and build the data in the index is by using an ALTER TABLE, CREATE INDEX, or DROP INDEX statement. When adding an index to a class definition, the index must be manually built. To read about building indexes manually, refer to Building Indexes.
For additional information on indexes, refer to Defining and Building Indexes, especially the section Properties That Can Be Indexed. Also see Index Definitions.
Adding Foreign Keys
To enforce referential integrity between tables you can define foreign keys in the corresponding persistent classes. When a table containing a foreign key constraint is modified, the foreign key constraints are checked. One way to add foreign keys is to add relationships between classes; see Defining and Using Relationships. You can also add explicit foreign keys to classes. For information, see Using Foreign Keys and Foreign Key Definitions.
Adding Triggers
Because InterSystems SQL supports the use of triggers, any trigger associated with a persistent class is included as part of the SQL projection of the class.
Triggers are code segments executed when specific events occur in InterSystems SQL. InterSystems IRIS® data platform supports triggers based on the execution of INSERT, UPDATE, and DELETE commands. The specified code will be executed either immediately before or immediately after the relevant command is executed, depending on the trigger definition. Each event can have multiple triggers as long as they are assigned an execution order.
If a trigger is defined with Foreach = row/object, then the trigger is also called at specific points during object access. See Triggers and Transactions.
Triggers are also fired by the persistence methods used by the legacy storage class, %Storage.SQL because it uses SQL statements internally to implement its persistent behavior.
For more information on triggers, see Triggers and Trigger Definitions.
Referring to Fields from ObjectScript
Within a class definition, there are several places that may include ObjectScript code used in SQL. For example, SQL computed field code and trigger code is executed from within SQL. In these cases, there is no concept of a current object, so it is not possible to use dot syntax to access or set data within a specific instance. Instead, you can access the same data as fields within the current row using field syntax.
To reference a specific field of the current row, use the {fieldname} syntax where fieldname is the name of the field.
For example, the following code checks if the salary of an employee is less than 50000:
If {Salary} < 50000 {
// actions here...
}
In UPDATE trigger code, {fieldname} denotes the updated field value. In DELETE trigger code, {fieldname} denotes the value of the field on disk.
To refer to the current field in a SQL computed field, use the {*} syntax.
For example, the following code might appear in the computed code for a Compensation field to compute its value based on the values of Salary and Commission fields:
Set {*} = {Salary} + {Commission}
For trigger-specific syntax, see Special Trigger Syntax.
Adding Row-Level Security
In addition to its general security, InterSystems IRIS provides SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. See Users and Roles for more information.
Typically, SQL security is controlled by granting SELECT privilege on a table or view to a user or role. The use of roles simplifies access control when the number of security roles is substantially fewer than the number of users. In most cases, view-level security provides adequate control over which rows each user can select; however, when the number of views required to achieve the desired control becomes very large, another alternative for fine-grained access control is needed.
For example, a hospital may make patient-specific data available online to each patient. Creating a separate view for each patient is not a practical alternative; instead, fine-grained access control, in conjunction with the InterSystems IRIS role-based authentication model, enables this type of application to be created efficiently and securely through row-level security.
The following are constraints on the use of row-level security:
-
Row-level security is only available for persistent classes.
-
Row-level security is only available for tables instantiated on the InterSystems IRIS server. It is not available for link tables (that is, those that are instantiated on foreign servers).
-
Row-level security is only enforced when accessing rows from SQL. It is not enforced when directly accessing globals or when accessing globals via the object interface.
Setting Up Row-Level Security
To enable row-level security for a table, edit the definition of the class from which the table is projected.
-
In the class definition code, set the value of ROWLEVELSECURITY to 1, such as:
ROWLEVELSECURITY = 1;
This definition for the parameter means that row-level security is active and that the class uses the generated %READERLIST property to store information about users and roles with authorized access to the row.
Alternatively, you can define the parameter as follows:
ROWLEVELSECURITY = rlsprop;
Where rlsprop is the name of a property in the same class. This alternative means that row-level security is active and that the class uses the given property to store information about users and roles with authorized access to the row. In this case, also add an index to the class as follows:
Index %RLI On rlsprop;
-
Define a %SecurityPolicy() class method, which determines and specifies the role and usernames that are permitted to select the row, subject to view and table SELECT privileges.
The structure of the %SecurityPolicy() method is:
ClassMethod %SecurityPolicy() As %String [ SqlProc ] { QUIT "" }
Its characteristics are:
-
It is a class method with the required name %SecurityPolicy.
-
It returns a string (type %String).
-
It takes zero or more arguments. If this method takes any arguments, each must match a property name in the class and they must all be distinct from each other.
-
The SqlProc keyword specifies that the method can be invoked as a stored procedure.
-
The QUIT statement of the method returns the users or roles that may view the row. If there is more than one user or role, QUIT must return a comma-separated list of their names. Returning the null string (as in the example) specifies that the row is visible to all users who hold the SELECT privilege on the table.
Important:A user who is assigned to the %All role does not automatically have access to rows in a table that are protected with row-level security. If %All is to have access to such a row, the %SecurityPolicy() method must explicitly specify this.
-
-
Compile the class and any dependent classes.
Adding Row-Level Security to a Table with Existing Data
To add row-level security to a table with existing data, first follow the procedure described in Setting Up Row-Level Security. Then:
-
Rebuild the indexes for the table.
-
Update the value of the property that lists the users and roles who can view each row.
Rebuilding the Indexes
Do not rebuild indexes while users are accessing the data for this table. Doing so may result in inaccurate query results.
The procedure to rebuild the indexes for a table is:
-
If the table has any views defined that have the WITH CHECK OPTION clause, remove these views with the DROP VIEW command. (You can re-create these views after updating who has access to each row).
-
From the Management Portal home page, go to the SQL page (System Explorer > SQL) page.
-
Select the namespace that contains the table.
-
Under Tables, select the name of the table. This displays the Catalog Details for the table.
-
On the Actions drop-down list, click Rebuild Table’s Indices.
For more information on rebuilding indexes, see Define and Build Indexes.
Updating Who Can View Each Row
The procedure to do this is:
-
From the Management Portal home page, go to the SQL page (System Explorer > SQL) page.
-
Select the namespace that contains the table.
-
Click Execute Query.
-
In the editable area, issue a statement to update the table. It should have the following form:
UPDATE MySchema.MyClass SET rlsprop = MySchema.SecurityPolicy(MySQLColumnName1, ...)
where
-
MySchema is the schema (package) containing the class.
-
MyClass is the name of the class.
-
rlsprop is the field containing the list of users and roles who can read the row. This is %READERLIST by default and the property name specified in the declaration of the ROWLEVELSECURITY parameter otherwise.
-
SecurityPolicy is value specified by the SqlName value in the definition of the %SecurityPolicy() method. If there is no explicit SQL name for the %SecurityPolicy() method and its class is MySchema.MyClass, then its default name is myClass_sys_SecurityPolicy (with a fully qualified form of MySchema.MyClass_sys_SecurityPolicy).
-
MySQLColumnName1, ... is the set of SQL column names corresponding to the arguments, if any, defined in the %SecurityPolicy() class method.
-
-
Click Execute.
-
If desired, re-create any view that you initially removed.
Performance Tips and Information
The %READERLIST property is a calculated field and its value is determined by the %SecurityPolicy() method. Whenever an INSERT or UPDATE occurs, %SecurityPolicy() is invoked for that row and populates the value of %READERLIST.
A collection index on the %READERLIST property is defined, and can be exploited by the query optimizer to minimize the performance impact when row-level security is enabled.
By default, when you set ROWLEVELSECURITY equal to 1, a collection index is defined for the %READERLIST property (column) because the security policy can, in general, return more than one comma-separated role or username. If your security policy never returns more than one user or role name, then you can override the ROWLEVELSECURITY parameter and explicitly define the %RLI index as an ordinary (non-collection) bitmap index. This generally provides optimal performance.
Security Tips and Information
Keep in mind the following security factors when using row-level security:
-
Row-level security operates in addition to table-level security. To execute a SELECT, INSERT, UPDATE, or DELETE statement, a user must have been granted both table-level access and row-level access for the relevant row.
-
User privileges are checked dynamically at runtime — when there is an attempt to execute an SQL command.
-
If you create an updateable view and specify WITH CHECK OPTION, then an INSERT operation on that view checks if the row to be inserted passes the WHERE clause that is specified in the view. Further, if you are creating the view from a table with row-level security, then the INSERT fails if either the WHERE clause of the view or the implicit row-level security predicate would cause that row to not be visible if you were to issue a command of SELECT * FROM on the view.
-
If you have access to a row, it is possible to change the value of the %READERLIST field (or whatever field holds the list of users and roles who can view the row). This means that you can perform an action that, directly or indirectly, removes your access to the row.
-
If you attempt to insert a row that would have violated a UNIQUE constraint if row-level security had not been defined, then it will still violate the constraint if row-level security is defined, regardless of whether the row causing the constraint failure is visible to the updating transaction.