script to update cloudstack db with categories for rating licensed template usage

gistfile1.txt

#!/bin/bash

# Author - Bill Jones 
# Version - 0.1
# Release Date - 8th Oct 2013
# Contact - bill.jones@sungard.com

# CloudMonkey script to configure Service Offerings

# ======================== Defaults ======================== 

DB1SERVER="localhost"
DBACCT="cloud"
DBACCTPWD="admin1"
DBNAME="cloud"
MYSQL="mysql"

# ======================== Set Parameters ======================== 


while getopts ":u:p:h:P" opt; do
  case $opt in
    u)
      DBACCT="$OPTARG"
      ;;
    p)
      DBACCTPWD="$OPTARG"
      ;;
    h)
      DB1SERVER="$OPTARG"
      ;;
    D)
      DBNAME="$OPTARG"
      ;;
    P)
      DBACCTPWD=""
      ;;
    \?)
      echo "Invalid option: -$OPTARG" >&2
      ;;
  esac
done


CMD="${MYSQL} -u ${DBACCT} -h ${DB1SERVER} -D ${DBNAME} -p${DBACCTPWD}"


mysql -u $DBACCT -p"$DBACCTPWD" -h $DB1SERVER $DBNAME <<-THIS_IS_THE_END

  DELIMITER //
		
	-- sproc to create new windows oscategory/ostype combos
	drop procedure if exists define_rated_category
	//
	create procedure define_rated_category(cat_id int, cat_name varchar(255))
	begin
		insert into cloud.guest_os_category (id, name, uuid) values (cat_id, cat_name, uuid());
		-- TODO: CS doesn't define windows server 2012, so we collapse everything into '2008'..? 
		insert into cloud.guest_os (category_id, display_name, uuid) values (cat_id, "Windows Server 2008 (64-bit)", uuid());
	end;
	//
	
	
	-- main sproc to update db. 
	drop procedure if exists update_database
	//
	create procedure update_database()
	begin
		declare category_id INT;
	
		select "Updating database..." as Status;
	
		start transaction;
			-- create categories and types for rated os combos, using a CS recognized guest type, and a BRM recognized OS category
			select "Defining categories.." as Status;
			call define_rated_category(12, "Windows Server 2008 (licensed)");
			call define_rated_category(13, "Windows Server 2008+MSSQL 2008 Web (licensed)");
			call define_rated_category(14, "Windows Server 2008+MSSQL 2008 Standard(licensed)");
			call define_rated_category(15, "Windows Server 2008+MSSQL 2012 Web (licensed)");
			call define_rated_category(16, "Windows Server 2008+MSSQL 2012 Standard (licensed)");
			call define_rated_category(17, "Windows Server 2012 Standard (licensed)");
			call define_rated_category(18, "Windows Server 2012+MSSQL 2012 Web (licensed)");
			call define_rated_category(19, "Windows Server 2012+MSSQL 2012 Standard (licensed)");
		
			-- update existing windows server 2008 OS with BRM recognized OS category
			select "Updating Windows types..";
			select id into @category_id from cloud.guest_os_category where name = 'Windows Server 2008 (licensed)';
			update cloud.guest_os set category_id = @category_id where name = 'Windows Server 2008 (32-bit)';
			update cloud.guest_os set category_id = @category_id where name = 'Windows Server 2008 (64-bit)';
			update cloud.guest_os set category_id = @category_id where name = 'Windows Server 2008 R2 (64-bit)';
		commit;
	end;
	//
	
	DELIMITER ;
	

	call update_database();
	
	drop procedure check_db_state;
	drop procedure define_rated_category;
	drop procedure update_database;
	
THIS_IS_THE_END