SOEN 287

About This Presentation

Title:

SOEN 287

Description:

SOEN 287 – PowerPoint PPT presentation

Number of Views:57

Avg rating:3.0/5.0

Slides: 48

Provided by: crai77

Category:

more less

Transcript and Presenter's Notes

Title: SOEN 287

1
SOEN 287

Back-end Storage
Chapter 10 Knuckles

The Data Tier
We discuss back-end data storage for Web
applications, relational data, and using the
MySQL database server for back-end storage for
Web applications.
Persistent data (as opposed to transient state
data) on the back-end of a Web application can be
stored in structured text files -- often called
flat files.

But it is cumbersome to maintain data in this
format. This is basically a table of data
"flattened" into a file.

Such data is usually stored in a database table.

Terminology Record -- A row in the table. In
this case we see the data for each customer is a
record. Key -- A column of the table. Also
called a field. (If you look at the flat file
version of the table on the previous slide, you
can see that each field looks like a hash the
whose key is the name of the field.) Primary key
-- a key which is guaranteed to have a unique
value for each record. Thus, the primary key
uniquely identifies each record.
4

Relational data refers to tables which share
relationships.

The primary key in the orders table is orderID.
The two tables are related through the shared
key custID.
Given a custID, all of the data for a given
customer, including all of the details about
their order(s), can be retrieved from the
related tables.

Database servers are like Oracle, Access, MySQL,
and mSQL are daemons which listen for queries
(read/write/search) on the data and send back any
results (success/failure/records matching
searches).
Database servers work on data retrieval and
manipulation on a level "behind" the http server.
This is the notion of a three-tier Web
application.

Large commercial Web applications may use a
whole farm of servers on each level. Small Web
applications might run the database and http
daemons on the same box. Either way, the concept
is the same.

The middle tier (http server(s)) is sometimes
called the information assembly tier. The idea
is that this tier transforms the raw data records
into human consumable information.
For this reason, http servers are sometimes
called information servers.
Note that caching in the middle tier is
important. Think of the results of a search
engine query. The data tier is queried only
once, and the results are cached in the middle
tier. Then, a request for the next 10 search
results, for example, simply pulls from the data
cached in the middle tier.
We discuss search result caching in Chapter 12.
As discussed in Chapter 18, XML being used more
commonly for more robust middle tier caching
needs.

Technically, the term database refers to a
collection of tables.
The database itself has a name and so does each
table in the database.

A database system (or database engine or
database software) is responsible for the binary
storage format of the data.
A programmer needn't be concerned with the
underlying storage format. We issue high level
SQL (Structured Query Language) commands to the
database software, which then handles the
lower-level read/write/query actions on the data.
Thus, we only need to think of the data in its
tabular format.
The easiest way to manipulate the data tables
(e.g. issue SQL commands) is through a GUI, which
most sophisticated database systems provide.

Clearly a Web application can't use a GUI to
interface with a database system, so we focus on
issuing SQL commands from within a Perl program.

10
(No Transcript)
11

Steps for using a database in a Web application
Steps 1-3 are typically done once by an
administrator.
Database daemon must be running.
A database must be created (using a GUI or by
issuing an administrative command from within a
program).
User/Password permissions must be set for the
database.
Steps 4-5 can be performed repetitively by the
Web app.
Connect to the database. The DBI (database
interface) Perl module will automate the process
of interfacing with the database software.
Manipulate the database Create tables, add
records to existing tables, search for records
which match some search criteria, delete records,
etc.

Structured Query Language (SQL)
Created by IBM in the 1970's.
Now an international standard -- ANSI SQL last
major standardization in 1992.
Nearly all database systems accept SQL queries.
(Oracle, Sybase, Informix, DB2, Access, MySQL,
mSQL, PostgresSQL, just to name a few)
We present a small subset of SQL sufficient to
add database support for Web applications.
For purposes of examining the SQL language, we
discuss "raw" SQL statements. Later, we will see
how to use the DBI module to connect to a
database and how to issue SQL commands from Perl
statements.

