delimiter $$$ drop procedure if exists paramScript$$$ create procedure paramScript() begin declare drop_tmpl varchar(255) default 'drop {type} if exists {schema}.{name} $$$'; declare create_tmpl varchar(255) default 'create function _{real_name}$(n {type}) returns int unsigned deterministic begin set @{name} = n; return 1; end $$$'; declare reset_tmpl1 varchar(255) default 'create function _R$() returns int unsigned deterministic begin set'; declare reset_tmpl2 varchar(255) default ' @{name} = null,'; declare reset_tmpl3 varchar(255) default ' @1 = null; return 1; end $$$'; drop temporary table if exists script_lines; create temporary table script_lines( id int not null primary key auto_increment, stmt varchar(1000) ) engine=MEMORY; drop temporary table if exists script_src; create temporary table script_src( column_name varchar(255), data_type varchar(255), table_names varchar(1000), column_types varchar(1000), real_type varchar(255), real_name varchar(255), primary key(column_name, data_type) ) engine=MEMORY select c.column_name, c.data_type, group_concat(c.table_name order by 1) table_names, group_concat(distinct c.column_type order by 1) column_types, if(count(distinct c.column_type) > 1, concat(c.data_type, '(', coalesce(max(c.character_maximum_length), max(c.numeric_precision)), ')'), c.column_type) real_type, if(cc.column_name is not null, concat_ws('_', c.column_name, c.data_type), c.column_name) real_name from information_schema.COLUMNS c left join information_schema.VIEWS v on c.table_catalog = v.table_catalog and c.table_schema = v.table_schema and c.table_name = v.table_name left join information_schema.COLUMNS cc on c.table_catalog = cc.table_catalog and c.table_schema = cc.table_schema and c.column_name = cc.column_name and c.data_type != cc.data_type where c.table_schema = database() and c.column_key != 'PRI' and v.table_catalog is null and not exists(select 1 from test_castor_m2.zzzTableExceptT te where c.table_name like te.tableName and c.column_name like te.columnName) group by c.column_name, c.data_type; -- starting delimiter insert script_lines(stmt) values('delimiter $$$'); -- drops insert script_lines(stmt) select replace(replace(replace(drop_tmpl, '{type}', r.routine_type), '{schema}', r.routine_schema), '{name}', r.routine_name) from information_schema.routines r where r.routine_schema = database() and r.routine_name like '\_%$'; -- reset function insert script_lines(stmt) values (reset_tmpl1); insert script_lines(stmt) select replace(reset_tmpl2, '{name}', column_name) from script_src group by column_name order by column_name; insert script_lines(stmt) values (reset_tmpl3); -- creates insert script_lines(stmt) select replace( replace( replace( create_tmpl, '{real_name}', s.real_name), '{type}', s.real_type), '{name}', s.column_name) from script_src s; -- ending delimiter insert script_lines(stmt) values('delimiter ;'); select stmt from script_lines order by id; drop temporary table script_src; drop temporary table script_lines; end$$$ delimiter ; select r.routine_schema, r.routine_type, r.routine_name from information_schema.routines r where r.routine_schema = 'test_castor_m2' and r.routine_name like '\_\_%k'; and r.routine_name like '\_%$'; select c.column_name, c.data_type, group_concat(c.table_name order by 1), group_concat(distinct c.column_type order by 1), if(count(distinct c.column_type) > 1, concat(c.data_type, '(', coalesce(max(c.character_maximum_length), max(c.numeric_precision)), ')'), c.column_type), if(cc.column_name is not null, concat_ws('_', c.column_name, c.data_type), c.column_name) real_name, c.* from information_schema.COLUMNS c left join information_schema.VIEWS v on c.table_catalog = v.table_catalog and c.table_schema = v.table_schema and c.table_name = v.table_name left join information_schema.COLUMNS cc on c.table_catalog = cc.table_catalog and c.table_schema = cc.table_schema and c.column_name = cc.column_name and c.data_type != cc.data_type where c.table_schema = 'test_castor_m2' and c.column_key != 'PRI' and v.table_catalog is null and not exists(select 1 from test_castor_m2.zzzTableExceptT te where c.table_name like te.tableName and c.column_name like te.columnName) group by c.column_name, c.data_type; select * from information_schema.COLUMNS c left join information_schema.VIEWS v on c.table_catalog = v.table_catalog and c.table_schema = v.table_schema and c.table_name = v.table_name where c.table_schema = 'test_castor_m2' and v.table_catalog is null order by c.column_name; create table zzzTableExceptT( tableName varchar(100) not null default '%', columnName varchar(100) not null default '%', primary key(tableName, columnName) ) engine=InnoDB; insert zzzTableExceptT(tableName, columnName) values ('%', 'added'), ('%', 'changed'), ('%', 'id'), ('zzz%', '%'); -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- -- ------------------------------------- truncate table paramTest; select benchmark(100000, paramInsert1(elt(floor(1 + rand() * 4), 'abc', 'def', 'ghi', 'jkl', 'mno'), round(rand() * 100), rand())); select benchmark(100000, _R$() and _valueStr$(elt(floor(1 + rand() * 4), 'abc', 'def', 'ghi', 'jkl', 'mno')) and _valueInt$(round(rand() * 100)) and _valueFloat$(rand()) and paramInsert2()); select benchmark(100000, _valueStr$(elt(floor(1 + rand() * 4), 'abc', 'def', 'ghi', 'jkl', 'mno')) and _valueInt$(round(rand() * 100)) and _valueFloat$(rand()) and paramInsert2()); select benchmark(100000, _R$()); -- ----------------------------------------------- -- scripts -- ----------------------------------------------- drop table if exists paramTest; create table paramTest( id int unsigned not null primary key auto_increment, valueStr varchar(255), valueInt int, valueFloat double, added timestamp default current_timestamp on update current_timestamp ) engine=InnoDB; delimiter $$$ drop function if exists paramInsert1 $$$ create function paramInsert1(s_ varchar(255), i_ int, f_ double) returns int unsigned not deterministic modifies sql data begin insert paramTest( valueStr, valueInt, valueFloat) values( s_, i_, f_); return last_insert_id(); end$$$ drop function if exists paramInsert2 $$$ create function paramInsert2() returns int unsigned not deterministic modifies sql data begin insert paramTest( valueStr, valueInt, valueFloat) values( @valueStr, @valueInt, @valueFloat); return last_insert_id(); end $$$ delimiter ; -- ----------------------------------------------- -- util -- ----------------------------------------------- drop table if exists zzzParamData; create table zzzParamData( ch char(1) primary key, dataType varchar(100), engineName varchar(100) ) engine=InnoDB; insert zzzParamData(ch, dataType, engineName) values -- ('S', 'varchar(255)', 'MEMORY'), -- ('T', 'text', 'InnoDB'), -- ('I', 'int', 'MEMORY'), -- ('F', 'double', 'MEMORY'), -- ('N', 'decimal(22,6)', 'MEMORY'), -- ('D', 'datetime', 'MEMORY'); ('S', 'varchar(255)', 'InnoDB'), ('T', 'text', 'InnoDB'), ('I', 'int', 'InnoDB'), ('F', 'double', 'InnoDB'), ('N', 'decimal(22,6)', 'InnoDB'), ('D', 'datetime', 'InnoDB'); delimiter $$$ drop function if exists __subsParam$$$ create function __subsParam(tmpl_ varchar(20000), ch_ char(1)) returns varchar(20000) not deterministic begin declare data_type, engine_name varchar(100); select dataType, engineName into data_type, engine_name from zzzParamData where ch = ch_; return replace(replace(replace(tmpl_, '{ch}', ch_), '{dataType}', data_type), '{engineName}', engine_name); end$$$ delimiter ; set @tmpl = concat( ' drop table if exists zzzParam{ch}; create table zzzParam{ch}( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value {dataType} null, primary key using hash (connId, name), index(ts) ) engine={engineName}; delimi', 'ter $$$ drop function if exists _{ch}$$$ create function _{ch}(name_ varchar(30), val_ {dataType}) returns int unsigned not deterministic begin insert zzzParam{ch}(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _{ch}_$$$ create function _{ch}_(name_ varchar(30), val_ {dataType}) returns int unsigned not deterministic begin if not exists(select 1 from zzzParam{ch} where connId = connection_id() and name = name_) then insert zzzParam{ch}(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists ${ch}$$$ create function ${ch}(name_ varchar(30)) returns {dataType} deterministic begin return (select value from zzzParam{ch} where connId = connection_id() and name = name_); end$$$ drop function if exists ${ch}_$$$ create function ${ch}_(name_ varchar(30), default_ {dataType}) returns {dataType} deterministic begin return coalesce(${ch}(name), default_); end$$$ delimi', 'ter ; '); set @tmpl22 = ' delete from zzzParam{ch} where connId = connection_id(); delete from zzzParam{ch} where ts < threshold_ts; '; set @tmpl21 = concat(' delimi', 'ter $$$ drop function if exists __$$$ create function __() returns int unsigned not deterministic begin declare threshold_ts timestamp default date_sub(now(), interval 1 hour); '); set @tmpl23 = concat(' return 1; end$$$ delimi', 'ter ; '); set @q1 = concat(@tmpl21, (select group_concat(__subsParam(@tmpl22, ch) separator '\n') from zzzParamData), @tmpl23); select @q1; -- call __executeSQL(@q1); set @q1 = __subsParam(@tmpl, 'S'); select @q1; -- call __executeSQL(@q1); set @q1 = __subsParam(@tmpl, 'T'); select @q1; -- call __executeSQL(@q1); set @q1 = __subsParam(@tmpl, 'I'); select @q1; -- call __executeSQL(@q1); set @q1 = __subsParam(@tmpl, 'F'); select @q1; -- call __executeSQL(@q1); set @q1 = __subsParam(@tmpl, 'N'); select @q1; -- call __executeSQL(@q1); set @q1 = __subsParam(@tmpl, 'D'); select @q1; -- call __executeSQL(@q1); -- ============================= -- generated code -- ============================= delimiter $$$ drop function if exists __$$$ create function __() returns int unsigned not deterministic begin declare threshold_ts timestamp default date_sub(now(), interval 1 hour); delete from zzzParamD where connId = connection_id(); delete from zzzParamD where ts < threshold_ts; delete from zzzParamF where connId = connection_id(); delete from zzzParamF where ts < threshold_ts; delete from zzzParamI where connId = connection_id(); delete from zzzParamI where ts < threshold_ts; delete from zzzParamN where connId = connection_id(); delete from zzzParamN where ts < threshold_ts; delete from zzzParamS where connId = connection_id(); delete from zzzParamS where ts < threshold_ts; delete from zzzParamT where connId = connection_id(); delete from zzzParamT where ts < threshold_ts; return 1; end$$$ delimiter ; -- --------------------------------------- drop table if exists zzzParamS; create table zzzParamS( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value varchar(255) null, primary key using hash (connId, name), index(ts) ) engine=MEMORY; delimiter $$$ drop function if exists _S$$$ create function _S(name_ varchar(30), val_ varchar(255)) returns int unsigned not deterministic begin insert zzzParamS(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _S_$$$ create function _S_(name_ varchar(30), val_ varchar(255)) returns int unsigned not deterministic begin if not exists(select 1 from zzzParamS where connId = connection_id() and name = name_) then insert zzzParamS(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists $S$$$ create function $S(name_ varchar(30)) returns varchar(255) deterministic begin return (select value from zzzParamS where connId = connection_id() and name = name_); end$$$ drop function if exists $S_$$$ create function $S_(name_ varchar(30), default_ varchar(255)) returns varchar(255) deterministic begin return coalesce($S(name), default_); end$$$ delimiter ; -- --------------------------------------- drop table if exists zzzParamT; create table zzzParamT( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value text null, primary key using hash (connId, name), index(ts) ) engine=InnoDB; delimiter $$$ drop function if exists _T$$$ create function _T(name_ varchar(30), val_ text) returns int unsigned not deterministic begin insert zzzParamT(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _T_$$$ create function _T_(name_ varchar(30), val_ text) returns int unsigned not deterministic begin if not exists(select 1 from zzzParamT where connId = connection_id() and name = name_) then insert zzzParamT(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists $T$$$ create function $T(name_ varchar(30)) returns text deterministic begin return (select value from zzzParamT where connId = connection_id() and name = name_); end$$$ drop function if exists $T_$$$ create function $T_(name_ varchar(30), default_ text) returns text deterministic begin return coalesce($T(name), default_); end$$$ delimiter ; -- --------------------------------------- drop table if exists zzzParamI; create table zzzParamI( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value int null, primary key using hash (connId, name), index(ts) ) engine=MEMORY; delimiter $$$ drop function if exists _I$$$ create function _I(name_ varchar(30), val_ int) returns int unsigned not deterministic begin insert zzzParamI(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _I_$$$ create function _I_(name_ varchar(30), val_ int) returns int unsigned not deterministic begin if not exists(select 1 from zzzParamI where connId = connection_id() and name = name_) then insert zzzParamI(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists $I$$$ create function $I(name_ varchar(30)) returns int deterministic begin return (select value from zzzParamI where connId = connection_id() and name = name_); end$$$ drop function if exists $I_$$$ create function $I_(name_ varchar(30), default_ int) returns int deterministic begin return coalesce($I(name), default_); end$$$ delimiter ; -- --------------------------------------- drop table if exists zzzParamF; create table zzzParamF( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value double null, primary key using hash (connId, name), index(ts) ) engine=MEMORY; delimiter $$$ drop function if exists _F$$$ create function _F(name_ varchar(30), val_ double) returns int unsigned not deterministic begin insert zzzParamF(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _F_$$$ create function _F_(name_ varchar(30), val_ double) returns int unsigned not deterministic begin if not exists(select 1 from zzzParamF where connId = connection_id() and name = name_) then insert zzzParamF(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists $F$$$ create function $F(name_ varchar(30)) returns double deterministic begin return (select value from zzzParamF where connId = connection_id() and name = name_); end$$$ drop function if exists $F_$$$ create function $F_(name_ varchar(30), default_ double) returns double deterministic begin return coalesce($F(name), default_); end$$$ delimiter ; -- --------------------------------------- drop table if exists zzzParamN; create table zzzParamN( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value decimal(22,6) null, primary key using hash (connId, name), index(ts) ) engine=MEMORY; delimiter $$$ drop function if exists _N$$$ create function _N(name_ varchar(30), val_ decimal(22,6)) returns int unsigned not deterministic begin insert zzzParamN(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _N_$$$ create function _N_(name_ varchar(30), val_ decimal(22,6)) returns int unsigned not deterministic begin if not exists(select 1 from zzzParamN where connId = connection_id() and name = name_) then insert zzzParamN(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists $N$$$ create function $N(name_ varchar(30)) returns decimal(22,6) deterministic begin return (select value from zzzParamN where connId = connection_id() and name = name_); end$$$ drop function if exists $N_$$$ create function $N_(name_ varchar(30), default_ decimal(22,6)) returns decimal(22,6) deterministic begin return coalesce($N(name), default_); end$$$ delimiter ; -- --------------------------------------- drop table if exists zzzParamD; create table zzzParamD( connId int unsigned not null, name varchar(30) not null, ts timestamp not null default current_timestamp on update current_timestamp, value datetime null, primary key using hash (connId, name), index(ts) ) engine=MEMORY; delimiter $$$ drop function if exists _D$$$ create function _D(name_ varchar(30), val_ datetime) returns int unsigned not deterministic begin insert zzzParamD(connId, name, value) values (connection_id(), name_, val_) on duplicate key update value = val_; return 1; end$$$ drop function if exists _D_$$$ create function _D_(name_ varchar(30), val_ datetime) returns int unsigned not deterministic begin if not exists(select 1 from zzzParamD where connId = connection_id() and name = name_) then insert zzzParamD(connId, name, value) values (connection_id(), name_, val_); end if; return 1; end$$$ drop function if exists $D$$$ create function $D(name_ varchar(30)) returns datetime deterministic begin return (select value from zzzParamD where connId = connection_id() and name = name_); end$$$ drop function if exists $D_$$$ create function $D_(name_ varchar(30), default_ datetime) returns datetime deterministic begin return coalesce($D(name), default_); end$$$ delimiter ; -- ---------------------------------------