Copyright © 2001-2022 Woodgrove Digital Engineering P⁄L. All rights reserved. Last revised:14th December 2022 CREDITS LINKS
Industrial Automation



The Database

Table of Contents

Database Structures

Memory Resident

Data Storage and Presentation

Relational

Fixed Size

Sequencing

No Record Locking

Queries

Database Structures

The database was developed on an IBM-XT personal computer in 1985 for Mobil Oil in Yarraville, Victoria. This database has many unique features, when compared to most proprietary databases. First of all, the concept of tables was a way of viewing the data rather than storing it. Each field from a table was stored contiguously. Say that a table had a column for product code, another for product description, another for the number of units in a carton, another for the number of cartons on a pallet, and so on. Then all the product codes for each of the products would be stored contiguously with one another while their association with those other fields of the table would be defined by the table specification. The intention in this approach was to use the power of the 8086's string primitive instructions which allowed a single assembler language instruction to be executed repeatedly to perform a move, search, or comparison. These instructions are still a part of the Intel instruction set for today's microprocessors.

Memory Resident

The database is entirely contained in memory. In the early days, there was a flag associated with each field, to indicate whether it should be stored in memory or on disc. Most fields were stored in memory, but those associated with the log files would be stored on disc. As the power of computers advanced, this flag was removed and all of the database was based in memory. The sizes of these databases for my warehouse applications range from around 5 megabytes to 40 megabytes. The 40 megabyte database was for Arnott’s Biscuits in Sydney because they insisted upon having log files with 60,000 records and 300,000 records. The size of the database for “The mUser” is 30 megabytes, while for the “The vUser” program it is 5 megabytes.

While contracting to GEC-Plessey Telecommunications for their development of the Local Controller for Telecom's Time-Domain Cross-connect, it was decided that an alteration notice system needed to be put in place. This was designed based on a proprietary database, and when I saw the results, I was disappointed, so I offered to write it based on my own database, which I needed to port to the operating system that we were using there. The resulting operator interface was much more sophisticated and user-firendly. GEC-Plessey Telecommunications were happy with it, and I received a call many years later letting me know that they were still using it.

Data Storage and Presentation

In modern speak, there are methods associated with each field and methods associated with each table. I had found my own approach to object-orientated programming before the concept was popular. The main methods associated with the fields are to translate the data from its form in the database to its form as presented to the operator, and back again, and to assist with the typing in of the data. The database has an integer type and a string type. Integers could consist of 1, 2, 4, 8, 16, or 32 bits, while strings could consist of from 1 to 128 bytes. Eight 1-bit integers would be held in a single byte of the database, and each would typically be presented to the operator as one of two characters. The larger sized integers can be presented in various ways including as fixed-point numbers, where the value 1 might be presented as "0.001", while the value 1000 would then be presented as "1.000". Times (32 bit integers including the date) and dates (16 bit integers) are also stored as integers but presented to the operator as suitably formatted text. In this way the database has remained quite tight.

Initially, the database was declared as “C” arrays of integers of varying size, and two dimensional arrays of “chars” for string fields of the database. This was used to provide the database for the database editor, which would then define its own database and then databases for all of my future warehousing projects.

Relational

This brings us to my approach to relational databases. A pallet load would hold a number of cartons of a given product. There would be a field for the details of a pallet that would indicate which product was contained in its cartons. That field would be a reference to the details associated with the product. The product is likely to be identified by a string containing the product code, but the reference from the pallet would be an integer indicating the record number of the product code. If the operator were to view the pallet details, the record number pointing to the product would be presented as the product code by the methods associated with the referencing field. Those methods would also be used to assist and ensure that when editing, only product codes that exist in the product database may be entered. There is no “packing” of any database, so once a record is set, its record number never changes. The referencing field may be an integer of any size, so even a 1-bit integer field may be presented to the operator as a text string.

With this approach, should a product code be altered, corrected perhaps, then every record that references that product will automatically present the new data and remain referencing that same product.

Fixed Size

Another pecularity of my database is that each field has a fixed number of records. These are set appropriately for the application. Should the needs of the application change, the database's design may be editied to change the length of the fields involved. The editing of the database's design also allows for the provision of additional fields, tables, and everything else. Once the design has been altered, there is a utility that needs to run to reshape the database, and the application needs to be recompiled.

Being of fixed length makes it possible for the database to be memory-resident and for the various fields associated with the tables to be located individually.

Sequencing

Now we come to the methods associated with the tables. There are several basic types of tables. The first is the sequenced database, such as the product database. Typically, these will be presented to the operator in the alphabetic order of the product code field. They can be sequenced using any algorithm involving the various fields of the product database.

The next is the ordered database, such as the racking in the high-rise. These are presented to the operator in the physical order of the records, and are identified by the record number, but that record number is formatted to reflect the rack location with which it is associated. Rack locations are named in a logical sequence so it is not difficult to design an algorithm to translate the record number to rack location name.

Then there are the log files. These tables are circular. There is a separate table that governs the presentation of these database. Primarily, that separate table includes a pointer to indicate the next record to be written to, which is the record containing the oldest log. When that pointer reaches the bottom of the file, it is reset to point to the first record. This is all transparent to the operator, who sees the latest log at the top of the file and the oldest at the bottom.

The fourth type is linked lists. There are two variations of these, but I'll just discuss the main variety. These involve two tables, one to contain the chains and the other to contain the links. The linked lists are used to contain the details of the pallets that are travelling on the conveyors, at the crane infeeds, on board the cranes, along with the load details used to report to a host computer system. These various locations that the pallets may be located in, are described in the chain database, while the details associated with the pallets are stored in the links. As a load is moved from one location to another, its details remain within the one record, with only the linking fields being adjusted. As there needs to be changes made to two tables at once, there are atomic operations provided to achieve this. The daemon software does not require atomic operations; they are provided for use by the operator interface, which works through the daemon. The daemon is a database server to the operator interface. Linked lists are also used to define the menus and submenus for the operator interface.

No Record Locking

When an operator is editing a row of a table, as changes are made to the datum in a column, that datum is written at the time that the focus leaves that field. In this way, should two people be editing the same table row, only those fields that the operator changes will be written. So if operator one were to alter the contents of column one and operator two were to alter the contents of column two, then columns one and two would each reflect the change made by the corresponding operator; had operator two written his change subsequently to operator one, that writing does not touch column one. Also, as the operator enters each field, the contents of that field are refreshed upon the field receiving focus. Also changes are determined by whether the operator has editted the field contents, and not on whether the field contents differ from the column contents as focus leaves the field, which would happen if the column contents were to change while focus was on the field. For this reason, I have not needed to implement record locking. On top of this, there are atomic functions provided for moving a link from one chain to another, as this requires simultaneous changes to two tables and for some other operations. For these reasons, I have not need to implement record locking.

Queries

From the earliest incarnation of the database, it allowed the operator to restrict the presentation of records to just those that exactly matched a given value for nominated fields and which contained a given string of text. I called these “restrictions” and “matches”, respectively. This was a simple, straight-forward, and operator-friendly way to make enquiries. Fairly recently I added the ability to make enquiries and alter fields using SQL-style WHERE and SET clauses, and to save queries. There is also the ability to mark arbitrary rows of a table for common editing. When that is done, the table is presented in a dialogue box for editing, with only those fields that share common data showing anything. As the dialogue box is closed, the operator is prompted on a column-by-column basis (only the editted columns) to approve the setting of the data to that column.

Back to top