OiRc-0005 A question of scale

Why did I ever do database visualization in 3D with VRML?

I had been working with a large, complex database, never mind where or should for whom, and I wanted to see it.

It defined or was defined by 750 objects. That's seven hundred and fifty object, and countless instances but I don't have to care about them.

Now how do you show 750 objects? Being an architecture student, which if you've been following along you already know and you can guess where I'm going with this, I am familiar with 3D.

The rationale for using VR and three dimensions is the following:

• On a static linear presentation, we can present, illegibly, a dozen objects in a row. (12)
• Extending this in two dimensions, we can present, illegibly, one hundred and forty-four object on a plane. (144)
• Extending this to three dimensions and using a VR browser, we can present one thousand, seven hundred and twenty-eight objects in a cube. (1,728).

Now, that's a lot more than the 750 objects I was faced with when trying to visualize the dataverse. Keep in mind that the 750 objects are local to a database and the Relationships revealed through an examination of the database for foreign keys are also local.

They don't have to be, which gets me back to the theme explored in OiRc-0001 Zip, nada, nothing, Sweet Fanny Adam. Ownership of specific memes is possible using the internet wisely.

I've already thought of this and out it in a wiki web page. Since I can't garantee that the page will survive all the turmoil in my house I'm including and comment on it:

A scheme for database presentation/interaction in VRML:

Disposition

a) Array the persistency mechanisms on the surface of a series of imaginary nested spheres. (0)

b) Topologically sort the persistency mechanisms (and therefore the objects mapped onto them) by the relationships in which they participate (1)(2) to provide the necessary information to position the persistency mechanisms and object classes in 3D.

c) Sometimes the mapping between tables (T) and objects (O) (T:O) will not be 1:1.

c1) The tables should be thought of as sections running from the surface to the center of a sphere. The objects should be distributed in the section as per their hierarchy. If database views are being used to 'simplify' access, objects representing the views can span several sections.

Tables and files, API'd legacy systems and other persistent external storage mechanisms should be rendered as colored 'tiles' on the surface of the nested imaginary sphere on which they are arrayed in sections. Differentiation between the types of persistency mechanisms can be accomplished by mapping a texture in addition to a color and providing a legend of any texture mapping used.

Objects are colored 'spheres' on or inside the 'tile' to which they are mapped. Relationships are thin 'rods' that run between tables and objects. They should probably be run in channels between the sections.

Color and opacity/transparency:

Color is best thought of as saturated CYMK (Cyan, Yellow, Magenta and Black) on a white or pale primary RGB (Red, Green or Blue,) colored background. The assignment of colors is arbitrary except that the mapping is the same as that used in creating real-world four color maps. The recently proved four color map theorem states that it will always be possible to construct a map where each object and each relationship will be dintinguished from adjacent objects and relationships with only the use of CMYK as color keys.

Opacity is an alpha channel quantity which should reflect the degree of interest in objects. Classifying objects as belonging to various systems and sub systems, it becomes possible to set the opacity to a low value for objects and relationships that are not relevant and high value for objects that are relevant. Nesting objects/tables should be partially saturated so that the reveal the nested objects/tables/relationships.

Given the computational burden imposed by the architectural presentation scheme, it should be generated only when the underlying data model changes.

The interaction and 3D manipulation, rotation and navitation can be provided by a VRML browser or an HTML browser VRML plug-in.

"Touching" a table, an object or a relationship should bring up any associated HTML description in a separate window or in a separate frame in a frame set.

Some interactive mechanism should be provided to allow the user to select a s(ubS)ystem and have the relevant objects, tables & relationships highlighted and irrelevant detail faded out. Execution of a macro adjusting the alpha channel is probably the simplest way to achieve this.

That leads us to the following data structures:

ALL objects have the following fields

ObjectUniqueID (128bits);

Mnemonic (128 bytes); (if none the UniqueID is used)

Description (String);

Class; (String, element of schema)

Version; (Integer)

Release; (Integer)

createUserDateTimeStamp;

activateUserDateTimeStamp;

alterUserDateTimeStamps; (OrderedCollection of UserDateTimeStamps)

inactivateUserDateTimeStamp;

dataDictionary (Dictionary of {Association of field=>value});

relationshipDictionary (Dictionary of relationship=>(OrderedCollection of ConnectionUniqueID)).

Connections consist of the following fields

ConnectionUniqueID (128bits);

Mnemonic (128 bytes); (if none the UniqueID is used)

Description (String);

Class; (String element of schema)

