Introduction to Web-Based DBMS Technology

1
Introduction to Web-BasedDBMS Technology

• Reading CB, Ch. 29

2
In this lecture you will learn

• the importance of web-based DBMS technology
• Architecture of database connected web
  applications
• PHPMySQL based technology for database connected
  web applications

3
Background to Web-Based DBMSs

• In just over 10 years, the WWW has grown
• from nothing to the world's most important and
  powerful information system,
• with hundreds of millions of users and billions
  of online documents and
• doubling every few years...
• Many businesses now use web-based information
  systems (intranets)
• As architecture of Web was designed to be
  platform-independent,
• can significantly lower deployment and training
  costs
• E-Commerce on the web is growing rapidly
• Data about hourly products, prices, etc better
  stored in databases than in files
• The web is the primary interface to DBMSs
• Web applications make data available globally

4
Basics of WWW

• Web is a very large client-server system
• Connected through routers and switches
• Communicating with TCP/IP protocol
• With no centralised control
• Servers publish pages at URLs
• Clients request pages by specifying the URLs
• Pages are transferred on the web using HTTP
  protocol
• Each HTTP interaction is independent
• No concept of a session

Browser connects to server Requests a page
Server
Client
Server transfers The requested page
5
1990's Client-ServerDBMS Architecture

• Example A high street travel agency
• Application/business code on client machine fat
  client
• Proprietary software - expensive to maintain
  update

• User Interface
• Application Logic

Client
1st Tier
Secure private network

• DB Engine
• Mass Storage

DB Server
2nd Tier
6
From 1995 Three-Tier Architecture

• Example A web-based bookstore . e.g. amazon.co.uk

Thin Client

• Web Browser

1st Tier
Internet
Application Server

• Web Server
• Application Logic

2nd Tier
Internet

• DB Engine
• Mass Storage

DB Server
3rd Tier

• All application/business code off client, onto
  server

7
Characteristics ofThree-Tier Architecture

• Advantages
• Platform independence - web browsers for every PC
• Cheap graphical user interface - potential for
  innovation
• Simplicity - easier to upgrade scale
• Disadvantages
• Costly to maintain server - poor development
  tools
• Less secure (hackers, etc.)
• Less reliable (packet loss)
• Stateless - no built-in support for transactions

8
Static and Dynamic Content

• HTML content stored entirely in files is static
• Most web content is dynamic
• needs to vary with time and users
• E.g. Amazon.co.uk
• Dynamic HTML pages need to be generated for every
  transfer/access
• Dynamic content may come from
• user inputs
• database tables
• Linking databases to web involves creating HTML
  pages on the fly using database query results
• We learn some techniques to generate dynamic HTML
  pages

9
Web Server SoftwareOld Technologies

• Existing Java programs that connect to DBMS can
  be extended to generate dynamic HTML using CGI
• CGI Common Gateway Interface
• CGI is generic and can be used with
• Java, C and other programming languages
• Unix scripts and other scripting languages
• Low-level DB access exploits DB interface
  libraries such as JDBC
• When using CGI with Java
• User input is obtained from HTML forms
• CGI script is invoked when user submits the form
  (see the html form in the example)
• CGI passes user input from the html form to the
  Java class
• as a list of attribute value pairs separated by
  .

10
Example cgi scripting

• This example contains
• An html form to obtain user name as input
• A cgi script for invoking a java class
• A java class
• For processing the user input and
• Printing a Hello World message
• Example only illustrates how cgi facilitates
  linking
• Html forms and Java classes
• Java class does not really connect to a database
• Try adding code for connecting to a database

11
Example HTML form

• ltHTMLgt
• ltHEADgt
• ltTITLEgtHello and Welcome!lt/TITLEgt
• lt/HEADgt
• ltBODYgt
• ltH1 ALIGNCENTERgtHello and Welcomelt/H1gt
• lthrgt
• lt!- helloworld.cgi script is executed when the
  form is submitted --gt
• ltFORM METHOD"POST ACTION"http//cgi.csd.abdn.a
  c.uk/ssripada/cgi_bin/helloworld.cgi"gt
• What is your name?
• ltINPUT TYPE"text" NAME"name" VALUE""gtltpgt
• ltINPUT TYPE"submit" VALUE"Submit"gt ltPgt
• lt/FORMgt

12
Java Code

• import java.io.
• import java.util.StringTokenizer
• public class HelloWorld
• public static void main(String args)
• String input ""
• BufferedReader istream new BufferedReader(new
  InputStreamReader(System.in))//input stream
