Opening the $hitlist! (full control on retrieved data)

Author: hansen@ahp-gmbh.de (-GHAN-)

THE CLAIM: Improve the DB-DRIVERS and OPEN THE HITLIST
I would like to have the $hitlist opened! ... or controlled by params which truly would make life a
bit easier! I claimed it here and there and did some workarounds but well ...! So, time to write it down.
(If you don't have a coffee by now this is a good time to get one *s* this text is a longer one)

This could look like:
------8<-----8<-------
$retrieve("OCC_LIMIT") = integer, maximum amount of retrieved occs per "RETRIEVE, READ"
$retrieve("POSITION")  = integer, starting position for "READ" (...and after having set those two params...)
retrieve/e

------8<-----8<-------

The well known rules:

if (position > MAX_POSITION)
  CORRECT $retrieve("POSITION") to MAX_POSITION and so on ...
endif
 
TECHNICAL INFO
Uniface internally handles this already. While we fire the retrieve trigger, Uniface takes the
profile and starts reading (READ-Trigger). The hitlist gets prefilled with the keyfields and is
ready for us to get the occ's. So, what we really work with is a selection of data choosen by the
keyfields in the $hitlist (... or some sort of) The requesting widget in the component gets the
first chunk of occ's and afterwards poll more data as it is requested (depending on the property
set in the widget). This is how it works today.

THEORY
I believe Uniface will poll the DBMS for the retrieve profile and get some data back (i.e. total
occs in profile, handle for this request and some other things that never will reach the surface).
And so: i do believe there is a possibility to take control of this and make it better! Further
more there are other nifty things, which easily could be implemented in the drivers.

Lets look at a simple thing which could boost SEARCHING within the DB:

UPPERCASE and LOWERCASE of Fields!
I looked it up and the DB's handle this in TWO different syntaxes (which then makes it easy to
implement in a DB driver):

upper(Field) and lower(Field): Oracle, MS-SQL, DB/2 and Informix
ucase(Field) and lcase(Field): MySQL, DB/2 and

Solid Case insensitivity in some situations would be outstanding! But i leave it up to Compuware
to invent a smart way to handle this for us :)

BENEFITS / POSSIBLE ENHANCEMENTS]

1) Reducing the DBMS overhead while working with large entities. Imagine the following
   setup:
      - 1 Entity, 2.000.000+ occs
      - you want to jump to position 45.235 => so you go SETOCC 45235 => Uniface reads
        the first 45.234 occs and then starts to let you work with it
        (Sure, better keys => less problems, i know) If you dont like
        this thought, then give it a try and place a "PUTMESS WHATEVER"
        in the Read-trigger :) make some coffee- ... this will take a while :))

2) As a result of a reduced data volume: FASTER Applications

3) NO NEED to archieve this by working with READ WHERE any longer!

4) Uniface would enable pagination-stuff NOT ONLY for widgets but also for web!
   (yes, burn me, but you Client-Server guys get a benefit of this aswell)
   Remember Gertons "Pick-it-up! Music Shop" demo. He did some extra code to let
   people do a sort of pagination ... Client-Server has is, the others not!

5) FASTER sorting by letting the DBMS sort and return just a needed slice of the hits!

6) AND FINALLY: Making me a happy man :)


Please feel free to ask or place your opinions to this!

 


cheers,

-GHAN-

