Tipale 3 - Identity Theft

Apology

Let me first apologize for:

  1.  Using a misleading title. This post has nothing to do with the common usage of the term.
  2.  Posting an advice which falls in the "Ugly but Effective" category.
*The example shown here uses MSSQL related syntax.

Wish you weren't here

I hope this post will become obsolete ASAP, because what I'll show here is something that I hate to do, which is tricking Magic to a certain course of action to overcome a missing feature.

The missing feature is a straightforward property of a Column in a Datasource, that defines it either as real or computed, so that only a real column will be included in an INSERT command issued by the Gateway.

As the title implies, the trick involves unconventional usage of the  INTEGER IDENTITY keyword.
The original intended use of this keyword is to instruct Magic not to try and insert a value to a column defined as Identity.
Luckily, Magic does not prevent us from defining more than one column as INTEGER IDENTITY (an option that is obviously blocked by the underlying DB), so we can use it to mark computed columns as well, and thus instruct the Gateway to refrain from including these columns in the generated INSERT command.

A good example would be using SQL Window Functions, which is an extremely powerful feature.

Lets add these two computed columns to a table that has a column named country:

A. Column named total, with DB Column nameCOUNT (*) OVER ()
B. Column named total in country, with DB Column name: COUNT (*) OVER (PARTITION BY COUNTRY)

For both of these new computed columns we will set the Type property in the SQL Category to INTEGER IDENTITY

As you can see, the totals will be computed when showing existing records, and when adding new records Magic will not try to update them.

It should be noted that Window Functions refer to the current dataview and not to the whole table!

Comments