The Fifteen-Minute SQL Tutorial: Appendix D - Perl for System Administration

To get at all of the data in our hosts table, use this SQL code:

USE sysadm
SELECT * FROM hosts

This returns all of the rows and columns in the same column order as our table was created:

name      ipaddr        aliases                     owner             dept     
bldg   room manuf      model      
--------- ------------ ---------------------------- ----------------- --------
------ ---- ---------- --------- 
shimmer   192.168.1.11  shim shimmy shimmydoodles   David Davis
Software   
Main  309  Sun        Ultra60    
bendir    192.168.1.3   ben bendoodles              Cindy Coltrane IT        
West  143  Apple      7500/100   
sander    192.168.1.55  sandy micky mickydoo        Alex Rollins IT        
Main  1101 Intergraph TD-325     
sulawesi  192.168.1.12  sula su-lee                 Ellen Monk
Design   
 Main  1116 Apple      G3

If we want to see specific columns, we just need to specify them by name:

USE sysadm
SELECT name,ipaddr FROM hosts

When we specify the columns by name they are returned in the order we specify them, independent of the order used when creating the table. For instance, to see IP addresses per building:

USE sysadm
SELECT bldg,ipaddr FROM hosts

This returns:

bldg       ipaddr          
---------- --------------- 
Main       192.168.1.11    
West       192.168.1.3     
Main       192.168.1.55    
Main       192.168.1.12

Databases wouldn’t be very interesting if you couldn’t retrieve a subset of your data. In SQL, we use the SELECT command and add a WHERE clause containing a conditional:

USE sysadm
SELECT * FROM hosts WHERE bldg="Main"

This shows:

name      ipaddr        aliases                     owner             dept     bldg   room manuf      model      
--------- ------------ ---------------------------- ----------------- -------- ------ ---- ---------- --------- 
shimmer   192.168.1.11  shim shimmy shimmydoodles   David Davis       Software  Main  309  Sun        Ultra60    
sander    192.168.1.55  sandy micky mickydoo        Alex Rollins      IT        Main  1101 Intergraph TD-325     
sulawesi  192.168.1.12  sula su-lee                 Ellen Monk        Design    Main  1116 Apple      G3

The set of available conditional operators for WHERE clauses are the standard programming fare:

=      >      >=      <      <=      <>

Unlike Perl, SQL does not have separate string and numeric comparison operators.

Conditional operators can be combined with AND/OR and negated with NOT. We can test for an empty column using IS NULL or non-empty with IS NOT NULL. For instance, this SQL code will show all of the machines without owners listed in our table:

USE sysadm
SELECT name FROM hosts WHERE owner IS NULL

If you want to find all of the rows that have a column whose contents is one of several specified values, you can use the IN operator to specify a list:

USE sysadm
SELECT name FROM hosts WHERE dept IN ('IT', 'Software')

This shows all of the machines in use in either the IT or software departments. SQL will also allow you to return rows that match a certain range of values (most useful with numeric or date values) with the BETWEEN operator. Here’s an example that shows all of the machines in the main building on the tenth floor:

USE sysadm
SELECT name FROM hosts 
  WHERE (bldg = 'Main') AND 
        (room BETWEEN '1000' AND '1999')

Finally, the WHERE clause can be used with LIKE to choose rows using weak pattern matching (in comparison to Perl’s regular expressions). For instance, this will select all of the machines that have the string “doodles” somewhere in their aliases:

USE sysadm
SELECT name FROM hosts WHERE aliases LIKE '%doodles%'

Table 4.2 lists the supported LIKE wildcards.

Some database servers have added extensions to SQL to allow for regular expression use in SELECTs. For instance, MySQL offers the REGEXP operator for use with SELECT. REGEXP doesn’t have all the power of Perl’s regular expression engine, but it offers a substantial increase in flexibility over the standard SQL wildcards.

Two useful clauses for a SELECT statement are DISTINCT and ORDER BY. The first allows us to eliminate duplicate records returned by a query. If we want a list of all of the distinct manufacturers represented in our hosts table, we could use DISTINCT:

USE sysadm
SELECT DISTINCT manuf FROM hosts

