02
Sat, Nov
2 New Articles

Implementing Referential Integrity in a Domino Database

Collaboration & Messaging
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

http://www.mcpressonline.com/articles/images/2002/PattonV200.jpg

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.

1. Sub QueryOpen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
2.    If (Mode = 1) Then
3.  If (Source.Document.LockFlag = "1") Then
4.   MsgBox "I am sorry, this document is in use.",0,"Document Locked"
5.   Mode = 0
Else
6.   Source.Document.LockFlag = "1"
7.   Call Source.Document.Save(True, False)
End If
      End If
   End Sub

Explanation

1. The QueryOpen event is declared.  This is a standard portion of all Domino forms.
2. The Mode variable signals whether the document is opened in read (0) or edit (1) mode.
3. Determine if the document is in use (locked) if trying to open in read mode.
4. A message is displayed if the document is already locked.
5. The mode is reset to reading.
6. The document is locked if not currently used.
7. The document is saved to preserve the lock.

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.

1. Sub QuerySave(Source As Notesuidocument, Continue As Variant)
2.    If (Source.Document.LockFlag = "1") Then
3.   Source.Document.LockFlag = "0"
      Else
4.   MsgBox "This document not properly locked.  No saves allowed.", 0, "Error"
5.   Continue = False
      End If
   End Sub

Explanation

1. The QuerySave event is declared.  
This is a standard component/event of all Domino forms.

2. The record locking flag variable is checked to 
determine if it has been previously set.

3. The document is unlocked.
4. An error is encountered if the document is not locked during a save.  
5. The Continue variable controls whether the document is saved. 
The default value is true, so it is set false to stop the saving of the document.

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:

@Text(@DocumentUniqueID)

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.

@DBLookup( 
1.   "Notes":"NoCache";
2.   "Domino Server":"Domino Database";
3.   "View name";
4.   "Key Value";
5.   "Field Name or Column Number");

Explanation:

1. The type of database accessed (Notes) 
and whether lookup information is cached between accesses.  
NoCache signals information is not cached.

2. The second set of parameters signals the Domino server 
and database file path/name of the data to be retrieved.

3. The third parameter is the view used by the lookup.
4. The actual document or set of documents accessed by way of the lookup.  
This corresponds to the first sorted column 
in the view specified in the previous parameter.

5. The field name of the values to be retrieved 
from the document or set of documents returned.  
Also, the column number (in the view) can be used as an alternative.


Note:  The syntax of the @DBColumn formula is similar.

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.

1.  Dim s As New NotesSession
2.  Dim db As NotesDatabase
3.  Dim vec As NotesViewEntryCollection
4.  Dim ve As NotesViewEntry, view As NotesView
5.  Set db = s.CurrentDatabase
6.  Set view = db.GetView("Test")
7.  Set vec = view.AllEntries
8.  Set ve = vec.GetFirstEntry()
9.  While Not (ve Is Nothing)
10.   If (ve.IsConflict) Then
11.   Call ve.Document.Remove(True)
End If
12.   Set ve = vec.GetNextEntry(ve)
Wend
13. Delete vec
    Delete view
    Delete db
    Delete s

Explanation

1. Create a new instance of a Notes session.  
This provides access to the current Notes environment.

2. A NotesDatabase object is created.
3. A NotesViewEntryCollection object is created.
4. NotesViewEntry and NotesView objects are created.
5. The NotesDatabase object is instantiated to the current database 
in which the code is running.

6. The NotesView object is instantiated to the specified view name.
7. The NotesViewEntryCollection object is instantiated 
with all elements from the view.

8. The NotesViewEntry object is instantiated to 
the first entry in the collection.

9. All elements in the collection are traversed using a While loop.
10. The IsConflict property is used to determine 
if the current entry is a conflict or not.

11. The remove method of the NotesDocument object 
is used to delete the document from a database if it is a conflict.

12. The next entry from a collection is accessed; 
the current entry is used as the bookmark.

13. Object cleanup is performed through the use of the Delete command.

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..

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: