ch7.PDF

Size: px

Start display at page:

Download "ch7.PDF"

到依尔根觉罗
4 years ago
Views:

1 283

2 Perl SQL D B D :: O D B C D B D :: S y b a s e

3 2 8 5

4 2 8 6 D B D :: O r a c l e

5 2 8 7 D B D :: S y b a s e D B D :: S y b a s e D B D :: S y b a s e D B D :: P r o x y D B D :: P r o x y D B D :: O D B C u n i x O D B C i O D B C

6 DBI use DBI; # $database # $database = "sysadm"; $dbh = DBI->connect("DBI:mysql:$database",$username,$pw); die "Unable to connect: $DBI::errstr\n" unless (defined $dbh); D B D :: m y s q l c o n n n e c t () c o n n e c t () R a i s e E r r o r P r i n t E r r o r $dbh = DBI->connect("DBI:mysql:$database", $username,$pw,{raiseerror => 1); c o n n e c t () d i e

7 2 8 9 q s o m e t h i n g q {s o m e t h i n g $results=$dbh->do(q{update hosts SET bldg = 'Main' WHERE name = 'bendir'); die "Unable to perform update:$dbi::errstr\n" unless (defined $results); $ r e s u l t s u n d e f S E L E C T p r e p a r e e x e c u t e $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"; p r e p a r e () p r e p a r e () e x e c u t e () q \ ' \ "

8 2 9 0 p r e p a r e () p r e p a r e () p r e p a r e d () d o () p r e p a r e () e x e c u t e () d o e x e c u t e ( ) 0 E 0-1 p r e p a r e () p r e p a r e () e x e c u t e = qw(bendir shimmer sander); $sth = $dbh->prepare(q{select name, ipaddr FROM hosts WHERE name =?); foreach $name (@machines){ $sth->execute($name); f o r e a c h W H E R E S E L E C T $sth->prepare( q{select name, ipaddr FROM hosts WHERE (name =? AND bldg =? AND dept =?)); $sth->execute($name,$bldg,$dept);

9 2 9 1 S E L E C T S E L E C T e x e c u t e () S E L E C T f e t c h r o w _ a r r a y r e f ( ) u n d e f f e t c h r o w _ a r r a y ( ) f e t c h r o w _ h a s h r e f ( ) f e t c h a l l _ a r r a y r e f ( ) u n d e f $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"; f e t c h r o w _ a r r a y r e f () while ($aref = $sth->fetchrow_arrayref){ print "name: ". $aref->[0]. "\n"; print "ipaddr: ". $aref->[1]. "\n"; print "dept: ". $aref->[2]. "\n";

10 2 9 2 f e t c h r o w _ h a s h r e f () f e t c h r o w _ a r r a y r e f () f e t c h r o w _ h a s h r e f () while ($href = $sth->fetchrow_hashref){ print "name: ". $href->{name. "\n"; print "ipaddr: ". $href->{ipaddr. "\n"; print "dept: ". $href->{dept. "\n"; f e t c h a l l _ a r r a y r e f () f e t c h r o w _ a r r a y r e f ( ) $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";

11 2 9 3 $ r o w r e f - > [ 0 ] $ s t h - > { N U M _ O F _ F I E L D S $ s t h - > { N A M E $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"; # # $sth->finish; # $dbh->disconnect; DBI

12 2 9 4 b i n d _ c o l () i n d _ c o l u m n s () b i n d _ c o l u m n s () $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"; # SELECT $rc = $sth->bind_columns(\$name,\$ipaddr,\$dept); while ($sth->fetchrow_arrayref){ # $name $ipaddr $dept ODBC use Win32::ODBC;

13 2 9 5 # MS-SQL Server user DSN # system DSN ODBC_ADD_DSN ODBC_ADD_SYS_DSN if (Win32::ODBC::ConfigDSN( ODBC_ADD_DSN, "SQL Server", ("DSN=PerlSysAdm", "DESCRIPTION=DSN for PerlSysAdm", "SERVER=mssql.happy.edu", # "ADDRESS= ", # IP "DATABASE=sysadm", # "NETWORK=DBMSSOCN", # TCP/IP Socket Lib ))){ print "DSN created\n"; else { die "Unable to create DSN:". Win32::ODBC::Error(). "\n"; W i n 3 2 : : O D B C

14 2 9 6 # DSN $dbh=new Win32::ODBC("DSN=PerlSysAdm;UID=$username;PWD=$pw;"); die "Unable to connect to DSN PerlSysAdm:". Win32::ODBC::Error(). "\n" unless (defined $dbh); d o () p r e p a r e () e x e c u t e () W i n 32 :: O D B C S q l () W i n 32 :: O D B C W i n 32 :: O D B C n e w $rc = $dbh->sql(q{select * from hosts); S q l () u n d e f d o () u n d e f I N S E R T D E L E T E U P D A T E R o w C o u n t () W i n 32 :: O D B C e x e c u t e () R o w C o u n t () W i n 3 2 : : O D B C p r e p a r e ( ) S q l ( )

15 2 9 7 d o () 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(); S E L E C T W i n 32 :: O D B C F e t c h R o w () u n d e f D a t a () F e t c h R o w () D a t a () D a t a () D a t a H a s h () f e t c h r o w _ h a s h r e f () D a t a H a s h () f e t c h r o w _ h a s h r e f () D a t a H a s h

16 2 9 8 if = if ($dbh->fetchrow()){ $ha = $dbh->datahash('name','ipaddr');... $ha{name $ha{ipaddr... { N A M E W i n 32 :: O D B C F i e l d N a m e s () { N U M _ O F _ F I E L D S F i e l d N a m e s () $dbh->close(); # system DSN ODBC_REMOVE_DSN ODBC_REMOVE_SYS_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";

17 N U L L ---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)]

18 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)] DBI MySQL Server use DBI; print "Enter user for connect: "; chomp($user = <STDIN>); print "Enter passwd for $user: "; chomp($pw = <STDIN>); $start= "mysql"; # # $start MySQL $dbh = DBI->connect("DBI:mysql:$start",$user,$pw); die "Unable to connect: ".$DBI::errstr."\n" unless (defined $dbh); # $sth=$dbh->prepare(q{show DATABASES) or die "Unable to prepare show databases: ". $dbh->errstr."\n";

19 3 0 1 $sth->execute or die "Unable to exec show databases: ". $dbh->errstr."\n"; while ($aref = $sth->fetchrow_arrayref) { push(@dbs,$aref->[0]); $sth->finish; # 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: ". while ($aref = $sth->fetchrow_arrayref) { push(@tables,$aref->[0]); $sth->finish; # 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;

20 3 0 2 S H O W $ s t a r t c o n n e c t ( ) SHOW TABLES SHOW COLUMNS T e r m :: R e a d k e y DBI Sybase Server 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); # $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";

21 3 0 3 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"; # $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: while ($aref = $sth->fetchrow_arrayref) { push(@tables,$aref->[0]); $sth->finish; # " " $dbh->do("use $db") or die "Unable to change to $db: ".$dbh->errstr."\n"; # 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";

22 3 0 4 $sth->finish; $dbh->disconnect or warn "Unable to disconnect: ".$dbh->errstr."\n"; S E L E C T d a t a b a s e s. o w n e r. t a b l e U S E S E L E C T W H E R E W H E R E WHERE type="u" AND type="s" D B D : : S y b a s e EXEC s p_columns $ s t h = $ d b h- > p r e p a r e (q { S E L E C T n a m e F R O M sy s o b j e c t s W H E R E t y p e = " U " ) " U "

23 ODBC MS-SQL Server $ d b h - > D r o p C u r s o r ( ) $ s t h - > f i n i s h W i n 3 2 : : O D B C use Win32::ODBC; print "Enter user for connect: "; chomp($user = <STDIN>); print "Enter passwd for $user: "; chomp($pw = <STDIN>); $dsn="sysadm"; # DNS # DSNs $dsn 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 )));

24 3 0 6 # $dbh = new Win32::ODBC("DSN=$dsn;UID=$user;PWD=$pw;"); die "Unable to connect to DSN $dsn:".win32::odbc::error()."\n" unless (defined $dbh); # 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(); # user-defined foreach $db (@dbs) { if (defined $dbh->sql("use $db")){ die "Unable to change to database $db:". Win32::ODBC::Error(). "\n"; print 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(); # foreach $table (@tables) { print "\t$table\n"; if (defined $dbh->sql(" {call sp_columns (\'$table\') ")){ die "Unable to query columns in

25 3 0 7 $table:".win32::odbc::error(). "\n"; while 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")); 7.5 use DBI; # syaccreate < > $admin = 'sa'; print "Enter passwd for $admin: "; chomp($pw = <STDIN>); $user=$argv[0]; # # * * $genpass = reverse join('',reverse split(//,$user)); $genpass.= "-" x (6-length($genpass));

26 3 0 8 # SQL # SQL # 1 create the database on the USER_DISK device, with the log on USER_LOG # 2 add a login to the server for the user, making the new database the default. # 3 switch to the newly created database # 4 change its owner to be this = ("create database $user on USER_DISK=5 log on USER_LOG=5", "sp_addlogin $user,\"$genpass\",$user", "use $user", "sp_changedbowner $user"); # $dbh = DBI->connect('dbi:Sybase:',$admin,$pw); die "Unable to connect: $DBI::errstr\n" unless (defined $dbh); # for (@commands) { $dbh->do($_) or die "Unable to $_: ". $dbh->errstr. "\n"; $dbh->disconnect; $ d b h - > d o () use DBI; # syacdelete < > $admin = 'sa'; print "Enter passwd for $admin: "; chomp($pw = <STDIN>); $user=$argv[0]; # SQL

27 3 0 9 # # drop the user's server = ("drop database $user", "sp_droplogin $user"); # $dbh = DBI->connect('dbi:Sybase:',$admin,$pw); die "Unable to connect: $DBI::errstr\n" unless(defined $dbh); # for (@commands) { $dbh->do($_) or die "Unable to $_: ". $dbh->errstr. "\n"; $dbh->disconnect or warn "Unable to disconnect: ". $dbh->errstr. "\n"; 7.6

28 d l ddddddd 15.23%/5MB hpotter %/5MB ddddddd 15.23%/5MB dumbledore %/5MB dddddddd 16.48%/5MB hgranger %/5MB ddddddd 15.23%/5MB rweasley l 3.40%/5MB ddddddddddddddddddddddddddd 54.39%/2MB hagrid no log use DBI; $admin = 'sa'; print "Enter passwd for $admin: ";

29 3 1 1 chomp($pw = <STDIN>); $pages = 2; # 2k # $dbh = DBI->connect('dbi:Sybase:',$admin,$pw); die "Unable to connect: $DBI::errstr\n" unless (defined $dbh); # $sth = $dbh->prepare(q{select name from 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) { # size $size = &querysum(qq{select size FROM master.dbo.sysusages WHERE dbid = db_id(\'$db\') AND segmap!= 4); # size $logsize = &querysum(qq{select size FROM master.dbo.sysusages WHERE dbid = db_id(\'$db\') AND segmap = 4); # $dbh->do(q{use $db) or die "Unable to change to $db: ".$dbh->errstr."\n"; # reserved_pgs # " " doampg " " ioampg

30 3 1 2 # 2KB $used=&querysum(q{select reserved_pgs(id,doampg)+reserved_pgs(id,ioampg) FROM sysindexes WHERE id!= 8); # $logused=&querysum(q{select reserved_pgs(id, doampg) FROM sysindexes WHERE id=8); # &graph($db,$size,$logsize,$used,$logused); $dbh->disconnect; # / SELECT sub querysum { my($query) = shift; my($sth,$aref,$sum); $sth = $dbh->prepare($query) or die "Unable to prepare $query: ".$dbh->errstr."\n"; $sth->execute or die "Unable to exec $query: ".$dbh->errstr."\n"; while ($aref=$sth->fetchrow_arrayref) { $sum += $aref->[0]; $sth->finish; $sum; # sub graph { my($dbname,$size,$logsize,$used,$logused)

31 3 1 3 # print ' 'x15. ' '.'d'x (50 *($used/$size)). ' 'x (50-(50*($used/$size))). ' '; # MB printf("%.2f",($used/$size*100)); print "%/". (($size * $pages)/1024)."mb\n"; print $dbname.'-'x(14-length($dbname)).'- '.(' 'x 49)." \n"; if (defined $logsize) { # print ' 'x15. ' '. 'l'x (50 *($logused/$logsize)). ' 'x (50-(50*($logused/$logsize))). ' '; # MB printf("%.2f",($logused/$logsize*100)); print "%/". (($logsize * $pages)/1024)."mb\n"; else { # print ' 'x15. " - no log".(' 'x 41)." \n"; print "\n"; q u e r y s u m () S U M S U M q u e r y s u m () S U M ()

32 3 1 4 D I S T I N C T SQL Server CPU use DBI; $syadmin = "sa"; print "Sybase admin passwd: "; chomp($sypw = <STDIN>); $msadmin = "sa"; print "MS-SQL admin passwd: "; chomp($mspw = <STDIN>); # Sybase Server

33 3 1 5 $sydbh = DBI->connect("dbi:Sybase:server=SYBASE",$syadmin,$sypw); die "Unable to connect to sybase server: $DBI::errstr\n" unless (defined $sydbh); # ChopBlanks $sydbh->{chopblanks = 1; # MS-SQL Server DBD::Sybase $msdbh = DBI->connect("dbi:Sybase:server=MSSQL",$msadmin,$mspw); die "Unable to connect to mssql server: $DBI::errstr\n" unless (defined $msdbh); # ChopBlanks $msdbh->{chopblanks = 1; $ =1; # STDOUT I/O # signal handler " " $SIG{INT = sub {$byebye = 1;; # while (1) { last if ($byebye); # sp_monitor $systh = $sydbh->prepare(q{sp_monitor) or die "Unable to prepare sy sp_monitor:".$sydbh->errstr."\n"; $systh->execute or die "Unable to execute sy sp_monitor:".$sydbh->errstr."\n"; # # cpu_busy while($href = $systh->fetchrow_hashref or $systh->{syb_more_results) { # last if (defined $href->{cpu_busy); $systh->finish; # % for (keys %{$href) {

34 3 1 6 $href->{$_ =~ s/.*-(\d+%)/\1/; # $info = "Sybase: (".$href->{cpu_busy." CPU), ". "(".$href->{io_busy." IO), ". "(".$href->{idle." idle) "; # MS-SQL Server $mssth = $msdbh->prepare(q{sp_monitor) or die "Unable to prepare ms sp_monitor:".$msdbh->errstr."\n"; $mssth->execute or die "Unable to execute ms sp_monitor:".$msdbh->errstr."\n"; while($href = $mssth->fetchrow_hashref or $mssth->{syb_more_results) { # last if (defined $href->{cpu_busy); $mssth->finish; # % for (keys %{$href) { $href->{$_ =~ s/.*-(\d+%)/\1/; $info.= "MSSQL: (". $href->{'cpu_busy'." CPU), ". "(".$href->{'io_busy'." IO), ". "(".$href->{'idle'." idle)"; print " "x78,"\r"; print $info,"\r"; sleep(5) unless ($byebye); # $sydbh->disconnect; $msdbh->disconnect;

35 3 1 7 Sybase: (33% CPU), (33% IO), (0% idle) MSSQL: (0% CPU), (0% IO), (100% idle) s p _ m o n i t o r s p _ m o n i t o r last_run current_run seconds Aug :05AM Aug :05AM 1 cpu_busy io_busy idle (0)-0% 0(0)-0% 40335(0)-0% packets_received packets_sent packet_errors (0) 1635(0) 0(0) total_read total_write total_errors connections (0) 180(0) 0(0) 11(0) s p _ m o n i t o r s p _ m o n i t o r D B D :: S y b a s e while($href = $systh->fetchrow_hashref or $systh->{syb_more_results) { last if (defined $href->{cpu_busy); I N T

36 3 1 8 s p _ m o n i t o r 7.7 D B I D B D :: m y s q l D B D :: S y b a s e W i n 32 :: O D B C SQL DBI

37 ODBC W i n 32 :: O D B C W i n 32 :: O D B C D B D :: S y b a s e

38 3 2 0

untitled

untitled OO 1 SQL Server 2000 2 SQL Server 2000 3 SQL Server 2000 DDL 1 2 3 DML 1 INSERT 2 DELETE 3 UPDATE SELECT DCL 1 SQL Server 2 3 GRANT REVOKE 1 2 1 2 3 4 5 6 1 SQL Server 2000 SQL Server SQL / Microsoft SQL