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.

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...

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. 

I don't understand why it is not recommended. Can you elaborate?

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

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

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

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

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.

 

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