Just to clarify - inheritance considers the primary super class hierarchy. %Persistent must be the first class some where in the primary super class hierarchy. Sample.Employee extends Sample.Person which extends %Library.Persistent.

If there is no storage definition then a storage definition whose type is %Storage.Persistent is created when compiling the class. This is done before the storage definition is compiled (every class member is compiled by the class compiler). Then, when the storage definition is compiled and if the type is %Storage.Persistent then the %Storage.Persistent.STORAGECOMPILERCLASS will generate a full storage definition for the current class definition.

What does that mean? Well - if this is the first time this storage definition has been compiled then it is most likely empty, other than the type class. The STORAGECOMPILERCLASS will generate a complete storage definition. But even if the storage definition is already defined, the STORAGECOMPILERCLASS still reviews that definition. If there are any changes detected to the class definition then the storage definition is updated to reflect those changes.

That means that the user can manually edit the storage definition at any time and the storage compiler will simply make sure that the definition is "complete" - no missing properties, no missing indexes, and so on.

Bottom line is that the user is free to make any desired changes, including deleting the storage definition completely.

Keep in mind that some changes can make the compiled class incompatible with existing data.

I agree with Tim but I'll take it one step further. Foreign keys are much more useful than relationships. After all, a relationship is simply a foreign key that maintains references to instances of the related class in memory. The projection of a relationship to SQL is simply as a foreign key. The set of related objects is simply populated using an SQL query. The problem with relationships is that they are extremely sticky and that can cause large numbers of objects to be inadvertently swizzled into memory. With foreign keys you have no in-memory model. That means with a foreign key you have to manage desired swizzling. Some view that as a problem, I view it as an advantage.

Another advantage of using foreign keys is that you can define multiple foreign keys using the same key component properties. No need to define a direct reference.

Creative minds might come up with a calculated property whose value is derived from the foreign key components, adding property methods to manipulate the related object/objects. This calculated property could be the direct reference. Perhaps transient would be better as a transient property also has instance memory allocated for it.

I am a fan Robert...but...

https://community.intersystems.com/post/new-video-sql-%E2%80%93-things-y...

Somewhere around the 36 minute mark of this video. Buried perhaps. Still, %SQL.CustomQuery has a lot of interesting capabilities.

Custom queries can also be instantiated without using SQL. Simply call %New and pass in the arguments that are defined by the %OpenCursor method. There is one difference here - the first argument of %New is the SELECTMODE and subsequent arguments correspond to the %OpenCursor arguments. Once instantiated, the interface is like any other %SQL.IResultSet.

USER>set result = ##class(example.custom.Query).%New(,"https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7","Default")

USER>write result.%Next()
1
USER>write result.name
Alabama
USER>write result.population
4849377
USER>while result.%Next() { write !,result.name,": ",result.population }

Alaska: 736732
Arizona: 6731484
Arkansas: 2966369
California: 38802500

First, keep in mind that all implementations (faithful implementations that is) of %SQL.CustomQuery are also projected as table-valued functions. That means you can include the function in the FROM clause of a SELECT statement.

The process of implementing a custom query is simple. These steps are described in the %SQL.CustomQuery class documentation so I'll just summarize here.

1. Define a new class that extends %SQL.CustomQuery;
2. Override the SQLNAME parameter, assign a valid SQL identifier that is to be the name of the TVF;
3. Define properties, in order, that are the columns of each row returned by this query. Let's call these "result columns". Each result column is defined as a non-private property;
4. Define properties that you will need to maintain the source data, pointers, etc. that you will use to manage the data used to produce rows. These properties are defined as "private";
5. Override %OpenCursor. Add parameters to this method override that correspond to the input parameters that will be passed when instantiating the custom query;
6. Override %FetchCursor. In this method, check for end of data. If not at the end then populate all of the result properties with data and return 1 (true). Otherwise, clear all result properties and return 0;
7.  Override %CloseCursor. In this override, release any resources acquired during instantiation and perform any necessary cleanup.

I won't post the version of the class that produces this output since the version of %Net.Http in current versions of CE/IRIS do not have a working GetJSON() method. The version of the class I'm posting simply passes in the raw JSON data as an argument.

The query:

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_query('https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*...','Default') 
ORDER BY population DESC

and the results:

5 row(s) affected

