AW: [DIRECT-L Valentina 2.0. -- What is your 3 DREAM features? // XML

[Ruslan Zasukhin]

on 2/2/04 5:03 PM, Florian Bogeschdorfer at fb at memedia.de wrote:

>>> Mix, subtract, add, merge existing cursors
>> 
>> We have made this on SQL level in the first turn.
> 
> OK. Say I make 2 cursors with similar requests:
> 
> Cursor1=makeCursor("SELECT * FROM table WHERE name='Florian')
> Cursor2=makeCursor("SELECT * FROM table WHERE name='Ruslan')
> Cursor3=VALmergeCursor(Cursor1,Cursor2)
> 
> The result is a new cursor having all records from Cursor 1 and 2. I know
> this can be easily done with SQL but what if the SELECT criteria is much
> larger? 

So what? 

    SELECT ............................
        UNION
    SELECT ..............................


> Also what about speed (we discussed that already, remember?) It
> would moreover be so elegant. Also maybe you have a large cursor and you
> could easily subtract step by step.
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Great words. Actually not easy.
All operations on cursor will require at first TOTAL SORTING by all columns.
Otherwise you do not have way to COMPARE records in effective way.


> Or you have the result of a time-consuming query and hold it in RAM. Now
> some parameters change by time/demand/whatever and instead of doing an all
> new query on the whole database I just say: "please subtract all names
> starting with 'r'".

> Or two clients make a similar cursor depending on some client data. Now I
> want to use both cursors from a third client and work with the data in a
> repeat loop. Instead of having to check both cursors I merge them and have
> no double records maybe.
> 
> So:
> 
> ADD would put two cursors together, if results are double, the cursor will
> have it double
> MERGE will join the two cursors, looking if some results and ids are the
> same and have a double record only occur once
> SUBTRACT will subtract :-)


> Of course this needs a lot of thinking. Maybe only cursors with the same
> tables and fields can be used (would make sense for me)

What you say can be made in this way:

1) you do SELECT, but not just into cursor, but into new TMP Table

        SELECT ... INTO Table tmp_t1
        SELECT ... INTO Table tmp_t2

Now you have the same task. TABLES.

Just by definition cursor is -- RESULT Table.
We should not use it so flexible as you offer.

Now having tmp_t1, you can tell,

    SELECT * FROM TMP_t1
   EXCEPT 
    SELECT * FROM tmp_T1 WHERE f = 'r'


> Example: in my database application my user looks for adresses and names for
> a weekly mailing. He makes a cursor with all girls between 20 and 30 from
> bavaria maybe. I kind of "store" this cursor in my application, saving the
> record Ids (my own, not RecId) and the query. Now the other day, my client
> says he wants to send out a mailing again but he has made another very
> complex search where he determined records that should not receive mail
> anymore. He has saved this result also. Now he could say: take mailing list
> query (1) and subtract this list (query 2). Voila: all I do is make cursor
> 1, make cursor 2 and do VALsubtractCursors(cursor1,cursor2). Now need to
> somehow make a most difficult SQL syntax that even might not work due to
> some restrictions in SQL.

We will discuss your task between SQL gurus.
And will see how this must be made on BIG DBMS.

> I would also like so save cursors to disk. Make a query, need the cursor
> again in some days, so I save it to disk. Interesting, because if I would
> repeat the query in a week, it might not be the same because of changed
> data. Right now, I save a list with my own record Ids to disk, but I can not
> make it a cursor anymore...I have to go through the list step by step if I
> need additional data.

Yes, this feature is interesting.
    ability store cursor to disk, and re-create it back AS CURSOR.

I want EXPLAIN potential problems:
* very often our cursor do not create TMP table, just keep pointers on
original table.
* I think we cannot SAVE this pointers to disk, because records can be
deleted, and replaced by new, so pointers will be quite wrong.

Only way -- create TMP table -- as result of such SAVING.
This means total coping of data of all records of cursor.
You still want such feature?  :-)


> I love this cursor thing (after having adepted to it) but I would like to
> work with them more comfortable:
> ADD,MERGE,SUBTRACT,LOAD,SAVE,XMLDUMP

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------