{"id":290537,"date":"2018-10-09T16:40:02","date_gmt":"2018-10-09T12:40:02","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=290537"},"modified":"-0001-11-30T00:00:00","modified_gmt":"2018-10-09T12:40:02","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=290537","title":{"rendered":"\u041a\u0430\u043a \u043d\u0430\u0443\u0447\u0438\u0442\u044c MySQL \u0437\u0430\u0433\u043b\u044f\u0434\u044b\u0432\u0430\u0442\u044c \u0432 \u043f\u0440\u043e\u0448\u043b\u043e\u0435"},"content":{"rendered":"\n
\"\u041a\u0430\u043a
\u0412 \u0441\u0442\u0430\u0442\u044c\u0435 \u0440\u0435\u0447\u044c \u043f\u043e\u0439\u0434\u0451\u0442 \u043e \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 \u0432 MySQL<\/b>. \u0425\u043e\u0447\u0443 \u043f\u043e\u043a\u0430\u0437\u0430\u0442\u044c \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044e \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043d\u0430 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430\u0445 \u0438 \u0442\u043e, \u043a\u0430\u043a\u0438\u0435 \u0443\u0434\u0438\u0432\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0432\u0435\u0449\u0438 \u043c\u043e\u0436\u043d\u043e \u0431\u0443\u0434\u0435\u0442 \u0441 \u044d\u0442\u0438\u043c \u0434\u0435\u043b\u0430\u0442\u044c.
\u041f\u043e\u0447\u0435\u043c\u0443 \u043d\u0430 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430\u0445? \u041f\u043e\u0442\u043e\u043c\u0443 \u0447\u0442\u043e \u043d\u0435\u0442 \u0434\u043e\u0441\u0442\u0443\u043f\u0430 \u043a \u0431\u0438\u043d\u0430\u0440\u043d\u043e\u043c\u0443 \u043b\u043e\u0433\u0443. \u0420\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u0441 \u0431\u0438\u043d\u0430\u0440\u043d\u044b\u043c \u043b\u043e\u0433\u043e\u043c \u043f\u043e\u0442\u0435\u043d\u0446\u0438\u0430\u043b\u044c\u043d\u043e \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u0430\u044f, \u0445\u043e\u0442\u044f \u0438 \u0431\u043e\u043b\u0435\u0435 \u0441\u043b\u043e\u0436\u043d\u0430\u044f \u0432 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0435, \u0442.\u043a. \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043f\u0430\u0440\u0441\u0438\u0442\u044c \u043b\u043e\u0433.
\u0421\u0440\u0430\u0437\u0443 \u0445\u043e\u0447\u0443 \u043f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0434\u0438\u0442\u044c, \u0447\u0442\u043e \u0434\u0430\u043d\u043d\u044b\u0439 \u043c\u0435\u0442\u043e\u0434 \u0441\u043e\u0437\u0434\u0430\u0441\u0442 \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u0443\u044e \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0443 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440. \u0418 \u0435\u0441\u043b\u0438 \u0443 \u0412\u0430\u0441 \u0430\u043a\u0442\u0438\u0432\u043d\u043e \u0438\u0437\u043c\u0435\u043d\u044f\u044e\u0449\u0438\u0435\u0441\u044f \u0434\u0430\u043d\u043d\u044b\u0435, \u0442\u043e \u0434\u0430\u043d\u043d\u043e\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u043c\u043e\u0436\u0435\u0442 \u043d\u0435 \u043f\u043e\u0434\u043e\u0439\u0442\u0438 \u0412\u0430\u043c \u0438\u043b\u0438 \u0431\u0443\u0434\u0435\u0442 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u0442\u044c \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043a\u043e\u0440\u0440\u0435\u043a\u0442\u0438\u0440\u043e\u0432\u043e\u043a \u0438 \u0434\u043e\u0440\u0430\u0431\u043e\u0442\u043e\u043a.
\u0412 \u0446\u0435\u043b\u043e\u043c \u0436\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0437\u0430\u043a\u043e\u043d\u0447\u0435\u043d\u043d\u044b\u043c \u0438 \u043a\u043e\u043c\u043f\u043b\u0435\u043a\u0441\u043d\u044b\u043c. \u041c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u0432\u043d\u0435\u0434\u0440\u0435\u043d\u043e \u00ab\u043a\u0430\u043a \u0435\u0441\u0442\u044c\u00bb \u0438 \u043f\u0440\u0435\u043a\u0440\u0430\u0441\u043d\u043e \u0441\u043f\u0440\u0430\u0432\u043b\u044f\u0442\u044c\u0441\u044f \u0441\u043e \u0441\u0432\u043e\u0435\u0439 \u0437\u0430\u0434\u0430\u0447\u0435\u0439.
<\/a>
\u0412\u0441\u0451, \u0447\u0442\u043e \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u043e \u043d\u0438\u0436\u0435, \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u043e \u043d\u0430 \u0432\u0435\u0440\u0441\u0438\u0438 MariaDB 10.0.32<\/b>
\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u044e\u0442\u0441\u044f \u0441\u0442\u043e\u043b\u0431\u0446\u044b \u0441 \u0442\u0438\u043f\u0430\u043c\u0438: \u0447\u0438\u0441\u043b\u0430, \u0441\u0442\u0440\u043e\u043a\u0438, \u0434\u0430\u0442\u044b. \u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u043c\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0434\u043e\u043b\u0436\u043d\u0430 \u0438\u043c\u0435\u0442\u044c \u0443\u043d\u0438\u043a\u0430\u043b\u044c\u043d\u043e\u0435 NOT NULL<\/b> \u0447\u0438\u0441\u043b\u043e\u0432\u043e\u0435 \u043f\u043e\u043b\u0435 id<\/b>.<\/p>\n

\u0414\u043b\u044f \u043d\u0430\u0447\u0430\u043b\u0430 \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0441 \u043a\u043e\u043d\u0444\u0438\u0433\u043e\u043c \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f: <\/p>\n

DROP TABLE IF EXISTS protocol_config; CREATE TABLE protocol_config ( id int(11) NOT NULL PRIMARY KEY auto_increment , command VARCHAR(50) NOT NULL -- \u041a\u043e\u043c\u0430\u043d\u0434\u0430 , table_name VARCHAR(50) -- \u0418\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b , column_name VARCHAR(50) -- \u0418\u043c\u044f \u043a\u043e\u043b\u043e\u043d\u043a\u0438 , denormalize_column VARCHAR(50) -- \u0414\u0435\u043d\u043e\u0440\u043c\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u043a\u043e\u043b\u043e\u043d\u043a\u0430 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 protocol , UNIQUE (command, table_name, column_name, denormalize_column) ) DEFAULT CHARSET=utf8 COMMENT='\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f'; <\/code><\/pre>\n
  \u0412\u0441\u0435 \u043e\u043f\u0446\u0438\u0438 \u043f\u0440\u0438\u043c\u0435\u043d\u044f\u044e\u0442\u0441\u044f \u0432\u043e \u0432\u0440\u0435\u043c\u044f \u0433\u0435\u043d\u0435\u0440\u0430\u0446\u0438\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430 \u043d\u0430 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435. \u0422.\u0435. \u043f\u0440\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043a \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043f\u0435\u0440\u0435\u0433\u0435\u043d\u0435\u0440\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u044b.<\/p>\n
  \u041f\u043e\u043b\u0435 command<\/b> \u2014 \u043e\u043f\u0446\u0438\u044f \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0438 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430:  <\/p>\n
    \n
  1. disable_protocol<\/b> \u2014 \u0432\u044b\u043a\u043b\u044e\u0447\u0430\u0435\u0442 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435.<\/li>\n
  2. exclude_table<\/b> \u2014 \u0443\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u043a\u043e\u0442\u043e\u0440\u0443\u044e \u043d\u0443\u0436\u043d\u043e \u0438\u0441\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0438\u0437 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f. \u041f\u043e \u0443\u043c\u043e\u043b\u0447\u0430\u043d\u0438\u044e \u0432 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0438 \u0443\u0447\u0430\u0441\u0442\u0432\u0443\u044e\u0442 \u0432\u0441\u0435 BASE TABLE ENGINE=InnoDB<\/b>.
      \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440,
      exclude_table protocol
      exclude_table protocol_pos<\/b><\/li>\n
  3. exclude_column<\/b> \u2014 \u0443\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u043f\u043e\u043b\u0435 \u043a\u043e\u0442\u043e\u0440\u043e\u0435 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0438\u0441\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0438\u0437 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f. \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0434\u0435\u043d\u043e\u0440\u043c\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u043d\u043e\u0435 \u043f\u043e\u043b\u0435, \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u043c\u043e\u0435 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430\u043c\u0438.
      \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440,
      exclude_column docs sum<\/b><\/li>\n
  4. denormalize_column<\/b> \u2014 \u0443\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0434\u0435\u043d\u043e\u0440\u043c\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u0442\u044c \u0432 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b (\u0442\u0430\u0431\u043b\u0438\u0446\u0430 protocol<\/b>). \u041f\u043e \u0443\u043c\u043e\u043b\u0447\u0430\u043d\u0438\u044e \u0432\u0441\u0435 \u043f\u043e\u043b\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u044e\u0442\u0441\u044f \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 protocol_pos<\/b>.
      \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440,
      denormalize_column docs id doc_id<\/b>
      \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446\u044b docs<\/b> \u0431\u0443\u0434\u0435\u0442 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u0442\u044c\u0441\u044f \u043f\u043e\u043b\u0435 id<\/b> \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 protocol<\/b> \u0432 \u043a\u043e\u043b\u043e\u043d\u043a\u0443 doc_id<\/b>. \u041f\u043e\u043b\u0435 doc_id<\/b> \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 protocol<\/b> \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0440\u0443\u043a\u0430\u043c\u0438.
      denormalize_column doc_pos doc_id doc_id<\/b>
      \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446\u044b doc_pos<\/b> \u0431\u0443\u0434\u0435\u0442 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u0442\u044c\u0441\u044f \u043f\u043e\u043b\u0435 doc_id<\/b> \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 protocol<\/b> \u0432 \u043a\u043e\u043b\u043e\u043d\u043a\u0443 doc_id<\/b>.<\/li>\n<\/ol>\n
      \u0422\u0430\u0431\u043b\u0438\u0446\u0430 protocol:  <\/p>\n
    DROP TABLE IF EXISTS protocol_pos; DROP TABLE IF EXISTS protocol; CREATE TABLE protocol ( id BIGINT NOT NULL PRIMARY KEY auto_increment , date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- \u0414\u0430\u0442\u0430 \u0432\u0440\u0435\u043c\u044f \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0438 , oper VARCHAR(1) NOT NULL -- \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u044f I, U, D , table_name VARCHAR(50) NOT NULL -- \u0418\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 , table_id BIGINT NOT NULL -- \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u043e\u043b\u044f id \u0432 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u043c\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 , username VARCHAR(50) NOT NULL -- \u0438\u043c\u044f \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f \u0438\u043b\u0438 \u0411\u0414 , ip varchar(45) -- IP \u0430\u0434\u0440\u0435\u0441 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f , user_agent varchar(256) -- \u0411\u0440\u0430\u0443\u0437\u0435\u0440 , KEY (table_name, date) ) DEFAULT CHARSET=utf8 COMMENT='\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439'; <\/code><\/pre>\n
      \u0422\u0430\u0431\u043b\u0438\u0446\u0430 protocol_pos:  <\/p>\n
    DROP TABLE IF EXISTS protocol_pos; CREATE TABLE protocol_pos ( prot_id BIGINT NOT NULL -- \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 protocol.id , column_name VARCHAR(50) NOT NULL -- \u0438\u043c\u044f \u0441\u0442\u043e\u043b\u0431\u0446\u0430 \u0438\u0437 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u043c\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b , old_val VARCHAR(2000) -- \u0441\u0442\u0430\u0440\u043e\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430 , new_val VARCHAR(2000) -- \u043d\u043e\u0432\u043e\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430 , PRIMARY KEY (prot_id, column_name) , FOREIGN KEY (prot_id) REFERENCES protocol(id) ) DEFAULT CHARSET=utf8 COMMENT='\u041f\u043e\u043b\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439'; <\/code><\/pre>\n
      \u0412 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 protocol<\/b> \u043c\u044b \u0444\u0438\u043a\u0441\u0438\u0440\u0443\u0435\u043c \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u044e, \u0430 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 protocol_pos<\/b> \u0437\u0430\u043d\u043e\u0441\u0438\u043c \u0438\u0437\u043c\u0435\u043d\u0438\u0432\u0448\u0438\u0435\u0441\u044f \u043f\u043e\u043b\u044f.
      \u0421\u0435\u0439\u0447\u0430\u0441 \u0432\u043e\u0437\u044c\u043c\u0451\u043c \u0437\u0430 \u043e\u0441\u043d\u043e\u0432\u0443 \u0433\u0435\u043d\u0435\u0440\u0430\u0442\u043e\u0440 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0438\u0437 \u043c\u043e\u0435\u0439 \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0438     \u00ab\u0420\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u0431\u0438\u0437\u043d\u0435\u0441-\u043b\u043e\u0433\u0438\u043a\u0438 \u0432 MySQL\u00bb<\/a><\/b> \u0438 \u043d\u0430 \u0435\u0433\u043e \u043e\u0441\u043d\u043e\u0432\u0435 \u043d\u0430\u043f\u0438\u0448\u0435\u043c \u0433\u0435\u043d\u0435\u0440\u0430\u0442\u043e\u0440 \u0434\u043b\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f.<\/p>\n
      \u0424\u0443\u043d\u043a\u0446\u0438\u044f gen_bl_trigger<\/b> \u0433\u0435\u043d\u0435\u0440\u0430\u0446\u0438\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430 \u0431\u0438\u0437\u043d\u0435\u0441-\u043b\u043e\u0433\u0438\u043a\u0438 \u0441\u043c\u043e\u0442\u0440\u0438\u0442 \u043d\u0430 \u043d\u0430\u043b\u0438\u0447\u0438\u0435 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b <\u0438\u043c\u044f_\u0442\u0430\u0431\u043b\u0438\u0446\u044b>_trg_proc<\/b>  <\/p>\n
    gen_bl_trigger<\/b><\/p>\n
    \n
    DELIMITER $ DROP FUNCTION IF EXISTS gen_bl_trigger$ CREATE FUNCTION gen_bl_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN     DECLARE text TEXT;     DECLARE f_proc INT;     SET group_concat_max_len = 9000000;     SET f_proc := (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES         WHERE ROUTINE_NAME = CONCAT(table_name, '_trg_proc')          AND ROUTINE_TYPE = 'PROCEDURE'          AND ROUTINE_SCHEMA = DATABASE()     );     IF IFNULL(f_proc, 0) = 0 THEN         RETURN '';     END IF;     SET text := CONCAT('\\nbl_proc: BEGIN IF @disable_', table_name, '_bl_trg = 1 OR @disable_all_bl_trg = 1 THEN LEAVE bl_proc; END IF;');     IF trigger_time = 'BEFORE' THEN         -- \u0421\u043e\u0437\u0434\u0430\u0451\u043c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443         SET text := CONCAT(text, '\\nCREATE TEMPORARY TABLE ');         -- \u0412\u0440\u0435\u043c\u0435\u043d\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0443\u0436\u0435 \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u0441\u043e\u0437\u0434\u0430\u043d\u0430 \u043a\u043e\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u0435\u0439 INSERT INTO ... ON DUPLICATE KEY UPDATE \u043f\u043e\u044d\u0442\u043e\u043c\u0443 \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c IF NOT EXISTS         -- \u0434\u043b\u044f INSERT IGNORE \u043d\u0435 \u0441\u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442 AFTER TRIGGER, \u043f\u043e\u044d\u0442\u043e\u043c\u0443 \u0442\u043e\u0436\u0435 \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c         IF trigger_type IN ('INSERT', 'UPDATE') THEN             SET text := CONCAT(text, 'IF NOT EXISTS ');         END IF;         SET text := CONCAT(text, table_name, '_tmp_trg (');         SET text := CONCAT(text, '\\ntime VARCHAR(1)');         SET text := CONCAT(text, '\\n, type VARCHAR(1)');         SET text := CONCAT(text, '\\n, col_changed VARCHAR(1000)');         SET text := CONCAT(text, (SELECT GROUP_CONCAT('\\n, new_', COLUMN_NAME, ' ', COLUMN_TYPE             , '\\n, old_', COLUMN_NAME, ' ', COLUMN_TYPE SEPARATOR '') text             FROM INFORMATION_SCHEMA.COLUMNS C             WHERE C.TABLE_NAME = table_name             AND C.TABLE_SCHEMA = DATABASE()             AND C.COLUMN_TYPE != 'text'             ));         SET text := CONCAT(text, ') ENGINE=MEMORY;');         -- \u0421\u043e\u0437\u0434\u0430\u0451\u043c \u043f\u0435\u0440\u0435\u043c\u0435\u043d\u043d\u044b\u0435         SET text := CONCAT(text, (SELECT GROUP_CONCAT('\\nSET @new_', COLUMN_NAME, ' := '             , IF(trigger_type = 'DELETE', 'NULL', CONCAT('NEW.', COLUMN_NAME)), ';'             , '\\nSET @old_', COLUMN_NAME, ' := '             , IF(trigger_type = 'INSERT', 'NULL', CONCAT('OLD.', COLUMN_NAME)), ';'             SEPARATOR '') text             FROM INFORMATION_SCHEMA.COLUMNS C             WHERE C.TABLE_NAME = table_name             AND C.TABLE_SCHEMA = DATABASE()             AND C.COLUMN_TYPE != 'text'             ));     END IF;     SET text := CONCAT(text, '\\nINSERT INTO ', table_name, '_tmp_trg VALUES (\"', SUBSTR(trigger_time, 1, 1), '\", \"', SUBSTR(trigger_type, 1, 1), '\", ');     -- \u0437\u0430\u043f\u043e\u043b\u043d\u0438\u043c col_changed \u0434\u043b\u044f UPDATE     IF trigger_type = 'UPDATE' THEN         SET text := CONCAT(text, 'CONCAT('             , (SELECT GROUP_CONCAT(CONCAT('IF(IFNULL(NEW.'                    , COLUMN_NAME, ', \"-\u042a\u044a\u042a\") != IFNULL(OLD.', COLUMN_NAME, ', \"-\u042a\u044a\u042a\"), \"|', COLUMN_NAME, '|\", \"\")'                ) SEPARATOR ', ') text                FROM INFORMATION_SCHEMA.COLUMNS C                WHERE C.TABLE_NAME = table_name                AND C.TABLE_SCHEMA = DATABASE()                AND C.COLUMN_TYPE != 'text'               )             , '), ');     ELSE         SET text := CONCAT(text, 'NULL, ');     END IF;     -- \u043e\u0441\u0442\u0430\u043b\u044c\u043d\u044b\u0435 \u043f\u043e\u043b\u044f     SET text := CONCAT(text, (SELECT GROUP_CONCAT(         CASE             WHEN trigger_time = 'BEFORE' THEN CONCAT('@new_', COLUMN_NAME)             WHEN trigger_type = 'DELETE' THEN 'NULL'             ELSE CONCAT('NEW.', COLUMN_NAME)         END         , ', '         , CASE             WHEN trigger_time = 'BEFORE' THEN CONCAT('@old_', COLUMN_NAME)             WHEN trigger_type = 'INSERT' THEN 'NULL'             ELSE CONCAT('OLD.', COLUMN_NAME)         END         SEPARATOR ', ') text         FROM INFORMATION_SCHEMA.COLUMNS C         WHERE C.TABLE_NAME = table_name         AND C.TABLE_SCHEMA = DATABASE()         AND C.COLUMN_TYPE != 'text'         ));     SET text := CONCAT(text, ');');     SET text := CONCAT(text, '\\nCALL ', table_name, '_trg_proc;');     IF trigger_time = 'BEFORE' THEN         SET text := CONCAT(text         , IF(trigger_type = 'DELETE'           , ''           , (SELECT CONCAT('\\nSELECT '              , GROUP_CONCAT('new_', COLUMN_NAME SEPARATOR ', ')              , '\\nINTO ', GROUP_CONCAT('@new_', COLUMN_NAME SEPARATOR ', ')              , '\\nFROM ', table_name, '_tmp_trg;'              , GROUP_CONCAT('\\nSET NEW.', COLUMN_NAME, ' := @new_', COLUMN_NAME, ';' SEPARATOR '')              ) text              FROM INFORMATION_SCHEMA.COLUMNS C              WHERE C.TABLE_NAME = table_name               AND C.TABLE_SCHEMA = DATABASE()               AND C.COLUMN_TYPE != 'text'             )           )         );         SET text := CONCAT(text, '\\nDELETE FROM ', table_name, '_tmp_trg;');     ELSE         SET text := CONCAT(text, '\\nDROP TEMPORARY TABLE ', table_name, '_tmp_trg;');     END IF;     SET text := CONCAT(text, '\\nEND;');     RETURN text; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u0424\u0443\u043d\u043a\u0446\u0438\u044f gen_prot_trigger<\/b> \u0433\u0435\u043d\u0435\u0440\u0430\u0446\u0438\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f:  <\/p>\n
    gen_prot_trigger<\/b><\/p>\n
    \n
    DELIMITER $ DROP FUNCTION IF EXISTS gen_prot_trigger$ CREATE FUNCTION gen_prot_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN     DECLARE text TEXT;     DECLARE denormalize_columns TEXT;     DECLARE denormalize_values TEXT;     DECLARE f_exclude_table INT;     SET group_concat_max_len = 9000000;     -- \u0438\u0441\u043a\u043b\u044e\u0447\u0438\u043c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0435\u0441\u043b\u0438 \u0435\u0451 \u043d\u0435 \u043d\u0430\u0434\u043e \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u0442\u044c, \u0432 \u043d\u0435\u0439 \u043d\u0435\u0442 \u043f\u043e\u043b\u044f id \u0438\u043b\u0438 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432\u044b\u043a\u043b\u044e\u0447\u0435\u043d\u043e     SET f_exclude_table := (         SELECT CASE WHEN pd.id IS NOT NULL THEN 1           WHEN pc.id IS NOT NULL THEN 1           WHEN C.COLUMN_NAME IS NULL THEN 1          END         FROM (SELECT NULL FROM dual) d         LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'         LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = table_name         LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = DATABASE()          AND C.TABLE_NAME = table_name          AND C.COLUMN_NAME = 'id'     );     IF trigger_time = 'BEFORE' OR f_exclude_table = 1 OR table_name IN ('protocol', 'protocol_pos') THEN         RETURN '';     END IF;     SET text := CONCAT('\\nprot_proc: BEGIN DECLARE prot_id INT; IF @disable_', table_name, '_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF;');     -- \u041f\u0440\u043e\u0432\u0435\u0440\u044f\u0435\u043c \u0447\u0442\u043e \u0438\u0437\u043c\u0435\u043d\u0438\u043b\u043e\u0441\u044c \u0445\u043e\u0442\u044c 1 \u043f\u043e\u043b\u0435, \u0438\u043d\u0430\u0447\u0435 \u0432\u044b\u0445\u043e\u0434\u0438\u043c \u0438\u0437 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f     IF trigger_type = 'UPDATE' THEN         SET text := CONCAT(text         , '\\nIF '         , (SELECT GROUP_CONCAT('IFNULL(NEW.'             , C.COLUMN_NAME, ', \"-\u042a\u044a\u042a\") = IFNULL(OLD.', C.COLUMN_NAME, ', \"-\u042a\u044a\u042a\")'             SEPARATOR ' AND '             ) text             FROM INFORMATION_SCHEMA.COLUMNS C             LEFT JOIN protocol_config ec ON ec.command = 'exclude_column'              AND ec.table_name = C.TABLE_NAME              AND ec.column_name = C.COLUMN_NAME             WHERE C.TABLE_NAME = table_name              AND C.TABLE_SCHEMA = DATABASE()              AND C.COLUMN_TYPE != 'text'              AND ec.id IS NULL)         , ' THEN LEAVE prot_proc; END IF;'         );     END IF;     -- \u0434\u0435\u043d\u043e\u0440\u043c\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u043b\u044f \u0434\u043b\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b protocol     SELECT IFNULL(GROUP_CONCAT(', ', dc.denormalize_column ORDER BY dc.id SEPARATOR ''), '') denormalize_columns     , IFNULL(GROUP_CONCAT(', '         , CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END         , dc.column_name         ORDER BY dc.id SEPARATOR ', '       )       , '') denormalize_values     INTO denormalize_columns, denormalize_values     FROM INFORMATION_SCHEMA.COLUMNS C     INNER JOIN protocol_config dc ON dc.command = 'denormalize_column'      AND dc.table_name = C.TABLE_NAME      AND dc.column_name = C.COLUMN_NAME     WHERE C.TABLE_NAME = table_name      AND C.TABLE_SCHEMA = DATABASE()     ;     -- \u0412\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u043c \u0441\u0442\u0440\u043e\u0447\u043a\u0443 \u0432 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b     SET text := CONCAT(text, '\\nINSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent' , denormalize_columns, ') SELECT IFNULL(u.email, USER()) username, \"', SUBSTR(trigger_type, 1, 1), '\", \"', table_name, '\"' , ', ', CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END, '.id' , ', au.ip, au.user_agent' , denormalize_values, ' FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID();');     -- \u0413\u0435\u043d\u0435\u0440\u0438\u043c \u0441\u0442\u0440\u043e\u0447\u043a\u0438 \u0434\u043b\u044f \u0432\u0441\u0442\u0430\u0432\u043a\u0438 \u043f\u043e\u043b\u0435\u0439 \u0432 \u043f\u043e\u0437\u0438\u0446\u0438\u0438 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430     SET text := CONCAT(text     , '\\nINSERT INTO protocol_pos (prot_id, column_name, '     , CASE trigger_type WHEN 'INSERT' THEN 'new_val'         WHEN 'UPDATE' THEN 'old_val, new_val'         WHEN 'DELETE' THEN 'old_val'       END     , ')\\n'     , (SELECT GROUP_CONCAT('SELECT prot_id, \"', C.COLUMN_NAME, '\", '         , CASE WHEN trigger_type = 'UPDATE'            THEN CONCAT('OLD.', C.COLUMN_NAME, ', NEW.', C.COLUMN_NAME, ' FROM dual WHERE IFNULL(NEW.', C.COLUMN_NAME, ', \"-\u042a\u044a\u042a\") != IFNULL(OLD.', C.COLUMN_NAME, ', \"-\u042a\u044a\u042a\")')            WHEN trigger_type = 'INSERT'            THEN CONCAT('NEW.', C.COLUMN_NAME)            WHEN trigger_type = 'DELETE'            THEN CONCAT('OLD.', C.COLUMN_NAME)           END         SEPARATOR '\\nUNION ALL '         ) text         FROM INFORMATION_SCHEMA.COLUMNS C         LEFT JOIN protocol_config ec ON ec.command = 'exclude_column'          AND ec.table_name = C.TABLE_NAME          AND ec.column_name = C.COLUMN_NAME         WHERE C.TABLE_NAME = table_name          AND C.TABLE_SCHEMA = DATABASE()          AND C.COLUMN_TYPE != 'text'          AND ec.id IS NULL       )     , ';\\nEND;'     );     RETURN text; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u0424\u0443\u043d\u043a\u0446\u0438\u044f generate_trigger<\/b> \u2014 \u0431\u0438\u0437\u043d\u0435\u0441-\u043b\u043e\u0433\u0438\u043a\u0430 + \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435:  <\/p>\n
    generate_trigger<\/b><\/p>\n
    \n
    DELIMITER $ DROP FUNCTION IF EXISTS generate_trigger$ CREATE FUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN     DECLARE text TEXT;     DECLARE bl_text TEXT;     DECLARE prot_text TEXT;     DECLARE trigger_time_short VARCHAR(3);     DECLARE trigger_type_short VARCHAR(3);     SET group_concat_max_len = 9000000;     SET trigger_time_short := LOWER(SUBSTR(trigger_time, 1, 3));     SET trigger_type_short := LOWER(SUBSTR(trigger_type, 1, 3));     SET text := '';     SET text := CONCAT(text, 'DROP TRIGGER IF EXISTS ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg$');     SET bl_text := gen_bl_trigger(table_name, trigger_time, trigger_type);     SET prot_text := gen_prot_trigger(table_name, trigger_time, trigger_type);     IF bl_text = '' AND prot_text = '' THEN         RETURN text;     END IF;     SET text := CONCAT(text, '\\nCREATE TRIGGER ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg ', trigger_time, ' ', trigger_type, ' ON ', table_name,' FOR EACH ROW trg_proc:BEGIN IF @disable_', table_name, '_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF;'     , bl_text     , prot_text     , '\\nEND$\\n'     );     RETURN text; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u0424\u0443\u043d\u043a\u0446\u0438\u044f generate_triggers<\/b> \u0434\u043b\u044f \u0433\u0435\u043d\u0435\u0440\u0430\u0446\u0438\u0438 \u0442\u0435\u043a\u0441\u0442\u0430 \u0432\u0441\u0435\u0445 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u043f\u043e \u0442\u0430\u0431\u043b\u0438\u0446\u0435:  <\/p>\n
    generate_triggers<\/b><\/p>\n
    \n
    DELIMITER $ DROP FUNCTION IF EXISTS generate_triggers$ CREATE FUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNS TEXT BEGIN     DECLARE table_name VARCHAR(200);     DECLARE text TEXT;     SET group_concat_max_len = 9000000;     SET table_name := p_table_name;     SET text := '';     SET text := (SELECT GROUP_CONCAT(generate_trigger(table_name, trigger_time, trigger_type) SEPARATOR '\\n')     FROM (SELECT 'BEFORE' trigger_time     UNION ALL SELECT 'AFTER' trigger_time) trigger_time     , (SELECT 'INSERT' trigger_type     UNION ALL SELECT 'UPDATE' trigger_type     UNION ALL SELECT 'DELETE' trigger_type     ) trigger_type);     RETURN text; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u0410\u0432\u0442\u043e\u0440\u0438\u0437\u0430\u0446\u0438\u044f \u043e\u043f\u0438\u0441\u0430\u043d\u0430 \u0432 \u0441\u0442\u0430\u0442\u044c\u0435 \u00ab\u0420\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f Row Level Security \u043d\u0430 MySQL\u00bb<\/a><\/b>  <\/p>\n
    DELIMITER ; DROP TABLE IF EXISTS users; CREATE TABLE `users` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `email` varchar(100) NOT NULL,   `pass` varchar(30) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `email` (`email`) ) DEFAULT CHARSET=utf8 COMMENT='\u041f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438 \u0441\u0438\u0441\u0442\u0435\u043c\u044b';  DROP TABLE IF EXISTS auth_users; CREATE TABLE `auth_users` (   `conn_id` bigint(20) NOT NULL,   `user_id` int(11) NOT NULL,   `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   `ip` varchar(45) DEFAULT NULL,   `user_agent` varchar(256) DEFAULT NULL,   PRIMARY KEY (`conn_id`)   -- , FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='\u0410\u0432\u0442\u043e\u0440\u0438\u0437\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438'; <\/code><\/pre>\n
      \u0422\u0435\u043f\u0435\u0440\u044c \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u043f\u0430\u0440\u0443 \u0442\u0435\u0441\u0442\u043e\u0432\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446:  <\/p>\n
    DROP TABLE IF EXISTS doc_pos; DROP TABLE IF EXISTS docs; CREATE TABLE `docs` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `num` VARCHAR(20) NOT NULL,   `date` DATE NOT NULL,   `warehouse` VARCHAR(100) NOT NULL,   `partner` VARCHAR(100) NOT NULL,   PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8 COMMENT='\u0414\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u044b'; DROP TABLE IF EXISTS doc_pos; CREATE TABLE `doc_pos` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `doc_id` int(11) NOT NULL,   `material` VARCHAR(100) NOT NULL,   `amount` int(11) NOT NULL,   `price` int(11) NOT NULL,   PRIMARY KEY (`id`)   , FOREIGN KEY (doc_id) REFERENCES docs(id) ) DEFAULT CHARSET=utf8 COMMENT='\u041f\u043e\u0437\u0438\u0446\u0438\u0438 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u043e\u0432'; <\/code><\/pre>\n
      \u0412\u044b\u043f\u043e\u043b\u043d\u0438\u043c \u0437\u0430\u043f\u0440\u043e\u0441 \u0434\u043b\u044f \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0441\u0442\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0432 \u0411\u0414:  <\/p>\n
    \u0417\u0430\u043f\u0440\u043e\u0441 \u0434\u043b\u044f \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0441\u0442\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0432 \u0411\u0414<\/b><\/p>\n
    \n
    SELECT table_name, comment, rows_cn, data_len_mb , MAX(need_bl_trg) need_bl_trg , MAX(exclude_prot) exclude_prot , MAX(CASE     WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers(\"', table_name, '\")')     WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers(\"', table_name, '\")')   END   ) create_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot , CONCAT(SUBSTRING_INDEX(gen_trg, '\\n', 2), '\\n', action_statement, '$', '\\n') action_statement , gen_trg FROM ( SELECT t.TABLE_NAME table_name , t.TABLE_COMMENT comment , t.TABLE_ROWS rows_cn , ROUND(t.DATA_LENGTH \/ 1024 \/ 1024, 2) data_len_mb , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg , CASE WHEN pd.id IS NOT NULL THEN '\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b \u0432\u044b\u043a\u043b\u044e\u0447\u0435\u043d'    WHEN pc.id IS NOT NULL THEN '\u0418\u0441\u043a\u043b\u044e\u0447\u0435\u043d\u043e \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u043e\u0439'    WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN '\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f'    WHEN C.COLUMN_NAME IS NULL THEN '\u041d\u0435\u0442 \u043f\u043e\u043b\u044f id'   END exclude_prot , tr.ACTION_STATEMENT action_statement , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')  AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA  AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id' LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA  AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE()  AND t.TABLE_TYPE = 'BASE TABLE'  AND t.ENGINE = 'InnoDB' ) d) d) d GROUP BY table_name, comment, rows_cn, data_len_mb ORDER BY table_name ; <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      <\/p>\n
     +---------------+--------------------------+-------+-----------+-----------+------------------+-------------------------------------------+ |table_name     |comment                   |rows_cn|data_len_mb|need_bl_trg|exclude_prot      |create_trg                                 | +---------------+--------------------------+-------+-----------+-----------+------------------+-------------------------------------------+ |docs           |\u0414\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u044b                 |      0|       0.02|       NULL|NULL              |SELECT generate_triggers(\"docs\")           | |doc_pos        |\u041f\u043e\u0437\u0438\u0446\u0438\u0438 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u043e\u0432        |      0|       0.02|       NULL|NULL              |SELECT generate_triggers(\"doc_pos\")        | |protocol       |\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439        |      0|       0.02|       NULL|\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f|NULL                                       | |protocol_config|\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f|      0|       0.02|       NULL|NULL              |SELECT generate_triggers(\"protocol_config\")| |protocol_pos   |\u041f\u043e\u043b\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439  |      0|       0.02|       NULL|\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f|NULL                                       | |users          |\u041f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438 \u0441\u0438\u0441\u0442\u0435\u043c\u044b      |      0|       0.02|       NULL|NULL              |SELECT generate_triggers(\"users\")          | +---------------+--------------------------+-------+-----------+-----------+------------------+-------------------------------------------+ <\/pre>\n
      \u0421\u0438\u0441\u0442\u0435\u043c\u0430 \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442 \u043d\u0430\u043c \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u044b \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043d\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b docs, doc_pos, protocol_config \u0438 users<\/b>
      \u041e\u0431\u0435\u0440\u043d\u0451\u043c \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 SELECT-\u043e\u043c \u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u043c \u0435\u0449\u0451 \u0440\u0430\u0437:  <\/p>\n
    \u0417\u0430\u043f\u0440\u043e\u0441 \u0434\u043b\u044f \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0441\u0442\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0432 \u0411\u0414<\/b><\/p>\n
    \n
    SELECT GROUP_CONCAT(create_trg SEPARATOR '\\nUNION ALL ') sql_text FROM ( SELECT table_name, comment, rows_cn, data_len_mb , MAX(need_bl_trg) need_bl_trg , MAX(exclude_prot) exclude_prot , MAX(CASE     WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers(\"', table_name, '\")')     WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers(\"', table_name, '\")')   END   ) create_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot , CONCAT(SUBSTRING_INDEX(gen_trg, '\\n', 2), '\\n', action_statement, '$', '\\n') action_statement , gen_trg FROM ( SELECT t.TABLE_NAME table_name , t.TABLE_COMMENT comment , t.TABLE_ROWS rows_cn , ROUND(t.DATA_LENGTH \/ 1024 \/ 1024, 2) data_len_mb , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg , CASE WHEN pd.id IS NOT NULL THEN '\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b \u0432\u044b\u043a\u043b\u044e\u0447\u0435\u043d'    WHEN pc.id IS NOT NULL THEN '\u0418\u0441\u043a\u043b\u044e\u0447\u0435\u043d\u043e \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u043e\u0439'    WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN '\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f'    WHEN C.COLUMN_NAME IS NULL THEN '\u041d\u0435\u0442 \u043f\u043e\u043b\u044f id'   END exclude_prot , tr.ACTION_STATEMENT action_statement , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')  AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA  AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id' LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA  AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE()  AND t.TABLE_TYPE = 'BASE TABLE'  AND t.ENGINE = 'InnoDB' ) d) d) d GROUP BY table_name, comment, rows_cn, data_len_mb ORDER BY table_name ) d ; <\/code><\/pre>\n<\/div>\n<\/div>\n
      \u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442:  <\/p>\n
    SELECT generate_triggers(\"docs\") UNION ALL SELECT generate_triggers(\"doc_pos\") UNION ALL SELECT generate_triggers(\"protocol_config\") UNION ALL SELECT generate_triggers(\"users\") ; <\/code><\/pre>\n
      \u0412\u044b\u043f\u043e\u043b\u043d\u0438\u043c \u0441\u0435\u0439\u0447\u0430\u0441 \u044d\u0442\u043e\u0442 \u0437\u0430\u043f\u0440\u043e\u0441:  <\/p>\n
    SELECT generate_triggers(docs) UNION ALL SELECT ….<\/b><\/p>\n
    \n
    DROP TRIGGER IF EXISTS docs_bef_ins_trg$ DROP TRIGGER IF EXISTS docs_aft_ins_trg$ CREATE TRIGGER docs_aft_ins_trg AFTER INSERT ON docs FOR EACH ROW trg_proc:BEGIN IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"I\", \"docs\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, \"id\", NEW.id UNION ALL SELECT prot_id, \"num\", NEW.num UNION ALL SELECT prot_id, \"date\", NEW.date UNION ALL SELECT prot_id, \"warehouse\", NEW.warehouse UNION ALL SELECT prot_id, \"partner\", NEW.partner; END; END$  DROP TRIGGER IF EXISTS docs_bef_upd_trg$ DROP TRIGGER IF EXISTS docs_aft_upd_trg$ CREATE TRIGGER docs_aft_upd_trg AFTER UPDATE ON docs FOR EACH ROW trg_proc:BEGIN IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, \"-\u042a\u044a\u042a\") = IFNULL(OLD.id, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.num, \"-\u042a\u044a\u042a\") = IFNULL(OLD.num, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.date, \"-\u042a\u044a\u042a\") = IFNULL(OLD.date, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.warehouse, \"-\u042a\u044a\u042a\") = IFNULL(OLD.warehouse, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.partner, \"-\u042a\u044a\u042a\") = IFNULL(OLD.partner, \"-\u042a\u044a\u042a\") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"U\", \"docs\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, \"id\", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, \"-\u042a\u044a\u042a\") != IFNULL(OLD.id, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"num\", OLD.num, NEW.num FROM dual WHERE IFNULL(NEW.num, \"-\u042a\u044a\u042a\") != IFNULL(OLD.num, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"date\", OLD.date, NEW.date FROM dual WHERE IFNULL(NEW.date, \"-\u042a\u044a\u042a\") != IFNULL(OLD.date, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"warehouse\", OLD.warehouse, NEW.warehouse FROM dual WHERE IFNULL(NEW.warehouse, \"-\u042a\u044a\u042a\") != IFNULL(OLD.warehouse, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"partner\", OLD.partner, NEW.partner FROM dual WHERE IFNULL(NEW.partner, \"-\u042a\u044a\u042a\") != IFNULL(OLD.partner, \"-\u042a\u044a\u042a\"); END; END$  DROP TRIGGER IF EXISTS docs_bef_del_trg$ DROP TRIGGER IF EXISTS docs_aft_del_trg$ CREATE TRIGGER docs_aft_del_trg AFTER DELETE ON docs FOR EACH ROW trg_proc:BEGIN IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"D\", \"docs\", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, \"id\", OLD.id UNION ALL SELECT prot_id, \"num\", OLD.num UNION ALL SELECT prot_id, \"date\", OLD.date UNION ALL SELECT prot_id, \"warehouse\", OLD.warehouse UNION ALL SELECT prot_id, \"partner\", OLD.partner; END; END$ DROP TRIGGER IF EXISTS users_bef_ins_trg$ DROP TRIGGER IF EXISTS users_aft_ins_trg$ CREATE TRIGGER users_aft_ins_trg AFTER INSERT ON users FOR EACH ROW trg_proc:BEGIN IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"I\", \"users\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, \"id\", NEW.id UNION ALL SELECT prot_id, \"email\", NEW.email UNION ALL SELECT prot_id, \"pass\", NEW.pass; END; END$  DROP TRIGGER IF EXISTS users_bef_upd_trg$ DROP TRIGGER IF EXISTS users_aft_upd_trg$ CREATE TRIGGER users_aft_upd_trg AFTER UPDATE ON users FOR EACH ROW trg_proc:BEGIN IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, \"-\u042a\u044a\u042a\") = IFNULL(OLD.id, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.email, \"-\u042a\u044a\u042a\") = IFNULL(OLD.email, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.pass, \"-\u042a\u044a\u042a\") = IFNULL(OLD.pass, \"-\u042a\u044a\u042a\") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"U\", \"users\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, \"id\", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, \"-\u042a\u044a\u042a\") != IFNULL(OLD.id, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"email\", OLD.email, NEW.email FROM dual WHERE IFNULL(NEW.email, \"-\u042a\u044a\u042a\") != IFNULL(OLD.email, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"pass\", OLD.pass, NEW.pass FROM dual WHERE IFNULL(NEW.pass, \"-\u042a\u044a\u042a\") != IFNULL(OLD.pass, \"-\u042a\u044a\u042a\"); END; END$  DROP TRIGGER IF EXISTS users_bef_del_trg$ DROP TRIGGER IF EXISTS users_aft_del_trg$ CREATE TRIGGER users_aft_del_trg AFTER DELETE ON users FOR EACH ROW trg_proc:BEGIN IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"D\", \"users\", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, \"id\", OLD.id UNION ALL SELECT prot_id, \"email\", OLD.email UNION ALL SELECT prot_id, \"pass\", OLD.pass; END; END$  DROP TRIGGER IF EXISTS doc_pos_bef_ins_trg$ DROP TRIGGER IF EXISTS doc_pos_aft_ins_trg$ CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW trg_proc:BEGIN IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"I\", \"doc_pos\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, \"id\", NEW.id UNION ALL SELECT prot_id, \"doc_id\", NEW.doc_id UNION ALL SELECT prot_id, \"material\", NEW.material UNION ALL SELECT prot_id, \"amount\", NEW.amount UNION ALL SELECT prot_id, \"price\", NEW.price; END; END$  DROP TRIGGER IF EXISTS doc_pos_bef_upd_trg$ DROP TRIGGER IF EXISTS doc_pos_aft_upd_trg$ CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW trg_proc:BEGIN IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, \"-\u042a\u044a\u042a\") = IFNULL(OLD.id, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.doc_id, \"-\u042a\u044a\u042a\") = IFNULL(OLD.doc_id, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.material, \"-\u042a\u044a\u042a\") = IFNULL(OLD.material, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.amount, \"-\u042a\u044a\u042a\") = IFNULL(OLD.amount, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.price, \"-\u042a\u044a\u042a\") = IFNULL(OLD.price, \"-\u042a\u044a\u042a\") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"U\", \"doc_pos\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, \"id\", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, \"-\u042a\u044a\u042a\") != IFNULL(OLD.id, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"doc_id\", OLD.doc_id, NEW.doc_id FROM dual WHERE IFNULL(NEW.doc_id, \"-\u042a\u044a\u042a\") != IFNULL(OLD.doc_id, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"material\", OLD.material, NEW.material FROM dual WHERE IFNULL(NEW.material, \"-\u042a\u044a\u042a\") != IFNULL(OLD.material, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"amount\", OLD.amount, NEW.amount FROM dual WHERE IFNULL(NEW.amount, \"-\u042a\u044a\u042a\") != IFNULL(OLD.amount, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"price\", OLD.price, NEW.price FROM dual WHERE IFNULL(NEW.price, \"-\u042a\u044a\u042a\") != IFNULL(OLD.price, \"-\u042a\u044a\u042a\"); END; END$  DROP TRIGGER IF EXISTS doc_pos_bef_del_trg$ DROP TRIGGER IF EXISTS doc_pos_aft_del_trg$ CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW trg_proc:BEGIN IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"D\", \"doc_pos\", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, \"id\", OLD.id UNION ALL SELECT prot_id, \"doc_id\", OLD.doc_id UNION ALL SELECT prot_id, \"material\", OLD.material UNION ALL SELECT prot_id, \"amount\", OLD.amount UNION ALL SELECT prot_id, \"price\", OLD.price; END; END$ DROP TRIGGER IF EXISTS protocol_config_bef_ins_trg$ DROP TRIGGER IF EXISTS protocol_config_aft_ins_trg$ CREATE TRIGGER protocol_config_aft_ins_trg AFTER INSERT ON protocol_config FOR EACH ROW trg_proc:BEGIN IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"I\", \"protocol_config\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, \"id\", NEW.id UNION ALL SELECT prot_id, \"command\", NEW.command UNION ALL SELECT prot_id, \"table_name\", NEW.table_name UNION ALL SELECT prot_id, \"column_name\", NEW.column_name UNION ALL SELECT prot_id, \"denormalize_column\", NEW.denormalize_column; END; END$  DROP TRIGGER IF EXISTS protocol_config_bef_upd_trg$ DROP TRIGGER IF EXISTS protocol_config_aft_upd_trg$ CREATE TRIGGER protocol_config_aft_upd_trg AFTER UPDATE ON protocol_config FOR EACH ROW trg_proc:BEGIN IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, \"-\u042a\u044a\u042a\") = IFNULL(OLD.id, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.command, \"-\u042a\u044a\u042a\") = IFNULL(OLD.command, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.table_name, \"-\u042a\u044a\u042a\") = IFNULL(OLD.table_name, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.column_name, \"-\u042a\u044a\u042a\") = IFNULL(OLD.column_name, \"-\u042a\u044a\u042a\") AND IFNULL(NEW.denormalize_column, \"-\u042a\u044a\u042a\") = IFNULL(OLD.denormalize_column, \"-\u042a\u044a\u042a\") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"U\", \"protocol_config\", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, \"id\", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, \"-\u042a\u044a\u042a\") != IFNULL(OLD.id, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"command\", OLD.command, NEW.command FROM dual WHERE IFNULL(NEW.command, \"-\u042a\u044a\u042a\") != IFNULL(OLD.command, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"table_name\", OLD.table_name, NEW.table_name FROM dual WHERE IFNULL(NEW.table_name, \"-\u042a\u044a\u042a\") != IFNULL(OLD.table_name, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"column_name\", OLD.column_name, NEW.column_name FROM dual WHERE IFNULL(NEW.column_name, \"-\u042a\u044a\u042a\") != IFNULL(OLD.column_name, \"-\u042a\u044a\u042a\") UNION ALL SELECT prot_id, \"denormalize_column\", OLD.denormalize_column, NEW.denormalize_column FROM dual WHERE IFNULL(NEW.denormalize_column, \"-\u042a\u044a\u042a\") != IFNULL(OLD.denormalize_column, \"-\u042a\u044a\u042a\"); END; END$  DROP TRIGGER IF EXISTS protocol_config_bef_del_trg$ DROP TRIGGER IF EXISTS protocol_config_aft_del_trg$ CREATE TRIGGER protocol_config_aft_del_trg AFTER DELETE ON protocol_config FOR EACH ROW trg_proc:BEGIN IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, \"D\", \"protocol_config\", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, \"id\", OLD.id UNION ALL SELECT prot_id, \"command\", OLD.command UNION ALL SELECT prot_id, \"table_name\", OLD.table_name UNION ALL SELECT prot_id, \"column_name\", OLD.column_name UNION ALL SELECT prot_id, \"denormalize_column\", OLD.denormalize_column; END; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u0423 \u043d\u0430\u0441 \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u0441\u044f \u0442\u0435\u043a\u0441\u0442 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u043c \u0435\u0433\u043e (\u0441 DELIMITER $<\/b>)<\/p>\n
      \u0422\u0435\u043f\u0435\u0440\u044c \u043d\u0430\u0448\u0438 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u044e\u0442\u0441\u044f \u0438 \u0432\u0441\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u044b\u0445 \u0437\u0430\u043f\u0438\u0441\u044b\u0432\u0430\u044e\u0442\u0441\u044f \u0432 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b.
      \u0427\u0435\u043a\u043d\u0435\u043c \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0438 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043f\u0435\u0440\u0432\u044b\u043c \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c:  <\/p>\n
    \u0417\u0430\u043f\u0440\u043e\u0441 \u0434\u043b\u044f \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0441\u0442\u0438 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u0432 \u0411\u0414<\/b><\/p>\n
    \n
    DELIMITER ; SELECT table_name, comment, rows_cn, data_len_mb , MAX(need_bl_trg) need_bl_trg , MAX(exclude_prot) exclude_prot , MAX(CASE     WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers(\"', table_name, '\")')     WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers(\"', table_name, '\")')   END   ) create_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot , CONCAT(SUBSTRING_INDEX(gen_trg, '\\n', 2), '\\n', action_statement, '$', '\\n') action_statement , gen_trg FROM ( SELECT t.TABLE_NAME table_name , t.TABLE_COMMENT comment , t.TABLE_ROWS rows_cn , ROUND(t.DATA_LENGTH \/ 1024 \/ 1024, 2) data_len_mb , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg , CASE WHEN pd.id IS NOT NULL THEN '\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b \u0432\u044b\u043a\u043b\u044e\u0447\u0435\u043d'    WHEN pc.id IS NOT NULL THEN '\u0418\u0441\u043a\u043b\u044e\u0447\u0435\u043d\u043e \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u043e\u0439'    WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN '\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f'    WHEN C.COLUMN_NAME IS NULL THEN '\u041d\u0435\u0442 \u043f\u043e\u043b\u044f id'   END exclude_prot , tr.ACTION_STATEMENT action_statement , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')  AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA  AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id' LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA  AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE()  AND t.TABLE_TYPE = 'BASE TABLE'  AND t.ENGINE = 'InnoDB' ) d) d) d GROUP BY table_name, comment, rows_cn, data_len_mb ORDER BY table_name ; <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      <\/p>\n
     +---------------+--------------------------+-------+-----------+-----------+------------------+----------+ |table_name     |comment                   |rows_cn|data_len_mb|need_bl_trg|exclude_prot      |create_trg| +---------------+--------------------------+-------+-----------+-----------+------------------+----------+ |docs           |\u0414\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u044b                 |      0|       0.02|       NULL|NULL              |NULL      | |doc_pos        |\u041f\u043e\u0437\u0438\u0446\u0438\u0438 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u043e\u0432        |      0|       0.02|       NULL|NULL              |NULL      | |protocol       |\u041f\u0440\u043e\u0442\u043e\u043a\u043e\u043b \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439        |      0|       0.02|       NULL|\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f|NULL      | |protocol_config|\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f|      0|       0.02|       NULL|NULL              |NULL      | |protocol_pos   |\u041f\u043e\u043b\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439  |      0|       0.02|       NULL|\u041d\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f|NULL      | |users          |\u041f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438 \u0441\u0438\u0441\u0442\u0435\u043c\u044b      |      0|       0.02|       NULL|NULL              |NULL      | +---------------+--------------------------+-------+-----------+-----------+------------------+----------+ 6 rows in set, 0 warnings (5.33 sec) <\/pre>\n
      \u0414\u043e\u0431\u0430\u0432\u0438\u043c \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f \u0438 \u0430\u0432\u0442\u043e\u0440\u0438\u0437\u0443\u0435\u043c\u0441\u044f:  <\/p>\n
    \/* DELETE FROM doc_pos; DELETE FROM docs; DELETE FROM auth_users; DELETE FROM users; DELETE FROM protocol_pos; DELETE FROM protocol; *\/ INSERT INTO users (email, pass) VALUES ('test@test.ru', '12345'); Query OK, 1 row affected (0.01 sec)  INSERT INTO auth_users (conn_id, user_id) SELECT CONNECTION_ID() conn_id , (SELECT u.id FROM users u WHERE u.email = 'test@test.ru') user_id ; Query OK, 1 row affected (0.00 sec) <\/code><\/pre>\n
      \u0412\u0441\u0442\u0430\u0432\u0438\u043c \u0442\u0435\u0441\u0442\u043e\u0432\u044b\u0439 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442:  <\/p>\n
    BEGIN; INSERT INTO docs (num, date, warehouse, partner) VALUES ('1', '2018-07-17', '\u0421\u043a\u043b\u0430\u0434 \u041c\u043e\u0441\u043a\u0432\u0430', '\u0413\u043e\u0440\u0438\u0437\u043e\u043d\u0442, \u041e\u041e\u041e'); SET @doc_id := LAST_INSERT_ID(); INSERT INTO doc_pos (doc_id, material, amount, price) VALUES (@doc_id, '\u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0432\u0430\u0440\u0435\u043b\u044c\u043d\u044b\u0435', 10, 52) , (@doc_id, '\u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0440\u0438\u043b\u043e\u0432\u044b\u0435', 20, 165) , (@doc_id, '\u0420\u0443\u0447\u043a\u0430 \u0448\u0430\u0440\u0438\u043a\u043e\u0432\u0430\u044f', 7, 30); COMMIT; <\/code><\/pre>\n
      \u041f\u043e\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u0447\u0442\u043e \u0443 \u043d\u0430\u0441 \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c \u0432 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0435:  <\/p>\n
    SELECT id, date, oper, table_name, table_id , (SELECT GROUP_CONCAT(pp.column_name, ': ('      , IFNULL(pp.old_val, 'NULL')      , ', '      , IFNULL(pp.new_val, 'NULL')      , ')'      SEPARATOR ', '      )    FROM protocol_pos pp    WHERE pp.prot_id = p.id    ) vals , p.username FROM protocol p; <\/code><\/pre>\n
      <\/p>\n
    \u0417\u0430\u043f\u0440\u043e\u0441 \u0434\u043b\u044f html<\/b><\/p>\n
    \n
    SELECT id, date, oper, table_name, table_id , (SELECT CONCAT('<table class=\"table table-bordered\"  style=\"width: 100%; margin: -9px;\">'    , GROUP_CONCAT('<tr><td style=\"font-weight: bold; width: 20%;\">', pp.column_name, '<\/td>'      , '<td style=\"width: 40%;\">', IFNULL(pp.old_val, \"<span style='color: #FF0000; font-style: italic;'>NULL<\/span>\"), '<\/td>'      , '<td style=\"width: 40%;\">', IFNULL(pp.new_val, \"<span style='color: #FF0000; font-style: italic;'>NULL<\/span>\"), '<\/td><\/tr>'      SEPARATOR ''      )    , '<\/table>'    )    FROM protocol_pos pp    WHERE pp.prot_id = p.id    ) vals , p.username FROM protocol p; <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      <\/p>\n
     +----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+ | id | date                | oper | table_name | table_id | vals                                                                                                                    | username          | +----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+ |  1 | 2018-10-09 17:21:27 | I    | users      |        1 | email: (NULL, test@test.ru), id: (NULL, 1), pass: (NULL, 12345)                                                         | admin@myhosting.ru| |  2 | 2018-10-09 17:21:51 | I    | docs       |        1 | date: (NULL, 2018-07-17), id: (NULL, 1), num: (NULL, 1), partner: (NULL, \u0413\u043e\u0440\u0438\u0437\u043e\u043d\u0442, \u041e\u041e\u041e), warehouse: (NULL, \u0421\u043a\u043b\u0430\u0434 \u041c\u043e\u0441\u043a\u0432\u0430)| test@test.ru      | |  3 | 2018-10-09 17:21:51 | I    | doc_pos    |        1 | amount: (NULL, 10), doc_id: (NULL, 1), id: (NULL, 1), material: (NULL, \u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0432\u0430\u0440\u0435\u043b\u044c\u043d\u044b\u0435), price: (NULL, 52)           | test@test.ru      | |  4 | 2018-10-09 17:21:51 | I    | doc_pos    |        2 | amount: (NULL, 20), doc_id: (NULL, 1), id: (NULL, 2), material: (NULL, \u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0440\u0438\u043b\u043e\u0432\u044b\u0435), price: (NULL, 165)            | test@test.ru      | |  5 | 2018-10-09 17:21:51 | I    | doc_pos    |        3 | amount: (NULL, 7), doc_id: (NULL, 1), id: (NULL, 3), material: (NULL, \u0420\u0443\u0447\u043a\u0430 \u0448\u0430\u0440\u0438\u043a\u043e\u0432\u0430\u044f), price: (NULL, 30)               | test@test.ru      | +----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+ <\/pre>\n
      \u041a\u0430\u043a \u0432\u0438\u0434\u0438\u043c \u0432\u0441\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0411\u0414 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u0443\u044e\u0442\u0441\u044f, \u043d\u0430\u0447\u0438\u043d\u0430\u044f \u043e\u0442 \u0432\u0441\u0442\u0430\u0432\u043a\u0438 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f.
      \u041f\u043e\u043b\u0443\u0447\u0438\u043c \u043e\u0442\u0447\u0451\u0442 \u043f\u043e \u043f\u0440\u043e\u0434\u0430\u0436\u0430\u043c:  <\/p>\n
    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date; <\/code><\/pre>\n
      <\/p>\n
     +---------------------+------------+------+ | report_time         | date       | sum  | +---------------------+------------+------+ | 2018-10-09 17:23:47 | 2018-07-17 | 4030 | +---------------------+------------+------+ <\/pre>\n
      \u0422\u0435\u043f\u0435\u0440\u044c \u0438\u0437\u043c\u0435\u043d\u0438\u043c \u0438\u043c\u0435\u044e\u0449\u0438\u0439\u0441\u044f \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442 \u0438 \u0434\u043e\u0431\u0430\u0432\u0438\u043c \u0435\u0449\u0451 \u043e\u0434\u0438\u043d:  <\/p>\n
    BEGIN; SET @doc_id := (SELECT id FROM docs WHERE num = '1'); UPDATE docs SET date = '2018-07-16', warehouse = warehouse WHERE id = @doc_id; DELETE FROM doc_pos WHERE doc_id = @doc_id AND material = '\u0420\u0443\u0447\u043a\u0430 \u0448\u0430\u0440\u0438\u043a\u043e\u0432\u0430\u044f'; UPDATE doc_pos p SET p.price = 105, p.material = '\u041a\u0440\u0430\u0441\u043a\u0438 \u043c\u0430\u0441\u043b\u044f\u043d\u044b\u0435' WHERE p.doc_id = @doc_id AND p.material = '\u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0440\u0438\u043b\u043e\u0432\u044b\u0435'; INSERT INTO docs (num, date, warehouse, partner) VALUES ('2', '2018-07-18', '\u0421\u043a\u043b\u0430\u0434 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a', '\u0420\u0430\u0434\u0443\u0433\u0430, \u0417\u0410\u041e'); SET @doc_id := LAST_INSERT_ID(); INSERT INTO doc_pos (doc_id, material, amount, price) VALUES (@doc_id, '\u0420\u0430\u043c\u043a\u0430 10*15', 5, 102) , (@doc_id, '\u0411\u0443\u043c\u0430\u0433\u0430 \u04104', 2, 165); COMMIT; <\/code><\/pre>\n
      <\/p>\n
    \u0422\u0430\u043a \u0431\u0443\u0434\u0435\u0442 \u0432\u044b\u0433\u043b\u044f\u0434\u0435\u0442\u044c \u043d\u043e\u0432\u044b\u0439 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b<\/b><\/p>\n
    \n
    SELECT id, date, oper, table_name, table_id , (SELECT GROUP_CONCAT(pp.column_name, ': ('      , IFNULL(pp.old_val, 'NULL')      , ', '      , IFNULL(pp.new_val, 'NULL')      , ')'      SEPARATOR ', '      )    FROM protocol_pos pp    WHERE pp.prot_id = p.id    ) vals , p.username FROM protocol p; <\/code><\/pre>\n
      <\/p>\n
     +----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+ | id | date                | oper | table_name | table_id | vals                                                                                                                       | username          | +----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+ |  1 | 2018-10-09 17:21:27 | I    | users      |        1 | email: (NULL, test@test.ru), id: (NULL, 1), pass: (NULL, 12345)                                                            | admin@myhosting.ru| |  2 | 2018-10-09 17:21:51 | I    | docs       |        1 | date: (NULL, 2018-07-17), id: (NULL, 1), num: (NULL, 1), partner: (NULL, \u0413\u043e\u0440\u0438\u0437\u043e\u043d\u0442, \u041e\u041e\u041e), warehouse: (NULL, \u0421\u043a\u043b\u0430\u0434 \u041c\u043e\u0441\u043a\u0432\u0430)   | test@test.ru      | |  3 | 2018-10-09 17:21:51 | I    | doc_pos    |        1 | amount: (NULL, 10), doc_id: (NULL, 1), id: (NULL, 1), material: (NULL, \u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0432\u0430\u0440\u0435\u043b\u044c\u043d\u044b\u0435), price: (NULL, 52)              | test@test.ru      | |  4 | 2018-10-09 17:21:51 | I    | doc_pos    |        2 | amount: (NULL, 20), doc_id: (NULL, 1), id: (NULL, 2), material: (NULL, \u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0440\u0438\u043b\u043e\u0432\u044b\u0435), price: (NULL, 165)               | test@test.ru      | |  5 | 2018-10-09 17:21:51 | I    | doc_pos    |        3 | amount: (NULL, 7), doc_id: (NULL, 1), id: (NULL, 3), material: (NULL, \u0420\u0443\u0447\u043a\u0430 \u0448\u0430\u0440\u0438\u043a\u043e\u0432\u0430\u044f), price: (NULL, 30)                  | test@test.ru      | |  6 | 2018-10-09 17:24:27 | U    | docs       |        1 | date: (2018-07-17, 2018-07-16)                                                                                             | test@test.ru      | |  7 | 2018-10-09 17:24:27 | D    | doc_pos    |        3 | amount: (7, NULL), doc_id: (1, NULL), id: (3, NULL), material: (\u0420\u0443\u0447\u043a\u0430 \u0448\u0430\u0440\u0438\u043a\u043e\u0432\u0430\u044f, NULL), price: (30, NULL)                  | test@test.ru      | |  8 | 2018-10-09 17:24:27 | U    | doc_pos    |        2 | material: (\u041a\u0440\u0430\u0441\u043a\u0438 \u0430\u043a\u0440\u0438\u043b\u043e\u0432\u044b\u0435, \u041a\u0440\u0430\u0441\u043a\u0438 \u043c\u0430\u0441\u043b\u044f\u043d\u044b\u0435), price: (165, 105)                                                           | test@test.ru      | |  9 | 2018-10-09 17:24:27 | I    | docs       |        2 | date: (NULL, 2018-07-18), id: (NULL, 2), num: (NULL, 2), partner: (NULL, \u0420\u0430\u0434\u0443\u0433\u0430, \u0417\u0410\u041e), warehouse: (NULL, \u0421\u043a\u043b\u0430\u0434 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a)| test@test.ru      | | 10 | 2018-10-09 17:24:27 | I    | doc_pos    |        4 | amount: (NULL, 5), doc_id: (NULL, 2), id: (NULL, 4), material: (NULL, \u0420\u0430\u043c\u043a\u0430 10*15), price: (NULL, 102)                     | test@test.ru      | | 11 | 2018-10-09 17:24:27 | I    | doc_pos    |        5 | amount: (NULL, 2), doc_id: (NULL, 2), id: (NULL, 5), material: (NULL, \u0411\u0443\u043c\u0430\u0433\u0430 \u04104), price: (NULL, 165)                       | test@test.ru      | +----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+ <\/pre>\n<\/div>\n<\/div>\n
    \u041f\u043e\u043b\u0443\u0447\u0438\u043c \u043d\u043e\u0432\u044b\u0439 \u043e\u0442\u0447\u0451\u0442:  <\/p>\n
    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date; <\/code><\/pre>\n
      <\/p>\n
     +---------------------+------------+------+ | report_time         | date       | sum  | +---------------------+------------+------+ | 2018-10-09 17:26:18 | 2018-07-16 | 2620 | | 2018-10-09 17:26:18 | 2018-07-18 |  840 | +---------------------+------------+------+ <\/pre>\n
      \u0421\u043c\u043e\u0442\u0440\u0438\u043c \u043d\u0430 \u043e\u0442\u0447\u0451\u0442 \u0438 \u043d\u0435 \u043c\u043e\u0436\u0435\u043c \u043d\u0430\u0439\u0442\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0437\u0430 2018-07-17<\/b> \u0447\u0438\u0441\u043b\u043e, \u0445\u043e\u0442\u044f \u0442\u043e\u0447\u043d\u043e \u043f\u043e\u043c\u043d\u0438\u043c, \u0447\u0442\u043e \u043e\u043d\u0438 \u0431\u044b\u043b\u0438, \u0443 \u043d\u0430\u0441 \u0434\u0430\u0436\u0435 \u0440\u0430\u0441\u043f\u0435\u0447\u0430\u0442\u0430\u043d\u043d\u044b\u0439 \u043e\u0442\u0447\u0451\u0442 \u0435\u0441\u0442\u044c \u0432 2018-10-09 17:23:47<\/b><\/p>\n
      \u041d\u0430\u0443\u0447\u0438\u043c MySQL<\/b> \u0437\u0430\u0433\u043b\u044f\u0434\u044b\u0432\u0430\u0442\u044c \u0432 \u043f\u0440\u043e\u0448\u043b\u043e\u0435! \u0414\u043b\u044f \u044d\u0442\u043e\u0433\u043e \u043d\u0430\u043f\u0438\u0448\u0435\u043c \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043f\u043e \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0443 \u0441\u043c\u043e\u0433\u0443\u0442 \u043e\u0442\u043a\u0430\u0442\u044b\u0432\u0430\u0442\u044c \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f.<\/p>\n
      \u041f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0430 exec_protocol<\/b> \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u043f\u043e \u0441\u0442\u0440\u043e\u0447\u043a\u0435 \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430 (p_prot_id)  <\/p>\n
    exec_protocol<\/b><\/p>\n
    \n
    DELIMITER $ DROP PROCEDURE IF EXISTS exec_protocol$ CREATE PROCEDURE exec_protocol(p_prot_id BIGINT, direction INT) BEGIN     DECLARE p_sql_text TEXT;     DECLARE done INT DEFAULT FALSE;     DECLARE cur CURSOR FOR     SELECT CONCAT(       CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1 THEN 'INSERT INTO'           WHEN p.oper = 'U' THEN 'UPDATE'           WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1 THEN 'DELETE FROM'         END       , ' ', p.table_name, ' '       , CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1           THEN CONCAT('(', GROUP_CONCAT(pos.column_name ORDER BY pos.column_name SEPARATOR ', '), ')'           , ' VALUES (', GROUP_CONCAT(QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END)             ORDER BY pos.column_name             SEPARATOR ', '             )           , ')'           )           WHEN p.oper = 'U'           THEN CONCAT('SET ', GROUP_CONCAT(pos.column_name             , ' = ', QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END)             ORDER BY pos.column_name             SEPARATOR ', '             )           , ' WHERE id = ', p.table_id           )           WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1           THEN CONCAT('WHERE id = ', p.table_id)         END       ) sql_text     FROM protocol p     INNER JOIN protocol_pos pos ON p.id = pos.prot_id     WHERE p.id = p_prot_id     ;     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     OPEN cur;     read_loop: LOOP         FETCH cur INTO p_sql_text;         IF done THEN             LEAVE read_loop;         END IF;         SET @exec_protocol_sql_text := p_sql_text;         SET @disable_all_prot_trg = 1;         -- SELECT @exec_protocol_sql_text;         PREPARE c_sql FROM @exec_protocol_sql_text;         EXECUTE c_sql;         DEALLOCATE PREPARE c_sql;         SET @disable_all_prot_trg = NULL;     END LOOP;     CLOSE cur; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u041f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0430 set_prot_snapshot_id<\/b> \u043e\u0442\u043a\u0430\u0442\u044b\u0432\u0430\u0435\u0442\/\u043d\u0430\u043a\u0430\u0442\u044b\u0432\u0430\u0435\u0442 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430 \u043f\u043e \u0434\u0438\u0430\u043f\u0430\u0437\u043e\u043d\u0443 id  <\/p>\n
    set_prot_snapshot_id<\/b><\/p>\n
    \n
    DELIMITER $ DROP PROCEDURE IF EXISTS set_prot_snapshot_id$ CREATE PROCEDURE set_prot_snapshot_id(p_beg_prot_id BIGINT, p_end_prot_id BIGINT, direction INT) BEGIN     DECLARE p_prot_id BIGINT;     DECLARE done INT DEFAULT FALSE;     DECLARE cur CURSOR FOR       SELECT p.id       FROM protocol p       WHERE p.id >= p_beg_prot_id        AND (p.id <= p_end_prot_id OR p_end_prot_id IS NULL)       ORDER BY p.id * direction       ;     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     OPEN cur;     read_loop: LOOP         FETCH cur INTO p_prot_id;         IF done THEN             LEAVE read_loop;         END IF;         CALL exec_protocol(p_prot_id, SIGN(direction));         -- \u0415\u0441\u043b\u0438 direction = -2, \u0442\u043e \u043e\u0442\u043a\u0430\u0442\u044b\u0432\u0430\u0435\u043c \u0431\u0430\u0437\u0443 \u0441 \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0435\u043c \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430         IF direction = -2 THEN             DELETE FROM protocol WHERE id = p_prot_id;         END IF;     END LOOP;     CLOSE cur; END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u041f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0430 set_prot_snapshot_date<\/b> \u043e\u0442\u043a\u0430\u0442\u044b\u0432\u0430\u0435\u0442\/\u043d\u0430\u043a\u0430\u0442\u044b\u0432\u0430\u0435\u0442 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0430 \u0437\u0430 \u043f\u0435\u0440\u0438\u043e\u0434  <\/p>\n
    set_prot_snapshot_date<\/b><\/p>\n
    \n
    DELIMITER $ DROP PROCEDURE IF EXISTS set_prot_snapshot_date$ CREATE PROCEDURE set_prot_snapshot_date(p_beg_date TIMESTAMP, p_end_date TIMESTAMP, direction INT) BEGIN     DECLARE beg_prot_id BIGINT;     DECLARE end_prot_id BIGINT;     SET beg_prot_id := (SELECT id FROM protocol WHERE date >= p_beg_date ORDER BY id LIMIT 1);     SET end_prot_id := (SELECT id FROM protocol WHERE date <= p_end_date ORDER BY id DESC LIMIT 1);     CALL set_prot_snapshot_id(beg_prot_id, end_prot_id, direction); END$ <\/code><\/pre>\n
      <\/div>\n<\/div>\n
      \u0422\u0435\u043f\u0435\u0440\u044c \u043c\u044b \u043c\u043e\u0436\u0435\u043c \u043b\u0435\u0433\u043a\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043e\u0442\u0447\u0451\u0442 \u043f\u043e \u043f\u0440\u043e\u0434\u0430\u0436\u0430\u043c \u043d\u0430 \u043f\u0440\u043e\u0448\u043b\u0443\u044e \u0434\u0430\u0442\u0443:  <\/p>\n
    DELIMITER ; BEGIN; CALL set_prot_snapshot_date('2018-10-09 17:23:47', NULL, -1); SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date; ROLLBACK; <\/code><\/pre>\n
      <\/p>\n
     +---------------------+------------+------+ | report_time         | date       | sum  | +---------------------+------------+------+ | 2018-10-09 17:28:30 | 2018-07-17 | 4030 | +---------------------+------------+------+ <\/pre>\n
      \u041a\u0430\u043a \u0432\u0438\u0434\u0438\u043c, \u043e\u0442\u0447\u0451\u0442 \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u0441\u044f \u0438\u043c\u0435\u043d\u043d\u043e \u0442\u0430\u043a\u0438\u043c \u043a\u0430\u043a \u043e\u043d \u0431\u044b\u043b \u0432 \u043f\u0440\u043e\u0448\u043b\u043e\u043c.
      \u0418 \u0442.\u043a. \u043c\u044b \u0441\u0434\u0435\u043b\u0430\u043b\u0438 ROLLBACK<\/b>, \u0442\u043e \u0441\u0435\u0439\u0447\u0430\u0441 \u0442\u0430\u043a \u0436\u0435 \u043b\u0435\u0433\u043a\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u043c \u0441\u0432\u0435\u0436\u0438\u0439 \u043e\u0442\u0447\u0451\u0442:  <\/p>\n
    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date; <\/code><\/pre>\n
      <\/p>\n
     +---------------------+------------+------+ | report_time         | date       | sum  | +---------------------+------------+------+ | 2018-10-09 17:29:18 | 2018-07-16 | 2620 | | 2018-10-09 17:29:18 | 2018-07-18 |  840 | +---------------------+------------+------+ <\/pre>\n
      \u041a\u0430\u043a\u0438\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u043e\u043d\u0430\u043b\u044c\u043d\u044b\u0435 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u0435\u0442 \u0434\u0430\u0442\u044c \u043f\u0440\u043e\u0442\u043e\u043a\u043e\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435:  <\/p>\n
      \n
    1. \u0412\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u043f\u043e\u043b\u0443\u0447\u0430\u0442\u044c \u043e\u0442\u0447\u0451\u0442\u044b \u043d\u0430 \u043b\u044e\u0431\u0443\u044e \u0434\u0430\u0442\u0443 \u0432 \u043f\u0440\u043e\u0448\u043b\u043e\u043c, \u0438\u043c\u0435\u043d\u043d\u043e \u0442\u0430\u043a\u0438\u043c\u0438 \u043a\u0430\u043a\u0438\u043c\u0438 \u043e\u043d\u0438 \u0431\u044b\u043b\u0438 \u0432 \u0442\u043e\u0442 \u043c\u043e\u043c\u0435\u043d\u0442 \u0432\u0440\u0435\u043c\u0435\u043d\u0438.<\/li>\n
    2. \u041f\u043e\u0438\u0441\u043a \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u00ab\u0438\u0441\u043f\u043e\u0440\u0442\u0438\u043b\u00bb \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 \u0411\u0414.<\/li>\n
    3. \u0410\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0430 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u044b\u0445. \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u044c \u043f\u0440\u043e\u0445\u043e\u0436\u0434\u0435\u043d\u0438\u044f \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u043e\u0432 \u0432 \u0441\u0438\u0441\u0442\u0435\u043c\u0435, \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0435 \u0441\u0442\u0430\u0442\u0443\u0441\u043e\u0432.<\/li>\n
    4. \u041e\u0442\u043c\u0435\u043d\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439. \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u043f\u0440\u0438 \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0438 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430 \u0432\u043c\u0435\u0441\u0442\u043e \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0445 \u0432\u043e\u043f\u0440\u043e\u0441\u043e\u0432: \u00ab\u0412\u044b \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0445\u043e\u0442\u0438\u0442\u0435 \u0443\u0434\u0430\u043b\u0438\u0442\u044c?\u00bb, \u043c\u043e\u0436\u043d\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u0442\u044c \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u043e\u0442\u043c\u0435\u043d\u044b \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f.<\/li>\n
    5. \u0420\u0430\u0441\u0448\u0438\u0440\u0435\u043d\u0438\u0435 \u043f.4, \u043a\u043e\u0440\u0437\u0438\u043d\u0430, \u043e\u0442\u043c\u0435\u043d\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439, \u043e\u0442\u043a\u0430\u0442 \u043f\u043e \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430.<\/li>\n<\/ol>\n<\/div>\n