Managing the HP3000 Part 19

⇓ ⇐

The HP 3000--for Complete Novices
Part 19: The Heart of IMAGE/SQL: Schemas

by George Stachnik

In our last installment we learned that the creation of an IMAGE/SQL database begins with the creation of a schema. A schema is an ordinary ASCII file. Database designers define the structure of each IMAGE/SQL database using a schema. A schema can be created, viewed, and edited using any ordinary editor. You can use an HP 3000-based editor or (if you're using a PC with a client-server connection to the HP 3000) a PC-based editor such as the Windows Wordpad or Notepad.

Before we launch into the details of coding a schema, you should know that everything we discuss in this article is covered in the fine IMAGE/ SQL manuals available from HP. A good place to begin learning the finer points of IMAGE/SQL database design is with Getting Started with HP IMAGE/SQL. The latest version is available from HP (Customer Order Number 36385-90008). You should also take a look at the TurboIMAGE/XL Database Management System Reference Manual (Customer Order Number 30391-90001). Both of these can be accessed online at no charge by visiting //docs.hp.com.

To begin with, there are four basic IMAGE/SQL definitions that we must learn. These are shown in Table 1. If you have been following our discussion of files on the HP 3000, then you'll find these IMAGE/SQL terms very similar to corresponding terms from the file system world.

***Table 1: IMAGE/SQL Definitions***
IMAGE/ SQL Term	Definition	Corresponding File System Term
Data Item	A string of bytes containing a piece of data. For example, a name, an address, or a numeric value.	Field
Data Entry	A collection of related data-items	Record
Dataset	A collection of related data-entries	File
Database	A collection of related datasets

In IMAGE/SQL terminology, the idea of a data item is pretty much the same as the idea of a field in an ordinary file. Each data item contains one piece of information. For example, a name, a part-number, or a price might be stored in a data item.

A collection of related data items is called a data entry. All the data items in a data entry are physically stored together, just as all the fields in a file system record are physically stored together. But that's where the resemblance between records and data entries ends.

When data is stored in records (i.e., in the file system), application programs must access the data in exactly the way it's physically stored. By contrast, IMAGE/SQL allows each application program to be independent of the physical layout of the data entries. We touched on this in the last installment, and we'll explore it in more detail in future articles.

A collection of related data items is called a dataset. Datasets are analogous to files. Like files, they can be accessed sequentially (one data entry at a time), and directly (keyed by relative record number). Unlike ordinary files, datasets can also be accessed using a key--so you can find the record that contains a particular key-value without having to search through the entire dataset. IMAGE/SQL datasets share this capability with KSAM files, but keyed access to IMAGE/SQL datasets is much faster than KSAM.

Each IMAGE/SQL dataset is physically stored on the HP 3000 as a file. The :LISTFILE command will display the filenames of your IMAGE/SQL datasets, just as it lists the names of ordinary files--but once again, that's where the analogy ends. File system commands such as :PRINT, :COPY, and :PURGE cannot be used to access data stored in an IMAGE/ SQL database. To get at the contents of an IMAGE/ SQL database requires the use of IMAGE/SQL's own utilities and commands, which we'll explore in the future.

Each IMAGE/SQL database is a collection of datasets that are related to one another according to rules defined in the database's schema. We'll begin looking at those rules next.

Schemas

An IMAGE/SQL schema is an ordinary ASCII file that contains a description of every data item in the database, as well as a set of relationships among those items.

Each data item is stored in the database using a specific data type. Data items can be made up of alphanumeric characters or binary numbers in a number of different formats. For the most part, IMAGE/SQL's data types correspond to the data types supported in popular programming languages such as C, Basic, and (especially) COBOL.

IMAGE/SQL also allows you to define subitems within an item. This allows you to construct arrays of items within a data entry (in much the same way that COBOL uses the OCCURS clause to do the same thing with file system records).

Each schema also includes security information relating to the data items. IMAGE/SQL security can be used to control which users or programs are allowed to access each individual item in a database.

We aren't going to explore all the data types supported by IMAGE/ SQL in this article. The details can be found in the manuals. For now, we will look at one or two of the most popular data types as we explore a sample schema in detail.

Database Structure

Each IMAGE/SQL database is made up of a combination of four kinds of datasets:

Root dataset (one per database)
Detail datasets
Manual Master datasets
Automatic Master datasets

Figure 1 contains a sample schema that we'll refer to throughout this article. This schema describes and defines the structure of a database called ORDERS. This database is made up of three datasets called CUSTOMER, DATE-MASTER, and SALES. The schema shown in Figure 1 contains definitions of all the data items that are part of the database, as well as all the information needed to create it.

To create an IMAGE/SQL database, the first thing you'll need to do is to decide the details of the database's structure. Then you'll use a text editor to create the schema that corresponds to this structure, and save this file on your HP 3000. When you save the schema, you can assign it any filename you like--in any group you please. The only restriction is that you'll want to avoid filenames beginning with the database name itself.

For example, the database described in Figure 1 will be called ORDERS. When you create a database using this schema, IMAGE/ SQL will give the filename ORDERS to the root dataset. It will also use the filenames ORDERS01, ORDERS02, and so on for the various detail and master datasets. So to avoid confusion, it's good practice to avoid calling the schema by these names.

A program called DBSCHEMA reads the schema as an input file, and creates the database using the information found there.

Parts of the Schema

All schemas are divided up into five sections:

BEGIN
PASSWORDS
ITEMS
SETS
END

Next we're going to explore each of these sections. You may want to follow along in Figure 1 as you read the remainder of this article.

The BEGIN Statement

Every schema begins with a BEGIN statement. The format of this statement is:

BEGIN DATA BASE ORDERS; << CUSTOMER ORDERS >>

The character string "BEGIN DATA BASE" identifies this as a BEGIN statement. It is followed by the name of the database that we are going to create. In this example, the database will be called ORDERS. The semicolon (;) at the end of the database name is required; it terminates the BEGIN statement.

The IMAGE database management system dates back to the early days of the HP 3000, when filenames could be no more than eight characters long. When you create the ORDERS database, IMAGE/ SQL will create a number of files with names that begin with the database name, such as ORDERS01, ORDERS02, and so forth. For this reason, IMAGE/ SQL will not allow you to use a database name that's more than six characters long (since it needs the remaining two characters for the extender 01, 02, and so forth). Of course, modern HP 3000s support the POSIX environment, which allows longer file names, but the six-character restriction for database names still remains in order to maintain compatibility with earlier versions of IMAGE.

In our example, we have followed the database name with the string

<< CUSTOMER ORDERS>>

This is a comment. Our sample schema will include many comments to help you understand how schemas are used. When you begin creating schemas of your own, don't forget to include comments so that other people will be able to understand your schemas. Just remember that your comments will be ignored by IMAGE/SQL and the HP 3000.

The PASSWORDS Section

The BEGIN statement of the schema is followed by the PASSWORDS section. This is where you will define security information that relates to your database. You already know that the MPE file system has security restrictions which you can use to restrict who has access to what files. IMAGE/SQL databases are implemented within the MPE/iX file system. This means that in order to access an IMAGE/SQL database, you must be authorized by the file system (i.e., have appropriate access at the account, group, and file levels) and by IMAGE/SQL itself.

The PASSWORDS section of your schema is where IMAGE/SQL's security rules for your database are defined. This section begins with the character string "PASSWORDS:" (don't forget the colon at the end).

