In "An Introduction to Maintaining Referential Integrity in Domino," I gave you an overview of Domino and referential integrity, which included brief descriptions of a few techniques for maintaining Domino data consistency. In this article, I will dive deeper into these techniques. Four topics are covered: record locking, creating unique identifiers, normalization techniques, and working with replication and/or save conflicts.

Record Locking

Introduce any diehard RDBMS developer/administrator to Domino and the inevitable topic raised is record locking. That is, how do you keep others from tainting data during simultaneous access? Of course, this is a standard feature of relational systems like Oracle, DB/2, and Sybase, but it doesn't transfer to the connectionless architecture of Domino.

The Lotus development language, LotusScript, provides one solution to this problem, but it is appropriate only in a constantly connected environment. It's operational with a server-based application and is a good approach for applications with scores of remote users. The locking of records (documents) occurs in real time. Disconnected users do not work in real time, so the logic is broken. Here is a closer look at the approach:

The Domino development environment is event-driven. Various events are fired when a database opens and closes; when views open, close, and refresh; when documents open or close; and so forth. Documents are locked when opened in edit mode. The document is unlocked upon closure.

This approach uses two Domino form events: QueryOpen and QuerySave. The QueryOpen event fires before the document is opened and presented to the user. The QuerySave event is triggered upon the saving of a document. Any code associated with the QuerySave event is executed before the document is actually saved. Figure 1 shows the standard QueryOpen event in Domino Designer with the associated LotusScript code. The code's purpose is to let a user know when a document is already in use.

Figure 1: Domino Designer allows you to create and associate code with a document's QueryOpen event.

Figure 2 contains the LotusScript code for the QueryOpen event. A simple flag variable is used to signal document locking. A value of one (1) is locked, and zero (0) is unlocked. The flag is set to one during the QueryOpen event when the document is opened in edit mode. The value is set to zero in the QuerySave event. There is one caveat in the QueryOpen event: You must determine if the document is already locked.

Figure 2: Through the document's QueryOpen event, this code updates the lock status or alerts the user when the document is being used by someone else.

Next, unravel the QuerySave event (see Figure 3). The QuerySave event code resets the lock flag variable if necessary. It abruptly halts saving if the document has not been properly locked. The QuerySave continue variable determines if saving occurs. This variable is set to false if problems exist. Record locking is only one aspect of data integrity. Another aspect, usually found in relational systems, is unique key values.

Figure 3: Through the document's QuerySave event, this code updates the lock status or prevents a locked document from being saved.

Creating Unique Identifiers

Keys, either primary or secondary, are very important to relational systems. They identify a chunk of data (row) throughout a system. Domino has somewhat of an equivalent in the form of the document ID. The Notes Document ID is a 32-bit hexadecimal number that uniquely identifies a document across all replicas of a database. This value is created when a Notes document is created. It never changes during the life of the document. The copying and/or pasting of a document are seen as a new document by the system; therefore, a new document ID is created.

The document ID is accessed by way of the @DocumentUniqueID function; it must be combined with the @Text function for proper display and storage. The following combination of the two functions is demonstrated:

This combination of the two functions is used to store the value in a form field for later use, or it can be stored in related documents to create a relationship or connection. Additionally, the document ID can be used in @DBLookup/@DBColumn formulae.

Another function provided in the Lotus development environment is the @Unique function. It generates a random, unique text value. It offers no guarantees of uniqueness across a database and its replicas. Now, turn your attention to data normalization in Domino.

Normalization Techniques

Normalization may be achieved in Domino by way of accepted normalization techniques and the Domino @DBColumn/@DBLookup formulae. These functions allow values to be retrieved from a Domino database by key value, view column number, and so forth. This allows the breaking of data along logical lines so it can be reused in a consistent manner throughout the enterprise. Figure 4 contains a quick breakdown of the @DBLookup formula.

Figure 4: The @DBLookup function can be used to retrieve documents through a key value.

A good example is a customer order system. One Domino database may contain customer information, another may contain order information, and still another may hold state/location information. The customer information database uses the state/location database when entering a customer's address information. Another database could be the merging point for all other databases; formulae are used to retrieve customer information. Also, ordered items are brought from another database.

Replication/Save Conflicts

The robust support for remote workers is one of the most important aspects of Domino, but it introduces problems in ensuring data integrity. This stems from the fact that one or more disconnected users may edit the same data. The next time these users replicate with the server, a save or replication conflict occurs. I will not delve into the details of these conflicts (see the article I mentioned earlier for more information), but the process of clearing these conflicts is left to the database administrator. Unfortunately, there is no magic wand provided by Lotus for addressing conflicts. I will cover two approaches to handling conflicts: programmatically and with a view.

The LotusScript language provides access to various nuances of a Domino database. The NotesViewEntryCollection class allows a programmer to work with elements of a view (such as documents, categories, and responses). The NotesViewEntry object represents each entry; this object has numerous methods and properties. The IsConflict property is a Boolean (true/false) value signaling whether the entry is a conflict or not. The utilization of this property while traversing every element in a view allows conflicts to be easily removed. The code contained in Figure 5 achieves the task at hand.

Figure 5: Through the IsConflict property of the NotesViewEntry object, this code automatically removes document conflicts.

The previous example allows quick cleanup of conflicts, but you may wish to review the conflicts before their deletion. Another approach to these dandies is the creation of a special administrative view displaying the conflicts. To set up such a view, first create a new view using the selection formula:
SELECT @IsAvailable("$Conflict")

• The first column should use the formula above; check Show Twistie, Categorized, and Ascending options in the column info box.
• The second column can be your choice (for example, name of document).
• When you open the view, if you have no replication or save conflicts, you will see only one category called 0; this will show all the documents.
• If you see a category appear titled 1, this will list any Replication/Save documents.

The result is a one-stop shop for conflicts in a database. It still requires manual intervention, but saves some time with its organization.

Although Domino is not a relational database, it is a very powerful development environment. It provides a myriad of tools such as LotusScript and built-in functions for mimicking relational systems. Other tools not covered in this article include Java (as an alternative to LotusScript) and enterprise integration (Lotus Enterprise Integrator [LEI] and Domino Enterprise Connection Services [DECS]). Domino provides all the elements for building applications to meet your needs.

Tony Patton works with various technologies such as Java, XML, HTML, and Domino. He is the author of Practical LotusScript and Domino Development With Java, both available from Manning Publications (www.manning.com). You can reach Tony via email at This email address is being protected from spambots. You need JavaScript enabled to view it..