Bash / MySQL queries…

Reduce connection counts to MySQL by using an array to get many values at once (assuming they’re single ‘word’ values)

I “woke up” and realised I often do this wrong …. (too many connections/individual queries on MySQL) when the query is returning simple values (single word like things) for each field.

As a means of example :

So a before query :

MYSQL="mysql -NB my_database"
IDENTIFIER=some_value

# Obviously $VAR1/$VAR2 can contain spaces etc in this example
VAR1=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
VAR2=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
....

And perhaps a nicer way might look more like :

#!/bin/bash
# Chuck output from a query into an array (retrieve many fields at once)
# coalesce( ... ) copes with a field that may be null.

BITS=( $(echo "SELECT coalesce(field1,200) as field1, field2, field3 FROM some_table WHERE some_key = '${IDENTIFIER}'" | $MYSQL ) )

# and then check we had enough values back
if [ ${#BITS[@] -lt 3 ]; then
echo "handle this error..."
fi
# This will not work if field1/field2/field3 contain spaces.
VAR1=${BITS[0]}
VAR2=${BITS[1]}
VAR3=${BITS[2]}

TP-Link HS110 – controlling from the command line

I have a TP-Link HS110 plug (probably identical to the HS100 … but I thought being able to query it through the app to find out energy usage would be neat …).

Anyway, it originally didn’t seem to let me schedule it through the app, so I dug around and wrote a crap shell script I can prod via cron.

Usage examples:

1. tplink.sh -u my@email.com -p mypassword -o off -> turns the first device found off.
2. tplink.sh -u my@email.com -p mypassword -o on -> turns the first device found on.
3. tplink.sh -u my@email.com -p mypassword -d TpLinkDeviceId -o on -> now for a specific device.
4. tplink.sh -u …. -p …. -d “?” -> dumps device list output …
5. tplink.sh -t tpLinktoken -d DeviceId -o on|off …

Code / download