Transcript PPT

DATABASES
http://www.flickr.com/photos/torkildr/3462607995/
Overview of databases
• Database = structured storage for your data
• Each database can have several tables
Grades
sid
cid
grade
Courses
cid prof
931905000
361
4.0
361
cscaffid
4
70
SE I
931905000
362
3.0
362
agroce
4
70
SE II
931905001
361
4.0
496
cscaffid
4
70
Mobile/Cloud
931905001
362
4.0
931905001
496
931905002
361
cred
cap
title
3.0
Students
sid
onid
name
2.0
931905000
cjones
C. Jones
931905001
amorgan2
A. Morgan
931905002
rholdt
R. Holdt
Setting up a database
• Simple approach: Get an ENGR database
Go to http://onid.oregonstate.edu, log in,
click Web Database on the left. It give you the
sample PHP code to use as well as database
names and passwords.
Database table overview: primary keys
Usually, your table will have a "primary key" – a column that is unique for each row.
Usually, you will use an integer as your primary key.
cid
prof
cred
cap
title
361
cscaffid
4
70
SE I
362
agroce
4
70
SE II
496
cscaffid
4
70
Mobile/Cloud
It is also possible to have a "composite key" – two or more columns
that as a combination are unique for each row.
Other columns in the table
Other columns in the table can be strings (aka "varchars"), ints, etc.
cid
prof
cred
cap
title
361
cscaffid
4
70
SE I
362
agroce
4
70
SE II
496
cscaffid
4
70
Mobile/Cloud
The data types supported vary by database. MySQL supports these…
http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html
Foreign keys
Sometimes, a non-primary column in one table references the primary key
in another table. This is called a "foreign key constraint."
sid
cid
grade
cid
prof
cred
cap
title
931905000
361
4.0
361
cscaffid
4
70
SE I
931905000
362
3.0
362
agroce
4
70
SE II
931905001
361
4.0
496
cscaffid
4
70
Mobile/Cloud
931905001
362
4.0
931905001
496
3.0
931905002
361
2.0
Example Connection
Current ONID Database Settings
Hostname
oniddb.cws.oregonstate.edu
Database Name
scaffidc-db
Username
scaffidc-db
Password
mYRBEDC15SlLJXmv
<?php
$mysqli = new mysqli("oniddb.cws.oregonstate.edu","scaffidc-db","mYRBEDC15SlLJXmv","scaffidc-db");
mysqli_close($mysqli);
?>
Queries
• mysqli object has a query() method
• Use with caution, does nothing to protect
against injection
• Generally you will just provide a query as a
single argument to this function
Queries Continued
• Return values:
– FALSE on failure
– For successful SELECT, SHOW, DESCRIBE or
EXPLAIN queries returns a mysqli_result object
– For other successful queries returns TRUE
Let’s create some tables!
<?php
$mysqli = new mysqli("oniddb.cws.oregonstate.edu","scaffidc-db","mYRBEDC15SlLJXmv","scaffidc-db");
$mysqli->query("drop table grades");
$mysqli->query("drop table students");
$mysqli->query("drop table courses");
/* watch out for, and remove, extra carriage returns below */
if (!$mysqli->query("create table courses(cid integer, prof varchar(64), cred integer, cap integer, title varchar(200),
primary key(cid))")
|| !$mysqli->query("create table students(sid integer, onid varchar(32), name varchar(200), primary key(sid))")
|| !$mysqli->query("create table grades(cid integer, sid integer, grade decimal(3,2), primary key(sid,cid), foreign
key(sid) references students, foreign key(cid) references courses)")
){
printf("Cannot create table(s).\n");
}
$mysqli->close();
?>
Let’s insert some rows!
…
/* watch out for, and remove, extra carriage returns below */
$mysqli->query("insert into courses(cid,prof,cred,cap,title) values(361,'cscaffid',4,70,'SE I')");
$mysqli->query("insert into courses(cid,prof,cred,cap,title) values(362,'agroce',4,70,'SE II')");
$mysqli->query("insert into courses(cid,prof,cred,cap,title) values(496,'cscaffid',4,70,'Mobile/Cloud')");
$mysqli->query("insert into students(sid,onid,name) values(931905000,'cjones','C. Jones')");
$mysqli->query("insert into students(sid,onid,name) values(931905001,'amorgan2','A. Morgan')");
$mysqli->query("insert into students(sid,onid,name) values(931905000,'rholdt','R. Holdt')");
…
/* watch out for, and remove, extra carriage returns above */
Reading all the rows from a table
…
/* watch out for, and remove, extra carriage returns below */
echo "<table>";
if ($result = $mysqli->query("select cid,prof,cred,cap,title from courses")) {
while($obj = $result->fetch_object()){
echo "<tr>";
echo "<td>".htmlspecialchars($obj->cid)."</td>";
echo "<td>".htmlspecialchars($obj->title)."</td>";
echo "<td>".htmlspecialchars($obj->prof)."</td>";
echo "<td>".htmlspecialchars($obj->cred)."</td>";
echo "<td>".htmlspecialchars($obj->cap)."</td>";
echo "</tr>";
}
$result->close();
}
echo "</table>";
…
Updating one row in the table
…
$mysqli->query("update courses set cap=80 where cid = 362");
echo "<table>";
if ($result = $mysqli->query("select cid,prof,cred,cap,title from courses where cid >= 300 and cid <= 400")) {
while($obj = $result->fetch_object()){
echo "<tr>";
echo "<td>".htmlspecialchars($obj->cid)."</td>";
echo "<td>".htmlspecialchars($obj->title)."</td>";
echo "<td>".htmlspecialchars($obj->prof)."</td>";
echo "<td>".htmlspecialchars($obj->cred)."</td>";
echo "<td>".htmlspecialchars($obj->cap)."</td>";
echo "</tr>";
}
$result->close();
}
echo "</table>";
…
Deleting one row in the table
…
$mysqli->query("delete from courses where cid = 362");
echo "<table>";
if ($result = $mysqli->query("select cid,prof,cred,cap,title from courses where cid >= 300 and cid <= 400")) {
while($obj = $result->fetch_object()){
echo "<tr>";
echo "<td>".htmlspecialchars($obj->cid)."</td>";
echo "<td>".htmlspecialchars($obj->title)."</td>";
echo "<td>".htmlspecialchars($obj->prof)."</td>";
echo "<td>".htmlspecialchars($obj->cred)."</td>";
echo "<td>".htmlspecialchars($obj->cap)."</td>";
echo "</tr>";
}
$result->close();
}
echo "</table>";
…
Deleting data when you have foreign keys
• Table X references table Y, delete rows from Y
before rows from X. (e.g., delete Grades before Courses.)
Grades
sid
cid
grade
Courses
cid prof
931905000
361
4.0
361
cscaffid
4
70
SE I
931905000
362
3.0
362
agroce
4
70
SE II
931905001
361
4.0
496
cscaffid
4
70
Mobile/Cloud
931905001
362
4.0
494
cscaffid
4
20
Web dev
931905001
496
3.0
931905002
361
2.0
cred
cap
title
A brief word about security
• DO NOT trust the data from the server
– One of your co-workers might have allowed users to
put evil data into the database!
– You can remove special characters with
htmlspecialchars()
• DO NOT trust the data from the browser
– JS and jQuery form validation can be bypassed!
– Used prepared statements if you need to use data
from the browser in your SQL statements
• Bonus: Potential for performance improvement if you need to repeatedly
execute an SQL statement.
Inserting with a prepared statement
/* Imagine, for a moment, that we are working with data from the browser */
if ($stmt = $mysqli->prepare("insert into courses(cid,prof,cred,cap,title) values(?,?,?,?,?)")) {
for ($i = 100; $i < 300; $i++) {
$cid = $i;
$prof = "Professor X$i";
$cred = 3;
$cap = ($cid < 200 ? 150 : 50);
$title = "CS Course $i";
/* for five params, pass five character types to bind_param with five values */
$stmt->bind_param("isiis", $cid, $prof, $cred, $cap, $title);
$stmt->execute();
}
$stmt->close();
} else {
printf("Error: %s\n", $mysqli->error);
}
Types of prepared statement params
•
•
•
•
i
d
s
b
integer
double
string
blob
Querying with a prepared statement
echo "<table border=1>";
$stmt = $mysqli->prepare("select cid,prof,cred,cap,title from courses where cid >= ? and cid < ?");
$cidmin = 165;
$cidmax = 170;
$stmt->bind_param("ii",$cidmin,$cidmax);
if ($stmt->execute()) {
$stmt->bind_result($cid, $prof, $cred, $cap, $title);
while($stmt->fetch()){
echo "<tr>";
echo "<td>".htmlspecialchars($cid)."</td>";
echo "<td>".htmlspecialchars($title)."</td>";
echo "<td>".htmlspecialchars($prof)."</td>";
echo "<td>".htmlspecialchars($cred)."</td>";
echo "<td>".htmlspecialchars($cap)."</td>";
echo "</tr>";
}
$stmt->close();
}
Walk through another site skeleton
Browse at
http://web.engr.oregonstate.edu/~scaffidc/courses/cs290/skeleton2/
Download at
http://web.engr.oregonstate.edu/~scaffidc/courses/cs290/lectures/site_skeleton2.zip
Good points: Demonstrates PHP-db integration
Bad points: No input validation or data-editing;
same page title on every page
We’re just scratching the surface…
• Other features supported by databases
–
–
–
–
–
–
–
–
Auto-assigned keys (covered later in this course)
Indexes (covered later in this course)
Transactions
Aggregates, grouping, sorting
Views and synthetic/computed columns
Triggers and stored procedures
Import, export, and backup
Replication
• For details, take the database courses
– And/or cover these in your How-To?
Activity
Spend the rest of class working on homework
http://web.engr.oregonstate.edu/~scaffidc/courses/cs290/homework.shtml