Monthly Archives: May 2009

One Step and then the Next

Cady took her first steps this week. I can’t wait to see her again and wish my grandbaby did not live so far away.

I’ve been taking a few steps lately too. Perhaps my writing muse was having a hard time keeping up and left me for a few too many days in a row. I’m not sure I have it back yet, so, I’ll catch you up on just a few details, nothing technical.

No sooner did I mention in a blog entry that I might be open for a gig than I got back to back calls. It has been about three years since I have taken on any new clients, so the calls had dried up. Perhaps the notice on my website that I was not doing any consulting helped with that too. After I wrote here that maybe I should take a call or two, I got them. I wasn’t even practicing The Secret on this one, so go figure. 

I took a gig about a month ago, which will help pay for a few hours this summer from two students, both of whom have other jobs. I was hoping to get a full-time student developer, but it didn’t pan out that way. I probably didn’t pick the right picture for my Dream Board (maybe I should start one of those).

An idea! Since I’m not one with twitter (ohm), what if I put a bunch of tweets together into a single article so that people are not interrupted by bits of information they could care less about, such as:

We are down to four. It wasn’t fitting well into Bobby’s life so he decided to bench himself before getting comfortable with the toolset.

Josh, a student, and Tony, a recent grad, will each work a few hours a week this summer.

[What’s with full sentences consistently, she asks herself. Get with the program.]

Still working to catch a stride, but making progress, putting one foot in front of the other, need to run without noticing the steps

Youngsters can be fearless, so I’m assigning them some things I don’t yet know how to do so they can teach me

Speaking of teaching, while being 100% dedicated to SnupNow, after a tough decision, I agreed to teach 2 courses next school year

I am trying not to worry about all the work right now and to figure out how not to worry about it at all, just do it

Teaching half-time is perhaps the hardest way to get a tiny amount of funding, but they came to me and said they needed me, I caved

I justified it to myself as something good for this project. I’m optimistic that it will work out well for SnupNow, but concerned too.

Only Tuesday and Thursday classes and I plan to keep all prep and grading to those days too

If you have ever taught, you likely know how hard that will be to pull off.

Lots to do on SnupNow this summer, and that is my focus right now.

Everyone likes this time in the school year best — when school’s out for the summer.

Hubby will be teaching in Zambia, gone for 7 weeks this summer. I will miss him. Hopefully I’ll get a lot done.

So, what do you think? Will this approach of putting all your tweets together rather than changing your facebook status or tweeting all the time catch on? I wonder what twitter’s retention rate is. I will avoid the temptation to google for that or write the question in an evernote. This is the concluding sentence where I tie it all together and go on to the next item on my list, getting things done one step at a time.


Identifying the Identifier

This article describes the various names for the primary key for a MultiValue developer working within the Cache’ software development environment. I would think this would only be of interest to those writing MultiValue applications using extended features of Cache’ and possibly to InterSystems employees who I am hoping will agree with me that this could be a bit overwhelming for a Cache’ beginner. 

If you are converting a MultiValue application from another MV implementation, then primary keys and their names convert as you would expect, so this only applies once you want to expand into the wealth of add-on features that Cache’ brings to the table for a MultiValue developer. If you were to use a variety of tools for the variety of work we can do using Cache’ alone, then you might have a similar, if not more complex, scenario.

1. @ID.

If using the Cache’ MultiValue Query Language, CMQL, from the colon prompt or in an MV select statement executed from MVBASIC, you can always refer to the primary key for a file as the @ID in our application. It is not a Property in a persistent Cache’ Class, however, which means it is not a Property we can reference anywhere outside of CMQL.

In the case of auto-incremented files, our Cache’ class for that file does not generate the @ID in the DICT automatically (rats), so we force that field into the DICT by coding it in an XData MVAdditionalDictItems section of the persistent class.

2. PartyId or similarly named key

We have coded our metadata source in Cache’ classes, so that each key has a synonym that is typically, but not always, the file name, sans package name, followed by Id. We have PartyId, ProfileId, OrgId, and AddressId, for example. An example of where it is otherwise is in the file that is keyed by Email, which is an e-mail address, one of the non-numeric primary keys.

There is a big caveat with this name for the primary key. In an auto-increment class it is also specified along with the @ID in the XData MVAdditionalDictItems section of the class. That means it is not an actual Property of the class. It is a Property when the @ID is not an auto-increment key, but otherwise it cannot be referenced within Zen pages or other Cache’ classes. It is then secret metadata that only CMQL knows about, just like the @ID. You can likely guess that I’m not delighted with that approach, but I can roll with it.

3. ID

Think of ID in SQL as a synonym for @ID in CMQL. 

4. %ID

