How to deal with VFP's local and remote views?

[epschaffer@gmail.com]

Hi Lianja Team,

I have an app containing approx 70 tables and 130+ local views.

This app started life in the Foxbase days, when Unix was supported, and was reluctantly ported to Windows at clients request, read insistence.
It now runs under VFP 9 SP2+.

At some stage, it shall be re-written to Lianja; imo the only REAL VFP alternative.

Lianja does not import a DBC's local and remote views.

The app's DBC has a few stored procedures, no triggers to speak of. No relationships have been specified; as parametrized views pull data in using the primary keys of the tables required.

I assume that it is possible in Lianja to replace a DBC's views with local and remote virtual tables.

Can someone knowledgeable please enlighten me?

Is it correct to assume that:
1. physical tables can be used in more than 1 virtual table?
2. it is possible to have virtual tables that contain columns from more than one table?

Is the FetchAsNeeded property of a virtual table, comparable to VFP's NoDataOnload? Meaning that virtual tables are only populated after a Requery?

What it boils down to, is that I have know how to work around the absence of views in order to be able to estimate the amount of work required to port the app to Lianja.

Any help is greatly appreciated.

Thanks!

[barrymavin]

To answer your questions:

1. physical tables can be used in more than 1 virtual table?

Yes. A VT is like a Connection and a remote view but it also handles transparent CRUD (Create Read Update and Delete) operations irrespective of the SQL dialect used by the remote data source.

2. it is possible to have virtual tables that contain columns from more than one table?

Yes. Just remember to exclude the columns from updates and inserts. This ability is built-in as is the basetable property so the database engine can insert and update data in the remote data source even though you may have fetched data that is a complex join that contains columns from multiple tables.

Is the FetchAsNeeded property of a virtual table, comparable to VFP's NoDataOnload? Meaning that virtual tables are only populated after a Requery?

No, FetchAsNeeded is being implemented so that the records in the local cursor that is created from the SQL SELECT will be fetched from the remote data source when referenced rather than when the virtual table is opened. This is to provide better performance and to be able to see remote data in real time without it having to be requeried. The best way is for you to create the Virtual Tables WHERE 1=0 then use requery( condition ) on the CursorAdapter() for the Virtual Table. This will populate the local cursor only with the data that matches the condition of the requery() and performance will be optimized.

Note that In Lianja the ODBC connections are pooled (and reference counted) so one ODBC connection will handle multiple Virtual Tables to optimize performance.

[epschaffer@gmail.com]

Thank you Barry, for your prompt and clear answer.

I have gone through the enhancement requests and Lianja roadmap and have found no reference to importing views. So I am concluding that there is at present no intention to implement a view import functionality.

Is there a possibility that you might reconsider and implement the conversion? Or should this question be submitted as enhancement request?

An alternative could be that Hank and his ERD4Lianja team, will offer some solace by converting views in Xcase models to virtual tables

Ultimately, if all else fails, with the number of views I have, it shall probably be worth my while to put the create virtual table command to good use.

Thanks again so far.

[barrymavin]

The importers that are used to import databases and projects are all written in Lianja and the source code of these is included in the distribution. Look in the library directory for all files like vfp_import_*.prg.

There is no reason why views could not be imported and a Virtual Table definition created for them insofar as the base tables are imported also. It's probably not a difficult task to accomplish as all the hard work is done in the vfp_import_dbc.prg script. All that is required is to recognize a view then create a virtual table with the same name as the view.

CREATE VIRTUALTABLE viewname CONNSTR "local" AS sql_select_command

A "local" CONNSTR causes the CursorAdapter to execute the SQL SELECT locally using the embedded database engine rather than connect remotely.

The ALTER VIRTUALTABLE command can be used to alter any of the clauses used in the CREATE TABLE command including altering any properties such as those that exclude columns and define the basetable for inserts and updates.

Special note: Remember a dbc database container in VFP is just a regular table so you can USE "fullpath_name.dbc" and inspect all of the columns from the console. Also, in Lianja you can inspect memo fields just by issuing ? nameoffield in the console.

[epschaffer@gmail.com]

Thank you again Barry.

