Databases and the Web - HU Berlin

townripeΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 7 μήνες)

115 εμφανίσεις

Databases

and the
Web

By David Drake

12 noon, October 31, 2001

ITC
-
Research Computing Support Center

244 Wilson Hall, 243
-
8800,

res
-
consult@virginia.edu

Databases and the Web

I.
Databases in the Abstract

II.
Creating Databases using Relational
DataBase Management Systems
(RDBMSs)

III.
Databases and Web Interfaces

I. Databases in the Abstract

A.
Definition

B.
Normalization

C.
Structure

D.
Language

I. A. Definition


A
database

is an organized collection of data
whose content must be
quickly

and
easily


Accessed


Managed


Updated


A
relational database

is one whose data are split
up into
tables
, sometimes called
relations



First Normal Form (
1NF
)


All attributes are single valued &
non
-
repeating


Second Normal Form (
2NF
)


Must be 1NF & must have primary key


Each non
-
primary key attribute must be
functionally
dependent

on primary key


Third Normal form (
3NF
)


Must be 2NF


Each non
-
primary key attribute must be dependent
only

on primary key



I. B. Normalization

(read logical organization)

I. C. 1. Tables (Relations)


Each
column

constitutes an
attribute


Each
row

constitutes a
record

or
tuple

Attribute 1

(column 1)

Attribute 2

(column 2)

Record 1

(tuple 1)

Record 2

(tuple 2)


Primary


An attribute or group of attributes which
uniquely identifies each record in a table


May not be a
Null

value


Foreign


used primarily for enforcing referential
integrity, but also for establishing
relationships

between the two tables

I. C. 2. Keys


One
-
to
-
one (1
-
to
-
1)


One
-
to
-
many (1
-
to
-
M or 1
-
to
-

)


Many
-
to
-
Many (M
-
to
-
M or

-
to
-

)

I. C. 3. Relationships

I. D. Structured Query Language

(SQL)


Pronounce “Sequel” or “Ess Que Ell”


Industry standard language of (Relational)
Databases


Allows for complete


Table Creation, Deletion, Editing


Data extraction (Queries)


Database management & administration





II. Creating Databases

using RDBMSs

A.
Microsoft Access


Creating Tables


Entering, Importing, Editing, & Viewing Data


Defining Relationships


Constructing Queries

B.
MySQL

… (
Documentation
)


Where to put it (servers at UVa)


Ditto MS Access

C.
Others


mSQL, PostGreSQL, Oracle, DB2, Informix, Sybase,
Empress, Adabas, ….

III. Databases and Web Interfaces

(What you need to get started)

A.
Requirements for a Database Web
Interface

B.
Where to Put Your Database and Scripts

C.
Server
-
Side Scripting Languages


ASP


Cold Fusion


Perl


PHP

III. A. Requirements for a Database
Web Interface


Your database (Access, MySQL)


A Web server with appropriate RDBMS


A way of connecting the two

(Common Gateway Interface


CGI


scripts and SQL)


Security concerns

III. B. Where to
Put

Your Database

and Scripts


Academic side of UVa


Access


ESERVICES es
-
web1 (web.virginia.edu)


MySQL


MySQL server (
dbm1.itc.virginia.edu
)


Home directory

(www.people


accessible through blue.unix),
faculty, curry, avery, minerva


www, jm.acs


www.itc(.virginia.edu)


Medical side of UVa


See the
UVa Health System’s Web Development
Center

Active Server Pages (ASP)


When a browser calls an ASP document, the
ASP Server reads the .asp document and

1.
Substitutes appropriate files for the (server
-
side)
include statements

2.
Runs the ASP code (Visual Basic Script


see the
Tutorial and Language Reference
, …)

3.
Returns the resulting HTML code to the browser


Example

(
code
,
copy of database
)

ASP Key Points (1)


ASP code enclosed in: <% VBScript code %>


Everything outside is HTML


The result of the combined HTML and ASP code
must be a “standard” HTML document, e.g.:


<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Final//EN">

<html>

<head>

<title>Miracle Drug Study</title>

<meta http
-
equiv="Content
-
Type" content="text/html; charset=iso
-
8859
-
1">

<meta name="Description" content=""><meta name="Keywords" content="">

<link rel=STYLESHEET type="text/css" href="">

</head>

<body>

</body>

</html>

ASP Key Points (2)


Connect with database:


Create connection object:


set conn = Server.CreateObject("ADODB.Connection")


Open connection:


conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=f:
\
web
\
database
\
rescomp
\
study.mdb")


Submit a (read
-
only) Query:


Generate SQL statement:


SQL = "SELECT FirstName, LastName, DOB, Gender FROM
Patients WHERE Gender = '" & Gender & "' ORDER BY
FirstName DESC"


set Patients = conn.execute(SQL)

ASP Key Points (3)


