New post
Question
· Apr 15, 2020

%CacheSQLStorage: data location as variable

#Mapping #ObjectScript #Caché

If two globals have same structure, how to reuse one CacheSQLStorage-based class to read both locations?

Thanks!

Update:

Let's say we have two globals:

^a(1)="dataa1"
^a(2)="dataa2"

and

^b(1)="datab1"
^b(2)="datab2"

i.e. the structure difference is global name only.

I would like to have two classes TableA and TableB both are inherited from TableAB, and TableAB taking global name as a parameter.

Discussion (14)3
Log in or sign up to continue
Stephen Canzano · Apr 15, 2020

Depending on what you exactly mean by the same structure you consider using a Serial Class definition that is embedded in both of your classes.  However, if the structure is stored across several global nodes I do not think you could use this as the Serial class would define the pieces(whether delimited or $ListBuild pieces) and then the serial property is described in your 2 classes to occupy a single node.

You could also consider defining a single abstract class that describes the properties and have your 2 classes inherit from your abstract class.

With regards to having a variable defining the data location I suspect that it may not be doable, even if it were I don't see how it improves things in a significant way.

0 0
Stephen Canzano · Apr 15, 2020

with your updated problem description where your globals only contain a single node I would consider the serial/embedded approach.  This still don't address your request for a variable name for the storage, but it does mean for the columns/properties you can define them in the serial class once and then embed in the class that represents global a and global b.  It also means in the storage map you just have to define the data node as the serial property. 

Let's say we could get it to work such that the storage map is based on a variable, I suspect other things wouldn't work.  For example, in Documatic when you view a class you can select the Storage checkbox and get a display that looks like 

I suspect if somehow you can get the storage map to be dynamic and based on a variable name this display would fail or not show you the value of the variable.

0 0
Robert Cemper · Apr 15, 2020

Based on the changed question you may consider this solution with just 1 Common Class:

If you look to the  Storage definition you see something similar:

<SQLMap name="Map1">
<Data name="....
</Data>
<Global>^tabA</Global>

Now you change the <Global> node to

<Global>@($s($d(%GLOBAL):%GLOBAL,1:"^tabA"))@</Global>

this means if there is no variable %GLOBAL you see ^tabA  but if you set %GLOBAL you see ^tabB 

to set the variable also directly from SQL  you may add this ClassMethod as SQLprocedure

ClassMethod SetGlobal(global As %String = "tabA") As %Integer [ SqlName = SetGlobal, SqlProc ]
{  set %GLOBAL=global  quit $$$OK }

now see the effect. first the test data:

 CACHEUSER>zw ^tabA
^tabA(1)="A=Brendan Bannon^55192^SQL Manager"
^tabA(2)="A=Nicole Aaron^63375^Support Specialist"
^tabA(3)="A=Kyle Baxter^61155^Senior Support Specialist"
^tabA(4)="A=Prasad Kari^58471^Support Specialist"
^tabA(5)="A=Clive Morgan^57982^Senior Support Specialist"
 
CACHEUSER>zw ^tabB
^tabB(1)="B=Brendan Bannon^55192^SQL Manager"
^tabB(2)="B=Nicole Aaron^63375^Support Specialist"
^tabB(3)="B=Kyle Baxter^61155^Senior Support Specialist"
^tabB(4)="B=Prasad Kari^58471^Support Specialist"
^tabB(5)="B=Clive Morgan^57982^Senior Support Specialist"

and now the SQL query

 CACHEUSER>zw   ; no variables

CACHEUSER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
[SQL]CACHEUSER>>select * from Tbl  -- tableA
 
ID      HireDate        Name    Sub2    Title
1       55192   A=Brendan Bannon        1       SQL Manager
2       63375   A=Nicole Aaron  2       Support Specialist
3       61155   A=Kyle Baxter   3       Senior Support Specialist
4       58471   A=Prasad Kari   4       Support Specialist
5       57982   A=Clive Morgan  5       Senior Support Specialist
 
5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0007s/20/955/0ms
          execute time(s)/globals/cmds/disk: 0.0004s/6/1324/0ms
                          cached query class: %sqlcq.CACHEUSER.cls6
---------------------------------------------------------------------------
[SQL]CACHEUSER>>COS set %GLOBAL="^tabB"  ; going for Global ^tabB

[SQL]CACHEUSER>>select * from Tbl -- tableB
 
ID      HireDate        Name    Sub2    Title
1       55192   B=Brendan Bannon        1       SQL Manager
2       63375   B=Nicole Aaron  2       Support Specialist
3       61155   B=Kyle Baxter   3       Senior Support Specialist
4       58471   B=Prasad Kari   4       Support Specialist
5       57982   B=Clive Morgan  5       Senior Support Specialist
 
5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0005s/20/955/0ms
          execute time(s)/globals/cmds/disk: 0.0003s/6/1324/0ms
                          cached query class: %sqlcq.CACHEUSER.cls6
