An Introduction to

Client/Server Database Development

A review of the technologies behind a revolution

By Liam Breck, Northampton, MA

What’s the Buzz?

Client/Server Computing is one of the industry’s hot buzzwords today, but just those two words alone don’t provide enough context for a specific discussion. Client/server is a very general software architecture that can be applied to a vast range of software missions. But if you overhear business execs talking about client/server, chances are you’ll hear about SQL servers in the same conversation. What are they really talking about? The mundane world of corporate database management. Only it isn’t so mundane anymore

A great wave of change is swelling at the very heart of the computer industry where big iron has held sway and users have been stuck in front of mouseless, monochrome terminals. Mini-computers and even Unix workstations are encroaching on mainframe turf, while personal computers with a GUI are replacing the terminals. What’s driving this trend are client/server database (hereafter CSDB) systems based on a common query language and standardized communications protocols, which allow unprecedented diversity in large information systems.

In this, and articles that will hopefully follow in future issues, I’ll be discussing the software components of CSDB systems, with a close eye on the development tools used in this field. This month I offer a general overview of the field, without discussing product specifics. If you are eager for specifics, examine the references at the end of the article, which points to places where they can be found. [Let us know what you think of these types of CSDB articles too! - Ed. nst]

First, a quick clarification about the people referred to herein. “Vendor” refers to a company which develops and publishes a particular software tool. “Developer” refers to a user of that tool (you!). “User” refers to a user of an application created by that developer (probably using that tool).

Basic Elements

A CSDB system consists of three primary software components (aside from the network software and operating systems of the computers in question): the client application (also called the front end), the data access layer (also called middleware), and the database server (also called a database engine, DBMS, data source, or back end).

The client application is responsible for accepting input from the user, submitting a query to the database server based on that input, receiving results from the server, formatting them, and presenting them to the user.

The data access layer is relatively transparent to the user, but may be very apparent to the developer of the client app. It provides for the app an API used to submit queries to a data source without much concern for the network between them.

The database server accepts queries from clients, processes them concurrently, and returns results. There are a number of different query languages around, by far the most prevalent of which is SQL. (By the way, contrary to conventional wisdom, “SQL” doesn’t stand for anything in particular. The ‘S’ isn’t for “standard” or “structured,” although the ‘QL’ is thought to stand for “query language.”)

Strengths and Weaknesses

Before we get into detail about these primary components, let’s discuss some of the advantages and weaknesses of these systems. There’s a lot of hype about “moving to client/server,” “downsizing,” “upsizing,” “rightsizing,” etc. It needs to be put in perspective. Older systems, by the way, are often referred to as “legacy,” “centralized,” or “host-based.”

A lot of the expected advantages of these systems are in their hardware requirements. CSDB systems distribute processing between the client, which runs the user interface and certain application logic, and the server, which handles the database engine and centralized data access logic. In an ideal scenario, this division of labor can prevent the slowdowns associated with systems where all the processing is performed centrally. However, this distribution creates a new potential bottleneck - the network. The network is also vulnerable to overloading by use external to the database application.

Another hardware benefit is the ability of an organization to employ cheap standard computers for both clients and servers, rather than one vendor’s proprietary hardware. Such machines are also general purpose - they can be sent on other missions and moved around the organization. On the other hand, so-called standard computers are often difficult to integrate. Centralized systems can be more turnkey in this respect.

In both hardware and software aspects, CSDB systems are billed as being scalable and modular. This is true to the extent that the designers plan for it. It is possible to develop CSDB systems which can evolve smoothly and incrementally into large services and whose components are replaceable with new products. It is discouragingly easy, however, to develop a CSDB system whose user capacity and/or transaction capability has a low ceiling, or whose components are tied to one another. But with careful planning, a CSDB system can be better in both respects than a host-based design. Keep in mind that diversity, which often follows effective modularity, can make for a significant management headache, especially where software deployment is concerned.