Create a new table with NO rows. This simply
defines the nature of columns (keys) for the
table.
CREATE TABLE table_name
(
Column_name_1 data_type_1,
Column_name_2 data_type_2
.
.
.
)
Keywords in SQL are NOT case sensitive.
A standard convention is to type SQL keywords in
upper-case. We will adhere to that convention.

The data types we will use
CHAR(length) -- A character string of specified
length. The string is right-padded with spaces
and stored with length number of characters (i.e.
fixed-width column)
VARCHAR(length) -- a character string of
specified length, not right-padded with spaces
(i.e. variable width column)
INTEGER
REAL
Note most database systems require that length
be in the range 1-255.

15
Example CREATE TABLE customers ( custID
VARCHAR(5), last VARCHAR(20), first
VARCHAR(20), age INTEGER, purchases REAL )
16

Insert a record into an existing table
INSERT INTO customers
VALUES
('33', 'Doe', 'John', 30, 15.75)
Equivalent statement where we can give the keys
and values in any order.
INSERT INTO customers
(age, purchases, first, last, custID)
VALUES
(30, 15.75, 'John', 'Doe', '33')
Note The extra whitespace in the statements is
for readability, not a syntax requirement.

It is recommended to quote strings using single
quotes, although most database systems also
accept double quotes.
(30, 15.75, 'John', 'Doe', '33')
Use two single quotes to escape a single quote.
Most database systems also accept \'.
INSERT INTO customers
(age, purchases, first, last, custID)
VALUES
(30, 15.75, 'Miles', 'O''Brien', '33')

The string O'Brien
18

Selecting records and sub-tables
Specify to select a sub-table by giving a list
of keys (or for all keys) where the selected
sub-table is subject to some criteria.
SELECT comma-delimited column_names or
FROM table_name
WHERE criteria
Applied to the customers table (three slides
back), this would return a sub-table with one
column and two rows.
SELECT custID
FROM customers
WHERE age gt 34
Note Best to give a handout containing the table.

Applied to the customers table, this would
return a sub-table with two columns and one row.
SELECT last, first
FROM customers
WHERE custID '12'
The criterion can contain simple pattern
matches.
SELECT last
FROM customers
WHERE last LIKE 'La'
The stands for any string of 0 or more
characters. So this statement searches for last
names that begin with the string 'La'. In MySQL,
the match is NOT case sensitive. Use LIKE BINARY
for case-sensitive matching. The negation of
LIKE is NOT LIKE.

AND/OR logic can be incorporated in queries.
This selects all columns matching the criterion
-- the whole table in this case.
SELECT
FROM customers
WHERE age gt 18 AND age lt 49
Note ltgt is the syntax for not equals.
Order (ORDER BY) the returned sub-table in
ascending (ASC) or descending (DESC) order
according to some column.
SELECT
FROM customers
WHERE purchases gt 100
ORDER BY age ASC

Updating a table
The following statement modifies all rows that
fit the criteria, replacing their column values
as specified in the list.
UPDATE table_name
SET list of pairs of form Column_name value
WHERE criteria
This updates the field in the purchases column
of any records matching the criterion on the age
column.
UPDATE customers
SET purchases 0
WHERE age lt 18

This updates the age and purchases column of a
particular record, identified by its primary key.
UPDATE customers
SET age 42, purchases purchases 100
WHERE custID '12'
Note how the key (column name) can be used as a
variable within a SET statement.

Deleting records
This deletes all records matching the criteria.
DELETE FROM table_name
WHERE criteria
The following deletes the whole table.
DROP TABLE table_name

