New post
AllAccepted answers
Vitaliy Serdtsev · Apr 13, 2021

If you use Studio to edit/compile *.csp files, check the following parameters:

  • Enable service status check (Recommended) (yes)
  • Studio is active application (2–60 sec) (5)
  • Studio is background application (30–600 sec) (60)
  • Automatically reload document when it is modified on the server and it has not been edited in Studio (yes)

And also see Caché Launcher (Cube) settings : Web Server IP Address / CSP Server Instance

I tried to simply edit the file in Studio (csp/samples/redirect.csp) and with an external editor FAR Manager ([CACHEROOT]\CSP\samples\redirect.csp)
For simplicity, I changed the comment in the header. I tested everything both locally and remotely.

So, I change it in Studio - the changes are immediately visible in FAR, I change it in FAR - the changes are immediately visible in Studio.
Or in another way: I change it locally - the changes are immediately visible on the remote machine, I change it on the remote machine - the changes are immediately visible locally.

In other words, there is no and cannot be any desynchronization, since the work is always done with the same file on the server, see Saving a CSP File.

Vitaliy Serdtsev · Apr 12, 2021

Important:
Jobbed Process Permissions are Platform-dependent
Running Programs or System Commands with $ZF(-100)

So that we speak the same language, I made a simple example using Using the Work Queue Manager

