Separate list results of persistent classes from SQL

Question

Question

Lucas Macori · May 7, 2021

Hello everyone,

We are trying out lists inside persistent classes. Because we may need to use them in a very close future.

So far, I managed to create a class that has a list property which has an indice.

Here is the class in question:

Class User.TestList.Data.Titre Extends (%Persistent, %Populate)
{
Property numTitre As %Integer;

Property millesime As %Integer;

Property codeProduit As %String;

/// Old field which will be replaced by the next one
Property numDossierMER As %Integer;

/// New field which is a list
Property numDossiersMER As list Of %Integer;

Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ];

/// Old index
Index numDossierMERIdx On numDossierMER;

/// New index
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

}

I also managed to query the table depending on values of the list with the following query structure:

SELECT
ID, codeProduit, millesime, numDossierMER, numDossiersMER, numTitre
FROM User_TestList_Data.Titre
WHERE FOR SOME %ELEMENT(numDossiersMER) (%VALUE IN (24, 662703520))

Altough, one question remained unanswered so far:

How can I fetch results from the table from SQL and split all elements of the list into separate lines ?

For example, let's say I only have two lines in my table.

If I query the table with a simple SELECT * FROM User.TestList.Data.Titre. The results will be as follows:

numTitre	millesime	codeProduit	numDossierMER	numDossiersMER (list)
1	2021	X	1	1 2 3
2	2021	X	4	4 5 6

How can I get the results to be formatted like this instead ?

numTitre	millesime	codeProduit	numDossierMER	numDossiersMER (list)
1	2021	X	1	1
1	2021	X	1	2
1	2021	X	1	3
2	2021	X	4	4
2	2021	X	4	5
2	2021	X	4	6

Thank you

Lucas

Product version: Ensemble 2017.1

$ZV: Cache for Windows (x86-64) 2017.2.1 (Build 801U) Wed Dec 6 2017 09:07:51 EST

Discussion (8)1

Log in or sign up to continue

Julius Kavay · May 7, 2021

There are two solutions, either you use the property numDossiersMER as array instead of list, as suggested by David Hockenbroch, or in case when existing application use list methods like insert and FOR-loops to acces list elements, then you can change this property to a kind of list-table property (see below).

Either of the above gives you the possibility to use queries like:

select Titre->ID, Titre->numTitre, Titre->millesime, Titre->codeProduit, Titre->numDossierMer, numDossiersMER
from User_TestList_Data.Titre_numDossiersMER
where numDossiersMER in (123, 234, 345)

The following guidance is based on the fact that Cache/IRIS uses the so called "schema evolution" in class storage, see also: https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=GOBJ_d...

I use to say list-table property if in a class definition a property shows up as

Property PropName As list of WhateverDataType;

but the SQL-projection is array-like

Property PropName As array Of WhateverDataType;

The steps to create a list-table property depends on the state of your project:

a) You not yet have any data (or the data you have can be deleted):

a1) Delete the possibly existing data

a2) Delete the storage definition (Studio-->Inspector-->Storage-->RightClick-->Delete)

a3) Change the property definition to array:

Property numDossiersMER As array of %Integer;

a4) Compile the class

a5) Change the property definotion to list:

Property numDossiersMER As list Of %Integer;

a6) Compile the class

Voila, you got a list-table property:

do obj.MyProp.Insert(data) to add data items
and query property data as it would be a table: select * from class.name_MyProp

b) You want to keep your data and you want to retain the property name numDossiersMER (because you don't want to change existing applications). Before proceeding, make a backup of your class globals, then:

b1) Rename the existing property and then add it again as a new array property:

from: Property numDossiersMER as list of %Integer
to  : Property OLDnumDossiersMER as list of %Integer

change the property name in the storage definition too

from:  <Value>numDossiersMER</Value>
to  :  <Value>OLDnumDossiersMEROLD</Value>

then add the new property as array

Property numDossiersMER as array of %Integer;

b2) Compile the class

b3) Change the property's collection from array to list

Property numDossiersMER as list of %Integer;

b4) Compile the class

b5) Transfer the list data from old storage to the new and potentially delete the old list data

set id=0
for  {set id=$order(^User.TestList.Data.TitreD(id)) quit:'id
        set obj=##class(User.TestList.Data.Titre).%OpenId(id)
        if 'obj write id,"  ??",! continue
        for i=1:1:obj.OLDnumDossiersMER.Count() do obj.numDossiersMER.Insert(obj.OLDnumDossiersMER.GetAt(i)
       // obj.OLDnumDossiersMER.Clear()
      do obj.%Save()
}

or you use an SQL statement instead of $order(...)

b6) Rebuild the indexes.

c) You want to keep your data and you want to have a new property name too. Again, before proceeding, make a backup of your class globals, then:

c1) Add the new property as an array

Property numNewDossiersMER As array Of %Integer;

c2) Compile the class

c3) Change the new property collection from array to list

