Identity property - CacheSQLStorage
Hello,
We have recently encountered an issue which requires us to define a new identity field (named xMDA in the attached example), instead of using the default ID field.
We need to run an SQL query which unfortunately overrides the ID field (see attached image) but we still need to be able to access the ID field in said query.
We are however unable to get the attached solution to work. Is there a way to get around this issue?
Class TafnitCore.System.MDA.USER.caghoNybdWE1x0wt0DpyXVMhA5Yx2x2 Extends %Persistent [ ProcedureBlock, StorageStrategy = MainStorage ]
{Property xMDA As %Integer [ Identity ];
Property Subscript1 As %String;
Property Subscript2 As %String;
Property Data As %String(MAXLEN = 32000);
Property ZREF As %String [ Calculated, SqlComputeCode = { S {*} = ##class(TafnitCore.System.Utils.CacheUtils).getZREF("^[""USER""]ASORT",{ID})}, SqlComputed, Transient ];
Index PMKey On (Subscript1, Subscript2) [ IdKey, PrimaryKey, Unique ];
Storage MainStorage
{
<SQLMap name="MainMap">
<Data name="Data">
<Delimiter>"$c(31)"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^["USER"]ASORT</Global>
<Subscript name="1">
<Expression>{Subscript1}</Expression>
</Subscript>
<Subscript name="2">
<Expression>{Subscript2}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^["USER"]ASORT.S</StreamLocation>
<Type>%CacheSQLStorage</Type>
} }
Comments
You can give the row ID a different SQL-visible name, but that does not replace the underlying object row ID used by the storage map. If your goal is mainly to avoid the default ID name colliding in SQL, use the class keyword SqlRowIdName to rename the SQL rowid column to something meaningful for your application. [1]
If you want a named IDKEY, there are two supported patterns: [1]
-
Property SomeId As %Integer [ Identity ];
This behaves like the default IDKEY: it is automatically generated and not settable. [1] -
Property SomeId As %Integer;withIndex ... On SomeId [ IdKey ];
This makes that property the object identity value, but you must set it yourself. Also, once saved, properties participating in anIdKeycannot be modified. [1]
In your example, xMDA [ Identity ] only defines an SQL identity column; it does not make xMDA the storage/object identity unless it is also the IdKey. Your current IdKey is PMKey On (Subscript1, Subscript2), so the object identity is based on those properties, not on xMDA. [1]
If you need to reference the actual row ID inside SqlComputeCode, use {%%ID}. That is the documented way to access the identity/row ID value in SQL compute code. [2]
So for your calculated property, instead of referencing {ID}, use {%%ID} in the compute expression. [2]
Your current design also means that ID is the system/internal row address, while a PrimaryKey is primarily for SQL/schema purposes. In Caché/IRIS, the %ID/IDKEY is what the system uses internally to identify and locate the row, while the primary key is what the application/schema exposes as the table key. These can be different. [3]
A practical way around your collision, therefore, is: [1][2][3]
- keep the internal row identity as
%ID - access it in computed SQL code using
{%%ID} - if needed, rename the SQL-visible rowid column with
SqlRowIdNameso your query can use another name instead ofID[1][2]
If the intention is to make xMDA itself be the true row/object identity, then it must participate as the IdKey; merely marking it [Identity] is not enough. [1]
Sources: