psa-chapter07.txt

perl语言的经典文章

Example code from Perl for System Administration by David N. Blank-Edelman
O'Reilly and Associates, 1st Edition, ISBN 1-56592-609-9

Chapter Seven
=============

#*
#* connecting/disconnecting to an SQL server using DBI
#*

use DBI;

# connect using to the database named $database using given 
# username and password, return a database handle
$database = "sysadm";
$dbh = DBI->connect("DBI:mysql:$database",$username,$pw);
die "Unable to connect: $DBI::errstr\n" unless (defined $dbh);

$dbh = DBI->connect("DBI:mysql:$database",$username,$pw,{RaiseError => 1});

# <perform tasks>

# tells server you will not need more data from statement handle
# (optional, since we're just about to disconnect)
$sth->finish;  
# disconnects handle from database
$dbh->disconnect;
-------
#*
#* two ways to send commands to an SQL using DBI
#*

$results=$dbh->do(q{UPDATE hosts 
                    SET bldg = 'Main' 
                    WHERE name = 'bendir'});
die "Unable to perform update:$DBI::errstr\n" unless (defined $results)

### OR ###

$sth = $dbh->prepare(q{SELECT * from hosts}) or 
  die "Unable to prep our query:".$dbh->errstr."\n";
$rc = $sth->execute or 
  die "Unable to execute our query:".$dbh->errstr."\n";
-------
#*
#* two examples of placeholder use with DBI
#*

@machines = qw(bendir shimmer sander);
$sth = $dbh->prepare(q{SELECT name, ipaddr FROM hosts WHERE name = ?});
foreach $name (@machines){
  $sth->execute($name);
  #<do-something-with-the-results>
}

### OR ###

$sth->prepare(
   q{SELECT name, ipaddr FROM hosts 
     WHERE (name = ? AND bldg = ? AND dept = ?)});
$sth->execute($name,$bldg,$dept);
-------
#*
#* several ways to retrieve query results using DBI
#*

# the query
$sth = $dbh->prepare(q{SELECT name,ipaddr,dept from hosts}) or
  die "Unable to prepare our query: ".$dbh->errstr."\n";
$sth->execute or die "Unable to execute our query: ".$dbh->errstr."\n";

while ($aref = $sth->fetchrow_arrayref){
  print "name: "   .  $aref->[0] . "\n";
  print "ipaddr: " .  $aref->[1] . "\n";
  print "dept: "   .  $aref->[2] . "\n";
}

### OR ###

while ($href = $sth->fetchrow_hashref){
  print "name: "   .  $href->{name}  . "\n";
  print "ipaddr: " .  $href->{ipaddr}. "\n";
  print "dept: "   .  $href->{dept}  . "\n";
}

### OR ###

$aref_aref = $sth->fetchall_arrayref;
foreach $rowref (@$aref_aref){
  print "name: "   .  $rowref->[0] . "\n";
  print "ipaddr: " .  $rowref->[1] . "\n";
  print "dept: "   .  $rowref->[2] . "\n";
  print '-'x30,"\n";
}

### OR ###

$aref_aref = $sth->fetchall_arrayref;
foreach $rowref (@$aref_aref){
  for ($i=0; $i < $sth->{NUM_OF_FIELDS};i++;){
    print $sth->{NAME}->[$i].": ".$rowref->[$i]."\n";
  } 
  print '-'x30,"\n";
}
-------
#*
#* snippet showing bind_columns from DBI
#*

$sth = $dbh->prepare(q{SELECT name,ipaddr,dept from hosts}) or
  die "Unable to prep our query:".$dbh->errstr".\n";
$rc = $sth->execute or 
  die "Unable to execute our query:".$dbh->errstr".\n";

# these variables will receive the 1st, 2nd and 3rd columns 
# from our SELECT
$rc = $sth->bind_columns(\$name,\$ipaddr,\$dept);

while ($sth->fetchrow_arrayref){
    # $name, $ipaddr, and $dept are automagically filled in from 
    # the fetched query results row
    #<do-something-with-the-results>
}
-------
#*
#* creating a DSN and connecting to a server, disconnecting and removing
#* the DSN using ODBC
#*

# creates a user DSN to a Microsoft SQL Server
# note: to create a system DSN, substitute ODBC_ADD_SYS_DSN 
# for ODBC_ADD_DSN
if (Win32::ODBC::ConfigDSN(
                   ODBC_ADD_DSN, 
                   "SQL Server", 
                   ("DSN=PerlSysAdm", 
                    "DESCRIPTION=DSN for PerlSysAdm", 
                    "SERVER=mssql.happy.edu",  # server name
                    "ADDRESS=192.168.1.4",     # server IP addr
                    "DATABASE=sysadm",         # our database
                    "NETWORK=DBMSSOCN",        # TCP/IP Socket Lib
                   ))){
    print "DSN created\n";
                             }
else {
    die "Unable to create DSN:" . Win32::ODBC::Error() . "\n";
}

# connect to the named DSN, returns a database handle
$dbh=new Win32::ODBC("DSN=PerlSysAdm;UID=$username;PWD=$pw;");
die "Unable to connect to DSN PerlSysAdm:" . Win32::ODBC::Error() . "\n" 
  unless (defined $dbh);

#<perform operations>

$dbh->close();

# replace ODBC_REMOVE_DSN with ODBC_REMOVE_SYS_DSN if you created a system DSN
if (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN,
                           "SQL Server","DSN=PerlSysAdm")){
    print "DSN deleted\n";
}
else {
    die "Unable to delete DSN:".Win32::ODBC::Error()."\n";
}
-------
#*
#* two examples of sending queries to an SQL server using ODBC 
#*

$rc = $dbh->Sql(q{SELECT * from hosts});

### OR ###

if (defined $dbh->Sql(q{UPDATE hosts 
                        SET bldg = 'Main' 
                        WHERE name = 'bendir'})){
    die "Unable to perform update: ".Win32::ODBC::Error()."\n"
}
else {
    $results = $dbh->RowCount();
}
-------
#*
#* two ways to retrieve results from a server using ODBC
#*

if ($dbh->FetchRow()){
  @ar = $dbh->Data(); 
  #<do-stuff-with-@ar-values>
}

### OR ###

if ($dbh->FetchRow()){
   $ha = $dbh->DataHash('name','ipaddr'); 
   #<do-stuff-with-$ha{name}-and-$ha{ipaddr}>
}
-------
#*
#* documented an MySQL server using DBI
#*

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;
-------
#*
#* documenting a Sybase server using DBI
#*

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";
-------
#*
#* documenting an MS-SQL server using ODBC
#*

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