Property numNewDossiersMER As list Of %Integer;

c4) Compile the class

c5) Transfer the list data from numDossiersMER to numNewDossiersMER according to b5)

It's IMPORTANT to follow the above steps in the given sequence!

Just to keep things complete, the other way around (array items stored as list items) is also possible. You have just to swap the definition sequence: define as list, compile, redefine as array, compile.

4 0

Guillaume Rongier · May 18, 2021

Hi Lucas,

A simple solution to you question can be this :

Property numDossiersMER As list Of %Integer(SQLPROJECTION = "table/column", STORAGEDEFAULT = "array");

Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

With those parameters you can achieve :

'As list Of %Integer' allows you to use the Insert() in ObjectScript and 'for some %element' in SQL command
'SQLPROJECTION = "table/column"' allows you to display the table as a column (note, the column does not appear in a select * it must be specified : select numDossierMER, numDossiersMER from User_TestList_Data.Titre )
'STORAGEDEFAULT = "array"' allows a separate table for the normalized representation
'Index numDossiersMERIdx On numDossiersMER(ELEMENTS);' bring the ability to use index on values with this SQL query :

select numDossierMER, numDossiersMER from User_TestList_Data.Titre
where
for some %element(numDossiersMER) (%Value in (345))

1 0

score 1 · Answer 1 · 2021-05-07T14:25:00-04:00

If that's what you want to do, you may want to consider using an array, not a list. By default, arrays are projected as a child table for SQL. You can find more details on the differences in the Working with Collections article, and the part I'm referring to specifically is the Default Projection of Array Properties section.

You could also change the storage default parameter of your list to "array", which is also described in the above article.

score 0 · Answer 2 · 2021-05-11T09:30:43-04:00

Thank you for your answer Julius Kavay.

I'm using your solution.
But unfortunately, I'm facing another problem. The indexes are not working.

Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

Index numDossiersMERKeyOn numDossiersMER(KEYS);

I added both lines to my class, but it's not used by IS.
How can I put an index on numDossiersMER elements and use it ?

Thank you

score 1 · Answer 3 · 2021-05-12T06:38:44-04:00

There is a keyword %NOINDEX indexname1, indexname2, ... to prevent the SQL-Enginne to use specific indices but there is no keyword for the opposite, something like %USEINDEX indexname, sadly.

Maybe someone with more SQL experience knows what is preventing the SQL engine to use the existing index over the numDossiersMER property...

But, and this is the great thing with IRIS and Cache, if everything else fails, you can always create your custom query.

Class User.TestList.Data.Titre Extends (%Persistent, %Populate)
{
Property numTitre As %Integer;
Property millesime As %Integer;
Property codeProduit As %String;
/// Old field which will be replaced by the next one
Property numDossierMER As %Integer;
Property numDossiersMER As list Of %Integer;
Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ];
/// Old index
Index numDossierMERIdx On numDossierMER;
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

Query Select(num...) As %Query(CONTAINID = 1, ROWSPEC = "ID:%Integer,Dossier:%Integer,codeProd:%String") [ SqlProc ]
{
}

ClassMethod SelectExecute(par As %Binary, num...) As %Status
{
   kill par, ^||tmpSelectQry
   for i=1:1:$g(num) set nr=$g(num(i)) merge:nr]"" ^||tmpSelectQry(nr)=^User.TestList.Data.TitreI("numDossiersMERIdx",nr)
   set par=$na(^||tmpSelectQry)
   quit $$$OK
}

ClassMethod SelectFetch(par As %Binary, row As %List, end As %Integer) As %Status
{
   set par=$query(@par)
   if par="" { set end=1, row="" }
   else {
      set end=0, id=$qs(par,2)
      set row=$lb(id, $qs(par,1), ..codeProduitGetStored(id)) // and other fields...
   }
   quit $$$OK
}

ClassMethod SelectClose(par As %Binary) As %Status
{
   kill par, ^||tmpSelectQry
   quit $$$OK
}

ClassMethod Test()
{
   write "Using a ResultSet...",!
   set rs=##class(%ResultSet).%New("User.TestList.Data.Titre:Select")
   if rs.Execute(230,3590,40110,507550,6094,70071,820096,9380148,8,592) {
   set t=$zh
   while rs.Next() { write rs.Data("ID"),?10,rs.Data("Dossier"),?30,rs.Data("codeProd"),! }
   }
   write "Time: ",$zh-t*1E3,!!
   write "Direct usage of the query methods...",!
   do ..SelectExecute(.par,230,3590,40110,507550,6094,70071,820096,9380148,8,592)
   set t=$zh
   for  do ..SelectFetch(.par,.row,.end) quit:end  zwrite row
   write "Time: ",$zh-t*1E3,!
}

