Question
· Jan 4

What is the proper way to get a new ID for a new record for a self-maintained primary key aka idkey?

Hi Developers!

Sometimes we need to deal with classes/tables where the primary key and the IdKey are something that is maintained by yourself.

What is the proper way to generate a new ID in case where ID is a %BigInt?

Property id As %Library.BigInt

Are there any system methods to provide it?

There is data already imported via SQL, so there is no last ID stored in ^myclassD, so I cannot do $I(^myclassD).

Thinking of:

set newid=$O(^myclassD(""),-1),newid=$I(newid)

What do you think?

Product version: IRIS 2025.3
Discussion (34)4
Log in or sign up to continue

I'm surprised that   data is already imported via SQL but ^myclassD has no content.
So take a look into the related generated class:

Storage Default
{
<Data name="DirDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
- - - - - -
after all properties
</Data>
<DataLocation>^oex.DirD</DataLocation>
<DefaultData>DirDefaultData</DefaultData>
<ExtentSize>986</ExtentSize>
<IdLocation>^oex.DirD</IdLocation>
<IndexLocation>^oex.DirI</IndexLocation>

 <IdLocation> is the one to go for
with all the new Storage strategies (columnar, sharded, ...  )
This might be quite an exotic global name and rarely the traditional.

Id location is in place - and has Ids in the first index, like ^myclassD(1),^myclassD(2), etc.

But nothing is set in ^myclassD

Other parameters I have:

Property id As %Library.BigInt [ SqlColumnNumber = 14 ];

Parameter ALLOWIDENTITYINSERT = 1;

Index MYCLASSPKEY1 On id [ IdKey, PrimaryKey, SqlName = MYCLASSPKEY1, Unique ];

And I import IDs from the csv file.

Since you manage the ID yourself, in the general case it may not necessarily be a simple counter, but, for example, a Fibonacci sequence.
If the source code of the class or table is available, it is better to see how the ID is generated in it. If there is no source code, and you are sure that this is a simple counter, then in my opinion it would be easier to make a standard ID and let IRIS manage it itself.

 
dc.onetomany.PKG
USER>##class(dc.onetomany.companies).Test()
17000 <- 17000
7000 <- 7000
current ID = 111
current ID = 500
current ID = 501

Wow. Thanks @Vitaliy Serdtsev !

This is impressive. 

So if I understand properly, LOAD DATA guesses somehow that id is the idkey and primarykey here?

&sql(LOAD DATA FROM FILE 'C:\data\companies.csv'
  INTO dc_onetomany.companies(id,name,industry,description,website)
  USING {"from":{"file":{"header":true}}})

if AllowIdentityInsert =1?
 

You can get the required Global reference like this programmatically:

 ; get compiled class  with your classname
USER>set classname="oex.Dir"
USER>set dic=##class(%Dictionary.CompiledClass).%OpenId(classname)
 ; get relationship to CompiledStorage
USER>set stor=dic.Storages.GetAt(1)
 ; get name of the ID-Global
USER>Write stor.IdLocation
^oex.DirD
USER>

Thank you @Matjaz Murko  , @Robert Cemper , @Vitaliy Serdtsev @Herman Slagman .

Of course, I'd love to let IRIS maintain IDs.

But I don't see if it is possible at all if I need to export data into csv that is connected to each other e.g. via IDs.

I've created an example app of a "SuperSystems Enterprise" software company, that produces software products of different kinds, and there are companies that exist and buy these products from time to time.

So there are 3 entities in such a given system: products, companies, and sales. And sales entity contains IDs of companies and products they purchased along with date and sum.

Here is the code - if you build it in docker it will load the data from 3 csv files in /data folder: companies.csv, products.csv and sales.csv into related classes.

Notice that companies.cls and products.cls in this case maintain their own ID not an IRIS one as I don't know the other way it can be performed in IRIS so that data on sales connecting products and companies can be imported accurately to the history before reflected in csv. Other than way I chose here.

I suggest anyone who is interested in an exercise to fork the project and change the iris classes structure, or IDs, or ways of loading data, so that still the history of sales will be accurate - in order to check it easie,r I've provided a unittest that will check the sums of sales for 103 and 104 companies as :

USER>zpm

ipm:USER>test dc-onetomany-case

What do you think? )

As I expected, it's one of the new storage features that cause the irritation

/// Use hashed global names
Parameter USEEXTENTSET = 1

from docu  https://docs.intersystems.com/iris20253/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_storageglobals#GOBJ_storageglobals_hashed

When you set USEEXTENTSET to 1, each index is also assigned to a separate global, instead of using a single index global with different first subscripts. Again, this is done for increased performance.

Not explicitly mentioned - this also affects  IDKEY !

And the example presented shows in detail that 
IdLocation and DataLocation are NOT identical anymore, as it used to be for decades
 

