SheQL -- the ::SHE+ILA:: relational DB

More about SheQL -- the ::SHE+ILA:: relational DB

A relational database stores its data in tables, as opposed to a hierarchical database which uses pointers between its records in order to represent its structure. In a relational database, we use "key fields" to connect our various tables. These are navigated to by lookups from "foreign keys" in other tables. ::SHE+ILA:: can simulate a relational database by means of its hash tables (see previous drawing). For example, let's consider a typical stock-control system :
StockItems <:: (plushies=200, marsbars=50, biscuits=150, computers=10, floppies=700); // Table of stock item quantities
Prices <:: (plushies=10.00, marsbars=1.00, biscuits=2.00, computers=300.00, floppies=1.50); // Prices in pounds of each item
CustBalances <:: (wanda=3000, sheila=1000, janet=500, julia=500, lyndi=200, lissi =200, tracy=50); // Table of customers and their account balances in pounds
SellCustAnItem<:: {(custname=%1, itemtype=%2); // Parameters are (customer name, item type)
CustBalances[custname] -= Prices[itemtype];
StockItems[itemtype]--;
}
SellWandaABiscuit <:: SellCustAnItem ("wanda", "biscuit");
This technology is useful in MUDs (Multi-User Adventure games, such as Shades, Wanderland, and Gods) as we can represent the Land as a table of rooms, each having inventory items and exits. We can pick up an item from a room by changing its location table entry, from the room where it currently is, to the player (the player can be themselves considered to be a room).

Now, a room can contain many objects simultaneously -- this is called a "one to many" relationship. Similarly, in our stock-control example, our customer could order several different items. One way to handle this is to use a variable-sized array or list (e.g. "new" in C++, or a linked list in Lisp, or the "<<" insert operator in ::SHE+ILA::). However, a relational DB tends to want fixed size tables where possible (avoiding garbage collection). So instead, we make a table of items where each row contains the item and also a key referencing the room (or customer) in whose inventory the item resides. E.g. if Wanda bought 2 biscuits and a marsbar, our inventory table may be :
Inventory <:: (("wanda", "biscuit"), ("wanda", "biscuit"), ("wanda", "marsbar"))
This table is thus "square" rather than "ragged", improving efficiency. When we want to see what Wanda is carrying, we can do a SQL SELECT on the Inventory table WHERE Customer == "Wanda", rather than looping through a variable-length array.

Note that because we cannot have duplicate keys, we cannot directly store the structure (("wanda", "marsbar"), ("wanda" ,"biscuit")) -- we would hold it internally as ("wanda", ("marsbar", "biscuit")). This is not really relational, as we now have a nested array within an array. One alternative is to allow compound keys, e.g. wanda1 and wanda2, and do a linear search (Inventory["wanda*"]) instead of a hash lookup, but this is inefficient. Otherwise we could allow arrays to be indexes, e.g. Inventory[("wanda", 1_2)] but this is difficult to generate a binary hash for, as it is not a scalar key.