Move through the data records:


do while NOT Patients.eof


Name = Patients(0) & " " & Patients(1)


Patients.MoveNext

loop


Add to or edit table:


Create and open Record Set object:


set RS = Server.CreateObject("ADODB.Recordset")

RS.Open “
table name
", conn, , adLockOptimistic, adCmdTable

(where
adLockOptimistic

= 3,
adCmdTable

= 2)

ASP Key Points (4)


Add to or edit table (continued):


Create new record, Edit, & Update:


RS.AddNew

RS(“Dosage”) = 200

RS.Update


Or Find desired record, Edit, & Update :


do while NOT RS.eof


if RS(“ID”) = 7 then



RS(“Dosage”) = 200



RS.Update


else



RS.MoveNext


end if

loop

ASP Key Points (5)


Clean up (free server resources) when done:


Queries:


Patients.Close

set Patients = nothing


Record Sets:


RS.Close

set RS = nothing


The Connection:


conn.close

set conn = nothing

ASP Security


Apart from various Internet Information
Services (IIS


Window’s

Web service)
security holes (for viruses and worms),
security is quite good.


Use http
s
:// if you want to protect content
over the internet


provides Secure Socket
Layer (SSL) security

ASP Resources


ITC
-
Training

Workshops


ASP Introduction


Microsoft’s
VBScript Tutorial & Language
Reference

(also
here
)


ITC
-
Desktop’s examples


WebMonkey’s tutorial


ColdFusion


Easy
-
to
-
learn Server
-
Side Scripting Language:
CFML, or Cold Fusion Markup Language, is
embedded in HTML code


CF code is enclosed in or by CF tags:



<CF
tagname

CF code

>


<Cf
tagname

>
CF Code

</Cf
tagname

>


Documents must end in .cfm


ColdFusion is Case
In
sensitive


Example

(
code
,
copy of database
)


ColdFusion Key Points (1)


All #variables# are enclosed in # signs


HTML output which includes of CF
variables must be surrounded by CF output
tags; e.g.:


<Cfset height = “tall”>

<CFoutput>

The <B>#height#</B> boy fell.

<Cfoutput>

ColdFusion Key Points (1)


Connect with database and run query
simultaneously:


<CFQUERY Name="Patients" dbtype="dynamic"
connectstring="#DBdriver# #DBfile#">

SELECT ID, FirstName, LastName

FROM Patients

ORDER BY FirstName

</CFQUERY>


Where the variables are defined beforehand:


<CFset Dbdriver = "Driver={MICROSOFT ACCESS DRIVER (*.mdb)};
UID=admin; PWD=; dbq=">


<CFset Dbfile = "f:
\
web
\
database
\
rescomp
\
study.mdb">

ColdFusion Key Points (2)


Access Query Results


<SELECT name="PatientID">

<CFoutput QUERY="Patients">



<OPTION value=#ID#>#FirstName# #LastName#

</CFoutput>

</SELECT>


Insert Data from a Form


If a HTML form submits variables to be inserted, do so directly
using CFinsert:


<CFinsert tablename="Treatment" dbtype="dynamic"
connectstring="#DBdriver# #DBfile#">


All variables in the form object (e.g. Form.var1) that match
attributes in the table are inserted into the table automatically

ColdFusion Key Points (3)


Insert Data using Cfquery (SQL):


<CFquery name="Treatment" dbtype="dynamic"
connectstring="#DBdriver# #DBfile#">

INSERT into Treatment

