Hello all,
I am getting started with relational databases and have been playing
around with MySQL and SQLite3. A problem I find common between the two
arises in data validation. I understand that "best practice" is to
implement constraints like foriegn keys or column/table CHECK
constraints to prevent some cases of invalid data entering the
database. However, in both those engines I get stuck on providing
meaningful feedback to the user about what went wrong. I can find
neither a mechanism whereby the program can query the database to nail
down which column raised the error, nor a way to query the database
about what constraints are enforced generally (short of parsing the
result of SHOW CREATE TABLE). The message is usually something generic
like "constraint failed" or "Can't write, because of unique constraint,
to table '%s'" which is basically useless.

Since validating user input and providing meaningful feedback are the
very definitions of a usable program (from the end-user perspective), I
am sure I am not the first person to encounter this problem. The only
solution I can think of is to implement validation one layer above the
database. This seems dangerous, not to mention redundant, because it
requires the programmer to keep the database constraints and
application constraints in sync => maintenance nightmare. One could
automate this one way or the other (have application code query the
schema and parse out constraints, or have all constraints in the app,
none constraints in the database engine) but these are just kludges and
seem like bad form.

Are there better solutions? Perhaps more fully featured engines support
this kind of granularity?

withtape (AT) gmail (DOT) com wrote:
The SQL standard defines a virtual database called "INFORMATION_SCHEMA"
which contains this information. It can be queried with SQL expressions
like any other tables. The information may be typically implemented
internally to the RDBMS; it's not stored like other data is.

Not every RDBMS implementation offers this INFORMATION_SCHEMA, however.
In the case of MySQL, for instance, it was first implemented in
version 5.0 (released circa October 2005).

One of the tables in INFORMATION_SCHEMA is called KEY_COLUMN_USAGE.
This shows primary and foreign keys in the tables.

Here are docs from the MySQL manual:
http://dev.mysql.com/doc/refman/5.0/...age-table.html

Here's a nice ER diagram produced by a MySQL user:
http://www.xcdsql.org/MySQL/informat...ON_SCHEMA.html

It seems that in general, it is assumed in the RDBMS world that database
schema are not supposed to change their data model so frequently. The
maintenance impact that you're describing "should" be low.

In practice, I realize that business rules do evolve. What seemed like
a one-to-many relationship suddenly becomes many-to-many. It's
impossible to be clairvoyant and predict all possible business rules at
the outset of a project, so these sorts of changes are necessary.

It has traditionally been the case that it is expensive and
time-consuming to implement such changes to the data model in a project.
Good project managers understand this, and work hard to define the
business rules early, to minimize the "requirements creep" after the
data model has been implemented.

Regards,
Bill K.

Bill Karwin <bill (AT) karwin (DOT) com> wrote:



I actually think that approx. 10% of any dev team should be looking at
5 years down the line and going "what if?".

I've seen too many legacy apps where the RDBMS is no more than a bit
bucket - they might as well be using Notepad to store the data - it
would at least have the advantage of being cheaper. I'm talking about
apps that are an important part of the infrastructure of multi-billion
$ companies.

Sometimes the mind boggles.


Paul...



--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Bill,
Thank you for the lead about the INFORMATION_SCHEMA standard. I suppose
that may be where I have to start. But as I say, this just seems
fundamentally flawed: That the RDBMS engine usually lacks a method for
giving specific error information at the source, and forces the
application developer to maintain the business rules if he wants to
make sense of them.

My concern about the possibility of changing the underlying database
structure is really secondary. My primary nag is the need to write
error handling without using the database engine, when it is so plain
that engine implementors could throw something meaningful. The engine
must know what the constraint was and exactly how it failed when it
throws the error; why does it not share this information?

Is there a coding framework that abstracts this? It seems so basic to
developing database applications. What is the point of implementing
business rules in the database engine if you then have to re-implement
them in the application because the engine doesn't provide good enough
feedback when an error occurs? It is worse than redundant.

I can only think I just don't know about the right tools yet because I
can't be the only person who doesn't want to re-implement constraints
checking every time I build a database.