(Chapter 10 continued)
Our examples feature MySQL as the database
engine.
It's open source and free.
It's fully featured.
And it's platform independent for the most part.
These examples and concepts carry over to other
database systems.
The DBI (database Interface) module provides the
objects used in the program to connect to,
prepare, and deliver SQL statements to the
database engine.
The DBDmysql module handles the details of the
connection behind the scenes.
Connecting to a different database requires only
a different DBD (Database Driver) module be
installed.

25
Overview of Perllt---gtMySQL interface

You construct SQL queries with the DBI module in
a Perl program.
The driver handles the connection details.
Raw SQL is delivered to the database engine.
Returned data is stored in a DBI object, which
has methods for extracting the data.

26
Overview of objects provided by the DBI
module. Database object This is the object that
holds the connection to the desired database. It
works kind of like a file handle since it
references the open database connection. For that
reason, we will call this object the database
handle. Query object This object is created by
calling a method of the database handle. It is
through methods of this object that we are able
to send SQL statements to the database
system. Driver object This object is seldom
used. It is only used for performing
administrative tasks, such as creating or
destroying an entire database, which has nothing
to do with SQL. Note that we are not talking
about creating or destroying tables which is
done through SQL statements. We include this for
completeness, and will not have occasion to use
this object.
27
DBI Module

PERL is capable of running SQL and MySQL queries
including inserts, selects, updates, deletes,
etc through a module termed DBI.
Often your web host will already have this module
as well as DBDmysql already installed.
DBI stands for database interface. Any functions
associated with DBI should work with all the
available SQL platform including SQL Server,
Oracle, DB2, and MySQL.

28
DBI(database interface)DBD(database driver)

Once they are installed, we can build the
introduction to our script by telling PERL to use
these modules as follows
dbimodules.pl
!/usr/bin/perl
PERL MODULES WE WILL BE USING
use DBI
use DBDmysql
Again, these modules allow for us to call upon
functions specific to working with a any database
platform including MySQL.
These modules must be in "use" to ensure proper
functionality of our scripts.

We will be calling on our database, table, and
host machine from time to time.
We recommend setting up a some variables for your
database and table name, so that you can call
upon them as you wish throughout this brief
tutorial.
You may also set up some variables for your user
name and password as we will also be needing to
connect to your MySQL web host.

30
dbiconfig.pl

!/usr/bin/perl
PERL MODULES WE WILL BE USING
use DBI
use DBDmysql
DBI CONFIG VARIABLES
host "localhost"
database "store"
tablename "inventory"
user "username"
pw "password"

31
Data Source name

In order to connect to our database platform we
first need to know our web server's data source
name. This information should be readily
accessible in your server's documentation. There
are four pieces that actively make up a DSN.
Name of SQL Platform (SQL Server, Oracle, DB2,
MySQL, etc).
Database Name
Host Name (www.myhost.com)
Port Number
Since we plan on executing our scripts from our
web server through our browser, we can substitute
our host's name with the term localhost.
dsn "dbiSQL_Platformdatabase_namelocalhostp
ort"

32
connect

The connect call tries to connect to a database.
The first argument, data source name, tells DBI
what kind of database it is connecting to.
It assumes the database daemon is running on the
same computer as the Web server. Otherwise, more
information such as the server address and port
number is sent to the constructor.

!/usr/bin/perl
PERL MODULES WE WILL BE USING
use DBI
use DBDmysql
HTTP HEADER
print "Content-type text/html \n\n"
CONFIG VARIABLES
platform "mysql"
database "store"
host "localhost"
port "3306"
tablename "inventory"
user "username"
pw "password"
DATA SOURCE NAME
dsn "dbimysql

34
Example Connect to a MySQL database named store,
where storeuser is the user name and pass is the
password. dbhandle DBI-gt
connect("DBImysqlstore","storeuser", "pass")
or errorPage(DBI-gterrstr()) The errstr()
method returns a string containing useful error
messages on failed connection attempts. Closing
an open database connection is very similar in
concept to closing an open file. dbhandle -gt
disconnect()
35

