Database Shellscripts Considered Harmful

Why You Should Stop Scripting and Start Designing Applications

As a database application designer and developer I am constantly dismayed to find complex database logic embedded in Unix shell scripts and SQL*Plus scripts. However when I suggest that this is not a good approach, the reaction is often along the lines of "but it works", "it's simple", "everyone uses scripts" and "why the hell not?". A response on one forum was "Haven't you heard of ETL?" suggesting (I think) that OS scripting is the only way to write a warehouse file loading process.

This article is an attempt to explain why I believe that scripting is in general unsuitable for database applications (with a couple of possible exceptions, which I'll mention at the end). It is written with Oracle and shell scripts in mind, but I believe the principles apply equally to any database and any scripting language.

Some problems with scripts

• Managing complexity: there are only limited debugging or profiling tools for scripts

  If the code requires any kind of complexity, there are generally no tools available to help you step through it, view variable values, set breakpoints, examine error stacks, or capture coverage or timing information1 Actually that isn't quite true: Toad 8.5 Professional Edition does include a Script Debugger, and PL/SQL Developer's Command Window allows you to step through a script one command at a time. However, both of these are for SQL*Plus scripts rather than shellscripts, and both are desktop tools, which may present limitations when the script is on the server.
   
  In researching this article I discovered bashdb, although I have never seen this before and I doubt that it is easily available at most development sites. (Perl, and Bash's bashdb, are exceptions).

  Now a well-written shell script is a thing of beauty - I love Korn shell and awk - but these venerable tools were not designed to interact with a database, and scripts that do so quickly become cumbersome and error-prone, and all you have to help you as a developer is set -x and some echo statements.

• Change and maintenance: scripts hide dependency information

  It has probably become second nature for most of us to click on a database object in an IDE such as PL/SQL Developer or TOAD, in order to view the dependencies. What packages use this table? What tables are used by this package? What does this materialized view query? Is it currently valid? If I change this column, what might be affected? All of this information and more is trivial to get out of the database, and of course enormously useful. Now: what SQL*Plus scripts use this table? What other scripts does this script call? What other scripts call it? You have no way of knowing, except by a hit and miss text search of your source code. You may have already encountered such issues when dealing with client-side SQL and dynamic code - and of course, scripts are both.

• Robustness and performance: scripts are essentially dynamic code

  Scripts are files containing arbitrary strings of text that have no connection with the database until you run them, at which point anything can happen. There are several problems with this, such as:

  • The possible performance overhead of recompiling each time they are run. Stored PL/SQL, on the other hand, is compiled in advance and cached in a shareable form on first use.
  • The lack of advance warning if there is something wrong with them. Stored PL/SQL is invalidated by the database as soon as a dependency is altered, and compilation errors can be viewed without executing the code. An invalid procedure will not run.
  • The fact that the commands within the script are not visible from the application (they are in a file on the server), are often dynamically constructed, and will be executed with a less strict set of privileges than is required for stored code, gives rise to some security issues as well as potential runtime errors.

• Stability (1): they are not installed in a schema, so it is harder to predict what they will do

  In an application with multiple schemas, it often makes a difference which schema commands are applied to. Stored code by definition belongs to one schema and (apart from invoker rights procedures) it is known at compilation time which objects will be affected. For scripts, this is often decided only at runtime.

• Stability (2): if they depend on environment variables it is even harder

  I have seen too many scripts whose behaviour was influenced in complex ways by environment variables, sometimes even including or skipping whole sections of code based on the value of $ORACLE_USER or some such variable. This was doubtless intended to add flexibility, but in practice it reduces predictability and stability, while adding complexity. What is the significance of each environment variable? Perhaps there is a really important one that you ought to know about. Where are they set? What if one is missing? Maybe the script will plough on regardless and cause mayhem, perhaps exiting only when it hits a problem (a table it can't drop, perhaps) and leaving the system in an unknown state. An invalid stored procedure would not have run at all.

  One application I worked on had a large, elaborate ETL script that (amongst a great many other things) dropped and rebuilt a number of tables, unless a DROP_TABLES variable was set to "N" in a config file. The location of the config file depended on several variables, and the file itself contained a lot more. That's a lot to be familiar with, and of course you might not be. It's trivial for SQL*Plus scripts to generate and run a list of commands from the data dictionary: for example, what if staging table names are assumed to end in _STG, but this suffix is customisable via a variable, and so the script truncates all tables whose names match '%${STAGING_SUFFIX}'. What happens if the variable is unset? Do you feel lucky? Needing to be familiar with a large number of variables before you can run some code with any confidence is a situation that technical support staff like to avoid.

• Portability: a shell script requires a particular environment to run in, on a Unix server

  A stored PL/SQL procedure can be executed from the desktop - it's trivial to do so with a right-click from an IDE, or a command line in SQL*Plus. In fact, iSQL*Plus makes it possible to call a procedure from any browser or potentially even a handheld device with a suitable network connection.

  Since Oracle runs on Windows PCs, Apple Macs, Linux, Solaris, mainframes and just about everything else besides, stored PL/SQL code is portable between all of those platforms (its model of an installed virtual machine and compiled bytecode is similar to Java's), and since it is stored within the database it is backed up along with your data and any granted permissions. If you move the database to a different server platform, your stored procedures will still work. Chances are your shell scripts won't.

• Security: a script requires a login on the database server

  Following on from the portability point above, executing a stored procedure from a database connection does not require an account on the server, let alone the whole business of opening a terminal window, logging in, navigating to the correct directory, ensuring that the appropriate environment variables are set, and remembering the right command-line arguments. Often the need to be able to run scripts is the single reason why end users require command-line access to the Unix server. Without it, they would not need server accounts at all.

  Often an OS application account is widely used to execute scripts (since this user's environment has been set up and is known to be correct), which means that a number of staff know the password and can in effect log in anonymously.

  Any automated connection to a database requires a user account and usually a password. (OS-authenticated Oracle accounts are of course possible, but in my experience rarely used in practice.) Many scripts either hardcode these within a script or store them as variables in config files, creating an obvious security vulnerability. On Unix systems, a command line such as sqlplus username/password@db @commands.sql can be viewed in full by other users issuing the ps command. There are well-known ways to avoid this of course, but developers do not always think to use them.

  A script can contain any arbitrary commands, including calling other arbitrary scripts (whose location is sometimes not resolved until execution time), and its contents are not visible to users or administrators without opening the file in a text editor. It can be edited by someone who does not have access to a database account, in the knowledge that it will later be executed by someone who does. To a hacker, this presents an easy route into the database.

• Architecture: scripts are a client application

  Client-server development has its advantages but also adds complexity, and therefore requires some careful design, for example regarding where to put business rules. This is something that has to be thought through from an architectural point of view. The fact that scripts are client applications but are usually regarded as part of the server by developers looking for something quick and convenient is something that I find worrying. Would you put complex SQL or PL/SQL in a client application? Well, perhaps. But you know you shouldn't.

• Interface design: scripts encourage high coupling and low cohesion

  Coupling and cohesion are computer science terms that are used to describe interface designs. A tightly coupled interface is one that is tied to the particular circumstances for which it was written and is not generic, while cohesion refers to its scope, with high cohesion being the generally desirable property of doing one clearly defined thing: "it does exactly what it says on the tin", in the immortal words of the Ronseal Woodcare ad campaign. Of course it is possible to write poor code in any language including (perhaps especially) PL/SQL, but it seems that the technique of connecting to SQL*Plus and issuing some more or less dynamic commands lends itself all too easily to the creation of scripts of the ad hoc rag bag "do a bunch of stuff that seems to need doing around this point in the batch" variety, when a more considered solution would have involved defining stored packages or other database objects - in other words, some application design.

On the plus side

Scripts can occasionally be valuable, for example when you need to load multiple OS files and do not know their names in advance - although even then I would try to keep processing within the database if I could, for example by using a stored Java procedure to retrieve the file names, or having an OS scheduled task periodically write the name of the next file to load into a text file, and defining an external table over that. If I did use a script, it would simply load files via SQL*Loader and perhaps call one or more PL/SQL procedures - it would not contain any business rules, and it would be structured so that it either succeeded or failed.

There are doubtless other cases where they are useful (system administration tasks such as backups are another one - and the kind of simple, portable utilities that run in SQL*Plus are of course invaluable), but you should never need a shell script, as part of a production application, to go into SQL*Plus, run queries and make decisions about how to proceed based on the results, because this is to embed business rules in a non-portable, non-visible, dynamic, inefficient, insecure client application. The fact that you see this type of thing a lot does not make it a way to design applications.

Life without scripts

If you have a job scheduler, each task can be implemented as a call to a PL/SQL procedure, without the need for a custom script. DBI-enabled Perl can make a database connection and call a procedure without the need for SQL*Plus, which should make a PL/SQL-only scheduler interface possible. Note particularly that a PL/SQL definer rights procedure (the default kind) will run against the schema it is installed in, even when called by another user. Therefore it is perfectly feasible to have a BATCH or SCHEDULER Oracle user that has no objects of its own or permission to create them, but is simply granted EXECUTE permission on relevant packages, procedures and functions.

From Oracle 9i, loading data can be accomplished by defining external tables, which have a normal table definition plus some additional clauses similar to SQL*Loader controlfile syntax; when you query the table, the OS file is read. This means you can pull data in from within the database rather than having to push it from the OS. It also means you can use all the power of SQL (DISTINCT, ORDER BY, MERGE, joins, analytic functions etc) in one pass through the OS file. You can even define a materialized view encapsulating all this logic, and your database staging process then becomes a matter of refreshing a materialized view. One advantage of this is that Oracle already provides the whole TRUNCATE - INSERT APPEND logic so you don't need to reinvent it, and afterwards the standard dependency and source viewing tools tell you when it was last refreshed and what external table it used.

© William Robertson 2005 www.williamrobertson.net