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

[erne]

Hi Ruslan,

on 16-05-2003 16:51, Ruslan Zasukhin at sunshine at public.kherson.ua wrote:

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

a quick draft based on a query class that I use in my project


Query class has props
DB as vDatabase
FromFields(x) as array of QueryField class
SearchExpression(x) as array of SearchExpression class
LinkedBos(x) as array of BaseObjects //this will perform autolink by objptr
Order(x) as array of Orderby class
SubQuery as Query

SearchExpression class has props
BoolOperator as String representing (and ; or ; and not : or not)
Field as QueryField
Value as Variant // would be nice if this could be also another queryfield
ComparisonOperator as String representing (= ; > ; < ; <> ; >= ; <= etc...)
SubExpression as SearchExpression

Orderby class has props
Field as QueryField
Mode as string or integer representing (ascending ; descending)

QueryField has props
BO as vBaseObject
Field as vField

e.g.

str = "SELECT Person.Lastname FROM Person
WHERE Name = 'Bill' AND LastName = 'Gates' Order by LastName descending

becomes

dim f1, f2 as QueryField
dim s1, s2 as SearchExpression
dim o as Orderby
dim q as Query
dim c as vCursor

//  constructors of queryfield
f1 = new QueryField(mDB.BaseObject("Person"), BoPerson.Field("Name")
f2 = new QueryField(mDB.BaseObject("Person"), BoPerson.Field("LastName")

s1 = new SearchExpression
s1.BoolOperator = "and"
s1.Field = f1
s1.Value = "Bill"
s1.ComparisonOperator = "="

s2 = new SearchExpression
s2.BoolOperator = "and"
s2.Field = f2
s2.Value = "Gates"
s2.ComparisonOperator = "="

//  constructor of Orderby
o = new Orderby(s2, "desc")

//  constructor of query passes the vDatabase
q = new Query(mDB)
q.FromFields.Append f2
q.SearchExpression.Append s1
q.SearchExpression.Append s2
q.Order.Append o

c = q.Run


Seems like a lot of work for a simple query
but I'm sure you see the good side of it as well :-)



Cool Runnings,
Erne.

-- 
|  e r  |  Ernesto Giannotta
|  n e  |  Musical Box - a media store