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

Lee Fesperman wrote:
> SQL DBMSs have dominated the database
> landscape for 20 years. They have beaten the
> bejeepers out of the ad-hoc OO approaches.

This is true, simply because more effort and money was put into the development of database engines to support SQL.

> Yet, you claim they are not usable.

No, but I think it is time to find a better match for object-oriented principles.

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

Can you please point out the misunderstanding?

An SQL SELECT statement has no start and no end. All tables have the same priority. It takes some people years to figure this out. This concept does not match the hierarchical tree-like structure that is used in OO-languages.

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

Did you have a look at the S.O.D.A. API? It is also a declarative approach. The API sets up a query graph, which can then be optimized by the DBMS.

An SQL parser to set up an optimization graph would have very similar functions.

The S.O.D.A. approach tries to eliminate the unecessary intermediate step, that typically takes place in an OO-application:

[X] objects =>
[ ] strings created programmatically =>
[ ] string parser =>
[X] query graph =>
[X] optimizer =>
[X] queries

An application that uses JDBC and ODBC has another 4 unnecessary intermediate steps.

By the way:
It will be a very simple task to write an SQL parser on top of the S.O.D.A. API.
> > - Every join needs to declare table and field explicitely.
>
> If you want to avoid that, use a view.

Views are very slow, since they do not uses indices efficiently.

> > - 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 think we can agree that OO languages are succesful. Developing software to store, query and retrieve objects more effectively is a natural next step in development.

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

Humans think in objects. Objects are a very convenient abstraction to describe complex entities. What further proof do you need?

If it would not be for the OO concept, I would need to describe a car with: "1000 screws, 400 pieces of metal, 500 pieces of plastic, ..... where screw1 is joined with piece 4 ..."

In OO I can just say "car". Why would you want to prove scientifically that this is more efficient?

To write an SQL statement to store a car with all pieces with SQL statements, I could spend years.

With an OO-approach I can just say "store the car". All links between objects are already described by the programming languages. Why would I want to remodel everything in another language?

> > 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')

Using a huge table to describe multiple classes is highly inefficient. I would use one table for every class.

NOT IN is typically not optimized by current database engines.

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

Yes.
Thank you for clarifying this.

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

Have you ever tried debugging JDBC and ODBC at call level? It is very interesting to watch all the overhead that is going on.

Companies like Oracle provide their own drivers to circumvent this overhead for performance reasons.

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

What would you use to generate foreign keys? Triggers?
...very portable ;-)

In an object database approach, the user does not have to care about IDs and links between objects at all.

> > Which isolation levels would match?
>
> Are you claiming this is totally solved by OO approaches? I think not.

True.
Locking techniques are typically very badly implemented in all object databases to date. Development is being continued.

This was just one of my arguments against the compatibility of SQL databases.

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

The typical implementation is:

• create a temporary table
• copy data
• drop foreign key indices and constraints
• drop the old table
• create a new table
• copy data
• create foreign key indices and constraints

Since reengineering tables is a lot of work, it is hardly done. Typically documentation "this field means this" is provided.

The class model of an application perfectly describes all "tables and attributes". An ideal database can analyze this model. There is no need to maintain tables or schemes manually.

> > Sorry, SQL is nowhere near to being an exchangable standard.
>
> Compared to what? SQL is by far the most exchangable data language.

We don't disagree here.
"the most exchangable" is true.

> > > + 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?

No, the compiled form is of course not readable. Can you read text files with your eyes only if the computer is shut down? One always needs a program to understand bits and bytes.

I was thinking about readability in a different sense: Readability for the programmer while he is doing his work.

The possibility to view isolated parts of a query problem makes it easier to understand them.

 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.

I never try to win arguments with personal insults.

The code that I have posted there is not my SQL code. It was created with an excellent generator tool, that spared the company some 5 manyears of development work.

Kind regards,
Carl

---
Carl Rosenberger
db4o - database for objects - http://www.db4o.com