This session presents a walk-through of Visual FoxPro’s new Offline View capability. Learn why you might want to implement these as part of your next application, and how to use them effectively. Best of all, they’re more useful than you think even when there’s no requirement for “on-the-road” functionality.
One of the most interesting new features of Visual FoxPro 5.0 is the first appearance of offline views in the product. Before delving into the mechanics of how they work, lets begin with a brief description and consider some application development scenarios in which they might be a useful asset.
In a sentence, an offline view is an “altered state” of a standard Visual FoxPro updateable view in which the cursor (e.g. result table) can be used as a collector of updates while physically detached from the source database.
If you haven’t yet implemented an updateable view in a production application, you should review their capabilities. While they are a very new (and possibly mysterious) technique to traditional Xbase developers, they work well, offer some engineering advantages, and form the basis of a strategy to migrate an application to a Client Server data store. Since offline views derive from updateable views, a review of updateable view technology in Visual FoxPro is recommended.
The most obvious scenario in which this technology is put to use is the “road warrior”: the traveling salesperson who needs to work with a database application while on the road –perhaps in a hotel room or at a client site. Based on the application’s business rules, some features would be impossible, (e.g. decrementing inventory as orders are placed) but many common business functions (like order entry) could be fulfilled without a physical connection to the source data. We’ll discuss this and other scenarios in more detail later.
The basic steps of execution are:
The tools for programmatic control of offline views are Spartan, but sufficient to get the job done.
CREATEOFFLINE(ViewName [, cPath])
ViewName Specifies the name of the existing view to take offline.
cPath Specifies the directory in which the offline view is placed and the name of the offline view. See NOTE #3 below.
USE <SQL view name> [ ADMIN | ONLINE ] EXCLUSIVE
SQL view name is the name given to the offline view by CREATEOFFLINE().
ADMIN allows you to inspect and/or modify the data while connected to the source .DBC (e.g. server) without committing updates.
ONLINE loads all the updates from the offline view into a tablebuffer of the underlying source data. Process the updates with a standard TABLEUPDATE() sequence. (Note that if the source table is buffered, updates from the offline view go into the source table’s buffer, and must be managed with TABLEUPDATE( ).)
DROPOFFLINE(ViewName)
Returns TRUE if successful. Restores view definition to its normal (online) status.
NOTES:
Requires DBC to be open, and view to be closed.
Like CREATEOFFLINE() this function is not a reflection of the offline state. If already not in offline status, returns .F. Use dbgetprop(<viewname>,"VIEW","OFFLINE") to test a view’s offline status.
If successful, deletes the temporary table and transaction log files. (.DBF, .TBF and .TDX. .TPT if there is a memo field.)
dbgetprop(<viewname>,"VIEW","OFFLINE")
Returns TRUE if view is offline, FALSE if it is online.
Returns error (#1562) if view is not in database container.
The main issue of primary concern in handling updates from offline views is data update collisions. Again, the mechanics are not greatly different from those of online views. When Visual FoxPro detects a change has been made to underlying data that is now receiving it reports an error. (#1585 Update conflict.) See the sample application provided with this session for a specimen of a logic sequence to detect and respond to update conflicts.
Note that an offline view doesn’t change the fundamental technology for handling update conflicts. There will always be a certain amount of detailed fitting of the application’s methodology for handling updates to the actual business requirements. The offline view merely postpones the moment of decision until the updates are merged (although many of the rules can be moved to the point of entry for a double-check.)
Ideally, offline views are by nature appropriate to implement in situations where the business rules allow effective database updating while “unplugged” from shared data. For example, a simplifying assumption (e.g. each salesman’s territory is non-overlapping, therefore each customer has one and only one salesman) may allow for partitioning data into offline views with little chance of collision. However, exceptions do occur, and your code will have to handle them, as it must in regular updateable views.
The difference with offline views, is that that the actual entry point of data is both physically and temporally distant from the point of collision detection. In resolving a data collision resulting from an update posted to online view, a dialog box could be constructed to inform the user:
If this is insufficient to resolve the problem, with a small amount of custom coding it is possible to track who made the update that caused the conflict. A user table could provide phone numbers or even write e-mail to inform the other user that an override was elected for a given piece of data. The user detecting the conflict may have a customer on the phone, a physical source document open in front of them, or other evidence on which to base the judgment of which data is really correct.
In an offline view, the data collision will not be detected until the update is applied. This may be hours or days after the point at which the data was acquired. Since the batches of updates may be substantial, there may be numerous exceptions generated all at once. The system has to be prepared to handle them or a bottleneck will occur at a point when there may be insufficient information immediately available to resolve the matter.
It is recommended that careful consideration be given to this issue when implementing offline views. If there is any real likelihood of data collisions, it is best to plan for handling them in advance. The simplest approach is one of the brute force techniques:
When an updateable view is implemented, rules and triggers applied to the underlying data source are not propagated. Therefore, unless a corresponding set of rules and triggers are part of the view definition as well as the source tables, updates to the view will be accepted without firing rules and triggers associated with the underlying data. Instead, when the updates are finally applied to the data (usually through a TABLEUPDATE() routine) all the rules and triggers fire at that time.
For that reason, careful planning is necessary when implementing views, and offline views in particular, to assure that logical and referential integrity, as well as business rule enforcement, are managed in the application. In simpler systems, this may mean choosing to enforce data integrity rules at the “surface” as well as at the engine level – meaning that they have to be written twice. Of course, keeping the two sets consistent through the course of periodic modification becomes an ongoing responsibility of maintenance programming.
In larger, more complex applications, it may be well worth the investment to construct an engine-based view generator utility built following the model of GENDBC. It should be noted that Visual FoxPro 5.0’s improvements to DBC management include releasing the restriction on the requirement for EXCLUSIVE use requirement for adding and modifying views, so this approach requires only writing the view construction syntax programmatically using the cryptic but relatively straightforward DBSETPROP( ) function.
Whether a view is collecting edits offline or online, the programming issues raised by rules and triggers are the same. At the point where the buffer finally flushes changes into the source data, rules and triggers can be expected to execute, and ensuing exceptions will need management.
You might ask, what if I coded the offline view functionality myself. Here’s what it would look like:
What application scenarios would benefit from offline views?
Most obviously, the road warrior scenario mentioned above. A salesman needs to take a subset of the database with him to support taking orders at customer’s site. This would probably include:
Consider a typical file-server based service-order processing system. A customer calls in, and a service order is written. Then an order processor plucks it from a queue, and takes action to fulfill it. The system needs to run nearly 24 x 7.
OK, so what happens when the system needs maintenance? You take everyone down, and they twiddle their thumbs while you sweat through some tweaks to the DBC, reindex routine, etc. Isn’t it comforting to know that while you are watching therm-bars, the cost of a staff of 40 is going right down the drain? Do you wonder why the operations manager is standing over your shoulder breathing loud and anxiously while you work?
Consider the same application with an offline view feature implemented.
Suddenly, the maintenance scenario is very different. Ten minutes before scheduled maintenance begins, (your mileage may vary based on the size of the datasets), everyone shuts down their working screens and runs their “take me offline” feature. When they get their “READY” messages, the appropriate data sets are established locally, and the staff goes right on working.
In all probability, the same interface can be used as the production screens. Since the users probably use a common view for online work, and separate views for offline work, you’ll probably want the data sources to be set dynamically at runtime. (You are probably familiar with the reasons to open data sources programmatically rather through the data environment in VFP 3.0. Here is just one more reason to do so.)
Before anyone notices the interruption, your maintenance work is done, and everyone is notified that the system is ready to be restored to normal online use. They finish their current record, then run their “Re-connect me” feature. Due to the nature of the process, there is very little likelihood of data collision, so all the updates flow into the source data successfully in little over a minute. The staff then goes back to their normal use of the system.
Note that the only “workflow” issue is how a newly entered order gets to a processor while the application is undergoing maintenance. If the queue file itself is offlined just once, and shared by all users, essentially 100% of the production processing can continue unimpeded. (Remember, offline views can be opened SHARE’d!)
Thus, building in offline support for an application with no “road warrior” requirement creates the opportunity to perform maintenance without disrupting the normal course of work, or incurring the high associated costs. Who knows, you might even get to do your maintenance work during the daytime shift!
A new data warehouse has been established for a national organization with several regional offices connected by a WAN. Various departments are charged with the responsibility of verifying and cleaning up chunks of the data for which they are responsible.
A series of offline views can be established on the local LAN’s to separate out the data to the responsible parties and export it to them. The offline views can be opened and edited in shared mode by multiple simultaneous users, using techniques identical to any other fileserver-based application with table buffering. When done, the updates can be sent back to headquarters to be applied to the master dataset.
A single view can be “offlined” only once. Therefore to allow each of a number of users to offline a portion of the data(either the same, different, or overlapping data sets) , a separate view must be available in the DBC corresponding to that user. If you create offline views on users’ local disks, it is certainly possible that many of these parallel offline views may have the same name and path. Visual FoxPro doesn’t seem to care which offline view is connected to which files. Experiments with renaming and copying the actual offline view files around indicates that there is no relationship between the offline files themselves and their pointer in the DBC. The key is a one-for-one correspondence between view logged in the DBC and offline pathnames.
Although not obvious at first glance, this scenario works without complications. Offline views are multi-user, and updates to them can be processed in the same way your applicaction manages multi-user edits to other shared data files. This capability might be helpful in the data-warehouse collection scenario described above.
Oops! If your application has a function that looks at shared data to determine the next available sequential key, there’ll be a hole in your logic when you try to do the same from offline. One common method used to help solve the “hole in the sequential numbering” problem is to create a table of numbers available for allocation. To assign the “next” sequential key from the table, lock the record long enough to place a “Pending” flag in the record for the returned number while it is in “checked out but not committed” status. (The user’s id, time and date, etc. can also be recorded, if desired.) When the record is committed, the flag is set to “Used” and the record can be cleared out of the file in the next housekeeping pass.
A sequential number allocation subsystem obviously takes more work than the “quick-and-dirty” method of peeking at the highest number in the production data, and assigning the next highest one. However, your investment will be repaid if a system built this way is upgraded to make use of offline views. A user going offline (for instance a salesman who will write new sales orders on the road) can be assigned a block of sequential numbers, and upon reconnecting online, can “return” the unused numbers as part of the process of posting new orders.
If the business rules allow writing the order and deferring the assignment of the sequential number until the order is posted, this provides another way of working around the problem.
Another approach is hashing the sequence number with identifiers for the region, salesman, etc., so the combination of salesman and sequence number is guaranteed to be unique. Of course, many existing systems will not support this type of alteration to sequencing numbers of basic business documents.
Murphy still being firmly employed in all IT departments, the inevitable will surely occur, and a set of offline view files will be lost or trashed. What happens? Obviously, the pending updates will be lost – they will never make it back to the source tables. What else?
Curiously, re-offlining the view doesn’t work. To try this, create an offline view in a distant subdirectory, then delete the offline view files. Try the same again, but it returns FALSE and nothing happens. Try DROP-OFFLINE() – it also returns false. However, it appears to reset the DBC so the next subsequent CREATE-OFFLINE()will work. Conclusion: Build a “reset” implementation into your application, with appropriate dialog box warnings.
Offline views appear to be a valuable addition to the suite of data-handling assets that make Visual FoxPro so versatile for data-centric application development. As well as supporting the classic “road-warrior” scenario, they provide a high-performance, low overhead technique for keeping an application functioning while performing maintenance, as well as mechanisms to support collecting and cleaning data from geographically disparate organizations without the overhead of replicated SQL servers. As usual, common sense in selecting the right tools for the job applies. An obvious use for an OLE automation call in VFP 5.0 is to expose the “offlining” logic by building it into a custom method of an application object, so it can be called directly. This can be hooked to a desktop icon or possibly called as part of a shutdown routine, to prepare the user’s environment to “hit the road”. Have fun!