Databases On The Web with perl

1
Databases On The Web with perl

• Archie Warnock
• warnock_at_awcubed.com
• http//www.awcubed.com

2
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
• Users, Cases, Documents
• Parties to Case
• Panel of judges
• Case Manager
• Financial

3
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

4
Architecture
5
Getting Started with perl DBD

• The Toolbox
• Linux
• perl 5
• An SQL or ODBC database for Linux - Oracle,
  MySQL, mSQL,
• DBI/DBD modules utilities
• CGI.pm module
• TextTemplate module

6
Session Overview

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

7
Client Configuration

• You can load this in a module
• if (DB eq oracle)
• ENVORACLE_HOME '/ltyour-path-to-oraclegt'
• ENVORACLE_SID ORCL'
• ENVTWO_TASK ORCL'
• connect_str "dbioracle"
• elsif (DB eq mysql)
• my database dbname"
• my server hostname"
• my port port"
• connect_str "DBImysqldatabaseserverp
  ort"
• else
• foo

8
The Database Connection

• sub connectToDB
• Make the connection
• Username and password are stored separately
• dbh DBI-gtconnect(
• mainconnect_str,
• maindbuser,
• maindbpass)
• or
• die print "Failed to connect to database
  server " . DBIerrstr
• return dbh

9
Connect To Database Server

• Now, open the connection by calling
• dbh connectToDB()
• Keep track of dbh - it is the handle for the
  database connection
• It will stay active for a single CGI connection,
  but it doesnt seem to be worth the trouble to
  maintain it across sessions

10
Do Some Database Stuff - 1

• Non-select statement, single row result
• language perl
• language dbh-gtquote(language)
• sql qqINSERT INTO compilers
• (language) VALUES (language)
• result dbh-gtdo(sql)
• if (result ! 1)
• Some error handler here
• dbh-gtdisconnect()

11
Do Some Database Stuff - 2

• Select statement, single row result
• language perl
• language dbh-gtquote(language)
• sql qq
• SELECT price FROM
• compilers WHERE
• language language
• result dbh-gtselectrow_array(sql)

12
Do Some Database Stuff - 3

• Select statement, multiple row result
• language perl
• language dbh-gtquote(language)
• sql qq
• SELECT FROM compilers
• sth dbh-gtdo(sql)
• while (_at_row sth-gtselectrow())
• some processing, row-by-row

13
Fancy Database Stuff

• Variable bindings for repeated statements
• sth dbh-gtprepare("insert into
  table(foo,bar,baz) values (?,?,?)")
• while(ltCSVgt)
• chop
• my (foo,bar,baz) split /,/
• sth-gtexecute(foo,bar,baz)

14
Print Results

• Results are returned in a row, so you can paste
  them into a table
• Use TextTemplate to insert dynamic HTML into
  static HTML
• sub PrintRedirect
• use TextTemplate no strict disable
  diagnostics
• Title shift
• Heading shift
• template new TextTemplate( TYPE gt FILE,
  SOURCE gt "maindocroot/logout.tmpl")
• print header(-refreshgt"10
  URLmainBaseCGI/Login", -typegt'text/html')
• text template-gtfill_in(OUTPUT gt
  \STDOUT)

15
A Real Example - perl

• sub PrintComplainantList
• use TextTemplate
• no strict
• disable diagnostics
• session shift
• template new TextTemplate( TYPE gt FILE,
• SOURCE gt "maindocroot/parties.tmpl")
• get the list of respondents by case number
• dbh DatabaseconnectToDB()
• if (dbh)
• CaseNo dbh-gtquote(caseno)
• sql qq
• SELECT user_id, email, last
• FROM claimant_cases
• WHERE case_id CaseNo

16
A Real Example - More perl

• sth dbh-gtprepare(sql)
• sth-gtexecute
• NumberOfRows 0
• _at_rows ""
• while (_at_row sth-gtfetchrow_array)
• Grab the results, row-by-row
• (row_head,_at_data) _at_row
• row_head "lta href\"ShowComplainant?Session
  sessionCasecasenoPartyrow_head\"gtrow_headlt
  /agt"
• push(_at_rows,th(row_head).td(\_at_data))
• NumberOfRows
• DatabasedisconnectFromDB(dbh)

17
Real Example - HTML Template

• lthtmlgtltheadgtlttitlegt title lt/titlegtlt/headgt
• ltbodygtlth1gt heading lt/h1gt
• ltform method"post"
• action" BaseCGI /NewCaseMenu"gt
• ltinput type"hidden" name"Session"
• value" session "gt
• ltinput type"hidden" nameCase"
• value" caseno "gt
• lttable width"100" border"0" cellspacing"0"
  cellpadding"0"gt
• (NumberOfRows)? foo'' fooTR(th(-align
  gt'left',\_at_col_head),_at_rows)
• foo
• lt/tablegtlt/formgtlt/bodygtlt/htmlgt

18
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
• TextTemplate allows you to use site management
  tools like FrontPage, DreamWeaver to maintain
  consistency