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.