A major advantage claimed by CSDB proponents is the quality of development tools in this market. It is undeniable that major advances have been made in development tools in the last few years, and CSDB tools have made the most advances of any. Procedural 4GLs, object-oriented 4GLs, graphical GUI builders, object-oriented GUI builders, even graphical object-oriented “languages,” data modelers, component repositories The list of features to boost programmer productivity is quite long.

Reduced development time is the expected gain from all this tool evolution, and this is the area of CSDBs I’ve seen hyped the most. “Develop a full working CSDB application in an hour without writing a single line of code!” Don’t believe a word of it. As development tools have improved, expectations of applications have risen doubly so. While it’s true that you probably won’t spend as much time poring over SQL source, and may not ever have to write a line of C, you will have to exploit all your development environment’s functionality, and this may mean writing a substantial amount of (OO)4GL code. In fact, the environment often won’t do something you need, or won’t do it fast enough, and you will be forced to write an external procedure in C or another 3GL.

Just as in every other area of software development, it is easy and common to seriously underestimate the time required to turn out the application as spec’ed, and it is highly unlikely that someone important will not insist on a change in the spec during the implementation phase! Be pessimistic when drawing up the schedule.

There is a major potential software disadvantage, too. Let’s face it, modern personal computer software - including operating systems, system extensions, development tools, and commercial and custom applications - is just not that stable. Hardly ever is a commercial product fully debugged before its next major version is released and the vendor ceases to support the previous one. Feature fever is bad for software stability, and almost never is “bug-free” regarded as an important feature by vendors. Mission critical systems simply cannot depend on such software. It wasn’t always this way. I’ve heard stories about older computer systems that have actually never crashed at most of their installations!

The advantages of CSDB systems are leading many organizations to consider migrating to them. Migrating a user base of any size from a host-based database to a CSDB system really requires re-engineering the business processes underlying the software of the legacy system. Business processes are usually developed with the computers of their day in mind. Re-implementing an existing business process in a new environment may not provide any of the expected benefits if that process is especially well adapted to a legacy system, or if it is fundamentally inefficient. Business Process Re-engineering is a whole field unto itself, and is outside the scope of MacTech, let alone this article. I can only caution the reader that software design is not the first, nor the most difficult, issue in migration.

Ess-Cue-Elle Spelled Out

The proliferation of SQL as the lingua franca of database systems has pushed the trend towards diverse, distributed data systems, despite the current (and hopefully temporary) lack of full interoperability of SQL-based products. The view here is focused solely on SQL-based tools, but there is not a single feature set for all SQL-accessible servers, nor one for all SQL-capable clients.

SQL is more of a language family than a single language. Although there is an evolving standard for it, many vendors have made proprietary extensions to the 1989 standard, in some cases creating incompatibilities with the 1992 standard. SQL is a non-procedural language, though some implementations have been extended to include procedural and even object-oriented features. Procedural functionality in CSDB development is usually only provided by the language used to build client apps.

Let’s examine a typical SQL query on a database that has a table for clients (client) and a table for the clients’ appointments (appnt).

SELECT client.name, client.city, appnt.date, appnt.time
 FROM client, appnt
 WHERE client.idnum = appnt.idnum
 AND appnt.date >  :twoWeeksAgo
 AND appnt.date <= :today
 ORDER BY appnt.date, appnt.time;

This query performs four basic operations:

• joining the client and appnt tables such that each appointment is matched to a client with the same ID number (specified by the FROM clause and the first comparison of the WHERE clause)

• selecting rows in the joined table whose date lies in a certain range

• sorting the rows by the date and time of the appointment

• selecting columns (listed after SELECT) from the joined table to display

The SELECT, INSERT, UPDATE, and DELETE statements make up the core of SQL’s data manipulation language (DML). SQL has a data definition language (DDL) that allows developers and admins to control a database’s schema or data structure. Its core statements are CREATE, ALTER, and DROP. SQL also has a powerful construct called a view, which is defined by a SELECT statement, and is used like a table in DML statements. It can provide a layer of abstraction from tables, which generally represent the decomposition of the data model necessary for the database engine.

