1/* Copyright (C) 2014 InfiniDB, Inc. 2 3 This program is free software; you can redistribute it and/or 4 modify it under the terms of the GNU General Public License 5 as published by the Free Software Foundation; version 2 of 6 the License. 7 8 This program is distributed in the hope that it will be useful, 9 but WITHOUT ANY WARRANTY; without even the implied warranty of 10 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 11 GNU General Public License for more details. 12 13 You should have received a copy of the GNU General Public License 14 along with this program; if not, write to the Free Software 15 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, 16 MA 02110-1301, USA. */ 17 18/* 19 * $Id: calsetuserpriority.sql 9440 2013-04-24 21:07:42Z chao $ 20 */ 21 22DELIMITER $$ 23DROP PROCEDURE IF EXISTS infinidb_querystats.calSetUserPriority; 24CREATE PROCEDURE infinidb_querystats.calSetUserPriority(IN host VARCHAR(50), IN usr VARCHAR(50), IN pri VARCHAR(10)) 25LANGUAGE SQL 26NOT DETERMINISTIC 27MODIFIES SQL DATA 28SQL SECURITY INVOKER 29COMMENT 'Procedure to set a given InfiniDB user to the given priority' 30pri_validation: BEGIN 31 IF upper(pri) not in ('HIGH', 'MEDIUM', 'LOW') THEN 32 select "Priority can only be set to 'High', 'Medium', or 'Low'" Error; 33 leave pri_validation; 34 END IF; 35 36 IF INSTR(host, ":") != 0 THEN 37 select "Port number cannot be used when setting user priority" Error; 38 leave pri_validation; 39 END IF; 40 41 user_validation: BEGIN 42 DECLARE cnt,c INT; 43 DECLARE cur_2 CURSOR for select count(*) from mysql.user where upper(user.host)=upper(host) and upper(user.user)=upper(usr); 44 DECLARE CONTINUE HANDLER FOR NOT FOUND 45 SET c = 1; 46 OPEN cur_2; 47 SET cnt = 0; 48 REPEAT 49 FETCH cur_2 into cnt; 50 until c = 1 51 END REPEAT; 52 IF cnt = 0 THEN 53 select "User does not exist in MySQL" Error; 54 LEAVE user_validation; 55 END IF; 56 57 BEGIN 58 DECLARE a, b INT; 59 DECLARE cur_1 CURSOR FOR SELECT count(*) FROM infinidb_querystats.user_priority where upper(user)=upper(usr) and upper(user_priority.host)=upper(host); 60 DECLARE CONTINUE HANDLER FOR NOT FOUND 61 SET b = 1; 62 OPEN cur_1; 63 SET a = 0; 64 REPEAT 65 FETCH cur_1 INTO a; 66 UNTIL b = 1 67 END REPEAT; 68 CLOSE cur_1; 69 IF a = 0 THEN 70 insert into infinidb_querystats.user_priority values (host, usr, upper(pri)); 71 ELSE 72 update infinidb_querystats.user_priority set priority=upper(pri) where upper(user)=upper(usr) and upper(user_priority.host)=upper(host); 73 END IF; 74 END; 75 END; 76END$$ 77DELIMITER ; 78