Re: S.O.D.A. database Query API - call for comments

Carl Rosenberger wrote:
>
> Lee Fesperman wrote:
> > Hmm, there some real advantages to queries in string form:
>
> I am not sure if we are talking about the same SQL language. Your points 1,
> 3, and 4 might be arguments for "SELECT person_name FROM person". The API is
> inteded for some more complex problems.

Actually, I was talking about ASCII queries versus componentized, compiled queries. I didn't know this was a referendum on SQL. SQL wasn't mentioned in your posting at all. But, I do believe I can handle a discussion of SQL.

> Some comments:
>
> > + easy to build using standard facilities
>
> No.

SQL DBMSs have dominated the database landscape for 20 years. They have beaten the bejeepers out of the ad-hoc OO approaches. Yet, you claim they are not usable.

> - SQL does not have a "viewport" on data. This is a non-human approach to
> view sets and a cause for a very long learning curve.

This just illustrates a profound misunderstanding of the relational model.

> - SQL causes terrible pains if you add more and more constraints
> incrementally. You need to reunderstand the entire statement. SQL statements
> can not be split into individual parts with meanings.

You're right. SQL is not procedural unlike C, COBOL, FORTRAN and object-oriented languages. It is a declarative language where you describe the results you wish to see, not how to go about getting the results. Therein lies the power of SQL. The declarative approach allows the enormous freedom for optimizers to find the most efficient query plan. By breaking down the query into tiny sub-operations, you are removing optimization choices for the DBMS.

> - Every join needs to declare table and field explicitely.

If you want to avoid that, use a view.

> - Have you ever written an outer join over 2 or more tables containing where
> clauses?

Many times. What's your point?

> - Worst of all:
> SQL does not match the object-oriented paradigm of modern programming
> languages.

It didn't match COBOL either, and a lot of people think that was a bad thing too. Programming languages are extremely difficult to use. OO gives some small improvements by providing a common structure. It only looks good compared to what went before. OO is not a panacea. It is not the solution for everything, especially DBMSs.

I hate to tell you, but 'modern programming languages' are not very good. The emperor has no clothes.

Object-oriented is a bunch of half-baked concepts with no theorectical foundation. There is no way to prove these concepts except empirically. Relational DBMSs, on the other hand, are built on fundamentally sound principles.

> Please feel free to write an SQL statement for the following example, by
> using standard facilities if you wish:
>
> class Person
> CommunicationMedium[] comMedia;
> class CommunicationMedium
> class Phone extends CommunicationMedium
> class MobilePhone extends Phone
>
> "I would like to know all Persons, that don't have a mobile phone."

If you insist...

SELECT *
FROM person
WHERE person_id NOT IN

       (SELECT person_id
        FROM person_comm_medium
        WHERE type = 'mobile phone')

> > + usually the most compact form for network transport
>
> No.
> What's efficient about a string format, that even sends keywords as
> uncompressed strings?
> Is a plain XML file more efficient than a zipped file?

I think I'm missing something here. We were comparing string queries to some unknown 'compiled' form. It's rather hard to see how compressed strings give all the benefits you claimed for your approach.

Let me put it simply ---- are you claiming that your compiled form will have fewer bits (for network transport) than the equivalent string form of a query?

> > + portable to multiple DBMSs
>
> No, except for extremely simple cases.

Unadultrated bullshit. I have built extremely complex applications that way.

> Considering you would want to build an application that runs under Oracle,
> Sybase and MSSQL:
> Which outer join syntax would be used?

JDBC (and ODBC) provide portable syntax escapes for outer joins.

> How would you generate unique IDs?
> How would you fetch these IDs to insert them into foreign keys?

Actually, this technique is not a very good idea in the first place.

> Which isolation levels would match?

Are you claiming this is totally solved by OO approaches? I think not.

> How would you rename tables or columns?
> ...O.K., nice idea...
> What would happen to your integrity constraints on the way?

You seem to be assuming some implementation here. Why do you think you will lose integrity constraints?

> Sorry, SQL is nowhere near to being an exchangable standard.

Compared to what? SQL is by far the most exchangable data language. Please give some rational counter example.

> > + human readable.
>
> No, because you can not split statements to isolate problems.

Answered above. Are you claiming that your encoded, compiled form is human readable?

> I have posted this mail with a typical SQL statement from my past employment
> to comp.databases.object.

I usually charge for doing people's work for them. Considering your lack of knowledge of SQL and relational principles and general attitude, I wouldn't care to look at any SQL you put together.

-- 
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
===================================================================
* Check out Database Debunkings (http://www.firstsql.com/dbdebunk/)
* "The Forum Where Database Matters Are Set Straight"