Now assume we have an open database handle
Using the query object
There are 4 steps in executing a SQL query using
DBI.
Prepare the SQL statement.
Execute the SQL statement.
Fetch any returned results from the query object.
This step is only necessary for SELECT queries.
Clear the query object.

36
Queries

Queries must be prepared and then executed.
Two lines of code are required for this, first
the prepare() function and then the execute()
function.
It it usually requires some work to build an SQL
statement in a Perl program, so we typically
store the SQL statement in a Perl variable.
We define a variable with an SQL statement. Then
we create a query handle and run our connect
statement along with the prepare function

PERL DBI CONNECT
connect DBI-gtconnect(dsn, user, pw)
PREPARE THE QUERY
query "SELECT FROM inventory ORDER BY id"
query_handle connect-gtprepare(query)
EXECUTE THE QUERY
query_handle-gtexecute()

The result of the prepare() method of the
database object is that the query object is
constructed and the SQL statement is stored in
the query object in some format.
The execute() method of the query object takes
that internally stored SQL statement and sends it
to the database engine (with the help of the
proper driver).
Upon a successful execution, the query object
contains data returned from the database query.
Again, we are usually only concerned with the
returned data when it is the result of a SELECT
query which returns a sub-table of data..

39
Another Example

DATA SOURCE NAME
dsn "dbiplatformdatabasehostport"
PERL DBI CONNECT
connect DBI-gtconnect(dsn, user, pw)
PREPARE THE QUERY
query "INSERT INTO inventory (id, product,
quantity) VALUES (DEFAULT, 'tomatoes', '4')"
query_handle connect-gtprepare(query)
EXECUTE THE QUERY
query_handle-gtexecute()

40
Bind and fetch

Two new functions are introduced in the next
slide, the bind_columns and the fetch()
functions.
Variable names are assigned to the column values
via the bind_column function
The fetch() function fetches the rows matching
the query

PERL DBI CONNECT
connect DBI-gtconnect(dsn, user, pw)
PREPARE THE QUERY
query "SELECT FROM inventory ORDER BY id"
query_handle connect-gtprepare(query)
EXECUTE THE QUERY
query_handle-gtexecute()
BIND TABLE COLUMNS TO VARIABLES
query_handle-gtbind_columns(undef, \id,
\product, \quantity)
LOOP THROUGH RESULTS
while(query_handle-gtfetch()) print "id,
product, quantity ltbr /gt"

42
MySQL module

MySQL queries and the like can be executed with
PERL via the MySQL Module. This module should
already be installed with your web server
As a quick overview, this module installs the
necessary functions required to execute MySQL
queries using a PERL script. Please take note
that this module only works with the MySQL
platform. Other SQL platforms will require the
use of the DBI module discussed in our PERL DBI
Module lesson.

43
Note the use Mysql

!/usr/bin/perl
PERL MODULE WE WILL BE USING
use Mysql
MySQL CONFIG VARIABLES
host "localhost"
database "store"
tablename "inventory"
user "username"
pw "password"
PERL MYSQL CONNECT
connect Mysql-gtconnect(host, database,
user, pw)

44
Example

Once PERL has established a connection we can
execute any of the built in module functions. A
great introductory function is the listdbs
function. This function reads from the MySQL
platform and places the name of each database
into an array.
_at_databases connect-gtlistdbs

45
List databases

MYSQL CONFIG VARIABLES
host "localhost"
database "store"
tablename "inventory"
user "username"
pw "password"
PERL CONNECT()
connect Mysql-gtconnect(host, database,
user, pw)
LISTDBS()
_at_databases connect-gtlistdbs
foreach database (_at_databases)
print "databaseltbr /gt"

46
A select example

Queries that use the SELECT clause are a little
more exciting.
Here we introduce two new functions, the
numrows() function and the numbfields() function.
Both of these do exactly as they say, one fetches
the number of rows returned with as the query
executes while the other fetches the number of
fields returned.