Using this version of a custom query class:

 
Class example.custom.JsonQuery Extends %SQL.CustomQuery
{

Parameter SQLNAME As String = "sample_custom_json_query";

Property data As %Library.DynamicAbstractObject [ Private ];

Property iterator As %Iterator.AbstractIterator [ Private ];

Property atEnd As %Integer [ InitialExpression = 0, Private ];

Property stateCode As %String;

Property name As %String;

Property population As %Integer;

Method %OpenCursor(data As %String(MAXLEN="")) [ Private ]
{
    try {
        if $isobject(data) {
            set ..data = data
        } else {
            set ..data = [].%FromJSON(data)
        }
        set ..iterator = ..data.%GetIterator()
        if '..iterator.%GetNext(.key,.value) {
            set ..atEnd = 0
            set ..iterator = ""
        }
    } catch exception {
            // this is just a place holder, this method reports errors by throwing an exception
            // but a catch can allow the user to log errors or perform some self-healing action
        throw exception
    }
}

Method %FetchCursor() As %Library.Integer
{
    set response = 0
    if ($isObject(..iterator)) && ('..atEnd) {
        if ..iterator.%GetNext(.key,.value) {
            set ..name = value.%Get(0)
            set ..population = value.%Get(1)
            set ..stateCode = value.%Get(3)
            set response = 1
        } else {
            set ..atEnd = 1
            set ..iterator = ""
        }
    } else {
        set ..name = ""
        set ..population = ""
        set ..stateCode = ""
    }
    return response
}

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
{
        // not really necessary as %OnClose will automatically close the cursor during destruction
        // but users can place code here to clean up other resources allocated for this query instance
        // that are external to the query instance. Like a temporary global.
    set ..iterator = ""
	set ..data = ""
}

}

and this query

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_json_query('[["STNAME","POP","DATE_","state"],["Alabama","4849377","7","01"],["Alaska","736732","7","02"],["Arizona","6731484","7","04"],["Arkansas","2966369","7","05"],["California","38802500","7","06"],["Colorado","5355866","7","08"],["Connecticut","3596677","7","09"],["Delaware","935614","7","10"],["District of Columbia","658893","7","11"],["Florida","19893297","7","12"],["Georgia","10097343","7","13"],["Hawaii","1419561","7","15"],["Idaho","1634464","7","16"],["Illinois","12880580","7","17"],["Indiana","6596855","7","18"],["Iowa","3107126","7","19"],["Kansas","2904021","7","20"],["Kentucky","4413457","7","21"],["Louisiana","4649676","7","22"],["Maine","1330089","7","23"],["Maryland","5976407","7","24"],["Massachusetts","6745408","7","25"],["Michigan","9909877","7","26"],["Minnesota","5457173","7","27"],["Mississippi","2994079","7","28"],["Missouri","6063589","7","29"],["Montana","1023579","7","30"],["Nebraska","1881503","7","31"],["Nevada","2839099","7","32"],["New Hampshire","1326813","7","33"],["New Jersey","8938175","7","34"],["New Mexico","2085572","7","35"],["New York","19746227","7","36"],["North Carolina","9943964","7","37"],["North Dakota","739482","7","38"],["Ohio","11594163","7","39"],["Oklahoma","3878051","7","40"],["Oregon","3970239","7","41"],["Pennsylvania","12787209","7","42"],["Rhode Island","1055173","7","44"],["South Carolina","4832482","7","45"],["South Dakota","853175","7","46"],["Tennessee","6549352","7","47"],["Texas","26956958","7","48"],["Utah","2942902","7","49"],["Vermont","626562","7","50"],["Virginia","8326289","7","51"],["Washington","7061530","7","53"],["West Virginia","1850326","7","54"],["Wisconsin","5757564","7","55"],["Wyoming","584153","7","56"],["Puerto Rico Commonwealth","3548397","7","72"]]') 
ORDER BY population DESC

produces the same result:

5 row(s) affected

I am happy to post other examples if you wish.

Dan

Sure, Fab, but let's compare apples to apples. Your test takes the most highly optimized $list traversal ($listnext) and compares its performance to an iterator. An iterator is an instance of a class and you are invoking methods to iterate through a dynamic array which, by its very definition, shouldn't have holes in it. So let's compare direct iteration vs. object iterators. I populated array and list the same way you did in your class. Then a simple command line test. Not valid as a white room benchmark but still it gives us a sense of what can be. Try it. Dynamic array random access is much better than $list random access. As the third test shows. 

%SYS>set start=$zh for i=0:1:array.%Size-1 { set disregard=array.%Get(i) } w !,$zh-start        

.000019

%SYS>set p=0,start=$zh while $listnext(list,p,value) { set disregard=value } w !,$zh-start

.007311

%SYS>set start=$zh for i=1:1:$ll(list) { set disregard=$li(list,i) } w !,$zh-start

8.673268

