1

UFCE47-20-1 Lecture 14 2011-12 1

UFCE47-20-1

Systems Development

DBI and Cookies(material mostly by Julia Dawson)

UFCE47-20-1 Lecture 14 2011-12 2

PLEASE turn off mobile

phones, pagers etc. X

UFCE47-20-1 Lecture 14 2011-12 3

HELP is at HAND

UFCE47-20-1 Lecture 14 2011-12 4

DBI and Cookies (1)

• HTTP is a stateless protocol

• How can information be preserved and passed

around?

• E.g. for e-commerce applications?

• Data is stored (made persistent) either

• in a database accessed by a web server

or

• on a web client machine

UFCE47-20-1 Lecture 14 2011-12 5

DBI and Cookies (2)

• DBI - DataBase Interface• Server-side

• Access to persistent data

• E. g. CGI (Common Gateway Interface) applications

• Cookies

• Client-side

• Browser-stored information

UFCE47-20-1 Lecture 14 2011-12 6

E-commerce s/w components

• We’ll be considering an e-commerce application

• Firstly, some aspects of an e-purchase

transaction

• Static data (web pages, logos etc)

• Dynamic data (prices, baskets etc)

• Data exchange - (client/server)

• Security (vital, but outside our remit!)

2

UFCE47-20-1 Lecture 14 2011-12 7

E-Transaction - Components

EIS / RDBMS

Web

Client

D

B

I

HTTP

HTTPS

CGI( Cookies ?? )

SQL = Structured Quuery Language

DBI = DataBase Interface

EIS = Enterprise Information System

RDBMS = Relational DataBase Management System

< SQL >

Web Server

UFCE47-20-1 Lecture 14 2011-12 8

A shopping example

• Consider a straightforward and typical internet shopping session…

• How much of the site is visible to the end user?• Think about what is behind the site as well as the obvious presentation

• What data is static and what is dynamic?• What ‘behind-the-scenes’ data is necessary?• Where will the data be held?• Will all the data be held in an external ‘database’• How will the site interact with the user?

• What functionality is required?• What security is required?

• What skills are required to design and implement such a shopping system?

• Do you have those skills?• How can you acquire those skills?

UFCE47-20-1 Lecture 14 2011-12 9

WildBird Home

UFCE47-20-1 Lecture 14 2011-12 10

Features of WildBird Home

• Home page

• Contact details

• About us

• Clear title bar and page title

• Menu/categories ??dynamic??

• Special offers ++dynamic++

• Images

• NEXT => "Online Shop"

UFCE47-20-1 Lecture 14 2011-12 11

Wildbird - Shop

UFCE47-20-1 Lecture 14 2011-12 12

Wildbird - enlarged image (pop-up)

3

UFCE47-20-1 Lecture 14 2011-12 13

Features of Wildbird - Shop

• New menu

• Consistent look & feel

• Search option ++dynamic++

• Images (thumbnail)

• Clear title

• Checkboxes ++dynamic++

• "Special" info (e.g. sold out) ++dynamic++

• NEXT => Basket

UFCE47-20-1 Lecture 14 2011-12 14

Wildbird - Shopping basket

UFCE47-20-1 Lecture 14 2011-12 15

Features of WB Shopping basket

• Role: Guard page before actual purchase

• Same menu

• Consistent look & feel

• Item options (e.g. remove) ++dynamic++

• Textboxes ++dynamic++

• Buttons - buy (checkout) - refresh (recalculate) -startover (empty) - add to basket (continue)

++dynamic++

• P&P ??dynamic??

• NEXT => "Checkout"

UFCE47-20-1 Lecture 14 2011-12 16

Wildbird - Purchase

UFCE47-20-1 Lecture 14 2011-12 17

Features of WB Purchase

• We have left the main WB site at this point …

• Secure lock icon ON (encryption enabled)

• Option for returning customer to the shop

• Drop down box ++dynamic++

• Textboxes ++dynamic++

• Buttons etc… ++dynamic++

UFCE47-20-1 Lecture 14 2011-12 18

WB - returning customer (order)

4

UFCE47-20-1 Lecture 14 2011-12 19

Cookies (revisited)

• We mentioned cookies briefly in an earlier lecture – this week we’ll actually use them

• Developed by Netscape

• Used because HTTP is a stateless protocol

• Textual information • written by server

• stored on client (by browser) – format depends on browser

• editable

• Extremely useful but …

• …Users may disable them

UFCE47-20-1 Lecture 14 2011-12 20

Cookies and Perl CGI.pm

• A quick and simple cookie based shopping basket can be made using Perl

• CGI.pm documentation

• http://perldoc.perl.org/CGI.html

• ‘Animal Crackers’ example (see next slides) came from – this site appears to now be unaccessible• http://stein.cshl.org/WWW/CGI/examples/cookie.cgi

• Examples of CGI scripts

• http://www.wiley.com/legacy/compbooks/stein/source.html

UFCE47-20-1 Lecture 14 2011-12 21

Zoo/Shopping Basket

UFCE47-20-1 Lecture 14 2011-12 22

Cookie example - the Perl#!/opt/csw/bin/perl -w# http://stein.cshl.org/WWW/CGI/examples/cookie.txt

use CGI qw(:standard); # use CGI

