Getting started with the Virtual File Accessor (VFA)
↵
|
Getting started with the Virtual File Accessor (VFA) |
|
In PROIV’s transparent SQL a file accessor in a cycle corresponds to a single SQL table, specified in a file definition. The virtual file accessor allows a file accessor in a cycle to retrieve the records of two or more SQL tables joined together and then filtered by criteria. The joining and the filtering are performed by the RDBMS to reduce the number of SQL transactions required and exploit the RDBMS’s query optimization. As with normal file accessors that use SQL file definitions, only the columns needed to populate used file variables are retrieved. Columns needed only to join tables do not have to be retrieved by PROIV as the join happens in the RDBMS. The filtering of records is done in a similar fashion to the “SQL Select” tab except that now selection criteria can be applied to any column in any file / table that makes up VFA.
The VFA has (limitations) compared to a file definition that is a single SQL table. The biggest ones are that the VFA can only be:
The benefit is in avoiding the need to read from multiple files separately and join the records in PROIV e.g. in an update cycle or report cycle.
It can be used in screen functions in certain kinds of cycles but not screen cycles themselves.
There is no file definition for a VFA. A VFA is a different kind of file accessor that is specified in place rather than from a file definition. The VFA is defined using two or more constituent file definitions, of the same SQL type. PROIV logic and dynamics have access to the file variables of each of the constituent files.
The VFA behaves like a normal file accessor. It has the same events to which logic can be attached. Logic cannot be attached to the individual constituent files that make up the VFA. This is not possible as the constituent files are being joined in the RDBMS, out of PROIV’s control. Logic used to control how the constituent files are joined and then their records filtered should now be done in the VFA specification (see later). The records can still be filtered in the After Read logic of the VFA but it is more efficiently done in the selection criteria of the VFA, by the RDBMS.
A VFA is created by turning the existing primary file accessor in a cycle into the VFA. If there is no existing primary file accessor then one will need adding to the cycle.In principle it could be any file definition but good practice is to use the one that is to be initial constituent file of VFA. Go to the properties of this file accessor and choose the VFA tab:
This shows the constituent files that will make up the VFA. It is initially empty and the developer will need to add at least two. Clicking “Add” automatically adds the file definition that the file accessor originally used. If this isn’t the intended file then “Delete” it and add the appropriate one. The “Add” and “Insert” buttons bring up an object selection dialog that is limited to files e.g.
The file “EG1_Authors” has been added so the VFA’s properties are now:
Pressing “OK” will close the (incomplete) VFA and a default name will be used:
The file accessor that just used the “EG1_Books” file definition has been replaced by the VFA named “VFA_DefaultCycle”. Although this VFA uses “EG1_Books” as a constituent file there was no obligation for it to do so.
The PROIV function won’t build yet as the VFA is missing join criteria between its two constituent files. Return to the “VFA” tab of “VFA_DefaultCycle”’s “Properties” dialog. Select “EG1_Authors” and click on the “Properties” button or double-click on “EG1_Authors”. A dialog will appear allowing us to specify how to join “EG1_Authors” to the previous constituent files of the VFA.
The join to the previous constituent files could be either “Inner” or “Left Outer”. This constituent file needs at least one join criteria against a previous constituent file of the VFA. Here “Column Name” refers to this constituent file, “EG1_Authors”, and “Author_Id” is a file variable in “EG1_Authors”. This file variable has to be equal to the file variable “Book_AuthorId” in the previous file “EG1_Books”. If there were more previous constituent files then it could have join criteria against one or more of them.
This dialog is similar to the “SQL Select” tab in a file accessor’s “Properties” dialog. Logical operators “And” and “Or” are used to combine individual join criteria. Parentheses are used to overcome logical operator precedence.
Pressing “OK” twice (exit “EG1_Authors” properties and exit “VFA_DefaultCycle” properties) returns to the PROIV function in Developer. The function can now be successfully built and can be run.
How to use the records returned by the VFA? The file variables of the constituent files of the VFA, “EG1_Books” and “EG1_Authors”, are available to use in the function like any other file accessor. As an example, here is the VFA populating a third file, “EG1_Report”:
Logic “001” is:
Where “Book_Id” is a file variable in “EG1_Books” made available via the VFA and “Report_BookId” is a file variable in “EG1_Report”. Similarly, Logic “002” is:
Where “Book_Title”, “Author_FirstName” and “Author_LastName” are available as they are file variables in the constituent files of the VFA. “Report_BookTitle” and “Report_Author” are in “EG1_Report”.
Currently all the records in the join between “EG1_Books” and “EG1_Authors” are being returned. This can be restricted by adding selection criteria to filter them. Return to the “VFA” tab of the “VFA_DefaultCycle”’s “Properties” dialog. Now select the initial constituent file, “EG1_Books”, and click “Properties” or just double-click the initial constituent file. This will open a dialog in which the selection criteria can be specified. Accessing this dialog via the initial file is done to make the Developer user interface simpler and does not reflect that these criteria are only for the initial constituent file. A dialog will appear as below except there are no selection criteria initially:
Again, for convenience, this dialog contains the edit box to change the VFA’s name. It has been renamed to “VFA_BooksAndAuthors”. Some selection criteria have been added to filter the records returned to PROIV. The screen is very similar to the existing “SQL Select” tab except the criteria can be on any constituent file so it must be explicitly stated. This example uses literals for the “Value” entries but they could be PROIV (scratch) variables instead.
Pressing “OK” returns to the VFA’s “Properties” dialog. Clicking on the “Expand” Icon, below the “Delete All” button, shows all the criteria:
This has been a very brief introduction to how to use a VFA in a PROIV function. There are a considerable number of conventions and limitations Virtual File Accessor Limitations about their use.
The previous section showed how to create a very simple VFA but also introduced all the dialogs that Developer uses to create and configure them.
The main one is the “VFA” tab of a file accessor’s “Properties” dialog. This contains a tree where the top-level nodes are the constituent files of the VFA and the children are join / selection criteria associated with them. Recall that the initial constituent file has no join criteria, and it is therefore convenient to associate the selection criteria with it. Even though that selection can use any constituent file or file variable. For subsequent files the children are always join criteria. The join / selection criteria can be displayed in this tab but not edited here. The constituent files’ “Properties” must be displayed to do this. The buttons have the following actions:
If a constituent file stops being the initial file (a new file is inserted before it) or becomes the initial file (file before it is deleted) then it will lose its join / selection criteria as they are no longer applicable to its new role.
Displaying the properties of a constituent file will either bring up a dialog containing the VFA’s name and row selection criteria or join type and join criteria, depending on whether it is the initial or subsequent constituent file respectively. Both contain a table for specifying the criteria and their layout is very similar to each other and the “SQL Select” tab. The VFA ones have additional columns though.
The columns common to both are:
The criteria only support one level of parentheses. Nested parentheses are not allowed.
The record selection criteria table, accessed via the initial constituent file, has the following specific columns:
Some operators do not have a right-hand side e.g. “Is Null”.
The join criteria table, accessed via the subsequent constituent files, has the following specific columns:
The acceptable operators differ depending on whether an entry is a condition against a previous constituent file and variable or not.
Developer will populate drop-down boxes with acceptable choices for the columns in both these tables. On some occasions the contents of the drop-down in later columns depends on the selection made in an earlier column e.g. “File” and “Column Name”.
The cut, copy and paste functionality of Developer can also be used on the VFA.
Developer will block the use of unacceptable characters in names.
Although the VFA is limited in the situations it can be used in, it should behave like other file accessors in those situations. It has the same events as other file accessors which trigger in the same situations.
Although the VFA must be the primary file, it can be used with other (non-VFA) secondary files. These secondary files may use transparent SQL to access tables in the same or different databases.
The timing / behaviour of a cycle is not affected by containing a VFA.
The aim of transparent SQL is for PROIV to be able to treat SQL tables like any other files. The disadvantage of this is that it neglects the benefits that the RDBMS has to offer. PROIV has existing techniques to try to use the RDBMS more. These range from the “SEL-ONLY” logic verb driving the where clause of the SQL statement at one end to full-function SQL at the other. The VFA is somewhere in the middle between transparent SQL and full-function SQL. It allows the RDBMS to do more of the work and reduces the number of SQL statement that need executing. A lot of the detail how this is done is hidden from the user, unlike in full-function SQL. It is no longer truly transparent SQL as this is something only SQL file definitions can do.
Related Topics
Document Function File Accessor
Report Function File Accessor
Screen Function File Accessor
Update Function File Accessor
SQL Where Clause
SQL Where Clause Behaviour on Secondary File(s)
Virtual File Accessor Advanced Operators
Virtual File Accessor Considerations
Virtual File Accessor Conventions and Limitations
Topic ID: 520203