Version; (1)

Release; (0)

createUserDateTimeStamp;

connectUserDateTimeStamp;

alterUserDateTimeStamps; (OrderedCollection of UserDateTimeStamps)

disconnectUserDateTimeStamp;

dataDictionary (Dictionary of {Association of field=>value});

relationshipDictionary (Dictionary of relationship=>(OrderedCollection of ObjectUniqueID)).

This schema is the subject of another article.

RDObject

Associated_HTML_description;

it will be assigned

position (XYZ);

color (CYMK-alpha).

Participating in

RDObject<->RDMapping;

RDObject<->RDRelationship;

RDS(ub)ystem<->RDObject.

RDTable

Associated_HTML_description;

it will be assigned

position (XYZ);

color (CYMK-alpha).

Participating in

RDTable<->RDMapping;

RDTable<->RDRelationship;

RDS(ub)ystem<->RDTable.

RDMapping

Associated_HTML_description;

RDTable{,RDTable}:RDObject{,RDObject} {;RDTable{,RDTable}:RDObject{,RDObject}}.

Participating in

RDObject<->RDMapping

RDTable<->RDMapping.

RDRelationship

Associated_HTML_description;

RDObject|RDTable{,RDObject|RDTable}; 0|1|n:0|1|(N|M); RDObject|RDTable{[,RDObject|RDTable};

it will be assigned

startPosition (XYZ){,articulationPosition (XYZ)} {;startPosition (XYZ){,articulationPosition (XYZ)}}:

joinPosition (XYZ);

articulationPosition (XYZ){,articulationPosition (XYZ)};

joinPosition (XYZ);

{articulationPosition (XYZ),}endPosition (XYZ) {;{articulationPosition (XYZ),} endPosition (XYZ)}.

Participating in

RDObject<->RDRelationship;

RDTable<->RDRelationship;

RDS(ub)ystem<->RDRelationship.

RDS(ub)ystem

Associated_HTML_description;

RDObject{,RDObject};

RDRelationship{,RDRelationship};

RDTable{,RDTable};

RDS(ub)ystem{,RDS(ub)ystem}

Participating in

RDS(ub)ystem<->RDObject;

RDS(ub)ystem<->RDTable;

RDS(ub)ystem<->RDRelationship;

RDS(ub)ystem<->RDS(ub)ystems.

0) The rationale for using VR and three dimensions is the following:

• On a static linear presentation, we can present, illegibly, a dozen objects in a row. (12)
• Extending this in two dimensions, we can present, illegibly, one hundred and forty-four object on a plane. (144)
• Extending this to three dimensions and using a VR browser, we can present one thousand, seven hundred and twenty-eight objects in a cube. (1,728).

1) The relationships themselves are a special class of problem since there may be several functional/state-based relationships between two tables which will not necessarily be revealed since one table (T) will only have one foreign key (FK) to another table (T') which would satisfy the relational calculus but not the actual object model. It is therefore necessary to delve deeper into the object model than simply relying on the data model. It is a starting point but not

2) Tables which have no dependencies on other tables (which don't have foreign keys) are arrayed on the outermost sphere. The nesting depth of a table (T) depends on nesting of relationships to other tables (T'..T). It will be positioned one level deeper than the deepest in T'..T;.

The schema was well enough defined that I could find all foreign keys and link them to their specific tables

Kudos to ScreenCastsOnline

We're taking a break from the pontificating to report that I've had some success at posting my audio feed here at http://oirc.libsyn.com/ and publishing it to iTunes. LibSyn is pretty usable and pretty cheap.






That should take care of half of the equation.

Now I have to work on my production environment, which is currently in useless figurative rubble at the other end of my condo. So much money spent and I can't even get AirPort Extreme signals to transmit from one end of the place to the other. You can't beat plaster on lath over brick shell building construction but sometimes you wish you could. Wireless works through the floor and ceiling but not through the wall. Geez!

And I'm not too happy with other things either (Like, how do I clone this damn G4 powerbook hard drive so that I can move everything to my G5 iMac which as four times the RAM and three times the clock speed?)

The audios and videos are intended to complement, supplement, condense and clarify the textual material which can be found on this blog.

I still like using a wiki for a collaborative work space but I must admit the blog concept rocks for uneditable works. All you can do is comment on things.

This milestone is brought to you courtesy of the instructional vodcasts I found at Screen Casts Online and the great and useful information by Don McAllister that I found there. Go there and vote for them at PodCast Alley.

