NoData, requery, and views of views (VFP-to-SQL Server)

I’m working on a project converting a client’s VFP 9 data to SQL Server. The VFP app uses local views (via data objects) that use native VFP data and local views. Swapping the native VFP data for SQL Server data isn’t as simple as redirecting a view from a DBF to a remote view. I should have realized this since, as I well know, VFP doesn’t automatically requery underlying views.

Say I have a view on a People table that selects the last name–call it lv_PeopleByLastName. It’s trivial to redirect the view to query remote data instead of local data. But if I then use lv_PeopleByLastName with NODATA, the remote view is also opened NODATA. That makes sense. The gotcha is that requering lv_PeopleByLastName does not also requery the remote view. So, even after Requery() the local view will still be empty since the remote view is empty. One must requery the remote view, then the local view.

IOW, once open every data source operates independently. VFP doesn’t automagically maintain dependency info about an open datasource (except through the database container, of course).

This should not have surprised me. It’s not fatal, just a complication I should have foreseen. So, I can’t simply redirect a view from local data to a remote view without changing either the views or the way dataobjects manage requerying.

Here are my options as best I can tell:

  1. Change the views so they don’t use other views. To be frank, SQL syntax and I are not BFFs. I’d rather stick a fork through my hand than work as a database manager. SQL feels like one is writing entire programs in a single line of code–the antithesis of maintainability. So, I like SQL streamlined as much as possible. Luckily most queries are fairly straightforward.
  2. I could rework the data objects and the views so that data objects cascade other data objects representing underlying views that would be requeried whenever the parent object is requeried. Okay, I confess. I just threw that in there because options should always come in threes.
  3. The data objects could make note of what underlying views are involved and requery them when the object is requeried. That works. It’s not elegant, but it is effective, and considering the bottle neck is generally fetching remote data, doesn’t add significant processing time. However, if more than view uses the same underlying data, it could make for some unnecessary requerying.

It looks like option 1, with option 3 held in reserve. Which I could have had done by now instead of posting. But I’ll call this organizing my thoughts rather than procrastinating on the inevitable.

Advertisements

6 thoughts on “NoData, requery, and views of views (VFP-to-SQL Server)

  1. Nancy,

    Re : Option 3 : However, if more than view uses the same underlying data, it could make for some unnecessary requerying.

    If you do a topological sort, no unnecessary requerying well be done

    Say:

    ViewA
    – ViewB
    – ViewC
    – ViewB

    The output (or the order of requery will be )
    – ViewB
    – ViewC
    – ViewA

    • Hi, Gregory,

      That is true enough for an encapsulated data object. But in a larger system where there may be multiple data objects another one might requery the same back end. Using your example:

      Class 1
        - View A
          - View B
          - View C
      
      Class 2
        - View X
          - View B
      

      If a form, say, sends a message for a global requery, View B will be requeried twice.

      • Nancy,

        I’m not sure I understand. Of course – in your example – view B will be requeried twice since the context (view parameters) may be different (class/object 1 or 2)

        But doesn’t the same apply to views solely based on tables ?
        Class 1
        – View A (?FirstName like ‘Geoff%’)
        – Table1

        Class 2
        – View A (?FirstName like ‘Jeff%’)
        – Table1

  2. Gregory, Hm. Embedded replies seem to go only so deep. Hope this isn’t too confusing.

    > I’m not sure I understand. Of course – in your example – view B will be requeried twice since the context (view parameters) may be different (class/object 1 or 2)

    But the context, in my example, _is_ the same. If the dataobjects are operating under different contexts, say, forms, then the requerying is fine. But if both coexist within another then the requerying will be redundant. That’s sort of the just the price to pay for encapsulated classes. But it’s more of an issue using a SQL Server backend instead of the native VFP backend.

    As for the second comment about tables, the issues is that what was a native table in VFP is now a remote view on a SQL backend. So, where the system wasn’t requerying tables before, it will be requerying views now. Does that make more sense? When I clear the decks maybe I’ll try to write more lucidly about this.

    • Nancy,

      OK, then – (1) for a global requery and (2) same contexts, the same applies

      Given in the same context
      – ViewA
      – ViewB
      – ViewC

      – ViewX
      – ViewB

      If I put them all in a topological sort
      (Successor, Predecessor)

      (‘ViewA’, ‘ViewB’)
      (‘ViewA’, ‘ViewC’)
      (‘ViewC’, ‘ViewB’)
      (‘ViewX’, ‘ViewB’)

      I get this output
      ViewB
      ViewC
      ViewX
      ViewA

      So, instead of doing the exercise per view, for a situation where the context is the same, you feed the dependencies (Successor, Predecessor) of all the views, the do the topological sort only once

      The principle does not really change. We have one bag per context to sort topologically, where a context can be one or more views sharing the same context

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s