Written by

Senior Startups and Community Programs Manager at InterSystems Corporation
Question Evgeny Shvarov · 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

Comments

Matjaz Murko · Jan 4

Hi.

Have you tried with $SEQUENCE or $INCREMENT?

Regards,
Matjaž

0
Evgeny Shvarov  Jan 4 to Matjaz Murko

Yes, $I is the short of $Increment.

But from which global?

$Seq is a faster option of $Increment

0
Evgeny Shvarov  Jan 4 to Matjaz Murko

so my 

set newid=$I(newid)

part uses $Increment

0
Robert Cemper · Jan 4

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.

0
Evgeny Shvarov  Jan 4 to Robert Cemper

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.

0
Vitaliy Serdtsev  Jan 5 to Evgeny Shvarov

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.

0
Evgeny Shvarov  Jan 5 to Vitaliy Serdtsev

I wish you use IRIS ID maintanence - but don't see how is it possible in this way. Published an example app - maybe you can make it. more details in the comment below.

0
Vitaliy Serdtsev  Jan 6 to Evgeny Shvarov
 

dc.onetomany.PKG

Class dc.onetomany.products Extends %Persistent
{
Parameter ALLOWIDENTITYINSERT = 1;
Property name As %VarString SqlColumnNumber ];
Property listpriceusd As %BigInt SqlColumnNumber ];
Property description As %VarString SqlColumnNumber ];
}

Class dc.onetomany.sales Extends %Persistent
{
Parameter ALLOWIDENTITYINSERT = 1;
Property companyid As %BigInt SqlColumnNumber ];
Property productid As %BigInt SqlColumnNumber ];
Property saledate As %Date SqlColumnNumber ];
Property saleamountusd As %BigInt SqlColumnNumber ];
}

Class dc.onetomany.companies Extends %Persistent
{
Parameter ALLOWIDENTITYINSERT = 1;
Property name As %VarString SqlColumnNumber ];
Property industry As %VarString SqlColumnNumber ];
Property description As %VarString SqlColumnNumber ];
Property website As %VarString SqlColumnNumber ];

