OXIESEC PANEL
- Current Dir:
/
/
home
/
cubot
/
conf
/
dbupgrade_tool
Server IP: 139.59.38.164
Upload:
Create Dir:
Name
Size
Modified
Perms
📁
..
-
02/03/2023 08:07:15 AM
rwxr-xr-x
📄
cubottable.sh
7.35 KB
02/03/2023 07:34:22 AM
rwxr-xr-x
📄
data.sql
26.07 KB
02/03/2023 07:34:22 AM
rwxr-xr-x
📄
upgrade_demo_tkml_20160418.sql
56.15 KB
02/03/2023 07:34:22 AM
rw-r--r--
📄
upgrade_tkml_20160418.sql
25.29 KB
02/03/2023 07:34:22 AM
rwxr-xr-x
📄
writesql.awk
2.73 KB
02/03/2023 07:34:22 AM
rwxr-xr-x
Editing: cubottable.sh
Close
#!/bin/bash # Ussage: cubottable.sh REFERRENCE_DATABASE_NAME CUSTOMER_DATABASE_NAME # Desc: This script will produce a sql file($UPGRADE_SQL_FILE), which will have sql queries to upgrade the customer database to current database version. # In this script we will compare the table structure of sampledb with customer database, and genrate some sql to make customer database updated one. # Author:Arun Kumar Swain # Team CUBOT. # Robust Designs (India) Pvt Ltd. # Bangalore. REFERENCE_DB="$1" CUSTOMER_DB="$2" CUBOT_HOME="/home/cubot"; UPGRADE_SQL_FILE="upgrade_${CUSTOMER_DB}_`date +%Y%m%d`.sql"; if [ "$REFERENCE_DB" == "" ]; then echo "Please supply reference database name." echo "Usage: sh $0 REFERRENCE_DATABASE_NAME CUSTOMER_DATABASE_NAME" echo "Example: sh $0 sampledb demo_mvas" exit 1; fi if [ "$REFERENCE_DB" == "" ]; then echo "Please supply reference database name." echo "Usage: sh $0 REFERRENCE_DATABASE_NAME CUSTOMER_DATABASE_NAME" echo "Example: sh $0 sampledb demo_mvas" exit 1; fi exec >$UPGRADE_SQL_FILE exec 1>>$UPGRADE_SQL_FILE ################################ Array to store table structure Start ################################################ declare -a Field_Main_Arr=(); declare -a Type_Main_Arr=(); declare -a Null_Main_Arr=(); declare -a Key_Main_Arr=(); declare -a Default_Main_Arr=(); declare -a Extra_Main_Arr=(); declare -a Field_Cust_Arr=(); declare -a Type_Cust_Arr=(); declare -a Null_Cust_Arr=(); declare -a Key_Cust_Arr=(); declare -a Default_Cust_Arr=(); declare -a Extra_Cust_Arr=(); declare -i num_main_col=0; declare -i num_cust_col=0; ################################Arra to store table structure End ################################################ TMPFILE="/tmp/tmp.out"; ################################################# Library Functions Start ################################################ fillarray(){ DB=$1 TABLE=$2 mysql $DB -N -e "desc $TABLE" | tr '\t' '|'>$TMPFILE if [ "$DB" == "$REFERENCE_DB" ]; then while read line do Field_Main_Arr[$num_main_col]=`echo $line|awk -F '|' '{print $1}'` Type_Main_Arr[$num_main_col]=`echo $line|awk -F '|' '{print $2}'` Null_Main_Arr[$num_main_col]=`echo $line|awk -F '|' '{print $3}'` Key_Main_Arr[$num_main_col]=`echo $line|awk -F '|' '{print $4}'` Default_Main_Arr[$num_main_col]=`echo $line|awk -F '|' '{print $5}'` Extra_Main_Arr[$num_main_col]=`echo $line|awk -F '|' '{print $6}'` num_main_col=`expr $num_main_col + 1`; done <$TMPFILE else while read line do Field_Cust_Arr[$num_cust_col]=`echo $line|awk -F '|' '{print $1}'` Type_Cust_Arr[$num_cust_col]=`echo $line|awk -F '|' '{print $2}'` Null_Cust_Arr[$num_cust_col]=`echo $line|awk -F '|' '{print $3}'` Key_Cust_Arr[$num_cust_col]=`echo $line|awk -F '|' '{print $4}'` Default_Cust_Arr[$num_cust_col]=`echo $line|awk -F '|' '{print $5}'` Extra_Cust_Arr[$num_cust_col]=`echo $line|awk -F '|' '{print $6}'` num_cust_col=`expr $num_cust_col + 1`; done <$TMPFILE fi } CompareRows(){ MCN=$1; #mainColNo RCN=$2; #refColNum OPT=$3; #OPTION index=""; if [ "$OPT" == "-1" -o "$OPT" == "-2" ]; then printArrLine $MCN $RCN if [ "${Null_Main_Arr[$MCN]}" == "NO" ]; then isnull="NOT NULL"; else isnull=""; fi if [ "${Default_Main_Arr[$MCN]}" != "NULL" -a "${Default_Main_Arr[$MCN]}" != "" ] ; then default="DEFAULT '${Default_Main_Arr[$MCN]}'" else default=""; fi if [ "${Extra_Main_Arr[$MCN]}" == "auto_increment" ]; then autoinc="AUTO_INCREMENT"; else autoinc=""; fi if [ "${Key_Main_Arr[$MCN]}" == "PRI" ]; then key="PRIMARY KEY"; elif [ "${Key_Main_Arr[$MCN]}" == "UNI" ]; then key="UNIQUE KEY"; elif [ "${Key_Main_Arr[$MCN]}" == "MUL" ]; then index=1; key=""; else key=""; fi fi if [ "$OPT" == "-1" ]; then echo "alter table $table add column ${Field_Main_Arr[$MCN]} ${Type_Main_Arr[$MCN]} $key $isnull $default $autoinc ;" ; elif [ "$OPT" == "-2" ]; then echo "alter table $table change column ${Field_Main_Arr[$MCN]} ${Field_Main_Arr[$MCN]} ${Type_Main_Arr[$MCN]} $key $isnull $default $autoinc ;" ; fi if [ "$index" == "1" ]; then echo "alter table $table add index (${Field_Main_Arr[$MCN]});" ; fi } getExtraCols(){ for((j=0; j<${#Field_Cust_Arr[@]}; j++));do got="-1"; for((i=0; i<${#Field_Main_Arr[@]}; i++));do if [ "${Field_Cust_Arr[$j]}" == "${Field_Main_Arr[$i]}" ];then got=1; break; fi done if [ "$got" != "1" ]; then echo "alter table $table drop column ${Field_Cust_Arr[$j]};" ; fi done } CompareTable(){ for((i=0; i<${#Field_Main_Arr[@]}; i++));do refColNo="-1"; for((j=0; j<${#Field_Cust_Arr[@]}; j++));do if [ "${Field_Main_Arr[$i]}" == "${Field_Cust_Arr[$j]}" ];then if [ "${Type_Main_Arr[$i]}" != "${Type_Cust_Arr[$j]}" ];then CompareRows $i $j -2 # -2 to change column elif [ "${Key_Main_Arr[$i]}" != "NULL" -a "${Key_Main_Arr[$i]}" != "${Key_Cust_Arr[$j]}" ];then CompareRows $i $j -2 # -2 to change column elif [ "${Default_Main_Arr[$i]}" != "NULL" -a "${Default_Main_Arr[$i]}" != "${Default_Cust_Arr[$j]}" ];then CompareRows $i $j -2 # -2 to change column fi refColNo=$j; break; fi done CompareRows $i $j $refColNo done } printArrLine(){ echo ""; echo "-- Converting [ ${Field_Cust_Arr[$2]} ${Type_Cust_Arr[$2]} ${Null_Cust_Arr[$2]} ${Key_Cust_Arr[$2]} ${Default_Cust_Arr[$2]} ${Extra_Cust_Arr[$2]} ] ==> [ ${Field_Main_Arr[$1]} ${Type_Main_Arr[$1]} ${Null_Main_Arr[$1]} ${Key_Main_Arr[$1]} ${Default_Main_Arr[$1]} ${Extra_Main_Arr[$1]} ]"; } printArr(){ echo "---------------------Main DB $table------------------------"; for((i=0; i<${#Field_Main_Arr[@]}; i++));do echo "${Field_Main_Arr[$i]}|${Type_Main_Arr[$i]}|${Null_Main_Arr[$i]}|${Key_Main_Arr[$i]}|${Default_Main_Arr[$i]}|${Extra_Main_Arr[$i]}"; done echo "---------------------Reference DB $table------------------------"; for((i=0; i<${#Field_Cust_Arr[@]}; i++));do echo "${Field_Cust_Arr[$i]}|${Type_Cust_Arr[$i]}|${Null_Cust_Arr[$i]}|${Key_Cust_Arr[$i]}|${Default_Cust_Arr[$i]}|${Extra_Cust_Arr[$i]}"; done } resetCounts(){ num_main_col=0; num_cust_col=0; unset Field_Main_Arr; unset Type_Main_Arr; unset Null_Main_Arr; unset Key_Main_Arr; unset Default_Main_Arr; unset Extra_Main_Arr; unset Field_Cust_Arr; unset Type_Cust_Arr; unset Null_Cust_Arr; unset Key_Cust_Arr; unset Default_Cust_Arr; unset Extra_Cust_Arr; } ################################################# Library Functions End ################################################ ################################################# Main area start ################################################ cubot_tables_array=( `${CUBOT_HOME}/bin/cubmenuserv -t | sort | tr '\n' ' '`) for table in ${cubot_tables_array[@]} do echo "-- **************************************** FOR TABLE :$table **************************************** --"; fillarray $REFERENCE_DB $table fillarray $CUSTOMER_DB $table if [ ${#Field_Cust_Arr[@]} -gt 0 ]; then CompareTable getExtraCols #printArr resetCounts else echo "create table $table like ${REFERENCE_DB}.${table} ;"; fi echo ""; echo ""; done echo "-- Now Putting Static Data --" echo "" mysqldump $REFERENCE_DB install_params ctrl_btn_info rd_codes prefs_values prefs_info edash_svcs cubot_help cuberrors time|grep -v "^--"|grep -v "^/\*\!"|grep -v "^LOCK"|grep -v "^UNLOCK" exit 0; ################################################# Main area end ################################################