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 • PHP+MySQL based technology for database connected web applications Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 ‘&’. Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

11. Example – HTML form <HTML> <HEAD> <TITLE>Hello and Welcome!</TITLE> </HEAD> <BODY> <H1 ALIGN=CENTER>Hello and Welcome</H1> <hr> <!–- helloworld.cgi script is executed when the form is submitted --> <FORM METHOD="POST“ ACTION="http://cgi.csd.abdn.ac.uk/~ssripada/cgi_bin/helloworld.cgi"> What is your name? <INPUT TYPE="text" NAME="name" VALUE=""><p> <INPUT TYPE="submit" VALUE="Submit"> <P> </FORM> <hr> </BODY> </HTML> Dept. of Computing Science, University of Aberdeen

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 name=John, 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 = "<B><I>Hello, World!</I></B> " +"<br>"+"From &nbsp"+name;//generate the html content String output = "Content-type: text/html"+"\n\n"+htmlOutput;//add the header information try{ ostream.write(output,0,output.length());//write the output ostream.close(); }catch(Exception ex){ System.out.println(ex.toString()); } }catch(IOException ex){ System.out.println("Input stream not read properly!"); } } } Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

15. PHP & MySQL • Stands for PHP:Hypertext 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 • PHP+MySQL is a very popular combination for producing dynamic web pages • MySQL - Public domain RDBMS • Home Page: www.mysql.com Dept. of Computing Science, University of Aberdeen

16. Web Database Architecture with PHP and MySQL 3 1 2 Browser Web Server PHP Engine MySQL Server 6 4 5 • Browser issues an HTTP request for a particular web page • Web server receives the request, retrieves the file and passes it to the PHP engine for processing • PHP engine connects to the MySQL server and sends the query • MySQL server receives the query, processes it, and sends the results back to the PHP engine • PHP engine receives the results, prepares the HTML page and send it to the web server • Web server sends the HTML page to the browser and browser displays the page to the user Dept. of Computing Science, University of Aberdeen

17. PHP+MySQL Programming Model • Web site made from “.php” files on web server • “.php” files contain HTML with embedded PHP code • PHP code is enclosed in <?php ... ?> • 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 Dept. of Computing Science, University of Aberdeen

18. Sample PHP Code <html> <head> <title>Sample Code</title> </head> <body> …… <?php $db = mysql_connect("localhost", "root"); mysql_select_db(“dreamhome",$db); $result = mysql_query("SELECT * FROM staff",$db); printf("First Name: %s<br>\n", mysql_result($result,0,"first")); printf("Last Name: %s<br>\n", mysql_result($result,0,"last")); printf("Address: %s<br>\n", mysql_result($result,0,"address")); printf("Position: %s<br>\n", mysql_result($result,0,"position")); ?> …. </body> </html> Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 Developer’s Virtual Library • www.php.net – PHP home page • www.mysql.com – mysql home page Dept. of Computing Science, University of Aberdeen