For our software, we use ID instead of %ID in any SQL code, although these will function the same for us. There are a couple of cases where these would give different results, but we have no such cases in our application, so, for us, %ID returns the same results as ID in an SQL statement. Don’t use %ID in SnupNow–think of it as something we translate to ID when we see it in any examples or documentation.

5. %id

Use %id only with Zen M-V-C and only with a Model, whether a persistent class or otherwise, where the primary key is an auto-increment key. In those cases, we could not give the primary key a name except for the DICT projection, so Zen gives it the name of %id.

I wish I could say that where it is @ID in CMQL and ID in SQL, it is %id in Zen M-V-C, but that is not the case. That is only true with an auto-increment key, otherwise you must use the synonym for @ID that is specified as the primary key property in the persistent class.

This is one case where I would have liked to see a different implementation so that %id would be the name for the modelId no matter how the values of a primary key are determined.

6. modelId

modelId is an attribute of a dataController object, corresponding to a hidden dataController component placed on a page and used by a form on the page, for example. This modelId does correspond to the @ID in all cases. 

7. %Id()

This is a method of %Persistent classes. If you call this method on an instance of a persistent class (a record), you get the value of the @ID for that record. The only place I am currently using this is in Model classes when I need the @ID from a record where the file has an auto-increment primary key. In those cases, we do not have another name for the primary key, so we can retrieve it this way. If we have a P.Party record (object) that we have named, then in our OO-enhanced MVBASIC we might write

partyid =>%Id()


This is related to SQL. We are not using this and it might be specific to COS (Cache’ ObjectScript), I dunno. Maybe someday we will need it. The documentation says “%ROWID is set to the RowID of the last row modified by the most recent INSERTUPDATE, or DELETE operation” which might prompt us to ask for the definition of RowId, but I’m thinking by now you might be exhausted.

I am exhausted and have perhaps exhausted this topic, although we haven’t even gotten to %XML.Id. That is fine, however, because, PTL, it is NOT another name for our @ID.

May I see your @ID?

My sister got carded around the time she turned 40, and they were not carding everyone. She might not want you to know she is over 40, but heigh ho, it’s pretty cool that someone might think she was underage at this point. With two adorable little boys, she still looks young and hip (too hip to use that term, I’m guessing). She definitely looks younger than I, and, well, she is. She says that she used to think that she was the cute one and I was the smart one, but now with her living in sunny Pasadena and me living in Iowa, she realizes that she is also the smarter of the two of us. Of course, she would have said “smartest.” 

Dawn, Mom, Beth

Dawn, Mom, Beth

I have almost never been carded. I guess I drank even less when I was younger than I do now, when I have a drink perhaps 6 times a year, give or take 4. With different allergies to at least beer, wine, gin, and tequila, count me in for the vodka or rum drinks, with an occasional white wine. You might think I have no good point here, other than to chitchat before talking about primary keys, but there really is a good reason to bring this up. Because the audience for this blog is small, I have met or will meet many of those reading this, and I mentioned a martini earlier, but now there might be a better chance you will buy me a vodka martini with 3 olives. In my role as a preacher’s wife, this is a very rare occurrence.

The ID I show when flying or were I ever to have been carded is my driver’s license. The unique identifier on it has 3 digits, then two alpha characters, then 4 more digits, although that pattern varies across the planet, even from state to state within the U.S. Let’s store information about a bunch of these driver’s licenses. We can do that by creating a database table, or, in our case, a (logical) MultiValue file. 

In the relational model there are candidate keys, those combinations of attributes that constitute a unique identifier for a relation. These are implemented as one or more columns in a table. In many DBMS implementations, one of these candidate keys, composed of one or more columns, is selected as the primary key for a table. 

In a MultiValue database, each File has a primary key that is a single field. This is typically referred to as the @ID. Not only can an MV file be modeled as a mathematical relation, just as tables are in a relational database, it can be modeled as a function. Functions, in mathematics, are relations with special characteristics, but I won’t bore you with too many details. A File named Drivers.Licenses is a function in that Drivers.Licenses(@ID) = @RECORD where @RECORD is the rest of the fields, other than the primary key. With Drivers.License as our function, Drivers.Licenses(“123AB4567”) = (“Mary Smith”, “F”, “01/01/1960”,…), a tuple of information related to the drivers license with license id 123AB4567.

The @ID is a single field, but it might be composed of multiple parts. So, where one might have a composite key in a relational database table, this would turn into a multi-part key in a MultiValue file, typically with parts of the key separated by a delimiter, such as an asterisk. We don’t need that for this example. We have a single field, LicenseId, that can be our primary key, aka our @ID. 