Query CompaniesBySales() As %SQLQuery(CONTAINID 1ROWSPEC "id:%Integer,name:%String,industry:%String,description:%String,website:%String,totalsales:%Numeric") [ SqlName CompaniesBySalesSqlProc ]
{
SELECT c.id,
       c.name,
       c.industry,
       c.description,
       c.website,
       SUM(s.saleamountusdAS totalsales
FROM dc_onetomany.sales s
JOIN dc_onetomany.companies c ON c.id s.companyid
GROUP BY c.idc.namec.industryc.descriptionc.website
ORDER BY totalsales DESC
}

Query CompanySalesTotal(companyid As %IntegerAs %SQLQuery(CONTAINID 1ROWSPEC "companyid:%Integer,totalsales:%Numeric") [ SqlName CompanySalesTotalSqlProc ]
{
SELECT :companyid AS companyid,
       COALESCE(SUM(s.saleamountusd), 0) AS totalsales
FROM dc_onetomany.sales s
WHERE s.companyid :companyid
}

/// d ##class(dc.onetomany.companies).Test()
ClassMethod Test()
{
  ##class(dc.onetomany.sales).%KillExtent()
  ##class(dc.onetomany.companies).%KillExtent()
  ##class(dc.onetomany.products).%KillExtent()
  
  &sql(LOAD DATA FROM FILE 'C:\data\companies.csv'
  INTO dc_onetomany.companies(id,name,industry,description,website)
  USING {"from":{"file":{"header":true}}})
  
  &sql(LOAD DATA FROM FILE 'C:\data\products.csv'
  INTO dc_onetomany.products(id,name,listPriceUSD,description)
  USING {"from":{"file":{"header":true}}})
  
  &sql(LOAD DATA FROM FILE 'C:\data\sales.csv'
  INTO dc_onetomany.sales(ID,companyId,productId,saleDate,saleAmountUSD)
  VALUES (saleId,companyId,productId,saleDate,saleAmountUSD)
  USING {"from":{"file":{"header":true}}})
  
  rs ##class(%ResultSet).%New("dc.onetomany.companies:CompanySalesTotal")
  rs.Execute(103)
  rs.Next()
  rs.Get("totalsales")," <- 17000",!
  rs ##class(%ResultSet).%New("dc.onetomany.companies:CompanySalesTotal")
  rs.Execute(104)
  rs.Next()
  rs.Get("totalsales")," <- 7000",!
  
  c=##class(dc.onetomany.companies).%New()
  c.name="my name 111"
  c.%Save()
  "current ID = ",c.%Id(),!
  
  &sql(insert into dc_onetomany.companies(ID,namevalues(500,'my name 500'))
  "current ID = ",%ROWID,!
  &sql(insert into dc_onetomany.companies(namevalues('my name 501'))
  "current ID = ",%ROWID,!
}

}
USER>##class(dc.onetomany.companies).Test()
17000 <- 17000
7000 <- 7000
current ID = 111
current ID = 500
current ID = 501
0
Evgeny Shvarov  Jan 6 to Vitaliy Serdtsev

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?
 

0
Evgeny Shvarov  Jan 6 to Vitaliy Serdtsev

I'm curious how and if the approach work if the id name in csv is diffent of "id", e.g.."CompanyId"?

0
Vitaliy Serdtsev  Jan 6 to Evgeny Shvarov

Well, of course it works. See loads data from 'C:\data\sales.csv' above.

0
Evgeny Shvarov  Jan 6 to Vitaliy Serdtsev

Ah, I see - 

  VALUES (saleId,companyId,productId,saleDate,saleAmountUSD)

Impressive indeed.

0
Robert Cemper  Jan 5 to Evgeny Shvarov

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>
0
Herman Slagman · Jan 5

Can't you set ^myClassD once? Set ^myClassD=$Order(^myClassD(""),-1)
And from then use $Increment(^myClassD). The advantage of using this is that the operation is atomic, even in a mirror.

0
Evgeny Shvarov  Jan 5 to Herman Slagman

Thank you, @Herman Slagman 
This is how I'm tackling it now - setting it after importing data and using $I(myclassD) after.

0
Evgeny Shvarov · Jan 5

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? )

0
Robert Cemper  Jan 5 to Evgeny Shvarov

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
 

0
Robert Cemper  Jan 5 to Robert Cemper

to be clear: 
USEEXTENTSET is great for very large tables where the top (with the ID) is many 
storage levels away from data blocks.
It's up to the dimension of your project if the eventual gain in speed pays off at all.
for small tables as we know from our samples, I doubt. 

0
Evgeny Shvarov  Jan 5 to Robert Cemper

Yes. But we can omit it here (set 0) for simplification. Still the issue is in IDs

0
Evgeny Shvarov  Jan 5 to Robert Cemper

USEEXTENTSET=1 is a default one now if you use DDL to create a class. Maybe for ObjectScript is a default as well.

0
Dan Pasco · Jan 5

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.

0
Evgeny Shvarov  Jan 5 to Dan Pasco

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?

0
Evgeny Shvarov  Jan 5 to Dan Pasco

define a property as IDENTITY

I’m defining an index on an property id:

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

Is it enough?

0
Dan Pasco  Jan 5 to Evgeny Shvarov

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:

0
Evgeny Shvarov  Jan 5 to Dan Pasco

Thanks Dan!

I’m having these property in my class that allows inserting ID values via SQL:

Parameter ALLOWIDENTITYINSERT = 1;

is it what you are talking about?

0
Dan Pasco  Jan 5 to Evgeny Shvarov

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.

0
Evgeny Shvarov  Jan 6 to Dan Pasco

Thanks @Dan Pasco !

So this $system.SQL.SetIdentityInsert(1) is a system-wide setting, right?

It looks like there is an option to have it as a parameter for a class as well, like @Vitaliy Serdtsev shared above  

0
Robert Cemper  Jan 6 to Evgeny Shvarov

$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 

0
Evgeny Shvarov  Jan 7 to Dan Pasco

I'm still curious what does [identity] do here?

property id as %Library.BigInt [ identity ];

is it only for "Object" access and not for SQL?

0
Dan Pasco  Jan 7 to Evgeny Shvarov

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")

0