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

1. 1/README [ Generics ]

[ Top ] [ Generics ]

NAME

Welcome to Flexviews!

SYNOPSIS

Flexviews is a unique toolkit for the creation and upkeep of MATERIALIZED VIEWS for MySQL. You may freely use this project in your own projects. Simply keep the above copyright notice intact.

A NOTE ABOUT DATABASES Flexviews must be installed into the `flexviews` schema. FlexCDC must be configured to use the `flexviews` schema as well. This is because the stored procedures must know where to find the Flexviews metadata, and an absolute reference to schema.table is the only safe way to avoid conflicts between the current default schema and the Flexviews schema. This is a shortcoming of MySQL stored procedures. Some day in the future, there will be a 'make' step that allows a global search/replace to switch the name `flexviews` to another name, but for now, you MUST install the tables and stored procedures in the `flexviews` schema.

A NOTE ABOUT UPGRADES There is no upgrade script currently. Please check future releases for more information about upgrading to that release. If you are using an older version of Flexviews, consider disabling all views, dropping the `flexviews` schema, and then installing from scratch.

MAJOR CHANGES IN THIS RELEASE * FlexCDC is completely rewritten as a PHP class and includes unit tests * New documentation built from RoboDOC comments embedded in the SQL stored procedures * Support for auto-changelog mode in FlexCDC, so table change logs do not have to be created manually with flexviews.create_mvlog. Note that you can elect not to use auto-changelog mode and instead manually register table changelogs with flexviews.create_mvlog(schema,table) * New Flexviews website with an online tool to convert SQL statements into Flexviews SQL_API calls.

COMING SOON * Flexviews test suite! * Autocleanup of changelog records which are no longer needed. The old mvlog_autocleanup script has not worked since the introduction of the external consumer. * Examples!

Quick Overview ------------------- installation: Change to the directory containing the install.sql file Connect to MySQL as the 'root' user (or any user with SUPER privilege) source the install.sql file set up FlexCDC (see the instructions in the consumer/ subdirectory) run the consumer (there is a .sh file provided) you must leave the consumer running at all times!

directories:

   * consumer - This directory contains FlexCDC which reads binary logs using mysqlbinlog and inserts entries into table change logs
   * procs - contains the source to the stored procedures.  usually invoked by install.sql - If you want to modify Flexviews this is probably the place to start.
   * php - contains misc scripts of no particular use to a wide audience, but that I want to keep under source control
   * schema - contains the CREATE DATABASE and CREATE TABLE statements for the flexviews schema.  usually invoked by install.sql

What is Flexviews My first experience with materialized views was with Oracle 8i. When my career went in a MySQL direction, the thing I missed most from Oracle was view materialization. I realised after a few years that my vision of materialized views would likely never make its way into the core MySQL server any time in the near future.

Flexviews supports SELECT-PROJECT-JOIN queries leveraging the algorithm described in 'How to roll a join': http://pages.cs.wisc.edu/~beyer/papers/matview_sigmod00.pdf (2000) Flexviews extends this algorithm to distributive aggregate functions with 'summary delta tables' Flexviews supports non-distributable aggregate functions such as MIN/MAX/COUNT(DISTINCT) by trading space-for-time using a secondary summary table, though this could be handled better with transparent query rewrite.

Many thanks to the authors of those papers.

Justin Swanhart

2. 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');

3. 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');

4. 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 only be called on disabled materialized views, though in the future adding indexes will be possible on enabled views. This adds the specified expression to the materialized view. If using aggregate functions, non-aggregated columns in the SELECT clause are GROUP expressions, otherwise, SELECT expressions are COLUMN expressions. Column references within an expression (regardless of type) must be fully qualified with the TABLE_ALIAS specified in flexviews.ADD_TABLE(). WHERE expressions are added to the WHERE clause of the view. The PRIMARY and KEY expressions represent keys on the materialized view table. Note that PRIMARY and KEY expressions do not reference base table columns, but instead you must specify one or more EXPR_ALIAS(es) previously defined.

INPUTS

v_mview_id -- The materialized view id (see flexviews.get_id)
v_expr_type -- GROUP|COLUMN|SUM|COUNT|MIN|MAX|AVG|COUNT_DISTINCT|PRIMARY|KEY
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 PRIMARY 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 PRIMARY and KEY indexes, 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	Experimental MIN support (uses auxilliary view)
MAX	Experimental MAX support (uses auxilliary view)
AVG	Experimental AVG support (adds SUM and COUNT expressions automatically)
COUNT_DISTINCT	Experimental COUNT(DISTINCT) support (uses auxilliary view)
PRIMARY	Adds a primary key to the view. Specify column aliases in v_expr.
KEY	Adds an index to the view. Specify column aliases in v_expr.

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

     call flexviews.add_expr(@mv_id, 'GROUP', 'an_alias.c1', 'c1');
     call flexviews.add_expr(@mv_id, 'SUM', 'an_alias.c2', 'sum_c2');
     call flexviews.add_expr(@mv_id, 'PRIMARY', 'c1', 'pk');

5. 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');

6. 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');

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

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

9. 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

10. 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

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

12. 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');

13. 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'));

14. 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!

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. 1/README [ Generics ]

2. CDC_API/create_mvlog [ Functions ]

3. CDC_API/get_mvlog [ Functions ]

4. SQL_API/add_expr [ Functions ]

5. SQL_API/add_table [ Functions ]

6. SQL_API/create [ Functions ]

7. SQL_API/disable [ Functions ]

8. SQL_API/enable [ Functions ]

9. SQL_API/remove_expr [ Functions ]

10. SQL_API/remove_table [ Functions ]

11. SQL_API/set_definition [ Functions ]

12. UTIL_API/get_id [ Functions ]

13. UTIL_API/get_sql [ Functions ]

14. UTIL_API/refresh [ Functions ]

15. UTIL_API/rename [ Functions ]