1. CDC_API/create_mvlog
2. CDC_API/get_mvlog
3. SQL_API/add_expr
4. SQL_API/add_table
5. SQL_API/create
6. SQL_API/disable
7. SQL_API/enable
8. SQL_API/remove_expr
9. SQL_API/remove_table
10. SQL_API/set_definition
11. UTIL_API/get_id
12. UTIL_API/get_sql
13. UTIL_API/refresh
14. UTIL_API/refresh_all
15. UTIL_API/rename

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

v_schema - The schema (aka database) in which the table resides
v_table_name - The name of the table you want to log changes on

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

v_table_schema - The schema which contains the materialized view
v_table_name - The name of the table which contains the rows for the view

RESULT

NULL will be returned if the table does not exist or is not logged
EMPTY STRING is returned if logging on the table is disabled
The name of the table will be returned otherwise

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

v_mview_id -- The materialized view id (see flexviews.get_id)
v_expr_type -- GROUP|BIT_AND|BIT_OR|BIT_XOR|COLUMN|SUM|COUNT|MIN|MAX|AVG|COUNT_DISTINCT|UNIQUE|KEY|PERCENTILE|STDDEV_POP|STDDEV_SAMP|VAR_SAMP|VAR_POP
v_expr -- The expression to add. Any columns in the expression must be prefixed with a table alias created with flexviews.add_table. When the UNIQUE or KEY expression types are used, the user must specify one more more aliases to index. These are normally aliases to GROUP expressions.
v_alias -- Every expression must be given a unique alias in the view, which becomes the name of the column in the materialized view. For UNIQUE and KEY expressions, this will be the name of the index in the view. You must NOT use any reserved words in this name.

NOTES

Possible values of v_expr_type (a string value):

EXPR_TYPE	Explanation
GROUP	GROUP BY this expression.
COLUMN	Simply project this expression. Only works for views without aggregation.
COUNT	Count rows or expressions
SUM	SUM adds the value of each expression. SUM(distinct) is not yet supported.
MIN	MIN (uses auxilliary view)
MAX	MAX (uses auxilliary view)
AVG	AVG (adds SUM and COUNT expressions automatically)
COUNT_DISTINCT	COUNT(DISTINCT) (uses auxilliary view)
STDDEV_POP	Standard deviation population(uses auxilliary view)
STDDEV_SAMP	Standard deviation sample(uses auxilliary view)
VAR_POP	Variance population (uses auxilliary view)
VAR_SAMP	Variance sample (uses auxilliary view)
GROUP_CONCAT	Group concatenation - NOT YET SUPPORTED (uses auxilliary view)
BIT_AND	BIT_AND(uses auxilliary view)
BIT_OR	BIT_OR(uses auxilliary view)
BIT_XOR	BIT_XOR(uses auxilliary view)
KEY	Adds 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

v_mview_id - The materialized view id (see flexviews.get_id)
v_table_schema - The schema which contains the table to add
v_table_name - The name of the table to add
v_table_alias - The table alias to use in the view. All tables MUST have an alias.
v_join_clause - Every table after the first must have a NOT-NULL join clause

NOTES

For views with a single table, or for the first table on a view with joins, the last paramter of the function will be NULL.
Additional tables MUST provide a valid ON or USING clause. CROSS JOIN/cartesian products ARE NOT SUPPORTED.
All expressions used in an ON clause must be prefixed with a valid table alias!

RESULT

An error will be generated in the MySQL client if:

The table does not exist
There is no materialized view log on the table

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

v_schema - The schema (aka database) in which to create the new view
v_mview_name - The name of the materialzied view to create
v_refresh_type - ENUM('INCREMENTAL','COMPLETE')

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:

COMPLETE - The view is completely replaced with CREATE TABLE .. AS SELECT. Slow, but works with all SQL.
INCREMENTAL - Changelogs are used to update the view only based on the rows that changed. Fast, but only works with a subset of SQL.

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

If the view can not be created for some reason, an error message will be displayed.
You can check the values of @MV_DEBUG and @v_sql which will contain the SELECT statement and CREATE TABLE statement for the view, respectively.

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

v_mview_id - The materialized view id
v_alias - The alias that was specified for the expression when you added it to the view

NOTES

You can not modify an enabled view.
This function is most often used to correct a mistake or typo made when creating the view.

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

v_mview_id - The materialized view id (see flexviews.get_id)
v_table_alias - The table alias you used when you added the table to the view.

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

v_mview_schema - The schema which contains the materialized view
v_mview_name - The name of the table which contains the rows for the view

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:

v_mview_id - The materialized view identifier
v_mode - COMPLETE|COMPUTE|APPLY|BOTH
v_uow_id - (MAY BE NULL) When a uow_id (transaction id) is provided the mode selected above will operate only on records created at or before the given uow_id. If a NULL value is provided, then the most recent committed transaction id will be used.

NOTES

v_mode:

v_mode	explanation
COMPLETE	COMPLETE 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
COMPUTE	COMPUTE 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.
APPLY	APPLY is used to apply any un-applied changes from previous COMPUTE runs
BOTH	BOTH 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:

v_mview_id - The materialized view identifier
v_mode - COMPLETE|COMPUTE|APPLY|BOTH
v_uow_id - (MAY BE NULL) When a uow_id (transaction id) is provided the mode selected above will operate only on records created at or before the given uow_id. If a NULL value is provided, then the most recent committed transaction id will be used.

NOTES

v_mode:

v_mode	explanation
COMPLETE	COMPLETE 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
COMPUTE	COMPUTE 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.
APPLY	APPLY is used to apply any un-applied changes from previous COMPUTE runs
BOTH	BOTH 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

v_mview_id - The materialized view id (see flexviews.get_id)
v_new_schema - The new schema, may be the same as the current schema
v_new_table - The new table, may be the same as the current table

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')

TABLE OF CONTENTS

1. CDC_API/create_mvlog [ Functions ]

2. CDC_API/get_mvlog [ Functions ]

3. SQL_API/add_expr [ Functions ]

4. SQL_API/add_table [ Functions ]

5. SQL_API/create [ Functions ]

6. SQL_API/disable [ Functions ]

7. SQL_API/enable [ Functions ]

8. SQL_API/remove_expr [ Functions ]

9. SQL_API/remove_table [ Functions ]

10. SQL_API/set_definition [ Functions ]

11. UTIL_API/get_id [ Functions ]

12. UTIL_API/get_sql [ Functions ]

13. UTIL_API/refresh [ Functions ]

14. UTIL_API/refresh_all [ Functions ]

15. UTIL_API/rename [ Functions ]