It's been said that the PASSWORDS section of the schema is the most difficult one to get right. It's not that the code is particularly complex--it's actually quite simple. The tricky part is that you must give a good deal of thought to how the database will be used before coding the PASSWORDS section.

For the purposes of this article, we will assume that the ORDERS database will be accessed by three groups or classes of users. This will become clearer when we get to the ITEMS part of the schema. For now, just remember that you must mentally divide the users of your database into classes, based on the kinds of access they will require to the various data items that will be stored there.

Each class will be identified by a number, and by a corresponding password. You'll code the numbers and passwords in the schema. Here is an example:

PASSWORDS:

4 CLERK;   << SALES CLERK >>

8 SUPER;   << SUPERVISOR OF CUSTOMER ORDERS DEPARTMENT >>

15 DO-ALL;  << PROGRAMMER/ANALYST - CREATOR OF DATA BASE>>

We've chosen to divide our user community into three classes called "Clerks," "Supers," and "Do-Alls." We've assigned them user class numbers of 4, 8, and 15 respectively.

You have probably guessed from our choice of passwords (not to mention the comments) that DO-ALL users have unrestricted access to our database, and that SUPER users have slightly less access, and that CLERK users have the least access of all. When we code the ITEMS section of the schema, we will define what DO-ALLs, SUPERs, and CLERKs can (and cannot) do.

There's no significance to the values of the user class numbers that we've assigned. That is, a user class number of 15 is not in any sense "better" than a class number of 4. The numbers are totally arbitrary. Also, don't forget that any text that's enclosed in angle brackets ("<< ... >>") is only commentary and ignored by the system.

In order to open an IMAGE/SQL database, a user or program will have to specify one of the passwords defined in the PASSWORDS section of the schema. The password will assign him or her to one of the user classes. And this, in turn, will determine what kind of access the user or program will be allowed to the various items in the database, according to rules that will be defined in the ITEMS section of the schema.

The ITEMS Section

