Re: Windows Ada database support.

["Dmitry A. Kazakov" <mailbox@dmitry-kazakov.de>]

On Mon, 06 Dec 2004 12:52:40 -0500, Warren W. Gay VE3WWG wrote:

> Dmitry A. Kazakov wrote:
>> On Mon, 06 Dec 2004 09:48:09 -0500, Warren W. Gay VE3WWG wrote:
> ...
>>>Keep in mind that you cannot use a relational database without
>>>using SQL (at least no examples come to my mind). So if you
>>>don't like SQL, then you have to either forget relational,
>>>or build a higher level "protocol" that compiles/translates
>>>to SQL underneath.
>> 
>> Yes, this is exactly what I meant. In short, Ada bindings should have
>> Create_Table (...) instead of Execute ("CREATE TABLE ..."). Let SQL be
>> perfect, firmly standardized, validated etc. Even so to have a language
>> within another language is a bad idea.
> 
> This problem is virtually unsolvable with the existing products

(SQL products)

> 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. ]

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.

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

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.

> 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. I think that it could still be possible to solve it in
an OO way. 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. It should have
some string type derived from the base. That may have constrained subtypes
or derived types according to the database constraints.

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.

> 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.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de