If we want to see our data returned in a sorted order, we can use ORDER BY:

USE sysadm
SELECT name,ipaddr,dept,owner FROM hosts ORDER BY dept

SQL has several operators that can be used to modify the output returned by a query. They allow you to change column names, do summary and intra/intercolumn calculations, reformat how fields are displayed, perform subqueries, and a whole host of other things. Please see an SQL book for more detail on SELECT’s many clause operators.

A new table containing the results of a query can be created on the fly by using an INTO clause on some SQL servers:

USE sysadm
SELECT name,ipaddr INTO itmachines FROM hosts WHERE dept = 'IT'

This statement works just like those we’ve seen previously, except the results of the query are added to another table called itmachines. With some servers, this table is created on the fly if it does not exist. You can think of this operator clause as the equivalent of the “>” operator in most Unix and NT command-line shells.

Some SQL servers allow you to create different views of a table. Views are like magic permanent SELECT queries. Once you create a view using a special SELECT query, the results of your query stick around and behave like their own table. Views can be queried like any other table. Modifications to a view, with a few restrictions, are propagated back to the original table or tables.

Note I said tables. Here’s where the magic of views comes in: a view on a table can be created that consists of a join between that table and another. This view behaves as one large virtual table. Changes to this view are propagated back to the original tables that are part of the join that created the view.

A view can also be created with a new column consisting of calculations performed between other columns in that table, almost like a spreadsheet. Views are useful for more mundane purposes also, like query simplification (i.e., may be able to select fewer columns) and data restructuring (i.e., table users sees a view of the data that doesn’t change, even if other columns in the underlying table structure are modified).

Here’s a view creation example that demonstrates query simplification:

USE sysadm
CREATE VIEW ipaddr_view AS SELECT name, ipaddr FROM hosts

Now we can use a very simple query to get back just the information we need:

USE sysadm
SELECT * FROM ipaddr_view

The result of this query is:

name                           ipaddr          
------------------------------ --------------- 
shimmer                        192.168.1.11    
bendir                         192.168.1.3     
sander                         192.168.1.55    
sulawesi                       192.168.1.12

Like tables, views are dropped using a form of the DROP command:

USE sysadm
DROP VIEW ipaddr_view

In all of the queries we’ve seen above, we’ve asked the server to hand us back all of the results once the query has completed. Sometimes it is preferable to receive the answer to a query one line at a time. This is most often the case when embedding SQL queries in other programs. If your query returns tens of thousands of lines, chances are pretty good that you’ll want to process the results one line at a time, rather than storing them all in memory for later use. Most SQL programming in Perl uses this line-at-a-time method. Here’s a small native-SQL program that demonstrates cursor use on a Sybase or MS-SQL Server:

USE sysadm
-- declare our variables
DECLARE @hostname character(30)
DECLARE @ip character(15)

-- declare our cursor
DECLARE hosts_curs CURSOR FOR SELECT name,ipaddr FROM hosts

-- open this cursor
OPEN hosts_curs

-- iterate over table, fetching rows one at a time, 
-- until we receive an error 
FETCH hosts_curs INTO @hostname,@ip
WHILE (@@fetch_status = 0)
  BEGIN 
     PRINT "----"
     PRINT @hostname
     PRINT @ip
     FETCH hosts_curs INTO @hostname,@ip
  END

-- close the cursor (not strictly necessary when followed 
-- by a DEALLOCATE)
CLOSE hosts_curs

-- undefine cursor def
DEALLOCATE hosts_curs

This produces the following output:

----
shimmer                       
192.168.1.11   
----
bendir                        
192.168.1.3    
----
sander                        
192.168.1.55   
----
sulawesi                      
192.168.1.12

Most database systems allow you to upload SQL code to the server where it is stored in an optimized, post-parsed form for faster execution. These uploads are known as stored procedures. Stored procedures are often a critical component of SQL for administrators because large parts of server administration for some servers rely on them. For example, to change the owner of the sysadm database in Sybase, you might do this:

USE sysadm
sp_changedbowner "jay"

See Chapter 7 for examples of calling stored procedures. Now that you’ve seen the basics of SQL, you’re ready to tackle Chapter 7.