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

[Keith DeLong]

Hi Ruslan,
I'm intrigued by your ideas. Unlike others who've responded, my db
experience outside Valentina goes back a decade (and further) so I've not
used any of the modern db environments. So I'll just dream with you as I
still work to get my brain around OOP DB concepts :-)

As I daydream I see this:

    Methods of class = SQL verbs (SELECT, WHERE, ORDERBY)
    Map SQL functions to a table (or DB super) method or property
     
Perhaps the method names would be changed for clarity, but I'm inclined to
leav them their common SQL name. In this way the concepts and transition to
Object RelationalDB would be easier to understand and explain.


class MyTable

    Mytable.Select (vFieldName(value), vFieldName(Value), etc)
     //where value is the appropriate data type for the particular vField

    MyTable.Where (vFieldName,[vFieldName], ...)   //or vFieldNun
      // where * = all fields and ** = fields and virtual fields

    MyTable.OrderBy (vFieldName, [vFieldName], ...)   //or vFieldNum

    //Cursor Equivalents
    Result = MyTable.QueryAll      // returns all records like SQL Query
    Result = MyTable.QueryFirst    // returns just first record
    Result = MyTable.QueryNext     // functional equiv to cur.nextrecord
    Result = MyTable.QueryLast     // returns last record

Other ideas...
    Result = MyTable.QueryMax(fieldname)
    Result = MyTable.QueryMin(fieldname)
    
    //Query Maintenance
    MyTable.ResetSelect             // resets Select to "*"
    MyTable.ResetWhere              // resets Where to ""
    MyTable.ResetOrderBy            // resets OrderBy ""
    MyTable.ResetQuery              // resets all the above to above values

So to use your example below:

    Person.Select (FirstName,LastName)
    Person.Where (FirstName("Bill"), LastName("Gates"))
    res = Person.QueryAll

The logic then expands to the Db super object. Valentina's functionality is
implements SQL vocabulary via new methods and properties....

Res = MyDb.Query(PersonTable,AddressTable).All
    // fields returned based on current Select of each table

Res = MyDb.Query(PersonTable,AddressTable).Min(Salarytable.Amount)


One last thing that would be incredibly powerful -- what if Result could be
set as a standard RB array, collection or dictionary. The power to return a
data query directly into these objects (that are supported and expanded by
RS) would be awesome.


Am I anywhere in the same dream as you Ruslan?

Keith DeLong



Ruslan wrote:
> 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?