Example of Creating a Hyper File

The following example covers creating a simple hyper file assuming (initially) two existing files each with one index.

While a created hyper file allows for multiple physical data files to be accessed as items in one file, there needs to be a way of determining in which physical data file an item belongs. This is achieved by using a dictionary definition combined with a lookup table to map all or part of every item ID (the hyper key) to the physical data file (the hyper section).

The definition of a hyper file is very flexible. There is almost no limitation on how hyper keys might be derived from item IDs as long as every item ID can be processed in the same way. Also, if you intend your hyper file to use a range lookup table, it must be possible to sort hyper keys in ascending order. So, for example:

If your item IDs do not already include information from which the desired hyper keys can be consistently derived, they must be restructured to do so before the hyper file can be created.

Creating the SALES hyper file: a worked example

In our example, we shall assume there is an existing set of files SALES_2014, SALES_2015, and so on for each trading year, and that their item IDs are of the form: SL-YR-nnnnn, where YR is a 2-digit year, and nnnnn is 5-digit transaction number starting at 10001 each year; that is, SL-14-10001 onwards in the SALES_2014 file, and SL-15-10001 onwards in SALES_2015 file.

For this item ID structure you just need the following YR dictionary definition in all files to extract the YR value:

Attribute Value Description

Item-id

YR

 
  001

A

Specifies that the item is used to format an attribute.
  002

0

Denotes that this description applies to the item ID.
  003

YR

 
  004

 

 
  005

 

 
  006

 

 
  007

 

 
  008

G1-1

A group extraction code to extract the (first) string delimited by a pair of hyphens.
  009

R

Denotes right-aligned.
  010

2

Specifies a maximum of 2 characters.

Assuming each file SALES_2014, SALES_2015, and so on has a CUST dictionary definition citing Customer number in attribute 1 then the hyper file can be configured to access these as a "joined up view" index across all the physical data files transparently to users and applications; that is, English, TCL, DataBasic, and so on.

Attribute Value Description

Item-id

CUST

 
  001

A

Specifies that the item is used to format an attribute.
  002

1

Denotes that this description applies to attribute 1.
  003

Customer

 
  004

 

 
  005

 

 
  006

 

 
  007

 

 
  008    
  009

L

Denotes left-aligned.
  010

10

 

We further assume that the SALES_2014 file already had a simple index created from the CUST dictionary definition by using the CREATE-INDEX command:

CREATE-INDEX SALES_2014 CUST

And similarly for the SALES_2015 file:

CREATE-INDEX SALES_2015 CUST

A hyper file called SALES can now be created by using the HYPER-CREATE command (as described below):

HYPER-CREATE SALES

Finally, the index can be added by the HYPER-ADD-INDEX command:

HYPER-ADD-INDEX SALES CUST

Now, if any application wants to access sales items for all years they just reference the SALES hyper file like a normal file using DataBasic, English, TCL, and so on. Items can be read, updated and deleted as required, with Reality maintaining in which physical file to store each item.

Each physical file, SALES_2014, SALES_2015, and so on can be in the same or different databases, even on remote system databases. Providing there is a common index for all physical data files the SALES hyper file can make use of them, accessing each index and joining them as one for use by English, TCL commands and application code.

Subsequently, if a new year is added to the hyper file, like SALES_2016, applications and users can either open the SALES hyper file and items will be filed in this new physical data file based on item IDs of the form SL-16-nnnnn, or any program or user at TCL can access each year as SALES_2014, SALES_2015 and SALES_2016.

A commentary on the HYPER-CREATE command

First, you are reminded that a predefined data definition item to extract hyper keys from item IDs is a prerequisite.

:HYPER-CREATE SALES 

NOTE before creating a hyper file you must have a data definition
item that can be used to extract one or more fields from an item ID.
This will be used to generate a key to be looked up in a table, which you
must supply as part of creating the hyper file, to identify which file an
item goes in.
Enter file name to use for data definition item ? SALES_2014

This can be any file that contains a suitable dictionary definition.

Enter name of data definition item to be used for item ID look up ? YR 

This is the predefined data definition item described above, that extracts the string delimited by a pair of hyphens from each item ID to form the item's hyper key.

Do you want a table where a range of item IDs can
map to one section Y/N [N] ? ¿

In this example we can use a simple lookup table, where each hyper key value maps directly to a corresponding hyper section.

Do you want right-aligned fields to be treated as left-aligned Y/N [N] ? ¿ 

Left-alignment is usually necessary only when using a range lookup table, although this may also depends on how the key value is defined.

Use hyper file as item lock container Y/N [Y] ? N  
Update lock all hyper sections Y/N [Y] ? N  

By default, associating item and update locks with the hyper file means that the hyper sections cannot be updated except though the SALES hyper file. But if you want to be able to update the hyper sections independently, you can override these defaults.

You must now enter the lookup table used to resolve items to files.
Enter value to match hyper key  ? 14 
Enter section name [SALES_14] ? SALES_2014 
Using existing section SALES_2014

The first lookup table entry maps the key value 14 onto the SALES_2014 file. Note that the command suggests a name for a data section to be created in the SALES hyper file, based on the specified key value, but this can be overridden as we know the file we want already exists.

Enter value to match hyper key  ? 15  
Enter section name [SALES_15] ? SALES_2015  
Using existing section SALES_2015

