MySQL meets NoSQL with JSON UDF

I recently got back from FOSDEM, in Brussels, Belgium. While I was there I got to see a great talk by Sveta Smirnova, about her MySQL 5.7 Labs release JSON UDF functions. It is important to note that while the UDF come in a 5.7 release it is absolutely possible to compile and use the UDF with earlier versions of MySQL because the UDF interface has not changed for a long time. However, the UDF should still be considered alpha/preview level of quality and should not be used in production yet! For this example I am using Percona Server 5.6 with the UDF.

That being said, the proof-of-concept that I’m about to present here uses only one JSON function (JSON_EXTRACT) and it has worked well enough in my testing to present my idea here. The JSON functions will probably be GA sometime soon anyway, and this is a useful test of the JSON_EXTRACT function. …

You can get Flexviews at the project page:
Flexviews project page

This release includes the following new features:

Table change logs now include a new column: fv$gsn. This column carries the "global sequence number" which uniquely identifies the order in which to make changes in the database. This was required to fix some problems identified in updating views that did not contain aggregate functions.

Updated refresh algorithm takes GSN into account and applies changes in GSN order.

All reported bugs have been fixed.

FlexCDC now supports all MySQL data types properly.

You can get Flexviews at the project page:
Flexviews project page

This release includes the following new features:

Table change logs now include a new column: fv$gsn. This column carries the "global sequence number" which uniquely identifies the order in which to make changes in the database. This was required to fix some problems identified in updating views that did not contain aggregate functions.

Updated refresh algorithm takes GSN into account and applies changes in GSN order.

All reported bugs have been fixed.

FlexCDC now supports all MySQL data types properly.

I am happy to announce the GA release of Flexviews. I've numbered the release 1.7.0 GA.
There are a small number of bug fixes and enhancements:

• 
  
• Fixes for views which have aggregate functions but no GROUP BY expressions could have had problems during incremental update.
  
• GA support for all aggregate functions except GROUP_CONCAT and AVG(distinct).
  
• The PERCENTILE_XX (ie PERCENTILE_90 for a 90th percentile calculation) is now stable too
  

FlexCDC got a number of fixes and improvements:

• 
  
• TIMESTAMP columns are now properly supported
  
• Now mysqlbinlog errors are detected, and the program exits gracefully
  
• FlexCDC now logs to a log file instead of writing to STDOUT/STDERR
  
• There are new PHP scripts for adding and removing table changelogs …

I am happy to announce the GA release of Flexviews. I've numbered the release 1.7.0 GA.
There are a small number of bug fixes and enhancements:

• 
  
• Fixes for views which have aggregate functions but no GROUP BY expressions could have had problems during incremental update.
  
• GA support for all aggregate functions except GROUP_CONCAT and AVG(distinct).
  
• The PERCENTILE_XX (ie PERCENTILE_90 for a 90th percentile calculation) is now stable too
  

FlexCDC got a number of fixes and improvements:

• 
  
• TIMESTAMP columns are now properly supported
  
• Now mysqlbinlog errors are detected, and the program exits gracefully
  
• FlexCDC now logs to a log file instead of writing to STDOUT/STDERR
  
• There are new PHP scripts for adding and removing table changelogs …

If you know me, then you probably have heard of Flexviews. If not, then it might not be familiar to you. I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog about it before then. For those unfamiliar, Flexviews enables you to create and maintain incrementally refreshable materialized views.

You might be asking yourself “what is an incrementally refreshable materialized view?”. If so, then keep reading. This is the first in a multi-part series describing Flexviews.

edit:
You can find part 2 of the series here: http://www.mysqlperformanceblog.com/2011/03/25/using-flexviews-part-two-change-data-capture/

The output of …

Available immediately Flexviews 1.6.0-RC2.

This release is a bugfix release.  This is the second Flexviews release candidate.  If no major bugs are discovered the next release will be the GA release.

Flexviews is a stored procedure managed materialized view system for MySQL 5.1 or greater. 

What is fixed in Flexviews 1.6.0-RC2?

• Numerous performance fixes. 
  • Flexviews uses fewer temporary tables and subqueries
  • A full table scan of the view is no longer required (only changed GB keys are scanned)
  • Dead code has been removed
• Bug fixes
  • Removing tables and adding them to a view again could result in the WHERE clause being generated in the wrong order
  • Fix a problem with applying deltas to views which use PERCENTILE …

Whats new in Flexviews 1.6.0RC1

• This is the first release candidate before the final release.  If no major bugs are uncovered, then the next release will be the first GA release. 
• Flexviews now has a test suite for all major features.  The creation of these tests uncovered a number of issues which have been resolved in this release. 
• All MySQL aggregate functions except GROUP_CONCAT are now supported. 
• A special aggregate function called PERCENTILE is now also supported.  The calculation uses a modified version of the GROUP_CONCAT based solution suggested by Roland Bouman for percentiles.  This function should be considered experimental.  Please report bugs if you find any.
• You can add indexes to enabled materialized views using SQL_API/add_expr
• Adding PRIMARY KEY indexes is no longer …

Flexviews allows you to cache (materialize) SQL statements and then quickly apply only the changes to underlying tables to update the cache. This can phenomenally increase the performance of your application and allow you to more easily maintain summary tables automatically.

I'll be demonstrating the usage of Flexviews and how to create 'fast refresh' materialized views.

I'll be showing off:
FlexCDC - Flexible change data capture for MySQL
Flexviews - Uses FlexCDC and stored procedures to incrementally maintain mateialized views, including those with inner JOINS and aggregation.

Often times I find myself having to handle a particular portion of a SQL statement via a script. I've written a lot of specialized regular expressions over time to handle these tasks, but the one that I've had to write the most is a basic 'tokenizer' which understands the quoting semantics of a MySQL statement. That is, it understands `BackTick`, 'Single Quote' and "Double Quoted" strings.

#PHP Heredoc syntax
$regex = <<< END_OF_REGEX
/
  [^ \"'`(),]*\([^)]*\)    #match functions like concat(x,"y",`a`.`z`) or sum(`xyz`);
  |\([^)]*?\)              #match grouped items
  |"[^"]*?"                #match double quoted items
  |'[^']*?'                #match single quoted items
  |`[A-Za-z0-9_ .'\"()+\\-&^%\$+?%\\/\\\\!`]+`  #match backtick mysql names
  |[^ ,]+                  #match keywords, operators and aliases
  |,
/xi
END_OF_REGEX;

EDIT: After some comments and further testing, I've …