Proposal to universal interface (bis)

[Vivien Malerba <malerba linuxbox com>]

Hi all!

This is the second message I send because my mailer (Spruce at the moment) just
hung...

Here is my proposal for the solution I mentionned a time ago to enable a
client to make queries
in a universal way (best seen with fixed size fonts!)

Tell me what you think of it.

Thanks to all.

Vivien
-------------------------------------------------------------

PROPOSED SOLUTION TO ISSUE STANDARD SQL COMMANDS FROM CLIENTS

-------------------------------------------------------------

The problem I want to adress here is that of issuing commands to a provider 
when you don't know what language it uses (SQL, ANSI SQL, modified language).

I propose that, as another possibility of sending some text (representing a SQL
query for example), we send an XML string. That XML string will be obtained
from a tree previously built into memory (using some easy functions on the
client side). 

The advantages of this method are:
* it can be easily extended to non DBMS providers.
* it can use all the extra stuff that some providers can have (the client app
  can know of these with the supports() functions recently implemented)
* it can be easily expanded in the future for some other type of sources 
  (LDAP,...)
* it does not require some changes in the IDL since the query is sent 
  as an XML string
* its use is not required and clients can still send SQL (or whatever) commands
  directly
* it is a way to make some queries in a universal way
* storing and retreiving queries is an easy job.

Drawbacks:
* each server need to implement an engine to recognize the XML string, 
  convert the XML string to a tree, and that tree to an SQL query which
  is quite a lot of work (for all the SQL like servers, the different 
  emplementations can be easily adapted from one SQL server)
* Query processing will be slower (though not a lot slower).



Organisation of the tree:
-------------------------

The top node should have the following attributes: 
* the query type (SELECT, INSERT, UPDATE, DELETE,...)
* some other informations about the query

Below the top node are the following nodes:
* the list of tables that are modified by the query, or their aliases (may 
  be empty)
* the list of affected tables (and their aliases). For a SELECT query, these
  are the tables after the FROM statement.
* the list of what will appear into the result of the query. This is a list of 
  nodes which can be either:
     - a constant value
     - the description of a table's attribute
     - a tree of functions or aggregates applied to one or more table's
       attributes (see description of that tree below)
  Every node in this list can have an attribute called PrintName.
* a tree describing the WHERE condition. See the description for that tree
  below.
* some other nodes for special purpose such as for ORDER BY, GROUP BY,...


Example:
A simple query like
"SELECT 'HOME' as TYPE, name, phone FROM mainlist lst 
WHERE is_priv(name) = 't' AND lst.age<30"
assuming there is a function named "is_priv" which returns 't' if the person
given as argument is a private friend, and 'f' otherwise.

might will give something like (all the tags need to be defined):

<?xml version="1.0"?>
<QUERY type="SELECT">
  <MODTABLES>
  </MODTABLES>
  <AFFECTEDTABLES>
    <table name="mainlist" alias ="lst"/>
  </AFFECTEDTABLES>
  <RESULTLIST>
    <cst val="'HOME'" printname="TYPE"/>
    <attr rel="lst" name="name"/>
    <attr rel="lst" name="phone"/>
  </RESULTLIST>
  <QUALCOND>
    <AND>
      <ISEQUAL>
        <func name="is_priv">
          <attr rel="lst" name="name"/>
        </func>
        <cst val="'t'"/>
      </ISEQUAL>
      <ISINF>
        <attr rel="lst" name="age"/>
        <cst val="30"/>
      </ISINF>
    </AND>
  </QUALCOND>
</QUERY>



Tree to describe a set of functions:
------------------------------------

The tree to describe an entry in the result of the query looks like (and can
be recursive):

 _______________
(               )
( function name )
(_______________)
        |
      __|_____________________ ... ______
 _____|______   _____|______       _____|______  
(            ) (            )     (            )
( Argument 1 ) ( Argument 2 )     ( Argument N )
(____________) (____________)     (____________) 

For example function1(lst.id, function2(lst.name)) would be like:

 ____________
(            )
( function1  )
(____________)
        |
      __|__________
 _____|___   _____|______ 
(         ) (            )
( lst.id  ) ( function2  )
(_________) (____________)
                    |
                  __|_______
                 (          )
		 ( lst.name )
                 (__________)

and something like as and XML string part:
<func name="function1">
  <attr rel="lst" name="id"/>
  <func name="function2">
    <attr rel="lst" name="name"/>
  </func>
</func>



Tree to describe a WHERE condition:
-----------------------------------

The tree principle is the same as for functions, with nodes being
AND, OR,... nodes with one or mode sub nodes being tests (such as 
equal, inferior, like,...) and subsubnodes the actual items that are tested.

The general description is:
 ____________
(            )
( WHERE node )
(____________)
        |
      __|
 _____|______ 
(            )
( AND/OR/... )
(____________)
      |
    __|________________________...
    |
 ___|___________________________
(                               )
( sub node1 like EQUAL, INF,... )
( or another AND/OR/... node    )
(_______________________________)
                 |
      ___________|________________...
      |
 _____|_________________
(                       )
( table attribute,      ) 
( constant, function,...)
(_______________________)



Conclusion:
-----------

Need some mode thoughts, and specially how to make non RDBMS systems fit in
this schema.

Some time need to be spent to enable this model to create tables (and other
objects), to set the description of an object, to set the rights of a user,...