8 Comments

  1. Hi G?nther, Case independend search ================== the case-independend search is a very very old request (70's) and the most efficient way to support this is the good old MATCHCODE . MATCHCODE=uppercase(FIELD1) in the -trigger. Only this way you can set indices in the database to force in-between searches rather than full table scans. REDUCTION of hitlists =============== An option (already in production) uses different stored procedures and SQL scripts with all kinds of temporary tables to narrow the result-table and than grab these remaining data with uniface. Both scenarios can be used right now. I agree with you totally that in most cases you can write proprietary SQL statements which execute much faster than the old uniface way of collectin data. Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. you may abuse the WHERE to add some clauses to the SQL statement generated by UNIFACE (it is a very old one as you can see from the 5.2.f: SELECT NAME FROM EMP ORDER BY NAME[5,10] works. Unfortunately uniface (5.2.f) doesn't allow a read order by "NAME[5,10]" So we tried some dirty tricks using the where-clause: read where "text" causes uniface to generate a sql statement like SELECT ... FROM ... WHERE (text) The following strange looking uniface statement builds a hitlist sorted by a substring: read where "0=0) order by NAME[5,10] --" This forces uniface to construct SELECT ... FROM ... WHERE (0=0) order by NAME[5,10] --) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ The '--' starts a comment in Informix SQL (as it does in Oracle SQL, I think) and is needed to eliminate the final parenthesis. I hope this will be helpful to someone else. AFAIK, there are some commands to limit the hitlist (to 1 record only) which may be applied here. Alternative: Transfor the entities ti views and put the limitation to the WHERE of the View.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. Dear Ulrich, dear readers,

    erhmm ... i'm definitly aware of how to tweek the SQL with WHERE (and not with U_WHERE). But actually this is not the point and neither the CLAIM!

    The Claim is just to open the UNIFACE NATIVE support for this without having in mind which DBMS you run! George Mockford showed us, how to push the limits with WHERE (look for "Uniface webcast - Web Page Pagination.wrf").

    My Claim goes a bit beyond that!

    1. I want to be able to just get SOME of MANY occ's! (without overhead)
    2. I DON'T want to play tricks on Uniface to archieve this (this will end up in a big mess if the tricks fail on DBMS "xyz")
    3. If i want to do multi tier development, then its MANDATORY to have the data in control and not always to get ALL occ's
    4. I dont want to do mappings for a unified search string :) (yes, you may call me a lazy bast###, but every DBMS is capable to do it on the fly!)

    And while talking about multi tier ... Look, this isn't that funny, if you think it over. Its about declaring the layers right aswell as the data retrievel. Without this, the overhead will become very high!

    Presentation tier:

    HERE we just get the data from a tier below this! ERGO:

    1. We call some Service to do so
    2. We don't have the entity in direct access (bye bye stepped hitlist! This is where it starts messing)
    3. then it's all or nothing, like in web (remember, most services are self contained => Stateless)
    4. The data is delivered in XML, a list or per entity param (as i call it remote entity)
    5. depending on how you retrieve the data, you perhaps need tons of DTDs


    Maybe i didn't get the real point in doing multi tier but in my point of view PRESENTATION is JUST PRESENTATION, BUSINESS does the thinking and DATA has and delivers the data through the layers. Feel free to put me in the right place if i got it wrong.

    What really scares me is a scenrio like this:
    In a case like "give me the order lines of customer X" which may be some 10.000 occ's and i only have space for a 30 then i don't want to transfer 10.000 occ's every time for every "ok, give me the next 30!"

    ... is this the way we have to walk!?

    Cheers,

    -GHAN-


    PS:
    I would like to hear MORE OPINIONS on this so don't hesitate and just join in! This is about YOUR FUTURE while doing Uniface development :)


    Author: -GHAN- (hansen@ahp-gmbh.de)
  4. Hi G?nther, as I said before I agree with you that a native SQL statement performs much better than the uniface method will do. But one of the things uniface claims since version 1 is that you DO NOT HAVE TO WRITE SQL STATEMENTS, but you can use many different databases. Consequently, they use only a handful of simple SQL statements and have a concept that each entity MAY reside in a different database (we know that in production environments this is a rare situation). Rather than amending the current read/write/updates, a totally new concept may be more interesting: $XMLSELECT(WHAT,FROM,WHERE,MAPLIST,$PATH) a combination of XMLSAVE and SQL-SELECT - WHAT: a DTD to define the required information - FROM: a list of tables to access - WHERE: a where statement, may use many tables - MAPLIST: a associative list mapping columns to fields - $PATH: database identifier like $ORA, $DEF Benefits: - No need to paint entities for readonly infos - No need to hold the data in 2 hitlists - DTD can be generated from the presentation form Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  5. hmm ... i guess the following is already done while using multi tier: >Benefits: >- No need to paint entities for readonly infos ===> i don't paint anything for those: getting them as XML, LIST, HTML. I even decided which fields have to be transferred to me... ;) >- No need to hold the data in 2 hitlists >- DTD can be generated from the presentation form ===> i went away from DTDs while transporting with XML some time ago. SAX rules since its limits are set to 10MB as a max parsed data. Furthermore i only transmit the needed fields. Personally i don't want to take the DB assignment job from Uniface. The .ASN and model have their hand on it and thats fine for me.


    Author: -GHAN- (hansen@ahp-gmbh.de)
  6. looks like I have to amend my description: The statements are all focused on the data delivery part. >- No need to paint entities for readonly infos ===> is meant for the component delivering the data; so we do not have to paint a lot of components only to support different frame-in-frame presentation situations. >- DTD can be generated from the presentation form ===> i used the DTD generated from PRESENTATION forms (UNIFACE) to tell the data-delivery component which fields to return, avoiding non-important fileds. The "generate DTD" is just to make life easier and have the modifications only in one place.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  7. Did somebody see the Film "TITANIC" in the newer version with Dicaprio and Kate Winslet?! After the ship has sunk some rescue boats circle around throug the mist, searching for survivors. And the man in the front of the boat shouts out : "... IS SOMEBODY ALIVE OUT THERE!? ... " Some days i feel the same, when i "circle around" in here :) And in addition to this topic here i see myself sitting in such a rescue boat shouting to YOU: " ... AM I THE ONLY ONE BUGGING AROUND WITH THE HITLIST ISSUE? ..." Let me carve out the problem once more: 1) ANNO DOMINI 2009- Compuware will launch RIA at the end of the year, but still the stepped hitlist is closed for pagination. 2) This affects EVERYBODY who uses: - multi tier development - USP / DSP - SOAP / Webservices 3) dynamic systems need dynamic data to turn Uniface to be up-to-date and ready to enter the RIA world ... 4) It's up to US (the community ... yes, this is you, you and you aswell as me) to get this issue off the road! If you aren't affected by now, this could change very fast! So if you find this idea "whatever", then do me a favour and simply reply your thought as a reply to this post. Don't hesitate to simply reduce your reply to ONE WORD if desired (e.g. "CRAP", "OK!", "GOOD", "BAD" ...) By doing so i could figure out how many people have read this. Thanx in advance -GHAN-


    Author: -GHAN- (hansen@ahp-gmbh.de)
  8. Hi G?nther, you are correct that UPPER/LOWER can be done "on the fly" by each database. But my point is: only with MATCHCODE you can build an index in the database which REALLY speeds up the case-independend select process. Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)