• Writer ostream new BufferedWriter(new
  OutputStreamWriter(System.out))//output stream
• try
• input istream.readLine()//read the input
  line
• //input is in the form of nameJohn, if the
  user input name John on the html form
• StringTokenizer st new StringTokenizer(input,"
  ")//tokenize the input line
• String name st.nextToken()//retrieve the
  token name and ignore it
• name st.nextToken()//retrieve the required
  token John
• String htmlOutput "ltBgtltIgtHello,
  World!lt/Igtlt/Bgt " "ltbrgt""From nbsp"name//gener
  ate the html content
• String output "Content-type
  text/html""\n\n"htmlOutput//add the header
  information
• try
• ostream.write(output,0,output.length())//w
  rite the output
• ostream.close()
• catch(Exception ex)

13
Cgi script

• Only two lines in helloworld.cgi file
• ! /bin/sh
• java HelloWorld
• Each CGI program runs as a separate process -
  resource-intensive
• Difficult to mix content and functionality
• As in the example you need to create the output
  html file in the java class
• Not easy at times
• Warning each server is configured differently
  for running cgi scripts
• Speak to the sys admin

14
Web Server SoftwareNew Technologies

• There are many new technologies that allow access
  to DBMSs
• Naturally there are advantages and disadvantages
  associated with each of them
• Examples
• Microsoft IIS, ASP - JScript / VBScript
• Sun Microsystems Java - JSP, servlets
• Netscape LiveWire Javascript
• In this course we use
• PHP code embedded in HTML
• To access MySQL databases

15
PHP MySQL

• Stands for PHPHypertext Preprocessor
• Recursive acronym
• Is a scripting language
• Interpreted, not compiled
• Public domain software
• Embedded directly into HTML pages
• Pages are published with .php extension
• Server executes the embedded PHP code every time
  that page is requested
• Home Page www.php.net
• PHPMySQL is a very popular combination for
  producing dynamic web pages
• MySQL - Public domain RDBMS
• Home Page www.mysql.com

16
Web Database Architecture with PHP and MySQL
3
1
2
Browser
Web Server
PHP Engine
MySQL Server
4
6
5

1. Browser issues an HTTP request for a particular
   web page
2. Web server receives the request, retrieves the
   file and passes it to the PHP engine for
   processing
3. PHP engine connects to the MySQL server and sends
   the query
4. MySQL server receives the query, processes it,
   and sends the results back to the PHP engine
5. PHP engine receives the results, prepares the
   HTML page and send it to the web server
6. Web server sends the HTML page to the browser and
   browser displays the page to the user

17
PHPMySQL Programming Model

• Web site made from .php files on web server
• .php files contain HTML with embedded PHP code
• PHP code is enclosed in lt?php ... ?gt
• Basic steps followed in any PHP script used to
  access a database
• Check and filter data coming from the user
• Set up a connection to MySQL server
• Selecting the appropriate database
• Query the database
• Retrieve the results
• Present the results back to the user
• Close the database connection

18
Sample PHP Code

• lthtmlgt
• ltheadgt
• lttitlegtSample Codelt/titlegt
• lt/headgt
• ltbodygt
• lt?php
• db mysql_connect("localhost", "root")
• mysql_select_db(dreamhome",db)
• result mysql_query("SELECT FROM staff",db)
  
• printf("First Name sltbrgt\n", mysql_result(resul
  t,0,"first"))
• printf("Last Name sltbrgt\n", mysql_result(result
  ,0,"last"))
• printf("Address sltbrgt\n", mysql_result(result,0
  ,"address"))
• printf("Position sltbrgt\n", mysql_result(result,
  0,"position"))
• ?gt
• .
• lt/bodygt
• lt/htmlgt

19
Building Web database Applications

• Apply appropriate software engineering life cycle
• Requirements analysis
• Design
• Implementation
• Testing
• Security of data is very important in Web
  Database applications
• Use MySQL privilege system to control access to
  data
• User identification and personalization is
  necessary with web database applications

20
Summary

• Organizations increasingly want data to be
  available over the internet
• Web databases require new technologies to extend
  simple HTTP protocol used on the web
• PHP is a scripting language embedded in html code
  to develop web database applications
• Security is one of the main issues in web
  database applications
• Useful Links
• www.w3.org World Wide Web Consortium
• www.wdvl.com Web Developers Virtual Library
• www.php.net PHP home page
• www.mysql.com mysql home page