go to post 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 descIDCreationDatefoo112017-05-26 08:40:15Y1407102017-05-26 08:40:10J979392017-05-26 08:40:03Y501182017-05-26 08:39:57E5172017-05-26 08:39:55L34685 row(s) affected
go to post 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
go to post 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
go to post 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
go to post 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.
go to post 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
go to post 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