Class dc.test [ Abstract ]
{

ClassMethod MyJob(SDIR As %String)
{
  s FILE=##class(%File).NormalizeFilename("DIRLIST.TXT",SDIR)

  q:##class(%File).GetFileSize(FILE)>0

  d ##class(%File).Delete(FILE)

  s X=$ZF(-1,$$$FormatText("DIR %1 >> %2",$$$quote(##class(%File).NormalizeDirectory(SDIR)),$$$quote(FILE)))
}

/// d ##class(dc.test).test()
ClassMethod test()
{
  s N=4
  s queue=$system.WorkMgr.Initialize(,.sc,N)
  f i=1:1:N d queue.Queue("##class(dc.test).MyJob","C:\Temp\test "_i)
  d queue.WaitForComplete()
}

}

I copied different files to the following directories:

C:\Temp\test 1
C:\Temp\test 2
C:\Temp\test 3
C:\Temp\test 4

After calling d ##class(dc.test).test() , everything worked out as expected: DIRLIST.TXT were created in the corresponding directories each with its own content.

Vitaliy Serdtsev · Apr 12, 2021

You can still avoid code duplication.
To do this, you need to make a function, and already use it in query.

For example:

/// Linked Funds via Person->Fund link
ClassMethod LinkedFundsByPerson(personId As %Integer) As %Boolean [ SqlName = CUSTOM_MyQuery, SqlProc ]
{
  &sql(SELECT NULL FROM QUASAR_Core_Client.Client AS Client
      INNER JOIN QUASAR_KYC_Fund.Fund AS Fund ON Client.Number=Fund.InternalReference
      LEFT JOIN QUASAR_GDPR_Close_Fund.FundClosure As FundClosure ON Client.Number=FundClosure.ID
    WHERE Client.ClientMarketIndicator='C'
    AND Client.Number IN
    (
    SELECT Client
    FROM QUASAR_KYC_Person.FundLink
    WHERE Person->InternalReference=:personId
    )
  )
  QUIT ''%ROWCOUNT
}


SELECT *
FROM TableA
WHERE CUSTOM_MyQuery(ID)>0
Vitaliy Serdtsev · Apr 12, 2021

You can't pass IDENTIFIER to class query, but only LITERALS. This is akin to error 5262:

Cannot project query with parameters '%1' as view

Here is a simple demo of this issue:

Class dc.test Extends %Persistent
{

Property p As %Integer;

Query LinkedFundsByPerson(p As %Integer) As %SQLQuery(ROWSPEC = "p:%Integer") [ SqlName = CUSTOM_MyQuery, SqlProc ]
{
SELECT p FROM dc.test
where ID IN
(
SELECT ID
FROM dc.test
WHERE p=:p
)
}

ClassMethod test()
{
  &sql(SELECT * FROM dc.test WHERE EXISTS (SELECT * FROM dc.CUSTOM_MyQuery(ID)))
}

}
Vitaliy Serdtsev · Apr 12, 2021

See CREATE FUNCTION

  • Create your own counting function: 
    CREATE FUNCTION my.GetCalcTableExtentSize(IN SchemaName SYSNAME, IN TableName SYSNAME) RETURNS BIGINT('') PROCEDURE LANGUAGE OBJECTSCRIPT
{
 quit ##class(%SQL.Manager.Catalog).GetCalcTableExtentSize(SchemaName, TableName)
}
  • Now you can use it in queries:
    select table_schema "Schema", table_name TableName,my.GetCalcTableExtentSize(table_schema,table_name) RowCount from information_schema.tables where table_type in ('BASE TABLE','VIEW')
Vitaliy Serdtsev · Apr 7, 2021

See %Regex.Matcher

Example:

s text = "This is a sample blob of text",
  keywords="This,blob,text"

s matcher=##class(%Regex.Matcher).%New($tr(keywords,",","|")),
  matcher.Text=text
w:matcher.Locate() "hit",!

d matcher.ResetPosition()

while matcher.Locate() {write "Found ",matcher.Group," at position ",matcher.Start,!}

USER>d ^test
hit
Found This at position 1
Found blob at position 18
Found text at position 26

Or see $locate: Using Regular Expressions in Caché

Example:

USER>w $locate(text,$tr(keywords,",","|"),1,e,x)
1
Vitaliy Serdtsev · Apr 2, 2021

I keep my promise (yes, it wasn't an April Fool's joke ;)

  • The first way is associated with a dummy field for the sake of being able to override the final BuildValueArray method and avoid the following error 
    ERROR #5272: Can't change final 'Method' : 'BuildValueArray')
    Class rcc.IC.ItemList Extends (%Persistent, %Populate) [ Final ]
{

Index xitmp On (ItemsP(KEYS), ItemsP(ELEMENTS));

Property Company As %String [ Required ];

Property Region As list Of %String(COLLATION = "EXACT", POPSPEC = ":4", VALUELIST = ",US,CD,MX,EU,JP,AU,ZA") [ Required ];

Property Items As list Of rcc.IC.serItem(POPSPEC = ":4") [ Required ];

Property ItemsP As %String(COLLATION = "EXACT") [ Calculated, Private, ReadOnly, Required, SqlComputeCode = {s {*} = {Items}}, SqlComputed ];

ClassMethod ItemsPBuildValueArray(
  value,
  ByRef array) As %Status
{
  s ptr=0
  while $listnext(value,ptr,val){
    s v=$li(val,1),
      array($li(v,1))="Subject",
      array($li(v,2))="Change",
      array($li(v,3))="Color"
  }
  q $$$OK
}

}
    select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow') and %value='Color')

    or if need to find values in any fields

    select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

    For the sake of speed, you can store in the index not all the fields of the serial class, i.e.:

    Index xitmp On ItemsP(KEYS);

ClassMethod ItemsPBuildValueArray(
  value,
  ByRef array) As %Status
{
  s ptr=0
  while $listnext(value,ptr,val){
    s v=$li(val,1),
      array($li(v,3))="" ; only Color
  }
  q $$$OK
}
    select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

    You can also add more dummy fields and accordingly indexes to cover all possible queries.

  • The second way involves changing the storage and creating a virtual table 
    Class rcc.IC.ItemList Extends (%Persistent, %Populate) [ Final ]
{

Index xitm On Items(ELEMENTS).Color;

Index xitm1 On (Items(ELEMENTS).Color, Items(KEYS));

Property Company As %String [ Required ];

Property Region As list Of %String(COLLATION = "EXACT", POPSPEC = ":4", VALUELIST = ",US,CD,MX,EU,JP,AU,ZA") [ Required ];

Property Items As list Of rcc.IC.serItem(POPSPEC = ":4", STORAGEDEFAULT = "array") [ Required ];

}
    select count(distinct ID) from rcc_IC.ItemList where ItemList_Items->Items_Color in ('blue','yellow') -- index "xitm1" is used

    or even faster

    select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(Items) (%Value in ('blue','yellow')) -- index "xitm" is used

    Of course, the data can be accessed from both tables, just do not forget about SetCollectionProjection GetCollectionProjection (for more information, see my article SQL index for array property elements)

Vitaliy Serdtsev · Apr 2, 2021

The %SYS namespace sources are open to study and often (but not always) serve as a coding etalon reference for application developers. This is a whole storehouse of knowledge for those who want to better understand certain mechanisms work of system classes.
If some of the commands found there are not documented, but effectively do useful work, then why can't they be used by application developers?
In addition, I do not rule out the fact that some of them are simply forgot to document.

What really needs to be hidden or potentially dangerous is already hidden in the deployed classes.

Vitaliy Serdtsev · Apr 1, 2021

Hi Robert.

I also got carried away with this question and found two more ways to use indexes for a list of serial objects, and you can explicitly specify specific fields in the query, rather than $list (%Value,3).

The speed may be not always the best, but I did the best I could. I tested on Caché (perhaps something has been improved in IRIS in this regard?)

If you're interested, I can share it.