LNAMP Shell 部署腳本
學習總結：
這個腳本，早期是出于對個人學習Shell的總結而寫，應該有些年頭了，目前也在一邊學馬哥視頻的基礎上陸續完善，10月初才完成LNAMP環境的分離式部署，并減少整個Shell腳本各部分的依賴關系。
我是網絡班13期高級班的學員，因個人做了幾年Linux運維，所以目前整個高級班的課程，我是跳著看了集群（LVS + Keepalived）、自動化（Cobbler、Ansible）、監控（Zabbix）、分布式存儲（Mogilefs和Glusterfs），系統調優以及Openstack的部分。因目前這幾個是我現在這個公司短期內要用的，也是目前我主要關心的幾個東西。
7月報的班，8月份崗位變動（被人坑了一把，現在兼了linux 運維的工作，應該也是一種機會吧，可以讓我直接把學習的東西，直接用在線上環境上）。
8月底到現在，一直都折騰在之前那個人交接的環境上了（環境相當混亂）。因此，一邊看視頻，一邊就直接用在線上環境了。
? 部署了Cobbler的內部源（涉及了CentOS、RedHat、Zabbix、Ubuntu）；
? 研究了ansible + zabbix，參考了 galaxy上的腳本，在線上部署了zabbix環境；
? 修改了早期的LNAMP腳本，調整為可分離式部署，并集成了目前公司在用的個人所寫的Git SCM運維方面的腳本。
本來也考慮把Git環境也整進去的，但想想還是算了，折騰不起了，把費腦子的事情還是發在學習上去……
轉入主題，Shell腳本的入門是很簡單的，但要學好是一個挺長時間的事情的，別把Shell想得太簡單，基本的運維腳本，只要是一個會點Linux知識，干過這行的，隨便都可以寫的出來。一個會寫代碼，有思想的運維是很可怕的，這也是你的一個競爭力的亮點。
請問以下功能性需求，如果要使用Shell編程，需要界面UI，如何實現？
假設，線上生產環境的一個系統，因早期的開發設計，導致業務層面有很大的BUG問題（比如說，構成數據庫唯一鍵的部分主鍵沖突），導致整個業務系統，在數據修改、查詢操作時，出現數據不一致，沖突性地問題。因此，想用Shell腳本來實現以下功能：
? 數據庫的修改（需要考慮沖突，界面UI顯示，及修正沖突等等）
? 數據庫的刪除
? 數據庫的備份（提供記錄級別、表級別、數據庫級別，可以選擇性地備份）
? 數據庫的還原（表級別，沒有數據時實現 insert操作，有時實現update操作）
這個需求是我真實碰到的，請問要實現以上功能，需要使用哪些工具？此外，需要保證整個腳本工作地準確性、可靠性及輸出的友好性，并且腳本需要盡量避免重復代碼。
這篇Blog 分享出來的腳本，我個人認為還是有很大的參考價值的。因他算是我一直以為對學習Shell腳本的一個很好地總結。腳本中，可能會存在部分代碼裝B的嫌疑，因有些很簡單能實現的代碼，我可能會有點稍微復雜點的方式來寫，主要是怕自己以后不用會忘了。實際上你懂得~~~
功能：
代碼說明：
代碼使用git 方式管理，代碼量（15000+ 行），包括了：
? LNAMP環境部署腳本（多套環境，PHP-FPM）
? 基礎的運維腳本
? Git運維腳本（命令的方式）
腳本代碼主要涉及的命令工具：
? awk、sed：這兩個不用說了，都是編程語言，shell 進階編程必須的
? dialog：文本模式下，圖形化 UI 設計必須的
? tput：也是一個交互性的、控制屏幕輸出的工具
腳本演示安裝：
使用了 script 命令錄制了，整個腳本的安裝過程。
在代碼包的根目錄下有個“demo”的腳本（基于scriptreplay），運行“./demo”即可演示相關安裝過程。
本腳本還有很多不完善的地方，另外在分離部署中只對基礎的 LNAMP 環境進行測試。僅供學習參考。
下載：百度網盤，鏈接: http://pan.baidu.com/s/1bnt3uLT，密碼: c732。
整個程序分為代碼包（30M）和源碼包（363M）。
解壓運行過程（以 root 執行）：
# tar -xvf mallux_nlnamp.tar
# tar -xvf m6-lnamp.tar
# cd lnamp/
# ./pwdHost
使用范圍：CentOS 6.x
使用腳本前，本地基于 Cobbler 完成部署的測試 kickstart 文件。
#platform=x86, AMD64, or Intel EM64T
#version=DEVEL
# Firewall configuration
firewall --enabled --ssh
# Install OS instead of upgrade
install
# Use network installation media
url --url=http://192.168.96.98:88/cblr/links/CentOS-6.5-x86_64
# Root password
rootpw --iscrypted $1$GoXayw5C$TlwEAmfGfXIwTeZgseAel0
# System authorization information
auth --useshadow --passalgo=sha512
# Use text mode install
text
# System keyboard
keyboard us
# System language
lang en_US
# SELinux configuration
selinux --permissive
# Do not configure the X Window System
skipx
# Installation logging level
logging --level=info
# Reboot after installation
reboot
# System timezone
timezone --isUtc Asia/Shanghai
# Network information
# Using "old" style networking config. Make sure all MAC-addresses are in cobbler to use the new-style config
network --bootproto=dhcp --device=eth0 --onboot=on
# System bootloader configuration
bootloader --location=mbr --md5pass='$1$8.oYW1$qsN3sdvzytZE0VT/TUph01'
%include /tmp/partitioning
%pre
if grep -q hda$ /proc/partitions
then
HD1st=hda
else
HD1st=sda
fi
echo "bootloader --driveorder=${HD1st}" > /tmp/partitioning
cat >> /tmp/partitioning <<EOF
zerombr yes
part /boot --asprimary --fstype ext4 --ondisk=${HD1st} --size=200
part pv.01 --fstype ext4 --ondisk=${HD1st} --grow --size=1
volgroup mainVG pv.01
logvol swap --vgname=mainVG --fstype swap --size=1024 --name=lv_swap
logvol / --vgname=mainVG --fstype ext4 --size=10000 --name=lv_root
EOF
if [ `grep sd.$ /proc/partitions | wc -l` -gt 1 ]
then
HD2nd=`grep sd.$ /proc/partitions | grep -v $HD1st | awk 'NR==1 {print $NF}'`
cat >> /tmp/partitioning <<-EOF
clearpart --drives=${HD1st},${HD2nd} --all --initlabel
part pv.02 --fstype ext4 --ondisk=${HD2nd} --grow --size=1
volgroup 2ndVG pv.02
logvol /DBHome --vgname=mainVG --fstype ext4 --grow --size=1 --name=lv_DBHome
logvol /home --vgname=2ndVG --fstype ext4 --grow --size=1 --name=lv_home
EOF
else
cat >> /tmp/partitioning <<-EOF
clearpart --drives=${HD1st} --all --initlabel
logvol /home --vgname=mainVG --fstype ext4 --grow --size=1 --name=lv_home
EOF
fi
%packages
@additional-devel
@base
@chinese-support
@development
@hardware-monitoring
@legacy-unix
@network-file-system-client
@network-tools
@performance
@server-platform
@system-admin-tools
@system-management-snmp
cmake
expect
git
ipset
iptraf
iptstate
dstat
lm_sensors
nmap
screen
ftp
telnet
tree
-mysql
-mysql-devel
-mysql-lib
%post
for (( i=0;i<=2;i++ ))
do
ntpdate -b 218.30.114.84 1>>/root/sync_time.log 2>&1
[ $? -eq 0 ] && break
sleep 5
done
hwclock --systohc --utc
Mlux="lnamp_system_v2.0.tar"
wget http://192.168.96.98:88/cblr/links/CentOS-6.5-x86_64/lnamp/$Mlux /root
sed -i '/keepcache/ s;\([[:alpha:]=]*\).*;\11;' /etc/yum.conf
#sed -i '/^#Port 22/aPort 10022' /etc/ssh/sshd_config
#sed -i '/22 / s/22/10022/' /etc/sysconfig/iptables
sed -i "7a alias vi='vim'" /root/.bashrc
sed -i "8a alias ll='ls -al'" /root/.bashrc
sed -i "9a alias grep='grep --color=auto'" /root/.bashrc
sed -i 's;\(^ \{1,\}\)\(\[ "$PS1".*\);\1#\2;' /etc/bashrc
sed -i '/^ \{1,\}#\[ "$PS1" = "\\/a \ [ "$PS1" = "\\\\s-\\\\v\\\\\\$ " ] && PS1="[\\[\\e[0;32;1m\\]\\u\\[\\e[0m\\]@\\[\\e[0;36;1m\\]\\h\\[\\e[0m\\] \\[\\e[0;33;1m\\]\\W\\[\\e[0m\\]]\\\\$ "' /etc/bashrc
grep -q 'md5pass' /boot/grub/grub.conf
if [ $? -gt 0 ]
then
sed -i '/^timeout/a password --md5 $1$8.oYW1$qsN3sdvzytZE0VT/TUph01' /boot/grub/grub.conf
fi
if [ -f '/usr/bin/git' ]
then
cat >>/etc/profile.d/git.sh<<-EOF
# bash completion support for core Git.
if [ -f /etc/bash_completion.d/git ]
then
source /etc/bash_completion.d/git
fi
EOF
fi
[ ! -e "/root/.vimrc" ] && {
cat >>/root/.vimrc<<-EOF
set pastetoggle=<F9>
set nobackup
set noswapfile
set hlsearch
set nonumber
set cindent
set autoindent
set shiftwidth=4
set tabstop=4
set expandtab
set softtabstop=4
set laststatus=2
set ruler
set backspace=indent,eol,start
syntax on
EOF
}
NIC="/etc/sysconfig/network-scripts"
if [ -e "$NIC/ifcfg-em1" ]
then
sed -i "/kernel.*quiet/ s/$/& biosdevname=0/" /boot/grub/grub.conf
rm -rf /etc/udev/rules.d/70-persistent-net.rules
for i in `seq 1 7`
do
emcfg="ifcfg-em$i"
ethcfg="ifcfg-eth$[i-1]"
if [ -e "$NIC/$emcfg" ]
then
mv $NIC/$emcfg $NIC/$ethcfg
sed -i "s/em$i/eth$[i-1]/" $NIC/$ethcfg
fi
done
fi
eject
%end
目錄規劃：
[root@localhost lnamp]# tree -L 1
.
├── apex.fw ### 目錄：存放運維腳本
├── archives ### 目錄：git 維護腳本
├── bin ### 目錄：存放dialog、tput 等命令
├── conf ### 目錄：lnamp 環境部分配置文件
├── etc ### 目錄：系統環境腳本、配置之類的
├── fonts ### 目錄：Cacti 所需的字體
├── gitclean ### 腳本：git reflog / gc 清理動作
├── gitkeep ### 腳本：空目錄下創建.gitkeep文件，防止 git 不提交空目錄
├── install ### 腳本：主腳本
├── iptables.recent ### 腳本：iptables recent 模塊，防火墻策略相關
├── luxgz ### 目錄：lnamp 源碼包
├── luxkey ### 目錄：ssh 用戶 key
├── packages ### 目錄：lnamp 源碼包解壓后的路徑
├── pwdHost ### 腳本：linux、mysql用戶密碼、網絡設置
├── README.md ### git 說明性文檔
├── RPMs ### 目錄：只放了一個YUM epel6 的 RPM 包
├── scripts ### 目錄：lnamp 環境安裝調用，所有腳本存放位置
├── sql ### 數據庫目錄：cacti監控系統、system web管理后臺
├── timeset ### 腳本：tput控制屏幕輸出
├── tmp ### 目錄：臨時輸出文檔，工作目錄
├── updates ### 目錄：存放Yum 自定義的本地源壓縮檔
└── version ### 腳本：用來修改腳本的作者、E-Mail等信息
14 directories, 8 files
腳本執行流程：“pwdHost”->“install”
首次運行時，請運行“pwdHost”腳本，這個腳本會詢問你一些相關設置，并在tmp下生成一些臨時文件，腳本最后會調用“install”腳本進入部署lnamp環境的主界面
“pwdHost”腳本會做如下設置：
? Linux、MariaDB數據庫的root密碼
? 本地新安裝或遠程數據庫連接設置
? 本地網絡設置
相關界面：
圖1 pwdHost UI
當運行過 pwdHost腳本后，后續就可直接執行“./install”調用程序主腳本了。如果需要重新修改設置，可重新運行pwdHost腳本。
運行“install腳本”時，會顯示當前系統的時間（timeset腳本控制）。
如果時間不正確，按下一次“ctrl+c”時，進行設置（如圖2）。否則可再次按下“ctrl+c”進入下一個安裝環節（如圖3）。
圖2 timeset 腳本輸出
圖3 選擇應用類型
說明：圖3這個界面，是以前殘留下來的。因我之前的公司是用PHP開發的，auto4s和dprp5s為兩個業務系統的普通賬號。這個在后續相關UI的選擇操作時，除了會在系統上創建相關用戶、拷貝用戶密鑰（luxkey目錄下）以外，還會在部署lnamp環境時，根據這個選擇，去修改模板配置文件，生成apache所需的業務系統配置文件。Git 用戶同理，但 git 用戶會多一個操作，解壓“archives”下的 git 運維腳本“repo_sync.tgz”到 git 用戶下。
圖3選擇后，會進入到一個詢問你是否要更改“root”用戶密碼的UI。確認后，正式進入部署lnamp主界面（如圖4）。
圖4 lnamp 主程界界面
這個不細說的，看著界面上，按相關鍵進行UI操作進行了，要提的幾點如下：
Lnamp環境部署上，如圖5所示，每個源碼編譯的包，都提供了輸出編譯參數（Debug），編譯后可訪問的地址（Available URL）、正式編譯安裝（Install）等選項。
圖5 nginx 安裝界面
數據庫提供了mysql（5.1）和mariadb（5.1和5.5），編譯時選擇字符校對集時，需要注意一下。一般情況下，選擇“utf-8”即可。但是，我們線上的 git環境，申核代碼這塊用了谷歌的gerrit，測試中發現2.5和2.7版本，gerrit無法在數據庫編譯時指定“utf-8”下工作，gerrit web UI上會有些操作會報錯，即使手工創建修改指定指定字符校對集也不行，不知道怎么回事，所以就引入了默認“latin1”字符校對集的編譯一項。
整個 LNAMP 環境部署完畢以后，相關可用的鏈接地址，就不貼了。相關編譯過程中，都有界面可查看。
LNAMP 環境部署，所需的相關腳本（圖6），環境都是基于源碼編譯。
圖6 LNAMP 部署腳本
文件說明：
mlux.pwd：密碼定義文件，其它一些數據庫、普通用戶、web 登陸用戶密碼設置。
default.pwd：默認linux和mysql的root用戶密碼，在沒有運行“pwdHost”腳本，直接調用“install”腳本時，默認將使用該文件中的root用戶默認密碼。否則，使用pwdHost腳本設置的密碼。
其它文件，_memu 結尾的為 UI上的界面部分，其它 _ins 等都是程序用到的腳本定義部分。這些腳本，都被根目錄下的“install”腳本引用了。
源碼包（如圖7），基本全是官方下載的，不過我有強迫怔。在本地解壓后，更改屬組用戶為 root 后，重新打包了，-_##!
圖7 源碼包
源碼包說明：
php-5.2.17.tar.gz：集成了 php-fpm 補丁。
最后，附上文章開始提到的那個數據庫需求的腳本，因涉及到數據庫，就只貼代碼了，僅供參考。腳本中大量應用了 dialog、sed 和 awk部分，應該算是 dialog 學習的一個好腳本。
#!/usr/bin/env bash
### --------------------------------------------------
### Filename: imgen_update
### Revision: latest stable
### Author: Mallux
### E-mail: gbmagic@aliyun.com
### Blog: blog.mallux.org
### Description: Update IMGEN System Product Information
### --------------------------------------------------
### Copyright ? 2014-2015 Mallux
#------------
# Exit Operation
#------
trap "__clean_up" EXIT
__clean_up() {
exec 6>&-
rm -rf $tmp_dir
}
#------------
# Shell - Global Variable and Function Settings
#------
Author="Mallux"
Email="gbmagic@aliyun.com"
pid=$$
tmp_dir="/tmp/imgen_tools/$pid" ; mkdir -p $tmp_dir ; chmod 1777 ${tmp_dir%/*} &> /dev/null
tmp_fifo="$tmp_dir/__$pid.fifo"
mysql_dump="mysqldump"
mysql_bin="mysql"
### Backup level and Operation Table
backup_level=( db_level table_level record_level )
imgen_table=( imgen_product imgen_ref imgen_tac )
backup_dir="./0-bak"
db_level_dir="$backup_dir/db_level"
table_level_dir="$backup_dir/table_level"
### Call mysql Command Function
__d_mysql_do_bin_cmd() {
$mysql_bin -h localhost -u$dbuser -p$dbpass "$@"
[ $? -eq 0 ] && return 0 || return 1
}
### Call mysqldump Command Function
__d_mysql_do_dump_cmd() {
args_1st=$1 ; shift
if [ x"$args_1st" == x"db_level" ]
then
$mysql_dump --opt --master-data=2 --skip-add-drop-table -u$dbuser -p$dbpass "$@"
elif [ x"$args_1st" == x"table_level" ]
then
$mysql_dump --opt --master-data=2 --skip-add-drop-table -c --skip-extended-insert -u$dbuser -p$dbpass "$@"
fi
[ $? -eq 0 ] && return 0 || return 1
}
#------------
# Dialog Unit UI Design
#------
__d_dialog_design_UI() {
mkfifo $tmp_fifo
exec 6<>$tmp_fifo
arch=`arch`
if [ x"$arch" == x"i686" ]
then
dgcmd=`pwd`/bin/dialog32
elif [ x"$arch" == x"x86_64" ]
then
dgcmd=`pwd`/bin/dialog64
else
dgcmd=`which dialog`
fi
[ ! -f "$dgcmd" -o ! -x "$dgcmd" ] && {
echo -e "\e[33;1mFatal:\e[0m The \"dialog\" command not found or does not have execute permission.\n"
exit 1
}
title="IMGEN System Product Update Author: $Author E-mail: $Email"
### Dialog msgbox Unit Function
__d_dialog_design_msgbox_UI() {
unset exit_state
ok_label=$1 ; height=$2 ; width=$3 ; shift 3
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--ok-label " $ok_label " \
--msgbox "$MSG" $height $width
return $?
}
### Dialog yesno Unit Function
__d_dialog_design_yesno_UI() {
unset exit_state
yes_label=$1 ; no_label=$2 ; height=$3 ; width=$4 ; shift 4
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--defaultno \
--yes-label " $yes_label " \
--no-label " $no_label " \
--yesno "$MSG" $height $width
return $?
}
### Dialog password Unit Function
__d_dialog_design_password_UI() {
unset exit_state
ok_label=$1 ; cancel_label=$2 ; height=$3 ; width=$4 ; shift 4
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--ok-label " $ok_label " \
--cancel-label " $cancel_label " \
--passwordbox "$MSG" $height $width \
--output-fd 6
return $?
}
### Dialog inputbox Unit Function
__d_dialog_design_inputbox_UI() {
unset exit_state
ok_label=$1 ;cancel_label=$2 ; height=$3 ; width=$4 ; shift 4
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--ok-label " $ok_label " \
--cancel-label " $cancel_label " \
--inputbox "$MSG" $height $width \
--output-fd 6
return $?
}
### Dialog textbox Unit Function
__d_dialog_design_textbox_UI() {
unset exit_state
file=$1
exit_label=$2 ; height=$3 ; width=$4 ; shift 4
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--exit-label " $exit_label " \
--textbox $file $height $width
return $?
}
### Dialog menu Unit Function
__d_dialog_design_menu_UI() {
unset exit_state
ok_label=$1 ; cancel_label=$2 ; extra_label=$3 ; height=$4 ; width=$5 ; list_height=$6 ; shift 6
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--ok-label " $ok_label " \
--cancel-label " $cancel_label " \
--extra-button \
--extra-label " $extra_label " \
--menu "$MSG" $height $width $list_height \
${list_items[*]} \
--output-fd 6
return $?
}
### Dialog radiolist Unit Function
__d_dialog_design_radiolist_UI() {
unset exit_state
ok_label=$1 ; cancel_label=$2 ; extra_label=$3 ; height=$4 ; width=$5 ; list_height=$6 ; shift 6
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--visit-items \
--ok-label " $ok_label " \
--cancel-label " $cancel_label " \
--extra-button \
--extra-label " $extra_label " \
--radiolist "$MSG" $height $width $list_height \
${list_items[*]} \
--output-fd 6
return $?
}
### Dialog checklist Unit Function
__d_dialog_design_checklist_UI() {
unset exit_state
select_item=$1
ok_label=$2 ; cancel_label=$3 ; extra_label=$4 ; height=$5 ; width=$6 ; list_height=$7 ; shift 7
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--separate-output \
--single-quoted \
--visit-items \
--ok-label " $ok_label " \
--cancel-label " $cancel_label " \
--extra-button \
--extra-label " $extra_label " \
--checklist "$MSG" $height $width $list_height \
${list_items[*]} \
2>$select_item
return $?
}
### Dialog form Unit Function
__d_dialog_design_form_UI() {
### The EOF section include leading TAB characters ( ctrl+v and Tab ), and end with a Space characters.
cat > $dload_form <<-EOF
unset exit_state
$dgcmd --clear \
--backtitle "$title" \
--title "| $sub_title |" \
--colors \
--ok-label " $ok_label " \
--cancel-label " $cancel_label " \
--extra-button \
--extra-label " $extra_label " \
--form "$MSG" $height $width $form_height \
${form_items[*]} \
2>$field_form
EOF
}
}
#------------
# Imgen System Product Database Operation
#------
__o_db_operation_UI() {
### Enter Database Name Function
__o_db_operation_input_dbname_UI() {
sub_title="Welcome to `whoami`"
MSG="\nEnter \Z1Database\Zn name for connect ( Default: \Z1IMGEN\Zn )"
### Call Dialog inputbox UI
__d_dialog_design_inputbox_UI Next Quit 10 70
exit_state=$? ; echo -e "\nexit" >&6
read -u6 dbname ; read -u6 EXIT
### Quit Button
if [ x"$exit_state" == x"1" ]
then
exit 1
### Next Button
elif [ x"$exit_state" == x"0" ]
then
dbname=${dbname:-IMGEN}
__o_db_operation_input_dbuser_UI
fi
}
### Enter Database User Function
__o_db_operation_input_dbuser_UI() {
MSG="\nEnter Database \Z1User\Zn for Login ( Default: \Z1root\Zn )"
### Call Dialog inputbox UI
__d_dialog_design_inputbox_UI Next Previous 10 70
exit_state=$? ; echo -e "\nexit" >&6
read -u6 dbuser ; read -u6 EXIT
### Previous Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_connect_UI
### Next Button
elif [ x"$exit_state" == x"0" ]
then
dbuser=${dbuser:-root}
__o_db_operation_input_dbpass_UI
fi
}
### Enter Database Password Function
__o_db_operation_input_dbpass_UI() {
MSG="\nEnter Database \Z1$dbuser\Zn Password ( Default: \Z1NULL\Zn )"
### Call Dialog password UI
__d_dialog_design_password_UI Next Previous 10 70
exit_state=$? ; echo -e "\nexit" >&6
read -u6 dbpass ; read -u6 EXIT
### Previous Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_input_dbuser_UI
### Next Button
elif [ x"$exit_state" == x"0" ]
then
dbpass=${dbpass:-sqlpass}
fi
}
### Test Database Connection Function
__o_db_operation_connect_UI() {
__o_db_operation_input_dbname_UI
### Call mysql Commamd, test Database connectivity
__d_mysql_do_bin_cmd $dbname -e quit 2>/dev/null
[ $? -eq 0 ] && dbcon_state="success" || dbcon_state="failure"
if [ x"$dbcon_state" == x"success" ]
then
__o_db_operation_main_menu_UI
else
sub_title="$dbname - Database Connection" ; MSG="\nIncorrect configure, Unable to Connect Database."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_connect_UI
fi
}
### Database Operation Menu Type Function
__o_db_operation_menu_type_UI() {
menu_type=$1 ; ok_label=$2 ; cancel_label=$3 ; extra_label=$4 ; shift 4
case $menu_type in
menu)
list_height=${#list_items[*]}
### Call Dialog menu UI
__d_dialog_design_menu_UI $ok_label $cancel_label $extra_label $[list_height+9] 70 $list_height
;;
radiolist)
list_items=( `echo ${list_items[*]} | xargs -n2 | awk '{print $1,$2,"off"}'` )
list_height=`echo ${list_items[*]} | xargs -n3 | wc -l`
### Call Dialog radiolist UI
__d_dialog_design_radiolist_UI $ok_label $cancel_label $extra_label $[list_height+9] 70 $list_height
;;
esac
return $?
}
### Database Operation Main Menu Function
__o_db_operation_main_menu_UI() {
unset list_items ; declare -a list_items
list_items=( "Backup Operation"
"Up-Del Operation"
"Restore Operation" )
list_height=${#list_items[*]}
sub_title="$dbname - Database Operation" ; width=70
MSG="- Select Database (\Z1 $dbname \Zn) Operation -"
MSG=`echo $MSG | sed ':a s/^.\{1,'"$width"'\}$/ & /;ta'`
MSG="\n$MSG"
### Choice Menu Type, menu or radiolist
__o_db_operation_menu_type_UI radiolist Select Re-connect Quit
exit_state=$? ; echo -e "\nexit" >&6
read -u6 operation ; read -u6 EXIT
### Re-connect Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_connect_UI
### Quit Button
elif [ x"$exit_state" == x"3" ]
then
exit 1
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ -z "$operation" ] && __o_db_operation_main_menu_UI
case "$operation" in
Backup)
__o_db_operation_input_tac_id_UI backup
;;
Up-Del)
__o_db_operation_input_tac_id_UI updel
;;
Restore)
__o_db_operation_restore_UI
;;
esac
fi
}
### Enter Product TAC_ID Function
__o_db_operation_input_tac_id_UI() {
unset product_id tac_id tac_fac tac_ref ref_id product_id_array tac_ref_array tac_id_array
unset operation ; operation=$1 ; shift
if [ x"$operation" == x"backup" ]
then
sub_title="$dbname - Backup Operation"
MSG="\nEnter \Z1TAC_ID(s)\Zn to Backup ( Separate them By \Z1comma\Zn )"
elif [ x"$operation" == x"updel" ]
then
sub_title="$dbname - Retrieve Product"
MSG="\nEnter \Z1TAC_ID\Zn to Retrieve Product ( Default: \Z1NULL\Zn )"
fi
### Call Dialog inputbox UI
__d_dialog_design_inputbox_UI Next Main-menu 10 70
exit_state=$? ; echo -e "\nexit" >&6
read -u6 tac_id ; read -u6 EXIT
### Main-menu Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_main_menu_UI
### Next Button
elif [ x"$exit_state" == x"0" ]
then
[ -z "$tac_id" ] && __o_db_operation_input_tac_id_UI $operation
#tac_id=${tac_id:-014496} ### Conflict - PRODUCT_ID && TAC_REF - 0-sql/imgen_2nd.sql
#tac_id=${tac_id:-867023} ### Conflict - PRODUCT_ID - 0-sql/imgen_1st.sql
#tac_id=${tac_id:-014450} ### Conflict - TAC_REF - 0-sql/imgen_1st.sql
#tac_id=${tac_id:-868858} ### Conflict - TAC_REF - 0-sql/imgen_3rd.sql
#tac_id=${tac_id:-864330} ### Normal
case "$operation" in
backup)
__o_db_operation_backup_UI
;;
updel)
__o_db_operation_query_tac_id_UI
;;
esac
fi
}
### Database TAC_ID Product Information Query Function
__o_db_operation_query_tac_id_info() {
product_info=$1 ; condition=$2 ; shift 2
### Table: imgen_product ( 0 ), imgen_ref ( 1 ) and imgen_tac ( 2 )
product_query="select t.TAC_ID,t.TAC_FAC,p.*,r.TAC_REF,r.REF_ID,r.REF_CREATION_DATE
from ${dbname}.${imgen_table[0]} p left join ${dbname}.${imgen_table[1]} r
on p.PRODUCT_ID=r.PRODUCT_ID left join ${dbname}.${imgen_table[2]} t on t.TAC_REF=r.TAC_REF
where t.TAC_ID=$tac_id"
if [ x"$condition" == x"normal" ]
then
product_query="${product_query}\G"
elif [ x"$condition" == x"tac_ref_conflict" ]
then
product_query="${product_query} and ${query_condition}\G"
fi
### Call mysql Command, generate TAC_ID product information
__d_mysql_do_bin_cmd -e "$product_query" > $product_info
[ -f "$product_info" ] && {
product_id=`awk '/PRODUCT_ID/ { if ( !a[$2]++ ) print $2 }' $product_info | xargs`
product_id_array=( `echo $product_id` )
tac_ref=`awk '/TAC_REF/ {print $2}' $product_info | xargs`
tac_fac=`awk '/TAC_FAC/ { if ( !a[$2]++ ) print $2 }' $product_info | xargs`
ref_id=`awk '/REF_ID/ { if ( !a[$2]++ ) print $2 }' $product_info | xargs`
fline_count=`sed -n '$=' $product_info` ; fline_count=${fline_count:-1}
block_splict=`echo $tac_ref | xargs -n1 | wc -l`
block_height=$[$fline_count/$block_splict]
sed -i '1,'"$block_height"' { /PRODUCT_CODE/ { x;H;d } ; /PRODUCT_REF_COM/ { G } }' $product_info
sed -i ''"$block_height"',$ { /PRODUCT_CODE/ { x;H;d } ; /PRODUCT_REF_COM/ { G } }' $product_info
sed -i -e "1 s/.*/TAC_ID: $tac_id\n------/" -e "1 s/^/\n/ ; $ G" $product_info
sed -i -e "/^\*\{6,\}.*row.*\*\{6,\}$/ s/.*//" $product_info
}
}
### Database TAC_ID Product Information Conflict Detection Function
__o_db_operation_query_tac_id_UI() {
product_info="$tmp_dir/__p_${tac_id}.product_info"
sub_title="$dbname Database - Query Result"
### Retrieve TAC_ID product informatin
__o_db_operation_query_tac_id_info $product_info normal
if [ -z "$tac_id" -o -z "$product_id" -o -z "$ref_id" ]
then
MSG="\nUnable to retrieve TAC_ID (\Z1 $tac_id \Zn) Product information."
__d_dialog_design_msgbox_UI Re-retrieve 7 70
__o_db_operation_input_tac_id_UI updel
fi
for key in ${!product_id_array[*]}
do
if [ x"$key" == x"0" ]
then
query_condition="PRODUCT_ID=${product_id_array[$key]}"
else
query_condition="$query_condition or PRODUCT_ID=${product_id_array[$key]}"
fi
done
### Call mysql Command, retrieve PRODUCT_ID related TAC_REF. Table: imgen_ref ( 1 )
tac_ref_array=( `__d_mysql_do_bin_cmd $dbname -e "select * from ${imgen_table[1]} where ${query_condition}\G" | awk '/TAC_REF: / {print $2}'` )
### TAC_REF Conflict Detection ( imgen_tac major key ), Duplicate TAC_REF
### Retrieve Process: TAC_ID -> TAC_REF -> PRODUCT_ID
if [ ${#product_id_array[*]} -gt 1 -a ${#tac_ref_array[*]} -gt 1 ]
then
### Duplicate TAC_REF Number. Don't use tac_ref_array to filter.
### Because, the system may detect PRODUCT_ID and TAC_REF conflict at the same time.
### The tac_ref value is used to query TAC_REF duplicate items.
unique_tac_ref=( `echo $tac_ref | xargs -n1 | awk '!a[$0]++ { print $0 }'` )
MSG="\nDuplicate TAC_REF ( TAC_ID:\Z1 $tac_id \Zn, TAC_FAC:\Z1 $tac_fac \Zn)"
MSG="$MSG\n\nQuery TAC_ID: \Z1$tac_id\Zn | Conflict TAC_REF:\Z1 ${unique_tac_ref[*]} \Zn"
MSG="$MSG\n\nConflict Major Key Information:\n------"
MSG="$MSG\nPRODUCT_ID: \Z1 $(echo $product_id | xargs -n1 | awk '{ print NR" | "$0,"\\ " }' | xargs) \Zn"
MSG="$MSG\n TAC_REF: \Z1 $(echo $tac_ref | xargs -n1 | awk '{ print NR" | "$0,"\\ " }' | xargs) \Zn"
MSG="$MSG\n REF_ID: \Z1 $(echo $ref_id | xargs -n1 | awk '{ print NR" | "$0,"\\ " }' | xargs) \Zn"
MSG="$MSG\n\nYou need to do the following Operation.\n------"
MSG="$MSG\nStep 1. \Z1insert\Zn new tac informatin into imgen_tac table,"
MSG="$MSG\n and \Z1generate New\Zn TAC_REF automatically.\n"
MSG="$MSG\nStep 2. \Z1update\Zn duplicate TAC_REF items on imgent_ref table,"
MSG="$MSG\n and \Z1relate New\Zn TAC_REF automatically."
### Step 1.1 - Retrieve related TAC_REF, Table: imgen_ref ( 1 )
tac_ref="${unique_tac_ref[0]}"
__d_dialog_design_msgbox_UI Next 23 80
__o_db_operation_conflict_tac_ref_query_UI
### PRODUCT_ID Conflict Detection ( imgen_product major key ), Duplicate PRODUCT_ID
### Retrieve Process: PRODUCT_ID -> TAC_REF -> TAC_ID
elif [ ${#product_id_array[*]} -eq 1 -a ${#tac_ref_array[*]} -gt 1 ]
then
for key in ${!tac_ref_array[*]}
do
if [ x"$key" == x"0" ]
then
query_condition="TAC_REF=${tac_ref_array[$key]}"
else
query_condition="$query_condition or TAC_REF=${tac_ref_array[$key]}"
fi
done
### Step 2.1 - Call mysql Command, Retrieve related TAC_ID. Table: imgen_tac ( 2 )
tac_id_array=( `__d_mysql_do_bin_cmd $dbname -sN -e "select TAC_ID from ${imgen_table[2]} where $query_condition;"` )
conflict_tac_id_file="$tmp_dir/__p_${tac_id}.conflict.tac_id"
echo ${tac_id_array[*]} | xargs -n1 > $conflict_tac_id_file
MSG="\nDuplicate PRODUCT_ID (\Z1 $product_id \Zn, TAC_ID:\Z1 ${tac_id_array[*]} \Zn)"
MSG="$MSG\n\nQuery TAC_ID: \Z1$tac_id\Zn | Conflict TAC_ID: \Z1$(echo ${tac_id_array[*]} | xargs -n1 | grep -v $tac_id)\Zn"
MSG="$MSG\n\nYou need to do the following Operation.\n------"
MSG="$MSG\nStep 1. \Z1insert\Zn new product informatin into imgen_product table,"
MSG="$MSG\n and \Z1generate New\Zn PRODUCT_ID automatically.\n"
MSG="$MSG\nStep 2. \Z1update\Zn duplicate PRODUCT_ID items on imgent_ref table,"
MSG="$MSG\n and \Z1relate New\Zn PRODUCT_ID automatically."
__d_dialog_design_msgbox_UI Next 17 80
__o_db_operation_conflict_product_id_update_UI
### No conflict
else
### Call Dialog textbox UI
__d_dialog_design_textbox_UI $product_info Next $[fline_count+9] 90
exit_state=$? ; #echo $exit_state ; exit
### Next Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_menu_updel_UI
fi
fi
}
### Database TAC_REF Conflict Operation - Query Filter
__o_db_operation_conflict_tac_ref_query_UI() {
field_info="$tmp_dir/__p_${tac_id}.conflict.query_field"
field_form="$tmp_dir/__p_${tac_id}.conflict.query_form"
field_data="$tmp_dir/__p_${tac_id}.conflict.query_data"
dload_form="$tmp_dir/__d_${tac_id}.dialog_design_form_UI"
sub_title="$dbname - Query Filter"
MSG="\nQuery TAC_ID: \Z1$tac_id\Zn | Conflict TAC_REF:\Z1 ${unique_tac_ref[*]} \Zn"
unset list_items ; declare -a list_items
list_items=( `cat $product_info | awk -F':' '/PRODUCT_/ { if ( !a[$1]++ ) print $1,"field","off" }' | sed '/PRODUCT_CREATION_DATE/d ; s/^ \{1,\}//'` )
list_height=`echo ${list_items[*]} | xargs -n3 | wc -l`
### Call Dialog checklist UI
__d_dialog_design_checklist_UI $field_info Select Re-Select Main-menu $[list_height+9] 70 $list_height
exit_state=$? ; #echo $exit_state ; exit
query_field=( `cat $field_info` ) ; field_height=${#query_field[*]}
field_max_width=`echo ${query_field[*]} | xargs -n1 | awk '{ if ( w < length($1) ) w = length($1) } END { print w }'`
### Re-Select Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_conflict_tac_ref_query_UI
### Main-menu Button
elif [ x"$exit_state" == x"3" ]
then
__o_db_operation_main_menu_UI
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ x"$field_height" == x"0" ] && {
MSG="\n Please select at least \Z1One\Zn field to Query."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_conflict_tac_ref_query_UI
}
__o_db_operation_conflict_tac_ref_retrieve_UI
fi
}
### Database TAC_REF Conflict Operation - Retrieve Product
__o_db_operation_conflict_tac_ref_retrieve_UI() {
### The original product information file ( __p_${tac_id}.product_info )
__product_info="$product_info"
unset form_items ; declare -a form_items
### Append field form, the form look like this: [ label y x item y x flen ilen ] ...
for key in ${!query_field[*]}
do
case "${query_field[$key]}" in
PRODUCT_NAME|PRODUCT_CODE)
form_value="5054W"
;;
PRODUCT_DESIGNATION|PRODUCT_REF_COM)
form_value="Pixi3-5.5 4G"
;;
PRODUCT_CREATION_DATE)
form_value=`date '+%Y-%m-%d %T'`
;;
PRODUCT_ID)
form_value="1698"
;;
esac
form_items[$key]="${query_field[$key]}: $[key+1] 1 '$form_value' $[key+1] $[field_max_width+3] 70 200"
done
MSG="\nEnter the following information:"
set -- Retrieve Re-input Return $[field_height+9] 70 $field_height
ok_label=$1 ; cancel_label=$2 ; extra_label=$3 ; height=$4 ; width=$5 ; form_height=$6 ; shift 6
### Call Dialog form UI - load form data
__d_dialog_design_form_UI ; sed -i '1,$ s/ $//' $dload_form ; source $dload_form
exit_state=$? ; #echo $exit_state ; exit
### Re-input Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_conflict_tac_ref_retrieve_UI
### Return Button
elif [ x"$exit_state" == x"3" ]
then
rm -rf $field_info $field_form $dload_form
__o_db_operation_conflict_tac_ref_query_UI
### Retrieve Button
elif [ x"$exit_state" == x"0" ]
then
[ -f "$field_info" ] && extra_comma_row=$[$(sed -n '$=' $field_info)+1]
[ -f "$field_form" ] && {
sed -i 's/ \{1,\}$//' $field_form
awk 'ARGIND==1 { a[FNR]=$0 ; next } ARGIND==2 { print a[FNR]"=""'\''"$0"'\''" }' $field_info $field_form > $field_data
unset query_condition
while read condition
do
if [ -z "$query_condition" ]
then
query_condition="p.$condition"
else
query_condition="$query_condition and p.$condition"
fi
done < $field_data
}
query_msg="$tmp_dir/__p_${tac_id}.conflict.query_msg"
### Step 1.2 - Retrieve TAC_ID product informatin and related PRODUCT_ID ( imgen_ref )
__o_db_operation_query_tac_id_info $query_msg tac_ref_conflict
if [ -z "$tac_id" -o -z "$product_id" -o -z "$ref_id" ]
then
MSG="\nUnable to retrieve TAC_ID (\Z1 $tac_id \Zn) Product information."
### Reuse the original product information file ( __p_${tac_id}.product_info )
### Because there is no query data will cause a script error.
product_info="$__product_info"
__d_dialog_design_msgbox_UI Re-retrieve 7 70
__o_db_operation_conflict_tac_ref_retrieve_UI
fi
### Call Dialog textbox UI
__d_dialog_design_textbox_UI $query_msg Next $[fline_count+9] 90
exit_state=$? ; #echo $exit_state ; exit
### Next Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_conflict_tac_ref_update_UI
fi
fi
}
### Database TAC_REF Conflict Operation - Update Product
__o_db_operation_conflict_tac_ref_update_UI() {
tacid_info="$tmp_dir/__p_${tac_id}.tacid_info"
field_info="$tmp_dir/__p_${tac_id}.conflict.insert_field"
field_form="$tmp_dir/__p_${tac_id}.conflict.insert_form"
field_data="$tmp_dir/__p_${tac_id}.conflict.insert_data"
### Call mysql Command, generate TAC_ID tac Field information. Table: imgen_tac ( 2 )
__d_mysql_do_bin_cmd $dbname -e "select * from ${imgen_table[2]} where TAC_REF=$tac_ref\G" | sed '1d' > $tacid_info
awk -F': ' '{print $1}' $tacid_info > $field_info
unset form_items ; declare -a form_items
insert_field=( `cat $tacid_info | awk -F':' '{print $1}'` ) ; field_height=${#insert_field[*]}
field_max_width=`echo ${insert_field[*]} | xargs -n1 | awk '{ if ( w < length($1) ) w = length($1) } END { print w }'`
### Append field form, the form look like this: [ label y x item y x flen ilen ] ...
for key in ${!insert_field[*]}
do
case "${insert_field[$key]}" in
TAC_CREATION_DATE)
form_value=`date '+%Y-%m-%d %T'`
;;
TAC_REF)
### Call mysql Command, Retrieve New TAC_REF. Table: imgen_tac ( 2 )
form_value=`__d_mysql_do_bin_cmd $dbname -sN -e "show create table ${imgen_table[2]}\G" | \
sed -n '/.*ENGINE=.*AUTO_INCREMENT=\([[:digit:]]*\) .*/ s//\1/p'`
new_tac_ref="$form_value"
;;
*)
form_value=`awk -F': ' '/'"${insert_field[$key]}"'/ {print $2}' $tacid_info`
;;
esac
form_items[$key]="${insert_field[$key]}: $[key+1] 1 '$form_value' $[key+1] $[field_max_width+3] 70 200"
done
sub_title="$dbname - TAC_REF Conflict" ; width=70
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG=`echo $MSG | sed ':a s/^.\{1,'"$[width-1]"'\}$/ & /;ta'`
MSG="\n$MSG\n\n\Z1Step 1.\Zn insert \Z1New\Zn tac informatin into imgen_tac table."
MSG="$MSG\n The \Z1TAC_REF\Zn ( major Key ) is automatically generated."
set -- Insert Re-input Main-menu $[field_height+12] $width $field_height
ok_label=$1 ; cancel_label=$2 ; extra_label=$3 ; height=$4 ; width=$5 ; form_height=$6 ; shift 6
### Call Dialog form UI
__d_dialog_design_form_UI ; sed -i '1,$ s/ $//' $dload_form ; source $dload_form
exit_state=$? ; #echo $exit_state ; exit
### Re-input Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_conflict_tac_ref_update_UI
### Main-menu Button
elif [ x"$exit_state" == x"3" ]
then
rm -rf $tacid_info $field_info $field_form $dload_form
__o_db_operation_main_menu_UI
### Insert Button
elif [ x"$exit_state" == x"0" ]
then
[ -f "$field_info" ] && extra_comma_row=$[$(sed -n '$=' $field_info)+1]
[ -f "$field_form" ] && {
sed -i 's/ \{1,\}$//' $field_form
awk 'ARGIND==1 { a[FNR]=$0 ; next } ARGIND==2 { print a[FNR]":",$0 }' $field_info $field_form > $field_data
new_tac_id=`awk '/TAC_ID/ {print $2}' $field_data`
new_tac_fac=`awk '/TAC_FAC/ {print $2}' $field_data`
new_tac_ref=`awk '/TAC_REF/ {print $2}' $field_data`
}
insert_sql="$tmp_dir/__p_${tac_id}.conflict.insert_sql"
insert_msg="$tmp_dir/__p_${tac_id}.conflict.insert_msg"
### Related Process: Generate ( SQL ) -> Insert ( imgen_tac )-> Relate ( imgen_ref )
### Step 1.3 - Generate insert DML statement, insert tac informatin into imgen_tac ( 2 ).
awk -F': ' 'BEGIN { print "insert into '${imgen_table[2]}' (" } \
ARGIND==1 { print $0"," ; next } \
ARGIND==2 { a[FNR]=$0 ; b[j++]=FNR } \
END { print ") values (" ; for (i=0;i<j;i++) print "'\''"a[b[i]]"'\''," }' $field_info $field_form | \
sed -e '1,$ s/^ \{1,\}// ; $s/,/ );/' -e ''${extra_comma_row}' s/,$//' > $insert_sql
[ -f "$insert_sql" ] && sed -e '1,$ s/^/| /' -e '1,$ s/$/\\n/' $insert_sql > $insert_msg
msg_height=`sed -n '$=' $insert_msg`
MSG="\nTAC_ID: \Z1$tac_id $tac_fac\Zn -> \Z1$new_tac_id $new_tac_fac\Zn | TAC_REF: \Z1$tac_ref\Zn -> \Z1$new_tac_ref\Zn"
MSG="$MSG\n\n------"
MSG="$MSG\n\Z1WARNING:\Zn Are you sure want to perform \Z1Insert\Zn Operation? ( Default: \Z1No\Zn )"
MSG="$MSG\n\n+------"
MSG="$MSG\n$(cat $insert_msg)+------"
### Call Dialog yesno UI
__d_dialog_design_yesno_UI Continue Cancel $[msg_height+13] 92
exit_state=$? ; #echo $exit_state ; exit
### Cancel Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_input_tac_id_UI updel
### Continue Button
elif [ x"$exit_state" == x"0" ]
then
MSG="\nTAC_ID: \Z1$tac_id\Zn -> \Z1$new_tac_id\Zn | TAC_REF: \Z1$tac_ref\Zn -> \Z1$new_tac_ref\Zn"
MSG="$MSG\n\n\Z1Step 2 :\Zn\n------"
### Step 1.4 - Call mysql Command, Insert New tac informain ( Record - Row level, insert into imgen_tac Table )
__d_mysql_do_bin_cmd $dbname < $insert_sql
[ $? -eq 0 ] && insert_state="Success" || insert_state="Failure"
MSG="$MSG\n imgen_tac \Z1Insert\Zn - [\Z1 $insert_state \Zn]"
### step 1.5 - Call mysql Command, Update old TAC_REF to New TAC_REF ( Record - Row level, update imgen_ref Table )
update_sql="update ${imgen_table[1]} set TAC_REF=$new_tac_ref where PRODUCT_ID=$product_id and TAC_REF=$tac_ref and REF_ID=$ref_id;"
[ x"$insert_state" == x"Success" ] && __d_mysql_do_bin_cmd $dbname -e "$update_sql"
[ $? -eq 0 ] && update_state="Success" || update_state="Failure"
MSG="$MSG\n imgen_ref \Z1Update\Zn - [\Z1 $update_state \Zn]"
MSG="$MSG\n\nIf it was successful, please press the \Z1Re-retrieve\Zn button to continue,"
MSG="$MSG\nand \Z1Update\Zn product information if necessarily.\n"
### Call Dialog msgbox UI
__d_dialog_design_msgbox_UI Re-retrieve 15 80
exit_state=$? ; #echo $exit_state ; exit
### Re-retrieve Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_input_tac_id_UI updel
fi
fi
fi
}
### Database PRODUCT_ID Conflict Operation - Retrieve and Update
__o_db_operation_conflict_product_id_update_UI() {
field_info="$tmp_dir/__p_${tac_id}.conflict.insert_field"
field_form="$tmp_dir/__p_${tac_id}.conflict.insert_form"
field_data="$tmp_dir/__p_${tac_id}.conflict.insert_data"
dload_form="$tmp_dir/__d_${tac_id}.dialog_design_form_UI"
### Call mysql Command, generate TAC_ID product Field information. Table: imgen_table ( 0 )
__d_mysql_do_bin_cmd $dbname -e "select * from ${imgen_table[0]} where PRODUCT_ID=$product_id\G" | sed '1d' | awk -F':' '{print $1}' > $field_info
sed -i -e '/PRODUCT_CODE/ { h;d } ; /PRODUCT_REF_COM/ { G }' $field_info
insert_field=( `cat $field_info` ) ; field_height=${#insert_field[*]}
field_max_width=`echo ${insert_field[*]} | xargs -n1 | awk '{ if ( w < length($1) ) w = length($1) } END { print w }'`
unset form_items ; declare -a form_items
### Append field form, the form look like this: [ label y x item y x flen ilen ] ...
for key in ${!insert_field[*]}
do
case "${insert_field[$key]}" in
PRODUCT_NAME|PRODUCT_CODE)
form_value="7048X"
;;
PRODUCT_DESIGNATION|PRODUCT_REF_COM)
form_value="Go play"
;;
PRODUCT_CREATION_DATE)
form_value=`date '+%Y-%m-%d %T'`
;;
PRODUCT_ID)
### Call mysql Command, Retrieve New PRODUCT_ID. Table: imgen_product ( 0 )
form_value=`__d_mysql_do_bin_cmd $dbname -sN -e "show create table ${imgen_table[0]}\G" | \
sed -n '/.*ENGINE=.*AUTO_INCREMENT=\([[:digit:]]*\) .*/ s//\1/p'`
new_product_id="$form_value"
esac
form_items[$key]="${insert_field[$key]}: $[key+1] 1 '$form_value' $[key+1] $[field_max_width+3] 70 200"
done
sub_title="$dbname - PRODUCT_ID Conflict" ; width=70
MSG="- TAC_ID: \Z1$tac_id\Zn -"
MSG=`echo $MSG | sed ':a s/^.\{1,'"$[width-1]"'\}$/ & /;ta'`
MSG="\n$MSG\n\n\Z1Step 1.\Zn insert \Z1New\Zn product informatin into imgen_product table."
MSG="$MSG\n The \Z1PRODUCT_ID\Zn ( major Key ) is automatically generated."
set -- Insert Re-input Main-menu $[field_height+12] $width $field_height
ok_label=$1 ; cancel_label=$2 ; extra_label=$3 ; height=$4 ; width=$5 ; form_height=$6 ; shift 6
### Call Dialog form UI
__d_dialog_design_form_UI ; sed -i '1,$ s/ $//' $dload_form ; source $dload_form
exit_state=$? ; #echo $exit_state ; exit
### Re-input Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_conflict_product_id_update_UI
### Main-menu Button
elif [ x"$exit_state" == x"3" ]
then
rm -rf $field_info $field_form $dload_form
__o_db_operation_main_menu_UI
### Insert Button
elif [ x"$exit_state" == x"0" ]
then
[ -f "$field_info" ] && extra_comma_row=$[$(sed -n '$=' $field_info)+1]
[ -f "$field_form" ] && {
sed -i 's/ \{1,\}$//' $field_form
awk 'ARGIND==1 { a[FNR]=$0 ; next } ARGIND==2 { print a[FNR]":",$0 }' $field_info $field_form > $field_data
new_product_id=`awk '/PRODUCT_ID/ {print $2}' $field_data`
}
insert_sql="$tmp_dir/__p_${tac_id}.conflict.insert_sql"
insert_msg="$tmp_dir/__p_${tac_id}.conflict.insert_msg"
### Related Process: Generate ( SQL ) -> Insert ( imgen_product )-> Relate ( imgen_ref )
### Step 2.2 - Generate insert DML statement, insert product informatin into imgen_product ( 0 ).
awk -F': ' 'BEGIN { print "insert into '${imgen_table[0]}' (" } \
ARGIND==1 { print $0"," ; next } \
ARGIND==2 { a[FNR]=$0 ; b[j++]=FNR } \
END { print ") values (" ; for (i=0;i<j;i++) print "'\''"a[b[i]]"'\''," }' $field_info $field_form | \
sed -e '1,$ s/^ \{1,\}// ; $s/,/ );/' -e ''${extra_comma_row}' s/,$//' > $insert_sql
[ -f "$insert_sql" ] && sed -e '1,$ s/^/| /' -e '1,$ s/$/\\n/' $insert_sql > $insert_msg
msg_height=`sed -n '$=' $insert_msg`
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn -> New PRODUCT_ID: \Z1$new_product_id\Zn"
MSG="$MSG\n\n------"
MSG="$MSG\n\Z1WARNING:\Zn Are you sure want to perform \Z1Insert\Zn Operation? ( Default: \Z1No\Zn )"
MSG="$MSG\n\n+------"
MSG="$MSG\n$(cat $insert_msg)+------"
### Call Dialog yesno UI
__d_dialog_design_yesno_UI Continue Cancel $[msg_height+13] 92
exit_state=$? ; #echo $exit_state ; exit
### Cancel Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_input_tac_id_UI updel
### Continue Button
elif [ x"$exit_state" == x"0" ]
then
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn -> New PRODUCT_ID: \Z1$new_product_id\Zn"
MSG="$MSG\n\n\Z1Step 2 :\Zn\n------"
### Step 2.3 - Call mysql Command, Insert New product informain ( Record - Row level, insert into imgen_product Table )
__d_mysql_do_bin_cmd $dbname < $insert_sql
[ $? -eq 0 ] && insert_state="Success" || insert_state="Failure"
MSG="$MSG\n imgen_product \Z1Insert\Zn - [\Z1 $insert_state \Zn]"
### step 2.4 - Call mysql Command, Update old PRODUCT_ID to New PRODUCT_ID ( Record - Row level, update imgen_ref Table )
update_sql="update ${imgen_table[1]} set PRODUCT_ID=$new_product_id where PRODUCT_ID=$product_id and TAC_REF=$tac_ref and REF_ID=$ref_id;"
[ x"$insert_state" == x"Success" ] && __d_mysql_do_bin_cmd $dbname -e "$update_sql"
[ $? -eq 0 ] && update_state="Success" || update_state="Failure"
MSG="$MSG\n imgen_ref \Z1Update\Zn - [\Z1 $update_state \Zn]"
MSG="$MSG\n\nIf it was successful, please press the \Z1Re-retrieve\Zn button to Re-retrieve.\n"
### Call Dialog msgbox UI
__d_dialog_design_msgbox_UI Re-retrieve 14 80
exit_state=$? ; #echo $exit_state ; exit
### Re-retrieve Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_input_tac_id_UI updel
fi
fi
fi
}
### Database Operation Up-Del ( include Update and Delete ) Menu Function
__o_db_operation_menu_updel_UI() {
unset list_items ; declare -a list_items
list_items=( "Update Operation"
"Delete Operation" )
sub_title="$dbname - Database Operation"
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
### Choice Menu Type, menu or radiolist
__o_db_operation_menu_type_UI radiolist Select Re-retrieve Main-menu
exit_state=$? ; echo -e "\nexit" >&6
read -u6 operation ; read -u6 EXIT
### Re-retrieve Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_input_tac_id_UI updel
### Main-menu Button
elif [ x"$exit_state" == x"3" ]
then
__o_db_operation_main_menu_UI
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ -z "$operation" ] && __o_db_operation_menu_updel_UI
case $operation in
Update)
__o_db_operation_update_UI
;;
Delete)
__o_db_operation_delete_UI
;;
esac
fi
}
### Database Update Function
__o_db_operation_update_UI() {
field_info="$tmp_dir/__p_${tac_id}.update_field"
field_form="$tmp_dir/__p_${tac_id}.update_form"
dload_form="$tmp_dir/__d_${tac_id}.dialog_design_form_UI"
update_sql="$tmp_dir/__p_${tac_id}.update_sql"
update_msg="$tmp_dir/__p_${tac_id}.update_msg"
sub_title="$dbname Database - Update Operation"
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
unset list_items ; declare -a list_items
list_items=( `cat $product_info | awk -F':' '/PRODUCT_/ {print $1,"Field","off"}' | sed '/PRODUCT_CREATION_DATE/d ; /PRODUCT_ID/d'` )
list_height=`echo ${list_items[*]} | xargs -n3 | wc -l`
### Call Dialog checklist UI
__d_dialog_design_checklist_UI $field_info Select Re-Select Return $[list_height+9] 70 $list_height
exit_state=$? ; #echo $exit_state ; exit
update_field=( `cat $field_info` ) ; field_height=${#update_field[*]}
field_max_width=`echo ${update_field[*]} | xargs -n1 | awk '{ if ( w < length($1) ) w = length($1) } END { print w }'`
### Re-Select Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_update_UI
### Return Button
elif [ x"$exit_state" == x"3" ]
then
__o_db_operation_menu_updel_UI
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ x"$field_height" == x"0" ] && {
MSG="\n Please select at least \Z1One\Zn field to Updated."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_update_UI
}
### Load field form Data from Database Query
__o_db_operation_update_load_form_UI
fi
}
### Database Update Function - Load form Data
__o_db_operation_update_load_form_UI() {
unset form_items ; declare -a form_items
### Append field form, the form look like this: [ label y x item y x flen ilen ] ...
for key in ${!update_field[*]}
do
form_value=`awk -F': ' '/'"${update_field[$key]}"'/ {print $2}' $product_info`
form_items[$key]="${update_field[$key]}: $[key+1] 1 '$form_value' $[key+1] $[field_max_width+3] 70 200"
done
MSG="\nEnter the following information:"
set -- Update Re-input Return $[field_height+9] 70 $field_height
ok_label=$1 ; cancel_label=$2 ; extra_label=$3 ; height=$4 ; width=$5 ; form_height=$6 ; shift 6
### Call Dialog form UI - load form data
__d_dialog_design_form_UI ; sed -i '1,$ s/ $//' $dload_form ; source $dload_form
exit_state=$? ; #echo $exit_state ; exit
### Re-input Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_update_load_form_UI
### Return Button
elif [ x"$exit_state" == x"3" ]
then
rm -rf $field_info $field_form $dload_form
__o_db_operation_update_UI
### Update Button
elif [ x"$exit_state" == x"0" ]
then
### Generate update DML statement
awk 'ARGIND==1 { a[FNR]=$0 ; next } \
ARGIND==2 { print a[FNR]"='\''"$0"'\''" }' \
$field_info $field_form > $update_sql
[ -f "$update_sql" ] && {
sed -i '$!s/$/,/' $update_sql
sed -i "1 s/^/update ${imgen_table[0]} set\n/" $update_sql
sed -i "$ s/$/\nwhere PRODUCT_ID=$product_id;/" $update_sql
sed -e '1,$ s/^/| /' -e '1,$ s/$/\\n/' $update_sql > $update_msg
}
msg_height=`sed -n '$=' $update_msg`
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG="$MSG\n\n------"
MSG="$MSG\n\Z1WARNING:\Zn Are you sure want to perform \Z1Update\Zn Operation? ( Default: \Z1No\Zn )"
MSG="$MSG\n\n+------"
MSG="$MSG\n$(cat $update_msg)+------"
### Call Dialog yesno UI
__d_dialog_design_yesno_UI Continue Cancel $[msg_height+13] 92
exit_state=$? ; #echo $exit_state ; exit
### Cancel Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_update_UI
### Continue Button
elif [ x"$exit_state" == x"0" ]
then
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG="$MSG\n\nUpdate STATE :\n------"
### Call mysql Command, Update TAC_ID product informain ( Record - Row level, update imgen_product Table )
__d_mysql_do_bin_cmd $dbname < $update_sql
[ $? -eq 0 ] && update_state="Success" || update_state="Failure"
MSG="$MSG\n imgen_product \Z1Update\Zn - [\Z1 $update_state \Zn]"
MSG="$MSG\n\nIf it was successful, please press the \Z1Re-retrieve\Zn button to Re-retrieve.\n"
### Call Dialog msgbox UI
__d_dialog_design_msgbox_UI Re-retrieve 13 80
exit_state=$? ; #echo $exit_state ; exit
### Re-retrieve Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_input_tac_id_UI updel
fi
fi
fi
}
### Database Delete Function
__o_db_operation_delete_UI() {
delete_msg="$tmp_dir/__p_${tac_id}.delete_msg"
sub_title="$dbname Database - Delete Operation"
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG="$MSG\n\n------"
MSG="$MSG\n\Z1WARNING:\Zn Are you sure want to perform \Z1Delete\Zn Operation? ( Default: \Z1No\Zn )"
MSG="$MSG\n\n+------"
product_del_sql="delete from imgen_product where PRODUCT_ID=$product_id;"
ref_del_sql="delete from imgen_ref where REF_ID=$ref_id;"
tac_del_sql="delete from imgen_tac where TAC_REF=$tac_ref;"
echo -e "$product_del_sql\n$ref_del_sql\n$tac_del_sql" > $delete_msg
sed -i -e '1,$ s/^/| /' -e '1,$ s/$/\\n/' $delete_msg
msg_height=`sed -n '$=' $delete_msg`
MSG="$MSG\n$(cat $delete_msg)+------"
### Call Dialog yesno UI
__d_dialog_design_yesno_UI Continue Cancel $[msg_height+13] 92
exit_state=$? ; #echo $exit_state ; exit
### Cancel Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_menu_updel_UI
### Continue Button
elif [ x"$exit_state" == x"0" ]
then
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG="$MSG\n\nDelete STATE :\n------"
unset delete_state ; declare -a delete_state
### Table: imgen_product ( 0 ) , imgen_ref ( 1 ) and imgen_tac ( 2 )
for key in ${!imgen_table[*]}
do
case ${imgen_table[$key]} in
imgen_product)
query_condition="PRODUCT_ID=$product_id"
delete_sql="$product_del_sql"
;;
imgen_ref)
query_condition="REF_ID=$ref_id"
delete_sql="$ref_del_sql"
;;
imgen_tac)
query_condition="TAC_REF=$tac_ref"
delete_sql="$tac_del_sql"
;;
esac
### Call mysql Command, Delete TAC_ID product information ( Record - Row level )
__d_mysql_do_bin_cmd $dbname -e "$delete_sql"
[ $? -eq 0 ] && delete_state[$key]="Success" || delete_state[$key]="Failure"
if [ x"$key" == x"0" ]
then
MSG="$MSG\n ${imgen_table[$key]} \Z1Delete\Zn - [\Z1 ${delete_state[$key]} \Zn]"
elif [ x"$key" == x"1" -o x"$key" == x"2" ]
then
MSG="$MSG\n ${imgen_table[$key]} \Z1Delete\Zn - [\Z1 ${delete_state[$key]} \Zn]"
fi
done
MSG="$MSG\n\nIf it was successful, please press the \Z1Re-retrieve\Zn button to Re-retrieve.\n"
### Call Dialog msgbox UI
__d_dialog_design_msgbox_UI Re-retrieve 15 80
exit_state=$? ; #echo $exit_state ; exit
### Re-retrieve Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_input_tac_id_UI updel
fi
fi
}
### Database Backup Function
__o_db_operation_backup_UI() {
tac_id_array=( `echo $tac_id | awk -v FS=',' '{ $1=$1 ; print $0 }' | xargs -n1 | awk '!a[$0]++ { print $0 }'` )
for tac_id in ${tac_id_array[*]}
do
product_info="$tmp_dir/__p_${tac_id}.product_info"
### Retrieve TAC_ID product informatin
__o_db_operation_query_tac_id_info $product_info normal
if [ -z "$tac_id" -o -z "$product_id" -o -z "$ref_id" ]
then
MSG="\nUnable to retrieve TAC_ID (\Z1 $tac_id \Zn) Product information."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_input_tac_id_UI backup
fi
done
level_info="$tmp_dir/__0_${pid}.backup_level"
MSG="\n- Note: \Z1Restore\Zn operation Provide \Z1Record-level ( Row )\Zn recovery -"
unset list_items ; declare -a list_items
### Backup level: db_level ( 0 ), table_level ( 1 ) and record_level ( 2 )
for key in ${!backup_level[*]}
do
case ${backup_level[$key]} in
db_level)
list_items[$key]="Database Level off"
;;
table_level)
list_items[$key]="Table Level off"
;;
record_level)
list_items[$key]="Record Level on"
;;
esac
done
list_height=${#list_items[*]}
### Call Dialog checklist UI
__d_dialog_design_checklist_UI $level_info Select Re-Select Main-menu $[list_height+9] 70 $list_height
exit_state=$? ; #echo $exit_state ; exit
select_level=( `cat $level_info` ) ; level_count=${#select_level[*]}
### Re-Select Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_backup_UI
### Main-menu Button
elif [ x"$exit_state" == x"3" ]
then
__o_db_operation_main_menu_UI
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ x"$level_count" == x"0" ] && {
MSG="\n Please select at least \Z1One\Zn Backup level."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_backup_UI
}
echo ${select_level[*]} | grep -qE "Database" ; [ $? -eq 0 ] && is_db_level=true || is_db_level=false
echo ${select_level[*]} | grep -qE "Table" ; [ $? -eq 0 ] && is_table_level=true || is_table_level=false
echo ${select_level[*]} | grep -qE "Record" ; [ $? -eq 0 ] && is_record_level=true || is_record_level=false
date_now=`date +%Y%m%d%H%M.%S`
[ x"$is_db_level" == x"true" ] && mkdir -p $db_level_dir/$date_now
[ x"$is_table_level" == x"true" ] && mkdir -p $table_level_dir/$date_now
MSG="\nBackup DIR :\n------"
MSG="$MSG\n \Z1 Database \Zn - [ $backup_dir/db_level_dir/$date_now ]"
MSG="$MSG\n \Z1 Table \Zn - [ $backup_dir/table_level_dir/$date_now ]"
MSG="$MSG\n \Z1 Record \Zn - [ $backup_dir/record_level_dir/TAC_ID/$date_now ]"
MSG="$MSG\n\nBackup STATE :\n------"
### Call mysqldump Command, Backup IMGEN Database.
[ x"$is_db_level" == x"true" ] && {
__d_mysql_do_dump_cmd db_level $dbname | gzip > $db_level_dir/$date_now/${dbname}.sql.gz
[ $? -eq 0 ] && backup_state="Success" || backup_state="Failure"
MSG="$MSG\n Database Level - [\Z1 $backup_state \Zn]"
}
### Call mysqldump Command, Backup Table Data ( include imgen_product, imgen_ref and imgen_tac ).
[ x"$is_table_level" == x"true" ] && {
__d_mysql_do_dump_cmd table_level $dbname ${imgen_table[*]} | gzip > $table_level_dir/$date_now/${dbname}_table.sql.gz
[ $? -eq 0 ] && backup_state="Success" || backup_state="Failure"
MSG="$MSG\n Table Level - [\Z1 $backup_state \Zn]"
}
### Call mysql Command, export TAC_ID product information ( Record - Row level, include insert and update DML statement ).
[ x"$is_record_level" == x"true" ] && {
for tac_id in ${tac_id_array[*]}
do
product_info="$tmp_dir/__p_${tac_id}.product_info"
### Retrieve TAC_ID product informatin
__o_db_operation_query_tac_id_info $product_info normal
if [ -z "$tac_id" -o -z "$product_id" -o -z "$ref_id" ]
then
MSG="\nUnable to retrieve TAC_ID (\Z1 $tac_id \Zn) Product information."
__d_dialog_design_msgbox_UI Re-retrieve 7 70
__o_db_operation_input_tac_id_UI backup
fi
product_id_array=( `echo $product_id` )
tac_ref_array=( `echo $tac_ref` )
ref_id_array=( `echo $ref_id` )
if [ "${#product_id_array[*]}" -gt 1 -o "${#tac_ref_array[*]}" -gt 1 -o "${#ref_id_array[*]}" -gt 1 ]
then
MSG="$MSG\n Record Level - [\Z1 Failure - $tac_id / Conflict\Zn ]"
else
record_level_dir="$backup_dir/record_level/$tac_id/$date_now" ; mkdir -p $record_level_dir
### Generate insert DML statement
insert_sql() {
table=$1 ; history_data=$2 ; shift 2
extra_comma_row=$[$(sed -n '$=' $history_data)+1]
awk -F': ' 'BEGIN { print "insert into '$table' (" } \
{ a[NR]=$2 ; b[j++]=NR ; print $1","} \
END { print ") values (" ; for (i=0;i<j;i++) print "'\''"a[b[i]]"'\''," }' $history_data | \
sed -e '1,$ s/^ \{1,\}// ; $s/,/ );/' -e ''${extra_comma_row}' s/,$//'
}
### Generate update DML statement
update_sql() {
table=$1 ; history_data=$2 ; shift 2
awk -F': ' '{print $1"='\''"$2"'\''"}' $history_data | \
sed '1,$ s/^ \{1,\}//; $! s/$/,/' | \
sed "1 s/^/update ${imgen_table[$key]} set\n/" | \
sed "$ a where $query_condition;"
}
unset backup_state ; declare -a backup_state
### Table: imgen_product ( 0 ), imgen_ref ( 1 ) and imgen_tac ( 2 )
for key in ${!imgen_table[*]}
do
case ${imgen_table[$key]} in
imgen_product)
query_condition="PRODUCT_ID=$product_id"
;;
imgen_ref)
query_condition="REF_ID=$ref_id"
;;
imgen_tac)
query_condition="TAC_REF=$tac_ref"
;;
esac
history_data="$record_level_dir/${dbname}.${imgen_table[$key]}.history_data"
insert_sql_file="$record_level_dir/${dbname}.${imgen_table[$key]}.sql_insert"
update_sql_file="$record_level_dir/${dbname}.${imgen_table[$key]}.sql_update"
### Call mysql Command, generate history Backup Query Data
__d_mysql_do_bin_cmd $dbname -e "select * from ${imgen_table[$key]} where ${query_condition}\G" | sed '1d' > $history_data
[ $? -eq 0 ] && backup_state[$key]="Success" || backup_state[$key]="Failure"
[ "${imgen_table[$key]}" == "imgen_product" ] && {
sed -i -e '/PRODUCT_CODE/ { h;d } ; /PRODUCT_REF_COM/ { G }' $history_data
}
### Generate insert and update DML statement
insert_sql ${imgen_table[$key]} $history_data > $insert_sql_file
update_sql ${imgen_table[$key]} $history_data > $update_sql_file
done
echo ${backup_state[*]} | grep -q 'Failure' ; [ $? -eq 0 ] && backup_state="Failure" || backup_state="Success"
MSG="$MSG\n Record Level - [\Z1 $backup_state - $tac_id \Zn]"
fi
done
}
__d_dialog_design_msgbox_UI Return $[level_count + ${#tac_id_array[*]} + 13] 80
__o_db_operation_main_menu_UI
fi
}
### Database Restore Function - Retrieve TAC_ID
__o_db_operation_restore_UI() {
record_level_dir="$backup_dir/record_level" ; mkdir -p $record_level_dir
sub_title="$dbname Database - Restore Operation" ; width=70
unset list_items ; declare -a list_items
list_items=( `find $record_level_dir -maxdepth 1 | sed '1d' | awk -F'/' '{print $NF}' | sort` )
list_height=${#list_items[*]}
[ x"$list_height" == x"0" ] && {
MSG="\nThe history Backup Point of TAC_ID \Z1Not found\Zn."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_main_menu_UI
}
list_items=( `echo ${list_items[*]} | xargs -n1 | awk '{print $1,"TAC_ID","off"}'` )
MSG="- Select Backup (\Z1 TAC_ID \Zn) to Restore -"
MSG=`echo $MSG | sed ':a s/^.\{1,'"$width"'\}$/ & /;ta'`
MSG="\n$MSG"
unset product_id tac_id tac_fac tac_ref ref_id product_id_array tac_id_array tac_ref_array
### Call Dialog radiolist UI
__d_dialog_design_radiolist_UI Select Re-select Main-menu $[list_height+9] $width $list_height
exit_state=$? ; echo -e "\nexit" >&6
read -u6 tac_id ; read -u6 EXIT
### Re-select Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_restore_UI
### Main-menu Button
elif [ x"$exit_state" == x"3" ]
then
__o_db_operation_main_menu_UI
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ -z "$tac_id" ] && __o_db_operation_restore_UI
__o_db_operation_restore_tac_id_UI
fi
}
### Database Restore Function - Restore TAC_ID
__o_db_operation_restore_tac_id_UI() {
MSG1="\n- Note: \Z1Restore\Zn operation Provide \Z1Record-level ( Row )\Zn recovery -"
MSG2="| TAC_ID: \Z1$tac_id\Zn |"
width=70
MSG1=`echo $MSG1 | sed ':a s/^.\{1,'"$[width-1]"'\}$/ & /;ta'`
MSG2=`echo $MSG2 | sed ':a s/^.\{1,'"$[width-1]"'\}$/ & /;ta'`
MSG="$MSG1\n$MSG2"
record_point="$record_level_dir/$tac_id"
### Retrieve TAC_ID Backup Number
history_point=( `find $record_point -maxdepth 1 | sed '1d' | awk -F'/' '{print $NF}' | sort` )
list_height=${#history_point[*]}
[ x"$list_height" == x"0" ] && {
MSG="\nThe TAC_ID (\Z1 $tac_id \Zn) history Backup Point \Z1Not found\Zn."
__d_dialog_design_msgbox_UI Return 7 70
__o_db_operation_main_menu_UI
}
unset list_items ; declare -a list_items
### Retrieve TAC_ID Backup level
for p_key in ${!history_point[*]}
do
unset exist_level ; declare -a exist_level
### Backup level: db_level ( 0 ) , table_level ( 1 ) and record_level ( 2 )
for l_key in ${!backup_level[*]}
do
case ${backup_level[$l_key]} in
db_level)
history_bkdir="$db_level_dir/${history_point[$p_key]}"
[ `ls $history_bkdir | wc -l` -ne 0 ] && exist_level[$l_key]=D
;;
table_level)
history_bkdir="$table_level_dir/${history_point[$p_key]}"
[ `ls $history_bkdir | wc -l` -ne 0 ] && exist_level[$l_key]=T
;;
record_level)
history_bkdir="$record_point/${history_point[$p_key]}"
[ `ls $history_bkdir | wc -l` -ne 0 ] && exist_level[$l_key]=R
;;
esac
done
exist_level=`echo ${exist_level[*]} | awk -v OFS='-' '{ $1=$1 ; print $0 }'`
list_items[$p_key]=`echo ${history_point[$p_key]} | xargs -n1 | awk -F'/' '{print $NF,"'$exist_level'","off"}'`
done
### Call Dialog radiolist UI
__d_dialog_design_radiolist_UI Select Re-select Return $[list_height+10] 70 $list_height
exit_state=$? ; echo -e "\nexit" >&6
read -u6 restore_point ; read -u6 EXIT
### Re-select Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_restore_tac_id_UI
### Return Button
elif [ x"$exit_state" == x"3" ]
then
__o_db_operation_restore_UI
### Select Button
elif [ x"$exit_state" == x"0" ]
then
[ -z "$restore_point" ] && __o_db_operation_restore_tac_id_UI
restore_msg="$tmp_dir/__p_${tac_id}.restore_msg"
restore_bkdir="$record_point/$restore_point"
record_level="$restore_bkdir/$dbname"
### Retrieve TAC_ID history Backup Point ( Record - Row level )
[ `ls ${record_level%/$dbname} | wc -l` -eq 0 ] && {
MSG="\nThe history Backup Point dose not have \Z1Record-level ( Row )\Zn backup.\n"
### Call Dialog msgbox UI
__d_dialog_design_msgbox_UI Return 7 80
exit_state=$? ; #echo $exit_state ; exit
### Return Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_restore_tac_id_UI
fi
}
### Display TAC_ID history Backup Point Query Data
result_file=`echo ${imgen_table[*]} | xargs -n1 | \
awk '{print "'$record_level'."$1".history_data"}' | xargs`
field_max_width=`awk -F': ' '{ if ( w < length($1) ) w=length($1) } END { print w } ' $result_file`
sed -i ':a s/^.\{1,'"$[field_max_width-1]"'\}:.*$/ &/;ta' $result_file
tac_point="- TAC_ID: $tac_id | Restore Point: $restore_point -"
tac_point=`echo $tac_point | sed ':a s/^.\{1,82\}$/ & /;ta'`
echo -e "\n $tac_point \n" > $restore_msg ; more $result_file >> $restore_msg
### Call Dialog yesno UI
__d_dialog_design_textbox_UI $restore_msg Next 30 90
exit_state=$? ; #echo $exit_state ; exit
### Next Button
if [ x"$exit_state" == x"0" ]
then
### Table: imgen_product ( 0 ), imgen_ref ( 1 ) and imgen_tac ( 2 )
product_id=`awk '/PRODUCT_ID/ {print $2}' ${record_level}.${imgen_table[0]}.history_data`
ref_id=`awk '/REF_ID/ {print $2}' ${record_level}.${imgen_table[1]}.history_data`
tac_ref=`awk '/TAC_REF/ {print $2}' ${record_level}.${imgen_table[2]}.history_data`
product_info="$tmp_dir/__p_${tac_id}.product_info"
### Retrieve TAC_ID product informatin
__o_db_operation_query_tac_id_info $product_info normal
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG="$MSG\n\n------"
MSG="$MSG\n\Z1WARNING:\Zn Are you sure want to perform \Z1Restore\Zn Operation? ( Default: \Z1No\Zn )\n"
MSG="$MSG\nIf the Product information is exists, the \Z1Update\Zn statement will be executed."
MSG="$MSG\nIf not, the \Z1Insert\Zn statement will be executed."
MSG="$MSG\n\n+------"
### Display history Backup Point insert or update DML statement file
if [ x"$fline_count" == x"1" ]
then
echo ${imgen_table[*]} | xargs -n1 | awk '{print "| '$record_level'."$1".sql_insert"}' > $restore_msg
else
echo ${imgen_table[*]} | xargs -n1 | awk '{print "| '$record_level'."$1".sql_update"}' > $restore_msg
fi
sed -i -e '1,$ s/$/\\n/' $restore_msg
msg_height=`sed -n '$=' $restore_msg`
MSG="$MSG\n$(cat $restore_msg)+------"
### Call Dialog yesno UI
__d_dialog_design_yesno_UI Continue Cancel $[msg_height+16] 92
exit_state=$? ; #echo $exit_state ; exit
### Cancel Button
if [ x"$exit_state" == x"1" ]
then
__o_db_operation_restore_UI
### Continue Button
elif [ x"$exit_state" == x"0" ]
then
MSG="\nTAC_ID: \Z1$tac_id\Zn | PRODUCT_ID: \Z1$product_id\Zn | TAC_REF: \Z1$tac_ref\Zn | REF_ID: \Z1$ref_id\Zn"
MSG="$MSG\n\nRestore STATE :\n------"
unset restore_state ; declare -a restore_state
### Table: imgen_product ( 0 ), imgen_ref ( 1 ) and imgen_tac ( 2 )
for key in ${!imgen_table[*]}
do
if [ x"$fline_count" == x"1" ]
then
restore_sql="${record_level}.${imgen_table[$key]}.sql_insert"
else
restore_sql="${record_level}.${imgen_table[$key]}.sql_update"
fi
### Call mysql Command, Restore TAC_ID product informatin ( Record - Row level )
__d_mysql_do_bin_cmd $dbname < $restore_sql
[ $? -eq 0 ] && restore_state[$key]="Success" || restore_state[$key]="Failure"
if [ x"$key" == x"0" ]
then
MSG="$MSG\n ${imgen_table[$key]} \Z1Restore\Zn - [\Z1 ${restore_state[$key]} \Zn]"
elif [ x"$key" == x"1" -o x"$key" == x"2" ]
then
MSG="$MSG\n ${imgen_table[$key]} \Z1Restore\Zn - [\Z1 ${restore_state[$key]} \Zn]"
fi
done
MSG="$MSG\n\nIf it was successful, please press the \Z1Re-retrieve\Zn button to Re-retrieve.\n"
### Call Dialog msgbox UI
__d_dialog_design_msgbox_UI Re-retrieve 15 80
exit_state=$? ; #echo $exit_state ; exit
### Re-retrieve Button
if [ x"$exit_state" == x"0" ]
then
__o_db_operation_input_tac_id_UI updel
fi
fi
fi
fi
}
### __o_db_operation_UI Function - Main Execute Entry
__o_db_operation_connect_UI
}
#------------
# Main
#------
__d_dialog_design_UI
__o_db_operation_UI
原創文章，作者：影·隨行，如若轉載，請注明出處：http://www.www58058.com/8976
呵！膽大心細，總結出來也一定有不小的收獲吧?？偨Y的也非常棒，果斷置頂
@stanley：最早寫了一版 CentOS 5.5 全自動的，后面因為生產環境上開發環境不一致，所以到 6.X 直接改成半自動化了~~整個腳本加測試，調試UI輸出用了整整一個月，－_## 現在不怎么改這個腳本了，越改越多，不敢改了，而且現在沒必要用這種腳本來部署其它環境了，直接用 ansilbe/saltstack 就行了
哈，數據庫那個，是線上的一個小系統，不過數據是挺重要的，他們設計那種BUG都能出現，而且那個手工去查、修正，太費時了，時間要用在點上，我不關心業務系統，只關心數據庫，他們有輸出文檔，知道怎么回事，就用 shell 直接實現了，業務系統有其它人維護，這種重復性工作還是算了~~不case，測試加寫腳本用了一個星期才完成了，腳本用得很好，沒問題哈哈
@影·隨行：贊 堅持
膜拜中！?。?！
真厲害啊