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

Carl Rosenberger wrote:
>
> 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.

Actually, I was implying that it was because -- relational is better.

> > Yet, you claim they are not usable.
>
> No, but I think it is time to find a better match for object-oriented
> principles.

You touch on that again below. I'll answer below.

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

Hard to thumbnail the relational model in a NG. Relational systems have many more 'viewports' than any other data model.

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

Also see below -- on OO vs. Relational.

> 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:
>
> ....
>
> 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.
I understand what you're trying to do. I happen to think it is a bad idea.

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

You're probably right. Current implementations have terrible weaknesses --- see the article, http://www.firstsql.com/fsound.htm on my company's site. I was probably assuming one of our RDBMSs.

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

That next step is exactly the step I'm opposed to. See below -- OO vs. relational.

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

OO vs. relational:

The definition of an object is very loose. Object-oriented techniques are a set of constructs and some guidelines. They don't guarantee a sound design.

The usual counter to the 'car' argument is -- what if you were a manufacturer and wanted to know the number of screws?

The real issue is that DBMSs deal with 'shared' data (like between departments of a company), while object-oriented techniques are concerned with application (problem) specific data. Putting such objects directly into the database will result in general usage difficulties. Object query languages will always be more complex than relational ones because of the special knowledge of each object required to 'navigate' its data.

Reusability for business objects has been a failure because there is no 'science' to designing objects, just skill and vague guidelines. Distinguishing a bad object implementation and a good one is often a matter of taste. OTOH, a database design that violates relational principles is clearly a bad design.

Also see the last few paragraphs of an exchange with C. J. Date on Debunkings --- http://www.firstsql.com/dbdebunk/lauri2.htm

> > > Please feel free to write an SQL statement for the following example, by
> > > using standard facilities if you wish:
> > >
> > >
> > > "I would like to know all Persons, that don't have a mobile phone."
> >
> > 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.

The size of table totally depends on the application, which wasn't explained.

> NOT IN is typically not optimized by current database engines.

Actually, I agreed with you here also (see http://www.firstsql.com/fsound.htm and http://www.firstsql.com/iexist.htm). However, as we've shown, this is fixable.

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

Not a big issue, but would you give representative percentages?

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

I have written several JDBC and ODBC drivers (quite efficient).

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

I wouldn't count that as a primary reason, because:

+ these drivers existed before odbc,
+ I believe Oracle participated in SAG (predecessor to odbc),
+ vendors often encourage use of proprietary interfaces for obvious reasons.

Microsoft has claimed that their ODBC driver for SQL Server is as efficient as their native driver. Others have put odbc features inside the core engine (like us).

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

I would reduce use of them, but when needed, I've used a control table for assigning id's, to be portable.

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

I agreed there are many rough spots to portable database code. What do you expect with 3 monsters (DB2, Oracle, SQL Server) who don't care about such issues?

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

I agreed it is clumsy and often misperformed. I started out defending ASCII queries and relational. I'm not the best defender of SQL. I think it should be much more relational.

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

Actually, I think the idea of a SQL 'debugger' is a great one (and have done some research on it.)

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

Ok, I was too harsh, but I don't believe you mentioned in the OP that it was generated. Generated code in any language (even an OO one) can be extremely difficult to comprehend.

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