This example is shown to mainly to give an idea of how you might deal with pointers to the raw indexes from within a file.  Clearly, we’d want to simplify this one step further:

Note that we don’t even need the "LOWER" from the basic program; TRANS takes care of that for us.  Apart from the simplicity of this method, we do away with possible SELECT list collisions.  This simple technique takes away so much of the complexity of dealing with indexes that they become a much more viable tool.  If you doubt this, imagine the case where these detail records have indexed sub-detail records, and the header records are contain indexed pointers to a master record.

Cascading I-descriptors

Using the original methods of accessing indexes, you’d need to nest selects within each other to get from school to student. With our new technique, this is accomplished by nesting raw index translates – and because I-descriptors can include other I-descriptors, we only need to write the outer I-descriptor once – for all the files!  The STUDENT.ID I-descriptor we just looked at will work for SCHOOL, TEACHER, STUDENT, and TEST – by redefining TEACHER.ID in each dictionary. 

We translate into the TEACHER_SCHOOL.ID (an index of pointers from TEACHER back to the parent SCHOOL item) using exactly the same technique as we did to get from STUDENT to TEACHER.  Note that we created a copy of @ID called SCHOOL.ID.

Becomes:

We end up with a list of "grandchildren" for the SCHOOL record – all the students in the school.

This is our original example.

We end up with a list of "children" for the TEACHER record – all the teacher's students.

This is very close to our original example.  The only difference is that TEACHER.ID is an attribute of STUDENT this time.

Becomes:

We end up with a list of "siblings" for the STUDENT record – the student's classmates.

We translate into the STUDENT file using a more traditional lookup.

We end up with a list of "aunts and uncles" for the EXAM record – the students in the class of the student who took the test.

You could make a good argument that this reuse of the original I-descriptor providing students for a teacher is an example of object-oriented programming.

Indexing I-descriptors

Note that the dictionary item that we index can be an I-descriptor.  This allows us to use and misuse indexes in some very creative ways.  The create.index command takes a snapshot of this dictionary item, so we’ll need to re-create the index if we change the I-descriptor.  Apart from the dictionary item specifying the key, the no.nulls keyword in the create.index is the most important.  Suppose you were creating an index on email address and two-thirds of your students have email addresses.  Normally, this would mean that if you did an index lookup for an empty email address, you’d get that third of your student file without email. This isn’t a very useful result, and is really bad when you consider that IBM recommends that B-tree items be smaller than 1k for best performance.  Using the no.nulls keyword when creating the index will cause the index logic to ignore items where the index key evaluates to null.

Using NO.NULLS

Here's our index command:

EMAIL.KEY is an I-descriptor:

We convert the email address to lower case to make sure that the sort order is meaningful.  We don’t want to rely on every application putting the data in uniformly. We use no.nulls because many students will not supply an email address (but I bet a marketer somewhere wants a list of them at some point – a job for another less dynamic type of index).

PHONE.KEY is another I-descriptor:

We assume the raw phone number is 7 or 10 digits.  The key we use for a number like (212) 555-3333 is 5553333212.  We store the index keys in sorted, right-justified ascending order.  If the customer doesn’t supply an area code, or the area code has changed since the record was created, we are still in the right neighborhood in this index. You may be thinking that you could write a routine to go look up the appropriate Area code for the customer’s ZIP code at index time.  This won't work because the index is only updated when the STUDENT file is updated, not some ZIPAREA file – and basing keys on external data may mean that it is not possible to generate the right key to update the index.

Generating File Pointers for Secondary Indexes Programmatically

indices is a Basic function that will return a list of Secondary Indexes for a supplied file, or details about a particular index including its path.  We can use this to generate VOC entries for the raw indexes to enforce a naming convention and to quickly respond to changes that might occur in indexes:

Running it on our test data:

Working with Secondary Index Data Directly

Now that we have VOC pointers to the index files, we can do more than a simple translate.  We can read and update the indexes directly.  The Secondary Indexes themselves are ordinary UniVerse records with the index entry as their @ID and the keys to the data records stored one per line.  We can use just about any Dictionary or Basic technique on these records, but any direct updates you make to the index data will corrupt the index.

Reporting Secondary Index Data Directly

Here's an example of a live production report pulled directly from a priority based index controlling a rendering images queue for large scale digital printing:

• Sequence is an I-descriptor @NI which displays the current line number in a report.
• Priority is the @ID of this Queue.
• Process is the digital process type from the First RD
• RDs left is an I-descriptor DCOUNT(@RECORD,@AM) that gives the count of indexed items at this priority.
• Ref/PD is a pointer back to the sales order for the First RD
• First RD is the first attribute - @RECORD<1> - of the index item and it is used to translate over to live data files to get the remainder of the information. 

By default we display the first five lines of the queue (out of 32).  When a renderer process is ready for work, we pull an RD item (08843222 is next) from the front of the queue.  We null out the priority attribute in RD 08843222 and it drops out of the index. These records are generally pulled out of the queue every few seconds.  Our simple report on the queue gives a lot of information in a couple of seconds.  More importantly, we can quickly provide a prioritized queue of work to our renderers.

Here is the code:

Using and Abusing the SET.INDEX command

We've seen how index data for STUDENT is stored in a directory I_ STUDENT.  The path to this directory is stored in the file header of STUDENT.  This file header information is normally populated with the create.index command and cleared with the delete.index command.  It can also be populated or cleared with the set.index command.  This allows us to attach a pre-built index to a file instead of creating and building an index in place.  We can use this to quickly set up an empty or " no.nulls " index without scanning a file or to use a restored version of an index instead of rebuilding from scratch.

SET.INDEX and the NO.NULLS Secondary Index

The problem with this empty index is that it still needs to be built before it is active.  This can be a big problem if you have a large file.  In order to build the index, UniVerse locks the file – opens and writes both fail.  Consider the case where we are adding a new KARMA field to the enormous BIGFILE.

We in the Pick world are spoiled in this regard – adding a field to a file shuts the database down for hours or days in those other databases, but not ours, right?  In the case of UniVerse Secondary Indexes, even though we know that the index on this freshly created field will end up empty, the build.index command will still select and process each record in the file, keeping the file locked the entire time.

We can, however, create an empty file that we'll use as a "backup" of all the records with valid KARMA values – i.e. none.  If we index this file we should end up with the same result as BIGFILE, but right away.  Here's how we do this (with some output suppressed):

Presto – instant KARMA!  Please experiment with your own BIGFILE and KARMA before trying this approach with live data.

Summary

Secondary Indexes simplify the tasks of maintaining referential integrity and managing ordered lists and queues.  The direct access examples and techniques described are a better match to traditional Pick coding practice than the selectindex Basic command.  The various short-cuts to building large indexes should allow more existing applications to take advantage of these indexes instead of continuing with grafting on home-brewed solutions.