See Also: SQL Criticism
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:
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 onlyIf 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.
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 ifSee also Error Handling Notes.
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.
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.
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.
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