Databases On The Web with perl Archie Warnock

Databases On The Web with perl Archie Warnock warnock@awcubed. com http: //www. awcubed. com

Arbitration System Overview • The Task: Build an online legal case management system with a Web interface • The Client: An Intergovernmental Organization with little IT experience • Subsystems include: 4 Users, Cases, Documents 4 Parties to Case 4 Panel of judges 4 Case Manager 4 Financial

Requirements • Web interface for filing complaints - submit evidence, edit submitted information, make financial arrangements • High security and confidentiality • Web-based interface for case management • Report generation for judges, arbitration center staff

Architecture

Getting Started with perl DBD • The Toolbox: 4 Linux 4 perl 5 4 An SQL or ODBC database for Linux - Oracle, My. SQL, m. SQL, … 4 DBI/DBD modules & utilities 4 CGI. pm module 4 Text: : Template module

Session Overview • • Connect to database server Do some database stuff Print results into a template Disconnect from database server

Client Configuration # You can load this in a module if ($DB eq $oracle) { $ENV{ORACLE_HOME} = '/<your-path-to-oracle>'; $ENV{ORACLE_SID} = ’ORCL'; $ENV{TWO_TASK} = ’ORCL'; $connect_str ="dbi: $oracle: "; } elsif ($DB eq $mysql) { my $database = “dbname"; my $server = “hostname"; my $port = “port"; $connect_str ="DBI: $mysql: $database: $server: $port"; } else { foo; }

The Database Connection sub connect. To. DB { # Make the connection # Username and password are stored separately $dbh = DBI->connect( $main: : connect_str, $main: : dbuser, $main: : dbpass) or die print "Failed to connect to database server ". $DBI: : errstr ; return $dbh; }

Connect To Database Server • Now, open the connection by calling: $dbh = connect. To. DB(); • Keep track of $dbh - it is the handle for the database connection • It will stay active for a single CGI connection, but it doesn’t seem to be worth the trouble to maintain it across sessions

Do Some Database Stuff - 1 • Non-select statement, single row result $language = ‘perl’; $language = $dbh->quote($language); $sql = qq[INSERT INTO compilers (language) VALUES ($language)]; $result = $dbh->do($sql); if ($result != 1) { # Some error handler here } $dbh->disconnect();

Do Some Database Stuff - 2 • Select statement, single row result $language = ‘perl’; $language = $dbh->quote($language); $sql = qq[ SELECT price FROM compilers WHERE language = $language]; $result = $dbh->selectrow_array($sql);

Do Some Database Stuff - 3 • Select statement, multiple row result $language = ‘perl’; $language = $dbh->quote($language); $sql = qq[ SELECT * FROM compilers]; $sth = $dbh->do($sql); while (@row = $sth->selectrow()) { # some processing, row-by-row }

Fancy Database Stuff • Variable bindings for repeated statements $sth = $dbh->prepare("insert into table(foo, bar, baz) values (? , ? )"); while(<CSV>) { chop; my ($foo, $bar, $baz) = split /, /; $sth->execute($foo, $bar, $baz); }

Print Results • Results are returned in a row, so you can paste them into a table • Use Text: : Template to insert dynamic HTML into static HTML sub Print. Redirect { use Text: : Template; no strict; disable diagnostics; $Title = shift; $Heading = shift; $template = new Text: : Template( TYPE => FILE, SOURCE => "$main: : docroot/logout. tmpl"); print header(-refresh=>"10; URL=$main: : Base. CGI/Login", -type=>'text/html'); $text = $template->fill_in(OUTPUT => *STDOUT); }

A Real Example - perl sub Print. Complainant. List { use Text: : Template; no strict; disable diagnostics; $session = shift; $template = new Text: : Template( TYPE => FILE, SOURCE => "$main: : docroot/parties. tmpl"); # get the list of respondents by case number $dbh = &Database: : connect. To. DB(); if ($dbh) { $Case. No = $dbh->quote($caseno); $sql = qq[ SELECT user_id, email, last FROM claimant_cases WHERE case_id = $Case. No];

A Real Example - More perl $sth = $dbh->prepare($sql); $sth->execute; $Number. Of. Rows = 0; @rows = ""; while (@row = $sth->fetchrow_array) { # Grab the results, row-by-row ($row_head, @data) = @row; $row_head = "<a href="Show. Complainant? Session=$session&Case=$c aseno&Party=$row_head">$row_head</a>"; push(@rows, th($row_head). td(@data)); $Number. Of. Rows++; } &Database: : disconnect. From. DB($dbh);

Real Example - HTML Template <html><head><title>{ $title }</title></head> <body><h 1>{ $heading }</h 1> <form method="post" action="{ $Base. CGI }/New. Case. Menu"> <input type="hidden" name="Session" value="{ $session }"> <input type="hidden" name=”Case" value="{ $caseno }"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> { ($Number. Of. Rows)? $foo='' : $foo=TR([th({align=>'left'}, @col_head), @rows]); $foo } </table></form></body></html>

Summary • perl is (of course) a superb prototyping and production tool • Interfaces to existing databases are easy to build, run well • CGI. pm allows simple scripting • Text: : Template allows you to use site management tools like Front. Page, Dream. Weaver to maintain consistency