New post
AllAccepted answers
Dan Pasco · Jun 28, 2017 
 Class User.DQ Extends %SQL.CustomQuery
{

Property id As %Integer;

Property lastName As %String;

Property firstName As %String;

Property age As %Integer;

Property ptr As %Integer [ Private ];

Property atEnd As %Boolean [ Private ];

Parameter SQLNAME As String = "DQ";

Method %OpenCursor() [ Private ]
{
try {
// Let's set up some temporary data
set ^CacheTempDQ(1) = $listBuild("Smith","John",42)
set ^CacheTempDQ(2) = $listBuild("Jones","Quincy",80)
set ^CacheTempDQ(3) = $listBuild("Wilson","George",71)
set ^CacheTempDQ(4) = $listBuild("Garcia","Andrew",32)
set ^CacheTempDQ(5) = $listBuild("Smoak","Felicity",24)
set ^CacheTempDQ(6) = $listBuild("Partridge","Audrey",32)
set ^CacheTempDQ(9) = $listBuild("Williams","Andrie",92)
set ^CacheTempDQ(10) = $listBuild("Orr","Robert",62)
set ^CacheTempDQ(11) = $listBuild("Moon","Lila",21)
set ^CacheTempDQ(120) = $listBuild("Lola","Elleoh",67)
set ..ptr = ""
set ..atEnd = 0
set ..%SQLCODE = 0
set ..%Message = ""
} catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
}
}

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
{
try {
set ..ptr = ""
set ..atEnd = 0
kill ^CacheTempDQ
set ..%SQLCODE = 0
set ..%Message = ""
} catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
}
}

Method %FetchCursor(ByRef sc As %Library.Status = {$$$OK}) As %Library.Integer
{
try {
if '..atEnd {
set ..ptr = $order(^CacheTempDQ(..ptr),1,data)
if ..ptr {
set link = ""
set ..lastName = $list(data,1)
set ..firstName = $list(data,2)
set ..age = $list(data,3)
} else {
set ..atEnd = 1
set ..%SQLCODE = 100
}
}
} catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
}
return '..atEnd
}

}

Then execute this statement:

 

select * from DQ()

 

or 

 

select * from DQ() where lastName %STARTSWITH 'S' order by age DESC

 
 
idlastNamefirstNameage
 SmithJohn42
 SmoakFelicity24

2 row(s) affected

 

Dan Pasco · Jun 28, 2017

Hi Chris,

Good question! This is a poorly understood area and there is much to say about it. I'll try to be brief. Feel free to follow up.

Your new property falls into the category of properties commonly referred to as 'derived columns' in other products. We support two different flavors - 'always computed' and 'triggered computed'. An 'always computed' property is indicated by the CALCULATED keyword. A 'triggered computed' property is 'NOT CALCULATED' although this is the default if you don't specify it. A triggered computed property can be TRANSIENT or stored (not transient - again, the default) and its value is triggered to recompute if the value of any property specified in the SQLCOMPUTEONCHANGE list of properties changes. In an object context, you need to be a aware that the value of a TRANSIENT property is directly settable unless you take precautions to prevent that.

The advantage to using SQLCOMPUTED/SQLCOMPUTECODE to derive a property value is that SQL and Objects seamlessly (almost) recognize the derived value and invoke the computation at the proper time. Overriding Get/Set methods can be used to derive property values but SQL will not recognize this as you have already discovered. 

SQL does not operate in an instance context. In other words, there is no oref available to SQL so SQLCOMPUTECODE must run in a class context and not in an instance (object) context. No oref. The way you have implemented your code is correct and, sorry to say, there isn't a cleaner way that I know about. 