SQL’s cursor construct is a feature heavily used by client applications, because it provides them with controlled access to query results. A cursor is also defined by a SELECT statement, and allows its creator to step through (and in some cases move around in) the results with the FETCH statement. Rows in a cursor can be modified by UPDATE and DELETE statements.

There is, of course, much more to SQL than I can summarize here. The best way to learn it is hands-on with the particular database server with which you will be developing applications, since SQL varies a lot among its implementations.

By the way, I avoid use of the word “relational” when describing database functionality. There are two reasons. One: It goes without saying that SQL-accessible database servers are relational by the common use of the term (which seems to mean: “rows in one table can own or belong to those in another” - a fairly dumb definition). Two: NO such database servers are literally relational because none implement the Relational Model published by E. F. Codd. Indeed, critics of SQL assert that SQL is an obstacle to realization of the Relational Model.

Tools of the Trade

There are many different types of software tools used in the development of CSDB systems. Here are the major categories:

• Client builders enable the development of custom client applications.

• Database programming tools enable the development of server-side application elements.

• CASE and data modeling tools assist with the design and implementation of the database itself.

• Database administration tools aid in the administration and maintenance of the database and the server responsible for it.

This month we study the features of client builders, the most glamorous tool category. We’ll study the other categories in future issues.

The Major Features of Client Builders Are:

Support for development on and deployment to the major OS platforms

Cross-development is not all it’s cracked up to be. Chances are, if a vendor has ported only their runtime module (and not the development environment) to a platform, then they don’t really understand or appreciate that platform. It is sadly the case that many client builders supporting development on the Macintosh originated on other platforms, and sometimes don’t support the Macintosh as well as they should.

Transparent access to various data sources

Most client builders can access most of the popular database servers, such as Oracle, Sybase, and Informix, plus ODBC-accessible servers. Some client builders can be extended by the developer to access additional data sources. To enable a client application to hit different back end targets without extra work by the developer, client builders usually provide a standard way to access the general capabilities of different servers. This can take the form of an SQL-like language, a proprietary query language, or a graphical query builder.

A graphical GUI builder with special objects for data presentation

To the standard collection of GUI objects, client builders’ WYSIWYG screen painters add special objects that support the presentation and manipulation of database rows without extra coding. Most GUI builders can be extended (with the help of a 3GL compiler) to include new kinds of objects. Some also have object-oriented features that assist in the development and maintenance of new GUI objects and collections thereof. In cases where the client application is compiled (as opposed to interpreted), client builders may include a way to examine and test the results of your GUI development efforts before runtime.

A programming language with a full-featured library

The language is probably the most important component of a client builder, and the current market is divided into four broad language categories:

• CSDB-focused products: These tools generally feature a fairly sophisticated 4GL, often with OO features.

• Standard-language-based toolkits: These may be complete, vendor-integrated toolkits for CSDB development, or a developer’s collection of favorite tools from different vendors. Smalltalk and C++ are the dominant languages in this category.

• Graphical language products: These tools are based on non-textual languages in which programming is accomplished by linking together pictures representing available services.

• Desktop database products with extensions for SQL-capability: These tools usually have a relatively limited, procedural 4GL.

Most important is the language library’s feature set. It must be able handle GUI control, file management, communications, data access and manipulation, reporting all of the functionality that’s provided to a 3GL by the OS and standard libraries, and then some. This is a tall order, and vendors’ standard libraries don’t usually fill it completely. Fortunately, in many cases, library extensions are available from third parties, or may be grown at home by the developer in a 3GL or the tool’s native tongue.

A runtime engine

This consists of the library of standard functions called by the developer’s code (usually the whole thing, not just the functions actually called), and possibly an interpreter for the client builder’s language. In some cases an interpreter is the only way to run the client app; in other cases the app may be compiled. The runtime engine may be a separate file on disk, or reside in the client app.

A debugger

OO4GL or not, client builder languages can embody logical errors, and even flaws that will crash the app and possibly the computer. A toolset’s high level debugger may not work at all in the latter case, but it will let you set a breakpoint and step to the innocent line of code that has somehow managed to trigger a serious bug in the client builder itself.

