[20211009]使用bash計算sql語句的sql_id.txt

[20211009]使用bash計算sql語句的sql_id.txt

--//以前寫的只能在命令列輸入sql語句，如果語句很長很明顯這樣的方式不是很好，嘗試支援從包含sql語句的文字檔案來計算sql_id.

$ cat sql_idx.sh
#! /bin/bash
# calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
# argv1 sql statement or sql of text file
# argv2 flag: 0= sql statement 1=sql of text file

odebug=${ODEBUG:-0}
oflag=${2:-0}

if [ $oflag -eq 0 ]
then
    sql_text=${1}'\0'
fi

if [ $oflag -eq 1 ]
then
#   sql_text="$( cat $1 | dos2unix | sed '$s/;\s*//')"'\0'
    sql_text="$( cat $1 | sed "s/
$//" | sed '$s/;\s*//')"'\0'
#   sql_text="$( cat $1 | sed '$s/;\s*//')"'\0'
fi

v1=$(echo -e -n "$sql_text" | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 |  sed   -n  -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z')
v2=${v1:(-16):16}
v3=${v2:(-8):8}
# v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n')
# v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n')

if [ $odebug -eq 1 ] ; then
        echo v1=$v1 v2=$v2 v3=$v3
fi

echo "sql_text = $sql_text"
echo "full_hash_value(16) = $v1 "
echo "hash_value(10) = $(( 16#$v3 )) "

BASE32=($(echo {0..9} {a..z} | tr -d 'eilo'))
res=''
for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n')
do
        res=${res}${BASE32[$(( 10#$i ))]}
done
echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')"
echo "sql_id(32) = $(printf "%013s" $res)"

res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' ))
echo "sql_id(32) = $(printf "%013s" $res1)"

--//注如果安裝dos2unix可以使用它,或者使用
代替.實際上文字oracle做了格式化處理變成unix格式來計算,另外文字最後不能有空行.
--//測試如下:
1.測試1,直接輸入sql語句.
$ ./sql_idx.sh 'select * from dept where deptno=10'
sql_text = select * from dept where deptno=10\0
full_hash_value(16) = 1431C45DBDDBB9E74EAA74D53650F131
hash_value(10) = 911274289
sql_id(32) = 4xamnunv51w9j
sql_id(32) = 4xamnunv51w9j
sql_id(32) = 4xamnunv51w9j

 ./sql_idx.sh 'select * from dept where deptno=10' 0
sql_text = select * from dept where deptno=10\0
full_hash_value(16) = 1431C45DBDDBB9E74EAA74D53650F131
hash_value(10) = 911274289
sql_id(32) = 4xamnunv51w9j
sql_id(32) = 4xamnunv51w9j
sql_id(32) = 4xamnunv51w9j

SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
 911274289 4xamnunv51w9j            0  3650f131

2.測試2,使用檔案aa1.txt:
$ cat -v aa1.txt
select * from dept where deptno=10;                                     ^M

$ ./sql_idx.sh aa 1
sql_text = select * from dept where deptno=10\0
full_hash_value(16) = 1431C45DBDDBB9E74EAA74D53650F131
hash_value(10) = 911274289
sql_id(32) = 4xamnunv51w9j
sql_id(32) = 4xamnunv51w9j
sql_id(32) = 4xamnunv51w9j

SCOTT@book> @ /home/oracle/bin/aa1.txt
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
 911274289 4xamnunv51w9j            0  3650f131

--//Ok正確.

$ cat -v aa1.txt
select * from dept where deptno=10                                    ; ^M

$ ./sql_idx.sh aa1.txt 1
sql_text = select * from dept where deptno=10                                    \0
full_hash_value(16) = 8FA1DE8E2715321F0E36B1BFE045F536
hash_value(10) = 3762681142
sql_id(32) = 0wdpjrzh4bx9q
sql_id(32) =  wdpjrzh4bx9q
sql_id(32) =  wdpjrzh4bx9q
--//我安裝的這個bash shell指令碼有點問題,前面的0不會補上.

SCOTT@book> @ /home/oracle/bin/aa1.txt
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3762681142 0wdpjrzh4bx9q            0  e045f536


3.測試3,文字多行的情況.

$ cat -v aa1.txt
select *^M
 from dept where deptno=10;                                     ^M

$ file aa1.txt
aa1.txt: ASCII text, with CRLF line terminators

 $ ./sql_idx.sh aa1.txt 1
sql_text = select *
 from dept where deptno=10\0
full_hash_value(16) = 1F6FA161B0609741DD5B66C5D8ABB5BB
hash_value(10) = 3635131835
sql_id(32) = duqv6srcarddv
sql_id(32) = duqv6srcarddv
sql_id(32) = duqv6srcarddv

SCOTT@book> @ /home/oracle/bin/aa1.txt
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3635131835 duqv6srcarddv            0  d8abb5bb

--//Ok正確.

$ dos2unix aa1.txt
dos2unix: converting file aa1.txt to UNIX format ...

$ ./sql_idx.sh aa1.txt 1
sql_text = select *
 from dept where deptno=10\0
full_hash_value(16) = 1F6FA161B0609741DD5B66C5D8ABB5BB
hash_value(10) = 3635131835
sql_id(32) = duqv6srcarddv
sql_id(32) = duqv6srcarddv
sql_id(32) = duqv6srcarddv

SCOTT@book> @ /home/oracle/bin/aa1.txt
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3635131835 duqv6srcarddv            0  d8abb5bb