Yahoo!! I'm the OiRc Podcast on the iTunes Music Store. My test 'casts are working. :-)

Now that the preliminaries are over, I can get on with the rest of the work.

Although my SysAdmin friend just called and said I had someone hacking into my Linux box as root from the NETHERLANDS. Damn... Small world, ain't it?

OiRc-0004 The library system revisited

A few years later, in 2002, while I was recuperating from the effects of 9/11 and the devastation wrought on the buildings that were literally my next door neighbours, I had a chance to revisit the Library system and come up with a better definition for the object model.

I was still cogitating; mulling over the real impact of relationships on software design. Like a dutiful son, I was going back to the family business and mining it for the nuggets of insight in what was after all familiar ground.

I was beginning to come to grips with Relationships.

Once again, I return to my wiki.

An Object model

This is where every Class and the hierarchy of every Class is defined and every Relationships is defined.

Functionally the definitions are:

every instantiable class shall define class methods to:

• new() return a new empty intance of itself.
• fromXMLString( aString ) return a new instance of itself filled with aString
• fromDatabase( aDatabase, aTable, aKey ) return a new instance of itself filled with a row returned data obtained by query against aDataBase in aTable for aKey.
• radixTable( aTableSymbolString, aLanguageRadix) answer any radixed value sets (array of labels) to associate aTableSymbolString, aRadix with a string value. The defaults are hardcoded English and internationalization will be handled by an internationalizationTable where sets named aTableSymbolName are stored in the table as aTableSymbolName, languageRadix, setRadix => aString.metadata
• slotLabel( aLanguageRadix, slotName) answers a label for a slot named aSlotNamed in language aLanguageRadix. The defaults are hardcoded English and internationalization will be handled by an internationalizationTable where slot named className, slotName, languageRadix => aString.metadata

every instantiable class shall define instance methods to:

• get() its constituent slots
• get() its constituent slots
• set( aValue ) its constituent slots
• set(, aValue) its constituent slots
• asXMLString() return itself as an XML string
• asTable(aLanguageRadix) returns itself for interaction as a table with its rows with each slot defined as one datumLabelStyle, slotLabel(slotLabel, aLanguageRadix)datumValueStyle, value row per slot and

one col spanned subtable for any instantiableFragment.

• fromTable(aTable) fills its slots with the contents of aTable. The table is the same as would have been answered by asTable()
• asTableRows(aLanguageRadix) returns itself for display as a table rows.

one datumLabelStyle, slotLabel(slotLabel, aLanguageRadix)datumValueStyle, value row per slot. If the slot is a compound instantiableFagment, it will be asked to answer itself asTableRows(aLanguageRadix).

Structurally the definitions are:

InstantiableFragment

objects can be instantiated but have no independent existence. They are only have meaning when embedded within other object instances.

Superclass: Object Instance Variables are:

• NONE

The following classes are subclasses of InstantiableFragment:

Amount

objects define the monetary cost or value of an object

Superclass: Object Instance GVariables are:

• currencyID Integer(3) radix of an item in a CurrencyTable
• currency String(14) is the currencyID is nil
• date Date
• amount FixedDecimal(14) the quantity of specie involved.

RecordID

objects define how the entire system handles uniqueness identifiers.

Superclass: InstantiableFragment Instance Variables are:

• recordID Integer (14) AutoIncrement

DateTimeStamp

objects record the date and time of an event such as record creation, update, deletion, or state change.

Superclass: InstantiableFragment Instance Variables are:

• date Date (yyyymmdd)
• time Time (hhmmsstht)

Operator

objects record the ID of the agent that created, updated, deleted a record or caused a state change. The ID can be that of a human being or of a batch process. They are stored in a CRUDE table

Superclass: InstantiableFragment Instance Variables are:

• operatorID String(14)
• memberID RecordID the ID of any member human agent

Address

objects record the position in space of an object

Superclass: InstantiableFragment Instance Variables are:

• countryID Integer(3) radix of item in a CountryTable
• countryName String(64) name of the country if radix is nil
• postalID String(16) depending on the countryID this is variously called a ZIP Code, PostalCode, Code Postal or some other nmemonic.
• firstDivision String(64) depending on the countryID this is variously called a State, Province, Prefecture, Departement or some other geopolitical boundary
• secondDivision String(64) depending on the countryID this may be ommitted of refers to a county or other geopolitical boundary.
• city String(64) name of the city of residence
• streetName String(64) coarse grained postal service location marker
• streetNumber String(64) fine grained postal service location marker
• internalRoute String(64) internal routing post postal delivery mechanism
• homePhone String(32)
• workPhone String(32)
• homeFax String(32)
• workFax String(32)
• homeEMail String(64)
• workEMail String(64)

