Bash-based MySQL inventory scanner

Question

I am trying to create a bash-based inventory scanner, and was wondering if there is anything 'glaringly' incorrect about my syntax or usage. I was hoping I could get some feedback on:

• bash programming best practice in regards to syntax, modularity, variable passing
• Maybe some book suggestions?
• How can I do it right the first time so I don't have to refactor later?
• Best way to interface MySQL with bash
• Suggestions on debugging (bash -x AND printf?)

Future plans:

Implement a barcode printer and various other functions to manipulate the warehouse inventory....the reason why I am writing this, is because we are currently keeping track of inventory (a few thousand items) using spreadsheets, and meat-based memory. Also might throw it onto a raspi, with a buck converter, battery pack, LCD screen, and WiFi dongle.

The first stab:

#!/bin/bash
# [email protected] 2014
# use it however if you find it useful
# Bash based inventory system
# This is a very simple utility for updating
# MySQL information for item location in a DB
# in a warehouse of inventory system 
#################################################
PASS=1ドル
#############################################
locationlist=( 'MA1' 'MA2' 'MA3' 'MA4' 'MA5' 'MA6' 'MA7' 'MA8'
'MB1' 'MB2' 'MB3' 'MB4' 'MB5' 'MB6' 'MB7' 'MB8' )
scanmodelist=( 'relocate_item' 'relocate_bin' 'set_item_status' )
pretenditems=( '123' '456' )
mysqlhost="localhost"
mysqluser="root"
DBNAME="test"
#############################################
printf "################################ starting ####################\n"
#echo ${scanmodelist[@]} ${locationlist[@]} ${pretenditems[@]}
if [[ -z $PASS ]]; then
 read -s -p 'Please [enter] SQL user password:' PASS
fi
set_scanmode () {
 input=1ドル
 if [ $input == 'relocate_item' ];then
 relocate_item
 elif [ $input == 'relocate_bin' ];then
 relocate_bin
 fi
 echo 'done' $input
}
return_scanner_input () {
 read -p 'scan:' -e input
 echo $input
}
check_input_for_scanmode () {
 for mode in ${scanmodelist[@]}
 do
 if [[ $mode == 1ドル ]]
 then
 mode=1ドル
 # requires: $sku $location
 break
 else
 mode=''
 fi
 done
 echo $mode
}
check_input_for_location () {
 _location=1ドル
 for item in ${locationlist[@]}
 do
 ##printf "[INFO]:check_input_for_location:Comparing: $location With: $item \n"
 if [ $item == $_location ]
 then
 ##printf "[INFO]:check_input_for_location:FOUNDL:$item \n"
 location=$item
 ##printf "[INFO]:check_input_for_location:breaking location \n"
 break
 else
 ##printf "[INFO]:check_input_for_location:NOFOUNDL:$item \n"
 location=''
 fi
 done
 ##printf "[INFO]:check_input_for_location: location:$location \n"
 echo $location
}
set_item_status () {
 echo $sku
 print_sql_row_from_sku $sku
}
relocate_item () {
 while true; do
 clear
 # check if mode and location are none
 # for first scan
 if [[ -z $mode ]]; then
 input=$(return_scanner_input)
 mode=$(check_input_for_scanmode $input)
 elif [[ -z $location ]]; then
 printf "Which location?\n"
 input=$(return_scanner_input)
 location=$(check_input_for_location $input)
 continue
 fi
 printf "Location:$location\n"
 printf "Which sku?\n"
 # if location and mode then grab sku
 input=$(return_scanner_input)
 sku=$(check_input_sku_exists $input)
 # check if sku 
 if [[ -n $sku ]]; then
 #printf "[INFO]:relocate_item: if -n sku: sku:$sku \n"
 # else check if input is 
 else
 _location=$(check_input_for_location $input)
 _mode=$(check_input_for_scanmode $input)
 #printf "[INFO]:relocate_item: else: _location:$_location \n"
 #printf "[INFO]:relocate_item: else: _mode:$_mode \n"
 if [[ -n $_mode ]]; then
 run
 elif [[ -n $_location ]]; then
 location=$_location
 printf "Switched Location:$location\n"
 else
 printf "I didnt find that sku.\n"
 fi
 sleep 1
 continue
 fi
 if [[ -z $sku ]]; then
 exit
 fi
 #printf "[INFO]:relocate_item: 1: sku:$sku location:$location \n"
 print_sql_row_from_sku $sku
 #printf "[INFO]:relocate_item: 2: sku:$sku location:$location \n"
 update_item_location $sku $location
 #printf "[INFO]:relocate_item: 3: sku:$sku location:$location \n"
 print_sql_row_from_sku $sku
 #printf "[INFO]:relocate_item: 4: sku:$sku location:$location \n"
 generate_zint_bar_code $sku
 sleep 1
 done
}
relocate_bin () {
 printf '\nrelocate_bin()\n'
}
update_item_location () {
 # requires: $sku $location
 #printf "[INFO]update_item_location: sku:1ドル location:2ドル \n"
 mysql -h$mysqlhost -u$mysqluser -p$PASS $DBNAME <<EOF
UPDATE item SET location="2ドル" WHERE sku="1ドル";
EOF
 sleep 5
}
check_input_sku_exists () {
 # run mysql check here
 # requires: $sku
 _sku=1ドル
 #printf "\n##############\n[INFO]:check_input_sku_exists:_sku:$_sku \n"
 if [[ -n $( mysql -h$mysqlhost -u $mysqluser -p$PASS $DBNAME <<EOF
SELECT * FROM item WHERE sku="$_sku";
EOF
) ]]
 then
 #printf "\n######\n[INFO]:check_input_sku_exists:_sku: IF true: $_sku \n\n"
 echo $_sku
 else
 ##printf "[INFO]:check_input_sku_exists:_sku: IF false: $_sku \n\n"
 echo ''
 fi
}
print_sql_row_from_sku () {
 # requires: $sku
 #printf "[INFO]:print_sql_row_from_sku:Printing row\n"
 mysql -h$mysqlhost -u$mysqluser -p$PASS $DBNAME <<EOF
SELECT * FROM item WHERE sku="1ドル";
EOF
 sleep 1
}
create_item () {
 printf "\ntrying to insert item: 1ドル\n"
 status="pending"
#sku | title | jnumber | state | load_id | bin_location | msrp | condition_id | band_size | date_listed | upc 
mysql -h$mysqlhost -u$mysqluser -p$PASS $DBNAME <<EOF
INSERT INTO item VALUES ('1ドル',NULL,NULL,'$status',NULL,NULL,NULL,NULL,NULL,NULL,NULL);
EOF
 mysql -h $mysqlhost -u $mysqluser -p$PASS -e "use $DBNAME;DESCRIBE item;"
 mysql -h $mysqlhost -u $mysqluser -p$PASS -e "USE $DBNAME;SELECT * FROM item LIMIT 100"
 printf '\nalso im making a bar code\n'
}
generate_zint_bar_code () {
 # requires: $sku
 mkdir -p ./tmp
 zint -o ./tmp/1ドル.png -d 1ドル
}
run () {
# uncomment these to test
# test_data
# exit
 #
 clear
 location=''
 scanmode=''
 input=''
 sku=''
 mode=''
 printf 'running...\n'
 read -p 'set_scanmode:' -e mode
 set_scanmode $mode
}
test_data () {
sku="example-sky-0123"
location="MB1"
print_sql_row_from_sku $sku
results=$(update_item_location $sku $location)
echo $results
results=$(check_input_sku_exists $sku)
echo $results
}
run
#!/bin/bash
#PASS='password'
#mysql -h localhost -u root -p$PASS -e "create database test"
#mysql -h localhost -u root -p$PASS -e "use test;SHOW tables;"
#mysql -h localhost -u root -p$PASS -e "use test;create table item (state char(25))"
#mysql -h localhost -u root -p$PASS -e "use test;ALTER TABLE item ADD state char(25))"
#mysql -h localhost -u root -p$PASS -e "use test;ALTER TABLE item ADD (state char(25))"
#mysql -h localhost -u root -p$PASS -e "use test;SHOW tables;"
#mysql -h localhost -u root -p$PASS -e "use test;DESCRIBE item;"
#mysql -h localhost -u root -p$PASS -e "use test;INSERT INTO item;VALUES ('the_sku','a title','a jnumber','pending')"
#mysql -h localhost -u root -p$PASS -e "use test;INSERT INTO item VALUES ('the_sku','a title','a jnumber','pending')"
#mysql -h localhost -u root -p$PASS -e "use test;DESCRIBE item;"
#mysql -h localhost -u root -p$PASS -e "use test;SHOW tables;"
#mysql -h localhost -u root -p$PASS -e "SELECT item FROM test LIMIT 2"
#mysql -h localhost -u root -p$PASS -e "USE test;SELECT * FROM item LIMIT 2"

