[ALL] let's think about Query Language for 2.0

[Charles Yeomans]

On Friday, May 16, 2003, at 10:51 AM, Ruslan Zasukhin wrote:

> Hi Guys,
>
> I write now specifications for feature of Valentina 2.0 -- Query.
>
> This is my comments from C++ code.
> Do not be afraid this is plain text for now :-)
>
> Read this text and find TASK after it.
>
>
>> 1) Query is the BRAIN of database kernel.
>>
>> 2) Query allow us execute search and sort on Tables without SQL.
>> We can consider this as low level engine of query execution.
>> SQL is bult over this as higher level. It will use something from 
>> Query    and
>> add own algorithms.
>>
>> 3) Query must be able to support Relational model, Netowrk model,
>> Object-Relational model and Object model.
>>
>> 4) Query can be one of the next kinds:
>> 4.1 Single Column.
>> For example,     f1 = 5, f1 > 5, f2 LIKE 'asd', f2 REGEX '.*22'
>> RESULT is Set of records of Table
>>
>> 4.2 Single Table.
>> For example,     (f1 = 5 OR f1 = 15)
>> (f1 >=5 AND f1 <= 7)
>> f1 BETWEEN 5 AND 7
>> RESULT is Set of records of Table
>>
>>
>> 4.3 Multi Table ???
>> Query cannot be of multi-table kind, because this require usage of 
>> LINK
>> apart.
>> RESULT should be something complex as JOIN Table.
>>
>> 5) In the 4.2 point, query can be of any complexity. We must be able 
>> analyse
>> it,
>> build the optimal plan of execution, execute. If needed explain the 
>> plan.
>>
>> 6) We must be able describe Query in terms of c++ and other 
>> langauges, using
>> simply API.
>> We will not use for this STRINGs like in SQL, because this require 
>> parser.
>> We will use something like factories to step by step build tree of 
>> query.
>>
>> 7) The query is represented by Tree of ENodes.
>>
>> 8) When we get the Tree of ENodes we start Query.Execute().
>> During it we ask Planner to build Tree of Plan_Nodes.
>> Then start execute this plan.
>> If tables was not changed, this plan still is up to date and can be 
>> used for
>> repeated search if need. So plan is stored in the Query object.
>> If tables was changed, then we will need build plan again.
>>
>> 9) Query can work as on indexed so not indexed fields.
>> Planner take this into account of course.
>> Depending on combination of indexed and not indexed field, and on 
>> operations
>> itself,
>> we can get
>> a) column searches that return BitSets, then combine that bitsets.
>> b) scanning of table and filtering of records.
>> c) at first SET searches, then scanning for selection we have got 
>> before.
>
> So guys, the dream is to make in 2.0 the "SQL parser+engine" as 
> separate
> plguin for Valentina itself.
>
> Developer should be able, if he want, do work without SQL at all.
> This can be for many simple dbs, or for network navigational model,
> Or just to be faster to avoid SQL overhead....
>
> This feature will require new classes in Valentina for RB and other
> products. It will bring life to V4RB BaseObject class, which confuse 
> some
> people why it exists today. I have told, problem is that V4RB have not
> SEARCH classes that have c++ SDK.
>
> So let's think now about this search classes API.
> I expect that the most active will be V4RB developers, although this 
> feature
> can be added for ALL APIs.
>
> Open your mind, and let's dream together.
>
> Assume you work with Table Person.
> What classes/functions you need to describe simple single table query?
>
>     Query = NodeEqual( Person.fldName, "Bill" )
>     Query.AndNode( NodeEqual( Person.LastName, "Gates") )
>     QueryResult = Query.Execute()
>
> Something like this.
> This is the same as SQL query:
>
>      str = "SELECT * FROM Person
>             WHERE Name = 'Bill' AND LastName = 'Gates'"
>      cursor = db.SqlSelect( str )
>
> As you see API can be comparable to SQL way by lines of code.
> Actually SQL can be even longer if think about varaibles and 
> concatenations
> to build string of query.
>
> And API way should work faster, because no conversion of numeric 
> values to
> sting, no SQL parser which should do huge job. Simple tasks should be
> resolved by simple things.
>
>
> Try to invent a great API together with me.
> Okay?
>

Okay.


Class Query

Sub Query(baseObject as VBaseObject)

(or perhaps VBaseObject should have a factory function NewQuery() as 
Query)

Property WhereCondition as WhereNode

Function Execute() as VCursor
FunctionExecute(VServer options) as VCursor

Sub AddSelectField(fld as VField)
Sub RemoveSelectField(fld as VField)

Sub AddOrderByField(fld as VField)
Sub AddOrderByField(fld as VField, isAscending as Boolean)

Sub RemoveOrderByField(fld as VField)

Function toString() as String //builds SQL query



Class Interface WhereNode

Function toString() as String

WhereNodeAnd implements WhereNode

Sub WhereNodeAnd(condition1 as WhereNode, condition2 as WhereNode)
Sub WhereNodeAnd(condition1 as WhereNode, condition2 as WhereNode, 
IsNOT as Boolean)

Class WhereNodeOr implements WhereNode

Sub WhereNodeOr(condition1 as WhereNode, condition2 as WhereNode)
Sub WhereNodeOr(condition1 as WhereNode, condition2 as WhereNode, IsNOT 
as Boolean)

Class WhereNodeEqual implements WhereNode

Sub WhereNodeEqual(field as VField, rhs as String)
Sub WhereNodeEqual(field as VField, rhs as Integer)
Sub WhereNodeEqual(field as VField, rhs as Date)
etc.

Sub WhereNodeEqual(field as VField, rhs as String, IsNOT as Boolean)
Sub WhereNodeEqual(field as VField, rhs as Integer, IsNOT as Boolean)
Sub WhereNodeEqual(field as VField, rhs as Date, IsNOT as Boolean)
etc.

The constructors probably should be something like

Sub WhereNodeEqual(field as VString, rhs as String)
Sub WhereNodeEqual(field as VVarChar, rhs as String)
Sub WhereNodeEqual(field as VText, rhs as String)
Sub WhereNodeEqual(field as VByte, rhs as Integer)
etc.


Class WhereNodeLessThan implements WhereNode

Sub WhereNodeLessThan(field as VField, rhs as String)
Sub WhereNodeLessThan(field as VField, rhs as Integer)
Sub WhereNodeLessThan(field as VField, rhs as Date)
etc.

Sub WhereNodeLessThan(field as VField, rhs as String, IsNOT as Boolean)
Sub WhereNodeLessThan(field as VField, rhs as Integer, IsNOT as Boolean)
Sub WhereNodeLessThan(field as VField, rhs as Date, IsNOT as Boolean)
etc.

Class WhereNoteLIKE implements WhereNode

Sub WhereNodeLIKE(field as VField, searchString as String, 
IsCaseSensitive as Boolean)



Then the code

dim q as Query
dim c as VCursor

q = new Query(db.BaseObject("Customers"))
c = q.Execute

is equivalent to "SELECT * FROM Customers".


I believe that it would be useful to write such a set of classes now 
and see how the interface works in practice.

I'm a little confused about point 4.2 -- does this have to do with 
support for models other than relational?

Charles Yeomans