Name

objects record the human nmemonic identifier of an object

Superclass: InstantiableFragment Instance Variables are:

• salutationID Integer(2) radix of item in a SalutationTable

1=> Mr.

2=> Mrs.

3=> Ms.

4=> Lord ...

• salutation String(12) if salutationID is nil
• nameType Integer(1)

1=> corporation, Ln

2=> individual, FnMiLn

3=> individual, LnFnMi

4=> Amerind, Mi(Band Number),FnLn

• lastName String(64) patronimic or or corporation name
• firstName String(64) filionimic
• middleInitial String(16) (may be Band Number for Amerinds)
• suffixID Integer(2) radix of item in a SuffixTable

1=> Esq.

2=> 2nd.

3=> 3rd.

4=> 4th. ...

• suffix String(16) if suffixID is nil.

Abstract classes

ModelObject

objects are abstract (uninstantiable) objects that define structure and behavior for their subclasses. They define the relationship between objects and the table that stores them.

Superclass: Object Instance Variables are:

• uniqueID RecordID
• createDTS DateTimeStamp
• createOID OperatorID
• updateDTS DateTimeStamp
• updateOID OperatorID
• deleteDTS DateTimeStamp
• deleteOID OperatorID
• objectID String(14) name of the object Class for reinstantiation from persistent store
• tableName String(14) name of the table where a persistent copy of the object is/will be stored
• nmemonic String(255) human readable nmemonic device.
• description String(1024) human readable description of the object

Concrete classes

Location

object is a point in space time where an Entity can be found.

Superclass: ModelObject Instance Variables are:

• locationID RecordID
• address Address
• effectiveFrom Date nil implies since record creation

when the date is partial, this implies a cyclic relocation

• effectiveUntil Date nil implies forever

Entity

object is a person or corporation

Superclass: ModelObject Instance Variables are:

• name Name
• roleID Integer(2) radix of item in a RoleTable

1=> Library

2=> Member

3=> Supplier

4=> Publisher

5=> Operator

• role String(12) if roleID is nil

Library

object is the library itself

Superclass: ModelObject Instance Variables are:

• entityID RecordID
• sic String(16) Standard Industry Code
• tin String(16) Tax ID Number

Acquisition

object is any item acquired for the purposes of presentation or circulation

Superclass: ModelObject Instance Variables are:

the uniquenessID is known as an accession number

• content String(1024) This may be a CSV string which can be parsed further
• costOfPurchase Amount the price paid to acquire the object
• costOfReplacement Amount the price which would be paid to replace the objec
• Circulatable Boolean

True=> this acquisition can leave the library premises

False=> For viewing in reference section only

Member

object can play service recipient roles in circulation

Superclass: ModelObject Instance Variables are:

the uniquenessID is known as a membership number

• entityID RecordID
• since Date
• renewalDue Date
• outstandingFines Amount

Catalogue

object describe any identical accessions

Superclass: ModelObject Instance Variables are:

the uniquenessID is known as a card image number for historical reasons

• cardImage String(2028) contains the catalog card/entry image
• typeID Integer(2) radix into a TypeTable

1=> Book

2=> Periodical

3=> Manuscript

4=> Audio Tape

5=> Audio CD

6=> URL

• type String(12) is typeID is nil
• externalID String(16) ISBN, ISSN, UPC or other real world designation
• publicationLocation Address
• publicationDate Date
• metric Integer(5) # of pages, running time or other indication of heft
• dimensions String(64) physical/spacial dimensions, if applicable
• summary String(64) may de redundant?
• accompanyingMaterialID Integer(2) radix into a MaterialTable

1=> ?

• accompanyingMaterial String(12) is accompanyingMaterialID is nil

Category

object describes the search taxonomy

Superclass: ModelObject Instance Variables are:

• superCategory RecordID (optimization feature only)

the mnemonic is the category name

subcategories are dynamically constructed and cached in a single structure

Keyword

object provides indexes into the search space

Superclass: ModelObject Instance Variables are:

the mnemonic is the keyword

index

object provides links between keywords and the search taxonomy

Superclass: ModelObject Instance Variables are:

• taxonomyID RecordID of the category
• keywordID RecordID of the keyword

Relationships

These define the articulations of the library system and provide for its functionality.

