Hi Omer, would your solution consider a trigger? Haven't tried this but you could have a trigger that executes after insert/update that would launch an update on the affected row that basically would "add" the new value in the counter to the old one:

Your queries should change to something like this 

INSERT OR UPDATE myTable SET name='Omer', counter = 1

INSERT OR UPDATE myTable SET name='Omer', counter = -1

.....

The logic in the trigger would grab old value (handle NULL as 0) and add the new value only in cases where both are different or whatever your business logic might be.

To prevent infinite loops on updates your after update trigger can be defined to only be executed after any of the other columns in the table are updated with the exception of counter.

The "only" way to do this via SQL would be using stored procedures.  You can pass your object tree using either XML or JSON string and process them (using object logic) inside your SP. Standard SQL as intended won't support what you want to achieve. 

$LISTBUILD (IDs of referenced objects) should do the trick. The "parent" table here is only storing the corresponding IDs not the entire objects. You need to save each object individually and then link them.

While the object counterpart takes care of saving/inserting both objects in memory, the same is not true for SQL. You need to treat each object as individual rows on different tables.

Have you tried "jobbing" the routine from the CSP?

It is a best data management practice to add restrictions to fields if they have them. So adding the maxlen is "recommended"

Adding the maxlen won't result in wasted storage since storage is not reserved based on field definitions. If that were the case, in your particular scenario, you'd be actually saving storage since all string fields are defaulted to maxlen = 50 if you don't specify a value. 

Maybe I'm missing something, but, beyond how nulls are treated, if you want parent to be unique within this definition you must define a unique index on parent (alone). The index you have defined only guarantees that the combination (parent, name) will be unique.

Even if you declare the property as required it wouldn't still solve the uniqueness requirement. 

You need to use HyperEvents to accomplish what you want. Use #server or #call. Although if I'm following your use case #server is the right one. In the corresponding javascript function you'll have code similar to

myfunction() {

...

var myvar = #server(mycachemethod());

//check myvar value

// return true or false based value

}

Guessing a typo(copy paste from SetQualifiers?) in the documentation. It should say "...otherwise it gets the default qualifiers for this namespace."

Journal files are also exposed as SQL (readonly) tables. In theory, you could write SQL statements to search/filter. 

Few options without knowing much about current setup and the use case (eg. how much involvement you want to have in the process)

• Synchronizing(shadow) between the 2 instances - Global export/import
• Global mapping from one instance to another
  • You could merge global from source to target
•  File transfer (traditional ETL) and manipulating the global directly (risky but doable) 

Also, any indices on such field would need to be rebuild. Indices on %String properties are expected to start with an empty space thus your queries using these indices won't return any existing (non-updated) rows.

Is the question how to write a trigger or how to send a SMS from within Cache/IRIS? 

You should be able to find how to do both things in the Intersystems documentation (link not working for me right now).

We may need more clarity to answer the specific question you have but here are some answers to things I could pull from your post:

- The reason relationships are not showing in ERD is because adding an object reference from one class to another doesn't generate a ForeignKey (only relationships do this automatically) thus no relationship is defined in the generated ERD. If a FK is required then it should be defined manually.

- The primary keys for the tables in the ERD diagram are listed in your posted image. The fields listed there are the ones that correspond to the primary key of each of the tables displayed. If you're looking for a programmatically way to see the primary (assuming you don't have access to the code) then you could check information_schema.key_column_usage for constraint_type = "PRIMARY KEY". You may need to look deeper depending on whether table correspond to a global mapped (legacy) table or a "pure" SQL table. 

- As for the different results, it may be you're passing the wrong value to the query (eg. ID for one table being used to query another) or indices are out of sync or corrupted (rebuilding indices should fix this).

Sorry I misread the SET statement...

Have you tried running the same update (capture exact values being passed) from a JDBC client such as DBeaver? 

The generated tSQL looks good by just glancing at it.

Have you tried removing the comma(",patient_id")= from the sqlcolumns and sqlvalues?

How old are you talking about? In any case, you could use old stored procedures provided by Cache to pull the data. There will be under %SQL.Manager.Catalog, eg. %SQL.Manager.Catalog.Procedures()

To expand on David's answer, you'd have to do something like

S query = "query 1"

S query2 = " UNION query 2"

I doUnion S query = query_query2

S rs = ##class(%ResultSet).%New(query)

.....

Flyway (and Liquibase) allows migrations to be written in SQL so you don't need a fully integration to be able to use them with Cache/IRIS. Fully integrating (e.g. extending Flyway) would provide access to other automations and features that otherwise are not available with just SQL e.g. using the same script in different DBMS. In the case of Liquibase, automated rollback generation, etc.