1 theory, practice & methodology of relational database design and programming copyright ©...
Post on 13-Dec-2015
214 Views
Preview:
TRANSCRIPT
1
Theory, Practice & Methodology of Relational Database
Design and ProgrammingCopyright © Ellis Cohen 2002-2008
ImplementingThe Middle Tier
These slides are licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License.
For more information on how you may use them, please see http://www.openlineconsult.com/db
© Ellis Cohen 2001-2008 2
Overview of Lecture
GUI-Based User Interfaces
Command-Based User Interfaces
Client vs Server-Side Execution
Middle-Tier Data &Cross-Tier Communication
© Ellis Cohen 2001-2008 3
GUI-Based User Interfaces
© Ellis Cohen 2001-2008 4
Graphical User Interfaces
Ordinarily, users invoke user operations through a (web-based) user interface, by
– filling in fields,
– making choices (e.g. checkboxes & radio buttons), and
– invoking actions (e.g. from pushbuttons or menu items).
The fields and choices are passed as parameters to code in the middle-tier
© Ellis Cohen 2001-2008 5
Login Operation
Presentation Tier
Data TierMiddle Tier
LOGIN
Login
:userid
:passwd SELECT role INTO aRoleFROM KnownUsersWHERE userid = :userid AND pwd = :passwdIF error,
return Login Error Page
ELSE build & return Home Page & remember :userid & aRole
:currole:curuser
Assuming database is responsible for user
authentication – e.g. via table KnownUsers
© Ellis Cohen 2001-2008 6
Using Session Variables
SELECT empno, ename, salFROM EmpsWHERE empno = :curuser OR mgr = :curuser
ShowSals
Once a session variable has been set,it can be used in subsequent operations.
What does the User Query operation ShowSals do?
IMPORTANT: This is the format we use to depict user operations defined
in the middle-tier
User Operation name & parameters
UserOperation
code
© Ellis Cohen 2001-2008 7
ShowSals
SELECT empno, ename, salFROM EmpsWHERE empno = :curuser OR mgr = :curuser
ShowSals
Shows employee #, name & salaryof the current user , and
of all employees the current user directly manages!
For a web-based interface, the result set would actually be used to build the resulting HTML page,
probably by using a query-based FOR LOOP
© Ellis Cohen 2001-2008 8
DestroyDept Operation
Presentation Tier
Data TierMiddle Tier
Destroy
DestroyDept
:deptno
:reldept
Destroys dept :deptnoIf :reldept is NULL,
delete all the employees in that department,else, move all the
employees in that dept to department :reldept
© Ellis Cohen 2001-2008 9
DestroyDept Implementation
BEGIN IF :reldept IS NULL THEN
-- delete all the employees in dept :deptno
DELETE FROM Emps WHERE deptno = :deptno; ELSE -- move the employees in dept :deptno to dept :reldept UPDATE Emps SET deptno = :reldept
WHERE deptno = :deptno; END IF; -- delete dept :deptno
DELETE FROM Depts WHERE deptno = :deptno;END;
Destroys dept :deptnoIf :reldept is NULL, delete all the employees in that department,
else, move all the employees in that dept to department :reldept
For a web-based interface, an acknowledgement page or some other useful page would then be returned
(if there were no error)
DestroyDept( :deptno, :reldept )
© Ellis Cohen 2001-2008 10
Command-Based User Interfaces
© Ellis Cohen 2001-2008 11
Web-Based 3-Tier Architecture
DatabaseServer
WebBrowser
Web or Application
Server
Presentation Tier Data TierMiddle Tier
Implements DB Operations
Implements User Operations
Handles overall DB mgt,
formatting & page navigation
DBApplication
API
(DB) Server-Side(DB) Client-Side
User
© Ellis Cohen 2001-2008 12
Command-Based 3-Tier Architecture
DatabaseServer
TerminalInterface
CommandInterpreter
DBApplication
API
User
Implementusing PL/SQL
Use SQL*Plusas the
Command Interpreter
© Ellis Cohen 2001-2008 13
DestroyDept Implementation
BEGIN IF :reldept IS NULL THEN DELETE FROM Emps WHERE deptno = :deptno; ELSE UPDATE Emps SET deptno = :reldept
WHERE deptno = :deptno; END IF; DELETE FROM Depts WHERE deptno = :deptno; pl( 'Department Destroyed' );END;/
Destroys dept :deptnoIf :reldept is NULL, delete all the employees in that department,
else, move all the employees in that dept to department :reldept
This is for a command-driven interface.A real web-based interface would show an
acknowledgement page or some other useful page
DestroyDept( :deptno, :reldept )
© Ellis Cohen 2001-2008 14
SQL*Plus User Operations
SQL*Plus does not have a way of defining user operations.
However, we can put each user operation into a separate script file
– To invoke the operation, just run the corresponding script file
– Use script file parameters to provide operation parameters
© Ellis Cohen 2001-2008 15
Using Script File Parameters
BEGIN IF &2 IS NULL THEN DELETE FROM Emps WHERE deptno = &1; ELSE UPDATE Emps SET deptno = &2
WHERE deptno = &1; END IF; DELETE FROM Depts WHERE deptno = &1; pl( 'Department Destroyed' );END;/
User Action:DestroyDept &1 – :deptno &2 – :reldept
Destroys dept :deptnoIf :reldept is NULL, delete all the employees in that department,
else, move all the employees in that dept to department :reldept
Script files can instead use command line parameters referred to as &num
SQL> -- delete dept 20 and its employeesSQL> @path/DestroyDept 20 NULL
The contents of DestroyDept.sql
© Ellis Cohen 2001-2008 16
Renaming Script File Parameters
DECLARE aDeptno int := &1; reldept int := &2;BEGIN IF reldept IS NULL THEN DELETE FROM Emps WHERE deptno = aDeptno; ELSE UPDATE Emps SET deptno = aDeptno
WHERE deptno = reldept; END IF; DELETE FROM Depts WHERE deptno = aDeptno; pl( 'Department Destroyed' );END;/
DestroyDept &1 – :deptno &2 – :reldept
© Ellis Cohen 2001-2008 17
Client vs Server-SideExecution
© Ellis Cohen 2001-2008 18
SQL*Plus and PL/SQL
SQL> BEGIN
insert into project( pno, pname, pmgr ) values ( …, …, … );
insert into project( pno, pname, pmgr ) values ( …, …, … );
…
insert into project( pno, pname, pmgr ) values ( …, …, … );
END;
/
Begins PL/SQL block
SQL*Plus knows how to parse pure SQL, but not PL/SQL, so it doesn't know when a
PL/SQL block actually ends. Use / to tell it.
© Ellis Cohen 2001-2008 19
Client-Side Processing
Client-SidePL/SQLEngine
(Oracle)Database
Server
DB Client-side DB Server-side
If the block is executed on the client, then each INSERT command is separately encountered,
and sent as a separate request to the DB server.
CommandInterpreter
© Ellis Cohen 2001-2008 20
Server- vs Client-Side Processing
If SQL*Plus were configured for client-side processing:– it would pass anonymous PL/SQL
blocks to a client-side PL/SQL engine
If SQL*Plus were configured for server-side processing (as the default SQL*Plus application is)– it passes anonymous PL/SQL blocks to
the database server, which passes the block to its internal server-side PL/SQL engine
© Ellis Cohen 2001-2008 21
Server-Side Processing
CommandInterpreter
PL/SQLEngine
OracleDatabaseServer
Core SQLDatabase
Engine
Ship down PL/SQL
block of code
DB Client-side DB Server-side
In server-side processing, the entire block is shipped once to the
DB server, which executes it in its embedded PL/SQL engine
© Ellis Cohen 2001-2008 22
Server-Side Output
Client-side Program
PL/SQLEngine
OracleDatabaseServer
Core SQLDatabase
Engine
Execution ofdbms_output.put_line( 'Hello' )
Strings passed to dbms_output.put_line are shipped to the client-side program through a separate channel.
If the client-side program is SQL*Plus, it will display the output if the SQL*Plus variable serveroutput is set.
© Ellis Cohen 2001-2008 23
Middle-Tier Data & Cross-Tier
Communication
© Ellis Cohen 2001-2008 24
Middle-Tier Data
A variety of middle-tier data is typically available, which can be used in executing user operations:
• Named middle-tier constants
• Parameters passed to user operations
• Session data maintained by the middle-tier
© Ellis Cohen 2001-2008 25
SQL*Plus Constants
SQL> define myDeptno = 10SQL> -- defines a SQL*Plus constant
SQL> SELECT ename FROM Emps WHERE deptno = &myDeptno;
SQL> BEGIN FOR rec IN (
SELECT ename FROM Emps WHERE deptno = &myDeptno)
LOOP pl( rec.ename ); END LOOP; END; /
SQL*Plus replaces uses of named constants by their
values before passinganything to the server
Use of a constant
© Ellis Cohen 2001-2008 26
SQL*Plus Constant Substitution
SQL> DEFINE myDeptno = 10SQL> -- defines a SQL*Plus constant
SQL> SELECT ename FROM Emps WHERE deptno = &myDeptno;
SQL> SELECT ename FROM Emps WHERE deptno = 10;
Automatically converted by SQL*Plus before passing it to the server
The same is true of script parameters,e.g. &1, &2, etc.
© Ellis Cohen 2001-2008 27
SQL*Plus Session Variables
SQL> VARIABLE myDeptno numberSQL> -- defines a SQL*Plus variable
SQL> SELECT ename FROM Emps WHERE deptno = :myDeptno;
SQL> BEGIN FOR rec IN (
SELECT ename FROM Emps WHERE deptno = :myDeptno)
LOOP pl( rec.ename ); END LOOP; END; /
SQL> EXECUTE :myDeptno := 10;
This is how client-side
session variables are
defined!
Setting a session
variable to a value
Session variables used in code
passed to the DB server are called
bind variables
© Ellis Cohen 2001-2008 28
Passing Bind Variables
Client-side Program
PL/SQLEngine
OracleDatabaseServer
Core SQLDatabase
Engine
DB Client-side DB Server-side
SELECT ename FROM Emps WHERE deptno = :myDeptno
When SQL*Plus parses the command, it ships
the name & value of myDeptno to the server along with the request
myDeptno10
myDeptno10
SELECT ename FROM Emps WHERE deptno = :myDeptno
The DB server can
access bind variables
© Ellis Cohen 2001-2008 29
Returning Bind Variables
Client-side Program
PL/SQLEngine
OracleDatabaseServer
Core SQLDatabase
Engine
DB Client-side DB Server-side
EXECUTE :myDeptno := 10
After executing a command or anonymous block, the values of any bind variables passed to the server
are returned to the client
BEGIN :myDeptno := 10; END;
myDeptnoNULL
myDeptnoNULL
myDeptno10
myDeptno10
© Ellis Cohen 2001-2008 30
Setting Session Variables
Login( :userid, :passwd )
Checks if the provided user id and password identify an authorized user
If so, set :curuser to the user id,and set :currole to the user's role
Below are the definitions of :curuser and :currole
SQL> VARIABLE curuser number -- assumes userid is a number -- instead use VARIABLE curuser varchar2(40) -- if your userid was a 40 char max string
SQL> VARIABLE currole char -- assumes current role is represented by a single character
© Ellis Cohen 2001-2008 31
Defining Login
BEGIN
SELECT userid, role INTO :curuser, :currole FROM KnownUsers WHERE userid = :userid AND pwd = :passwd;
pl( 'User logged in with role ' || :currole );
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR( …, ' Illegal Userid or Password' );
END;
Login( :userid, :passwd )
User operation name & parameters
© Ellis Cohen 2001-2008 32
Setting Session Variables
Client-side Program
PL/SQLEngine
OracleDatabaseServer
Core SQLDatabase
Engine
BEGIN login code END BEGIN … END;
curuser curroleNULL NULL
curuser curroleNULL NULL
curuser currole 7782 C .
curuser currole 7782 C .
© Ellis Cohen 2001-2008 33
Script-Based Login Implementation
BEGIN
SELECT userid, role INTO :curuser, currole FROM KnownUsers WHERE userid = &1 AND pwd = '&2';
pl( 'User logged in with role ' || :currole );
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR( …, ' Illegal Userid or Password' );
END;
Login( :userid, :passwd ) &1 – :userid &2 – :passwd
© Ellis Cohen 2001-2008 34
Clearer Script-Based Login Implementation
DECLARE aUserid number := &1; passwd varchar(40) := '&2';BEGIN SELECT userid, role INTO :curuser, :currole FROM KnownUsers WHERE userid = aUserid AND pwd = passwd;
pl( 'User logged in with role ' || :currole );EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR( …, ' Illegal Userid or Password' );
END;
Login( :userid, :passwd ) &1 – :userid &2 – :passwd
© Ellis Cohen 2001-2008 35
Middle-Tier User Authentication
EmpDBWeb
Browser
Middle-Tier:curuser
7782
App User7782
3) Database authenticates the
application connecting as
EmpDB
The application can then access all of EmpDB's objects
2) Application authenticates
the user logging in as 7782
when the userdoes a Login
1) User provides 7782 as their
userid
top related