This second (and, in this example, final) lookup table entry maps the key value 15 onto the SALES_2015 file.

Enter value to match hyper key  ? ¿ 

Press Return to signal that there are no more lookup table entries.

Add BIN section to take unidentified items Y/N [Y] ? ¿ 
Enter section name for BIN section [SALES_BIN] ? ¿ 
Enter the modulo to use when creating SALES,SALES_BIN [7] ? ¿ 
[417] File 'SALES_BIN' created.
D code =DL, modulo = 7, separ = 1

Here we specify that any items that cannot be mapped by the lookup table are to be placed in a new BIN section of the SALES hyper file, accepting the suggested SALES_BIN file name. For example, an item ID of SL-13-10511 produces a hyper key value of 13, which has no entry in the lookup table and therefore this item belongs in the BIN section. If there were no BIN section such items would cause a fail when an attempt was made to write to the item; although triggers could be used to warn or correct such errors, a BIN section is recommended.

   0) Data def: BY YR (H
      Right justified
      Ascending
      Item-lock using Hyper File as container

File table specified as:

   1)  14  SALES_2014,SALES_2014 
   2)  15  SALES_2015,SALES_2015 
   3) BIN  SALES,SALES_BIN

Enter field number or A-Append, D-Delete, <RETURN>-Continue ? ¿  
Create HyperFile SALES,SALES using above definition Y/N [Y]: ? ¿ 

The HYPER-CREATE command summarises what you have specified so far, including the lookup table, and gives you the opportunity to change anything. Press Return twice to accept the definition and create the SALES hyper file.

Accessing the SALES hyper file

Here we briefly look at how we can access our example SALES hyper file and its constituent hyper sections — the physical data files SALES_2014 and SALES_2015. We assume the presence of suitable customer, product and price data.

So, for example, by using the English SORT command on the SALES_2014 and SALES_2015 files:

:SORT SALES_2014
SALES_2014  Customer.. Product... Price..... YR

SL-14-10001 CUST-1     PROD-1     100.00     14
SL-14-10002 CUST-2     PROD-2     200.10     14
SL-14-10003 CUST-3     PROD-3     300.10     14
SL-14-10004 CUST-3     PROD-3     300.00     14
:SORT SALES_2015
SALES_2015  Customer.. Product... Price..... YR

SL-15-10001 CUST-1     PROD-1     100.00     15
SL-15-10002 CUST-2     PROD-2     200.00     15
SL-15-10003 CUST-3     PROD-3     300.00     15

The existence of indexes can be confirmed by using the SELECT-INDEX command on these files:

:SELECT-INDEX SALES_2014 CUST
4  Items selected.
:SELECT-INDEX SALES_2015 CUST
3  Items selected.

So far, so familiar. But we can also apply these same commands to the SALES hyper file, and the results appear as if the data constituted a single file:

:SORT SALES
SALES.....  Customer.. Product... Price..... YR

SL-14-10001 CUST-1     PROD-1     100.00     14
SL-14-10002 CUST-2     PROD-2     200.10     14
SL-14-10003 CUST-3     PROD-3     300.10     14
SL-14-10004 CUST-3     PROD-3     300.00     14
SL-15-10001 CUST-1     PROD-1     100.00     15
SL-15-10002 CUST-2     PROD-2     200.00     15
SL-15-10003 CUST-3     PROD-3     300.00     15

And as for the index:

:SELECT-INDEX SALES CUST
7  Items selected.

Moreover, we can EDIT the SALES hyper file directly, for example by adding a new item:

:EDIT SALES SL-16-10001
New item
Top
.I
001 CUST-X
002 PROD-Y
003 4.99
004
Top
.FI
'SL-16-10001' filed in file 'SALES'

We can confirm the presence of this new item as follows:

:SORT SALES
SALES.....  Customer.. Product... Price..... YR

SL-14-10001 CUST-1     PROD-1     100.00     14
SL-14-10002 CUST-2     PROD-2     200.10     14
SL-14-10003 CUST-3     PROD-3     300.10     14
SL-14-1000D CUST-3     PROD-3     300.00     14
SL-15-10001 CUST-1     PROD-1     100.00     15
SL-15-10002 CUST-2     PROD-2     200.00     15
SL-15-10003 CUST-3     PROD-3     300.00     15
SL-16-10001 CUST-X     PROD-Y       4.99     16

But in which physical file is the new item stored? In fact it is put into the BIN section because the item ID of SL-16-10001 produces a hyper key of 16, which is not present in the lookup table. We can confirm this by applying the English LIST command to the BIN section file, SALES_BIN:

:LIST SALES,SALES_BIN
SALES,SALES_BIN Customer.. Product... Price..... YR

SL-16-10001     CUST-X     PROD-Y       4.99     16

1 ITEMS LISTED

What we should have done instead is add a new SALES_2016 file complete with index CUST to the hyper file by using the HYPER-ADD command to update the lookup table so that it looks like this:

File table specified as:

   1)  14  SALES_2014,SALES_2014 
   2)  15  SALES_2015,SALES_2015 
   3)  16  SALES_2016,SALES_2016
   4) BIN  SALES,SALES_BIN

Then when we EDITed the SALES file to create the new item it would automatically be added to the SALES_2016 section.