TABLE OF CONTENTS

1. CDC_API/create_mvlog [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.create_mvlog - Create a table changelog for a MySQL table

SYNOPSIS

flexviews.create_mvlog(v_schema, v_mview_name)

FUNCTION

This function creates a table change log (aka materialized view log) on a particular table in the database. Any data changes made to the table will be recorded in the table change log by the Flexviews binary log consumer (FlexCDC).

You do not need to use this function if you use the auto-changelog mode of FlexCDC. When this mode is used, change logs are made automatically when a change for a table is detected and the log has not yet been created.

If you use temporary tables, or you have only a small number of tables to log, then you might consider not using auto-changelogging and instead only log certain tables. Use this function to add a table to the list of tables to log. Once added a table may not be removed.

INPUTS

RESULT

An error will be generated in the MySQL client if the changelog could not be created.

EXAMPLE

mysql>

       call flexviews.create_mvlog('test', 'my_table');

2. CDC_API/get_mvlog [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.get_mvlog - Get the materialized view log name for the given table in the given schema

SYNOPSIS

flexviews.get_mvlog(v_mview_schema, v_mview_name)

FUNCTION

This function returns the materialied view log name for the table.

INPUTS

RESULT

EXAMPLE

mysql>

    select flexviews.get_mvlog('test','mv_example');

3. SQL_API/add_expr [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.add_expr - Add an expression or indexes to a materialized view.

SYNOPSIS

flexviews.add_expr(v_mview_id, v_expr_type, v_expression, v_alias)

FUNCTION

This function adds an expression or indexes to the materialized view definition. This function may normally be called only on disabled materialized views. The exception are KEY and UNIQUE index expressions, which may be added at any time.

The expression specified in v_expression is added to the materialized view. If your view will be using aggregate functions, then you should specify the 'GROUP' type for the non-agg columns. If your view does not use aggregation, then each expression should be of type 'COLUMN'. In the expression, any references to table columns must be fully qualified with the TABLE_ALIAS that you specified in the flexviews.ADD_TABLE() call. For example, if you added a table with alias 'T', then when you reference a column in that table, you must prefix the alias: call flexviews.add_expr(flexviews.get_id('test','mv1'),'GROUP','t.c1', 'group_on_t_c1');

WHERE expressions are added to the WHERE clause of the view.

The UNIQUE and KEY expressions represent indexes on the materialized view table. Note that UNIQUE and KEY expressions do not reference base table columns, but instead you must specify one or more expression aliases. This is similar to the use of table aliases in expressions (see above). See the examples below for more info.

INPUTS

NOTES

Possible values of v_expr_type (a string value):

EXPR_TYPEExplanation
GROUPGROUP BY this expression.
COLUMNSimply project this expression. Only works for views without aggregation.
COUNTCount rows or expressions
SUMSUM adds the value of each expression. SUM(distinct) is not yet supported.
MINMIN (uses auxilliary view)
MAXMAX (uses auxilliary view)
AVGAVG (adds SUM and COUNT expressions automatically)
COUNT_DISTINCTCOUNT(DISTINCT) (uses auxilliary view)
STDDEV_POPStandard deviation population(uses auxilliary view)
STDDEV_SAMPStandard deviation sample(uses auxilliary view)
VAR_POPVariance population (uses auxilliary view)
VAR_SAMPVariance sample (uses auxilliary view)
GROUP_CONCATGroup concatenation - NOT YET SUPPORTED (uses auxilliary view)
BIT_ANDBIT_AND(uses auxilliary view)
BIT_ORBIT_OR(uses auxilliary view)
BIT_XORBIT_XOR(uses auxilliary view)
KEYAdds an index to the view. Specify column aliases in v_expr.
PERCENTILE_##Adds a percentile calculation to the view.

SEE ALSO

flexviews.enable, flexviews.add_table, flexviews.disable

EXAMPLE

mysql>

     set @mv_id = flexviews.get_id('test', 'mv_example');
     call flexviews.add_table(@mv_id, 'schema', 'table', 'an_alias', NULL);

     #add a GROUP BY 
     call flexviews.add_expr(@mv_id, 'GROUP', 'an_alias.c1', 'c1');

     #add a SUM
     call flexviews.add_expr(@mv_id, 'SUM', 'an_alias.c2', 'sum_c2');

     # add indexes 
     call flexviews.add_expr(@mv_id, 'KEY', 'c1,sum_c2', 'key2');
     call flexviews.add_expr(@mv_id, 'UNIQUE', 'alias1,alias2,alias3', 'index_name');

     # calculate the 95th percentile of an expression
     call flexviews.add_expr(@mv_id, 'PERCENTILE_95', 'c1', 'pct_95');

4. SQL_API/add_table [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.add_table - Add a table to the FROM clause of the materialized view.

SYNOPSIS

flexviews.add_table(v_mview_id, v_table_schema, v_table_name, v_table_alias, v_join_clause);

FUNCTION

This function adds a table to the FROM clause of the materialized view.

INPUTS

NOTES

RESULT

An error will be generated in the MySQL client if:

SEE ALSO

flexviews.disable, flexviews.get_id

EXAMPLE

mysql>

     set @mv_id = flexviews.get_id('test', 'mv_example');
     call flexviews.add_table(@mv_id, 'schema', 'table', 'an_alias', NULL);
     call flexviews.add_table(@mv_id, 'schema', 'table2', 'a2', 'ON an_alias.c1 = a2.c1');

5. SQL_API/create [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.create - Create a materialized view id for the view. This is the very first step when creating a new materialized view.

SYNOPSIS

flexviews.create(v_schema, v_mview_name, v_refresh_type)

FUNCTION

This function creates a materialized view id for a new materialized view. The materialized view identifier is stored in LAST_INSERT_ID() and is also accessible using flexviews.get_id()

INPUTS

RESULT

An error will be generated in the MySQL client if the skeleton can not be created.

NOTES

Every materialized view has a unique identifier assigned to it by this function. Almost every other Flexviews function takes a materialized view id (mview_id) as the first parameter. v_refresh_type:

Note that this command will not immediately create the table. It will be created only when the view is ENABLED.

SEE ALSO

SQL_API/enable, SQL_API/add_table, SQL_API/add_expr

EXAMPLE

mysql>

     call flexviews.create('test', 'mv_example', 'INCREMENTAL');
     call flexviews.create('test', 'another_example', 'COMPLETE');

6. SQL_API/disable [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.disable - Drop the materialized view table.

SYNOPSIS

flexviews.disable(v_mview_id);

FUNCTION

This function drops the table holding the rows for the materialized view. There is no warning and the table is dropped as soon as this command is issued.

INPUTS

v_mview_id - The materialized view id

RESULT

An error will be generated in the MySQL client if the view can not be disabled.

NOTES

The dictionary information is not removed, instead the metadata is updated to reflect the disabled status.

SEE ALSO

SQL_API/create, SQL_API/enable, SQL_API/get_id

EXAMPLE

mysql>

    call flexviews.disable(flexviews.get_id('test','mv_example'))

7. SQL_API/enable [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.enable - Materialize a view which has not yet been materialized. Once a view is enabled its structure can not be altered unless it is disabled.

SYNOPSIS

flexviews.enable(v_mview_id);

FUNCTION

This function creates the table which will hold the rows for the materialized view. It then uses INSERT .. SELECT to populate the view. Once the call to this function is made, any DML statements made on the base tables will be reflected in the view when it is refreshed.

INPUTS

v_mview_id - The materialized view id

RESULT

An error will be generated in the MySQL client if the view can not be enabled.

NOTES

SEE ALSO

flexviews.disable, flexviews.get_id

EXAMPLE

call flexviews.enable(flexviews.get_id('test','mv_example'))

8. SQL_API/remove_expr [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.remove_expr - Remove an expression or indexes from a materialized view.

SYNOPSIS

flexviews.remove_expr(v_mview_id, v_alias)

FUNCTION

This function removes the expression with the given alias from the materialized view.

INPUTS

NOTES

SEE ALSO

flexviews.enable, flexviews.add_table, flexviews.add_expr

EXAMPLE

mysql>

     set @mv_id = flexviews.get_id('test', 'mv_example');
     call flexviews.add_table(@mv_id, 'schema', 'table', 'an_alias', NULL);
     call flexviews.add_expr(@mv_id, 'PRIMARY', 'c1*oops*', 'pk'); # ADD AN EXPRESSION WITH A PROBLEM
     call flexviews.remove_expr(@mv_id, 'pk'); # REMOVE THE EXPRESSION

9. SQL_API/remove_table [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.remove_table - Remove a table from a materialized view.

SYNOPSIS

flexviews.remove_table(v_mview_id, v_table_alias);

FUNCTION

This function removes a table from a materialized view. Any expressions which reference this table must also be removed manually!

INPUTS

RESULT

An error will be generated if the view is enabled. No error is raised if the given alias does not exist in the view.

SEE ALSO

flexviews.disable, flexviews.get_id

EXAMPLE

mysql>

     set @mv_id = flexviews.get_id('test', 'mv_example');
     call flexviews.add_table(@mv_id, 'schema', 'table', 'an_alias', NULL);
     call flexviews.remove_table(@mv_id, 'an_alias'); #remove the table we just added

10. SQL_API/set_definition [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.set_definition - sets the SQL SELECT statement to be used by the CREATE TABLE AS ... SELECT statement which is used for COMPLETE refresh materialized views.

SYNOPSIS

flexviews.set_definition(v_mview_id, v_sql);

FUNCTION

COMPLETE refresh materialized views are created and refreshed with CREATE TABLE ... AS SELECT. The SELECT statement provided in v_sql is used to create the view.

INPUTS

v_mview_id - The materialized view id (see flexviews.get_id) v_sql - The SELECT statement to use for the view

RESULT

If the UPDATE does not succeed then the error will passed to the MySQL client

SEE ALSO

SQL_API/disable, UTIL_API/flexviews.get_id, SQL_API/flexviews.enable

EXAMPLE

mysql>

     call flexviews.set_definition(flexviews.get_id('test','mv_example'), 'SELECT * from my_table where c1=1')

11. UTIL_API/get_id [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.get_id - Get the materialized view id for the given view in the given schema

SYNOPSIS

flexviews.get_id(v_mview_schema, v_mview_name)

FUNCTION

This function returns the materialied view id for the given materialized view.

INPUTS

RESULT

NULL will be returned if the view does not exist.

EXAMPLE

mysql>

    set @mv_id := flexviews.get_id('test','mv_example');

12. UTIL_API/get_sql [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.get_sql - Retrieve the SELECT statement which would represent the materialized view in a regular view

SYNOPSIS

flexviews.get_sql(v_mview_id)

FUNCTION

This function returns the SELECT statement which would be used by a normal view for the given expressions, tables etc which have been created for the materialized view.

INPUTS

v_mview_id - Id of the view

RESULT

The SQL for the view.

EXAMPLE

mysql>

     call flexviews.get_sql(1);
     call flexviews.get_sql(flexviews.get_id('test','test_mv'));

13. UTIL_API/refresh [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.refresh - Applies changes made to the database since the materialized view was created.

SYNOPSIS

flexviews.refresh(v_mview_id,

FUNCTION

This function initiates the refresh process for the given view. The process varies depending on the type of view.

INPUTS

This function takes a combination of input parameters:

NOTES

v_mode:

v_modeexplanation
COMPLETECOMPLETE is used only for COMPLETE refresh materialized view. The view is refreshed from scratch using a combination of CREATE TABLE, INSERT INTO and RENAME TABLE
COMPUTECOMPUTE is used for INCREMENTAL tables. It computes the changes since the last refresh but it does not apply them. Low cost frequent computations can be made while maintaining the transactional consistency of the view at the last refresh point in time.
APPLYAPPLY is used to apply any un-applied changes from previous COMPUTE runs
BOTHBOTH executes a COMPUTE followed by an APPLY

SEE ALSO

flexviews.enable, flexviews.add_table, flexviews.add_expr

EXAMPLE

mysql>

     set @mv_id = flexviews.get_id('test', 'mv_example');
     call flexviews.refresh(@mv_id, 'BOTH', NULL);

NOTES

The external binary log consumer MUST BE RUNNING in order to COMPUTE changes to views!

14. UTIL_API/refresh_all [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.refresh_all - Computes or applies changes or completely refreshes every enabled materialized view, serially.

SYNOPSIS

flexviews.refresh(v_mview_id,

FUNCTION

This is a convenience function... This function initiates the refresh process for all enabled views. It operates only on one type of view at a time. That is, if you specify 'COMPLETE', only 'COMPLETE' views will be refreshed. Conversely, complete refresh views won't be refreshed if 'COMPUTE' or 'APPLY' is specified.

INPUTS

This function takes a combination of input parameters:

NOTES

v_mode:

v_modeexplanation
COMPLETECOMPLETE is used only for COMPLETE refresh materialized view. The view is refreshed from scratch using a combination of CREATE TABLE, INSERT INTO and RENAME TABLE
COMPUTECOMPUTE is used for INCREMENTAL tables. It computes the changes since the last refresh but it does not apply them. Low cost frequent computations can be made while maintaining the transactional consistency of the view at the last refresh point in time.
APPLYAPPLY is used to apply any un-applied changes from previous COMPUTE runs
BOTHBOTH executes a COMPUTE followed by an APPLY

SEE ALSO

flexviews.enable, flexviews.add_table, flexviews.add_expr

EXAMPLE

mysql>

     set @mv_id = flexviews.get_id('test', 'mv_example');
     call flexviews.refresh(@mv_id, 'BOTH', NULL);

NOTES

FlexCDC MUST BE RUNNING in order to COMPUTE changes to views!

15. UTIL_API/rename [ Functions ]

[ Top ] [ Functions ]

NAME

flexviews.rename - Rename a materialized views

SYNOPSIS

flexviews.rename(v_mview_id, v_new_schema, v_new_table);

FUNCTION

This function renames the given materialized view.

INPUTS

RESULT

An error will be generated in the MySQL client if the view can not be enabled.

SEE ALSO

SQL_API/disable, UTIL_API/get_id, SQL_API/enable, SQL_API/create

EXAMPLE

mysql>

     call flexviews.rename(flexviews.get_id('test','mv_example'), 'test', 'new_name_example')
     call flexviews.rename(flexviews.get_id('test','mv_example'), 'new_schema_example', 'test')
     call flexviews.rename(flexviews.get_id('test','mv_example'), 'new_schema', 'and_new_table')