Re: Newbie question

Paul wrote:

> Kenneth Downs wrote:

>> The source of the constraints is outside of both the db and the client,
>> it is an outside source (in our case, a data file describing the
>> database). The program the builds them into the DB is actually the
>> *authority* for
>> them as well, it enforces constraint changes during an upgrade.  An
>> upgrade is therefore defined as that process where the schema is changed
>> (including constraints) and a new copy of the schema (including
>> constraints) is given to the client.

Paul, as a prelude to my response, let me say that I don't see the client as being the enforcer of constraints. The server does that. The client knows about the constraints though so that, where apppropriate, it can improve the overall experience, saving round trips to the server, or doing them more efficiently.

The overall thrust of my approach is all about having both server and client use an always-synchronized dictionary of what the database is supposed to be.

> OK I see. So in effect you have a meta-constraint that says: constraints
> may only be changed by a certain mechanism. Do you have something to
> prevent a sufficiently-privileged user from amending a constraint
> interactively, bypassing your upgrade mechanism?

By definition you can't. A sufficiently priveleged user operates at or above the authority of the upgrade process, and so can always thwart anything. But this is true of every db implementation in the world.

>
> It also presupposes you have control over the code for all the clients
> as well as a DBMS, which may not be true in the general case. Not to
> denigrate this method in other cases though.
>
> I guess once you have "decoupled" clients and servers there's no real
> answer to this problem.

In our implementation we make the server fully able to "defend itself", the server implements all business rules. The client's knowledge of the rules is to improve the overall experience, but a bad client cannot commit bad data.

>
> Also, what if you have a constraint that, for example, says the sum of
> some columns must be less than a certain value. And the client only has
> privileges to read one of the columns? Now you're forced to make a
> server trip and deal with the error in some meaningful way ("Error: the
> sum of your entry and two other numbers that you aren't allowed to know
> about should be less that 42")

These are best enforced at the server, where you have ACID. In our implementation the client has no knowledge or interest in these constraints.

Also, even though a round trip is required to determine validity, so would at least one round trip be required to gather the other information involved in the constraint, so I do not see this as a performance/efficiency issue.

>
> Even for a foreign key constraint, the client will have to either query
> the "lookup" table first to check a corresponding entry exists, or else
> just try it and deal with the error if it fails. And in the first case,
> the lookup table may change between checking it and updating the main
> table. Unless it's in a transaction I suppose. In fact, aren't these two
> methods actually functionally equivalent? An INSERT that potentially
> fails is the same as a SELECT conditionally followed by an INSERT based
> on the result of the SELECT.
>

Again, the client only has knowledge of constraints to improve the overall experience. So in the case of an RI check, the final answer comes from the server. But we asked the question, how can I improve the user experience if the client knows about the database? It can do many things depending on the parent table. When the parent table is small, such as a list of States and Provinces, it can cache a pre-built widget for it, such as an HTML <SELECT> element. When the table changes (not that often), the client refreshes the cached element. Other kinds of situations lend themselves to other conveniences, but others will always require a complete trip to the server.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)