Re: Windows Ada database support.

["Warren W. Gay VE3WWG" <ve3wwg@NoSpam.cogeco.ca>]

Dmitry A. Kazakov wrote:
> On Mon, 06 Dec 2004 12:52:40 -0500, Warren W. Gay VE3WWG wrote:
>>that people out there use. Some database products (PostgreSQL
>>for example) promote the use of Object IDs (basically Row IDs).
>>Still others don't support the concept at all (MySQL). A grey
>>area exists in a 3rd area (like Informix), which allows you to
>>use them at your own peril (Row IDs are unique, if you keep
>>within certain database design restrictions).
> 
> I think that this is a shortcoming of the very concept of a relational
> view. Relation is not an array. Period. So IF it should be
> Ada.Database.Relational, then there will be no such think as row ID. [ But
> it is a good sign. Database vendors start to conceive shortcomings of the
> existing models. This will erode SQL dominance. ]

Well, database theory experts rage wars over issues like this
in other newsgroups. All I would personally like to see on a
practical level (theory aside) is AGREEMENT and CONSISTENCY.
I could live with or without row IDs, as long as all vendors
did so uniformly. As soon as one supports the idea, then any
Ada package that binds to it must provide access to it (existing
database schemas are not going to change to suit the Ada
package writer).

OTOH, I have no problem WITH row ids, as long as they are unique.
The whole row ID problem AFAIK, only really started to be a
problem when vendors started permitting other disk configurations
that caused the row ids to be "parallel" in some way, and thus
non-unique.

> Also we should distinguish two cases:
> 
> A. Some Ada application stores its data in a database. The way the data are
> organized there is free.

Its not clear to me what you mean by "free" here.

> B. Accessing existing (but unknown at design time) data base. The data
> structure is fixed and the application must adapt to it.

I think I know the two points you are raising, but it isn't
clear which is A or B.

> A is much easier than B. Not everybody needs B. Further B is often bound to
> some concrete data base, in which case data base specific bindings make
> much sense. So we could ignore B for a while. At least until it will be
> clear how to provide at least A.

If in one case you mean a package capable of being used in a GUI
tool, where it can connect to the database and discover tables,
keys, indexes, views, triggers and the like, and allow dynamic
operations without any foreknowledge, then agreed, this is much
more difficult. This is even more of a nightmare approach wise,
because the standard(s) never addressed this need - hence every
vendor implements this functionality they way they see fit.

While the above is import (for tools), application needs are usually
much simpler. Written to do fixed operations, on a fixed set of
tables, views and stored procedures. But as I've pointed out,
even this simpler case is complex in a multi-vendor world.

>>Related to this same issue is how to identify rows that lack
>>a natural primary key. Some databases support an identity
>>type for the purpose (Sybase), while others use sequences.
>>Still others like MySQL use some weird idea of an auto
>>increment integer field (I am too lazy to look up the
>>specifics for this, but this is documented in the APQ
>>manual).
>>
>>Here's another good one: Some databases allow you to declare
>>a VARCHAR(256). Others are restricted to VARCHAR(255), and you
>>must switch to a different type (TEXT I think), if you need
>>longer fields.
>>
>>Some support boolean types, and others do not. Some support
>>arrays, others do not. If they both support arrays, they
>>are guaranteed to work with different rules and syntax.
>>
>>There seems to be virtually no agreement on how blobs are
>>handled and managed, between the different products.
> 
> This is the case B. 

Blobs?

>I think that it could still be possible to solve it in
> an OO way. 

I beg to differ on this one, though I've not tried very
hard on this one ;-)  Consider some of the challenges:

1) PostgreSQL uses an API that opens/creates etc. and returns
    an OID.
2) PostgreSQL blobs are referenced by saving a OID in a column
    of a row.
3) ALL PostgreSQL blob operations must occur within the confines
    of a transaction (otherwise the operation fails!)
4) MySQL (IIRC), wants you to put the entire blob into a
    row.
5) MySQL, IIRC, wants you to perform the blob I/O in one
    operation and IIRC, doesn't care about transactions (optional).
6) PostgreSQL blobs can be operated on like files, with seeks,
    partial writes, reads etc.

I seem to recall there were more problems, but when I started
with this list, I decided to leave it for a rainy day!!! ;-)

> All database types should be derived from one base. Factory can
> be used to create values "like in the column". Differences between VARCHAR,
> TEXT, LONGCHAR etc are uninteresting for the application. 

You might expect so, but the Boolean case and Dates have created
a lot of problem. Since you have to work with SQL, how do you
satisfy databases that want 0 and 1 for Booleans (or bit?),
and the more nomal ones that that True and False?

I sheltered the application to some degree from this in APQ,
but allowing the fields to be encoded for you (APQ knows
in the MySQL case it wants 0 and 1 - I think it was MySQL).
But this problem still pokes out in places like hardcoded WHERE
clauses :

...
WHERE MARRIED = False and ...

or

WHERE MARRIED = 0 and ...

So if MARRIED is BOOLEAN, I think you have to test if you
are using MySQL (I think it was them), and then use the 0
and 1 instead (or use APQ to encode a hardcoded False!)

Dates, Timestamps and timezones get even more interesting.
Versions of databases add to the problems! MySQL in one
version formats the dates differently than later versions
-- ugh!

> In general I do not think that primitive data types are the greatest
> problem. The problem is that the semantics of "what and how" slips away. It
> is too low level.

I think you would be surprised! Just sticking to "normal"
primitive data types in APQ, has had me see enough horrors ;-)

>>The list of incompatibilities and differences are many
>>more. For example, there are differences in the way the
>>client libraries work (ability to fetch one row at a time,
>>randomly or not (PostgreSQL), must fetch them all into
>>client memory for random access, or use one-at-a-time
>>sequential access (MySQL), etc.)
>>
>>The challenges for a "unified Ada access" layer are so
>>numerous, that I consider it unachievable. I took a stab
>>at providing a "portable" binding in APQ, but had to
>>make various compromises along the way (these are documented
>>in the manual).
> 
> I think that APQ could become an alternative to ODBC. ODBC tries to swallow
> documents and spreadsheets (like Excel), things that are too far from a
> "normal" data base.

Well, in fairness to ODBC, some sort of generalized interface
was called for, and it was "a solution" of sorts. But I never
felt it was meant to be used by application programmers. IMHO,
any API that has application programmers coding a whole whack
of complex API calls to just do a select and return a row (for
example), is not "the solution". Application programmers want
to focus on the application problem - not API details (most
mediochre programmers can't get it right anyway). Once coded,
it becomes so complex, that no one wants to make major changes
to it. Then comes the patchwork!

Many people feel that code generators are the answer. I disagree
there also. That is a one-time fix. Eventually, someone has to
go back and change it. At this point it is unreadable and near
unmaintainable.

APQ tries to make it simple for the application programmer. I
would much rather be thinking in SQL and Ada terms, than trying
trying to remember API calls. APQ's API tries to be self
intuitive, and time will tell if it is or not.

This has been an interesting diversion ;-)
-- 
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg