Server Documentation (Perl for System Administration)

7.4. Server Documentation

A great deal of time and energy goes into the configuration of an SQL server and the objects that reside on it. Having a way to document this sort of information can come in handy in a number of situations. If a database gets corrupted and there's no backup, you may be called upon to recreate all of its tables. You may have to migrate data from one server to another; knowing the source and destination configuration can be important. Even for your own database programming, being able to see a table map can be very helpful.

To give you a flavor of the nonportable nature of database administration, let me show you an example of the same simple task as written for three different SQL servers using both DBI and ODBC. Each of these programs does the exact same thing: print out a listing of all of the databases on a server, their tables, and the basic structure of each table. These scripts could easily be expanded to show more information about each object. For instance, it might be useful to show which columns in a table had NULL or NOT NULL set. The output of all three programs looks roughly like this:

---sysadm---
        hosts
                name [char(30)]
                ipaddr [char(15)]
                aliases [char(50)]
                owner [char(40)]
                dept [char(15)]
                bldg [char(10)]
                room [char(4)]
                manuf [char(10)]
                model [char(10)]
---hpotter---
        customers
                cid [char(4)]
                cname [varchar(13)]
                city [varchar(20)]
                discnt [real(7)]
        agents
                aid [char(3)]
                aname [varchar(13)]
                city [varchar(20)]
                percent [int(10)]
        products
                pid [char(3)]
                pname [varchar(13)]
                city [varchar(20)]
                quantity [int(10)]
                price [real(7)]
        orders
                ordno [int(10)]
                month [char(3)]
                cid [char(4)]
                aid [char(3)]
                pid [char(3)]
                qty [int(10)]
                dollars [real(7)]
...

7.4.1. MySQL Server via DBI

Here's a DBI way of pulling this information from a MySQL server. MySQL's addition of the SHOW command makes this task pretty easy:

use DBI;

print "Enter user for connect: ";
chomp($user = <STDIN>);
print "Enter passwd for $user: ";
chomp($pw = <STDIN>);

$start= "mysql"; # connect initially to this database

# connect to the start MySQL database
$dbh = DBI->connect("DBI:mysql:$start",$user,$pw);
die "Unable to connect: ".$DBI::errstr."\n" unless (defined $dbh);

# find the databases on the server 
$sth=$dbh->prepare(q{SHOW DATABASES}) or
  die "Unable to prepare show databases: ". $dbh->errstr."\n";
$sth->execute or
  die "Unable to exec show databases: ". $dbh->errstr."\n";
while ($aref = $sth->fetchrow_arrayref) {
    push(@dbs,$aref->[0]);
} 
$sth->finish;

# find the tables in each database
foreach $db (@dbs) {
    print "---$db---\n";
    
    $sth=$dbh->prepare(qq{SHOW TABLES FROM $db}) or
     die "Unable to prepare show tables: ". $dbh->errstr."\n";
    $sth->execute or
      die "Unable to exec show tables: ". $dbh->errstr."\n";

    @tables=(  );
    while ($aref = $sth->fetchrow_arrayref) {
	    push(@tables,$aref->[0]);
    } 

    $sth->finish;
    
   # find the column info for each table
   foreach $table (@tables) {
	    print "\t$table\n";

	    $sth=$dbh->prepare(qq{SHOW COLUMNS FROM $table FROM $db}) or
          die "Unable to prepare show columns: ". $dbh->errstr."\n";
	    $sth->execute or
          die "Unable to exec show columns: ". $dbh->errstr."\n";

	    while ($aref = $sth->fetchrow_arrayref) {
	        print "\t\t",$aref->[0]," [",$aref->[1],"]\n";
	    } 

       $sth->finish;
   }
}
$dbh->disconnect;

A few quick comments about this code:

We connect to a start database only to satisfy the DBI connect semantics, but this context is not necessary thanks to the SHOW commands. This won't be the case in our next two examples.
If you thought the SHOWTABLES and SHOWCOLUMNS prepare and execute statements looked like excellent candidates for placeholders, you're absolutely right. Unfortunately, this particular DBD driver/server combination doesn't support placeholders in this context (at least not when this book was being written). We'll see a similar situation in our next example.
We prompt for a database user and password interactively because the alternatives (hard coding them into the script or passing them on the command line where they can be found by anyone running a process table dump) are even worse evils. This prompt will echo the password characters as typed. To be really careful, we should use something like Term::Readkey to turn off character echo.

7.4.2. Sybase Server via DBI

Here's the Sybase equivalent. Peruse the code and then we'll talk about a few salient points:

use DBI;

print "Enter user for connect: ";
chomp($user = <STDIN>);
print "Enter passwd for $user: ";
chomp($pw = <STDIN>);

$dbh = DBI->connect('dbi:Sybase:',$user,$pw);
die "Unable to connect: $DBI::errstr\n" 
  unless (defined $dbh);

# find the databases on the server 
$sth = $dbh->prepare(q{SELECT name from master.dbo.sysdatabases}) or
  die "Unable to prepare sysdatabases query: ".$dbh->errstr."\n";
$sth->execute or
  die "Unable to execute sysdatabases query: ".$dbh->errstr."\n";

while ($aref = $sth->fetchrow_arrayref) {
    push(@dbs, $aref->[0]);
}
$sth->finish;

