The Database
Table of Contents
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
number of fields required. 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 sorted 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 sorted 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 databases. 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.
Another 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 simultaneously, 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.
There is also provision for using two-dimensional record numbers. In the
case of the use of colours in the various graphical representations of the
plant devices, each of these colour uses is given a name. Additionally, each
user is referenced in the database by his name. The actual colour that is used
on the display is dependent upon both the user and the use for the colour.
Such records are identified by the user name and the description of the colour
use, and the database presentation may be restricted to showing only those
records for a given user, to see all his colour settings, or for a given colour
purpose, to see how much it varies between the users.
Similarly, some databases may include fields that have many times the number
of records presented in the database. For example, the display screens menu
database includes the three fields that provide the text, security level, and
refresh time for each display screen, but then goes on to provide fields that
set the size and position of that display. Those latter fields have such a set
of records (equivalent to the number of displays) for each user. For these
databases, the user may select which user's size and position fields to work
with.
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's
focus 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. There are also atomic functions
provided for some other operations.
I realise that there are other reasons for implementing record locking, but
these applications have no such needs. This is one of the advantages of
writing software for an application, rather than writing software that needs to
be all things to all people.
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
|