Images/Barcode example and other info on GitHub.

Answer 1

There is a syntax error here:

if [[ -n $sku ]]; then
 #printf "[INFO]:relocate_item: if -n sku: sku:$sku \n"
# else check if input is 
else

The then block is empty and this is invalid.

After you fix that, copy-paste your code on ShellCheck. It will point it many problems. I will focus on the points that may not be obvious from there.

Prefer [[ ... ]] instead of [ ... ]

[[ ... ]] is more modern, easier to use, and more powerful than [ ... ]. You used it in some places, but not everywhere. For example:

if [ $input == 'relocate_item' ];then
 relocate_item
elif [ $input == 'relocate_bin' ];then
 relocate_bin
fi

In this code, you should double-quote $input to prevent globbing and word splitting, as ShellCheck can tell you. Or, you can replace [ ... ] with [[ ... ]], then it's fine.

Also, you don't need to quote bare strings like relocate_item and relocate_bin. Just drop those quotes. The same goes for the quotes on these lines too:

echo 'done' $input
mysqlhost="localhost"
mysqluser="root"
DBNAME="test"

A minor detail, but I prefer to put a space after the ; in ];then.

Clearing variables

You don't need to clear variables like this:

location=''
mode=''

This is enough and easier:

location=
mode=

Running one-line mysql commands

You run one-line mysql commands using here-documents in many places:

mysql -h$mysqlhost -u $mysqluser -p$PASS $DBNAME <<EOF
SELECT * FROM item WHERE sku="$_sku";
EOF

You can do this on one line using here-strings like this:

mysql -h$mysqlhost -u $mysqluser -p$PASS $DBNAME <<< "SELECT * FROM item WHERE sku='$_sku';"

Printing stuff

As ShellCheck tells you, don't embed variables in the format string of printf like this:

printf "something $x and $y\n"

This is the right way:

printf "something %s and %s\n" "$x" "$y"

Instead of echo '', you can write simply echo without parameters, it's the same thing.

• jmunsch: Oh wow! Very neat also of interest as I just caught that empty if statement you were referring to: stackoverflow.com/questions/26897554/…