ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing HFSQL Client/Server
  • 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/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview

Definition

A materialized view is a physical data source defined by an SQL query. This materialized view is physically created on the HFSQL server. It can be used like an HFSQL data file by 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,
    • data binding,
    • 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" tab, in the "Creation" group, expand "New" and select "View".
    • On the "Analysis" pane, select the "Views" folder then select the "New view" option of context 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 context 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" tab, in the "Analysis" group, expand "Generation" and select "Generation".
Remarks:
  • 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:
Qry_View is Data Source
 
// 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, ...).
Remark: 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:
Remark: 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:
  • via the SQL "REFRESH MATERIALIZED VIEW" statement.
  • via HRefreshView.
  • via a refresh scheduled task. This task is defined via a hScheduleMaterializedView variable. Then, this task can be handled via the following functions:
    HAddSchedulingCreates a scheduled task on an HFSQL server:
    • stored procedure,
    • backup,
    • optimization,
    • refresh of materialized view.
    HDeleteSchedulingDeletes a scheduled task found on an HFSQL server:
    • stored procedure,
    • backup,
    • optimization,
    • refresh of materialized view.
    HExecuteSchedulingImmediately runs a scheduled task regardless of its schedule:
    • stored procedure,
    • backup,
    • optimization,
    • refresh of materialized view.
    HModifySchedulingModifies a scheduled task on an HFSQL server:
    • stored procedure,
    • backup,
    • optimization,
    • refresh of materialized view.

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:
HInfoViewReturns information about a materialized view.
HRefreshViewAsks to recalculate the content of a materialized view.
HUpdateViewCreates or updates the description of an SQL view or materialized view from the analysis to an HFSQL server.
Minimum version required
  • Version 20
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 01/26/2023

Send a report | Local help