In one of my latest postings I mentioned a way to create semi materialized views in MySQL. The problem was that the solution required a stored procedure for every materialized view. That’s very annoying. So I looked for a more general and dynamic approach. The result is another stored procedure with 3 parameters:

• the name for the source table / view
• the primary key columns of the source table / view
• the desired name for the materialized view

Basically this procedure does the following:

• It drops the materialized view if it already exists
• It creates the materialized view with the structure and data of the source table
• It adds a primary key to the newly created materialized view

However the procedure does not create triggers on the table. This could be an improvement for a next version ;)
To call the procedure use:

CALL create_mview('big_table', 'id', 'view_m_big_table');

And here’s the procedure:

DROP PROCEDURE IF EXISTS create_mview;
DELIMITER //
CREATE PROCEDURE create_mview (IN source_table VARCHAR(150), IN primary_key VARCHAR(150), IN target_table  VARCHAR(150))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
MODIFIES SQL DATA
BEGIN
-- declare some variables ...
DECLARE m_view_name VARCHAR(150);
DECLARE create_m_view_sql TEXT;
DECLARE drop_m_view_sql TEXT;
DECLARE add_primary_key_sql TEXT;
--
-- create the sql queries to drop the materialized view, create the tmaterialized view
-- and add a primary key to the materialized view.
SET m_view_name:=concat('view_m_', source_table);
SET @drop_m_view_sql:=concat('DROP TABLE IF EXISTS ',target_table);
SET @create_m_view_sql:=concat('CREATE TABLE ', target_table , ' SELECT * FROM ', source_table);
SET @add_primary_key_sql:=concat('ALTER TABLE ', target_table , ' ADD PRIMARY KEY (', primary_key ,')');
--
-- drop the materialized view if it exists
PREPARE stmt1 FROM @drop_m_view_sql;
EXECUTE stmt1;
--
-- create the table
PREPARE stmt2 FROM @create_m_view_sql;
EXECUTE stmt2;
--
-- add the primary key
PREPARE stmt3 FROM @add_primary_key_sql;
EXECUTE stmt3;
--
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
DEALLOCATE PREPARE stmt3;
--
END; //
DELIMITER ;

You can download the procedure also.

Popularity: 46% [?]