Transcript ppt - kaist
Programming for WWW
(ICE 1338)
Lecture #11
July 30, 2004
In-Young Ko
iko .AT. icu.ac.kr
Information and Communications University (ICU)
Announcements
Your midterm score can be checked from
the class homepage
Average: 81.0
Homework #3 is due by August 4th
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
2
Review of the Previous Lecture
XML-Based Languages
SVG (Scalable Vector Graphics)
Web Servers
Servlets (JSP)
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
3
Contents of Today’s Lecture
XML Addressing and Linking
Database Access on the Web
Relational Database
SQL
Database Access Architectures
MySQL
Perl/MySQL
PHP/MySQL
JDBC/MySQL
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
4
XLink (XML Linking Language)
http://www.w3.org/XML/Linking
A generalization of the HTML link concept
Simple Links
<students xlink:href="students.xml">
The list of students.</students>
Extended Links
<element xmlns:xlink="http://www.w3.org/1999/xlink/namespace/"
xlink:type="extended">
<locator href="Source" role="f"/>
<locator href="Target" role="t"/>
...
</element>
External Link Sets
<annot xmlns:xlink="http://www.w3.org/1999/xlink/namespace/"
role="xlink:external-linkset">
<title>DV's Annotations</title>
<locator href="http://rpmfind.net/veillard/linkset.xml"/>
</annot>
http://daniel.veillard.com/Talks/2000-Linking/slide12-0.html
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
5
XPath
http://www.w3schools.com/xpath/xpath_examples.asp
http://www.w3.org/TR/xpath
A language to address parts of an XML document
<?xml version="1.0"?>
<catalog>
<cd>
<title>Empire Burlesque</title>
<artist>Bob Dylan</artist>
<country>USA</country>
<company>Columbia</company>
<price>10.90</price>
<year>1985</year>
</cd>
<cd>
<title>Hide your heart</title>
<artist>Bonnie Tyler</artist>
<country>UK</country>
<company>CBS Records</company>
<price>9.90</price>
<year>1988</year>
</cd>
…
</catalog>
July 30, 2004
Selecting elements in an absolute path
• /catalog
• /catalog/cd/price
• /catalog/cd[price>10.80]
Selecting elements in different levels
• //cd
Selecting elements by matching patterns
• /catalog/cd/*
• /catalog/*/price
• /*/*/price
Selecting branches
• /catalog/cd[1]
• /catalog/cd[last()]
Selecting attributes
• //cd[@country='UK']
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
6
XPointer
http://www.w3.org/XML/Linking
Defines the fragment identifier syntax for XML
resources
Is based on XPath (extension of XPath)
Returns a set of nodes, points or ranges within the
document
e.g., #xpointer(id("foo"))
xpointer(/chapter[3]/elem[@name="foo"])
http://daniel.veillard.com/Talks/2000-Linking/slide12-0.html
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
7
Relational Databases
A database is a collection of data organized to
allow relatively easy access for retrievals,
additions, and deletions
A relational database is a collection of tables of
data, each of which has one special column that
stores the primary keys of the table
Rows are sometimes called entities
AW lecture notes
http://coronet.iicm.edu/Dbase1/reldb_p.htm
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
8
Relational DB Example
Designing a relational database for used
Corvettes that are for sale
The table could have information about various
equipment the cars could have
Use a separate table for state names, with only
references in the main table
Logical Model:
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
9
Relational DB Example (cont.)
The Corvettes table
The States table
The CorvettesEquipment crossreference table
The Equipment table
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
10
SQL (Structured Query Language)
A standard language to create, query, and
modify databases
Supported by all major database vendors
More like structured English than a
programming language
We cover only six basic commands:
CREATE TABLE, SELECT, INSERT,
UPDATE, DELETE, and DROP
SQL reserved words are case insensitive
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
11
Table Creation
The CREATE TABLE command:
CREATE TABLE table_name (
column_name1 data_type constraints,
…
column_namen data_type constraints)
There are many different data types (INTEGER, FLOAT,
CHAR(length), …)
There are several constraints possible, e.g., NOT NULL,
PRIMARY KEY
e.g., CREATE TABLE States (
State_id INTEGER PRIMARY KEY NOT NULL,
State CHAR(20))
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
12
SELECT & INSERT
The SELECT Command – Used to specify queries
e.g., SELECT Body_style FROM Corvettes
WHERE Year > 1994
The INSERT Command:
INSERT INTO table_name (col_name1, … col_namen)
VALUES (value1, …, valuen)
The correspondence between column names and
values is positional
e.g., INSERT INTO Corvettes(Vette_id, Body_style,
Miles, Year, State)
VALUES (37, 'convertible', 25.5, 1986, 17)
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
13
UPDATE & DELETE
The UPDATE Command – To change one or more
values of a row in a table
UPDATE table_name
SET col_name1 = value1,…, col_namen = valuen
WHERE col_name = value
The WHERE clause is the primary key of the row to be
updated
e.g., UPDATE Corvettes SET Year = 1996
WHERE Vette_id = 17
The DELETE Command
July 30, 2004
e.g., DELETE FROM Corvettes WHERE Vette_id = 27
The WHERE clause could specify more than one row of
AW lecture notes
the table
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
14
Delete Tables and Databases
The DROP Command – To delete whose
databases or complete tables
DROP (TABLE | DATABASE) [IF EXISTS] name
e.g., DROP TABLE IF EXISTS States
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
15
Joins
A Join is an operation to build a temporary table by
combining columns from different tables
e.g., Retrieve all cars that have CD players
SELECT Corvettes.Vette_id, Corvettes.Body_style,
Corvettes.Miles, Corvettes.Year, Corvettes.State
FROM Corvettes, Equipment
WHERE Corvettes.Vette_id = Corvettes_Equipment.Vette_id
AND Corvettes_Equipment.Equip = Equipment.Equip_id
AND Equipment.Equip = 'CD'
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
16
Architectures for Database Access
Client-Server (two-tier) Architectures
Client tasks:
Provide a way for users to submit queries
Run applications that use the results of queries
Display results of queries
Server tasks:
Client
Implement a data manipulation language, which
can directly access and update the database
Because the relative power of clients has
grown considerably, we could shift
processing to the client, but then
maintaining data integrity is difficult
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
DB
Server
AW lecture notes
17
Architectures for Database Access
(Cont.)
A Three-tier System
For Web-based database access, the middle
tier can run applications
The middle tier provides Web-based access to
a database
Client just gets results
Client
Middle Tier
e.g., Web Browser
e.g., Web Server &
Server Applications
DB
Server
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
18
Embedded SQL
SQL commands are embedded in programs
written in a host programming language, whose
compiler is extended to accept some form of SQL
commands
e.g., int a;
EXEC SQL SELECT gpa INTO :a
FROM Student WHERE SID=2001234;
printf("The GPA is %d\n", a);
Advantage: One package has computational support of
the programming language, as well as database access
with SQL
Disadvantage:Portability among database systems
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
19
Database Access Mechanisms
Microsoft Access Architecture
A tool to access any common database structure
Provides an API for a set of objects and methods that
are an interface to different databases
The Perl DBI/DBD Architecture
July 30, 2004
Database Interface (DBI) provides methods & attributes
for generic SQL commands
Database Driver (DBD) is an interface to a specific
database system (MySQL, Oracle, etc.)
Convenient for Web access to databases, because the
Perl program can be run as CGI on the Web server
system
AW lecture notes
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
20
Database Access Mechanisms
PHP & Database Access
An API for each specific database system
Also convenient for Web access to databases, because
PHP is run on the Web server
The Java JDBC Architecture
JDBC is a standard protocol that can be implemented
as a driver for any database system
JDBC allows SQL to be embedded in Java applications,
applets, and servlets
JDBC has the advantage of portability over embedded
SQL
A JDBC application will work with any database system
for which there is a JDBC driver
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
21
The MySQL Database System
A free, efficient, widely used SQL implementation
Available from http://www.mysql.org
Logging on to MySQL (starting it):
mysql [-h host] [-u username] [database name] [-p]
The given database name becomes the focus of
MySQL
Database focus can be changed by the use
command:
e.g., use cars;
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
22
MySQL Commands
To create a new database
e.g., CREATE DATABASE cars;
To create a database table
e.g., CREATE TABLE Equipment
(Equip_id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY,
Equip INT UNSIGNED);
To see the tables of a database:
SHOW TABLES;
To see the description of a table (columns):
e.g., DESCRIBE Corvettes;
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
23
Database Access with Perl/MySQL
Needed:
DBI – a standard object-oriented module
DBD – for the specific database system
DBI Module
Interface is similar to Perl’s interface to external
files – through a filehandle
To provide access to DBI and create a DBI
object: use DBI;
Access to the object is through the
reference variable, DBI
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
AW lecture notes
24
Perl/MySQL (cont.)
To connect to the database:
$dbh = DBI->connect(
"DBI:driver_name:db_name
[, username] [, password]);
e.g., $dbh = DBI->connect("DBI:mysql:cars");
The connect method is usually used with die
e.g., $dbh = DBI->connect("DBI:mysql:cars") or
die("Could not connect!");
A Perl program can have connections to any
number of databases
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
25
Perl/MySQL (cont.)
To create a query, we usually compile the SQL
command first, then use it against the database
e.g., $sth = $dbh->prepare(
"SELECT Vette_id, Body_style, Year, States.State
FROM Corvettes, States
WHERE Corvettes.State = States.State_id
AND States.State = 'California'");
To execute a compiled query, use execute, as in:
e.g., $sth->execute() or
die "Error –query: $dbh->errstr\n";
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
26
Perl/MySQL Example
access_cars.pl
#!/usr/bin/perl -w
use DBI;
use CGI ":standard";
print header();
print start_html("CGI-Perl MySQL database access");
my $dbh = DBI->connect("DBI:mysql:cars", "root", "");
my $query = param("query");
print "<p> <b> The query is: </b>", $query, "</p>";
my $sth = $dbh->prepare($query);
$sth->execute or
die "Error - unable to execute query: $dbh->errstr\n";
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
27
Perl/MySQL (cont.)
The $sth object keeps the result of a query
To display the results, we would like column
names, which are stored in a hash
$col_names = $sth->{NAME};
Rows of the result are available with the
fetchrow_array method, which returns a
reference to an array that has the next row
of the result
Returns false if there are no more rows
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
28
Perl/MySQL (cont.)
Putting query results in an HTML document
can cause trouble (>, <, “, and &)
Avoid the problem by using the CGI function,
escapeHTML
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
29
Perl/MySQL Exampleaccess_cars.pl
print "<table> <caption> <h2> Query Results </h2> </caption>", "<tr align = 'center'>";
my $col_names = $sth->{NAME};
foreach $field_name (@$col_names) {
print "<th> $field_name </th>";
}
print "</tr>";
while (@result_rows = $sth->fetchrow_array) {
print "<tr align = 'center'>";
while ($#result_rows >= 0) {
$field = shift @result_rows;
$field = escapeHTML($field);
print "<td> $field </td>";
}
print "</tr>";
}
print "</table>";
$sth->finish;
$dbh->disconnect;
print end_html();
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
30
PHP/MySQL Example access_cars.php
<html>
<head><title> Access the cars database with MySQL </title></head>
<body>
<?php
$db = mysql_connect("localhost", "root", ""); // Connect to MySQL
if (!$db) {
print "Error - Could not connect to MySQL"; exit;
}
$er = mysql_select_db("cars"); // Select the cars database
if (!$er) {
print "Error - Could not select the cars database"; exit;
}
trim($query);
print "<p> <b> The query is: </b> " . $query . "</p>";
$result = mysql_query($query); // Execute the query
if (!$result) {
print "Error - the query could not be executed";
$error = mysql_error();
print "<p>" . $error . "</p>";
exit;
}
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
31
PHP/MySQL Example access_cars.php
print "<table><caption> <h2> Query Results </h2> </caption>";
print "<tr align = 'center'>";
$num_rows = mysql_num_rows($result);
$row = mysql_fetch_array($result);
$num_fields = sizeof($row);
while ($next_element = each($row)) { // Produce the column labels
$next_element = each($row);
$next_key = $next_element['key'];
print "<th>" . $next_key . "</th>";
}
print "</tr>";
for ($row_num = 0; $row_num < $num_rows; $row_num++) {
reset($row);
print "<tr align = 'center'>";
for ($field_num = 0; $field_num < $num_fields / 2; $field_num++)
print "<th>" . $row[$field_num] . "</th> ";
print "</tr>";
$row = mysql_fetch_array($result);
}
print "</table>";
?>
</body></html>
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
32
Database Access with JDBC/MySQL
Approaches to using JDBC outside the Web
JDBC is a Java API for database access
The API is defined in the java.sql package
Can use a two-tier configuration
Disadvantage: Every client must have a driver
for every database vendor
Can also use a three-tier configuration
The application runs on the client side, the
middle machine runs JDBC, and the third
system runs the database system
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
33
JDBC/MySQL
Connecting the application to the driver
The getConnection method of DriverManager, which
select the correct driver from those that are registered
The general form of a reference to a database for the
connection operation is:
jdbc:subprotocol_name:more_info
The “subprotocol” specifies the driver (e.g., odbc, mysql)
The “more info” part depends on the specific database being used
e.g., For MySQL and the cars database,
myCon = DriverManager.getConnection(
"jdbc:mysql://localhost/cars?user=root");
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
34
JDBC/MySQL
SQL commands through JDBC
Statement myStmt = myCon.createStatement();
SQL commands are String objects
e.g., final String sql_com = "UPDATE Corvettes " +
"Year = 1991 WHERE Vette_id = 7";
The action commands are executed with the
executeUpdate method of Statement
e.g., myStmt.executeUpdate(sql_com);
Returns the number of affected rows
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
35
JDBC/MySQL
A SELECT is executed by sending it as the actual
parameter to the executeQuery method of
Statement
The executeQuery method returns an object of class
ResultSet
Get rows from ResultSet with next iterator
e.g., ResultSet result;
final String sql_com = "SELECT * FROM Corvettes
WHERE Year <= 1990"
result = myStmt.executeQuery(sql_com);
while(result.next()) {
String aCol = result.getString(2);
String style = result.getString("Body_style");
}
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
36
JDBC/MySQL Metadata
Metadata - to get table and column names
from a database
Two kinds of metadata:
Metadata that describes the database
DatabaseMetaData dbmd = myCon.getMetaData();
Metadata that describes a ResultSet object
ResultSetMetaData resultMd = result.getMetaData();
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
37
JDBC/MySQL Database Metadata
String tbl[] = { "TABLE" };
DatabaseMetaData dbmd = myCon.getMetaData();
result = dbmd.getTables(null, null, null, tbl);
System.out.println("The tables in the database are: \n\n");
while (result.next()) {
System.out.println(result.getString(3));
}
Output from this:
The tables in this database are:
CORVETTES
CORVETTES_EQUIPMENT
EQUIPMENT
STATES
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
AW lecture notes
38
JDBC/MySQL Result Metadata
// Create an object for the metadata
ResultSetMetaData resultMd = result.getMetaData();
// Loop to fetch and display the column names
for (int i = 1; i" <= resultMd.getColumnCount(); i++) {
String columnName = resultMd.getColumnLabel(i);
System.out.print(columnName + "\t");
}
System.out.println("\n");
Output:
Vette_id Body_style Miles Year State
AW lecture notes
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
39
Web-based DB References
Introduction to Relational Database Design:
http://www.edm2.com/0612/msql7.html
XML representation of a relational database:
http://www.w3.org/XML/RDB.html
July 30, 2004
Programming for WWW (Lecture#11)
In-Young Ko, Information Communications University
40