I can see the advantages of a "local" connection and do not see any impediments on generating code for all the views in a DBC.

Sometimes however users demand a different backend e.g. Pervasive, not to mention a certain SQL server. (I should have used 'decision makers' instead of users! Those curious types that want to hear .NET and all that jazz)

Where I come from we named local views with a "lv_" prefix, while remote views were given an "rv_" prefix. All views were referenced dropping the first letter of the prefix and a global setting instructed the data layer to either use the local or global views.

How does one solve this predicament in Lianja?

Can CONNSTR properties be modified on the fly?

Could one use a similar technique, e.g. by creating local and remote virtual tables and giving corresponding virtual tables, identical alias's?

For now I am quite content with the outcome of our discussion. One more step in the right direction. Thanks.

[barrymavin]

Thats the whole point of Virtual Tables, they are data source independent. They are all contained within a database.

You can also have a different database containing Virtual table definitions for each database type that you want to support.

You can issue:

USE vt_name CONNSTR "..."

or alter the VT definition.

ALTER VIRTUALTABLE vt_name CONNSTR "..."

Also, COPY DATABASE will copy over one set of VT definitions to another database.

Yes, ALIAS is a keyword clause when you create a VT and thats what it will be seen as internally after it is opened. You cannot obviously have two VTs open with the same ALIAS.

[epschaffer@gmail.com]

Thanks for now Barry, you have been a great help.

[epschaffer@gmail.com]

Hello, fellow developers looking to import your local views into Lianja,

To simulate local VFP views in Lianja, I create a virtual table for each view, using a routine to generate a script (prg); which I run from the console in Lianja.

(Discovered that the use of multiple underscores in a virtual table name is not a good idea; macro's do not work in the console; simulated NODATAONLOAD=.T. by "where 1=0", connstr="local")

Having had the opportunity to test the generated virtual tables for a few days, on tables, of max 1.5 GB, I have not seen performance reasons for maintaining virtual tables. Let me emphasize using my DBC's and environment.

I do not yet know how to quickly and easily move around or hide elements in forms. So to present a subset of the fields in records and to influence the order in which they appear, I now use virtual tables. This is most likely to change in the future.

I have dropped all my "old" virtual tables and am in the process of removing loads of indices or is it indexes, from my tables. Many existed to optimize view performance and for reporting purposes.

Quite superfluously, in order to get meaningful data from the generated views, their "where" clauses need to be replaced by appropriate values. Using the cursoradapters's requery method, does the trick for me.

After the implementation of local views and cursoradapters in Foxpro, many considered updating tables directly a No No, I was one of them. Someone mentioned to me, that I had to get rid of my old ways of thinking and start thinking the "Lianja" way. I have not been able to discover a manual yet, but I suppose that my changed opinion on the importance of views, might be a good example and I hope a step in the right direction.

[HankFay]

Having had the opportunity to test the generated virtual tables for a few days, on tables, of max 1.5 GB, I have not seen performance reasons for maintaining virtual tables. Let me emphasize using my DBC's and environment.

Reasons to use virtual tables:

1) the ability to switch backends (to mssql, mysql, oracle, Amazon's Aurora, etc.)

2) the ability to scale the database (using a common, clustered backend, with multiple LSQL instances). (Note: this requires high-speed connections, database tuning, etc.)

3) the ability to select a sub-set of records for the user to work with, based on their search criteria.

...am in the process of removing loads of indices or is it indexes, from my tables. Many existed to optimize view performance and for reporting purposes.

If you are going to do reporting the old-fashioned way (SET RELATION), you will need the same indexes to make this happen.

Can you write a web app that works against tables? Yes: been there, done that.. However, as soon as you run into a table with lots of records, you will want to select from among them for displaying to the user: you're right back to virtual tables.

Will it allow you the flexibility you need to scale, or meet customers' needs for a particular backend database? No.

Will it complicate reporting? Yes.

My $.02.

Hank

[epschaffer@gmail.com]

Hello Hank,

Thanks for enlightening me. I am relieved to have left my Xcase models unchanged

Your $.02. are greatly appreciated, as always!

Ernst