There is one other thing you should know. When you open the object you may not actually get a new oref if that object was previously open in your process. That goes for any call to %Open([d]  - we cache the OID's with their active orefs and always return the existing oref from %Open if one is present in process memory. What does that mean? It means that you may see data in memory that has not committed to storage - Objects sees the data because of the in-memory context but SQL won't because it runs in an on-disk (storage) context. By opening objects you may produce a computed value that is different from the same data that is stored. There are ways you can avoid this problem, one being to store the derived value and trigger it to recompute on INSERT and UPDATE. This is easily defined by adding %%INSERT and %%UPDATE to the SQLCOMPUTEONCHANGE list of trigger properties.

Not so brief.

-Dan

Dan Pasco · Jun 20, 2017

Hi Peter!!

Your comment is correct of course. But think about the behavior and not the physical model. Everything that PARENT/CHILDREN relationships offers can be easily replicated using ONE/MANY with the only differences being the default storage model and the composite IDKEY that is required by PARENT/CHILDREN. With ONE/MANY you have the advantage of using system assigned integer ID's (frees you up to use bitmap indices - think product/invoice line perhaps) and you have more flexibility with referential actions. PARENT/CHILDREN requires ONDELETE=CASCADE. With ONE/MANY you have the option for ONDELETE=NOACTION/CASCADE... And, of course, if you with to establish existence dependency between the parent class and the child then simply add a REQUIRED constraint to the relationship whose cardinality = ONE.

Hope to see you at Global Summit!

 

Dan

Dan Pasco · Jun 16, 2017

I am not a fan of parent/children relationships. Think about what this really means. Kyle's comment about the data being nested beneath the parent object in the global is just the default physical storage model. The user has control over storage and can change that structure. But consider the logical behavior that PARENT cardinality brings. First, a relationship is like a foreign key - it is constrained by a referential constraint and it is subject to referential actions. Since this is a foreign key referencing the IDKEY of the parent class there is no need to define an ONUPDATE action (IDKEY values cannot be changed) but ONDELETE must be CASCADE. Why? Because PARENT cardinality establishes an EXISTENCE dependency on the parent. You can't change that unless you change the cardinality to ONE. Secondly, the IDKEY of the child class implicitly includes the parent relationship. This establishes an IDENTIFICATION dependency on the child class. You can't identify (the ID/OID) an instance of the child class unless you know the ID of the parent class. Furthermore, once a parent value is assigned and saved, you cannot change the value of the parent property. To do so would identify a different object and it isn't allowed. So - PARENT defines existence and identification dependencies.

To summarize PARENT/CHILDREN behavior:

1. Existence dependency;

2. Identification dependency;

3. Physical storage default is nested (artificially creating an implicit ONDELETE CASCADE behavior at the physical level - IMO this is not a good thing!)

 

And think about other ways to achieve the same thing using ONE/MANY or even a simple foreign key:

1. Existence dependency - make the relationship in the child class (or property if using fkey) REQUIRED;

2. Identification dependency - define the IDKEY and add the relationship with cardinality = ONE to the IDKEY;

3. Physical storage model nested - I don't recommend this model for ONE/MANY. 

The advantages of ONE/MANY:

1. The user has control over dependencies;

2. More ONDELETE/ONUPDATE options;

3. The option to base the IDKEY on a positive integer value, allowing the use of bitmap indices;

What does a relationship do for you that a foreign key does not? One thing. It maintains the relationship in memory when either side of the relationship is in memory (swizzled). This can be a good thing. It can also be a bad thing if there are a large number of related objects in the n-cardinality side.

Dan Pasco · Jun 15, 2017

Caché Objects persistent classes project to SQL as tables. Caché Objects relationships are binary - a relationship must have a defined inverse relationship in the related class. We only support relationship/inverse relationship cardinality pairs of one:many and parent:children (a special case of one:many where the parent is implicitly required and is also implicitly part of the IDKEY). A Caché Objects n-cardinality relationship (children, many) is transient in the container and there is no projection to SQL. A 1-cardinality relationship is stored and projects to SQL as a column that is constrained by a foreign key constraint. A simple query can be used to retrieve the contents of the unprojected n-cardinality relationship. Consider two tables, A and B, related by properties b and a. A.b is of type B and specifies cardinality = many.  B.a is of type A and specifies cardinality = one.

The value of A.b can be determined by this query:

select %ID from B where B.a = ?

and specify '?' as the A.%ID value. In fact, the internal relationship implementation uses a very similar query to populate A.b on %Open.

Dan Pasco · May 26, 2017

Yes, of course. The SQLCOMPUTECODE can invoke a classmethod but not an instance method unless you provide the instance. If you wish to pass arguments that correspond to other properties then just use curly-brace syntax to refer to them:

sqlcomputecode = { set {*} = ..MyComputeMethod({property1},{property2}) }

and the compiler will work out the details.

Also, we have proposed syntactical changes that would make this much nicer. The idea is that a class definition is composed of some set of members, members being things like properties, methods, indices, and so on. Each member type, excepting methods, can also have methods. The idea for the syntax is to embed the method definition with the member. This was just an idea and is not an active development project. But - something like this is what we envision:

property foo as %String [ sqlcomputed ] {

    method Compute(someParameter as %String) as %String {

        your code goes here, set the return value and return it

    }

}

 

Just an idea...

Dan Pasco · May 26, 2017

Triggers and computed properties (compute triggers) are certainly better than callbacks. Now that we have unified triggers callbacks should be avoided - IMO - in favor of using triggers. However, initial expression when there is some outside influence such as a clock or other value source involved, when the property is not read-only protected, etc. makes using initialexpression a bad idea in some cases. In cases where there is no opportunity for the user to interact with a new instance prior to it being saved then initialexpression is acceptable. If there is opportunity for interaction between instantiation and save (serialization) then initialexpression can be unreliable. The %%INSERT/%%UPDATE compute triggers avoid that. 

Dan Pasco · May 26, 2017

As a follow-up, I should have provided an example!


Class User.TriggerCompute Extends (%Persistent, %Populate)
{

Property foo As %String;

Property CreationDate As %TimeStamp [ SqlComputeCode = { set {*}=$zdt($h,3) }, SqlComputed, SqlComputeOnChange = %%INSERT ];
}

and I populated this class at random intervals. Here are five sample rows:

select top 5 * from triggercompute order by creationdate desc

IDCreationDatefoo
112017-05-26 08:40:15Y1407
102017-05-26 08:40:10J9793
92017-05-26 08:40:03Y5011
82017-05-26 08:39:57E51
72017-05-26 08:39:55L3468

5 row(s) affected

Dan Pasco · May 26, 2017

Very interesting question. Rubens Silva's answer is accurate - %OnAddToSaveSet provides more flexibility when you need to modify the object or the graph of objects to be serialized. 

The callbacks we provide as part of Caché Objects are limited to Objects and are often quite useful. However, Caché Persistent classes also project to SQL as tables. Users can perform CRUD operations using either Objects or SQL. Callbacks are not processed by SQL so any work you do in a callback must either be reproduced in some form in SQL or the user must recognize that accessing persistent data through SQL might produce different results from performing an equivalent access using Caché Objects. 

But we have better choices these days. Caché Objects provides two mechanisms for triggering behavior. SQL recognizes both of these mechanisms. The first is a compute trigger. You can define a property as SQLCOMPUTED. The value of the property can be defined to be computed whenever the value of a property defined in the SQLCOMPUTEONCHANGE list of properties is changed. We support two meta-properties to trigger a compute on insert and update - %%INSERT and %%UPDATE. If SQLCOMPUTEONCHANGE includes %%INSERT then the value of the property is computed when the object is about to be inserted into the database. This computation will override the current value of the property. Similarly, if %%UPDATE is specified in SQLCOMPUTEONCHANGE then the property's value is computed just prior to saving an updated version of the object. 

Compute triggers have been present in Caché for a very long time and %%INSERT/%%UPDATE were available in 2008.1.

The second mechanism is a TRIGGER. TRIGGERs were one the exclusive domain of SQL but we since added a new FOREACH keyword value of ROW/OBJECT. When FOREACH = ROW/OBJECT the trigger will be pulled at the defined time for each filing event using SQL or Objects. FOREACH = ROW indicates that the trigger is invoked only when SQL is used. There is no FOREACH value for invoking a trigger only when Objects is used.

We refer to this feature as "unified triggers". This feature first appears in Caché 2014.1.

-Dan

Dan Pasco · May 26, 2017

Very interesting question. The callbacks we provide as part of Caché Objects are limited to Objects and are often quite useful. However, Caché Persistent classes also project to SQL as tables. Users can perform CRUD operations using either Objects or SQL. Callbacks are not processed by SQL so any work you do in a callback must either be reproduced in some form in SQL or the user must recognize that accessing persistent data through SQL might produce different results from performing an equivalent access using Caché Objects. 

Rubens Silva's answer is accurate - %OnAddToSaveSet provides more flexibility when you need to modify the object or the graph of objects to be serialized. 

But we have better choices these days. Caché Objects provides two mechanism for triggering behavior. SQL recognizes both of these mechanisms. The first is a compute trigger. You can define a property as SQLCOMPUTED. The value of the property can be defined to be computed whenever the value of a property defined in the SQLCOMPUTEONCHANGE list of properties is changed. We support two meta-properties to trigger a compute on insert and update - %%INSERT and %%UPDATE.

The second mechanism is a TRIGGER. TRIGGERs were one the exclusive domain of SQL but we since added a new FOREACH keyword value of ROW/OBJECT. When FOREACH = ROW/OBJECT the trigger will be pulled at the defined time for each filing event using SQL or Objects. FOREACH = ROW indicates that the trigger is invoked only when SQL is used. There is no FOREACH value for invoking a trigger only when Objects is used.

We refer to this feature as "unified triggers". This feature first appears in Caché 2014.1.

-Dan

Dan Pasco · Nov 30, 2016

Hi John,

Great question! There is a lot of misinformation floating around about the general topic of CALCULATED, TRANSIENT, SQLCOMPUTED, etc. I'll try to clear the air and lay a clean foundation. First, let's discuss CALCULATED and TRANSIENT.

Each property defined in a class is accessed by a "getter" and a "setter", implemented as <property>Get and <property>Set methods respectively. In most common cases these accessor methods are trivial and are implemented at a very low level in Caché simply retrieve or store a value in the instance memory allocated to the property. In fact, the <property>Get and <property>Set method generators depend on instance memory. If no instance memory is allocated then the method generators have no clue where to retrieve the property's value from or where to set (store) the property's value. The allocation of instance memory is controlled by the CALCULATED keyword. If CALCULATED is true then no instance memory is allocated and the only way to access the property value is to override the accessor methods. Commonly, only the <property>Get method is overridden. We refer to such a property (CALCULATED with overridden Get method) as a "derived property". A CALCULATED property is not implicitly settable but, in theory, such a property can be set if the <property>Set method is implemented. I don't know offhand if the theory works in practice.

Now on to TRANSIENT. TRANSIENT simply means that the property is not stored when the containing object is saved. For a subclass of %SerialObject it means the property is not part of the serialized state produced by %GetSerial. There is instance memory allocated for a TRANSIENT property, assuming the user hasn't also defined it as CALCULATED. BTW, CALCULATED implies TRANSIENT but the reverse is not true. Since instance memory is allocated to a TRANSIENT, NOT CALCULATED property, the system does generate accessors (Get and Set) and such a property is implicitly settable. 

Users can override the Get/Set accessors to inject custom behaviors for any property, CALCULATED or not, but doing so directly causes problems for SQL. (Property accessor methods run in an instance context and SQL does not.) Caché provides a mechanism for deriving a property value that can be used outside of an instance context. That mechanism is triggered by SQLCOMPUTED and SQLCOMPUTECODE. I won't go into details of how to define SQLCOMPUTECODE here.

If a property is defined as CALCULATED and SQLCOMPUTED then we refer to that property as "always computed", meaning that every <property>Get reference will cause the SQLCOMPUTECODE to be executed (more on that in a bit) to derive the current value of the property. 

If the property is defined as NOT CALCULATED and SQLCOMPUTED then we refer to that property as "triggered computed" and its value will only be derived when certain conditions are true. First, if the current value of the property is NULL then the SQLCOMPUTECODE is called to derive the current value. Second, if the value of any property in the SQLCOMPUTEONCHANGE list of properties is changed then the SQLCOMPUTECODE is called to derive an updated value. Finally, there are two special tokens that can be specified in SQLCOMPUTEONCHANGE - %%INSERT and %%UPDATE. These tokens will trigger the SQLCOMPUTECODE to derive an updated value for the property when inserting (%%INSERT)  a new instance into the extent of the class (or a new row in the SQL table) or updating (%%UPDATE) and existing instance or row.

That's the foundation. 

Determinism. The function used to derive a value is only deterministic if that function returns the same value each time it is invoked with the same inputs. (if a = b then f(a) = f(b)). A property that is NOT CALCULATED + TRANSIENT is implicitly non-deterministic in an instance context. A CALCULATED property can be deterministic but it depends on the implementation of the function to derive the value. A property whose value is derived by overriding Get, Set or both is implicitly non-deterministic in an SQL context. (this concept is similar to third and fourth normal forms in relational systems). A simple non-deterministic function is now() implemented in Caché as "return $h". A more complex example would be a function that returns a value retrieved from another table. That function is non-deterministic because the value retrieved can be updated at any time, that is, passing the same input argument to the function multiple times can produce different results.

And the short answer to your question - if you define derived properties in your class for the purpose of simplifying SQL queries then make certain you understand the implications of non-deterministic derivation. Not all non-deterministic derived values are bad. Take Age() as an example. If Age is computed based on current time then it is non-deterministic. That makes it a bad candidate for a NOT CALCULATED property but showing Age in a query is a good thing.

HTH,

Dan

Dan Pasco · Mar 14, 2016

A couple of small points. %SQL.Statement is the newest and the recommended approach. As was stated previously, in many cases it will perform better. From a pure SQL perspective, a class query is defined as an SQL-Invokable-Routine (SIR). A SIR can be either a procedure or a function. The SQL standard further states that there is an implicit procedure for every function. Functions can be invoked by other SQL statements - SELECT for example, while procedures can be invoked by a CALL statement. The %SQL.Statement %PrepareClassQuery call will prepare a CALL statement for a class query. Of course, the class query must be projected as a SQLPROC (an unfortunate choice of keyword name). 

 

But, a class query is a function.

 

There is a new feature (as of 2015.1) called "table-valued function" that allows a class query projected as a SQLPROC to be invoked directly from a SELECT statement in the FROM clause (you can try this in SMP/Explorer/SQL/SAMPLES):

select * from sample.sp_sample_by_name('N')

This option not only allows you to fully encapsulate any hidden (and perhaps unsupported) direct method calls but also allows the statement author to select a subset of available columns, order the results or further restrict the results:

 

select name,dob from sample.sp_sample_by_name('N')  where DOB > '01/01/1950' order by DOB desc
 

This feature is not restricted to %SQLQuery class queries, %Query queries are also supported.

 

As a side note, the hidden and unsupported direct class query method, Func, is what we generate to support the SIR-Function interface.

Dan Pasco · Jan 21, 2016

Hi,

There is no capability to order the objects returned by the various iterator methods implemented by %Library.RelationshipObject. Your best alternative is to use SQL to select the related objects using a restriction similar to "WHERE Company = :companyID" and then to include explicit ORDER BY to define the desired ordering. Of course, this comes with some requirements - the set of related objects, Employee instance in this example, has to be saved to the Employee extent before you can use SQL to query them and also any changes made to those instances in memory will not be reflected by the query results. That is why I used the technique of querying using dynamic SQL and %ObjectSelectMode as it reduces the impact of in-memory changes.

It doesn't completely eliminate the "persistence" requirement for querying as  any newly related objects have to be saved before they can be queried. 

The only other possiblity is for you to iterate over the set of related objects and build your own sorted array.

Hope this helps!

 

Dan

Dan Pasco · Jan 20, 2016

Caché supports binary relationships with cardinality 1:N (one to many, many to one).  A relationship presents two behaviors. One is to logically link the two objects togther, allowing one to be accessed from the other. When persisted, the relationship is only valid for as long as both objects exist in their respective extents. In this, a relationship is very much like a foreign key constraint in SQL. The other behavior is to maintain the relationship in memory when both of the related objects are present. Think of this as in-memory referential integrity.

The concept of an "ordered relationship" only is relevant when you consider that one object can be related to many others. From the perspective of the one object, the many objects could be ordered. But what defines that order? Is it the order in which the relationship with each of the many side objects was formed? Is it some implied order based on the state of the many side object? A key of some sort? What happens if the key is not defined in all of the many side objects? What if the ordered key value is changed while the object is in memory?

There are many questions that come up in the conversation about "ordered relationship". Often, the user assumes that the order in which many side objects were related to the one side object will define an order. That is not true nor can it ever be true. There is no defined order and any order implied cannot be assumed to hold over time.

The best approach is to not assume any order. If you require the related many side objects to be presented in a particular order then use a simple SQL select statement to retrieve those related objects and use an ORDER BY clause to explciitly define the order. This recommendation comes with the caveat that all of the related objects must have been persisted before executing the SQL statement. It also presumes that the many side of the relationship is a persistent class.

It is possible to prepare and execute the order relationship SQL statement in a manner that will honor the in-memory state of the related objects. The uncommitted state of the relationship will not be honored. Only relationships already formed and persisted are visible to SQL.

The SAMPLES database includes an example of a 1:N relationship - Sample.Company and Sample.Employee.

In the following example, I use dynamic SQL with %ObjectSelectMode = 1 (true). I open an instance of Sample.Employee and change its name to "State,Altered". Before I run the statement, I write the value of the Name property before changing it. Notice the output - it contains the altered name because of the basic rule of swizzling. If I were to also select the Name column in the SELECT statement, it would be the value as it current resides on disk, not as it resides in memory. I will leave that as an exercise for the reader.

LATEST:SAMPLES>kill

LATEST:SAMPLES>set employeeInMemory = ##class(Sample.Employee).%OpenId(193)

LATEST:SAMPLES>write employeeInMemory.Name

Gibbs,Greta A.

LATEST:SAMPLES>set employeeInMemory.Name = "State,Altered"

LATEST:SAMPLES>set sql = "select %ID As Employee from Sample.Employee where Company = ? order by SSN"                                     

LATEST:SAMPLES>set statement = ##class(%SQL.Statement).%New()

LATEST:SAMPLES>set statement.%ObjectSelectMode = 1

LATEST:SAMPLES>do statement.prepare(.sql)

LATEST:SAMPLES>set employees = statement.execute(10)

LATEST:SAMPLES>while employees.%Next() { set employee = employees.Employee write !,employee.%Id(),$c(9),employee.Name,?30,employee.SSN,! }

195 Beatty,Stuart T.          123-22-3131

200 Vanzetti,Kevin Q.         186-73-9108

170 Eagleman,Bart C.          247-83-1354

191 LaRocca,Tara W.           338-71-2352

164 Clay,Kristen S.           476-52-4746

193 State,Altered             523-94-2490

165 Campos,Edgar D.           558-21-6591

115 Davis,Angela G.           767-95-5164

155 Sato,Alfred L.            845-25-6807