More Notes and Babble about
Table Oriented Programming

Side-topics to Table Oriented Programming

Updated: 11/8/2002

SQL Stored Procedures and Remote Processing

Sometimes it is advantageous for performance reasons to let the remote processor (database engine) process a database call rather than return each record to the local processor (your program). This is because the database engine might be closer to the data, and thus have a higher bandwidth of access.

This can pose a problem if we want to use local functions instead of SQL functions or stored procedures (still remote) to process records. As described before, letting our program do the processing gives us easy access to all the data and functions within our program. Here are some disadvantages of stored procedures:

Stored procedures split your code between the one program and process and another program and process. Obviously, it is more convenient to have to program in only one place. Program partitioning is an artifact of bandwidth limitations, not something invented just to make programming easier.

Stored procedures have NO access to functions, variables and data in your program unless somehow passed as parameters. Native functions generally cannot be passed. (Remember the control tables? They often cannot be processed remotely.)

Stored procedures are neither standardized nor likely to be in the same language that you are using.

Ideally it would be easier to process the records locally to avoid separate programming spaces. However, for speed purposes, it often makes sense to let the database engine handle the record processing if possible. How would ITOP resolve this apparent conflict?

First, ITOP should use standard SQL function names and comparing behavior where appropriate. This allows table processing statements to be executed the same regardless of whether run locally or by the database engine. Note that if an internal function or other local objects are referenced, then processing must be local, since the database engine can't handle them.

Second, perhaps the programmer should be given option of specifying whether local processing or remote processing takes place. The results should be the same either way if the remote system supports standard SQL. If local functions and objects are used, then local processing would take place regardless of the local/remote setting. I envision control words or block (nest) commands resembling the following:

    local preferred
    local only
    remote preferred
    remote only

If the "preferred" statements are used, then the ITOP system can use the other option if the preferred one is not available or practical. If the "remote only" option is chosen, but cannot be done, then an error is triggered. For example, if the programmer knowingly uses non-standard SQL that only the remote system understands, then "remote only" would be the only choice. This is similar to "passthrough" SQL requests. This technique is only one suggestion.

Error Collections Example

  rs = openRecordSet(....)
  ...
  rs.name = getFromForm("name")
  rs.rank = getFromForm("rank")
  rs.serial = getFromForm("serial")

  if not SaveIfValid(rs)
    errRS = getDBerrors(rs)    ' a recordset (table) of errors
    while getNext(errRS)
       displayMessage(errRS.text)
    end while
  else
    output "Your form info was successfully saved!"
  end if

Generic Looper Example

This example was a response to some closure issues.

sub loopy2(sql, funcName)
  var rs = openDB(sql)
  while getNext(rs)
    execute(funcName + "(rs)")
  end while
  closeDB(rs)
end sub

// Typical call:

loopy2("select * from orders where clientID=7", "processOrders")

If more than one parameter is allowed, then we could change the middle line to:

    execute(funcString + "rs)")

It would be up to the programmer to supply the left parenthesis and any other parameters.

Dealing with Many Attributes

One issue with tabled GUI's is that the number of properties may grow to several dozens for some widgets, even hundreds under some systems. There are at least three ways to deal with this:

Expand the widget table for every possible property
Make dedicated per-widget tables for less-commonly-used properties
Make an "attribute" table

The first one is controversial. It is sometimes labeled as "sparse tables". I generally don't have a problem with these as long as the less common fields are kept to the right (if field ordering/views are provided), but they bother some people for some reason which they have so far been unable to articulate, or are fears based on misconceptions or habitual traditions. some say that it "wastes space", but this is highly an implementation issue.

Option #2 can be bothersome because it can create more joins and clutter the table-space with lots of little tables. It also tends to make some assumptions about groupings that may turn out to be arbitrary or short-lived in usefulness. It smacks of the abuse of taxonomies and/or subtyping often found in OO modeling. Note that a separate table is a necessity if the attribute relation is not one-to-one.

Option #3, attribute table, has fields such as "attribName" and "attribValue", plus a foreign key to the primary widget table. Attribute tables can often make attributes more dynamic, such as adding them at run-time. (There is no law that prevents regular tables from doing the same, by the way.) However, table engines that don't allow (or hamper) dynamic field widths often make the implementation of these problematic. The table industry is too dominated by the "big iron" RDBMS systems that don't promote light-duty or dynamic tables very well. They are stuck in a FORTRAN mindset in many ways.

Dealing with "sparse attributes" is sometimes considered a drawback of the relational paradigm. I consider it part of a tradeoff made to get the power of relational. See Core Differences article for more on the tradeoff. See also Dynamic Relational.

Avoiding Set/Gets and Other Interface Bloat

OOP interfaces are often full of collection-oriented operations like SetValue, GetValue, AddElement, GetParent, ClearList, etc. Sometimes more than half of an interface is devoted to such operations. If the interface was table-based, then the interface size and/or complexity could be greatly reduced in many cases.

For a simple example, let's look at a typical pull-down list-box in a GUI interface. Rather than bloat the interface with commands such as AddElement, RemoveElement, SetSortRank, ClearList, etc., it can be reduced to supplying a simple table handle (result set) or relational expression.

  query = "select SiteID, SiteName from Sites order by SiteName"
  ListBox(query, default="")

This command accepts a query that supplies two columns, the value and the description. The interface is not concerned with the source of the list content nor managing the list of items that actually go into the list. We are avoiding the need for perhaps a dozen collection management operations because our collection system (tables) already handles these. In many cases the content already exists in a database. Why devote interface features to recreating a mini-database just for the list-box?

In essence, we are "factoring" collection operations out of potentially each and every component and into a common tool-kit, known as relational tables and/or database management.

Footnotes
There may be other display options not shown (used) here. However, these often depend on the conventions of the interface, such as whether its web or GUI, and thus will not be dealt with here.
Also, if the "value" column is the same as the description, then we can simply supply the description column twice ("select siteName, siteName ...."). We could make it an option (optional named parameter in this case) to receive only one column, but adding to the interface to avoid doubling up columns is not warranted in my opinion.
If the content is static rather than from a dynamic table, then a ListOptions table can be created with 4 columns (at least): ListID, TheValue, Description, and SortOrder. We can even make a wrapper around the above command (or add it as an option) so that we only have to supply the ListID, and not a full query. Some will say that getting list contents from a table is slower than storing them in code. However, this depends on the table engine and language.
This interface does not include multiple selections. However, I question the use of list-boxes for such use anyhow. Better alternatives include Set Selectors or scrollable grids with check-boxes on them.

More Topics In The Works