Storage Default
{
<Data name="TitreDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>numTitre</Value>
</Value>
<Value name="3">
<Value>millesime</Value>
</Value>
<Value name="4">
<Value>codeProduit</Value>
</Value>
<Value name="5">
<Value>numDossierMER</Value>
</Value>
</Data>
<Data name="numDossiersMER">
<Attribute>numDossiersMER</Attribute>
<Structure>subnode</Structure>
<Subscript>"numDossiersMER"</Subscript>
</Data>
<DataLocation>^User.TestList.Data.TitreD</DataLocation>
<DefaultData>TitreDefaultData</DefaultData>
<ExtentSize>1000000</ExtentSize>
<IdLocation>^User.TestList.Data.TitreD</IdLocation>
<IndexLocation>^User.TestList.Data.TitreI</IndexLocation>
<Property name="%%CLASSNAME">
<AverageFieldSize>1</AverageFieldSize>
<Selectivity>100.0000%</Selectivity>
</Property>
<Property name="%%ID">
<AverageFieldSize>5.88</AverageFieldSize>
<Selectivity>1</Selectivity>
</Property>
<Property name="codeProduit">
<AverageFieldSize>4.89</AverageFieldSize>
<Selectivity>0.0004%</Selectivity>
</Property>
<Property name="millesime">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<Property name="numDossierMER">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<Property name="numTitre">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<SQLMap name="IDKEY">
<BlockCount>-63088</BlockCount>
</SQLMap>
<SQLMap name="numDossierMERIdx">
<BlockCount>-7912</BlockCount>
</SQLMap>
<SQLMap name="titreIdx">
<BlockCount>-19940</BlockCount>
</SQLMap>
<StreamLocation>^User.TestList.Data.TitreS</StreamLocation>
<Type>%Storage.Persistent</Type>
} }

Some examples after do ##class(..).Poulate(1E6)

USER>d ##class(User.TestList.Data.Titre).Test()
Using a ResultSet...
700556    8                   R7369
696384    230                 R6776
952257    592                 E8624
209184    3590                Q7863
239874    6094                N7969
497500    40110               W6490
188796    70071               O9708
145090    507550              S3705
803994    820096              S20
97986     9380148             W6598
Time: .787

Direct usage of the query methods...
row=$lb("700556","8","R7369")
row=$lb("696384","230","R6776")
row=$lb("952257","592","E8624")
row=$lb("209184","3590","Q7863")
row=$lb("239874","6094","N7969")
row=$lb("497500","40110","W6490")
row=$lb("188796","70071","O9708")
row=$lb("145090","507550","S3705")
row=$lb("803994","820096","S20")
row=$lb("97986","9380148","W6598")
Time: .894

score 0 · Answer 4 · 2021-05-12T11:11:26-04:00

Alright,

I think the "FOR SOME %ELEMENT" will do just fine when querying the table based on elements of the list (and therefore using the indices).

The fact that we can display the values in distinct lines is already great.

Thank you for help

score 1 · Answer 5 · 2021-05-12T08:01:18-04:00

Did you perform "Purge cached queries" before your test?
I tried it with some test data and could see the expected query plan using
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

score 1 · Answer 6 · 2021-05-10T12:12:53-04:00

The above solution is great if you’d like to keep the list in each record of one table. Depending on the nature of the data in your application, another option is to “normalize” the data a bit and create a separate table for the “numDossiersMER” values and link them back to the original “Titre” table as follows.

Convert the planned updated table from this:

User.TestList.Data.Titre

numTitre	millesime	codeProduit	numDossiersMER (list)
1	2021	X	1 2 3
2	2021	X	4 5 6
3	2021	X	4 2 3
4	2022	X	2 5 7 8

To the following 2 normalized tables

User.TestList.Data.TitreNew

Id (IRIS)	numTitre	millesime	codeProduit
1	1	2021	X
2	2	2021	X
3	3	2021	X
4	4	2022	X

User.TestList.Data.DossierMER

Id (IRIS)	titreID	numDossierMER
1	1	1
2	1	2
3	1	3
4	2	4
5	2	5
6	2	6
7	3	4
8	3	2
9	3	3
10	4	2
11	4	5
12	4	7
13	4	8

The “id (IRIS)” in each table is the “ROWID” assigned by IRIS as each entry is created in the table.

Using these two tables, the following “JOIN” query will get the results to be formatted as you like:

select numTitre, millesime, codeProduit, numDossierMER from User_TestList_Data.TitreNew t JOIN User_TestList_Data.DossierMER d on d.TitreId = t.id

Please note that this “normalized” solution is great if the “numDossierMER” values can be shared among various “Titre” records as shown in my made-up example above.

Sample code here:

Class User.TitreNew Extends (%Persistent, %Populate)
{ 
Property numTitre As %Integer; 
Property millesime As %Integer; 
Property codeProduit As %String; 
Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ]; 
}

Class User.DossierMER Extends (%Persistent, %Populate) 
{ 
Property titreID As %Integer; 
Property numDossierMER As %Integer; 
}

(Please excuse the formatting)