Perl for System Administration

Appendix D. The Fifteen-Minute SQL Tutorial

Relational databases can be an excellent tool for system administration. A relational database is accessed and administered using Structured Query Language (SQL) statements. As a result, it is a good idea for system administrators to learn at least the basics of SQL. The goal of this appendix is not to make you a full-time database programmer or even a real database administrator; that takes years of work and considerable expertise. However, we can look at enough SQL so you can begin to fake it. You may not be able to speak the language, but you'll at least get the gist if someone speaks it at you, and you'll know enough to go deeper into the subject if you need to. In Chapter 7, "SQL Database Administration", we'll use these basic building blocks extensively when we integrate SQL and Perl.

SQL is a command language for performing operations on databases and their component parts. Tables are the component parts you'll deal with most often. Their column and row structure makes them look a great deal like spreadsheets, but the resemblance is only surface-level. Table elements are not used to represent relationships to other elements--that is, table elements don't hold formulas, they just hold data. Most SQL statements are devoted to working with the data in these rows and columns, allowing the user to add, delete, select, sort, and relate it between tables.

Let's go over some of the operators offered by SQL. If you want to experiment with the operators we'll be discussing, you'll need access to an SQL database. You may already have access to a server purchased from Oracle, Sybase, Informix, IBM, Microsoft, etc. If not, an excellent open source database called MySQL can be downloaded from http://www.mysql.org.

For this appendix, we'll be using mostly generic SQL, though each database server has its own SQL quirks. SQL statements particular to a specific database implementation will be noted.

The SQL code that follows will be shown using the capitalization standard found in most SQL books. This standard capitalizes all reserved words in a statement.

Most of the example SQL code in this appendix will use a table that mirrors the flat-file machine database we saw in Chapter 5, "TCP/IP Name Services". As a quick refresher, Table D-1 shows how that data looks in table form.

Table D-1. Our Machine Database

D.1. Creating /Deleting Databases and Tables

In the beginning, the server will be empty and void of objects useful to us. Let's create our database:

CREATE DATABASE sysadm ON userdev=10 LOG ON userlog=5
GO

This SQL statement creates a 10MB database on the device userdev with a 5MB log file on the userlog device. This statement is Sybase/Microsoft SQL Server-specific, since database creation (when performed at all) takes place in different ways on different servers.

The GO command is used with interactive database clients to indicate that the preceding SQL statement should be executed. It is not an SQL statement itself. In the following examples, we'll assume that GO will be typed after each individual SQL statement if you are using one of these clients. We'll also be using the SQL commenting convention of "--" for comments in the SQL code.

To remove this database, we can use the DROP command:

DROP DATABASE sysadm

Now let's actually create an empty table to hold the information shown in Table D-1.

USE sysadm
-- Last reminder: need to type GO here (if you are using an interactive
-- client) before entering next statement
CREATE TABLE hosts (
  name      character(30)     NOT NULL,
  ipaddr    character(15)     NOT NULL,
  aliases   character(50)     NULL,
  owner     character(40)     NULL,
  dept      character(15)     NULL,
  bldg      character(10)     NULL,
  room      character(4)      NULL,
  manuf     character(10)     NULL,
  model     character(10)     NULL
)

First we indicate which database (sysadm) we wish to use. The USE statement only takes effect if it is run separately before any other commands are executed, hence it gets its own GO statement.

Then we create a table by specifying the name, datatype/length, and the NULL/NOTNULL settings for each column. Let's talk a little bit about datatypes.

It is possible to hold several different types of data in a database table, including numbers, dates, text, and even images and other binary data. Table columns are created to hold a certain kind of data. Our needs are modest, so this table is composed of a set of columns that hold simple strings of characters. SQL also allows you to create user-defined aliases for datatypes like ip_address or employee_id. User-defined datatypes are used in table creation to keep table structures readable and data formats consistent between columns across multiple tables.

The last set of parameters of our previous command declares a column to be mandatory or optional. If this parameter is set to NOT NULL, a row cannot be added to the table if it lacks data in this column. In our example, we need a machine name and IP address for a machine record to be useful to us, so we declare those fields NOT NULL. All the rest are optional (though highly desirable). There are other constraints besides NULL/NOT NULL that can be applied to a column for data consistency. For instance, one could ensure that two machines are not named the same thing by changing:

name      character(30)     NOT NULL,

to:

name      character(30)     NOT NULL CONSTRAINT unique_name UNIQUE,

We use unique_name as the name of this particular constraint. Naming your constraints make the error messages generated by constraint violations more useful. See your server documentation for other constraints that can be applied to a table.

Deleting entire tables from a database is considerably simpler than creating them:

USE sysadm
DROP TABLE hosts

C.4. Leftovers		D.2. Inserting Data into a Table

Copyright © 2001 O'Reilly & Associates. All rights reserved.