In InterSystems IRIS, when you compile a persistent class, you automatically get a SQL table. Sometimes, there are situations that require having a slightly (or not so slightly) different names/options in SQL compared to Object model. Here are some of the settings that you can change to make it happen.
Class-level settings
They define the "Face" of your class when it appears as a table in the SQL catalog.
SqlTableName allows you to change the name of SQL table that holds objects from your class. This is especially useful, when your class name uses reserved SQL words in a name, e.g. Group.
Usage:
Class Banking.Group Extends %Persistent [ SqlTableName = Banking_Group ]
{
}
SqlRowIdName allows you to rename the default primary key column to something semantically meaningful. Every persistent class has a unique identifier, and by default it's called ID. Naming it PatientID or OrderID makes it much more readable and prevents naming collisions in complex queries.
Usage:
Class Banking.Account Extends %Persistent [ SqlRowIdName = AccID ]
{
}
DdlAllowed allows or prevents the table schema from being changed using SQL. For example, if you are a "Class-first" developer, you don't want a DBA accidentally running ALTER TABLE and desyncing the class definition. Setting this to Not DdlAllowed (default) ensures the Class is the "source of truth." Setting it to DdlAllowed allows standard SQL DDL commands to modify the class.
Usage:
Class Banking.Transaction Extends %Persistent [ DdlAllowed ]
{
}
Class Banking.Account Extends %Persistent [ Not DdlAllowed ]
{
}
Property-level settings
These parameters change how individual data points (properties) are projected as columns.
SqlFieldName allows to provide a different column name than the property name.
Usage:
Property CurrentAmount As %Float [ SqlFieldName = Amount ];
SqlListType and SqlListDelimiter allow to define how "List" collections are stored and viewed in SQL. Since SQL is inherently "flat," it struggles with lists. The goal of these settings is to set the storage method that matches your performance needs for "Unnesting" the data in queries. There are several possible values for the SqlListType:
SUBNODE: Stores list items in a separate global, which is more efficient for large lists (like a default for an array).
DELIMITED: Stores items as a single string with delimiters set in SqlListDelimiter
LIST: Stored items in $List() format in memory and on disk. This is the default.
Usage:
Property Currencies As list Of %String [ SqlListDelimiter = ";", SqlListType = DELIMITED ];
There are several more settings you can configure to change how your SQL data is represented compared to the object model. Go through the documentation to uncver them all!
.png)
.png)
.png)
.png)
.png)