Форум технической поддержки КБ «ПУЛЬСАР-ТЕЛЕКОМ»

Код: Выделить всё

REPLACE INTO `pegas_repository`.`locals` (param,value) VALUES ('repo_version','1')

Код: Выделить всё

    CREATE DATABASE `pegas_repository` /*!40100 DEFAULT CHARACTER SET utf8 */;
     
    DROP TABLE IF EXISTS `pegas_repository`.`locals`;
    CREATE TABLE  `pegas_repository`.`locals` (
      `param` VARCHAR(16) NOT NULL DEFAULT '',
      `value` BIGINT(20) UNSIGNED NOT NULL,
      PRIMARY KEY (`param`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    DROP TABLE IF EXISTS `pegas_repository`.`revisions`;
    CREATE TABLE  `pegas_repository`.`revisions` (
      `revision` INT(10) UNSIGNED NOT NULL,
      `comment` text NOT NULL,
      `user` VARCHAR(32) NOT NULL,
      `user_addr` INT(10) UNSIGNED NOT NULL,
      `upd_counter` BIGINT(20) UNSIGNED NOT NULL,
      `utime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`revision`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    DROP TABLE IF EXISTS `pegas_repository`.`repo`;
    CREATE TABLE  `pegas_repository`.`repo` (
      `id` CHAR(38) NOT NULL,
      `revision` INT(10) UNSIGNED NOT NULL,
      `action` enum('insert','delete') DEFAULT NULL,
      `title` text,
      `data` BLOB,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    DROP TABLE IF EXISTS `pegas_repository`.`repo_history`;
    CREATE TABLE  `pegas_repository`.`repo_history` (
      `id` CHAR(38) NOT NULL,
      `revision` INT(10) UNSIGNED NOT NULL,
      `action` enum('insert','delete') DEFAULT NULL,
      `title` text,
      `data` BLOB,
      PRIMARY KEY (`id`,`revision`) USING BTREE
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    GRANT SELECT,EXECUTE ON pegas_repository.* TO '$PEGAS_REPO$'@'%' IDENTIFIED BY PASSWORD '*BE8B2E213CD3C004D276580037FC41DB30D6EFCF';
    GRANT SELECT ON smk30.* TO '$PEGAS_REPO$'@'%';
     
    USE pegas_repository;
     
    DELIMITER $$
     
    DROP PROCEDURE IF EXISTS `pegas_repository`.`REVFIXED`$$
    CREATE PROCEDURE  `pegas_repository`.`REVFIXED`(USER CHAR(32),user_addr INT UNSIGNED,comment TEXT, OUT rev_counter INT)
    BEGIN   SET AUTOCOMMIT=0;  START TRANSACTION;  SELECT IFNULL(MAX(VALUE), 0)+1 INTO rev_counter FROM `locals` WHERE param='rev_counter';  INSERT INTO `locals` (param,VALUE) VALUES ('rev_counter',rev_counter) ON DUPLICATE KEY UPDATE VALUE=rev_counter;  INSERT INTO `revisions` (revision,comment,USER,user_addr,upd_counter,utime) VALUES (rev_counter, comment,USER,user_addr,UPDCNTR(),NULL);  COMMIT;END $$
     
    DELIMITER ;
     
    DELIMITER $$
     
    DROP FUNCTION IF EXISTS `pegas_repository`.`INSERT_IN_REPO`$$
    CREATE FUNCTION  `pegas_repository`.`INSERT_IN_REPO`(tool_id CHAR(38),tool_title TEXT,repo_data BLOB) RETURNS BIGINT(20)
    BEGIN   IF NOT ISNULL(@rev_counter) OR @rev_counter<0 THEN    INSERT pegas_repository.repo (id,action,title,DATA,revision) VALUES (tool_id,'insert',tool_title,repo_data,@rev_counter)       ON DUPLICATE KEY UPDATE action='insert',title=tool_title,DATA=repo_data,revision=@rev_counter;    INSERT pegas_repository.repo_history (id,action,title,DATA,revision) VALUES (tool_id,'insert',tool_title,repo_data,@rev_counter);    RETURN @rev_counter;  END IF;  RETURN @rev_counter;END;
     
     $$
     
    DELIMITER ;
     
    DELIMITER $$
     
    DROP FUNCTION IF EXISTS `pegas_repository`.`UPDCNTR`$$
    CREATE FUNCTION  `pegas_repository`.`UPDCNTR`() RETURNS BIGINT(20)
    BEGIN   DECLARE curr_max BIGINT;  SELECT upd_counter INTO curr_max FROM `smk30`.`locals`;  RETURN curr_max;END;
     
     $$
     
    DELIMITER ;
     
    DELIMITER $$
     
    DROP FUNCTION IF EXISTS `pegas_repository`.`REV2CNT`$$
    CREATE FUNCTION  `pegas_repository`.`REV2CNT`(rev INT UNSIGNED) RETURNS BIGINT(20)
    BEGIN   DECLARE counter BIGINT;  SELECT upd_counter INTO counter FROM `revisions` r WHERE r.revision=rev;  RETURN counter;END;
     
     $$
     
    DELIMITER ;
     
    REPLACE INTO `pegas_repository`.`locals` (param,VALUE) VALUES ('repo_version','1')