The next section of the schema is the ITEMS section. The ITEMS section defines each and every data item in the database. Once again, an example will serve to explain it: Figure 2 shows part of the ITEMS section from our sample schema. It begins with the character string "ITEMS:" Once again, the colon (":") is required, and the character string "<< (READ/WRITE) >>" is just a comment. This is followed by a description of each item in the database. In the fragment shown, we have defined four items, and named them ACCOUNT, CITY, DATE, and DELIV-DATE. The remainder of the ITEMS: section is shown in Figure 1.

Figure 2: ITEMS Section Excerpt

ITEMS:              << (READ / WRITE) >>

ACCOUNT,        X6   (4,8,15/8,15);    << CUSTOMER ACCT=AA-NNN >>
CITY,           X14  (4,8,15/8,15);    << CUSTOMER CITY        >>
DATE,           X6   (4,8,15/8,15);    << DATE = DDMMYY        >>
DELIV-DATE,     X6   (4,8,15/8,15);    << DEL. DATE = DDMMYY   >>

Each item name is followed by a comma, which in turn is followed by the item designator. This is a character string that describes the allowable format of the data item. For example, the item called ACCOUNT has been described using the string "X6."

The "X" indicates that the ACCOUNT data item can be made up of alphanumeric ASCII characters. The "6" indicates that this item is six bytes long. So, for example, the character strings "123456," "ABCDEF," and "AaAaAa" all conform to the description "X6." But "12345667" does not (it's too long) and therefore IMAGE/SQL will not allow this value to be stored in the ACCOUNT data item.

It's worth noting that the comment that follows the description of ACCOUNT suggests that this item is for a "CUSTOMER ACCT" of the form "AA-NNN." You might interpret this to be a character string of two alphabetic characters followed by a hyphen and three numerics (for example, "XY-345"). But remember, this is just a comment. The schema that we have coded (specifically the "X6") instructs IMAGE/SQL to allow this data item to contain any string of six ASCII characters. If you code logic elsewhere in a database application that further restricts the contents of ACCOUNT to a particular form, then that should be reflected in the comments, just as we have shown in our example above. But it doesn't restrict what an application program may or may not decide to store physically in your database.

It's also worth noting that the descriptions of the date fields shown in the example are hardly Y2k compliant. A future article in this series will address Y2k compliance for IMAGE/SQL databases.

IMAGE/SQL allows you to describe data items using a variety of descriptors aside from the "X" that we've just discussed. For example, the sample schema shown in Figure 1 includes a number of items that are described with a "J," which indicates a "packed decimal" data item (what COBOL calls a "COMP-3" data item). A complete description of all the data types allowed by IMAGE/SQL is beyond the scope of this article (see the manual), but in a future article we'll address some of the most troublesome gotchas surrounding IMAGE/SQL. Some of these result from loading IMAGE/SQL databases with data that does not correspond to the data item descriptions defined in the schema.

Read and Write Access

The item description in the ITEMS section of the schema is followed by a READ/WRITE list for each item. These lists are made up of class numbers from the PASSWORDS section.

The items shown in Figure 2 all have the same READ/WRITE list of:

(4,8,15/8,15).

When a user or program opens an IMAGE/SQL database, a password must be supplied. IMAGE/SQL uses the password to assign this user or program a user class number from the PASSWORDS section of the schema.

Using the sample schema code, we can determine the following three rules:

A user who opens this database with a user class number of 4, 8, or 15, that is, using one of the class numbers that appear to the left of the slash ("/"), will be allowed to READ the data contained in the items ACCOUNT, CITY, DATE, and DELIV-DATE.
If a user opens this database with a user class number of 8 or 15, i.e., using one of the class numbers that appear to the right of the slash ("/"), then that user will be allowed to WRITE (update or change) the data in this item.
If, on the other hand, this database is opened with a user class number of 4, i.e., using a class number that does not appear to the right of the slash, then that user will not be allowed to change the data in this item.

When we created the PASSWORDS section of this schema, we defined only three user classes: 4, 8, and 15, corresponding to the passwords CLERK, SUPER, and DO-ALL respectively. This means that the ITEMS section shown in the example can be described as follows:

Any user who opens this database using class number 8 or 15, i.e., using a password of SUPER or DO-ALL, will be able to read or write any of the three items shown in the example. But a user who opens this database using a class number of 4 (using a password of CLERK) will only be able to read these items.

Our example is very simple. In a real commercial application program, you would very likely have many more classes of users, and much more variation in the levels of item access than that shown here. For example, note the item called CREDIT that's defined in the sample schema shown in Figure 1. It has a read/write list of:

(8,15/8,15)

How would you interpret this? Work it out for yourself before you look at the answer (which is at the end of Figure 1).

Our look at schemas will continue in the next installment of the series.

George Stachnik works in technical training in HP's Network Server Division.

←Part 18 Part 20→

The HP 3000--for Complete Novices Part 19: The Heart of IMAGE/SQL: Schemas