use CGI::Carp(fatalsToBrowser);

%zoo = cookie('animals'); # get the cookie

@new = param('new_animals'); # HTML form processing

# If the action is 'add', then add new animals to the zoo. Otherwise # delete them. foreach (@new) {

if (param('action') eq 'Add') { $zoo{$_}++; } elsif (param('action') eq 'Delete') {

$zoo{$_}-- if $zoo{$_}; # i.e. reduce count if this animal is in the zoodelete $zoo{$_} unless $zoo{$_}; # i.e. delete animal if count now zero

} }# make cookie

$the_cookie = cookie(-name=>'animals', -value=>\%zoo,

-expires=>'+1h');

# MUST BE the FIRST print statement

print header(-cookie=>$the_cookie); ...

UFCE47-20-1 Lecture 14 2011-12 23

EIS - RDBMS - the database(DB)Examples: Oracle; mySQL; SQL ServerWarning: DATABASES ARE NON-STANDARD

• Web Server and the Database (RDBMS) are usually separate entities• Database is maintained by a specialist (DBA)

• Processes communicate with the database usually using SQL (Structured Query Language)

• Programs can translate to and from SQL using an Interface e.g. DBI• Particular DBs use different DataBase Drivers (DBD) but the code

remains unaltered.

• So … Perl's DBI is a neutral interface to many relational databases (and some non-relational ones too – recall the CSV text database shown in week 13)

• Note that some more advanced SQL features may only be available with full relational DBs

UFCE47-20-1 Lecture 14 2011-12 24

Perl DBI - schematic

More Information at http://dbi.perl.org/docs/

NB: Many other Perl DB drivers are available

5

UFCE47-20-1 Lecture 14 2011-12 25

Using CGI Perl and RDBMS

• Now the coding starts to get "interesting"

• CGI programs can write HTML using Perl

• CGI/RDBMS uses:• Perl to write embedded …

• HTML and also …

• SQL

• Plus … the database is managed using transaction statements

• Uses the Perl DBI module for DataBase Interface

UFCE47-20-1 Lecture 14 2011-12 26

DataBase OperationsRelational Databases can handle many connections and many transactions

"simultaneously".

Operations• Connect/Disconnect

• Transaction - Start/Rollback/Checkpoint/Commit

• Prepare Statements

• Execute Statements

• Fetch Data

• Check Errors

UFCE47-20-1 Lecture 14 2011-12 27

• Data is stored in tables of rows of fields

• E.g. a table of second hand cars (called car)

• Add a new car - an Astra - cost 300

A Database Table

Make Model Price

Honda Civic 400

Fiat Punto 700

Ford Astra 120

UFCE47-20-1 Lecture 14 2011-12 28

DBI Simple Example (Not CGI)

Inserting Data into the database

use DBI;

$host = "DBI:mysql:webber:localhost";

# find and connect to database with a name and password

$dbh = DBI->connect($host, 'username', 'passwd')

or die "Can't connect to $dbh->errstr\n";

# define a line of SQL (data to put into database)

$cmd = "insert into car values ('Ford', 'Astra', '300')";

# compile the line of SQL

$update = $dbh->prepare($cmd);

# try to run the operation on the database

$update->execute or die "Error message …";

# free up the connection

$dbh->disconnect;

exit(0);

UFCE47-20-1 Lecture 14 2011-12 29

Get data from a database

• Ask the user to choose a make

• Look for matches in the database

• uses SQL select statement

• for "Ford" should find 2 cars

Astra at 120

Astra at 300

UFCE47-20-1 Lecture 14 2011-12 30

Selecting Data from Databaseuse DBI;

$host = "DBI:mysql:webber:localhost";

# find and connect to database with a name and password

$dbh = DBI->connect($host, 'username', 'passwd')

or die "Can't connect to $dbh->errstr\n";

$sth = $dbh->prepare('SELECT * FROM car WHERE make=?')

or die "Couldn't prepare statement: " . $dbh->errstr;

print "Enter make> ";

chomp ($make = <STDIN>); # Read input from the user

$sth->execute($make) # Execute the query or die

"Couldn't execute statement: " . $sth->errstr;

# Read the matching records and print them out

while (@data = $sth->fetchrow_array()) {

$model = $data[1]; my $cost = $data[2];

print "$model at $cost\n";

}

print "No make matched" if ($sth->rows == 0);

$sth->finish;

$dbh->disconnect;

6

UFCE47-20-1 Lecture 14 2011-12 31

phpMyAdmin

• A useful utility to manage mySQL databases

• CSCT setup – each student has access to a

single private database

• Within their database students can set up and manage multiple tables

• See

http://www.cems.uwe.ac.uk/supportweb/public/page.php?id=1 and search for ‘phpmyadmin’

• Next week I’ll discuss prepackaged

distributions including mySQL

UFCE47-20-1 Lecture 14 2011-12 32

References & Help

• The course book (Bates)

• CGI.pm documentation• http://perldoc.perl.org/CGI.html

• ‘Animal Crackers’ example (see next slides) came from – this site appears to now be unaccessible• http://stein.cshl.org/WWW/CGI/examples/cookie.cgi

• Examples of CGI scripts• http://www.wiley.com/legacy/compbooks/stein/

source.html

• Perl DBI: http://dbi.perl.org/docs/