Expressions, Criterions, and Assignments
One of the great advantages of a client/server environment is the ability of the client to defer computation to the server. When a complicated query is requested, the client does not have to execute the query itself; the server does the work. This is illustrated by SQL statements like this:
SELECT *
FROM SomeTable
WHERE COl1 = 17 AND COL2 < COL3 - LENGTH( RTRIM (COL4) )
The
WHERE clause, which is specified by the client but executed by the server, contains a type of expression that is encapsulated in the DB Interface Module by the classes
RWDBExpr,
RWDBCriterion, and
RWDBAssignment. These classes represent the following expressions:
• RWDBExpr is the base class for simple or complex expressions.
• RWDBAssignment is used in assigning new values to columns, as in the
SET clause for updating rows.
The various databases have minor differences in their expression syntax. For example, the Sybase database has a modulus operator, while the Oracle database uses a function call instead. The DB Interface Module reconciles these differences by using standard C++ expression syntax involving columns, literals, and function invocations to encapsulate expressions. The Access module then translates expressions into forms appropriate for the database in use. These translations are described in the guides for the Access Modules.
Instances of the
RWDBExpr,
RWDBCriterion, and
RWDBAssignment classes are usually created anonymously. The following sections show how objects of these classes originate and interact.
RWDBCriterion
Both
RWDBCriterion and
RWDBAssignment derive from
RWDBExpr. The next example creates a query based on the criterion that
COL1 from some table has a value equal to
17:
RWDBColumn column1 = aTable["COL1"]; //1
RWDBSelector aSelector = myDbase.selector(); //2
aSelector.where(column1 == 17); //3
The
RWDBSelector instance on
//2 is explained in
“Selecting Data” of this manual. For now, look at
//3. The
where() member function of
RWDBSelector accepts an
RWDBCriterion instance as an argument. A C++ expression involving an
RWDBColumn instance
column1, the
operator ==, and a literal
17 are passed to it. Through automatic type conversions and overloading of the relational
operator==, an
RWDBCriterion instance is provided. Here's what happens:
1. First, the compiler tries to apply
operator== to the instance of the
RWDBColumn and the literal integer
17.
2. Since there is no operator to accomplish this, it tries to cast the column and integer into objects on which it can apply some
operator==. The only action the compiler can take is to cast the
RWDBColumn instance into an
RWDBExpr.
3. The compiler then does the same thing for the literal integer.
4. Once these are cast, the compiler can apply the
operator== for the two instances of
RWDBExpr. Since
operator== for two
RWDBExpr instances returns an instance of
RWDBCriterion, the
where() member function is satisfied.
Here’s an example using an
RWDBCriterion as the check condition in an
RWDBCheckConstraint.
RWDBSchema benefitSchema;
// Define columns for benefits table
benefitSchema.appendColumn("empnum", RWDBValue::Int);
benefitSchema.appendColumn("salary", RWDBValue::Decimal,
RWDB_NO_TRAIT, RWDB_NO_TRAIT, 10, 2);
benefitSchema.appendColumn("life_ins", RWDBValue::Decimal, RWDB_NO_TRAIT,
RWDB_NO_TRAIT, 10, 2);
// ... add other columns
// Define check constraint on the table
RWDBCheckConstraint lifeInsCheck(benefitSchema["life_ins"] < 5 //1
* benefitSchema["salary"], "max_life_ins_check");
benefitSchema.checkConstraint(lifeInsCheck);
// Create table
db.createTable("benefits", benefitSchema);
The expression passed as the first argument to the constructor of
RWDBCheckConstraint in line
//1 forms the condition to be checked by the generated check constraint. Similar to the previous example in this section,
RWDBColumn instances returned by
benefitSchema["life_ins"] and
benefitSchema["salary"] and the constant integer
5 are all implicitly converted to
RWDBExpr.
The multiplication operator then creates another expression combining the expressions from integer
5 and column
benefitSchema["salary"]. The relational operator
< produces an
RWDBCriterion instance from the two expressions on either side of it. The generated SQL will be similar to
CONSTRAINT max_life_ins_check CHECK(life_ins < 5 * salary).
RWDBCriterion and RWDBExpr
Internally,
RWDBExpr instances are data structures in the form of trees. The
RWDBCriterion class derives from
RWDBExpr and, therefore, is similarly structured. The example in
“RWDBCriterion” results in an
RWDBCriterion instance containing a three-node binary tree. The root node represents the dyadic expression of the
operator ==. The two leaf nodes represent the column and the literal integer
17.
It is important to note that evaluation of
RWDBExpr instances is done by the library at the time of the execution. Expressions that produce
RWDBExpr or
RWDBCriterion instances might look like any other C++ expression, but their evaluation is deferred to the access library module.
Here is another example, which constructs a more complex
RWDBCriterion instance. Assume that an
RWDBSelector instance already exists:
aSelector.where((col1 % 5 == 0) || col2.isNull());
The underlying access library module evaluates this expression according to its own syntax. For the Oracle database, the expression would be evaluated as:
WHERE (MOD(COL1, 5) = 0) OR COL2 IS NULL
For Sybase it would be slightly different:
WHERE (COL1 % 5 = 0) OR COL2 IS NULL
This difference arises because the Oracle database has a modulus function, while the Sybase database has a modulus operator.
RWDBAssignment
RWDBAssignment also derives from
RWDBExpr. It is used in assigning new values to columns in tables, in other words, updating. The only way to create an instance of class
RWDBAssignment is through the invocation of the
RWDBColumn::assign() method. An
RWDBAssignment is an encapsulation of the SQL phrase:
SET column = expression
where
column refers to the
RWDBColumn instance whose
assign() method produced the
RWDBAssignment, and
expression refers to its argument. Here is an example:
RWDBAssignment a = col1.assign(col2 / 3.1415);
This is equivalent to the SQL phrase:
SET COL1 = COL2/3.1415
The compiler interprets the SQL phrase as follows:
• The
assign() member function accepts an
RWDBExpr as an argument.
• The compiler does not find an overloaded
operator / for
RWDBColumn instances and doubles, but it has one for two
RWDBExpr instances.
• Since the compiler can create an
RWDBExpr instance from both an
RWDBColumn and a double, it does.
• It then applies the
operator / to form an
RWDBExpr acceptable to the
assign() function.
• Eventually, the assignment is executed by the underlying access library module.
Summary
Class
RWDBExpr and its derived classes
RWDBCriterion and
RWDBAssignment serve as encapsulations of expressions to be evaluated by the DB Interface Module and executed at the database. They allow the use of the familiar C++ expression syntax, while offering the advantage of database portability.