sqlscript

#set -x
rm -rf /tmp/puppet/*
ntd="/tmp/puppet/$(openssl rand -base64 1)"
nti="/tmp/puppet/$(openssl rand -base64 1)"
old_list="/tmp/puppet/$(openssl rand -base64 1)"
new_list="/tmp/puppet/$(openssl rand -base64 1)"
rpass="hungcao"

#### Step 1: Get the file from remote host
#wget -O $new_list  https://raw.githubusercontent.com/caohiephung/rstring/master/user.list.txt
wget -O $new_list  https://raw.githubusercontent.com/caohiephung/rstring/master/10k-user.txt
if [ -z "$(cat $new_list)" ]; then
        echo " ERROR"
else
#### Step 2: Query the table to get current user list 
mysql -umysql -p$rpass -e "select username from mydb.users" | sed 1d > $old_list


#### Step 3: Compare  old.list and user.list
comm -23 --nocheck-order $old_list $new_list > $ntd
comm -13 --nocheck-order $old_list $new_list > $nti


#### ensure that ntd or nti not null
if [ ! -z $"(cat $ntd)" ] && [ ! -z $"(cat $nti)" ]; then

#### Step 4: Run while loop to delete user
while read line_ntd
do
        mysql -umysql -p$rpass -e "delete from mydb.users where username=\"$line_ntd\""
	rm -rf /userhome/$line_ntd
done < $ntd


### Step 5a: Run while loop to insert user
if [ -z "$(mysql -uroot -p123456 -e "select * from mydb.users")" ]; then
i=100000
else
i=$(echo $(mysql -umysql -p$rpass -e "select id from  mydb.users order by id desc limit 1;") | awk '{print $2}')
fi

while read line_nti
do
        let i=$i+1
	j=$(echo $i | cut -c1-3)
	path="/userhome/$j/$line_nti"
        mysql -umysql -p$rpass -e "insert into mydb.users (id,username,path) values ($i,\"$line_nti\",\"$path\" )"
	mkdir -p $path
done < $nti
fi
fi