In this example, I have used what is called a natural key in that the driver’s license identifier, LicenseId, is created outside of our system. We could, alternatively, have selected a surrogate key, one we generate within our system. What might prompt us to model our data with a surrogate key for our Drivers.License file? 

1. Ease of implementation I

Cache’ gives an easy means of creating an auto-increment key, one where the first one written is 1, the next one is 2, and the 537th one is 537. In other words, using a surrogate key in Cache’ is a piece of cake.

 To get this feature packaged in, we need to write records using object syntax of newkey = myrecord->%Save(). Since we are using the class definition as the source for our metadata, if we specify no primary key index on any property in the class, then it is assumed that the primary key is an auto-increment key.

As best I can tell, use of an auto-increment key in Cache’ is almost assumed, so that if you use a natural key for an MV file in Cache’ someone might tell you that you are overriding the id. Don’t punch them out if they say that, even if that is your inclination.

2. Ease of implementation II

In the Zen AJAX framework, using Model-View-Controller, when you refer to the %id in a page with a dataController, it refers to the primary key if and only if the key is an auto-increment key. In your XML specification for a form field, for example, you can specify dataBinding=”%id”. Otherwise you are on your own for figuring out the name of the primary key. There is no general name for the unique identifier for a model class.  

If I am struggling with some of the basic Create-Read-Update-Delete (CRUD) features in a Zen web page, it is likely that the Model I am using is keyed with a natural key. Few examples in the Cache’ documentation or sample namespace are done with anything other than an auto-increment primary key.

3. Bitmap indices

We can create way-cool bitmap indices for Cache’. While I have not yet defined any of these (who optimizes for performance before a system is live, eh?), I anticipate these could be very helpful in due time. As the Cache’ documentation indicates, “bitmap operations are optimized for transaction processing: you can use bitmap indices within tables with no performance penalty as compared with using regular indices”.

One requirement for these is stated as “you can only define bitmap indices in tables (classes) that either use system-assigned numeric ID values, or use an IDKEY to define custom ID values when the IDKEY is based on a single property with type %Integer and MINVAL is greater than 0”. There you have it, we have this added feature available for files that have numeric keys. 

4. The Usual Reasons for Choosing a Surrogate Key

Of course there are the usual reasons for using an auto-increment or system-generated key that is not the same as the number on a physical object, such as a driver’s license. The argument between selecting a natural or surrogate key is well-worn. I am more likely to argue for a natural key when the decision seems to be a toss-up. 

What do we do if we find that everything about the driver’s license was entered properly except for the LicenseId? Unless we write tools to do otherwise, that would be a delete and re-enter scenario if the key is the LicenseId, but would be a piece of cake if the LicenseId were just another field like the Gender.

Of course, if this is not really a Drivers.License file for tracking driver’s license information, but one where we want to track a person so that they can change their driver’s license number and we want to reflect that change here, then we would also not want the LicenseId to be the primary key.

Some of those are better reasons than others for choosing a surrogate key. Although I look first for natural keys, with our SnupNow software we have more files that are keyed with an auto-increment key than we might have were I not to have adjusted the MultiValue modeling skills acquired over my prior years of experience. Of those files we have started using in our builds, 9 out of 16 [16 files translates to ~50 relational tables] have an auto-increment key, with some of the others being co-files of these, in that the value of their key is the same as the auto-increment key in a sister file. I can only think of two files I specified with alpha characters in the key. Obviously the Email primary key includes alpha characters.

I am not typically a fan of creating unique identifiers for unique identifiers, surrogate keys when there are obvious natural keys, but there were not many natural keys for this application anyway, with Email being an exception. I would have had more multi-part keys if implemented in another MV environment, however. I steered away from some of those simply because multi-part keys are typically not numeric, having an alpha delimiter between the parts. I created two multi-part keys with no delimiter, but with the second part of the key being a fixed length so we can find the parts without a delimiter.

Since this is the first Cache’ implementation for which I have done any data modeling, time will tell if I made good primary key choices. A more accurate way to state that might be to say that time will tell which were the poorest choices I made regarding primary keys. I don’t recall discussions among developers where we talk about how delighted we are with a particular choice for a primary key, but I certainly can recall times when what previously seemed a good choice has turned out to be a lousy one, typically due to new requirements.

If I were a purist, or maybe even an excellent software engineer, I would state right now that when we see that an @ID should be redefined, we will refactor for the change right then and there so we do not perpetuate poor design choices as we add to the software. Truth be told, however, I would be ID’d as a pragmatist and a business woman, so any such decisions will be made given the cost and benefit information we have at the time.

I will be continuing this topic, with the next article documenting the various names for the @ID within our development environment. If you thought this one was getting a little too detailed or technical, you should definitely avoid the next one. They can’t all be winners to all types of people. In fact, before I let you read that one, may I see your @ID?