Mysql Openstack Stored Procedures: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
Строка 180: Строка 180:
   
   
−
DROP PROCEDURE IF EXISTS upload;
 +
DROP PROCEDURE IF EXISTS upload2;
 
DELIMITER |
  
DELIMITER |
   
−
CREATE PROCEDURE upload() BEGIN
 +
CREATE PROCEDURE upload2() BEGIN
 
DECLARE done BOOLEAN DEFAULT FALSE;
  
DECLARE done BOOLEAN DEFAULT FALSE;
−
DECLARE _data TEXT;
 +
DECLARE json_data TEXT;
  +
DECLARE _uuid TEXT;
⚫
DECLARE cur CURSOR FOR SELECT json_object(created_at,updated_at,id,user_id,project_id,image_ref,hostname,reservation_id,uuid,deleted) FROM instances;
  
  +
DECLARE cur CURSOR FOR SELECT uuid from instances;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
   
Строка 192: Строка 193:
   
 
testLoop: LOOP
  
testLoop: LOOP
−
FETCH cur INTO _data;
 +
FETCH cur INTO _uuid;
 
IF done THEN
  
IF done THEN
 
LEAVE testLoop;
  
LEAVE testLoop;
 
END IF;
  
END IF;
   
  ⚫
SELECT json_object(created_at,updated_at,id,user_id,project_id,image_ref,hostname,reservation_id,uuid,deleted) FROM instances WHERE uuid=_uuid LIMIT 1 INTO json_data;
   
−
SET @http_host='http://172.16.169.34:8080/';
 +
CALL send_http_data('http://172.16.169.34:8080/', json_data);
  ⚫
CALL send_http_data('http://127.0.0.1:8081/', json_data);
−
SET @tt_resu = (SELECT http_post(@http_host, _data));
  
−
INSERT INTO httplog(host, request,response) values(@http_host, @tt_json, @tt_resu);
  
−
 
⚫
SET @http_host='http://127.0.0.1:8080/';
  
−
SET @tt_resu = (SELECT http_post(@http_host, _data));
  
−
INSERT INTO httplog(host, request,response) values(@http_host, @tt_json, @tt_resu);
  
   
 
END LOOP testLoop;
  
END LOOP testLoop;

Версия 16:48, 1 сентября 2016


openstack --os-identity-api-version 2  project create

openstack --os-identity-api-version 2  project list
+----------------------------------+----------+
| ID                               | Name     |
+----------------------------------+----------+
| 3cd08ae190564da49ad9838039e566b4 | admin    |
| 5e1d89c540764153970ffbe8ad3403d5 | services |
| ed43deaf0b944723af84732e481504dd | project1 |
+----------------------------------+----------+

openstack --os-identity-api-version 2  user create --project project1 --password user1 user1
+------------+----------------------------------+
| Field      | Value                            |
+------------+----------------------------------+
| email      | None                             |
| enabled    | True                             |
| id         | 7759b1b8b1f2489ba869f6ff4c4b26cd |
| name       | user1                            |
| project_id | ed43deaf0b944723af84732e481504dd |
| username   | user1                            |
+------------+----------------------------------+

openstack --os-identity-api-version 2  role list
+----------------------------------+-----------------+
| ID                               | Name            |
+----------------------------------+-----------------+
| 107d9c20c6fd44859a273015128f5020 | admin           |
| 3518209f468a40618eea71eede882c4f | heat_stack_user |
| 9fe2ff9ee4384b1894a90878d3e92bab | _member_        |
| e556e1a095684c8ea2ab8ec10f87500b | SwiftOperator   |
+----------------------------------+-----------------+
root@node-1:~# openstack --os-identity-api-version 2  role add --user 7759b1b8b1f2489ba869f6ff4c4b26cd --project project1 admin
+-----------+----------------------------------+
| Field     | Value                            |
+-----------+----------------------------------+
| domain_id | None                             |
| id        | 107d9c20c6fd44859a273015128f5020 |
| name      | admin                            |
+-----------+----------------------------------+






DELIMITER |
DROP TRIGGER IF EXISTS nova_instances_insert;
CREATE TRIGGER  nova_instances_insert
AFTER INSERT ON instances
FOR EACH ROW BEGIN
    SET @tt_json = (SELECT json_object(created_at,updated_at,id,user_id,project_id,image_ref,hostname,reservation_id,uuid,deleted ) FROM instances  WHERE uuid = NEW.uuid LIMIT 1);

    SET @http_host=http://127.0.0.1:8081/'
    SET @tt_resu = (SELECT http_post(CONCAT(@http_host, NEW.id), @tt_json));
    INSERT INTO httplog(http_host, request,response) values( @tt_json, @tt_resu);
    
    SET @http_host=http://127.0.0.1:8081/'
    SET @tt_resu = (SELECT http_post(CONCAT(@http_host, NEW.id), @tt_json));
    INSERT INTO httplog(http_host, request,response) values( @tt_json, @tt_resu);