foreach $db (@dbs) {
    $dbh->do("USE $db") or
      die "Unable to use $db: ".$dbh->errstr."\n";
    print "---$db---\n";

    # find the tables in each database
    $sth=$dbh->prepare(q{SELECT name FROM sysobjects WHERE type="U"}) or
      die "Unable to prepare sysobjects query: ".$dbh->errstr."\n";
    $sth->execute or
      die "Unable to exec sysobjects query: ".$dbh->errstr."\n";

    @tables=(  );
    while ($aref = $sth->fetchrow_arrayref) {
        push(@tables,$aref->[0]);
    } 
    $sth->finish;

    # we need to be "in" the database for the next step
    $dbh->do("use $db") or
          die "Unable to change to $db: ".$dbh->errstr."\n";
  
    # find the column info for each table
    foreach $table (@tables) {
	    print "\t$table\n";

       $sth=$dbh->prepare(qq{EXEC sp_columns $table}) or
	       die "Unable to prepare sp_columns query: ".$dbh->errstr."\n";
	    $sth->execute or
	       die "Unable to execute sp_columns query: ".$dbh->errstr."\n";

	    while ($aref = $sth->fetchrow_arrayref) {
	        print "\t\t",$aref->[3]," [",$aref->[5],"(",
                  $aref->[6],")]\n";
	    } 
	    $sth->finish;
    }
}
$dbh->disconnect or
  warn "Unable to disconnect: ".$dbh->errstr."\n";

Here are the promised salient points:

Sybase keeps information on its databases and tables in the special system tables sysdatabases and sysobjects. Each database has a sysobjects table, but the server keeps track of the databases in a single sysdatabases table located in the master database. We use the more explicit databases.owner.table syntax in the first SELECT to unambiguously reference this table. To get at the per-database sysobjects, we could just use this syntax instead of explicitly switching database context with USE. But like cd ing to a directory, this context makes the other queries a little simpler to write.
The SELECT from sysobjects uses a WHERE clause to only return user-defined tables. This was done to limit the size of the output. If we wanted to include all of the system tables too, we would change this to:
```
WHERE type="U" AND type="S"
```
Placeholders in DBD::Sybase are implemented in such a way as to prohibit their use with stored procedures. If this weren't the case, we'd surely use them for the EXEC sp_columns.

7.4.3. MS-SQL Server via ODBC

Finally, here's the code for pulling the same information from a MS-SQL server via ODBC. You'll notice that the actual SQL needed is almost identical to the previous example thanks to the Sybase/MS-SQL connection. The interesting changes between this example and the last are:

The use of a DSN, which also gives us a default database context, so we don't have to be explicit about where to look for the sysdatabases table.
The use of $dbh->DropCursor( ) as the rough analogue to $sth->finish.
The annoying syntax you need to use to execute a stored procedure. See the Win32::ODBC web pages for more detail on dealing with stored procedures and other anomalies like this.

Here's the code:

use Win32::ODBC;

print "Enter user for connect: ";
chomp($user = <STDIN>);
print "Enter passwd for $user: ";
chomp($pw = <STDIN>);

$dsn="sysadm"; # name of the DSN we will be using

# find the available DSNs, creating $dsn if it doesn't exist already
die "Unable to query available DSN's".Win32::ODBC::Error(  )."\n" 
  unless (%dsnavail = Win32::ODBC::DataSources(  ));
if (!defined $dsnavail{$dsn}) {
    die "unable to create DSN:".Win32::ODBC::Error(  )."\n"
      unless (Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, 
				  "SQL Server", 
				  ("DSN=$dsn", 
				   "DESCRIPTION=DSN for PerlSysAdm", 
				   "SERVER=mssql.happy.edu",
				   "DATABASE=master",
				   "NETWORK=DBMSSOCN", # TCP/IP Socket Lib
				  )));
}

# connect to the master database
$dbh = new Win32::ODBC("DSN=$dsn;UID=$user;PWD=$pw;");
die "Unable to connect to DSN $dsn:".Win32::ODBC::Error(  )."\n" 
    unless (defined $dbh);

# find the databases on the server
if (defined $dbh->Sql(q{SELECT name from sysdatabases})){
    die "Unable to query databases:".Win32::ODBC::Error(  )."\n";
}

while ($dbh->FetchRow(  )){
    push(@dbs, $dbh->Data("name"));
}
$dbh->DropCursor(  );

# find the user tables in each database
foreach $db (@dbs) {
    if (defined $dbh->Sql("use $db")){
	     die "Unable to change to database $db:" . 
             Win32::ODBC::Error(  ) . "\n";
    }
    print "---$db---\n";
    @tables=(  );
    if (defined $dbh->Sql(q{SELECT name from sysobjects 
                            WHERE type="U"})){
	    die "Unable to query tables in $db:" . 
            Win32::ODBC::Error(  ) . "\n";
    }
    while ($dbh->FetchRow(  )) {
	    push(@tables,$dbh->Data("name"));
    } 
    $dbh->DropCursor(  );

    # find the column info for each table
    foreach $table (@tables) {
  	    print "\t$table\n";
  	    if (defined $dbh->Sql(" {call sp_columns (\'$table\')} ")){
	        die "Unable to query columns in 
                $table:".Win32::ODBC::Error(  ) . "\n";
	    }
	    while ($dbh->FetchRow(  )) {
	        @cols=(  );
	        @cols=$dbh->Data("COLUMN_NAME","TYPE_NAME","PRECISION");
	        print "\t\t",$cols[0]," [",$cols[1],"(",$cols[2],")]\n";
	    } 
	    $dbh->DropCursor(  );
    }
}
$dbh->Close(  );

die "Unable to delete DSN:".Win32::ODBC::Error(  )."\n"
    unless (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN,
                                "SQL Server","DSN=$dsn"));