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