END |
DELIMITER ;







DELIMITER |
DROP TRIGGER IF EXISTS nova_instances_before_update;
CREATE TRIGGER nova_instances_before_update
BEFORE UPDATE ON instances
FOR EACH ROW BEGIN
    SET @tt_json = (SELECT json_object(created_at,updated_at,id,user_id,project_id,image_ref,hostname,reservation_id,uuid,deleted ) FROM instances  WHERE uuid = OLD.uuid LIMIT 1);

   // SET @tt_resu = (SELECT http_post(CONCAT('http://127.0.0.1:8081/', OLD.id), @tt_json));
   // SET @tt_resu = (SELECT http_post(CONCAT('http://172.16.169.34:8080/', OLD.id), @tt_json));
   // INSERT INTO httplog(request,response) values( @tt_json, @tt_resu);


    SET @http_host=http://127.0.0.1:8081/'
    SET @tt_resu = (SELECT http_post(CONCAT(@http_host, NEW.id), @tt_json));
    INSERT INTO httplog(http_host, request,response) values( @tt_json, @tt_resu);
    
    SET @http_host=http://127.0.0.1:8081/'
    SET @tt_resu = (SELECT http_post(CONCAT(@http_host, NEW.id), @tt_json));
    INSERT INTO httplog(http_host, request,response) values( @tt_json, @tt_resu);

END |
DELIMITER ;




DELIMITER |
DROP TRIGGER IF EXISTS nova_instances_after_update;
CREATE TRIGGER nova_instances_after_update
AFTER UPDATE ON instances
FOR EACH ROW BEGIN
    SET @tt_json = (SELECT json_object(created_at,updated_at,id,user_id,project_id,image_ref,hostname,reservation_id,uuid,deleted ) FROM instances WHERE uuid= OLD.uuid LIMIT 1);

//    SET @tt_resu = (SELECT http_post(CONCAT('http://127.0.0.1:8081/', OLD.uuid), @tt_json));
//    SET @tt_resu = (SELECT http_post(CONCAT('http://172.16.169.34:8080/', OLD.id), @tt_json));
//    INSERT INTO httplog(request,response) values( @tt_json,  @tt_resu);

    SET @http_host=http://127.0.0.1:8081/'
    SET @tt_resu = (SELECT http_post(CONCAT(@http_host, NEW.id), @tt_json));
    INSERT INTO httplog(http_host, request,response) values( @tt_json, @tt_resu);
    
    SET @http_host=http://127.0.0.1:8081/'
    SET @tt_resu = (SELECT http_post(CONCAT(@http_host, NEW.id), @tt_json));
    INSERT INTO httplog(http_host, request,response) values( @tt_json, @tt_resu);

END |
DELIMITER ;




DROP TABLE IF EXISTS httplog;
CREATE TABLE `httplog` 
(
  `request` varchar(512) DEFAULT NULL,
  `response` varchar(512) DEFAULT NULL,
  `seq` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `local_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `host` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`seq`)
);

123


DROP PROCEDURE IF EXISTS send_http_data;

DELIMITER |
CREATE PROCEDURE send_http_data(
IN http_host MEDIUMTEXT,
IN http_data  MEDIUMTEXT
) BEGIN




    SET @tt_resu = (SELECT http_post(http_host, http_data));
    INSERT INTO httplog(host, request,response) values(http_host, http_data, @tt_resu);

END |
DELIMITER ;




    SET @tt_resu = (SELECT http_post(http_host, _data));
    INSERT INTO httplog(host, request,response) values(http_host, _data, @tt_resu);






DROP PROCEDURE IF EXISTS upload2;
DELIMITER |

CREATE PROCEDURE upload2()  BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE json_data TEXT;
  DECLARE _uuid TEXT;
  DECLARE cur CURSOR FOR SELECT uuid from instances;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  testLoop: LOOP
    FETCH cur INTO _uuid;
    IF done THEN
      LEAVE testLoop;
    END IF;

    SELECT json_object(created_at,updated_at,id,user_id,project_id,image_ref,hostname,reservation_id,uuid,deleted) FROM instances WHERE uuid=_uuid LIMIT 1  INTO json_data;

    CALL send_http_data('http://172.16.169.34:8080/', json_data);
    CALL send_http_data('http://127.0.0.1:8081/', json_data);

  END LOOP testLoop;

  CLOSE cur;
END |
DELIMITER ;

Ссылки

Источник — https://noname.com.ua/mediawiki/index.php?title=Mysql_Openstack_Stored_Procedures&oldid=7937