VALUES (#PatientID#, #EventID#, Now(), #Dosage(mg)#, #Severity#,
#Time#)

</CFquery>


Other Data editing features also available;
see
documentation

Cold Fusion Resources


ITC
-
Training

Workshops


Cold Fusion Introduction


Allaire/Macromedia’s
Documentation Web page


Developing CF Applications


CFML
Reference


CFML
Quick Reference


ITC
-
Desktop’s examples


WebMonkey’s tutorial


Security links page

Practical Extraction and

Report Language (
Perl
)

a.k.a. Pathologically Eclectic Rubbish Lister


Ubiquitous


Originally designed to be a better general
purpose tool than a Unix shell, it has grown and
spread to be supported from Windows to
Macintosh to VMS.


Powerful but Cryptic


Example

(
code
)

Perl Key Points (1)


The file itself must end in “.cgi” or “.pl”


First
line

must

specify the location of the
Perl engine (The DBI module will not work
for “#!/usr/local/bin /perl[5]”


see below):


#!/uva/bin/perl
-
w


First
printed

line
must

be the following if
you want its response to go to a browser:


print "Content
-
type: text/html
\
n
\
n";


Perl Key Points (2)

Modules


You *must* use the DBI module which
allows you to interface with the database
(see
DBI link 1

&
DBI link 2
)


use DBI;


You can (should?) also make use of the
CGI
module


use CGI;

Perl Key Points (3)


Set the usual parameters:


my $hostname = "dbm1.itc.virginia.edu";

my $username = "dld5s"; # "my" defines a local variable

my $password = "ias!";

my $database = $username . "_study"; # = dld5s_study

my $data_source = "DBI:mysql:$database:$hostname";


Connect to the database:


my $dbh = DBI
-
>connect($data_source, $username, $password)

or die "Can't connect to $data_source: $DBI::errstr
\
n";

Perl Key Points (4)


Define the SQL statement and execute


my $SQL = "SELECT FirstName, LastName, DOB, Gender


FROM Patients


WHERE Gender = '$Gender‘


ORDER BY FirstName DESC";

my $sth = $dbh
-
>prepare($SQL)

or die "Unable to prepare $SQL: dbh
-
>errstr
\
n";

$sth
-
>execute or die "Unable to execute query: $dbh
-
>errstr
\
n";


Clean up


$sth
-
>finish;

$dbh
-
>disconnect;


Perl Security


Perl/MySQL
can

be made secure apart from one
serious flaw as implemented at UVa:


Because web files must be readable by the world (unix
permissions), anyone with an account on the server
where you run the php code can see the code,
including
your MySQL $password!


A couple of exceptions to this flaw are as follows:


If you secure your server so that there are no other users on it


(It
may
** be possible to
compile

Perl scripts into binary
executables using perlcc. Then you must hide or remove your
source code containing the MySQL password)


One other possible poor to fair workaround: use .htaccess to
password protect your php directory (limited access)


See the
passwords link
,
security links page

Perl Resources


ITC
-
Training

Workshops


Perl Programming Introduction


Perl Programming for the Web


Perl Documentation


Overview
,
Built
-
in functions
,
Data types
,
Regular expressions
, …


Modules
:
DBI(1)
,
DBI(2)
,
CGI


WebMonkey’s Tutorial
,
etc.


MySQL and PERL for the Web

by DuBois (New Riders)


Learning Perl

by Schwartz & Christiansen (O’Reilly)


Programming Perl
by Wall, Orwant, & Christiansen
(O’Reilly)


Programming the Perl DBI: Database Programming with
Perl

by Descartes, Bunce, & Mui (Editor) (O’Reilly)

PHP: Hypertext Preprocessor
(
PHP
)


HTML embedding scripting language (see
the
PHP online manual


When a browser calls a PHP document, the
Server reads the PHP document and


Runs the PHP code


Returns the resulting HTML code to the
browser


Example

(
code
)

PHP Key Points (1)


Filename must end in .php or .phtml


PHP code enclosed in <?php PHP code ?>
or <? PHP code ?>


Everything outside is HTML


Output is (generally) to a browser requiring
standard HTML



PHP Key Points (2)

Connecting with RDBMS and editing, adding, and
deleting databases therein are all done through
PHP functions


Connect with MySQL RDBMS


mysql_connect($hostName, $userName, $password) or
die("Unable to connect to host $hostName");


Connect with database


mysql_select_db($dbName) or die("Unable to select
database $dbName");

PHP Key Points (3)

Queries: Nearly all table interaction and
management is done through queries:


Basic information searches


$SQL = "SELECT FirstName, LastName, DOB,
Gender FROM Patients WHERE Gender = '$Gender‘
ORDER BY FirstName DESC";

$Patients = mysql_query($SQL);


Editing, adding, and deleting records and tables


$SQL = "INSERT INTO Patients (FirstName,
LastName) VALUES('$firstName', '$lastName')";

$Patients = mysql_query($SQL);

PHP Key Points (4)


Cleaning up: close the database connection


mysql_close();


PHP/MySQL Security


The same problems as PHP occur with Perl
if you run it as a Perl or CGI script.


See the
passwords link

PHP Resources


ITC
-
Training

Workshops


PHP and MySQL


PHP Documentation


ITC
-
Desktop’s examples


PHP’s Tutorial


WebMonkey’s Tutorial


PHP and MySQL Web Development

by Welling
& Thomson (SAMS)


Beginning PHP4

by Blan, Choi, et. al (Wrox)


(Other) Books


MySQL

by DuBois (New Riders)


MySQL and PERL for the Web

by DuBois (New Riders)


MySQL & mSQL

byYarger, Reese, & King


PHP and MySQL Web Development

by Welling & Thomson
(SAMS)


Beginning PHP4

by Blan, Choi, et. al (Wrox)


Learning Perl

by Schwartz & Christiansen (O’Reilly)


Programming Perl
by Wall, Orwant, & Christiansen (O’Reilly)


Programming the Perl DBI: Database Programming with Perl

by Descartes, Bunce, & Mui (Editor) (O’Reilly)


SQL
-
99 Complete, Really

by Gulutzan & Pelzer (R&D Books)