---------------------------------------------------------------------------

 next with pure SQL

 CACHEUSER>zw   ; no variables
CACHEUSER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

[SQL]CACHEUSER>>select * from Tbl where SetGlobal('^tabB')=1
 
ID      HireDate        Name    Sub2    Title
1       55192   B=Brendan Bannon        1       SQL Manager
2       63375   B=Nicole Aaron  2       Support Specialist
3       61155   B=Kyle Baxter   3       Senior Support Specialist
4       58471   B=Prasad Kari   4       Support Specialist
5       57982   B=Clive Morgan  5       Senior Support Specialist
 
5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0008s/27/3394/0ms
          execute time(s)/globals/cmds/disk: 0.0003s/6/1350/0ms
                          cached query class: %sqlcq.CACHEUSER.cls10
---------------------------------------------------------------------------
[SQL]CACHEUSER>>

The SQL procedure SetGlobal is independent of the rows:
It will be processed before the row selecting.  So we set our local variable %GLOBAL there.
As it is part of the WHERE clause we have to check the return value.
So it works with any SLQ Query tool. eg. Mgmt Portal.

Setting the variable works of course also for object access.  

4 0
Brendan Bannon · May 7, 2020

Arto

You can have one class look at multiple globals by defining the first subscript level to be of type Global and the global name for the map would be *

I have an example of this in the Mapping zip file that is part of the SQL storage series I did a couple of years back.  Mapping.TwoGlobals.cls

In that class, I have 2 globals:  Member and Provider.

/// 
/// ^Member(1,"08/24/2005 11:39",4)=One Memorial Drive
///                              5)=3rd Floor
///                              7)=Cambridge
///                              8)=MA
///                              9)=02142
///  
/// Array: ^Provider
/// ^Provider(1,"08/24/2005 11:43",10)=
///                                11)=15 Cliff Dr
///                                12)=
///                                13)=
///                                14)=Plainville
///                                15)=MA
///                                16)=02762

Theglobal and first subscript looks like:

<Global>^*</Global>
<Subscript name="1">
<AccessType>Global</AccessType>
<Expression>{GloType}</Expression>
<NextCode> i {L1}="" s {L1}="Member" QUIT
 i {L1}="Member" s {L1}="Provider" QUIT
 s {L1}=""
 </NextCode>
</Subscript>

The class has an extra twist in that the two globals are not exactly the same so the fourth subscript level needs to deal with the data being in different places.

you can get all my mapping examples from here

0 0
Brendan Bannon · May 8, 2020

So the issue with Inheritance and Cache SQL Storage is you can't modify the storage to add new properties in the sub class.  For you that does not matter, you just want to change the global name.  So if there was some way we could write the code so that it would generate a different global name for the different classes you would be all set.  I tried using $parameter($classname,"MyParameter") but could not get it to work.  One issue, I think, is when we are compiling this code we are in a Cached Query class, not the class I want to be referencing.  

The best I could do is hard code it to the current class.  So if you have 2 classes with the info below the only change you need to make is to change the parameter value from A to B.

Class Mapping.SuperClass Extends %Persistent [ Language = objectscript, StorageStrategy = NewStorage1 ]
{ Parameter GloParameter = {"A"}; Property GlobalName As %String; Property Sub1 As %Integer; Property Data1 As %String; Index Master On (GlobalName, Sub1) [ IdKey ]; Storage NewStorage1
{
<SQLMap name="Map1">
<Data name="Data1">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^*</Global>
<Subscript name="1">
<AccessType>Global</AccessType>
<Expression>{GlobalName}</Expression>
<NextCode> i {L1}="" s {L1}=##class(Mapping.SuperClass).#GloParameter QUIT
 s {L1}=""
</NextCode>
</Subscript>
<Subscript name="2">
<AccessType>Sub</AccessType>
<Expression>{Sub1}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.SuperClassS</StreamLocation>
<Type>%Storage.SQL</Type>
} }

1 0
Dan Pasco · May 11, 2020

I admit that when I first read this post I wasn't interested in commenting. I don't normally get involved in SQL mapping questions. @Brendan Bannon is the expert on that topic. As I read through this more, I became interested in what you didn't ask and I need to make sure that I'm on the right track.

Are you most interested in sharing the definition of a data node or do you have specific requirements that make you want to use SQL-mapped storage?

--Dan

0 0
Dan Pasco · May 13, 2020

What I don't know is if you need SQL mapped storage because default storage doesn't work for your case. Inheritance of storage is not the same as for other class members. I wrote storage inheritance and I am intrigued by the case where there are common serializations shared by multiple classes. Steve Canzano's idea is really close to what I think is a good solution but introducing %SerialObject brings with it some complications. Interestingly enough, the idea of SQL Map inheritance was probably brought up first by Steve several years ago.

What is clear is that no storage is inherited from anything other than the primary super class. I would like to understand your need to see if that restriction is preventing you from doing as you wish. Relaxing that restriction is not a simple task so I would also like to know if there is an existing pattern that can address your need.

-Dan

0 0