Figure 4 - Diagram of Direct Mapping to Database
Analysis of a business problem is often done by using object-oriented modeling techniques. The business model directly translates into software objects and object interactions, which can be coded without translation by using object-oriented languages. Many of the objects will have persistence and will need to be stored in a DBMS system. Since many available DBMS systems are relational, this presents a mismatch requiring some kind of object to relational mapping. The mapping flattens objects into tables and corresponding relationships. A single object class may map into multiple database tables. It also translates object-oriented behavior from an object-oriented syntax into a database-oriented syntax using SQL operations and transactions.
All of this distracts the application developer from the problem to be solved and additionally requires database expertise on his/her part. The developer simply wants to model, store and retrieve an object without having to know the underlying database schema implementation. He requires the ability to find object instances quickly and easily without composing complicated joins to retrieve the normalized data.
This paper describes a TCL/sybTcl solution to this mismatch dilemma for Sybase. Any object class derived from class "Entity" inherits the persistent behavior in the form of the "Standard Interface", an object-oriented API which handles the object to relational mapping in a quick and intuitive way. Each Entity-derived object class is also initialized with a database driver object that encapsulates the details of the particular database. Our solution encapsulated sybTcl to provide the interface to Sybase. I will also show a simple TK GUI application based on the "Standard Interface", which allows object-oriented interaction and view-managed display of any persistent object class constructed in this way. A view-managed display provides the ability to format, sort, and find the listed object instances. This elegant solution solved our mismatch problem freeing our developers to concentrate on the application and to realize improved productivity and software quality.
There is a recurring problem that constantly appears when developing applications requiring persistent storage of data. Our project was faced again with this problem as we sought to create a product with a graphical user interface to manipulate a complex set of data and behavior. To add to the difficulty, a new team of developers was assigned to solve the problem. The work force had no knowledge of the business and little knowledge of database technology.
The legacy system to be replaced was created initially by concentrating on the data manipulation and storage. The GUIs were simply crafted to provide a view into this relational database. The user interface code directly embedded SQL statements to access and manipulate the data. This data driven approach produced a direct mapping of the user interface to the database layout resulting in a web of database interconnections. As shown in Figure 1, this is nothing more than a persistent global data store with all of the same drawbacks as using global variables in memory core [Heinkiens p26, 28]. It is impossible to know what ripple effects a database change will have on the system.
Figure 1 - Web of DB connections
There is a second drawback to this approach that is unique to database technology. Multiple instances of the application caused database locking and performance problems because of the random nature and order of the table access. For example, as shown in the figure 2, if process 1 does a join which accesses two tables in a given order locking table 1, and process 2 does the join in the opposite order locking Table 2, the result is a database deadlock since neither process can finish the transaction.
Figure 2 - A database lock condition
Thirdly, data integrity was constantly compromised which caused inconsistent operation and symptoms. These problems were very hard to diagnose and even worse to fix. Any change to the database required a change to the user interface code. Maintenance was almost impossible. The code was very difficult to follow and debug. Any change to one part could ripple through the system and affect other parts that seemed totally unrelated.
Finally, this direct view into the relational database requires the end user to know the database layout to perform his job. It places the burden on him to translate his business processes into the database implementation of the business.
How does a project overcome this difficulty and get an inexperienced team of developers educated about the business and productively producing robust code in a short period of time?
The answer was found by transitioning from data manipulation to more abstract information manipulation [Heinckiens p1.]. A model was needed to describe the business entities, interactions and behavior. Elemental data models can and do change rapidly, but a business model tends to be much more stable. The application and its user interface should be dependent on the business model but the business model should not be dependent on the application and user interface [Heinckiens p29-35.]. Furthermore, the business model should be largely independent of the database layout. We chose object-oriented methods and tools to model the business. Our reasons for doing this are beyond the scope of this paper, but we found it easier to document because of the direct mapping of business language to objects of a software model. O-O modeling allows abstraction where the abstractions encapsulate data structure and behavior. The abstractions represent contractual interactions with other abstractions. The model simply becomes a straight forward expression of how you do business. These are the elements to which the project needed to focus the developers attention if we were to achieve the goals in the time allotted [Heinckiens p5.]. However, what does one do about data persistence? How does one map an O-O model to a relational database? Oh no, that problem again!
On many projects a large amount of time is expended on mapping the business model to the database layout. This mapping also accounts for many of the program bugs. In this case, an object-oriented business model presents a different set of constructs from those used in a relational database. This phenomenon is called an impedance mismatch and is graphically shown in Figure 3[Heinckiens p19-21.].
Figure 3 - Diagram of mismatch
The first mismatch occurs between classes and tables. This typically shows itself in inheritance relationships. A class may map to one or more tables. If a class maps to multiple tables, how is the encapsulation preserved? The second mismatch occurs between attributes and columns. A column is a standard database datatype whereas an attribute may be that or another compound object. A third mismatch occurs because of the language difference. An object-oriented language has a certain syntax while the database has another syntax. This requires a developer to know and incorporate two separate languages and to translate between the syntaxes. A developer who understands the business model should not be required to mentally translate between the O-O model and the database layout. A developer simply wants to make a given object persistent. Further, this translation causes communication difficulty when discussing the implementation with customers and other developers. The translation must be committed to code which clouds the understanding of the program every time the mismatch is encountered. If this impedance mismatch is solved once with a layer that provides a direct mapping, every developer can use it to make the objects persistent and the code is straightforward and in agreement with the object model. The mapping is now consistent and straight-forward as show in Figure 4[Heinckiens p21.].
Figure 4 - Diagram of Direct Mapping to Database
It was not clear from the outset that the relational database employed by the project would be the database of choice in the near future. The application had to be written in such a way as to allow switching of the database implementation without affecting the application or the user interface. We needed a persistent layer that automatically handled the object to relational mapping while hiding the database implementation. This required a universal object interface to be developed that could be used in coding the business rules and implementing persistent.
The resulting standard interface is the topic of this paper. The object-oriented interface and persistent layer of this tool solved the impedance mismatch problem once for everyone. A high level of code reuse was automatically achieved. All bugs due to code that handled persistent were eliminated. Developers were now free to concentrate on implementing the business rules and a graphical user interface that robustly supported the business. Time to develop was greatly compressed because of working at a higher levels of abstraction.
We chose to create a persistent layer over Sybase using tcl and SybTcl [Poindexter], primarily because of the ability to rapidly prototype the concept. Tcl is not an object-oriented language but we simulated it by using namespaces [Ousterhout]. Therefore, each object-oriented class becomes a namespace defined with all of its methods within a single file. Some of the other software constructs discussed here are the result of using a non-object-oriented language but the result is the same. The implementation is intuitive but could probably be done even better using [incrTcl]. We did not have that available to us at the time.
Figure 5 shows the software stack that resulted from our design. The object of a stack such as this is to ensure a separation of concerns between layers to assure flexibility and a resilience to change. The interfaces stay portable making the application code independent of the actual database implementation. The standard interface is the same regardless of the database we are using. [Heinckiens p50-51.]
Figure 5 - The Stack
Figure 5 shows the layers involved. At the lowest layer is the actual database. Sybase comes with an Open Client API implemented in the C programming language to communicate with the database server [Darnovsky, Bowman, & Paulsell]. The diagram also shows other possible databases to be used in our project.
At the next level, SybTcl and OraTcl abstract this API further by providing a higher level TCL-like interface but still talking with a relational database syntax. Other third party middleware tools such as RogueWave provide object-oriented APIs that make objects of relational components but it is still a relational syntax at the same level. This is still not what we want.
The final layer, the Standard Interface, is needed to create the object-oriented interface with O-O programming syntax. This was accomplished with two classes, DbObject and Entity. All applications must use entity-derived classes to achieve persistence. The entity class must use the DbObject to access the database. DbObject hides the database implementation from the entity business classes. The project is also currently working on a DbObject to encapsulate Oracle and perhaps another one to encapsulate any XML compliant database. All business objects work the same regardless of the database providing the persistence.
DbObject provides an interface to Entity and encapsulates SybTcl. This layer is needed to protect the application from database implementation changes.
DbObject does the interface conversion from relational SQL to the Entity object array (described later). It also uses the information provided in the Entity definition to construct the relational SQL statements. It does this using the directives provided in the Entity class.
This is a non-persistent abstract base class that defines the persistent behavior through its standard procedure interface. These virtual procedures are coded once in this base class to be used by all derived objects needing the persistent behavior. These procedures use the DbObject interface to do the object to SQL mapping. The Entity directs the mapping of the object-oriented relationships such as inheritance, reference, and containment and assures the referential integrity. It also contains the instantiation procedures used by each derived class to create its own derived standard interface procedures on file initialization.
Figure 6 - The layer interaction.
What we have created here are three layers effectively: the database layer, the object layer and the business layer shown in Figure 6. The database layer consists of sybTcl which manipulates the database tables and implements the relations with joins. The object layer is represented by DbObject which handles the mapping between individual classes and their associated tables. A single entity class that realizes an inheritance hierarchy maps that single class to multiple tables through DbObject. The Business layer contains business model classes that derive their persistent behavior from Entity. This affords each business class its own persistent interface for inserting, updating, deleting and querying objects. [Heinckiens p55.]
Figure 7 - Database Deadlock Mediation
The layering of the standard interface enforces a certain order of table access so that each persistent operation uses the same sequence. The order of access is from the base table down to the most derived table according to the inheritance hierarchy. In figure 7, process 1 will lock the base table before accessing the derived table. Process 2 will now block when trying to access the base table and remains blocked until the transaction of Process 1 is complete. The deadlock is eliminated because of the enforced order of access imposed by the entity class.
Figure 8 shows a UML object model that we will use as an example throughout this paper [Fowler]. This object model contains a base class, ManagedObj, several derived classes, Building, Service and Building Service and a reference class, ServiceDescription, all of which are derived from Entity. The standard Interface has formalized three standard relationships shown in the model; inheritance, containment and reference. All objects are persistent being derived from Entity. Building, Service and BuildingService are Managed Objects. A Building contains Services, some of which are BuildingServices and each Service refers to its ServiceDescription.
Figure 8 - The UML Object Model for the Example
The Tcl approach to the object-oriented layer is a lightweight, simple to use interface that addresses most of the problems outlined in the introduction. Each will be discussed briefly.
The standard Interface mapping is defined as follows:
Entity attributes can also refer to other entity objects. The attributes, oid, coid, and ServiceDescriptionOid are Entity Integers that map to the numeric datatypes in the database.
An object instance derived from a base class contains all of the attributes of both the derived and base class. If we retrieve a derived object instance, we want to see all of the attributes of all of the classes in the inheritance hierarchy as though they belong to the derived class. When mapping to a relational database, it is best to have a separate table for the base class and one for the derived class. This keeps the data normalized. The identity in the base class becomes the foreign key in the derived class and so is the oid for the object. It is important to access these tables in the given order of base table followed by derived table. The standard interface automatically allocates the entity attributes to the appropriate columns of the appropriate table and connects the foreign keys on an insert operation. The following entity attributes are required by the standard interface for inheritance:
Each Entity can also contain other Entities and the standard Entity procedures are made to handle these relationships automatically. For example, if a container is deleted, all Entities contained by the container are also deleted recursively. This ensures proper data integrity automatically. There are other standard procedures which maintain these contained recursive relationships. The following attribute is required for containment:
This relation is one that we use very often, so we formalized it to be specifically understood by the standard interface. An entity can refer to another entity's data. This prevents having to have multiple copies of reference data in each object instance. Therefore, the object instance has a reference Oid which refers to the reference object instance. For example, each Service object instance has a ServiceDescription. ServiceDescription is a set of read-only data that describes the available services. Each service simply refers to its description. When a Service instance is retrieved, the ServiceDescription attributes become part of the Service. However, only the Service can get updated; the ServiceDescription part is static. One could think of this reference relationship as a read-only inheritance where the reference object is a base class.
We just discussed the Object to relational mapping, but what does the object interface look like?
An object contains both data and behavior. The data and behavior is specified by a class. Since we are not using [incrTcl] or C++, the data and behavior is specified by an Entity class. With this implementation, the behavior is specified by tcl procedures and the attributes and their properties are specified with a namespace declaration all defined in a single file. Each instance of a persistent object in the database becomes an Entity in-core, uniquely identified by an oid.
In Tcl, the Entity attributes of the persistent object are represented by a Tcl array. This is very intuitive and also allows direct binding to the TK widget for automatic updates. The Tcl array attributes correspond to relational table columns and the array values correspond to the column values. Each array instance corresponds to a row in the table. The oid corresponds to the identity value of the row. The attribute conversion is bidirectional.
The following shows an example of what a retrieved BuildingService object instance looks like as a Tcl array, bs. Notice the single array has attributes belonging to ManagedObj, Service, ServiceDescription and BuildingService. A programmer simply wants to retrieve a single instance of BuildingService data without regard to how it is stored in the database.
Example of the BuildingService array.
bs(action) = I: Inserted bs(adminState) = L: Locked bs(buildingLocation) = Middletown, NJ bs(coid) = 12 bs(description) = Private Janatorial Contractor bs(dt) = bs(hours) = 9:00 am - 5:00 pm bs(mdt) = BuildingService bs(oid) = 9 bs(operState) = M: Maintenance bs(owner) = bs(ServiceDescriptionOid) = 6 bs(serviceName) = Building Maintenance bs(serviceType) = M: Maintenance bs(usageState) = I: Idle
There is a set of standard virtual procedures which every persistent Entity must have. The standard behavior of these procedures is specified once in the entity class. Procedure constructors are used to create the set of standard Entity procedures for each derived entity. These procedures are constructed when the Entity is initialized. These constructed procedures act like virtual procedures and can be redefined. Certain procedures are polymorphic, such that any call to a base class standard procedure will execute the derived (perhaps redefined) procedure. New procedures can also be added. These standard procedures handle the object to relational mapping, converting Entity attributes and directives into the relational SQL statements. When you insert an object, for example, you pass the array and the entity will convert the array attributes to a SQL insert statement and then populate the array oid. An object can be retrieved by specifying the oid and receiving a populated array. In summary we have:
Data Integrity is sometimes handled in the database through the use of stored procedures and triggers but this requires the programmer to know an additional database specific scripting language. Many times, in my experience, the triggers and stored procedures were forgotten and went uninstalled. The system subsequently behaved erratically until the omission was discovered. This type of error is very bad if it happens after deploying a production system.
Instead, the Entity interface automatically enforces data integrity by encapsulating the inheritance, containment, and reference relationships. No stored procedures or triggers are needed. If a container is deleted, everything it contains is also deleted. If a derived object is inserted, its base and derived tables are populated in the proper order with the proper keying. If a Service of serviceName is inserted, the proper reference key (serviceDescriptionOid) for the named serviceDescription is populated. In summary we have:
Figure 9 shows the UML object model for the Standard Interface API. Let us start by defining some of the attribute conventions used:
Figure 9 - The Standard Interface API
The following will demonstrate the API with actual examples. It requires a Tcl interpreter to execute on the command line. The majority of commands will be demonstrated. The parray command and result (in italics) are not required but are used here to show array contents for illustrative purposes.
First, the entities must be initialized. This loads the Building namespace into core and builds the standard interface procedures. Second a database connection must be obtained. Database instructions such as Connect, Close, Begin, Rollback, and Commit go through the DbObject since they are independent of a particular Entity class. Note, the connection index is returned.
DbObject::Connect { {dbuser} {dbpass} {dsquery} } : handleIndex
%BuildingInit %DbObject::Connect tle tle123 SYBdevgdn 1 %
Entity::Create { objref {selectList} }
An entity instance is a tcl array. This is ideal for GUI widgets which can bind directly to array variables. If the widget is updated, the array variable is also updated and vice-versa.
Note, that Create loads array b with default values (specified in the namespace definition). This is created in core. There is no database interaction yet. When no selectList is provided, the entire array is created. Also note, that Building which is a derivative of ManagedObj contains all of the attributes of both ManagedObj and Building.
#The Derived class
#Note it contains all of the attributes of the base class (in bold)
%Building::Create b
%
%parray b
b(action) =
b(adminState) = L: Locked
b(buildingName) =
b(buildingNumber) = 1
b(certificateOfOccupancy) = N: No
b(coid) =
b(comment) =
b(createDate) = function getDate()
b(dt) =
b(location) =
b(mdt) =
b(oid) =
b(operState) = M: Maintenance
b(type) = R: Residential
b(usageState) = I: Idle
%
Entity::Insert {objref} : oid
Now that a default Building entity is created, the insert can be done. Insert requires a complete array loaded with, at least, default values. Some array values can also be set before doing the insert. This procedure automatically begins a transaction, inserts the ManagedObj first followed by the Building. It automatically assigns the oid (foreign key), the dt and mdt fields. Insert also sets the oid in the array and returns the oid. Any Sybase function such as getdate() will run, but the in core entity does not get this update. Note that dt, mdt, and oid (for inheritance) are ignored if they are set by the user.
set b(buildingName) "Ann Arbor Municipal" set b(buildingNumber) 101 set b(comment) "A test of insert" %Building::Insert b 15 #Oid returned %
Entity::Refresh { objRef } : 1(Success), 0(Failure)
This updates the in-core entity from the database. Only the oid is needed to identify the object to refresh. A successful refresh returns a 1. Note, that createDate has now been updated (in bold).
%Building::Refresh b 1 #Success %parray b b(action) = b(adminState) = L: Locked b(buildingName) = Ann Arbor Municipal b(buildingNumber) = 101 b(certificateOfOccupancy) = N: No b(coid) = b(comment) = A test of insert b(createDate) = Jun 19 2003 9:32:21:756PM b(dt) = b(location) = b(mdt) = Building b(oid) = 15 b(operState) = M: Maintenance b(type) = R: Residential b(usageState) = I: Idle %
Entity::Update {objref}
Update will only update the attributes contained in an array. This example demonstrates a partial object update. The ArrayCopy procedure will copy the specified attributes and values from the b array specified by the selectList. However, inheritance and containment require a minimum set of required attributes:
Entity::ArrayCopy { objRef {selectList} } : objList
Next the user changes the value of adminState and updates the object.
%array set upb [Building::ArrayCopy b adminState] %parray upb upb(adminState) = L: Locked #User specified upb(dt) = upb(mdt) = Building upb(oid) = 15 % %set upb(adminState) [Building::GetAttribute adminState enum unlocked] %Building::Update upb %
Entity::Value { oid attr {value} } : value
This useful accessor procedure allows a single value to be set or retrieved from the database if the oid is known.
#Get the value
%Building::Value 15 operState
M: Maintenance #Result returned
%
#Set the value to closed
%Building::Value 15 operState [Aggregate::GetAttribute operState enum closed]
C: Closed #The new set value returned
%
Entity::Retrieve { oid objRef {selectList} } : 1(Success), 0(Failure)
This procedure builds an array, b, similar to Create, but loads it with the values obtained from the database by using the oid. If a selectList is given, only those attributes and the required attributes are retrieved and built into the array.
%unset b #Start with no array % %Building::Retrieve 14 b "buildingName buildingNumber type comment oid adminState operState usageState" 1 #Success % %parray b b(adminState) = L: Locked b(buildingName) = Riverview Hospital b(buildingNumber) = 12 b(comment) = Boasts a beautiful view of the Navesink River b(oid) = 14 b(operState) = M: Maintenance b(type) = P: Professional b(usageState) = I: Idle %
Entity::Delete { objref }
The Building is a container object that can contain BuildingService instances. If an Building contains BuildingService, each BuildingService and its contents are recursively deleted followed by the deletion of Building. Performing a delete only removes the persistent object in the database. The array must specifically be unset to remove the information from core. Note, I could achieve the same result polymorphically by typing ManagedObj::Delete b
%Building::Delete b % %unset b #Delete the in core array %
Entity::Query { objRef {where} {order} }
Entity::Next { objRef } : 1(Success), 0(Failure)
Create with a select list creates a partial array of user selected attributes with default values. The query will retrieve only the attributes specified in the array which is why the array is passed to Query. Next will return a 1 if an object was found and return the object by loading up the array, bs. A 0 is returned when no more rows are found and the previously set array b is untouched.
%
%BuildingService::Create bs "coid usageState hours buildingLocation"
%BuildingService::Query bs
%BuildingService::Next bs
1 #Success returning a row
%parray bs
bs(buildingLocation) = Middletown, NJ
bs(coid) = 11
bs(dt) =
bs(hours) = 9:00 am - 5:00 pm
bs(mdt) = BuildingService
bs(oid) = 4
bs(usageState) = I: Idle
%
#In code, each object is gotten in this way
while {[BuildingService::Next bs]} {
parray bs
}
Entity::Container { oid objref } : 1(Success), 0(Failure)
Knowing the oid from the queried Building Service above, the persistent container object (Building) can be retrieved.
%unset b #Start with no array %BuildingService::Container 4 b #oid of BuildingService 1 #Success %parray b b(action) = I: Inserted b(adminState) = U: Unlocked b(buildingName) = AT&T Middletown b(buildingNumber) = 1 b(certificateOfOccupancy) = Y: Yes b(coid) = b(comment) = This is where I work b(createDate) = Jun 19 2003 9:19:47:623PM b(dt) = b(location) = Middletown, NJ b(mdt) = Building b(oid) = 11 b(operState) = O: Opened b(type) = P: Professional b(usageState) = I: Idle %
Entity::ContainedList { coid {selectList} } : objList
Given the containerOid, this procedure on the contained entity retrieves any that are contained by that container. The list of found objects is returned. Another way to do this is by using RetrieveObjBy and specifying to search by coid. Both methods will be demonstrated.
%set objList [BuildingService::ContainedList 11] #oid of Building
%llength $objList
5
%
%parray [lindex $objList 0] #Display the first returned object
b(action) = I: Inserted
b(adminState) = U: Unlocked
b(buildingName) = AT&T Middletown
b(buildingNumber) = 1
b(certificateOfOccupancy) = Y: Yes
b(coid) =
b(comment) = This is where I work
b(createDate) = Jun 19 2003 9:19:47:623PM
b(dt) =
b(location) = Middletown, NJ
b(mdt) = Building
b(oid) = 11
b(operState) = O: Opened
b(type) = P: Professional
b(usageState) = I: Idle
#Code to print each returned object is as follows:
foreach object $objList {
array set bs $object
parray bs
}
Note another way to do this
Entity::RetrieveObjBy { {attr value} ...} : objList
%set objList [BuildingService::RetrieveObjBy coid 11] %llength $objList 5 %
Entity::Count { attr {attr value} ...} : count
In the examples above, I obtained a count of contained instances by retrieving the objList and doing an llength on it. This could also be done by the database as follows:
BuildingService::Count 8 #The number of total BuildingServices
Many of the standard interface procedures take a variable attribute-value list. If no list is provided, it counts every persistent instance in the class. The count can be refined by adding attribute-value pairs. The example below can be understood by saying "Count the BuildingService instances where the coid=11."
# Another way to count containees is as follows %BuildingService::Count * coid 11 5 #Same contained count of BuildingServices for Building 11
Entity::GetListOf { attr {attr value} ... } : valueList
Many times an option Menu or select list on a Graphical user interface need a unique list of values from a class. This example shows how to get a list of Services. There are multiple ARAMARK services but it is only returned once. GetListOf can also be refined using attribute-value pairs.
%Service::GetListOf serviceName
ARAMARK {Gift Shop} USPS #Returned unique serviceNames
Entity::GetAttribute { {attribute} {property} {enumTagList} } : variable results
Each Entity has attributes with properties specified in the namespace. This flexible procedure is used to get this information. It is also used in a namespace definition to inherit all of the attributes of the base class. This procedure should be used in the code rather than hardcoding information to preserve the flexibility of reserving changes to the namespace. For enumerations, the tag can be used in the code so value changes can be made anytime.
#Retrieving the properties for a given attribute, type
%array set properties [Building::GetAttribute type]
%
%parray properties
properties(column) = b.type
properties(defWidth) = 1 12
properties(default) = R: Residential
properties(enum) = residential {R: Residential} professional {P: Professional} industrial {I: Industrial}
properties(label) =
properties(table) = Building
properties(type) = EnumText
%
#Retrieving the column property for a given attribute
%Building::GetAttribute type column
b.type
%
#Retrieving enumeration properties for a given attribute
%Building::GetAttribute type enum
residential {R: Residential} professional {P: Professional} industrial {I: Industrial}
%
#Retrieving the possible enumeration tags for a given attribute
%Building::GetAttribute type enum tags
residential professional industrial
%
//Retrieving the possible enumeration values for a given attribute
%Building::GetAttribute type enum values
{R: Residential} {P: Professional} {I: Industrial}
%
//Retrieving a particular enumeration value for a particular attribute
%Building::GetAttribute type enum residential
R: Residential
%
The other procedures in the interface that have not been demonstrated are similar in nature to those just shown. See the Entity Manpage for more information on all standard interface procedures.
All of the information needed to define the Entity can be gotten from the object model. Each Entity definition is contained in a single file. Each Entity file contains a namespace definition, an initialization function, the standard procedure constructors, and the specialized procedure definitions. Definition of an entity requires the following steps:
This section will show the big picture of how to map the object model into the Entity specification using BuildingService as an example. Specific detail and instructions can be found in the Entity Specification section of the Entity Manpage.
The Namespace contains the Entity Attribute and property definitions, the relationship directives, and the inherited, contained, or referenced Entity class initializations. The directives define the relationships of the entity to other entities and to the underlying database table or tables. Figure 10 shows the object model and the resulting namespace definition for Building Service.
Figure 10 - The Entity namespace Definition
Each Entity file completely defines an Entity. It contains a namespace definition described above, an initialization function, the standard procedure instantiations, and the specialized procedure definitions.
Figure 11 is an abbreviated version of the Building Service file to show the mapping from the object model to the contents of the file.
Figure 11 - The BuildingService Entity File, BuildingService.htm
These simple steps are all that are required to specify an entity class. Each class specified in this way is ready to interface to the database through the API via command line, application code or the graphical view-managed database viewing tool, siWisql. Notice how similar the information is both in content and in structure to the object model for the class. The mapping is so straightforward that entity generation and database table creation could easily be automated from a UML modeling tool such as Rational Rose. The following links are provided to show the complete specification of all the example entities for those interested:
Our project developed a windowing tool based on the standard interface to directly view and manipulate persistent objects in the database. While isql and wisql are tools for viewing tables in a relational database, siWisql views and manipulates objects. To do this effectively, we incorporated a concept that we call View Management. View Management allows the user to customize the display of the object attributes and instances in a scrolled listbox. Specifically, the user can define which attributes are displayed, giving them customized labels, spacing and justification. The user can also define what instances of a class are displayed, filtering out unneeded ones and sorting the instances in the final display. All of these modifications can be saved to a view which can be quickly recalled at any time. Of course the view is stored as an object instance of the View Entity class. Figure 12 shows the siWisql main screen viewing the set of Building objects. The screen shows three Buildings. Notice the option menu which selects the class to be viewed is set to "Building".
Any derived Entity class can automatically be viewed using siWisql.
Figure 12 - siWisql
View Management provides three editors (Format, Sort, and Find) for controlling the display of the object instances within a class. Each of the editors makes use of the attribute properties defined within the entity.
The format editor, in figure 13, retrieves the list of attributes from the Entity definition. It also uses the defWidth and label properties to determine default values when formatting. It allows a displayable label to be assigned to the attribute along with a definition of the attribute width, justification and form. The form setting allows the user to display the terse or verbose forms of any enumeration. All of these set parameters are stored to a View object instance. The selected attributes form the Tcl array which is passed to the standard interface for processing. The standard interface ultimately translates this information into a select clause of a SQL statement.
Figure 13 - The Format Editor
The sort editor, in figure 14, also retrieves the list of attributes from the Entity definition. Attributes can be selected for either ascending or descending sorts. Multiple attributes can be selected for priority sorts. All of these set parameters are stored to a View object instance. The standard interface ultimately translates this information into a orderby clause of a SQL statement. In this example, we choose to display instances by oid in ascending order.
Figure 14 - The Sort Editor
The find editor, in figure 15, also retrieves the list of attributes from the Entity definition. It allows searches to be defined by attribute and it uses the type and enum properties to enable intuitive and easy selection. The type property determines the operator selections and the widget used for the value. Text widgets are used for numbers and text. If an enumerated attribute is selected, an option menu is provided and loaded with the enumerated values retrieved from the entity definition. This allows user selection of the given values. All of these set parameters are stored to a View object instance. The standard interface ultimately translates this information into the where clause of a SQL statement. In this example, we choose to only to display instances with a buildingName of Middltetown and a buildingNumber of 1.
Figure 15 - The Finder
All of the above view information can be saved to a named view for later use. Views can be added, modified, deleted, and locked. Default views can be created. View management also allows users to export the view-managed information to a file or printed to paper. Figure 16 shows the view menu options.
Figure 16 - The View Menu
A procedures menu is provided to allow the user to add, update, and delete object instances for the selected class and to traverse the containment tree by using ContainedList and Container procedures. Figure 17 shows an example of the insert screen. Again, all attributes are retrieved from the entity definition using the type and enum properties to determine the widget to present for each attribute. This builds the objref which is passed to the insert procedure. The update and delete screens are similar but map to the update and delete standard interface procedures respectively.
The ContainedList operation presents a list of container classes and upon selecting one, brings up a new instance of siWisql showing the list of contained object instances. The Container procedure also brings up a new instance of siWisql listing only the container class.
Figure 17 - SiWisql Detail Screen
The Standard Interface is one of the abstracted mechanisms that was responsible for a successful Network Management development. It allowed analysis to be done using object-oriented principles and modeling techniques. The entire design was modeled by the team leader using UML object models, sequence diagrams, state diagrams and data dictionaries. We started with a brand new team of 12 people who had to be briefed, trained, and productive within a two month period. During this period, they had to learn TCL/TK, the abstractions including the Standard Interface, the visual models and the requirements and business rules expressed by the use cases and UML models. It is always a challenge to have an information exchange from a single team leader to 12 developers and get them productively programming in parallel. A set of sequence diagrams was assigned to each developer which they used to code their part of the application. It would not have been possible to diagram the behavior in this way without the Standard Interface.
The team created the network management product which consists of approximately 15 individual applications and 161 entity classes within a 9 month development period. We now have a clearly modeled design that maps to a consistent implementation. The database model operates in an organized and orderly way without deadlocks or data integrity problems. The standard interface allowed each developer to concentrate on the business without being distracted by the database implementation.
Their productivity was evident from the start due to working at a higher level of abstraction. The code is much easier to produce, to explain, and to maintain after weeks and months have passed. The following examples show the power of the standard interface to hide the underlying SQL code. Notice how a single easy-to-read standard interface command results in a torrent of SQL code that would be difficult to manage, model or understand.
I use BuildingService as the example because it has a triple layer of inheritance with a refers to ServiceDescription. Notice how simple the concept of retrieving a derived object is with a single standard Interface procedure. This results in a fairly complicated join. Which would you rather have to code?
%BuildingService::Retrieve 4 bs 1
SQLSTRING: select bs.serviceOid, sd.serviceType, sd.description, m.mdt,
m.operState, bs.dt, s.hours, s.serviceDescriptionOid,
sd.serviceName, m.adminState, s.owner, m.usageState,
m.action, bs.buildingLocation, m.coid
from ManagedObj m, Service s, ServiceDescription sd, BuildingService bs
where m.oid = s.managedObjOid and s.serviceDescriptionOid = sd.oid
and bs.serviceOid = s.managedObjOid and bs.serviceOid = 4
ROW: 4 F {Food Service with Great taste} BuildingService C {} {10:00 am - 2:00 pm} 1 ARAMARK L {Mr. Sanchez} I I {Middletown, NJ} 11
This is again an example of an insert of BuildingService where the Tcl array bs has been populated perhaps by the GUI TK widgets. This single intuitive command results in a series of SQL statements within a transaction, that first insert the appropriate columns into the ManagedObj table. The database generated oid is retrieved. Next, the ServiceDescription Table must be queried to obtain the ServiceDescriptionOid for the given serviceName. Finally, an insert occurs into the Service table using the oid and the serviceDescriptionOid followed by an insert into the BuildingService table. Please note that the standard interface automatically enforces this order and does it the same way for each object every time. It is coded and debugged once resulting in much code re-use and superb reliability. Do you see the power?
%BuildingService::Insert bs 18
SQLSTRING: select sd.oid from ServiceDescription sd where sd.serviceName = 'ARAMARK'
ROW: 1
SQLSTRING: begin transaction
SQLSTRING: insert into ManagedObj (dt, mdt, adminState, action, usageState, operState, coid)
values ('Service', 'BuildingService', 'L', 'I', 'I', 'C', 11)
SQLSTRING: select @@identity
SQLSTRING: insert into Service (dt, managedObjOid, hours, serviceDescriptionOid, owner)
values ('BuildingService', 18, '10:00 am - 2:00 pm', 1, 'Mr. Sanchez')
SQLSTRING: insert into BuildingService (serviceOid, dt, buildingLocation)
values (18, null, 'Middletown, NJ')
SQLSTRING: commit transaction
returning: '18'
This example can be viewed by the programmer as deleting a Building. However, this building contains a BuildingService. A single, simple one-line procedure is responsible for deleting the appropriate entry from ManagedObj, Building, and then ManagedObj, Service, and BuildingService. This example is simple because it only contains one BuildingService. The standard interface automatically handles the recursive deletion ensuring perfect data integrity. This command becomes more powerful as the number of contained elements increases. Watch now!
Building::Delete b
SQLSTRING: begin transaction
SQLSTRING: select m.oid, m.dt, m.mdt, m.action, m.usageState, m.adminState,
m.operState, m.coid,
from ManagedObj m where m.coid = 12
ROW: 9 Service BuildingService I I L M 12
SQLSTRING: select m.oid, m.dt, m.mdt, m.action, m.usageState, m.adminState,
m.operState, m.coid
from ManagedObj m where m.coid = 9
SQLSTRING: delete ManagedObj from ManagedObj m where m.oid = 9
SQLSTRING: delete Service from Service s where s.managedObjOid = 9
SQLSTRING: delete BuildingService from BuildingService bs where bs.serviceOid = 9
SQLSTRING: delete ManagedObj from ManagedObj m where m.oid = 12
SQLSTRING: select s.managedObjOid, sd.serviceType, sd.description, m.mdt,
m.operState, s.dt, s.hours, s.serviceDescriptionOid, sd.serviceName, m.adminState, s.owner, m.action,
m.usageState, m.coid
from ManagedObj m, Service s, ServiceDescription sd
where m.oid = s.managedObjOid and s.serviceDescriptionOid = sd.oid and m.coid = 12
SQLSTRING: delete Building from Building b where b.managedObjOid = 12
SQLSTRING: commit transaction
The above examples reinforce the savings to the programer in terms of complexity, reduction of programming errors, consistency of output code and reliability of persistent access. The end result is that the project has highly maintainable code which is built around a solid re-usable abstract mechanism.
Standard Interface has provided a base for expanding to other databases. The team is currently working on an DbObject interface to Oracle and perhaps other databases which use XML as the interface.
Several other ideas for improvement have also been considered. We would like to auto-generate the database table constructors from the entity specification and we are investigating generation of the entity specification from UML modeling tools such as Rational Rose.
There are many people who are involved in making a project successful. All of the good insights and ideas are difficult to acknowledge. I first want to thank Michael Cites for introducing me to TCL/TK several years ago. He has provided me with many ideas for improvement in the product leading to this paper whether I asked for them or not. I would also like to point out and thank all of the members of our Network Management development team, namely, Maureen McCarthy, Tom Tucholski, Jay Edgar, Jennifer Irons, Rich Wurth, and Doug Woolley. They are the ones who worked with, tested, and improved this product to make it what it is today. I would also like to thank Jim Licato, our integration tester, who helped me to scan the images of the screen captures. Of course, I want to thank Ken Boyd and Tom Brightfield, my immediate managers for granting the time and permission to take on this paper especially with project-related deadlines always looming. And finally, but most important, I want to thank my wife, Sue, and my children, Kristie, Scott and Bethany for giving up their dad for a short period of time to pursue this activity. Things such as this almost always cost them the most. I want to openly acknowledge the entire TCL/TK community for giving so much to so many without guarantee of any payback. I sincerely hope that the ideas in this paper can be a sufficient contribution back to them.