Variables and System Interaction

One thing I particularly like in usql is its capability to interact with the system it runs on. In Linux, you can type \! to open an interactive shell. Typing \! followed by a shell command will just execute the command and then return you to the usql prompt. Usql also lets you set and use environmental and also internal variables. For example:

#> \set MY_NAME 'Marco'
#> \set MY_NAME = 'Marco'
#> \unset MY_NAME
#> \setenv NUMBER_OF_CUSTOMERS FOUND_CUSTOMERS

In the first line, the \set meta command defines a variable and assigns a value to it. Without parameters, it shows names and values of all the previously defined variables. The \unset command, of course, deletes the specified variable. The command on the last line is, in my opinion, the most powerful, because it assigns a value (in this case, the current value of an internal variable called FOUND_CUSTOMERS) to an external variable. This feature is an efficient way for a usql script to pass what it finds in a database to whatever other script had called it. The snippet of pseudo code in Listing 1, which may be a part of any Linux shell script using usql to interact with databases, sums up the two methods:

01 usql -f myusqlscript_1 -C -o USQL-OUTPUT.csv
02 usql -f myusqlscript_2
03 echo "The number of customers is $NUMBER_OF_CUSTOMERS"

In Line 1, usql executes the queries found in the file myusqlscript_1 and writes the result, in CSV format, to a file called USQL-OUTPUT.csv. In Line 2, usql executes the contents of the file myusqlscript_2 without creating any file. However, if the file myusqlscript_2 contains this combination of usql commands already:

\gset FOUND_CUSTOMERS
 \setenv NUMBER_OF_CUSTOMERS FOUND_CUSTOMERS

then the shell script of Listing 1 will find the result of the work done by usql inside $NUMBER_OF_CUSTOMERS and will reuse as needed (see line 3). Of course, no data exchange method between usql and shell script is better than the other.

Most of the time, an output file is a more efficient way to store and pass around lots of output, and environment variables may be more convenient to exchange one or a few values.

Like normal shell scripts, usql meta commands can use back ticks, that is, inverted single quotes, to assign the output of a command to a variable. At the usql prompt, or in a usql file, you may write statements like the following:

\echo Welcome `echo $USER`
Welcome marco
=>

but you may also combine this feature with the \set meta command to save values obtained from the shell in some variable:

=> \set MY_NAME `echo $USER`
=> \echo :MY_NAME
marco

The last \echo statement introduces one final feature of usql: variable interpolation. Once you have created a variable and assigned a value to it with \set, you can substitute its value to its name in composite statements, just like you can in shell scripts. All you have to do is prefix the variable name with a colon:

=> \set CURRENT_CUSTOMER marco
=> \set CURRENT_TABLE sales
=> select * from :CURRENT_TABLE where "name" = :'CURRENT_CUSTOMER';
=> \p
=> select * from sales where "name" = 'marco';
=> \g

Usql can handle variable names and interpolated strings – either alone or enclosed in single or double quotes, depending on the SQL syntax of the current database. As long as you place a colon before the name and any quotes, usql will understand that what follows is a variable name that should be replaced with the value of the variable before executing the query.

Conclusion

Usql is a useful tool that lets you interact with several different database systems from a single interface. You can also run scripts and access Linux command line programs. You can even run scripts that let you generate usql command files on the fly. If you work with database systems, I hope this tutorial will encourage you to try out usql, because it could save you a lot of time.

The Author

Marco Fioretti is a freelance author, trainer, and researcher based in Rome, Italy, who has been working with Free and Open Source Software since 1995 – and on open digital standards since 2005. Marco is a Board Member of the Free Knowledge Institute (http://freeknowledge.eu), and he blogs about digital rights at http://stop.zona-m.net.