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?
Comments
Hi.
Have you tried with $SEQUENCE or $INCREMENT?
Regards,
Matjaž
Yes, $I is the short of $Increment.
But from which global?
$Seq is a faster option of $Increment
so my
set newid=$I(newid)
part uses $Increment
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.
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.
dc.onetomany.PKG
Class dc.onetomany.products Extends %Persistent
{
Parameter ALLOWIDENTITYINSERT = 1;
Property name As %VarString [ SqlColumnNumber = 2 ];
Property listpriceusd As %BigInt [ SqlColumnNumber = 3 ];
Property description As %VarString [ SqlColumnNumber = 4 ];
}
Class dc.onetomany.sales Extends %Persistent
{
Parameter ALLOWIDENTITYINSERT = 1;
Property companyid As %BigInt [ SqlColumnNumber = 2 ];
Property productid As %BigInt [ SqlColumnNumber = 3 ];
Property saledate As %Date [ SqlColumnNumber = 4 ];
Property saleamountusd As %BigInt [ SqlColumnNumber = 5 ];
}
Class dc.onetomany.companies Extends %Persistent
{
Parameter ALLOWIDENTITYINSERT = 1;
Property name As %VarString [ SqlColumnNumber = 2 ];
Property industry As %VarString [ SqlColumnNumber = 3 ];
Property description As %VarString [ SqlColumnNumber = 4 ];
Property website As %VarString [ SqlColumnNumber = 5 ];
Query CompaniesBySales() As %SQLQuery(CONTAINID = 1, ROWSPEC = "id:%Integer,name:%String,industry:%String,description:%String,website:%String,totalsales:%Numeric") [ SqlName = CompaniesBySales, SqlProc ]
{
SELECT c.id,
c.name,
c.industry,
c.description,
c.website,
SUM(s.saleamountusd) AS totalsales
FROM dc_onetomany.sales s
JOIN dc_onetomany.companies c ON c.id = s.companyid
GROUP BY c.id, c.name, c.industry, c.description, c.website
ORDER BY totalsales DESC
}
Query CompanySalesTotal(companyid As %Integer) As %SQLQuery(CONTAINID = 1, ROWSPEC = "companyid:%Integer,totalsales:%Numeric") [ SqlName = CompanySalesTotal, SqlProc ]
{
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()
{
d ##class(dc.onetomany.sales).%KillExtent()
d ##class(dc.onetomany.companies).%KillExtent()
d ##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}}})
s rs = ##class(%ResultSet).%New("dc.onetomany.companies:CompanySalesTotal")
d rs.Execute(103)
d rs.Next()
w rs.Get("totalsales")," <- 17000",!
s rs = ##class(%ResultSet).%New("dc.onetomany.companies:CompanySalesTotal")
d rs.Execute(104)
d rs.Next()
w rs.Get("totalsales")," <- 7000",!
s c=##class(dc.onetomany.companies).%New()
s c.name="my name 111"
d c.%Save()
w "current ID = ",c.%Id(),!
&sql(insert into dc_onetomany.companies(ID,name) values(500,'my name 500'))
w "current ID = ",%ROWID,!
&sql(insert into dc_onetomany.companies(name) values('my name 501'))
w "current ID = ",%ROWID,!
}
}USER>d ##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?
Not quite so (see RowID Hidden?).
ALLOWIDENTITYINSERT is needed for other purposes:
I'm curious how and if the approach work if the id name in csv is diffent of "id", e.g.."CompanyId"?
Well, of course it works. See loads data from 'C:\data\sales.csv' above.
Ah, I see -
VALUES (saleId,companyId,productId,saleDate,saleAmountUSD)
Impressive indeed.
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>THat's good to know, thank you @Robert Cemper !
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.
Thank you, @Herman Slagman
This is how I'm tackling it now - setting it after importing data and using $I(myclassD) after.
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? )
Also published on OEX.
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
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.
Yes. But we can omit it here (set 0) for simplification. Still the issue is in IDs
USEEXTENTSET=1 is a default one now if you use DDL to create a class. Maybe for ObjectScript is a default as well.
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?
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?
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:
.png)
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:.png)
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?
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.
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
$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
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
Yes, this sounds safer.
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?
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")