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.

[HankFay]

HI Paul,

yes, we will create your VT's from the xCase views. It will be a little bit of work (for us, not you <s>) because Lianja's SQL parser does not (to my knowledge) currently handle the nested parens that xCase uses, but instead requires the name of the table being joined ahead of the JOIN syntax. It's all doable (I've already been experimenting with the conversion).

I've got one project I'm finishing up now, and then my "ProSysPlus" time will be focused on the PSP4Lianja ecosystem.

Hank

[epschaffer@gmail.com]

Hi Hank,


Hello again Hank,

That's entirely my idea; a little work for you and a fantastic tool for all of us.

I am eagerly awaiting the launch and in the meantime shall spend time, learning Lianja. I noticed "no coding developers" are being targeted, that group shall surely ... off topic!

Wishing you a prosperous time;-)

Ernst Paul

[HankFay]

Hi Paul,

"NoCode" (tm by Lianja) development has a long tradition in organizations. I've seen excel spreadsheets and a word table organizing very important activities in a Farraday-shielded. guarded location on a guarded site. They would have been much better off with Lianja, had it existed. <s>

And then, when the users want to do more, they can learn a little more and do it. Some of those users will become domain expert developers: not professional developers, but developers that know their domain well enough that with the appropriate tool they can get the work done. Professional developers, who don't know the domain, would a) be unlikely to do as well, in terms of capturing the user needs; and b) probably wouldn't do at all because they, the professional developers, are busy doing "real" work. And if the Professional Developers were to do the work, they would a) over-complicate it; and b) have a unacceptable timeframe ("we can get to it in 6 months" is the shortest timreframe I've heard in any organization).

Domain expert developers have the advantage of working with no meetings. They articulate their expertise through the app, which allows iterative development (do it until it feels right). Verbal communication is always partial, and thus the usual software development cycle whether agile or not has to go back and forth with the "stakeholders," in order to fill in the inevitable gaps. Frankly, I'm happy for my background in psychology when I'm in a situation where there is not a domain expert developer, as the skills needed to get a set of specifications is much closer to psychological interviewing than it is to software development. And I'm happiest when there is a domain expert developer who I can simply guide as to how they can accomplish what they need.

Hank

[epschaffer@gmail.com]

Hi Hank,

Quite!

Although I presume that CIO's in many financial, health and government institutions, are going to take some time in embracing the idea.

The combination of Lianja and PSP4Lianja, is going to be difficult to ignore. Hello Lianja!

Ernst Paul