PlanetMysql.ru — информация о СУБД MySQL

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
  • Improved error messages
• FlexCDC Bug fixes
  • Bulk insert mode did not work when a transaction changed rows in more than one table

Please note that I am not currently making separate FlexCDC releases.  If you are only interested in FlexCDC, you will find it in the consumer/ subdirectory of the Flexviews tarball.  I will package FlexCDC up as a standalone release along with the GA release of Flexviews.
PlanetMySQL Voting: Vote UP / Vote DOWN

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 supported.  All views get an auto_incrementing primary  key.  You can add additional UNIQUE indexes instead.
• There is an upgrade process from 1.5.3b (see UPGRADE and upgrade.sql)

Significant bug fixes

• Views with aggregate functions but no GROUP BY columns now work properly for all supported aggregate function types
• NULL values in GROUP BY columns are now properly supported
• NULL values now work properly with distributive aggregate functions
• There is a wrapper script around run_consumer.php which can restart the consumer if it stops running

As always, get it at:
http://sourceforge.net/projects/Flexviews
PlanetMySQL Voting: Vote UP / Vote DOWN

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.
PlanetMySQL Voting: Vote UP / Vote DOWN

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;

When used with the preg_match_all() function, an array is produced which represents the tokenized string. Functions calls, quoted strings and grouped expressions are returned together, and may need additional processing, depending on your needs.

For example:

-- select sum(a * b) sweety,'abc' as a1, 
--        "abc",concat(`def`.`abc`,'hello', x.y, z) as `blah`,
--        null + 1 
--   from `abc`.`def` as def1 
--   join xyz.zzz z1 
--     on (z1.a = def1.a) 

Array
(
    [0] => select
    [1] => sum(a * b)
    [2] => sweety
    [3] => ,
    [4] => 'abc'
    [5] => as
    [6] => a1
    [7] => ,
    [8] => "abc"
    [9] => ,
    [10] => concat(`def`.`abc`,'hello', x.y, z)
    [11] => as
    [12] => `blah`
    [13] => ,
    [14] => null
    [15] => +
    [16] => 1
    [17] => from
    [18] => `abc`.`def`
    [19] => as
    [20] => def1
    [21] => join
    [22] => xyz.zzz
    [23] => z1
    [24] => on
    [25] => (z1.a = def1.a)
)

I'm using this to process SQL statements and automatically create the Flexviews calls for the query.
The following output is generated by my program for the above SQL statement (only the SELECT clause is currently represented):

call flexviews.add_expr(@mvid, 'SUM', 'a * b', 'sweety');
call flexviews.add_expr(@mvid, 'GROUP', 'abc', '1');
call flexviews.add_expr(@mvid, 'GROUP', "abc", '_abc_');
call flexviews.add_expr(@mvid, 'GROUP', 'concat(`def`.`abc`,\'hello\', x.y, z)', 'blah');
call flexviews.add_expr(@mvid, 'GROUP', 'null + 1', 'null_+_1');

PlanetMySQL Voting: Vote UP / Vote DOWN