Depending on what you really want to do, there are a couple of options. First of all, you can (and should perhaps) define a property as an IDENTITY. Setting that property directly requires certain privileges. 

If you don't really care about this property being used as the IDKEY, you can define a %Counter and define it as a Primary Key. It has been a while since I've used %Counter but I can come up with an example if this interests you.

Thanks, @Dan Pasco! My task is to understand how can I better deal in IRIS if I need to export data into csv that contains records that reference IDs.  So I be able to import this data back into IRIS (same or another) preserving all the existing linkages.

I posted an example app illustrating the task.

What I came up with so far is:

introducing my own index as IDKEY and PrimaryKey - that works well for importing data from csv containing IDs.

But if when I’m introducing new records then with ObjectScript it wants me to create new IDs. This is where I have questions - how do I do it in the best way. You say I can try %Counter instead of %BigInt?

With a system-assigned %ID value (the IDKEY is defined as "system assigned" internally), you cannot override the value as there is no settable property. The way to work around that restriction is to define a property as the IDENTITY. It has similar behavior in that its value is system assigned but you can also override that assignment by specifying the value yourself. You have to set a switch that allows you to do that but it is possible. This type of behavior is similar to using sequences in PostgreSQL where some number of inserts that specify the id are followed by an update of the sequence used for id auto-assignment to ensure uniqueness later. Of course, when using IRIS and you specify the identity value, you need to be certain that a future increment (uses $sequence most likely) won't produce a value that will not be unique. 

This is a simple example of a class using identity.

class com.intersystems.App extends %Library.Persistent {

property id as %Library.BigInt [ identity ];
property name as %String;
classmethod add(name as %String) as %Integer {
    set me = ..%New()
    set me.name = name
    do me.%Save()
    return me.%Id()
}
}

I then implemented a simple unit test:

method TestAddWithId() {
    set obj = ##class(com.intersystems.App).%New()
    set obj.name = "E2"
    set obj.id = 100
    try {
        $$$ThrowOnError(obj.%Save())
        set id = obj.%Id()
        do $$$AssertEquals(100, id, "TestAdd() - add 'E2' with Id = 100")
    } catch exc {
        do $$$AssertTrue(0, "TestAddWithId() - Failed, exception caught: "_exc.AsSQLMessage())
    }
}

And the result of executing that test is:

The solution to this problem is to turn on IDENTITY_INSERT. I updated the test to do that.

method TestAddWithId() {
    set old = $system.SQL.SetIdentityInsert(1)
    try {
        do $system.SQL.Execute("delete from com_intersystems.App")
        set obj = ##class(com.intersystems.App).%New()
        set obj.name = "E2"
        set obj.id = 100
        $$$ThrowOnError(obj.%Save())
        set id = obj.%Id()
        do $$$AssertEquals(100, id, "TestAdd() - add 'E2' with Id = 100")
    } catch exc {
        do $$$AssertTrue(0, "TestAddWithId() - Failed, exception caught: "_exc.AsSQLMessage())
    }
    do $system.SQL.SetIdentityInsert(old)
}

and now the test passes:

I don't know anything about ALLOWIDENTITYINSERT - maybe someone has added that since I was last involved with the compiler? 

But - as a general rule - you should not allow IDENTITYINSERT all of the time as it introduces a responsibility to keep the ID counter updated. When I first ran my test, it failed not because identity insert didn't work but because my counter wasn't updated to be 101 (I had inserted 100 with ID set).

UPDATE:

I researched this and found that while the documentation implies that you can define this parameter in any persistent class, that isn't the truth. The documentation appears to be wrong. It only works for classes using SQL Storage. It won't work for your case. Unless I misread the source code changes. I did look at %Persistent and that parameter is not defined by that class.

$system.SQL.SetIdentityInsert(1) is deprecated and replaced by $system..SQL.Util.SetOption(IdentityInsert ,1) 

from class docs:>>>
https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&CLASSNAME=%25SYSTEM.SQL.Util#METHOD_SetOption

IdentityInsert - Set the IDENTITY_INSERT option for this process. IDENTITY_INSERT
controls the ability of the user to specify a value for the IDENTITY property when
saving a new object, a value for the IDENTITY column, or an explicit ROWID value in an SQL INSERT.

Notes

  • Changing this configuration setting takes effect immediately and lasts for the duration of the process or until $SYSTEM.SQL.Util.SetOption("IsolationMode",pValue) is called again.
  • This is a per-process setting.

Sounds good to me 

No - IDENTITY is for both Objects/SQL. I don't know why the $system.OBJ function was refactored to place it into a SQL class but IdentityInsert is a feature we added quite some time ago to support either T/SQL or Informix. 

IDENTITY property/column is system assigned but can be explicitly set and the IDKEY is based on this property. This is the global produced by my "E2" test where I set the id property to 100.

^com.intersystems.AppD=102
^com.intersystems.AppD(100)=$lb("","E2")