Some Client Builders Also Include These Features:

A database modeling/definition tool

These vary widely among client builders, but few approach the quality of dedicated data modeling tools. Some client builders have built-in support for third party modeling tools.

An SQL database engine for development use

This is usually a single-user engine, though some client builders provide a complete CSDB toolkit out of the box by including a full-fledged multi-user server.

A graphical query builder

The quality of query builders also varies a lot among client builders. They sometimes require more effort than simply typing the corresponding SQL, but they often save much tedious coding.

A graphical report writer

Very few environments omit reporting capabilities entirely, but not many have full-featured report builders. These tend to work in much the same way that GUI builders do, though they cannot be entirely WYSIWYG because their layouts are created to print variable amounts of information. Language libraries usually have a group of functions that work with the report builder.

Support for development teams

As the popularity of CSDB systems has increased, so has the number of developers stumbling over each others code on a given project. Not only must source code text be managed, but also GUI elements, data structures, and evolving libraries of these pieces. Some client builders can use the services of a third party version control system.

A compiler which generates native code, pseudo-code, or 3GL code

To get the developer’s efforts up to speed on user workstations, a vendor has three options: a native compiler for their language for each target platform, a pseudo-code compiler whose platform-independent output is pseudo-interpreted by a runtime engine, or a translator that generates 3GL code for the target platform. I feel that the presence one of these is crucial. Several client builders only provide interpreters, making their runtime engines large, and apps sluggish.

A reasonable memory footprint for compiled apps

;-) If you think C++ and an app framework make for oversize applications, you’re in for a nasty surprise when you start writing apps with just about any of the client builders on the market. User workstations typically need to dedicate three or more megabytes of memory to an app written with one of these tools. Brace yourself.

A business rule modeling system

Business rule development is a complex area. Basically, a business rule system centralizes general rules about data manipulation and integrity, and obviates the need to implement them in the GUI code, on a case-by-case basis in the client app, or in server procedures. In the finished application, the rules will actually dwell in those locations, but that is transparent to the developer. Very few client builders have a sophisticated BR system, though emphasis on the need for this feature is increasing.

Distribution of application components across a network

Following on the evolution of BR systems is the emergence of development tools that can transparently distribute parts of your application to appropriate computers in your network and link them together into an integrated, but distributed, computing system. This is the province of very high end tools, and is a fairly new segment of the CSDB market.

No runtime/distribution fees

A fair number of vendors impose a per-user charge for distribution. Unlike the concurrent-user charges assessed by database server vendors, these runtime fees apply to every workstation where the client application is deployed, active or not. This often involves copy protection which can prevent legal use of the app. There is only one thing to say about such practices: “Grrrrrrr!”

Making Ends Meet

A data access layer provides an API that lets you submit SQL statements from clients on different platforms to (possibly diverse) data servers, and handles the network protocols and (in some cases) the server differences behind the scenes. It provides a function library which can be called from 3GL (and often 4GL) environments, and often requires intermediate server software that resides on the server host and passes client requests to the database engine. The implementation of the library for a particular client platform, network and data server is usually called a driver, and a client needs a driver for each data source it accesses.

There are two types of data access APIs, server-specific native systems from server vendors (Sybase Open Client, Oracle OCI/SQL*Net, Informix Net, etc.), and server-general middleware systems from third parties. Native systems are tuned for their target servers, do not require intermediate server software, and generally perform better than middleware systems. Middleware systems are useful where the API is not transparent (i.e., in a 3GL) and there are diverse servers, or when a native solution is not available for a required data source. (Non-relational database systems, in which 85% of all corporate data is said to be housed, often do not have native APIs.) Transparent support for different data access APIs, especially native ones, exists in most of the client builders on the market. Thus, most developers will not have to learn a data access API in order to get started.

I will take up specific data access layers in another issue, but two bear brief mention here due to their status on the Macintosh.