For 2017.1CE and later as well as all IRIS versions, %SQL.CustomResultSet should not be used. Instead, use %SQL.CustomQuery. There are several good reasons for this. There is good class documentation available. I am happy to post examples if anyone is interested.

That is a good question! I checked the internal change logs and found my changes were in 2020.1. Maybe earlier but I installed 2020.1 build 215 and tested a simple case.

IRIS for UNIX (Apple Mac OS X for x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:24:45 EDT

The test is simple - I am running in the 2020R1 instance, namespace USER and there are no classes runnable in this namespace - just a clean, new install. I have several other instances running, all different versions/build numbers. I am testing against my XDBC instance which is currently labeled as 2020.4 (obviously not yet released but the IRIS Native code is essentially the same as in 2020.1). The XDBC instance is listening on port 51780. First, proof this doesn't work locally.

USER>write ##class(Sample.Person).CurrentAge($h-35000)                 

WRITE ##CLASS(Sample.Person).CurrentAge($H-35000)
^
<CLASS DOES NOT EXIST> *Sample.Person

And then, attempt the same function using the IRIS Native connection to the XDBC instance.

USER>set host="localhost",port=51780,namespace="USER",user="_SYSTEM",pwd="SYS" 

USER>set connection = ##class(%Net.DB.DataSource).CreateConnection(host, port, namespace, user, pwd)

USER>set iris = connection.CreateIris()                                                             

USER>write iris.ClassMethodValue("Sample.Person","CurrentAge",$h-35000)                             
95

This is just a sample. Browse the classes in the SMP Explorer, look at the class docs for %Net.DB.Iris.cls.

This functionality might also be present in a 2019 kit but I didn't test it.

Can you elaborate? How else can a session running on one IRIS instance invoke a class method on some other IRIS instance?

There are a number of limitations on the size of an SQL statement I think, maximum string length is one of those limitations. It is based on the normalized "signature", including the statement text, as a single string, even when you pass the source statement as an array. I haven't worked in this area for several years so I can't speak to the details.

There is much to be discovered regarding Object Persistence. The ability to specify an expression in place of a literal global name in any of the various LOCATION keywords is just one. For example, you can add a public variable to a LOCATION keyword and its value at object filing time will be used to form the global location where the filer will update the data. There is risk involved and these "features" are most likely not documented. I do not recommend using these in production systems unless you fully understand the ramifications of doing so.

That said, there are a number of features that may or may not be documented but are certainly not private. In the context of this message, two come to mind. First is the Object Journal. Override the parameter OBJJOURNAL in a persistent class and all other classes referenced by this class and filing events are journaled. The Object Journal records each filing event for classes with the OBJJOURNAL parameter set and another class, %ObjectJournalTransaction, can be used to view the versions of those objects that were filed. All this would be wonderful but for a bug that I just discovered while coming up with an example for this post. I did a bit of research and it seems this bug has been present for a very long time and never reported. That indicates nobody is aware that this feature exists. I fixed the bug temporarily for the example.

SAMPLES>set person = ##class(Sample.Person).%OpenId(10)

SAMPLES>write oldperson.Name
Uhles,Ralph W.
SAMPLES>set person.Home.State = "NY"

SAMPLES>w person.%Save()
1
SAMPLES>set person.Office.State = "FL"

SAMPLES>set person.Name = "Book, John J"

SAMPLES>write person.%Save()
1
SAMPLES>write person.Name
Book, John J
SAMPLES>set journal = ##class(%ObjectJournalRecord).IDKeyOpen(3,4)

SAMPLES>set oldperson = journal.OpenObjectVersion(.s)

SAMPLES>write oldperson.Name
Uhles,Ralph W.

The second item of interest is something that has been around for a while as well and is definitely a fully documented and supported feature. This feature, Triggers, was previously an SQL only feature but it is now (and has been for several versions) available for Object filing as well. Using a save trigger for Objects and SQL allows access to the old and new values as well as a way to detect which values have been modified. I am happy to post an example if anyone is interested.

While the ability to specify an expression as a LOCATION keyword value and the use of Object Journal are not well known or mainstream features, Triggers are very much mainstream and can be quite useful.

The difference between runs can be caused simply by normal variations in system load. The difference between Concurrency = 0 and Concurrency = 1 is only present when the object is stored in multiple global nodes. There are five possible values that are described in the %Library.Persistent class documentation (viewable through the SMP). Concurrency = 1 is referred to as "atomic read". For single node objects there is no extra work required for atomic read. 

The name of the <index>Open method is based on the name of the index. I suspect that in one case the name of the IDKEY index is "IDKEY" and in another it is "idkey". No <index>Open method is generated if the index is not unique, primary or id.