PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Definition
  • When to use a materialized view?
  • Creating a materialized view
  • Overview
  • Creation from the data model editor
  • Creation by using the SQL code
  • Using a materialized view
  • Overview
  • Rights on a materialized view
  • Modifying the content of a materialized view
  • Index on a materialized view
  • WLanguage functions used to handle a materialized view
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview

Definition

A materialized view is a physical data source defined by an SQL query. It is physically created on the HFSQL server. It can be used like a HFSQL file by the applications that access the database.
The application can use or handle a materialized view by using the regular file management functions. The data used is not automatically updated: you must ask to update the data.

When to use a materialized view?

Using a materialized view presents several benefits:
  • Independence with respect to the physical structure of the data:
    The materialized view corresponds to an abstraction layer between the data structure and the data use. If the physical structure of data evolves, the application code does not change. Only the base query of view must be adapted to the new structure.
  • Process acceleration
    Using a materialized view is interesting if extracting data from the view is a long process and if the data does not have to be real time. For example:
    • statistics,
    • reporting,
    • databinding,
    • selections for mailshots,
    • targeting,
    • creating "flat" data files,
    • ...
Creating a materialized view

Overview

Several methods can be used to create a materialized view:
  • Create the materialized view from the data model editor. In this case, to use the materialized view, the data must be updated on the server. This update can be performed:
    • via the automatic data modification (performed when installing the application for example).
    • via HUpdateView.
  • Creating the materialized view by using the SQL "CREATE MATERIALIZED VIEW" code in the application.

Creation from the data model editor

To create a materialized view from the data model editor:
  1. Start the view creation wizard:
    • In the ribbon, on the "Analysis" pane, in the "Creation" group, expand "New" and select "View".
    • On the "Analysis" pane, select the "Views" folder then select the "New view" option of popup menu.
  2. In the wizard, select "Create a materialized view" then go to the next screen (arrow at the bottom of wizard).
  3. Specify the name of the view to create then select the edit mode of base query for the view:
    • Wizard. In this case, the query of the view is created via the wizard for query creation. The query is displayed graphically. The query can be modified at any time via the wizard.
    • SQL code. In this case, you directly enter the SQL code for selecting the elements selected by the view.
  4. The materialized view is created. By default, it is not associated with a connection. To associate the view with a connection defined in the data model editor:
    • On the "Analysis" pane, select the materialized view that was just created.
    • In the popup menu (right mouse click), select "Description of the view".
    • In the window that is displayed, you can modify the name of the materialized view and specify the connection associated with the materialized view.
    • Validate.
  5. Generate the analysis to use the materialized view in the code of the application: on the "Analysis" pane, in the "Analysis" group, expand "Generation" and select "Generation".
Notes:
  • A key or a composite key will be created based on the "ORDER BY" statement.
  • The materialized views cannot contain parameters.

Creation by using the SQL code

To create a materialized view using SQL code, all you have to do is use the CREATE MATERIALIZED VIEW command in the SQL code of the query. For example:
// Create the materialized view
MyMaterializedViewCode is string
MyMaterializedViewCode = [
Create materialized view View_Customer
AS SELECT * FROM Customer
]
HExecuteSQLQuery(Qry_View, HFSQLConnection, MyMaterializedViewCode)
Caution: The SQL code for creating the materialized view must refer to items and data files defined in the analysis only. For example, references to stored queries cannot be used in this code.
Using a materialized view

Overview

To use a materialized view, simply use the HFSQL functions for accessing data files. The <File Name> parameter will correspond to the name of the materialized view.
Like a standard data file, a materialized view can be:
  • browsed using the HFSQL functions (FOR EACH, HRead*, ...),
  • associated with a control (Table control, List Box control, ...).
Note: If the materialized view was not created in the data model editor, HDeclareExternal must be called before it can used.

Rights on a materialized view

A materialized view corresponds to a data file. Right management is handle the same way as for data files. These rights can be configured:
  • via the HFSQL Control Center (not available in this version). via the HFSQL Control Center.
  • via HModifyViewRights.
Note: HInfoViewRights is used to find out the current rights on the materialized view.

Modifying the content of a materialized view

The content of a materialized view is updated:

Index on a materialized view

To create an index on a materialized view, use CREATE INDEX. If the materialized view is refreshed, the indexes are automatically updated: there is no need to re-create the indexes.
WLanguage functions used to handle a materialized view
The following WLanguage functions are used to handle the materialized views:
Versions 20 and later
HInfoView
New in version 20
HInfoView
HInfoView
Returns information about a materialized view.
Versions 20 and later
HRefreshView
New in version 20
HRefreshView
HRefreshView
Asks to recalculate the content of a materialized view.
Versions 20 and later
HUpdateView
New in version 20
HUpdateView
HUpdateView
Creates or updates the description of a SQL view or materialized view from the analysis to a HFSQL server.
Minimum required version
  • Version 20
This page is also available for…
Comments
Click [Add] to post a comment