DAL (originally CL/1), from Independence Technologies and an Apple product until recently, has been around for a while, and is the most prevalent middleware system currently employed on Macintosh clients. It adds some useful extensions to SQL which enable offloading of some data processing from client to server. However, its performance may be limited in some environments. It accesses the popular database servers on a variety of host platforms. It is used in concert with the Data Access Manager API of the MacOS and also supports Microsoft Windows clients.

ODBC is a Microsoft implementation of the SQL Access Group Call-Level Interface specification, and Apple has ported it to the Macintosh. It does not require intermediate server software. ODBC driver development is the province of server vendors and third parties, and the current availability of Macintosh drivers is limited and growing slowly. ODBC drivers are usually implemented as translators for a native API, which may result in poor performance. ODBC is criticized for its three-level specification which means different drivers may not support the same functions. Microsoft is beginning to downplay ODBC in favor of new data access capabilities in OLE.

Behind It All

Database servers are the workhorses in a CSDB system. They consist of two integrated components, an SQL parser and a native database management system. The server translates and optimizes SQL queries for the native DBMS, performs the queries, and returns the results to clients. Data servers generally need significant horsepower in the host computer and multitasking capability in the host operating system in order to process queries from concurrent users in a reasonable time frame.

Due to these requirements the servers currently available for the MacOS are mostly meant for light-duty multi-user or single-user applications. The PowerMacs based on the new PowerPC Reference Platform specification, running PowerOpen (Apple’s and IBM’s new Unix), Netware, Windows NT, or a future MacOS, have the potential to attract more server vendors to Apple hardware. Reference Platform PowerMacs are still a year away, however, and versions of these operating systems for them are necessarily farther off.

We’ll have a closer look at SQL database servers in general, and those for the Macintosh in particular, in the near future.

Getting Started

If you are not involved in developing a CSDB project for your employer, but want to get your feet wet with SQL and a client builder tool and/or a data access API, where do you start? Mac ODBC with an ODBC driver for a single-user database engine or even a text-based data source, and an inexpensive client builder (perhaps the 3GL compiler you probably already have) might be the best place. The SQL specified by ODBC is pretty standard, most client builder tools can access data sources through ODBC, and its API is similar enough to others to be generally educational. Be aware, however that some drivers may not include much documentation about SQL or ODBC’s dialect thereof. (Note that as of this writing, it might be difficult to assemble this toolset, but I expect the technical and availability issues to be ironed out by the time the article appears.)

CSDB Information Resources

Here is a collection of resources (one of which I developed) that many have found valuable in keeping up to date with CSDB technology for the Macintosh.

The Macintosh Client/Server Database Development Summary is an overview that covers over 30 software tools for Macintosh and cross-platform development in three categories: client application development tools, data access layers, and database servers. The eight page document provides an explanation of each category and a brief description of each product. It can be obtained through the WWW at http://www.astro.nwu.edu/lentz/mac/software/csdb-summary.html (best method), or by emailing a request including your name, position, department and organization to maccsdb@external.umass.edu.

DBMS magazine, published by Miller Freeman, is a good source for product reviews and industry trends, though it doesn’t usually have much to say about the Macintosh. For an evaluation copy contact MF Circulation at (800) 227-4675.

The Center for the Application of Information Technology maintains an extensive collection of pointers to IT resources at http://www.cait.wustl.edu/cait/infosys.html.

The Usenet newsgroup comp.client-server has moderate traffic and is consistently interesting. The Macintosh database newsgroup comp.sys.mac.databases is not such a good source of CSDB info, but if you’re posting a question about CSDBs for Macintoshes, I suggest you cross-post to both groups. The comp.databases.[vendor] groups are more heavily trafficked, and some of them are contributed to by the vendor’s technical support staff.

Trade shows are usually a good source of hype and confusion, but may also offer opportunities to talk to knowledgeable vendor personnel. The Database and Client/Server World show will be held during June 13-15 in Boston. The show Expo is free. Hint: don’t just wander around the expo floor, study the program and try to hit all the vendors that interest you. For info contact DCI at (508) 470-3880 or dbworld@aol.com.