In case you're wondering these are index tables. In many database systems they would be defined as external keys and stored with the objects (a Fourth Normal Form no no but a good idea for quick relationship reconstruction after crashes.)

For our purposes, we will define one table per relationship and store connections in rows consisting of the owner objectID, row RecordID, member objectID, row RecordID.

This system had been remarkable for being defined without any participation sort order and this perhaps might need to be addressed. The sort information (one or more member slot name and collating sequence,) is defined in the relationship and the specific values are stored in-fixed between the owner data and the member data in the connection row.

Relational integrity is maintained very simply by sweeping through the indexes looking for connection rows where an object might have been participating in a relationship and deleting the connections. If recessary, the object referred to would also be deleted (recursively calling the delete routine,) before executing the actual deletion of the connection.

All relationships are subclasses of the generic Relationship class and all connections are instances of the generic Connection class.

The methods of the Connection class are markably simple.

Connection

object provides links between persistent object instances

Superclass: Object Instance Variables are:

• connectionID RecordID
• relationshipID Integer radix identifying the connection class
• ownerID RecordID
• orderString String
• member ID RecordID

class method:

• connect: anObject to: anotherObject via: aRelationship

instance method:

• disconnect via aRelationship

EntityN-m-:isWhere:M--Location

define the, possibly periodic, position in space/time of any entity or group of entities such as a family. It should be deleted when deleting the last entity at that location.

Library1-m-:is:1-d-Entity

defines the library entity, its deleted with the library (entity should be a library.)

Member1-m-:is:1-d-Entity

defines the member entity, its deleted with the member (entity should be a member.)

• birthDate Date
• adhesionDate Date

---

• schoolGrade
• schoolTeacher

Library1-m-:holding:0N--Acquisition

defines a library's holdings

Member1-m-:familyGroup:0N--Member

defines family groups of members

Member0N-m:reservation:0M--Catalogue

defines the reservation of (m)any catalogue item. The limit on the number of links from any member (max reservations by a member) or from any catalogue item (max reservation before we stop trying to enforce them) is a matter of policy.

Member01-m-:onLoan:0N--Acquisition

defines the loan of acquisitions to members. The entire system is being evolved to maintain this information. Strangely enough its not even worth its own independent object. Its a valued connection node. The limit on the number of link from any member (max loans) is a matter of policy. Just to keep things interesting and on the cutting edge (see AdvancedRelationshipTheory), these connections carry data:

• loanDate Date on which the acquisition was circulated.
• dueDate Date by which the acquisition is supposed to be returned.
• returnDate Date on which the acquisition was returned.
• loanState radix into LoanStateMachine.

Acquisition1--:supplier:N--Entity

defines the supplier of the acquisition (entity should be a supplier.)

Acquisition1--:author:N--Entity

defines the author of the acquisition (entity should be an author.)

Catalog0N--:publisher:1-m-Entity

defines the publisher of the catalog item (entity should be a publisher.)

Catalog0N-m-:classification:0N--Acquisition

defines the classication of a holding. There can be many copies of the same item

Category01-m-:taxonomy:0N--Category

defines the hierarchy of categories. (virtual relationship as the actual taxonomy is re-aggragated into dynamically constructed and cached single structure)

Category1-m-:catPivot:0N-d-index

defines the category-keyword search space

Keyword1-m-:keyPivot:0N-d-index

defines the keyword-category search space

Something I have to date neglected in the definition and use of state machines to describe

• Entity: birthday ->

juvenile |

adult.

• Acquisition: physical; state ->

ordered not yet received |

received not yet catalogued |

catalogued not yet on shelf |

onshelf reference only |

onshelf circulatable (no OnLoan connection exists) |

onshelf on loan (OnLoan connection exists) |

being repaired (RepairEntity connection exists) * I'll have to add this

widthdrawn

• OnLoan

on time (returnDate is nil & dueDate <= currentDate) |

late (returnDate is nil & dueDate > currentDate) |

lost (returnDate NOT nil & loanState is LOST)

You get the idea.

But sadly, I was making a mistake. Many of the data values are being carried as radixes into 'code tables'. This is infact an N:1 relationship. While the class defines many instances, only one of them participates in a relationship with (and therefore has a connection to) the object instance. (I know. Its hard to keep all these relationships and conections straight.)

Likewise, OnLoan is a state machine and the value is actually a relationship that an instance has with the OnLoan state machine.

We'll get to how I was able to discover the origin of the error and what I did to solve a completely different problem which led me to a few epiphanies; specifically about visualization of a large and complex database.