Thanks again for your help.

-Jason Chang

Standard SQL lets you put a "CONSTRAINT <constraint name>" clause on
things, so you can see the name in error messages.

Right, the standard does let you name constraints. But neither engine I
have used so far (SQLite 3 and MySQL 5) supports this feature
meaningfully. For example, under MySQL 5, I create the following table:

CREATE TABLE checktest (start INT(4), end INT(4), CONSTRAINT end_c
UNIQUE end_u (end));

This isn't really what I'm after; I'm really trying to use CHECK
constraints, but MySQL doesn't have those at all, so I'm just making a
unique index for demonstration. As you can see, I have both named the
constraint "end_c" and named the unique index "end_u", so one would
expect to get one of those names back when an error occurs. Instead,
violating the constraint with a duplicate insertion just yields

"Duplicate entry for key 1"

My intuition says, "ok, it would have been nice to get the key name,
but I can do something with the key number, right?" No, not really. The
INFORMATION_SCHEMA pseudo-database contains nothing about key ordinals,
and you can't use WHERE clauses on MySQL's SHOW INDEX command, which
does give the key ordinal.
Already at this point I am ready to give up, because even if it is
possible to do, it is just too unwieldy. (Too unwieldy, that is, to
rely on the database engine for error reporting. The solution would be
to ossify the table definition, set up my own array of error messages
based on the known index numbers, parse out the number of the error
when one occurs, and display array[parsedIndex]. I don't like this
solution because it makes changes at the engine level expensive, and it
requires parsing the error message rather than hopping right to the
information via a known API call.)

Likewise, SQLite allows you to name CONSTRAINTS, but doesn't report the
name when a violation occurs.

My problem must be that I am using inferior, inadequate engine
implementations, but can anyone suggest better ones for casual use?
What about POSTGRES or Firebird?

Going beyond RDBMS engine short-comings, are there any common solutions
to this problem, or does everyone shrug, re-implement rules in
application code, and go on with their lives? Am I harping on something
that doesn't really matter?

Thanks again for your time.
-Jason Chang

withtape (AT) gmail (DOT) com wrote:
What DBMS are you using?
Oracle PL/SQL works the way you describe. Coding follows a BEGIN
<normal code logic> EXCEPTION <error handling logic> END; Pattern.
Violating a constraint is an error like many others.

Ed

Ed, I have been trying out MySQL and SQLite, as they are free and thus
easier for me to try. I guessed that Oracle would have something of the
kind, but I feel like that would be overkill for my purposes (a
local-area web site and an end-user program using an embedded database
engine), in terms of software heft if not cost. Admittedly, I haven't
looked too hard at Oracle's licensing terms yet, I'm just going off
their reputation/marketing.

Perhaps my problem is that I am listening to people who say "move the
logic into the database" but who really mean "into a database engine
that actually supports doing so."

-Jason Chang

I think you should look into software/programming languages you are
using to create end-user interface. I'm 99% sure that all (or at least
all worth looking at) have some mechanism to catch error and show a
user friendly message, for example in php you can catch Oracle error
and translate it to whatever you need. I assume that other db engines
also throw similar exceptions/errors that you can catch. Just look in
specific documentation. Of course to make app more robust you should
catch them in a consistent way, better write some universal error
handling procedure.
BTW Oracle has free express edition that of course has limitations (4
GB data, 1 GB RAM, 1 proc) and it can be found here
http://www.oracle.com/technology/pro.../xe/index.html
But if you need just to store some data MySQL would be faster and most
probably easier to maintain.

Gints Plivna
http://www.gplivna.eu/

"withtape (AT) gmail (DOT) com" <withtape (AT) gmail (DOT) com> wrote:



This means things like triggers, foreign keys, check constraints. No
database engine worth its salt doesn't support these - if you enforce
rules at the database level, then no matter how inexperienced, tired,
stupid or drunk the application programmer is, s/he cannot mess up
your data.


That's the whole rationale behind moving the logic into the database.



Paul...


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.